2

I have a table with a primary key. Postgres automatically created an index on this primary key, and it is separately taking up +34% more storage.

Why does Postgres not simply keep the table sorted by the primary key? It's auto increment so it will always be in order. I tried CLUSTER but that did not decrease the size of this primary key index. Why is a primary key index even needed if the table itself will always be in that order?

3
  • 4
    There is no such thing as a "sorted table". Rows in a relation table have no "order". You are referring to a concept called "clustered index" or "index organized table" which is available in other DBMS systems but not on Postgres (and really not needed that often, except for very narrow tables where all columns are indexed)
    – user1822
    Mar 29, 2020 at 9:23
  • "You are referring to a concept called "clustered index" or "index organized table" which is available in other DBMS systems but not on Postgres" Then I must be misunderstanding this page: postgresql.org/docs/9.1/sql-cluster.html which seems to say: "When a table is clustered, it is physically reordered based on the index information." What does that mean?
    – lurf jurv
    Mar 30, 2020 at 18:42
  • 1
    clustering a table by an index is something different than an index organized table (or a clustered index). You might want to read this: use-the-index-luke.com/sql/clustering/…
    – user1822
    Mar 30, 2020 at 18:43

1 Answer 1

1

In PostgreSQL, the only way to guarantee uniqueness is with a unique index. So a unique index is used to implement all primary key and unique indexes in PostgreSQL. The extra space that the primary key takes is to be expected.

Also, PostgreSQL does not maintain a certain order in the table. Whenever you update a row, it moves somewhere else, and the free space left by past updates and deletes is reused for future inserts.

2
  • I understand. I'm just asking why postgres does it this way? For example, in my case of a primary key that is auto increment, what is the actual reason why postgres doesn't maintain that in order?
    – lurf jurv
    Mar 30, 2020 at 18:45
  • "Why" is always difficult to impossible to answer. All databases that I know do it like this. Only very few use cases would benefit from it. Also, that would mean that the physical address of rows changes even if it is not modified at all, so it could not be used in indexes. That would make all index access slow, because it has to go through the primary key. It just doesn't seem a reasonable thing to do, but of course this is just my opinion. Mar 30, 2020 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.