12

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!

Improve this question

1 Answer 1

Reset to default
13

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).

Improve this answer
2
  • The answer is very good but lack explanation of what will happen if only insert is used. No deletes no upserts? In other words if I can manually "cluster" data by copie them to new table and then rename? Commented Dec 3, 2022 at 12:40
  • @S.R AFAIK no garantees are made but I believe that currently it would indeed store the rows in insert order provided they all have the same size. When they are not the same size larger rows may require a new page (rows are always stored in a single page) leaving empty space that can be filled up with a smaller row later on. Commented Dec 4, 2022 at 11:01

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.