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