Lepis puts one Postgres database on many Postgres servers. Your app keeps one connection string. The servers stay stock Postgres: no extension, no fork, two settings changed. Here is how it works, what we measured, and what happened when we broke it on purpose.
The short version
- It scales. Eight 4-vCPU servers behind one router did 76,799 write transactions a second, against 12,169 on one server. That is 6.3 times.
- It gets the same answers as Postgres. 3,500 generated queries, checked against one plain Postgres: 0 wrong.
- It survives broken servers. We broke a live cluster 21 ways under load. In every scenario that heals by itself, 0 acknowledged writes were lost.
- It moves data cheaply. Moving a quarter of a node wrote 107 MB of WAL on the source, against 1,584 MB for a row-by-row move.
- It refuses before it guesses. What it cannot run correctly gets an error that names the fix.
When each result was measured
Lepis changes every week, and we do not rerun everything for each change. So every number here is a record: what one build did on one day. How it works describes the router as of 0.1.25.
| Result | Measured | On |
|---|---|---|
| Hash against Postgres | Oct 5 | Postgres 13, 17 and 18 |
| Cost of routing | Oct 6 | One 8-vCPU host, shared |
| Failure drills | Oct 11 to 18 | Live SnoutData Cloud fleet |
| Generated queries, moves under load | Oct 17 | Router 0.1.11, one table per node |
| Scale-out to 8 nodes | Oct 18 | Eleven hosts, one table per node |
| Chunks, joins, growth | Nov 1 | Chunked build, one 4-vCPU box in Docker |
Tables are stored as chunks by default since November 1, and the scale-out has not been run on them. The raw output is public.
How it works
The pieces
- The router speaks the Postgres protocol and holds connections, nothing else. Run as many as you like.
- A distribution key, say
customer_id, decides where each row lives. Tables that share a keyspace live together, so joining them stays on one node. - Ranges cut the hash space, and each belongs to one node. They split, merge and move.
- Chunks are what a range is made of: plain tables with their own indexes, 64 per keyspace unless you expect more data.
- Reference tables are small and copied whole to every node.
- The home node holds everything else, plus the catalog. There is no etcd and no extra service.
Three things it does not trust
Its own reading of SQL. Every statement goes through Postgres's parser (libpg_query). What it cannot parse, it refuses.
Its own hash. It is Postgres's extended hash, ported to Rust and checked against the server: 0 differences on 101 million values.
Its own routing. Each chunk carries a CHECK constraint on that same hash. A stale router, a bug or someone with psql cannot put a row on the wrong node. The node says no.
Moving data
- Copy. The target subscribes to the moving chunk tables by logical replication.
- Pause. Only the moving chunks lock, for 2 seconds at most by default. A job that would run over backs off and tries again.
- Switch. The source's copies are retired, the target's attach, and the catalog moves. Statements that waited get a
40001to retry. - Clean up.
DROP TABLE. NoDELETE, noVACUUM, and the disk comes back.
In SnoutData Cloud a new node can start as a restore of the source's backup from S3 instead. It adds no copy load to the live primary, then drops the chunks it does not own.
Queries across nodes
| The statement | What Lepis does |
|---|---|
| Names the key | Sends it to one node |
| Names no key | Asks every node and merges: ORDER BY, LIMIT, aggregates, GROUP BY, DISTINCT |
| Joins a small table off the key | Copies the small side to each node (up to 100,000 rows) |
| Joins two big tables off the key | Re-hashes both, node to node (up to 1 billion rows, estimated) |
| Window function, DISTINCT ON, UNION | Gathers the rows on the home node and runs it there (up to 1 million rows or 1 GiB) |
| Writes to two nodes | Two-phase commit, with the decision logged once on the home node |
| Goes past a limit | Refuses, and the error names the limit and the fix |
The join paths are for a single SELECT outside a transaction, and every limit is a setting. If a router dies mid-commit, another one finishes the job: a logged decision is completed, and no decision after 30 seconds is rolled back.
What we broke on purpose
The best results here are the ones that went wrong first.
| What we did | Result |
|---|---|
| 21 ways of breaking a three-node cluster under load | 0 acknowledged writes lost or duplicated, 0 transfers half applied, where it heals itself |
| Home node broken so it could not start | Back on its replica in 130 s, 0 lost, nobody involved |
| A database's disk destroyed under load | Back in 89 s, 0 of 4,231 lost (a plain database) |
| One 8 KB page overwritten on disk | Caught by its checksum; every row matched its digest after the replica took over |
| Coordinator killed at each of 9 commit points | Recovered at all 9; 240 bank transfers, no money made or lost |
| 8 writers and 19 cutovers per run | About a million writes each on Postgres 18 and 17, 0 lost |
The fleet acceptance run takes a cluster from first node to turned off again, checking every row at each step. It passed all 49 checks.
What those drills caught
- The last 44 seconds. A node died while its replica was behind, and the promotion lost those writes. Failover now salvages the node's unarchived WAL first, or waits for a person. Run again: 0 lost.
- The backup that kept the damage. pgBackRest copies a bad page with a warning and a successful exit. A replica restored from it carried the damage and was promoted. Replicas now restore from the newest backup with no checksum error.
- 797 writes. The first disk drills lost 797 and 301 acknowledged writes, to two bugs of ours. Both are fixed, and the run above lost none.
- The 37-second stall. Growing a table to 40 million rows froze a node's clients for up to 37 s, on an
ALTER TABLEqueued behind aCOPY. With the fix, the slowest client transaction took 1,059 ms. - One wrong answer. The generated corpus caught a
NOT EXISTSanswering wrongly over the new join path, before it was committed as passing.
On the chunked build, 500 generated queries gave 493 equal, 7 refused, 0 wrong. The 40 million rows all came back exact.
How fast
Spreading the data
Eleven EC2 hosts: eight nodes on 4 vCPUs, two routers on 8, one client. 10 million accounts, every transaction on one node. Peak transactions a second, over 32 to 512 clients.
| Nodes | Read, router | Read, no router | Write, router | Write, no router | TPC-B, router | TPC-B, no router |
|---|---|---|---|---|---|---|
| 1 | 68,296 | 73,191 | 12,169 | 11,608 | 7,611 | 7,644 |
| 2 | 129,753 | 123,468 | 25,734 | 26,028 | 14,124 | 14,619 |
| 4 | 223,356 | 249,216 | 45,923 | 51,411 | 25,134 | 28,483 |
| 8 | 341,126 | 504,827 | 76,799 | 103,722 | 44,238 | 58,558 |
| 8, two routers | 409,449 | 85,626 | 45,952 |
- Up to 4 nodes, the nodes are the limit. They run at 94 to 100% CPU.
- At 8 nodes, one router is the limit. It delivers 68 to 76% of what the nodes do alone. A second router lifts that to 78 to 83%.
- The catch. The 8-node write and TPC-B peaks had the home node at 1,200 connections. At 600 they are 62,551 and 39,322 (see below).
The price of the extra hop
One 8-vCPU host shared by Postgres, the router and pgbench, one node behind the router. Each cell is tps / p50 ms / p99 ms.
| Workload | Direct | Router, transaction mode | Session pooler |
|---|---|---|---|
| select, 16 clients | 123k / .123 / .31 | 65k / .238 / .51 | 58k / .271 / .45 |
| select, 64 clients | 113k / .471 / 1.73 | 80k / .704 / 2.17 | 57k / 1.126 / 1.40 |
| TPC-B | 5.0k / 2.36 / 9.9 | 4.85k / 2.87 / 9.6 | 4.85k / 2.80 / 10.9 |
- Where it costs. Simple reads: 53% of direct at 16 clients, about 71% at 64.
- Where it does not. TPC-B keeps 97% of direct.
- Where it loses. p99 at 64 clients is 2.17 ms against the pooler's 1.40, not yet explained. Session mode's p99 on TPC-B is the worst in the run, 12.5 ms.
Moving data and running joins
Same 4-vCPU box in Docker, both layouts, 10 million rows per table. Compare the two columns, and do not read them as capacity.
| Moving 25% of a node | One table per node | Chunks |
|---|---|---|
| Time | 130.6 s | 38.9 s |
| WAL on the source | 1,584 MB | 107 MB |
| Disk given back | none | 379 MB |
| Write pause | 48 ms | 144 ms |
| p99 read during the move | 975 ms | 368 ms |
| p99 write during the move | 38 ms | 51 ms |
- Joins cost what you would expect. A broadcast join took 956 ms against 528 ms on one Postgres. Two 10 million row tables off the key took 22.2 s against 2.4 s.
- Cutover pauses elsewhere. Logical moves on the one-table layout: p99 0.21 s on Postgres 18. A move by promotion on the fleet: 736 to 875 ms over seven runs, with no writers.
What it does not do
- The home node is the single point. In the Cloud a broken home fails over in about two minutes, with every key out meanwhile. 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 copy in the second zone is up. The cluster stops while its nodes are restored.
- It is not distributed SQL, and not multi-master. Each node is a whole Postgres, and every row has one owner.
- No global snapshot. A read across nodes sees each node at its own moment.
- A router behind on the catalog can serve out-of-date reads for at most 10 seconds, then stops routing. Its writes are refused by the node.
- Still refused across nodes:
INSERT ... SELECT, SERIALIZABLE and savepoints. - Table rules: unique keys must include the distribution key, a plain
serialis refused, and 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 though it touches one node. Past about 300 clients the home node ran out of connections for them.
- Unexplained: a node spends 17% more CPU per transaction behind the router.
- The advisor's thresholds are defaults we chose, not measured. The autopilot is off until you turn it on.
Where it sits
There are three ways to spread Postgres: replace the storage engine, install an extension in every server, or put a router in front of plain servers. Lepis is the third.
- Nothing to install. Any Postgres 17 or 18, yours or a managed one, with
max_prepared_transactionsabove zero andwal_level = logical. - The node enforces ownership, not only the router.
- Moves drop tables. A move ends in
DROP TABLE, not a delete and a vacuum. - In the Cloud, a new node can be a restore from S3, not a copy off the live primary.
Run it yourself
From the public snout-lepis repository. Docker is the only requirement, and benchmarks belong 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