PostgreSQL 19: Finally See Which Tables Need VACUUM (New Feature)

If you run Postgres in production, this is the single most important video you can watch. Vacuum is not a maintenance command. Vacuum is the subsystem that decides whether your database keeps running or falls over.

Over the next twenty two slides, we are going to trace exactly what happens inside Postgres when you run an UPDATE or a DELETE. You will see why nothing actually gets deleted. Why your tables keep growing anyway. And why ignoring vacuum is one of the most common ways production Postgres databases die.

I am going to show you the internals, the tuning knobs that actually matter, real production disasters from companies you know, and the eight signals you need on your monitoring dashboard. By the end of this video, you will not just know what vacuum does. You will know how to tune it, how to debug it, and how to spot the failure modes before they take you down. Let us start at the beginning.

Here is the single most important thing to understand about Postgres. It never updates a row in place. Never. When you run an UPDATE, Postgres does not overwrite the existing row. It writes a brand new version of the row, and marks the old version as obsolete.

Both versions live on disk at the same time. Different concurrent transactions see different versions, depending on when they started. This is called multi-version concurrency control. Or MVCC. And it is a deliberate trade.

What you get from MVCC is huge. Readers never block writers. Writers never block readers. Every transaction sees a consistent snapshot of the database, without taking a single lock. That is the property that lets you run heavy analytics against a busy OLTP system without bringing it to its knees.

But the trade is not free. Every UPDATE leaves an obsolete row version behind. Every DELETE leaves the deleted row physically present on disk, just marked as no longer visible. These obsolete row versions are called dead tuples. And they pile up. Vacuum is the garbage collector that cleans them.

Skip vacuum, and three things happen, in order. First, your tables grow even though row counts stay constant. Second, queries get slower because every scan reads dead bytes. Third, eventually, the database refuses all writes. That third one has taken down Sentry, Mailchimp, GoCardless, and many others. We are going to cover all three failure modes in detail. Keep watching.

Every row in Postgres carries hidden bookkeeping fields. They are invisible in normal SELECTs. But they are the engine of MVCC. And once you can see them, MVCC stops being magic.

The two most important are xmin and xmax. Xmin stores the transaction ID that created this version of the row. Xmax stores the transaction ID that deleted or updated it. Or zero, if the row is still alive.

There are other fields too. Ctid is the physical location of the row, expressed as a block number and offset. That is what indexes point to. T_infomask holds visibility hint bits. Whether the creator committed, whether the row is frozen, whether this is a heap-only tuple, and so on.

The visibility rule is simple. A tuple is visible to your transaction if its xmin committed before your snapshot started. And either its xmax is zero, or the deleter committed after your snapshot, or the deleter rolled back. That is the entire game. Every SELECT in Postgres runs that comparison against every tuple it considers.

You can see all of this directly. Just SELECT xmin, xmax, ctid, and your real columns. Try it. Once you can see the bookkeeping, everything that follows in this video makes sense.

Let us walk through a real example. Two sessions, one row, two different truths.

Session A begins a transaction at transaction ID one hundred. It reads a row from the orders table. The row has xmin equal to ninety eight, xmax equal to zero. Session A sees the row with total of forty nine ninety nine.

Now Session B begins at transaction ID one hundred and two. It updates the same row, sets total to fifty nine ninety nine, and commits. What happened on disk? The original row had its xmax stamped with one hundred and two. A brand new tuple was written, with xmin one hundred and two and total fifty nine ninety nine. Two versions, same row, both physically present.

Session A reads the row again. Still sees forty nine ninety nine. Why? Because Session A's snapshot was taken at XID one hundred. The new version has xmin one hundred and two. That is in the future from Session A's perspective. So Session A correctly sees the old version.

Session B sees the new version. Neither blocked the other. Neither took a row lock. This is MVCC working exactly as designed. And the moment Session A commits, the original row becomes a dead tuple. But it cannot be cleaned up yet. Some other long-running transaction might still need it. Hold on to that thought. We will come back to it.

Now let us watch bloat appear in real time. Four operations on a single eight kilobyte heap page.

Step one. Initial state. Eight live rows on the page. Clean and tight.