Database Engines 101: How They Work, the Main Types, and How to Swap Them in PostgreSQL

🧪 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 with CREATE TABLE ... USING <engine>.

Table of Contents

1. Part 1: How an engine works
2. Part 2: Types of engines
3. Part 3: The lab
4. 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:

1. 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.
2. 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.
3. A crash creates a row that never existed. Cut power halfway through replacing 42,alice,1000 with 42,bob,2000 and you get 42,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:

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

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.

Further reading

• PostgreSQL: Database Page Layout
• PostgreSQL: Table Access Method Interface
• PostgreSQL: BRIN Indexes
• PostgreSQL: Write-Ahead Logging
• Citus columnar storage
• MySQL: Alternative Storage Engines