🧪 This article has a hands-on lab. You'll run PostgreSQL in Docker, store the same 5 million rows in
two different storage engines, crash the database on purpose, and measure what changes.
If you already know the theory, skip straight to the lab.TL;DR: A database engine is the part of a database that turns "save this row" into bytes on disk and back again.
There's no single best engine. Row stores suit transactions, LSM trees suit heavy writes,
column stores suit analytics and in-memory engines suit speed.
PostgreSQL lets you choose per table withCREATE TABLE ... USING <engine>.
Table of Contents
- Part 1: How an engine works
- Part 2: Types of engines
- Part 3: The lab
- Cheat sheet: pick an engine by use case
Part 1: How an engine works
When you run INSERT INTO users VALUES (1, 'alice', 1000), two very different pieces of software are involved:
SQL text
│
▼
┌──────────────────────────┐
│ Query layer │ parse → plan → execute
│ "what do you want?" │
└────────────┬─────────────┘
│ "store this row", "give me row X"
▼
┌──────────────────────────┐
│ Storage engine │ pages, rows, logs, versions
│ "how do the bytes live?"│
└────────────┬─────────────┘
▼
disk / S3
This article is about the bottom box. The easiest way to see why it looks the way it does is to build the obvious version and watch it fail.
The naive engine: one row per line in a text file
with open("users.csv", "w") as f:
for i in range(200_000):
f.write(f"{i},user_{i:06d},{i * 7 % 100000}\n")
On my machine it failed three ways almost immediately:
- Read cost depends on which row you want. Finding row N means counting N newlines. Row 0 took 0.25 ms and row 199,999 took 17.6 ms, 70x slower, on a file under 5 MB.
- A row that grows overwrites its neighbour. The only safe fix is to rewrite everything after it. I changed 40 bytes and had to write 4.6 MB, a 121,667x write amplification.
-
A crash creates a row that never existed. Cut power halfway through replacing
42,alice,1000with42,bob,2000and you get42,bobce,1000. It parses fine, so nothing warns you.
Real engines solve these with four ideas.
Idea 1: Pages
Split every file into fixed-size blocks called pages. PostgreSQL uses 8 KB. Finding block 5,000 becomes plain arithmetic (5000 × 8192), so every read costs the same. The page becomes the unit of disk I/O, caching and crash recovery.
Idea 2: Slotted pages
Inside each page, a small slot array at the front points to rows stored at the back. The two regions grow toward each other:
byte 0 ┌────────────────────────────────┐
│ Page header (24 bytes) │
├────────────────────────────────┤
│ Slot 1 │ Slot 2 │ Slot 3 │ ... │ ← slots grow DOWN ↓
├────────────────────────────────┤
│ free space │
├────────────────────────────────┤
│ ... │ Row 3 │ Row 2 │ Row 1 │ ← rows grow UP ↑
byte 8192 └────────────────────────────────┘
A row's address is (page, slot), not a byte offset. So the engine can move rows around inside a page to reclaim space, and every index entry pointing at (5, 3) still works.
Idea 3: Checksums and the write-ahead log
Checksums detect damage: each page stores a checksum of its own bytes, so a half-written page fails verification instead of returning 42,bobce,1000.
The write-ahead log (WAL) repairs damage. Before touching a page, the engine appends a description of the change to a sequential log and flushes it to disk:
1. append "page 5, slot 3: balance 1000 → 2000" to the WAL → fsync
2. change page 5 in memory
3. write page 5 to disk ... eventually
If the database crashes before step 3, recovery replays the log. You'll watch this happen in the lab.
Idea 4: MVCC
Instead of overwriting a row, write a new version and stamp each version with the transaction that created it (xmin) and the one that replaced it (xmax). Each transaction reads from a snapshot, so a long report keeps seeing the old balance while an update commits alongside it. Readers never block writers. The cost is that old versions pile up and must be cleaned out, which is what PostgreSQL's VACUUM does.
Part 2: Types of engines
Those four ideas describe PostgreSQL's default engine. Other engines make different trade-offs, depending on what they're optimised for.
Row-oriented store (B-tree + pages)
Whole rows are kept together in pages and updated in place, with B-tree indexes to find them. The classic design.
- Great at: transactions, point lookups, frequent updates (OLTP).
- Weak at: scanning a few columns across billions of rows, because it reads whole rows anyway.
-
Examples: PostgreSQL
heap, MySQL InnoDB, SQLite, Oracle, SQL Server.
Log-structured merge tree (LSM)
Never update in place. Writes go to an in-memory buffer (plus a log for safety). When the buffer fills, it's flushed to disk as an immutable sorted file, and background compaction merges those files over time.
- Great at: very heavy write and ingest rates, since every disk write is sequential.
- Weak at: reads may check several files (Bloom filters help), and compaction uses background CPU and I/O.
- Examples: RocksDB, LevelDB, Cassandra, ScyllaDB, MyRocks, CockroachDB's Pebble.
Column-oriented store
Store each column separately and compress it. A query that needs 2 of 12 columns reads only those 2.
Row store (one page) Column store
┌──────────────────────────┐ id: 1 2 3 4 ...
│ 1 │ IN │ 12.50 │ chrome │ country: IN US IN DE ... ← compresses
│ 2 │ US │ 8.00 │ safari │ amount: 12.5 8.0 3.2 ... very well
│ 3 │ IN │ 3.20 │ chrome │ browser: ...
└──────────────────────────┘
- Great at: analytics such as aggregates, dashboards and scans over huge tables (OLAP). Much smaller on disk.
- Weak at: updating or deleting single rows, and fetching whole rows one at a time.
- Examples: ClickHouse, DuckDB, Snowflake, BigQuery, Parquet files, Citus/Hydra columnar for PostgreSQL.
In-memory store
Keep everything in RAM. Durability comes from snapshots and append-only logs, or you give it up.
- Great at: microsecond latency for caches, sessions, leaderboards and queues.
- Weak at: RAM is expensive, and durability is weaker unless you configure it.
-
Examples: Redis, Memcached, MySQL
MEMORY, VoltDB.
Pluggable engines
Some databases let you choose the engine per table. MySQL made this famous:
CREATE TABLE orders (...) ENGINE=InnoDB;
CREATE TABLE cache (...) ENGINE=MEMORY;
PostgreSQL has had the same thing since version 12, through the Table Access Method API:
CREATE TABLE orders (...) USING heap; -- the default row store
CREATE TABLE events (...) USING columnar; -- added by an extension
It goes further than tables:
| What you can swap | Syntax | Built in | Added by extensions |
|---|---|---|---|
| Table engine | CREATE TABLE ... USING x |
heap |
columnar (Citus, Hydra), orioledb
|
| Index engine | CREATE INDEX ... USING x |
btree, hash, gin, gist, spgist, brin
|
hnsw, ivfflat (pgvector) |
| Durability | CREATE UNLOGGED TABLE |
skip the WAL entirely | — |
Now let's plug some in.
Part 3: The lab
You need: Docker, and about 15 minutes. The table load takes about a minute.
We'll use the citusdata/citus image: stock PostgreSQL 17 with the columnar table engine already installed. We're only using its columnar engine, not its distributed features.
Step 0: Start PostgreSQL
docker run -d --name pg-engines -e POSTGRES_PASSWORD=pw citusdata/citus:13.0
docker exec -it pg-engines psql -U postgres
Inside psql, turn on timing:
\timing on
Step 1: See which engines are installed
PostgreSQL lists every engine in the pg_am catalog table ("am" = access method):
SELECT amname AS engine,
CASE amtype WHEN 't' THEN 'table' ELSE 'index' END AS kind
FROM pg_am ORDER BY kind DESC, amname;
engine | kind
----------+-------
columnar | table ← added by the extension
heap | table ← PostgreSQL's default
brin | index
btree | index
gin | index
gist | index
hash | index
spgist | index
Step 2: Same table, two engines
A realistic 12-column web-events table. The only difference between the two is the USING clause:
CREATE TABLE events_row (
id bigint, ts timestamptz, user_id int, country text, amount numeric(10,2),
device text, browser text, page_url text, referrer text,
session_id uuid, status smallint, latency_ms int
) USING heap;
CREATE TABLE events_col (LIKE events_row) USING columnar;
Load 5 million rows of fake events into both:
INSERT INTO events_row
SELECT g,
'2026-01-01'::timestamptz + g * interval '1 second',
((g::bigint * 7919) % 100000)::int,
(ARRAY['IN','US','DE','BR','JP'])[1 + g % 5],
(g % 10000) / 100.0,
(ARRAY['mobile','desktop','tablet'])[1 + g % 3],
(ARRAY['chrome','firefox','safari','edge'])[1 + g % 4],
'/products/' || (g % 5000) || '/details?ref=campaign_' || (g % 97),
'https://www.example-' || (g % 300) || '.com/search?q=item' || (g % 1000),
md5(g::text)::uuid,
(ARRAY[200,200,200,404,500])[1 + g % 5],
(g % 900) + 20
FROM generate_series(1, 5000000) g;
INSERT INTO events_col SELECT * FROM events_row;
VACUUM ANALYZE events_row;
ANALYZE events_col;
Now compare their size on disk:
SELECT c.relname AS "table", a.amname AS engine,
pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c JOIN pg_am a ON a.oid = c.relam
WHERE c.relname LIKE 'events_%';
table | engine | size
------------+----------+--------
events_col | columnar | 127 MB
events_row | heap | 868 MB
Same data, 6.8x smaller. Storing each column together means similar values sit next to each other, and they compress well.
Step 3: Run an analytics query
Average order amount per country. BUFFERS shows how many 8 KB pages each engine had to read:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT country, round(avg(amount), 2) FROM events_row GROUP BY country;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT country, round(avg(amount), 2) FROM events_col GROUP BY country;
Trimmed output:
-- heap
-> Parallel Seq Scan on events_row
Buffers: shared read=111093 ← 868 MB of pages
Execution Time: 852 ms
-- columnar
-> Custom Scan (ColumnarScan) on events_col
Columnar Projected Columns: country, amount
Buffers: shared hit=3164 read=16 ← 25 MB of pages
Execution Time: 1409 ms
The columnar engine read 35x fewer pages. Projected Columns: country, amount shows why: it skipped the other 10 columns entirely.
But look at the times. On my laptop, heap was still faster. The whole dataset fits in RAM, so reading pages is cheap. PostgreSQL also split the heap scan across 3 CPU cores, while this columnar engine scans on one.
💡 That's the real lesson: an engine is a bet on your bottleneck. Columnar wins when I/O is the limit, for
example tables bigger than RAM, network-attached cloud disks, or storage you pay for by the byte scanned.
When everything is already in memory, the difference shrinks, and a well-parallelised row store can win.
Step 4: Update one row
UPDATE events_row SET status = 500 WHERE id = 42;
UPDATE events_col SET status = 500 WHERE id = 42;
UPDATE 1
ERROR: UPDATE and CTID scans not supported for ColumnarScan
The columnar engine refuses. Compressed column chunks can't be cheaply edited in place, so this engine is append-only. It suits event logs and history tables, not a users table.
Step 5: Turn durability down with UNLOGGED
The WAL from Part 1 has a cost. PostgreSQL lets you skip it per table:
CREATE TABLE clicks_safe (LIKE events_row);
CREATE UNLOGGED TABLE clicks_fast (LIKE events_row);
SELECT pg_current_wal_lsn() AS before \gset
INSERT INTO clicks_safe SELECT * FROM events_row LIMIT 1000000;
SELECT pg_current_wal_lsn() AS middle \gset
INSERT INTO clicks_fast SELECT * FROM events_row LIMIT 1000000;
SELECT pg_current_wal_lsn() AS after \gset
SELECT pg_size_pretty(pg_wal_lsn_diff(:'middle', :'before')) AS wal_written_safe,
pg_size_pretty(pg_wal_lsn_diff(:'after', :'middle')) AS wal_written_fast;
wal_written_safe | wal_written_fast
------------------+------------------
199 MB | 0 bytes
The unlogged insert wrote zero bytes of WAL and ran about 1.5x faster (1.5 s vs 2.3 s). Both tables hold 1,000,000 rows.
Step 6: Crash it on purpose
Quit psql (\q), then kill PostgreSQL the hard way. SIGKILL gives it no chance to clean up, like pulling the power cord:
docker kill -s KILL pg-engines
docker start pg-engines
docker logs pg-engines 2>&1 | grep -E "not properly shut down|redo"
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/57A9DD88
LOG: redo done at 0/57A9DE58
That's the WAL replay from Part 1. Now reconnect and count:
SELECT (SELECT count(*) FROM clicks_safe) AS safe_rows,
(SELECT count(*) FROM clicks_fast) AS fast_rows;
safe_rows | fast_rows
-----------+-----------
1000000 | 0
The unlogged table is empty. With no WAL to replay, PostgreSQL can't know whether its pages are consistent, so after a crash it truncates them. It's a good fit for staging tables and caches you can rebuild. It's a bad fit for anything you can't afford to lose.
Step 7: Swap index engines
Indexes are pluggable too. Put a B-tree and a BRIN index on the same timestamp column:
CREATE INDEX events_ts_btree ON events_row USING btree (ts);
CREATE INDEX events_ts_brin ON events_row USING brin (ts);
SELECT indexrelid::regclass AS index,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index WHERE indexrelid::regclass::text LIKE 'events_ts_%';
index | size
-----------------+--------
events_ts_btree | 107 MB
events_ts_brin | 40 kB
The BRIN index is about 2,700x smaller. A B-tree stores one entry per row. BRIN ("Block Range INdex") stores only the min and max timestamp for each group of 128 pages. Since events arrive in time order, those ranges barely overlap, so the index can still skip most of the table.
Both indexes answer "give me Feb 1st" correctly (86,400 rows). BRIN is lossy: it returns whole page ranges, and PostgreSQL rechecks the rows, discarding 11,028 extras. If your data isn't physically ordered by that column, BRIN becomes useless and you're back to the B-tree.
Step 8: Change a table's engine later
You don't have to choose the engine forever. Since PostgreSQL 15, one command moves an existing table to a different engine:
SELECT pg_size_pretty(pg_total_relation_size('clicks_safe')); -- 174 MB
ALTER TABLE clicks_safe SET ACCESS METHOD columnar;
SELECT pg_size_pretty(pg_total_relation_size('clicks_safe')); -- 25 MB
ALTER TABLE clicks_safe SET ACCESS METHOD heap; -- and back again
This is the "plug and play" part. The table name, the columns and your queries stay the same. Only the engine underneath changes. The catch is that it rewrites the whole table and locks it while doing so, so plan it like a migration.
You can also change the default for new tables:
SET default_table_access_method = columnar;
Clean up
docker rm -f pg-engines
Cheat sheet: pick an engine by use case
| Use case | Pick | Why |
|---|---|---|
| Users, orders, payments |
heap + btree
|
Frequent updates, point lookups, full durability |
| Event logs, metrics history, audit trails | columnar |
Append-only, scanned by a few columns, ~7x smaller |
| ETL staging, rebuildable caches |
UNLOGGED heap |
No WAL, faster writes. Emptied after a crash |
| Huge time-ordered tables, range filters |
brin index |
Kilobytes instead of 100+ MB |
| Full-text search, JSONB, arrays |
gin index |
Indexes the values inside a column |
| Maps and geometry (PostGIS) |
gist index |
Handles overlaps and nearest-neighbour queries |
| AI embeddings, similarity search |
hnsw (pgvector) |
Approximate nearest-neighbour search |
| Massive write-heavy key-value data | LSM engine (RocksDB, Cassandra) | Sequential writes and compaction |
| Sub-millisecond cache or session store | In-memory (Redis) | RAM speed; durability is optional |
Want to go deeper?
I'm learning this by building one: LakePG is a from-scratch Python storage engine that writes byte-for-byte PostgreSQL-compatible pages, with slotted pages, checksums, tuples and MVCC. Its tests compare the pages it writes against a real PostgreSQL 17 server. It's a learning reimplementation of ideas from PostgreSQL, Neon and Databricks Lakebase, not an original design and not production software.
Top comments (1)
Great breakdown! Really liked how you explained database engines in a simple way while also covering the practical PostgreSQL side. The section on swapping engines was especially useful. Nice read and really helpful starter for anyone getting into database fundamental! 👏