Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows and returns them according to their physical location. The table in question has been clustered by its primary key.
As I understand it, PostgreSQL does not automatically continue doing clustering after a clustering is finished (although it does remember that it clustered according to a certain index). Now, since this is the primary key, I wonder if the physical storage order would be according to that (which if true I would like to use to our advantage for a specific query).
In summary, how does PostgreSQL order its new records, especially after clustering?
Thanks very much!
Best Answer
Rows in postgresql have no fixed order. Not only are records placed where ever there is free space records can also move around. This is because when a row is updated a new version of the row is created in a new location while the old version continues to live in it's old location until it's removed by vacuum.
A CLUSTER operations sorts all rows but does indeed not affect how postgresql add's rows. So the data won't stay sorted. However postgresql keeps statistics among which is the correlation for each column between the order of the rows in the tables and the sorted order of that column. So the planner can still optimize it's plan based on the statistics that tell it the table is still mostly sorted even if some rows have been added after a cluster operation (or have been moved by updates).