TinyPG: I Built a Toy PostgreSQL in 300 Lines of JavaScript

I spent many weekends digging into the internals of SQL databases. I chose PostgreSQL because I wanted to answer one question : how does a database actually stay correct when the power goes out mid-write? Not the marketing answer. The real one.

So I built TinyPG, a toy PostgreSQL in ~300 lines of JavaScript. No SQL parser, no indexes, no replication. Just the four parts that make a database a database, and one rule that ties them together:

A database is a notebook (heap.db), a journal (wal.log), a tiny desk to write on (the buffer pool), and a rule: "journal first, page later." Everything else (MVCC, recovery, isolation) falls out of those four things.

Here's the tour.

The four parts

1. The heap, your notebook (heap.db)

A single file, sliced into fixed 4 KB pages. Real Postgres uses 8 KB, same idea, bigger pages. A page is just bytes:

┌────────────────────────────┐ offset 0
│ magic   (4)  0xDEADBEEF    │
│ num_tuples (4)              │  ← page header (12 bytes)
│ free_offset (4)             │
├────────────────────────────┤
│ tuple 0: header + data      │
│ tuple 1: header + data      │  ← tuples grow downward
│ tuple 2: header + data      │
│                            │
│  ... empty space ...       │
└────────────────────────────┘ offset 4096

Every row has its own little header:

tuple_len (4)  │ xmin (4)  │ xmax (4)  │ data_len (4)  │ data...
                  ↑           ↑
                  creator     deleter (or 0 if alive)
                  txid        txid

Those two numbers, xmin and xmax, are the entire mechanism behind MVCC. Hold that thought.

2. The WAL, your journal (wal.log)

Every change is first appended to wal.log, then applied to the in-memory page. The records look like this (real Postgres uses compact binary; we use JSON so you can cat it):

{"type":"BEGIN","txid":1,"lsn":1}
{"type":"INSERT","txid":1,"data":"{\"id\":1,\"name\":\"alice\"}","lsn":2}
{"type":"COMMIT","txid":1,"lsn":3}

The lsn is the Log Sequence Number, a monotonically increasing id. In real Postgres it's the byte offset into the WAL stream; here it's just a counter, but the meaning is identical.

The critical operation is fsync() on COMMIT. Without it, a power failure between the kernel acknowledging the write and the disk physically storing it would silently lose your commit. fsync is what makes the database durable (the D in ACID). One syscall is doing all the heavy lifting.

3. The buffer pool, your desk

Disk is slow. RAM is fast. So we cache recently-used pages in memory.

TinyPG's pool holds 8 pages (MAX_BUF_PAGES). Production Postgres typically uses 25% of system RAM (shared_buffers). When the pool is full and you need a new page, we evict the least-recently-used one. If the evictee is dirty, we flush it to disk first.

Real Postgres uses clock-sweep (a hash-counter approximation of LRU that avoids contention), but the rule is the same.

4. The transaction manager, your bookkeeper

Hands out monotonically-increasing transaction ids (1, 2, 3, …) and remembers which ones committed.

In TinyPG that's literally a Set<txid>. Postgres calls it the CLOG and stores two bits per txid (in-progress / committed / aborted / sub-committed) in pg_xact/. Same job, fancier name.

Walk through a single INSERT

You type:

BEGIN
INSERT {"id": 1, "name": "alice"}
COMMIT

What actually happens:

1. BEGIN. TxnMgr issues txid = 1, WAL appends a BEGIN record. No fsync. Begin is cheap because if we crash right now there's nothing to recover.

2. INSERT {…}. WAL appends the INSERT record. WAL first. Then we grab a page from the buffer pool, write the tuple header (xmin: 1, xmax: 0) plus the JSON, and mark the buffer dirty. Notice what we did not do: we never flushed the page. The edit lives in RAM. If the process dies right now, the page edit is gone, but the WAL record is on disk. That is enough.

3. COMMIT. WAL appends a COMMIT record and we call fsync() on the WAL file. This is the durability point. After it returns, the OS has told us "yes, the bytes are on physical media." TxnMgr adds 1 to the committed set.

The heap page is still in RAM only. That's fine. The WAL has enough information to reconstruct it. A future checkpoint will eventually flush dirty pages and trim old WAL.

Crash between step 2 and step 3? No commit record. Recovery skips it. The insert never happened. Crash after step 3? Recovery sees the commit and redoes the insert. Either way, the database is internally consistent. That's atomicity.

Walk through a SELECT under MVCC

This is the bit that surprised me most.

A read does not see "the current state" of the database. It sees a snapshot.

Two transactions running side by side:

txA: BEGIN
txA: INSERT {"id": 1}      (in memory only, not yet committed)
                                txB: BEGIN
                                txB: SELECT       → 0 rows
txA: COMMIT
                                txB: SELECT       → 1 row

Why does txB see 0 rows the first time?

When SELECT runs, it calls snapshot(), a frozen copy of the committed set as of right now. At that moment txA's id is not in the committed set. So when we scan the heap and find the tuple with xmin = txA.id, the visibility check fails:

// isVisible(xmin, xmax, snap):
const xminOk = snap.committed.has(xmin) || xmin === snap.myTxid;
if (!xminOk) return false;  // ← we return here for txA's row

The inserter hasn't committed in our snapshot's universe, so the row is invisible. We skip it. txB sees 0 rows.

