CODEWITHSUNDEEP
djangopostgresql
AlejandroVK
AlejandroVK

Reputation: 7605

how to deal with virtual index in a database table in Django + PostgreSQL

Here is my current scenario:

Need to add a new field to an existing table that will be used for ordering QuerySet.

This field will be an integer between 1 and not a very high number, I expect less than 1000. The whole reasoning behind this field is to use it for visual ordering on the front-end, thus, index 1 would be the first element to be returned, index 2 second, etc...

This is how the field is defined in model:

priority = models.PositiveSmallIntegerField(verbose_name=_(u'Priority'),
                                            default=0,
                                            null=True)

I will need to re-arrange (reorder) the whole set of elements in this table if a new or existing element gets this field updated. So for instance, imagine I have 3 objects it this table:

Element A priority 1

Element B priority 2

Element C priority 3

If I change Element C priority to 1 I should have:

Element C priority 1

Element A priority 2

Element B priority 3

Since this is not a real db index ( and have empty values), I'm gonna have to query for all elements on database each time a new element is created / updated and change priority value for each record in table. Not really worried about performance since table will always be small BUT, I'm worried this way to proceed is not the way to go or simply it generates too much overhead.

Maybe there is simpler way to do this with plain SQL stuff? If I use an index though, I will get an error every time an existing priority is used, something I don't want either.

Any pointers?

Upvotes: 1

Views: 185

Answers (2)

serg
serg

Reputation: 111265

To insert at 10th position all you need is a single sql query:

MyModel.objects.filter(priority__gte=10).update(priority=models.F('priority')+1)

Then you would need a similar one for deleting an element, and swapping two elements (or whatever your use case requires). It all should be doable in a similar manner with bulk update queries, no need to manually update entry by entry.

Upvotes: 1

JGH
JGH

Reputation: 17836

First, you can very well index this column, just don't enforce it to contains unique values. Such standard indexes can have nulls and duplicates... they are just used to locate the row(s) matching a criteria.

Second, updating each populated* row each time you insert/update a record should be looked at based on the expected update frequency. If each user is inserting several records each time they use the system and you have thousands of concurrent users, it might not be a good idea... whereas if you have a single user updating any number of rows once in a while, it is not so much an issue. On the same vein, you need to consider if other updates are occurring to the same rows or not. You don't want to lock all rows too often if they are to be updated/locked for updating other fields.

*: to be accurate, you wouldn't update all populated rows, but only the ones having a priority lower than the inserted one. (inserting a priority 999 would only decrease the priority of items with 999 and 1000)

Upvotes: 1

Related Questions