← Abdul Basit Sajid
pgoverlay · case study

A copy-on-write system that copied everything

A branch of a 5 GiB database held 5.05 GiB before it ran a query, and a single SELECT copied a 488.5 MiB table into a branch that had only read it. One cause both times: Postgres opens files read-write even to read them, and OverlayFS copies a file whole the moment it is opened that way.

Go · Postgres · OverlayFS · Linux · github.com/abd-ulbasit/pgoverlay

The problem

pgoverlay gives you a Postgres database per pull request. You seed one dataset once, and on the default backend every branch after that is a stock postgres container whose data directory is an OverlayFS mount (the layered filesystem behind Docker's default image storage): the seed read-only underneath, shared by every branch, and an empty writable layer on top, meant to hold only what that branch changes. The whole design rests on the copying that does not happen.

So the number that matters is the size of that writable layer. It decides whether a database per pull request is affordable, and it went wrong twice.

The first copy: 5 GiB before a single query

The first benchmark, a median of five runs in a Colima VM on an M1 Pro, branched a 5.00 GiB database in 61.9 seconds and left 5.05 GiB in the branch's writable layer. No queries had run and no rows had been written; the branch had only started. The 1 GiB dataset did the same: 1.00 GiB of data, 1.05 GiB of writable layer. A partial copy does not land on the dataset size at two scales. A full copy does.

I suspected crash recovery: the seed is a pg_basebackup copy, so every branch starts by recovering it. The log said otherwise: redo done ... elapsed: 0.00 s. Replay took no time, so the minute went somewhere before it. Before replaying its write-ahead log, Postgres runs SyncDataDirectory, which flushes every file in the data directory to disk and opens each one read-write to do it. On OverlayFS, opening a file from the read-only seed that way copies the whole file into the branch, on the open, before a byte is written. The durability pass copied the dataset.

A plausible story is not a diagnosis, so I reran it with one flag changed: recovery_init_sync_method=syncfs, which flushes the whole filesystem in one call instead of opening each file. The writable layer came out at 16 KiB when recovery finished, and the flag became the fix: creating a branch took 1.89 s and left 33.1 MiB, almost all of it log written at the end of recovery, at both 1 and 5 GiB. The flag needs Postgres 14 or later on Linux, so pgoverlay refuses Postgres 13 and older.

A SELECT is a read-write open

The next thing I measured was a branch that only reads. On a 488.5 MiB table, frozen on the source so that reading it had almost nothing to write back, one SELECT count(*) grew a branch from 34.6 MiB to 523.1 MiB. The file that appeared in the writable layer was the whole table.

It is the same mechanism on the ordinary read path. Postgres stores each table and index in files of up to 1 GiB, and opens every one of them read-write, whatever the query is going to do. So a branch copied every file it read, and the first query waited for the copy: in a later run on a similar table, the first SELECT count(*) took 19.4 s and the second 116 ms. No recovery setting reaches this: it is how stock Postgres opens a file to read it.

The options, measured before building one

I ran every candidate that could run on one host, against one table.

  • OverlayFS metacopy and fuse-overlayfs copied exactly the same bytes: both copy the data on any open with write access.
  • A loopback XFS pool gave block-level copy-on-write on a plain ext4 host, but synchronous writes on it ran at a third to two thirds of ext4's rate, and it needs a privileged loop device that survives reboots. Deferred, not rejected.
  • btrfs snapshots, dm-thin and qcow2 are block-level too, but need privileges, kernel modules desktop VMs may lack, and fragile set-up.
  • A patched Postgres would give up the stock images and mean a fork of every supported major version. Rejected without building it.

What was left was to change what the kernel is told, not what Postgres does.

What v1.0.0 does

  • Open read-only until the first write. A small library preloaded into each branch's Postgres (LD_PRELOAD) turns its read-write opens of table files into read-only ones, so OverlayFS copies nothing. At a file's first write it reopens the file read-write on the same descriptor, and Postgres never notices. Each branch checks at start that its kernel (Linux 4.19 or later) makes this safe for Postgres's other processes; if it does not, the branch copies on open and reports it.
  • Do the writes a read can cause once, in the seed. A read in Postgres can write: the first read of a row records on the page that its transaction committed, and reads prune old row versions. A fresh pg_basebackup copy also has to recover before it opens. pgoverlay recovers, freezes, analyses and cleanly shuts down each seed once, so a read in a branch has no recovery to run and no commit flags to set.
  • Clone instead of copy where the disk allows. On XFS with reflinks, or on btrfs, OverlayFS clones the file instead of copying it, and the filesystem then copies only the blocks that change: on my test machine, a one-row UPDATE of a 113 MB table cost the branch 40 KiB. pgoverlay detects which it has at startup, and on a host whose own disk is ext4 it can put its volumes on an XFS or btrfs disk.

The release gate

I set one gate for the release: with the library, warm read-only pgbench throughput had to stay within noise of copying on open, and warm TPC-B within 5%. It ran on dedicated GitHub-hosted amd64 and arm64 VMs, five rounds each with the order rotated every round, so any drift on the host hit every configuration alike.

amd64, medians of five rounds, pgbench scale 50 plus a 949 MiB table. arm64 ran the same and passed the same gate. Raw runs, hosts and what was not measured are in the repository.
Copy on open v1.0.0
Branch after a read-only run, disk not shared with the seed 753.1 MiB 1.6 MiB
Read-only tps, warm 118,064 116,833
TPC-B tps, warm 11,823 11,648

Beyond throughput, on amd64, I killed Postgres in the middle of TPC-B: it restarted with its balances intact and pg_amcheck, Postgres's corruption check, clean. On Postgres 14 to 18 and the Alpine image, every read copied nothing.

What it still costs

On ext4, the first write to a table file copies the file. Up to 1 GiB, once per file per branch, and the wait lands where Postgres next writes the page to disk, usually the checkpoint: on the amd64 runner, the checkpoint after a one-row UPDATE of a 949 MiB table took 5.51 s (4.09 s on arm64). XFS or btrfs volumes make that a clone.

A branch that cannot run the library copies on read. A kernel that fails the check, or an image with no build of the library, falls back to copying on open, and the branch reports it.

Also: branch containers need CAP_SYS_ADMIN to mount their overlay (the Kubernetes CSI mode needs no added capabilities); branches do not follow the source after seeding; crash testing killed the process and did not cut power; XFS on a fast disk and btrfs were not measured under pgbench; and the library was not measured on Docker Desktop, Colima, OrbStack or Kubernetes hostPath.

How it was built

Most of the commits were written with coding agents, which I run in parallel git worktrees; I review, benchmark and integrate what they write, and the repository says so. The benchmark file keeps every number, including the ones that contradicted the design.