Django Postgres database records reordering

Question

For some reason every time I save a record to my postgres (it's on heroku) database from Django, that record is moved to the end of the database.

For example, if I have 3 records:

record1

record2

record3

And I update record 1, they become ordered like this:

record2

record3

record1

This is making it tough for my website to navigate through a set of pages in the right order. Anybody know how to stop these records from moving around?

Answer 1

Basically, you can't. SQL databases can and do change the order of records as needed to ensure efficiency and speed.

If you always want to display records in a certain order, you need to add the ordering yourself. You could do something like have a "created_at" column in which you store a timestamp when the record is created, and then sort by that column when you retrieve the records, using order_by.