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

Question

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?

serg · Accepted Answer · 2017-05-22 05:54:21Z

1

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.

answered May 22, 2017 at 5:54

serg

112k78 gold badges326 silver badges337 bronze badges

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

1 Comment

AlejandroVK Over a year ago

Thanks, I'll check it out, looks like this logic can be good enough to update all elements before and after a new update / insert, cheers!

JGH · Accepted Answer · 2017-05-22 02:02:18Z

1

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)

answered May 22, 2017 at 2:02

JGH

18.2k5 gold badges40 silver badges57 bronze badges

1 Comment

AlejandroVK Over a year ago

thanks, yes that would be the case, I don't expect really frequent or even concurrent updates, so it should be doable

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related