One Postgres Database Over Many Postgres Servers

The short version

• It scales. Eight 4-vCPU servers behind one router: 76,799 writes a second against 12,169 on one, 6.3 times.
• It matches Postgres. 3,500 generated queries against plain Postgres: 0 wrong.
• It survives broken servers. A live cluster broken 21 ways under load: 0 acknowledged writes lost wherever it heals itself.
• It moves data cheaply. A quarter of a node moved on 107 MB of source WAL, against 1,584 MB row by row.
• It refuses before it guesses. The error names the fix.

When each result was measured

Lepis changes every week, so we do not rerun everything. Each number is what one build did on one day. How it works describes router 0.1.25.

Chunks became the default on November 1, after the scale-out. The raw output is public.

How it works

⤢ Expand

The pieces

• The router speaks the Postgres protocol and holds only connections. Run as many as you like.
• A distribution key, say customer_id, picks each row's node. Tables split by row, never by column. Tables sharing a keyspace join on one node.
• Ranges cut the hash space. Each has one owner, and they split, merge and move.
• Chunks make up a range: plain tables with their own indexes, 64 per keyspace by default.
• Reference tables are small and copied whole to every node.
• The home node holds everything else and the catalog. No etcd, no extra service.

Three things it does not trust

• Its reading of SQL. Postgres's parser (libpg_query) reads every statement. What it cannot parse is refused.
• Its hash. Postgres's extended hash, ported to Rust: 0 differences on 101 million values.
• Its routing. Each chunk has a CHECK on that hash, so a stale router, a bug or psql cannot put a row on the wrong node.

Moving data

1. Copy. The target subscribes to the moving chunks by logical replication.
2. Pause. Only those chunks lock, 2 seconds at most by default. A job that would run over retries.
3. Switch. The source's copies retire, the target's attach, the catalog moves. Waiting statements get a 40001 to retry.
4. Clean up. DROP TABLE. No DELETE, no VACUUM, and the disk comes back.

In the Cloud, a new node can start from the source's S3 backup, then drop the chunks it does not own.

Queries across nodes

Join paths apply to a single SELECT outside a transaction, and every limit is a setting. If a router dies mid-commit, another finishes: a logged decision completes, and no decision after 30 seconds rolls back.

What we broke on purpose

A client wrote known keys, read fixed answers and moved money across nodes through the public address. A checker then compared every table with the client's log. Lost means told committed, and not there. Keys out runs from first failure to first success.

Everywhere else, nothing was duplicated or half applied. In the first run, keys on the other data nodes never failed.

The other drills

What those drills caught

• The last 44 seconds. A node died with its replica behind, and promotion lost 630 writes. Failover now saves the node's WAL first, or waits for a person. Rerun: 0 lost.
• The backup that kept the damage. pgBackRest copies a bad page with a warning and a clean exit, so a restored replica carried it. Replicas now use the newest clean backup.
• 797 writes. The first disk drills lost 797 and 301 to two bugs of ours. Fixed; the rerun lost none.
• The 37-second stall. Growing a table to 40 million rows froze a node's clients: an ALTER TABLE queued behind a COPY. Fixed, the slowest transaction took 1,059 ms.
• One wrong answer. The generated corpus caught a NOT EXISTS going wrong on the new join path, before it was committed.

On the chunked build, 500 generated queries: 493 equal, 7 refused, 0 wrong. All 40 million rows came back exact.

How fast

Spreading the data

Eleven EC2 hosts: eight 4-vCPU nodes, two 8-vCPU routers, one client. 10 million accounts, every transaction on one node. Peak transactions a second, 32 to 512 clients.

• To 4 nodes, the nodes are the limit, at 94 to 100% CPU.
• At 8, one router is the limit: 68 to 76% of the nodes alone, 78 to 83% with two routers.
• The catch. The 8-node write and TPC-B peaks had the home at 1,200 connections. At 600: 62,551 and 39,322.

The price of the extra hop

One node behind the router, all on one shared 8-vCPU host. Cells: tps / p50 ms / p99 ms.

• Costs: simple reads, 53% of direct at 16 clients, about 71% at 64.
• Costs little: TPC-B keeps 97% of direct.
• Loses: p99 at 64 clients, 2.17 ms against the pooler's 1.40, unexplained. Session mode's TPC-B p99 is 12.5 ms.

Moving data and running joins

One 4-vCPU box in Docker, 10 million rows per table. Compare the columns; they are not capacity numbers.

• Joins: a broadcast took 956 ms against 528 ms on one Postgres. Two 10 million row tables off the key: 22.2 s against 2.4 s.
• Other pauses: logical moves, one table per node, p99 0.21 s on Postgres 18. A move by promotion on the fleet, no writers: 736 to 875 ms.

What it does not do

• The home node is the single point. In the Cloud a broken home fails over in about two minutes, every key out. A home that hangs or is cut off is not covered there.
• Losing a whole host loses no acknowledged write on Pro and above while the second-zone copy is up, but stops the cluster until its nodes are restored.
• Not distributed SQL, not multi-master. Each node is a whole Postgres, and every row has one owner.
• No global snapshot across nodes.
• A router behind on the catalog can serve stale reads for at most 10 seconds, then stops. The node refuses its writes.
• Refused across nodes: INSERT ... SELECT, SERIALIZABLE, savepoints.
• Table rules: unique keys include the distribution key, plain serial is refused, partitioned tables stay on the home node.
• Not measured: scale-out on chunks, transactions across nodes, skewed keys, a fixed-rate run.
• Open defect: a session's first prepared transaction commits in two phases on one node. Past about 300 clients the home ran out of connections.
• Unexplained: a node spends 17% more CPU per transaction behind the router.
• Advisor thresholds are chosen, not measured. The autopilot is off until you turn it on.

Where it sits

Postgres spreads three ways: a new storage engine, an extension in every server, or a router in front of plain servers. Lepis is the third.

• Nothing to install. Any Postgres 17 or 18, with max_prepared_transactions above zero and wal_level = logical.
• The node enforces ownership, not only the router.
• Moves end in DROP TABLE, not a delete and a vacuum.
• In the Cloud, a new node can be an S3 restore, not a copy off the live primary.

Run it yourself

From the public snout-lepis repository. It needs Docker; run benchmarks on a throwaway host.

bash scripts/dev.sh cargo test -p snout-lepis --lib     # unit tests
bash scripts/it.sh 18                                   # every cluster test and the oracle
bash scripts/hash-check.sh                              # the hash against a real server
bash scripts/it-physical.sh 18                          # a move by promotion
LEPIS_BENCH_ROUTED=1 bash bench/pgbench.sh 60 16 <label>
bash bench/scaleout.sh --routers <a>[,<b>] --client <c> --nodes <1>,...,<8>   # eleven hosts
bash bench/chunks.sh                                    # moves and joins, both layouts