Resort (reorder) table data

Question

What is the fastest way to reorder (resort) a table so that the physical representation most closely matches the logical, using PostgreSQL?

Answer 1

Order of insertion does not in the end always control order in the table. Tables are ordered by their clustered index, if they have one. If they do not have one, then the order is technically undefined, although it is probably safe in many cases to assume that they're ordered in insertion order this doesn't mean they'll stay that way. Lacking a specific ordering the DB engine is free to reorder as it sees fit to optimize retrieval.

If you want to control order on disk, the single best way is to properly define your clustered index. Always.

Answer 2

Use CLUSTER to reorder a table by a given index.

(BTW, ALTER TABLE ... ORDER BY ...)

Answer 3

Having data ordered does not mean that you won't need ORDER BY clause in your queries anymore.

It just means that the logical order or the data is likely to match the physical order, and retrieving the data in logical order (say, from an index scan) will more likely result in a sequential read access to the table.

Note that neither MySQL nor PostgreSQL guarantee that INSERT … SELECT … ORDER BY will result in the data being ordered in the table.

To order data in PostgreSQL you should define an index with the given order and issue this command:

CLUSTER mytable USING myindex

This will rebuild the table and all indexes defined on it.