Most of the schema-design instincts a MySQL engineer carries around are downstream of a single InnoDB decision: the table is the primary key. Rows live inside a B-tree ordered by the primary key, and everything else, secondary indexes, covering strategies, even advice like "keep your primary key small and monotonic," follows from that. PostgreSQL made the opposite decision. Tables are heaps, rows live wherever there is space, and every index, primary key included, is a separate structure pointing into the heap.
Neither design is simply better. But if you bring InnoDB instincts to PostgreSQL unexamined, you will over-engineer some things and under-engineer others. This article walks through what actually changes.
How InnoDB stores a table
In InnoDB, every table is an index-organized table. The clustered index is built on the primary key; if you do not declare one, InnoDB uses the first unique index over NOT NULL columns, and failing that generates a hidden six-byte row identifier. Leaf pages of the clustered index contain the full rows, in primary key order.
This buys real advantages. A primary key lookup lands directly on the row with a single B-tree descent. Range scans over the primary key read physically adjacent pages, which is why ORDER BY id LIMIT patterns and time-clustered scans are so cheap when the key is monotonic. The cost is symmetrical: inserting in random key order, the classic UUIDv4 primary key mistake, splits pages across the whole tree and churns the buffer pool, and every secondary index carries a copy of the primary key as its row pointer.
That last point deserves emphasis because it is the least visible. An InnoDB secondary index lookup is two B-tree descents: one down the secondary index to find the primary key value, then one down the clustered index to find the row. And the size of the primary key is physically embedded in every secondary index entry.
How PostgreSQL stores a table
A PostgreSQL table is a heap: a bag of pages with rows placed wherever free space allows, addressed by a tuple identifier, the ctid, which is just a page number and an item slot. Every index on the table, including the primary key's underlying unique index, is an independent B-tree whose leaf entries point at heap TIDs. The primary key is not privileged storage; it is a constraint plus an ordinary index.
The consequences invert the InnoDB trade. A lookup through any index costs the same shape of work: one index descent, then a heap fetch by TID, one page read, no second tree to walk. Insert order and key randomness do not reorganize the table, so a UUID primary key does not shred table storage the way it shreds a clustered tree, though the index on it still suffers from random insertion. The heap has no inherent order, so nothing guarantees that rows adjacent in key order are adjacent on disk; the CLUSTER command can physically reorder a table by an index once, but PostgreSQL does not maintain that order afterward.
There is one PostgreSQL-specific cost to know about: because of MVCC, updates write new row versions, and ordinarily every index needs a new entry pointing at the new version. The HOT optimization avoids the index writes when no indexed column changed and the new version fits on the same heap page, which is a large part of why over-indexing hurts PostgreSQL write performance more than people expect. The PostgreSQL overview covers the MVCC model in more depth.
Secondary index lookups: double descent vs heap fetch
For point queries through a secondary index, the two designs land closer than the architecture diagrams suggest: both do an index traversal plus one more step. The differences show up at the edges. In InnoDB, the second step is another logarithmic descent, but it benefits from the clustered tree's hot upper levels living in the buffer pool. In PostgreSQL, the second step is a single page fetch, but a range scan through a secondary index can hit many scattered heap pages. PostgreSQL mitigates the scatter with bitmap heap scans, which sort TIDs into physical page order before fetching.
Where the designs diverge sharply is what happens when the index alone could answer the query.
Covering indexes vs index-only scans and the visibility map
In MySQL, a covering index is a pure index-design question: if every column the query needs is in the secondary index, including the implicit primary key columns riding along in every entry, InnoDB answers from the index and EXPLAIN says Using index. It works unconditionally.
PostgreSQL has the same concept, the index-only scan, with an extra runtime condition attached. Heap tuples carry the visibility information in PostgreSQL's MVCC design, and the index does not know which entries point at rows visible to your snapshot. The planner works around this with the visibility map: for heap pages marked all-visible, no heap check is needed; for anything else, the executor must fetch the heap page anyway, which EXPLAIN ANALYZE reports as Heap Fetches. Vacuum is what sets the all-visible bits, so index-only scans are fast on well-vacuumed, read-mostly tables and quietly degrade on write-heavy ones.
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at)
INCLUDE (total_amount);
EXPLAIN (ANALYZE, BUFFERS)
SELECT created_at, total_amount
FROM orders
WHERE customer_id = 42
AND created_at >= now() - interval '30 days';The INCLUDE clause adds payload columns to the index leaf level without making them part of the key, which is the closest PostgreSQL analog to designing a covering secondary index in MySQL. If the plan shows an Index Only Scan with a high Heap Fetches count, the index design is fine and the vacuum schedule is not; check when the table was last vacuumed before adding more columns to the index.
SELECT relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';Primary key size stops being a tax
The InnoDB rule that a fat primary key inflates every secondary index simply does not apply to PostgreSQL. Heap TIDs are the same size no matter what your primary key looks like, so a composite natural key or a text key costs you only its own index, not a multiplier across all of them. Monotonic-key discipline also relaxes: UUID keys are a smaller problem on a heap, though on both engines a time-ordered identifier such as UUIDv7 is kinder to the index that stores it than a fully random one.
The instinct to keep in modified form is index minimalism, for a different reason. In InnoDB, extra secondary indexes cost you primary-key copies and change buffering; in PostgreSQL they cost you write amplification by defeating HOT updates and slowing every non-HOT update. Also drop the habit of adding the primary key to the end of a secondary index to "cover" a query; in InnoDB it was already there implicitly, and in PostgreSQL you use INCLUDE deliberately when a measured query needs it. When you are unsure which indexes actually earn their keep against a real workload, a PostgreSQL index advisor grounded in observed queries beats porting the MySQL index list wholesale.
Habits to keep, habits to drop
Keep: small, stable primary keys as a default, not because of secondary index bloat but because narrow unique indexes are cheap and foreign keys reference them. Keep: designing indexes around real query shapes rather than columns in isolation. Drop: assuming the table is ordered by primary key, because no heap query has an inherent order and any query relying on it needs an explicit ORDER BY. Drop: treating covered queries as a pure schema property; in PostgreSQL they are a schema property multiplied by vacuum health. And add one new habit that has no MySQL equivalent: watch the visibility map's effects, because an index-only scan that silently became a heap-fetching scan is a regression that no schema diff will ever show you.
Where MonPG fits after the move
Most of the PostgreSQL-specific risks in this article are invisible in a schema review and obvious in monitoring: Heap Fetches climbing on a hot index-only scan, dead tuples accumulating ahead of autovacuum, an index that stopped being used after a plan change, HOT update ratios collapsing after someone indexed a frequently updated column. MonPG watches exactly this layer, and only this layer; it is a PostgreSQL-only tool, so once your migration lands, it tracks index usage, vacuum and bloat signals, and query family history in one place. If you are building your post-migration checklist, start with PostgreSQL monitoring and make vacuum health and index usage first-class dashboards rather than things you query by hand during incidents.