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

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

manishrjain.com/egres

I love Postgres. It is an incredible database. But, its design is also 30 years old and most of what people fight in production comes from decisions made long time ago: VACUUM, transaction id wraparound, bloat, a process per connection, MVCC. So, I’ve been wondering about this question for a while,

“What would Postgres look like if it were to be designed today?”

For the past year, I’ve been quietly working on answering that question. Today, I’m excited to announce Egres to the world.

Egres is not a fork or a port of Postgres. It is a new database, designed from first principles, that speaks Postgres. Apart from libpg_query, it uses no Postgres code. This post walks through what Egres can do, the benchmark numbers, why I built it, and then the design that makes those features and numbers possible.

This release runs as a single node. The multi-node code is in but not enabled yet; that’d land in the next release. Most of the work in this version went into storage, replication, resource metering and fiber based query execution. The query features are basic for now, equality-only and no joins, and will grow with each release. You can install and get started with Egres by following this quickstart guide.

On 600M row (580 GiB data) benchmarks against Postgres v18, on a 16-core Ryzen 5950X with each server capped at 64 GiB of memory (link):

In general, Postgres does better when data fits comfortably in RAM; it achieves higher txn/s in the first 15 mins of the benchmark. As data starts growing, Egres does much better.

After the load (settle phase), Egres has lower p50 latency across all four queries, by a factor (2.9x - 226x). The tails are more mixed for select and customer queries. When the blocks are in RAM, Egres wins (p50). When they have to be fetched from disk, Egres pays the extra cost of decompressing bigger blocks, 16 KiB for Egres in this run, vs 8 KiB for Postgres. On select, it’s practically a tie. On customer, which has 75 rows scattered across the table, it costs Egres 6 to 8ms at p95 and p99 extra, making Postgres 33% faster (something for Egres to improve in future versions).

On the other two queries, sku_day and customer_day_count, Egres wins by a factor, ranging from 4.6x to 226x. Postgres was given the same four single-column indexes as Egres, without any composite index on (sku, day) or (customer_id, day). With those, Postgres would surely do better than the ~500ms p95 it reached for sku_day. But the point is, native bitmap indexes in Egres avoid the need to predict which columns will be queried together, and perform really well even without composite indexes. The same query runs in <4ms p95 in Egres.

Benchmarks are easy to get wrong, so I’ve put together this benchmarks page which explains the methodology, the schema, the Postgres configuration, the queries, and how to reproduce the numbers.

While at Uber, I ran up against one of the largest MySQL fleets anywhere. What had to be built around the database made the shortcomings of the database clear.

And that was MySQL, very heavily productionized database across the industry. Postgres adds its own issues on top. In fact, Uber famously moved off Postgres due to these limitations.

Lots of companies and developers are pouring resources into fixing what can be fixed in Postgres. But these issues are not bugs that can be fixed. They follow from decisions made decades ago, and changing them now would require a major rewrite or a new on-disk format, a cost very few teams running Postgres in production can reasonably absorb.

My first iteration of Egres swapped out the storage engine of Postgres with an LSM tree. The way MyRocks did with MySQL. Facebook cut its instance size by 62% moving its user database to MyRocks, and Uber saved more than 30% moving Docstore and Schemaless to it.

Neon, AlloyDB and Aurora also swap out the storage engine, but for a different goal. Their approach has been to serve Postgres pages via remote storage, so the compute is stateless and can be started and stopped at will.

But it comes with a lot of machinery. Neon puts a Paxos quorum of Safekeepers and fleet of Pageservers under every compute, and even then a replica is eventually consistent, so read-your-writes have to be served from the leader. AlloyDB reads its pages straight out of Colossus (distributed, low-latency filesystem used across Google) and spins up a read-only Postgres in a microVM for every agent that asks, with “sub-second freshness”. That is the same asynchronous replication, with a better network and faster storage.

Moving the pages to remote storage still doesn’t touch the rest of the list: a process per connection, a row update causing write amplification across indexes, VACUUM scans and pg_repack to deal with dead tuples, 32-bit transaction ids, write amplification in physically replicated WAL, lack of native compression, etc.

A stateless compute seems to help with multi-tenancy, because it lets you bring up a Postgres instance per tenant without moving data. But having an instance per tenant is expensive. A microVM makes it cheaper, but it comes with caveats. You still have to preallocate minimum resources for that tenant, tear them down after a period of inactivity to avoid billing for idle resources, and every restart begins with empty cache. All these show up in higher query latency.

A better way is resource pooling. Housing thousands of tenants on the same server, charging them for what they used, throttling them when they use resources beyond a cap. Nothing is reserved, or torn down, so no cold starts. The server can be sized based on collective usage across all tenants, and get much better resource utilization. If that sounds too far-fetched, note that every web service already works this way: one process, thousands of users, each billed for what they use.

