3 ms·
PostgreSQL uses heap files for the primary table storage, not B-trees. In PostgreSQL table data is primarily stored in heap files (unordered collections of page
by greenavocado 1y ago
PostgreSQL uses heap files for the primary table storage, not B-trees. In PostgreSQL table data is primarily stored in heap files (unordered collections of pages/blocks). Indexes (including primary key indexes) use B-trees (specifically B+ trees). When you query a table via an index, the B-tree index points to locations in the heap file
InnoDB uses a clustered index approach. The primary key index is a B-tree. The actual table data is stored in the leaf nodes of this B-tree. Secondary indexes point to the primary key.
One is not better than the other in general terms. InnoDB's clustered B-tree approach shines when:
You frequently access data in primary key order
Your workload has many range scans on the primary key
You need predictable performance for primary key lookups
Your data naturally has a meaningful ordering that matches your access patterns
PostgreSQL's heap approach excels when:
You frequently update non-key columns (less page splits/reorganization)
You have many secondary indexes (they're smaller without primary keys)
Your access patterns vary widely and don't follow one particular field
You need faster table scans when indexes aren't applicable
I personally find PostgreSQL's approach more flexible for complex analytical workloads with unpredictable access patterns, while InnoDB's clustered approach feels more optimized for OLTP workloads with predictable key-based access patterns. The "better" system depends entirely on your specific workload, data characteristics, and access patterns.
- farazbabar 1y agoDon't forget high speed committed writes to append only tables (the opposite of scans), postgres approach is better here as well.
- saltcured 1y agoIt's also deeply entwined with the MVCC concurrency control and the ability to do DDL in transactions, right?
- jiggawatts 1y agoSQL Server supports every combination of heap storage, clustered storage, MVCC, and DDL in transactions.
- Sesse__ 1y agoIndexes that point directly to the disk column are also significantly faster to access; it is a persistent pain point for OLAP on InnoDB that all secondary indexes are indirect. You can work around it by adding additional columns to the index to make your lookups covering, but it's kludgy and imprecise and tends to bloat the index even further. (The flip side is that if you have tons of indexes, and update some unrelated column, InnoDB doesn't need to update those indexes to point to the location of the new row. But I'm generally very rarely annoyed by that in comparison.)
- sroussey 1y agoOn commercial databases from Oracle and Microsoft, you can cluster the DB on any index. Really would love for Postresql to do the same.
- cwbriscoe 1y agoYou can but new rows will not be clustered until you re-cluster. CLUSTER table_name USING index_name;
- sroussey 1y agoI remember that for MySQL MyISAM (syntax was different) in the olden days. I thought Oracle let you do it though. My bad. MySQL Innodb and other have real clustered primary key indexes with all the pluses and minuses that entails.
- foobahhhhh 1y agoTakes me back 20 years to using SQL Server! It let you choose clustered index or not IIRC.