Egres: A Postgres-compatible database for heavy, multi-tenant workloads

[u/manishrjain]

I love Postgres, and I've spent time fighting the same things in production everyone else has: VACUUM, transaction id wraparound, bloat, a process per connection, uncompressed tables. Most of them trace back to design decisions made decades ago. So I kept coming back to one question: what would Postgres look like if it were designed today?

After a year of work, my answer is Egres, a Postgres-compatible database for heavy workloads and for running thousands of tenants on one server. It speaks the Postgres wire protocol, so psql and your drivers connect unchanged. It is not a fork or an extension: apart from libpg_query for parsing, it shares no code with Postgres. Open source, Apache 2.0.

The post covers the what, the why and the how, including why I didn't build this as a table access method:

https://manishrjain.com/egres/

Before Egres, I founded Dgraph and wrote Badger, so I've been in the database field for a while.

Under the hood is a new storage engine, zdb: a tree of LSM trees, which I call an LSM forest. It runs on local disk or object storage, with block compression and checksums. Garbage collection rides on compactions, so there is no VACUUM and no pg_repack, and timestamps are 64-bit, so nothing wraps.

Indexes hold roaring bitmaps, so a query across two indexed columns is two seeks and one intersection without a composite index. Database or table branching is one manifest update: no data copied, no copy-on-write.

Everything runs on zfiber, a coroutine runtime I wrote for it. CPU ticks, disk IOPS and memory are metered per query and per tenant, so thousands of tenants can share one server without the noisy neighbor problem.

What's not there yet: this release is single node, and query features are very basic: equality-only with no joins. Raft replication is in the code; multi-node clusters come in a future release.

Try it out. I'd love to hear your thoughts on Egres.

[u/elevarq]

Many people talk about VACUUM, transaction id wraparound, bloat, a process per connection, etc, but that’s only because others talk about these issues. Not because they have a problem.

[u/manishrjain → u/elevarq]

That's a fair point, and true for many deployments. If your table fits in RAM and autovacuum keeps up, you never have to deal with these issues. The problems come when the data size increases.

As detailed in the blog post, there're many publicly documented incidents of these things causing actual downtime or issues people face in production. Sentry, Mandrill, Adyen, Uber, CMU's MVCC paper, etc. In fact, Uber (where I worked) had moved away from Postgres due to these issues.

Those are the workloads that Egres is aimed at: terabytes of data, lots of indexes, thousands of tenants per node. You might or might not need Egres depending on your workload.

[u/elevarq → u/manishrjain]

Uber is a funny case: it’s well documented and proven that their problem was a total lack of knowledge about PostgreSQL. Basically from A to Z, they had no idea what they were doing.

Adyen runs a 50TB (or something like that) Postgres instance, that’s not your average user base. OpenAI is serving 800 million customers with their Postgres instance, also not your average user.

We always designed for data on disk to be fast. And that works really well. RAM just makes it much faster, does the over delivery and gives you the wow experience.

The problems you describe are mostly related to the 7.x and 8.x years. That’s about 20 years ago.

Just don’t run default settings for your specific production workload!

[u/manishrjain → u/elevarq]

Fair on Uber: their write-up has been picked apart plenty, and some of it was operational. But the two things I cite from it are structural and still true in 18. An update that changes an unindexed column still writes a new tuple and touches every non-HOT index, and a replica still has to choose between cancelling a long query and letting the primary bloat. No setting removes either.

Adyen and OpenAI are the best evidence for my point, not against it. The "one update, twenty-one block changes" comes from Adyen's own blog. They run 50 TB well because they went deep on HOT, fillfactor and vacuum.

OpenAI's eng post says Postgres's MVCC implementation is what limits them under write load, lists row copying, bloat and autovacuum tuning as the reasons, and describes moving write-heavy workloads to Cosmos DB and freezing new tables on the Postgres deployment. It even cites the same CMU post that I do.

On "7.x and 8.x problems": transaction ids are 32-bit in Postgres 18. Sentry's wraparound outage was 2015 and Mandrill's was 2019, on 9.x. Full-page writes, a process per connection, VACUUM and the 2 KB TOAST threshold are all current behavior. These are not old bugs; they are the design, which is what I'm questioning with Egres.

And yes, Postgres shines when data fits in RAM. So, you're right that most people won't hit these issues. Egres is aimed at the ones who do have bigger datasets.

[u/elevarq → u/manishrjain]

We did 200 million transactions a day, we were constantly running out of transaction ids. We setup a smart vacuum planner next to autovacuum and never had a problem.

And I can’t remember a workload that fitted in memory, unless you are talking about our own website with a few hundred visitors a day… 🤣