After txA commits and txB runs SELECT again, txB takes a fresh snapshot. Now txA is in committed, xmax is still 0 (alive), the row is visible. That's READ COMMITTED isolation: a new snapshot per statement. Postgres also offers REPEATABLE READ (one snapshot per transaction) and SERIALIZABLE (one snapshot + anti-anomaly detection). TinyPG sticks with READ COMMITTED.

What about deletes?

When txA deletes a row, we don't erase it. We just set xmax to txA's id. The tuple stays on disk.

Why? Because some concurrent transaction might still hold a snapshot from before the delete. They need to see the row. Only after every snapshot that could possibly see the old version is gone can we physically remove the tuple. That's what VACUUM does in real Postgres. TinyPG just lets dead tuples linger forever. Run SHOW PAGES and you'll see tombstones with xmax = tx7 long after they "vanished" from SELECT. That's MVCC working as designed.

Walk through a crash and recovery

Here's the part that finally made it click for me.

Simulate a crash mid-transaction:

txC: BEGIN
txC: INSERT {"id": 99, "name": "crash-row"}
[process dies. no COMMIT, no close, no flush]

State at crash time:

• wal.log: BEGIN tx3, INSERT tx3 (on disk)
• heap.db: maybe contains tx3's tuple, maybe doesn't (RAM-only edit)
• buffer pool: gone. Process died. RAM lost.

Now restart. new Database() calls _recover():

Pass 1: find committed transactions. Read every WAL record. Build the committed set from records of type COMMIT. tx1 and tx2 committed; tx3 has no COMMIT record. So committed = {1, 2}.

Pass 2: redo. Truncate the heap to a clean slate, then walk the WAL again and re-apply every INSERT whose txid is in committed. tx3's INSERT is skipped because tx3 isn't committed. After recovery, the heap looks exactly like it did before tx3 ever ran. crash-row never existed. Atomicity preserved.

Real Postgres is smarter. It starts replay from the last checkpoint LSN instead of byte 0, and applies WAL records to specific pages instead of truncate-then-redo. But the logic is identical: replay committed work, ignore uncommitted work.

Postgres mapping cheat-sheet

Every concept in TinyPG maps directly to real Postgres source code. The toy and the production system speak the same language.

A page, byte by byte

Here's a 4 KB page right after we insert one row {"id": 1, "name": "x"} in txid = 7:

offset │ bytes                       │ meaning
───────┼─────────────────────────────┼────────────────────────
   0   │ DE AD BE EF                 │ magic (page is valid)
   4   │ 00 00 00 01                 │ num_tuples = 1
   8   │ 00 00 00 26                 │ free_offset = 38
  12   │ 00 00 00 26                 │ tuple_len = 26
  16   │ 00 00 00 07                 │ xmin = 7  ← creator txid
  20   │ 00 00 00 00                 │ xmax = 0  ← alive
  24   │ 00 00 00 0E                 │ data_len = 14
  28   │ {"id":1,"name":"x"}         │ ← the row, as JSON
  38   │ 00 00 00 ... (rest unused)  │ free space

If txid = 9 later deletes this row, byte 20 flips from 00 00 00 00 to 00 00 00 09. The data bytes are untouched. Any reader whose snapshot doesn't yet contain tx9 still sees the row. That's the entire deletion mechanism.

Real Postgres tuples are denser (no per-tuple length, fields stored as columnar bytes, NULLs in a bitmap) but the principle of "header with xmin/xmax + payload" is exactly the same.

Things to try

If you want to feel this stuff in your hands:

1. Watch a row die. INSERT a row, COMMIT, then DELETE WHERE id = 1. SELECT returns nothing. Now SHOW PAGES and the row is still there with xmax set. That's MVCC.
2. Watch the WAL grow. Run SHOW WAL after each command. Notice INSERT and DELETE always appear before their COMMIT.
3. Fill the buffer pool. The pool holds 8 pages. INSERT ~200 rows. Watch pages cycle in and out of SHOW BUFFERS, oldest first.
4. Simulate a crash. In the CLI, BEGIN and INSERT but don't COMMIT. Quit with .exit. Restart and SELECT, your insert is gone. Recovery saw no COMMIT and skipped it.
5. Two-tab MVCC. Open the GUI in two browser windows side by side. Each tab gets its own transaction. Start a transaction in window A, INSERT, don't commit. SELECT from window B and you see zero rows. Commit in A, SELECT in B again, and the rows appear. That's snapshot isolation across two real concurrent sessions.
6. Read the WAL with your eyes. Open wal.log in any text editor. It's newline-delimited JSON. You can literally read the database's mind.

What I actually learned

Five ideas. That's it.

1. WAL-before-page. Every change goes to the journal before it touches the data file. Without this, recovery is impossible.
2. fsync on commit. One syscall is the difference between durable and "mostly durable, sometimes." Skipping it makes everything else a lie.
3. xmin / xmax for MVCC. Two integers per row replace an entire lock manager. Readers and writers stop fighting.
4. Snapshot isolation for reads. A read never sees "now". It sees a frozen view of "what was committed when I started." This is the trick that makes concurrency feel sane.
5. Two-pass redo on recovery. Find what committed. Redo it. Ignore everything else. That's it. That's the whole recovery algorithm.

Production Postgres is 1.6 million lines of C, and most of them are about making these five ideas faster, smaller, more concurrent, and more correct in edge cases I haven't even imagined. But the core fits in 300 lines of JavaScript. And once you've written those 300 lines, the rest of the database internals literature suddenly reads like English.

If you've been curious about what's actually inside a database, write a tiny one. There's no shortcut, but there's also no mystery once you have.

← Back to posts