Three Answers From One Table: The Method Is the Decision

Last month my team at AGLedger published a piece I want to put in front of a wider audience than the Postgres crowd it was written for. It is called Three ways to measure a Postgres queue table, three different answers, and on its surface it is about a job queue, a retention window, and a table that would not sit still. Underneath, it is about something every operator and every buyer runs into weekly: the number you are handed depends on how someone chose to look, and nobody tells you how they looked.

I have spent thirty years on the receiving end of vendor benchmarks. I have also produced a few. This post is about what changed in how I read them after watching our own measurement go wrong four separate ways before it went right.

#The setup

AGLedger runs on stock PostgreSQL with no extensions. The same database is the record store, the audit vault, the job queue, and the rate-limit store. That is a deliberate choice, and it means the honest question is not "can Postgres do this" but "which of the settings we chose actually did what we chose them for."

So the team built a rig: one non-burstable instance per load level, PostgreSQL 18.6, fsync and synchronous commit both on, nothing relaxed to flatter a number. They pushed a pg-boss job table to 8,000 jobs per second with a one-hour retention window and sampled the table size every thirty seconds for eleven and a half hours. The question was simple. Does one hour of retention keep the table bounded, or does it creep?

#Three methods, three verdicts

Here is the part worth reading twice. The same eleven-hour series was analyzed three standard ways.

• Fit a trend line across the whole steady-state window, no cherry-picking, and it reports the table growing about 3 percent a day. Pick the steepest twenty minutes and the same method reports 72 percent a day. The first verdict says plan a migration. The second says page someone tonight.
• Count file extensions and wait for them to stop, which is what most of us would do by instinct, and it reports the table settled. Ship it. Then the table extended again three hours later.
• Take the peak-to-trough range over a window long enough to hold many retention cycles, and it reports a band under 1 percent, ending below its own peak. Bounded. Size it and move on.

Same data. Three answers. Each one is a defensible reading, each one implies a different action, and only one of them is right.

The reason is mechanical and it is documented Postgres behavior. Under a retention window that deletes in age order, VACUUM regularly truncates trailing empty pages and hands the space back to the operating system. The heap does not creep and it does not plateau. It breathes. A trend line averages the rises and falls into a slope that describes neither. An extension counter watches one edge of the cycle and is structurally blind to the other. Only a method that can see both directions can return the right answer, and it needs a window long enough to contain several cycles before it means anything.

#How long is long enough

At 8,000 jobs per second, the last heap extension landed almost eleven retention cycles into the run. A two-hour benchmark against a one-hour window contains one deletion cycle and cannot tell a plateau from a slow slope. Most published queue benchmarks run for minutes. That is not dishonesty. It is a method that produces a confident answer with no way to know it is wrong.

And even the right answer on the heap was only half the table. The indexes on the same table do not truncate, so a slope is the correct instrument there, and that slope was positive at every rate after eleven hours with no settling point in sight. A "bounded" verdict that only looked at heap bytes would have been true and misleading at the same time.

#The four mistakes that came first

What made me trust the final article was not the result. It was the testing notes behind it, which list the ways the team got it wrong before they got it right. I will paraphrase.

1. They measured the wrong configuration first. The opening sweep ran stock autovacuum on the assumption that stock is what ships. It is not. The correction became the experiment.
2. They ran an A/B in the one regime where the variable could not express itself. A compressed six-minute retention window pinned both arms to the same autovacuum ceiling, so a real setting read as inert. They filed that as a finding and had to retract it.
3. They measured the settle, not the setting. Let the table fill, wait, then read the result, and every threshold catches up. Both arms came back identical.
4. They measured one edge of a sawtooth, four different ways. The headline number moved six times before the peak-to-trough method finally behaved.

Each of those is a competent engineer making a reasonable call. None of them failed a test, because a test asserts behavior and every one of these settings leaves behavior identical. The lesson the team drew, and the one I keep coming back to, is this: a storage setting pays only under a condition, and the condition is usually about something the setting cannot see.

The companion piece, Retention does not protect a Postgres queue from a pinned xmin horizon, makes the same point from the other direction. Everything above holds only while the database's visibility horizon keeps advancing. Hold a snapshot open for half the retention window, as a BI tool or a long report might, and the same table more than doubles while autovacuum runs the same number of times and reclaims nothing. Nothing errors. Every counter looks healthy. The design that bounds the table is simply orthogonal to the thing that unbounds it.

#What this means if you buy software

I work with mid-market companies who are being handed numbers every week: throughput, latency, cost per query, accuracy on a benchmark, hours saved by an agent. Very few of those numbers arrive with the method attached. Almost none arrive with the retractions.

Three questions have earned their place in every evaluation I run now.

• What did the method see, and what was it blind to? A trend line cannot see a truncation. An accuracy score cannot see the prompts it was never given. Ask what edge of the cycle the instrument was watching.
• How many cycles did the window contain? One retention cycle, one quarter, one sprint of pilot usage. If the answer is one, you have a fill phase, not a steady state.
• What did you get wrong first? A team that can name its own retracted findings has a methodology. A team that cannot has a marketing deck.

The AGLedger article ends with a list titled "What this does not show." Index growth has no asymptote yet. The handler was a no-op. The rig tops out at 8,000 jobs per second and that is the top of the hardware, not the top of the finding. I would trust a vendor who wrote that section about their own product over one who did not, every time, and I would encourage you to ask for it.

The number is never the decision. The method is.

Read the full measurement at agledger.ai, and the companion post on the xmin horizon. Both include the rig specification, the sampling query, and the sections on what the runs do not prove.