How to Choose a Database Storage Engine for Read and Write Loads

A database storage engine comparison comes down to one question: where does the row live on disk, and what has to happen before a write is acknowledged. InnoDB and Postgres heap storage both write ahead to a log and keep a separate copy of the row, so readers and writers rarely block each other. MyISAM writes the row in place and takes a table level lock, so a single writer stalls every reader on that table. Pick the engine whose concurrency model matches how often your workload writes versus reads, not the one that looks fastest in a single session test.

What a storage engine actually controls

The engine sits below the SQL parser and above the filesystem. It owns the page format, the buffer pool, the locking rules, and the crash recovery path. The server hands it a request to read or write a row and the engine decides how to satisfy that request. Two engines can answer the same SELECT with completely different disk traffic.

In MySQL the engine is chosen per table, so you can mix them in one schema. In Postgres there is one heap storage format for ordinary tables, and alternatives arrive as extensions or table access methods rather than a per table engine keyword. That difference matters when you plan migrations.

InnoDB, MyISAM and Postgres heap: a read and write comparison

InnoDB

InnoDB stores rows in a clustered B+ tree keyed by the primary key, so a primary key lookup walks one tree and finds the row inline. Secondary indexes store the primary key value, which means a secondary index lookup costs an extra descent into the clustered index. Writes go to the redo log first, then dirty pages are flushed by background threads. Row level locking and multi version concurrency control let a reader see the last committed version while a writer works on the current one.

For write heavy tables this is usually the right shape. The cost shows up in the buffer pool: if the working set does not fit, every read that misses goes to disk, and the redo log flush policy decides how much durability you trade for throughput. Check the current settings with:

mysql> SHOW VARIABLES WHERE Variable_name IN
    -> ('innodb_flush_log_at_trx_commit','innodb_buffer_pool_size');
+--------------------------------+------------+
| Variable_name                  | Value      |
+--------------------------------+------------+
| innodb_buffer_pool_size        | 134217728  |
| innodb_flush_log_at_trx_commit | 1          |
+--------------------------------+------------+

A value of 1 for innodb_flush_log_at_trx_commit means every commit waits for the log to reach disk. Raising it trades a small window of lost transactions on a crash for fewer fsync calls. Decide that against your recovery requirements, not against a benchmark you saw somewhere.

MyISAM

MyISAM keeps the table data in one file and the indexes in another, with no transaction log and no row versions. A write takes a table level lock, so concurrent readers wait until the write finishes. Reads are simple sequential or index scans over a flat file, and the index is a plain B-tree pointing at a byte offset. There is no crash recovery beyond a repair pass, and no foreign keys.

That combination suits append only or read mostly data where the table is small enough to stay in the OS page cache, and where a repair after a crash is acceptable. It does not suit a table that takes concurrent inserts from many connections, because the lock serialises them.

Postgres heap storage

Postgres writes rows into heap pages in insertion order, with no clustering by primary key unless you ask for it. Every update writes a new row version and marks the old one dead; a background process called autovacuum reclaims the dead versions later. Readers never block writers and writers never block readers, because each transaction sees a snapshot. The price is table bloat: a hot row updated many times leaves many dead tuples behind until vacuum catches up.

Indexes are separate structures that point at the tuple's physical location, so an index scan costs a heap fetch per row. A covering index with INCLUDE columns can avoid that fetch. Watch the bloat and the vacuum lag directly:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 5;

If n_dead_tup climbs and last_autovacuum stays old, your write rate is outrunning the cleanup and read latency will drift upward as scans walk past dead rows.

How to match the engine to your workload

Start by classifying the table, not the application. A table that takes many small updates to the same rows behaves very differently from one that takes bulk inserts and range scans. For the first, InnoDB's in place update of the clustered index and Postgres's versioned heap both work, but Postgres needs vacuum tuning and InnoDB needs a buffer pool large enough to hold the hot pages. For the second, MyISAM or an append oriented layout can be fine if you can accept table locks and no crash safety.

Then measure with your own queries. Run the read path and the write path separately under concurrency, because a single session test hides every locking effect. Use sysbench or your own harness, and watch the engine's own counters rather than wall clock alone.

sysbench oltp_read_write --tables=4 --table-size=1000000 \
  --threads=16 --time=60 --db-driver=mysql prepare
sysbench oltp_read_write --tables=4 --table-size=1000000 \
  --threads=16 --time=60 --db-driver=mysql run

Compare the transaction rate at increasing thread counts. If throughput flattens as threads rise, you have hit a lock or a log flush bottleneck, and that tells you more than any single number.

Finally, check what the engine does on restart. A storage engine that cannot recover cleanly after a power loss is a liability for any table holding data you cannot rebuild. Read the recovery documentation for the engine you are considering and confirm it matches your tolerance for lost work.

What to do next

List your tables and mark each one as read heavy, write heavy, or mixed, then note whether it needs transactions and crash recovery. Run the concurrency test above against the candidate engine on hardware shaped like production, and read the engine's own status counters during the run. Choose per table where the server allows it, and revisit the choice when the access pattern changes rather than when a migration is forced on you.