The four horsemen behind thousands of Postgres outages - malisper.me

Primary Menu

malisper.me

malisper.me

General<br>About Me

Twitter

Table of Contents for Postgres Posts

RSS

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Email Address

Subscribe

Skip to content

Home » pgrust » The four horsemen behind thousands of Postgres outages

Postgres is great, but there are some very common problems that people have that can pretty easily lead to outages with Postgres. These aren’t just theoretical issues. From talking to a lot of startups, these are the things that actually cause outages in production, especially on teams that don’t have a dedicated database person. These are the four main areas I want to improve upon, in pgrust, my reimplementation of Postgres.

VACUUM and Transaction ID Wraparound

The Postgres vacuums are probably the number one cause of outages related to Postgres. When you delete a row in Postgres, Postgres doesn’t actually delete it. Postgres will keep the row around and mark it as deleted. This is done for many reasons including giving Postgres an easy way to rollback the transaction if needed. To actually delete the rows and free up the space, Postgres has a background job called the vacuum, which will physically remove the rows that were marked as deleted.

This creates some problems. First, the vacuum itself. Because the vacuum has to read all your data, the vacuum can consume a ton of I/O. This I/O will compete with the I/O your queries need and can slow your queries down. If you try to configure your vacuum such that it doesn’t use as much IO, it may fall behind, and you may actually be accruing deleted rows faster than the vacuum can clean them up. You end up having to play this really messy game with the vacuum where you want to be aggressive enough that it keeps up with how quickly you’re updating your deleted data, but not too fast that it competes with I/O for your queries.

The second issue is transaction wraparound. In order to keep track of which rows are live and which ones are dead, Postgres will label those rows with a transaction ID. Postgres uses a 32-bit ID to represent the transaction IDs. That means you can have at most four billion transactions before Postgres has to start reusing transaction IDs. One job of the vacuum is it will reclaim the old transaction IDs so they can be reused. Now, if the vacuum falls too far behind on reclaiming transaction IDs, Postgres will actually shut down your DB to prevent you from reusing live transaction IDs. This may sound silly, but it’s necessary because otherwise your data can become corrupted. I would bet this is probably caused on the order of thousands or tens of thousands of outages with Postgres.

What pgrust is doing differently:

I’m looking at two different ways to solve for this:

There’s been a patch out there to add 64-bit transaction IDs to Postgres. There’s been reluctance to merge the patch for a number of reasons: it breaks compatibility with previous versions of Postgres and adds 32 bits of overhead to every row. I personally think, given the number of outages this has caused, it’s totally worth it to make this change.

I’m looking at alternative architectures that don’t require VACUUMs at all. Oracle takes a completely different approach to marking rows as deleted that doesn’t require a VACUUM, referred to as an undo log. There are projects exploring adding this kind of support to Postgres.

Connection Limits and Query Parallelism

When you spin up a Postgres instance, there’s a configuration variable for the maximum number of connections that Postgres can create. If more things try to connect to Postgres then the connection limit, they won’t be able to, and your database will go down. Changing the number of connections requires restarting your DB, so you need to be very thoughtful about about this setting when first creating your database. If you don’t know about this ahead of time, you’ll likely end up with a connection limit that’s too low. Many people will run software like PgBouncer, which will partially solve this problem, but it’s still a big pain.

You may ask, why does Postgres even have a connection limit in the first place? The answer largely comes down to how Postgres parallelizes queries. Every time you create a new Postgres connection, Postgres will create a new process. Compared to other ways of doing parallelism, processes are very expensive, both in terms of taking CPU resources but also the amount of time it takes to spin up a new process.

Not only do processes limit Postgres’ ability to parallelize across queries, but they also limit Postgres’s ability to parallelize within a query. When Postgres wants to parallel process a single query, Postgres will spin up multiple processes and have them each process the query. Because it’s expensive to spin up new processes, Postgres will only do this for long-running...