Add BrinIndex to existing PostgreSQL database using Django

Question

I am running a website using Django, of which the database runs on PostgreSQL. To increase performance I want to add a BrinIndex to a model that is naturally ordered on disk.

I have added the index to my models definition as follows.

from django.contrib.postgres.indexes import BrinIndex

class Measurement(models.Model):
    class Meta:
        indexes = (
            BrinIndex(fields=['time'])
        )

    time = models.DateTimeField(
        'Time of measurement',
        null=True
    )

But running

python3 manage.py makemigrations

Returns an error

Traceback (most recent call last):
  File "C:\Program Files\JetBrains\PyCharm 2018.1.1\helpers\pycharm\django_manage.py", line 52, in <module>
    run_command()
  File "C:\Program Files\JetBrains\PyCharm 2018.1.1\helpers\pycharm\django_manage.py", line 46, in run_command
    run_module(manage_file, None, '__main__', True)
  File "C:\Python36\lib\runpy.py", line 205, in run_module
    return _run_module_code(code, init_globals, run_name, mod_spec)
  File "C:\Python36\lib\runpy.py", line 96, in _run_module_code
    mod_name, mod_spec, pkg_name, script_name)
  File "C:\Python36\lib\runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "C:/Users/*USERNAME*/*PROJECT_NAME*\manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "C:\Users\*USERNAME*\*PROJECT_NAME*\venv\lib\site-packages\django\core\management\__init__.py", line 371, in execute_from_command_line
    utility.execute()
  File "C:\Users\*USERNAME*\*PROJECT_NAME*\venv\lib\site-packages\django\core\management\__init__.py", line 347, in execute
    django.setup()
  File "C:\Users\*USERNAME*\*PROJECT_NAME*\venv\lib\site-packages\django\__init__.py", line 24, in setup
    apps.populate(settings.INSTALLED_APPS)
  File "C:\Users\*USERNAME*\*PROJECT_NAME*\venv\lib\site-packages\django\apps\registry.py", line 112, in populate
    app_config.import_models()
  File "C:\Users\*USERNAME*\*PROJECT_NAME*\venv\lib\site-packages\django\apps\config.py", line 198, in import_models
    self.models_module = import_module(models_module_name)
  File "C:\Python36\lib\importlib\__init__.py", line 126, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 978, in _gcd_import
  File "<frozen importlib._bootstrap>", line 961, in _find_and_load
  File "<frozen importlib._bootstrap>", line 950, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 655, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 205, in _call_with_frames_removed
  File "C:/Users/*USERNAME*/*PROJECT_NAME*\*APP_NAME*\models.py", line 300, in <module>
    class Measurement(models.Model):
  File "C:\Users\*USERNAME*\*PROJECT_NAME*\venv\lib\site-packages\django\db\models\base.py", line 298, in __new__
    new_class._meta.indexes = [copy.deepcopy(idx) for idx in new_class._meta.indexes]
TypeError: 'BrinIndex' object is not iterable

Am I doing something wrong? Can this index not be added afterwards?

Are you importing django.contrib.postgres.indexes in your models.py? — tatlar
– tatlar, Commented Jun 11, 2018 at 15:59
Also, which version of Django are you using? BrinIndex is only supported in version 1.11 and above. — tatlar
– tatlar, Commented Jun 11, 2018 at 16:00
Did you check if the field is NULL affects the ability to index (see my answer)? — tatlar
– tatlar, Commented Jun 11, 2018 at 18:25

Mohammad Davoudi · Accepted Answer · 2019-07-28 07:15:53Z

2

here is the problem of your code when defining tuples with a single value you have to put "," at the end of the value otherwise python won't recognize it as a tuple for example:

a = ('value1') #is a string not a tuple
a = ('value1',) #is a tuple

here is what your code should look like: from django.contrib.postgres.indexes import BrinIndex

class Measurement(models.Model):
    class Meta:
        indexes = (
            BrinIndex(fields=['time']),  # "," is added here
        )

    time = models.DateTimeField(
        'Time of measurement',
        null=True
    )

answered Jul 28, 2019 at 7:15

Mohammad Davoudi

415 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tatlar · Accepted Answer · 2018-06-11 16:25:00Z

0

A couple of things to try:

You can't always have an index on a NULL field. I don't know the inner workings of BrinIndex but it's something you might like to check.
Your model definition should include a db_index attribute:

from django.contrib.postgres.indexes import BrinIndex

class Measurement(models.Model):

    time = models.DateTimeField(
        'Time of measurement',
        db_index=True,
        null=True
    )

    class Meta:
        indexes = (
            BrinIndex(fields=['time'])
        )

answered Jun 11, 2018 at 16:25

tatlar

3,1762 gold badges30 silver badges41 bronze badges

1 Comment

Nerman Over a year ago

#2 is not quite correct. From the docs : >Use Meta.indexes or Field.db_index

Collectives™ on Stack Overflow

Add BrinIndex to existing PostgreSQL database using Django

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related