Postgres cannot do this, because it cannot tell you what a query costs in CPU, memory or disk IOPS. Nor does it make it safe to run these tenants together: as mentioned earlier, one runaway query that gets OOM-killed brings down the entire instance.

Given all these, I gave up my earlier attempt at a “MyRocks for Postgres” and decided to take the longer, more ambitious path to write Egres from scratch, while ensuring Postgres wire protocol and SQL so existing tools would keep working.

Egres is written in Zig. It allows using C libraries whenever relevant and it gives explicit control over every allocation, which is what a per-query metering needs. Most importantly, an allocation request can be rejected with an error, instead of causing a process level panic or abort.

Egres is not trying to be a distributed, sharded database like CockroachDB, Yugabyte or Multigres. Nor is it trying to replace Postgres everywhere. It is designed for the workloads Postgres does not handle well: tables with many indexes, terabytes on disk, or thousands of tenants sharing one node.

Status: Mechanisms are built. Cap enforcement mid-query in future release.

Egres is a single process, using a custom designed coroutine system called zfiber. zfiber spawns as many threads as the number of logical cores, and cooperatively schedules fibers across these threads. All work across the database: connections, queries, WAL, compactions, disk and network I/O are run using these fibers.

zfiber is custom-built to the needs of a database. It is different from Go’s goroutines or Rust’s Tokio in that it allows certain fibers to have higher priority than others. For example, the fibers running Raft loops get higher priority and chosen over others if ready and waiting. zfiber also support dedicated lanes (dedicated threads) that fibers can’t cross. So a very heavy query workload would not spill into the threads serving the Raft fibers.

zfiber threads have local task queues and run task stealing so no thread sits idle while another has a queue. While zfiber doesn’t natively evict a running fiber, functions in Egres run a maybeYield call which can be used to optionally yield a fiber. zfiber tracks CPU ticks spent per fiber so it can tell how much CPU a query took.

Egres does direct I/O, bypassing filesystem cache. It stores the blocks read in an S3-FIFO cache to avoid relying on page cache. All storage I/O activity goes through a virtual filesystem API, which can meter the traffic to device and throttle disk IOPS as needed. In fact, Egres runs a quick calibration on boot to figure out each device’s bandwidth, and calculates the cost of writes as a factor of cost of reads, so it can charge reads and writes accordingly.

Any disk activity causes a fiber to park and wait for the I/O completion, meanwhile other fibers can run on the threads. zfiber uses Mitchell Hashimoto’s libxev which provides io_uring on linux.

Disk operations are throttled based on priority (and tenancy in future). Inspired by the SILK paper, disk operations carry one of 3 priorities: P0 is for queries, P1 and P2 for compactions. Knowing the full bandwidth of the disk, lower priorities get the bandwidth the higher ones leave unused. Thus, background compactions don’t interfere with queries and ensure query latencies stay low even with many compactors running at the same time. The same mechanisms could be applied to object storage, based on number of pending requests in addition to bandwidth.

Furthermore, all memory is allocated via custom allocator, a thin wrapper over jemalloc, which Zig’s allocator interface makes easy to do. A memory governor tracks live memory against the total memory allocated to the process. Every 100ms it sizes the block cache up and down to make room for queries and background tasks, keeping the process under its specified memory cap.

As of this release, only the total memory used by the process is being tracked. Future releases will track memory per query and per tenant. An allocation could then park the fiber waiting for some duration for more memory to become available, or, if over the cap, fail with an error propagated back to the user instead of an OOM kill.

Given these mechanisms, Egres would be able to accurately meter and throttle the CPU ticks spent, the memory used, and the disk IOPS per query, per tenant. That allows thousands of tenants to share resources, without any noisy neighbor problem, and without running a VM per tenant.

Beyond resource management, Neon and AlloyDB wrap each Postgres in a microVM for two reasons, which don’t directly apply to Egres.

The first reason is best phrased by AlloyDB: “shared allocations mean shared fate”. Postgres’s memory cannot be capped from outside, so a cgroup limit means the OOM killer takes the postmaster down and every tenant with it. In Egres, running out of a resource is a function error, which can be treated like every other error and returned all the way back to the user. Tenants share the resources but do not share the fate.

The second is security. Postgres has no boundary inside the process: extensions are C running with the engine’s full power, and a memory-safety bug in that code, reached through one tenant’s input, reaches every tenant’s data. A VM per tenant puts a kernel between them.

Egres is one process too, so what it does instead is shrink the attack surface. A client can send only SQL, a session is bound to one database at connection time (auth coming in future release), and no tenant native code runs in the process. Extensions will be compiled into the engine rather than loaded at runtime, and user-defined functions, likely WebAssembly, will run in a sandbox metered like any other fiber. That is the bet every multi-tenant web service makes: a small surface instead of a kernel per customer.

Status: available in the current release.

Egres runs on top of a new storage engine I built, called zdb. While zdb bootstrapped as a port of Badger from Go to Zig, it quickly went way beyond Badger. zdb is a tree of LSM trees, a