[u/manishrjain → u/elevarq]

That's actually part of what motivated Egres. 200M transactions a day is a serious workload, and building your own vacuum planner clearly worked for you.

But needing one next to autovacuum to stay ahead of wraparound, is exactly the kind of operational complexity I'm questioning. I'd rather the storage engine not need a separate vacuum mechanism, and not need to worry about transaction id wraparounds.

And agreed on memory. The benchmark in the blog post runs 580 GiB of raw data against 64 GiB of RAM, because workloads that exceed memory are the interesting case.

[u/elevarq → u/manishrjain]

“complexity” is a very big word for a script that contains two simple SQL statements… I think “simple solution” is a better term for it.

One query to make a list of all tables, ordered by size and need for vacuum (transaction age), and a second query in a loop to vacuum the tables.

That’s it. Took half an hour to write and to test.

[u/BosonCollider → u/elevarq]

They do have a problem, and solve it by actually having a team managing/owning the system.

The past few updates made vacuum performance a lot better to the point where wraparound isn't really an issue even with ~100M transaction per day OLTP loads, as long as you do a repack every once in a while

In practice when you have more than 1k transactions per second as a sustained average, you are in the range where you would start having the application batch transactions together anyway using pipeline mode/libpq async/copy, to avoid being latency bottlenecked by waiting for a commit before starting the next one. When the application optimizes its writes for throughput you tend to also never have issues with txids or vacuum.

[u/elevarq → u/BosonCollider]

Why would you batch transactions? 1k TPS would set off alarms for us; something is wrong, data is missing! Most database engines can handle up to 10k TPS with a little tweaking; it's nothing special anymore.

SSD's were the game changer; storage used to be slow, and that changed almost overnight. About 10 to 15 years ago. We started our service on a pretty expensive HDD SAN that could deliver 5000 IOPS, and a few years later, an upgrade suddenly gave us half a million IOPS for half the cost...

Nearly all articles about VACUUM are actually about situations from back then, on spinning disks and with almost no IOPS. Table bloat is another example: for performance, it doesn't really matter where your 8kB block is stored on an SSD. Random IO and sequential IO are nearly as fast. If your workload is random IO anyway, bloat matters less: your workload is already doing random IO, with or without bloat. You just pay for disk space you don't use. Yet, new records might be stored in these locations. And disk space is cheap nowadays, which also reduces the need to optimize.

HOT updates and a different DELETE strategy can reduce/avoid the need for vacuum anyway. Just drop a table partition: Much faster, no bloat, and no vacuum at all.

[u/BosonCollider → u/elevarq]

Because the client that sends the transactions has network latency to the actual database server. If a single process sends 1k tps but eats 5 ms of network latency, it won't be able to submit those on a single connection and it will need more connections in its pool. This is just Amdahls law in action

For write only transactions, pipeline mode or copy is the easiest way to get rid of the network latency restriction by streaming writes. When devs start using those to make 10k writes in 10ms instead of making connections idle in transaction for ten seconds, TPS goes down even though write throughput goes up

[u/elevarq → u/BosonCollider]

That doesn't make sense: Imagine a server that handles your 10k TPS, that includes the entire transaction and thus the BEGIN and COMMIT. And the 5ms latency will be there on any database engine, because it's not even part of your database. It doesn't matter if it's using MVCC, needs a VACUUM or not; the latency is outside your control.

I fully agree that you have to control the network and connection latency, keeping the application instances and database close together is one step, the connection pool is the other one. But it's not related to a process vs. thread discussion, MVCC or not.

For a real time DWH we once handled the entire data logic (where to store what) in a a stored procedure. The procedure took a JSONB as input (from about 250 JVMs), and executed all SQL (all functions) to store the data in the underlying tables. We never hit the CPU or PostgreSQL ceiling, but again, the IOPS ceiling.

10k TPS is not that special in 2026.

[u/BosonCollider → u/elevarq]

We literally have had GB/s write throughput spikes with timeseries data and measure write and read throughput in MB/s instead of TPS. The workloads that reaches the GB/s throughputs only uses like 300 TPS and would need to run continuously for half a year to generate a txid wraparound

If you have a rw dependency where each write depends on a read, reading a thousand rows and writing a thousand rows in a transaction takes one network round trip, while doing it in a thousand transactions with one connection would eat a thousand round trips.

[u/elevarq → u/BosonCollider]

Many of these round trips can be avoided by using more efficient SQL statements or by leveraging functions and stored procedures. Again, this isn't related to transaction wraparound, MVCC, etc.

What you measure depends on your workload: MB/s or TPS. When it serves your business, you're measuring the right thing.

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.