I am using a simple model with an attribute that stores all the data for that object in a JSONField. Think of it as way to transfer NoSQL data to my PostgreSQL database. Kinda like this:
from django.contrib.postgres.fields import JSONField
class Document(models.Model):
content = JSONField()
Each Document
object has (more or less) the same keys in its content
field, so I am querying and ordering those documents using those keys. For the querying and ordering, I am using Django's annotate()
function. I recently came across this:
https://docs.djangoproject.com/en/2.1/ref/contrib/postgres/indexes/
I also know that PostgreSQL using JSONB, which is apparently indexible. So my question is this: Can I index my content
field somehow to make my read operations faster for complex queries? And if so, then how do I do it? The documentation page I linked has no examples.
For those that want to index a particular key, create a raw sql migration:
Run ./manage.py makemigrations --empty yourApp
where yourApp
is the app of the model you want to change indexes for.
Edit the migration i.e.
operations = [
migrations.RunSQL("CREATE INDEX idx_name ON your_table((json_field->'json_key'));")
]
Where idx_name
is the name of the index, your_table
is your table, json_field
is your JSONField, and json_key
in this case is the key you want to index.
Notice the ->
operator in the index creation code line, and not ->>
as mentioned here.
That should do it, but to verify all went well run the following sql:
SELECT
indexname,
indexdef
FROM
pg_indexes
WHERE
tablename = '<your-table>';
and see if your index is there.
There is a bit more universal and Django native way. You can use following custom Migration Operation:
class CreateJsonbObjectKeyIndex(Operation):
reversible = True
def __init__(self, model_name, field, key, index_type='btree', concurrently=False, name=None):
self.model_name = model_name
self.field = field
self.key = key
self.index_type = index_type
self.concurrently = concurrently
self.name = name
def state_forwards(self, app_label, state):
pass
def get_names(self, app_label, schema_editor, from_state, to_state):
table_name = from_state.apps.get_model(app_label, self.model_name)._meta.db_table
index_name = schema_editor.quote_name(
self.name or schema_editor._create_index_name(table_name, [f'{self.field}__{self.key}'])
)
return table_name, index_name
def database_forwards(self, app_label, schema_editor, from_state, to_state):
table_name, index_name = self.get_names(app_label, schema_editor, from_state, to_state)
schema_editor.execute(f"""
CREATE INDEX {'CONCURRENTLY' if self.concurrently else ''} {index_name}
ON {table_name}
USING {self.index_type}
(({self.field}->'{self.key}'));
""")
def database_backwards(self, app_label, schema_editor, from_state, to_state):
_, index_name = self.get_names(app_label, schema_editor, from_state, to_state)
schema_editor.execute(f"DROP INDEX {index_name};")
def describe(self):
return f'Creates index for JSONB object field {self.field}->{self.key} of {self.model_name} model'
@property
def migration_name_fragment(self):
return f'create_index_{self.model_name}_{self.field}_{self.key}'
Usage example:
from django.db import migrations
from util.migration import CreateJsonbObjectKeyIndex
class Migration(migrations.Migration):
atomic = False # Required if concurrently=True for 0 downtime background index creation
dependencies = [
('app_label', '00XX_prev_migration'),
]
operations = [
migrations.SeparateDatabaseAndState(
database_operations=[
# Operation to run custom SQL command. Check the output of `sqlmigrate` to see the auto-generated SQL
CreateJsonbObjectKeyIndex(
model_name='User', field='meta', key='adid', index_type='HASH',
concurrently=True,
)
],
)
]
Tested with Django-2.2 and and AWS Postgres RDS, but should be compatible with other Django
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With