Two Storage Engines Under One API: Postgres for Hot Rows, Lance for Everything Else
Our data engine writes new rows to Postgres and moves older ones into Lance columnar files on object storage, behind a single owner-scoped read API. Here is how the two tiers fit together, and why our biggest performance problem was the number of files, not their size.
Our platform stores everything in one append-only data engine: agent conversations, app records, usage logs, crawled data. Some of it is read seconds after it is written. Most of it is read rarely, in bulk, by analytics or search. No single database is good at both, so the engine has two tiers behind one API.
- Hot: Postgres. New writes land here. Point reads, recent pages and anything interactive are served from it.
- Cold: Lance on object storage. Older rows move into Lance, a columnar format built on Arrow, stored as files in an S3-compatible bucket. Scans, filters over many rows and text search run here.
Callers never choose a tier. Every read goes through one owner-scoped API, and the engine decides where to look.
Show the data
| Postgres only | Both tiers | Lance only | Unknown | |
|---|---|---|---|---|
| Datasets | 104 | 12 | 23 | 67 |
One read across two tiers
When a dataset has data in both tiers, a read combines them. The engine merges the two schemas, removes rows that exist in both because they were copied to Lance but not yet pruned from Postgres, and pages through the combined result as if it were one table. Genuine duplicates in the data are kept; only copies made by the move are removed.
Owner scoping is enforced in the read path, not by the caller. A cold file with no owner column returns nothing to a scoped read, so a mistake in how a file was written fails closed instead of exposing rows.
Cold reads avoid downloading whole files. A first pass fetches only the filter and identity columns with byte-range requests, and a second pass fetches full rows only for the matches.
Moving rows without losing them
- Copy first, delete second. Rows are written to Lance and confirmed before they are removed from Postgres, so a crash between the steps leaves a duplicate, never a gap.
- Checked pruning. Postgres rows are deleted only after a check that the matching cold rows exist.
- Wait for a full file. Rows stay in Postgres until a dataset has enough of them to write one reasonably sized file, instead of trickling out a file per batch.
The real problem was the number of files
For months, cold reads were slower than they had any right to be. The data was not large. The problem was that the early mover wrote a new small file for every batch, and object storage is fast at reading bytes but slow at opening objects. One conversation dataset reached about 31,000 files.
Every cold read also went through a rate limiter, at 25 requests per second and 8 in flight. With tens of thousands of tiny files, that limiter, not bandwidth, set the speed of every scan. One query downloaded about 300 MB of index files and matched nothing.
Compaction shows the scale of the problem. In one October run on that dataset, a single batch of 535 files holding 566,768 rows became 4 files.
The fixes were all about fewer, larger files:
- Hold rows in Postgres until a full file is worth writing.
- Run an hourly compactor that merges small files into large ones.
- Keep a catalog of cold files in Postgres, so listing a dataset does not mean listing a bucket, and clean up files that compaction replaced.
- Serve recent text search from a Postgres index, instead of scanning cold index files for data that is still hot.
Other things that went wrong
- Schema drift broke compaction. Files written months apart disagreed about column encodings and struct fields, so merging them failed. A single run also had one time budget for every dataset, so a slow dataset early in the list starved the ones after it.
- Postgres ran out of disk. One engine database filled an 88 GiB volume and crash-looped for about two and a half days. The write-ahead log looked like the cause but was only the first symptom. Autovacuum tuning and a larger volume fixed it.
- Object storage metadata calls fail. Listing and head requests fail transiently often enough that every one needs retries.
Where it stands
Read latency is still uneven. Over a few days in October, reads from a memory dataset had a 95th percentile of 2 seconds, while the largest conversation dataset reached 30 seconds at the 95th percentile. The architecture is right; the remaining work is keeping files large and indexes close to where queries run.
If you are building something similar
- Put a single read API in front of both tiers from the start, and make it enforce access control.
- Copy before you delete, and check before you prune.
- Count files, not just bytes. On object storage, the number of objects per query decides your latency.
- Plan for schema drift in old files before you write your first compactor.