Change order of the table rows in PostgreSQL

Question

I have a table in PostgreSQL. I want to re-order the rows data physically according to a specific column (which is not primary key). In my case this column type is date. How can I do it?

Answer 1

Have you tried "ORDER BY"? For example, "SELECT * FROM table_name ORDER BY date ASC;"? If you want the dates in reverse order, you can try "DESC" instead of "ASC".

Answer 2

If you have an index on that column, then the CLUSTER command will physically "order" the rows according to that index

CLUSTER [VERBOSE] table_name [ USING index_name ]

http://www.postgresql.org/docs/current/static/sql-cluster.html

Note that this "order" isn't automatically maintained, you need to run that statement on a regular basis manually.

---

This will however not guarantee any specific order when retrieving the rows. Not even when no joins or aggregates are involved.

Even if all you do is select * from the_table the order in which the rows are returned is still not guaranteed. For example: Postgres has a feature called "synchronized seq scan", which means that if one session starts a seq scan (select * from ...) and another session is doing the same thing, the second one piggy-backs on the first seq scan (where ever that is) and then adds the "missed" rows at the end of the result.

The only way to guarantee an order of a result set (really: the only) is to supply an order by clause.

---

This only makes sense (at least to me) if you have a single harddisk in your server (which is not a SSD). In that case a seq scan might be faster because all blocks might be right next to each other (which isn't guaranteed either because of the way a file system re-uses free space).

On a SSD or a proper server which uses a RAID array with many, many hard disks I can't see how this could be beneficial in any way.