Three ways to measure a Postgres queue table, three different answers
By Michael Cooper · Founder
We ran a pg-boss job table at 8,000 jobs/s for eleven and a half hours to answer one question: does a one-hour retention window keep it bounded? The heap answer is yes. Getting there took several passes, and most of the wrong answers failed the same way.
The short version
A Postgres heap under a retention window does not creep and it does not sit still. It reaches a high-water mark and then oscillates, including downward, because VACUUM truncates trailing empty pages and hands that space back to the operating system.
That property defeats the two obvious ways to ask whether a table is growing. Fit a line to the whole post-fill window and you get +3.2% a day; fit it to the worst 20 minutes and you get +72% a day. Count file extensions and wait for quiet and you get settled, three hours before the next extension. Take the peak-to-trough range over a window holding many cycles and you get a 0.84% band ending below its own peak.
The heap is the easy half. On the same run the indexes reached 2,387 MB and were still growing at about +26 MB/h when we stopped.
What was measured, and on what
One m7i.xlarge (4 vCPU, non-burstable) per rate point, one rate point per instance, so no two points share I/O. PostgreSQL 18.6 in Docker on the same instance as the load driver, 200 GB gp3 at 6,000 IOPS and 250 MB/s. Server settings shared_buffers=4GB, work_mem=32MB, maintenance_work_mem=512MB, max_wal_size=8GB, checkpoint_timeout=15min. Both fsync and synchronous_commit are on.
Queue configuration: deleteAfterSeconds: 3600, maintenanceIntervalSeconds: 30, expireInSeconds: 300, retryLimit: 5 with backoff. The handler is a no-op, which maximises churn per second.
Measure the leaf partition, not the parent
pg-boss creates its job table with PARTITION BY LIST (name). A partitioned parent has no storage of its own, so pg_relation_size('pgboss.job') returns 0 no matter how much data is in the queue. Both rows, from the 8,000 jobs/s run reported below, sampled at t=28,206s:
relname | heap_bytes | indexes_bytes ------------+--------------+--------------- job | 0 | 0 job_common | 9235193856 | 2345754624
Every size in this post is the leaf. One queue means one leaf partition, job_common, sampled every 30 seconds with:
SELECT relname,
n_live_tup, n_dead_tup,
vacuum_count, autovacuum_count, analyze_count, autoanalyze_count,
pg_relation_size(relid) AS heap_bytes,
pg_indexes_size(relid) AS indexes_bytes
FROM pg_stat_user_tables
WHERE schemaname = 'pgboss';pg_stat_user_tables lists only relations with storage, which is why this returns the leaves and skips the parent. If you prefer to work from the parent, sum across pg_partition_tree(). Heap and index sizes are exact. Dead-tuple figures are the statistics collector’s estimate, reported throughout as n_dead_tup / (n_live_tup + n_dead_tup) and read as a median over a window rather than off a single sample. Sizes are quoted in decimal MB (bytes divided by one million), so they read about 5% larger than the MiB pg_size_prettyprints. A "heap extension" below means an increase of more than 1 MB between consecutive samples.
1. Why does fitting a line to table size invent a trend?
Because a heap has no slope to fit. It changes in discrete page extensions and truncations, and a line averages them into a rate that describes neither.
Take the honest version first. Fit least squares across the entire post-fill window, no window selection at all, 10.3 hours and 1,236 samples:
window t=4200s to t=41255s (10.3 hours, all post-fill samples) least-squares +12.3 MB/h on a 9089 MB table extrapolated +0.14%/h, about +3.2% per day VERDICT: growing.
That is the wrong answer, on a table whose last sample sits 0.45% below its own all-time peak. No cherry-picking was involved. A series that steps up and then truncates back down has a positive least-squares slope whenever the sampling window happens to contain more of the rising edges, and there is no window length that reliably fixes it.
Window selection makes it worse. Search the post-fill series for the steepest 20-minute window and it reports this:
window t=17254s to t=18454s (worst 20 minutes, selected adversarially) least-squares +277 MB/h on a 9233 MB table extrapolated +3.0%/h, about +72% per day VERDICT: growing, and fast enough to page someone.
A table gaining 72% a day is a capacity incident by Thursday. That window spans 1,200 seconds and contains four distinct heap values, rising from 9,233.1 MB to 9,296.2 MB and then stopping. It is the steepest 20 minutes in the run, so treat it as an upper bound on how wrong a fit can be rather than as a typical result. The full-window fit above is the typical result, and it is also wrong.
2. Why doesn’t counting heap file extensions work?
Because it watches one edge of the cycle. Extensions are visible between samples. Truncations are not events, and nothing fires when the file gets smaller.
A heap does not drift, so the better instinct is to count the steps and wait for them to stop:
heap file extensions after the fill: 17 longest quiet stretch 171 minutes, t=17947s to t=28206s VERDICT: settled. Any reasonable stopping rule fires here. ...and then it extended again at t=28206s.
Two things are wrong with that verdict. The obvious one is that the heap extended again 171 minutes later, and kept extending until t=38,767s, near the end of the run. An hour of no change and two hours of no change both fire inside that gap.
The subtler one is that the table was not idle during the quiet stretch. It took six distinct values, falling from a peak of 9,296.3 MB to a trough of 9,218.8 MB before ending the stretch at 9,235.2 MB: 77.5 MB peak to trough, 61.0 MB start to end. It also moved by 65,536 bytes at t=19,835s, which is eight pages and far under the 1 MB threshold, so "byte-identical" would be wrong even where the megabyte figure holds still. The tool called the stretch quiet because it detects an extension as an increase between samples and cannot see a decrease at all.
That is the structural problem, and it is worse than the false stop. A method built on extensions has an output space containing only "grew" and "silent". On a series that rises and falls it cannot return the right answer for any window you give it.
3. What does a bounded queue table actually look like?
A narrow band, entered from below, ending at or under its own peak. Not a flat line.
window t=20628s to t=41255s (5.7 hours, many retention cycles) trough / peak 9218.8 MB / 9296.3 MB band 0.84% ended 9254.8 MB, -0.45% against its all-time peak VERDICT: bounded. It breathes inside a 1% band and ends below its peak.
The mechanism is documented behaviour. The PostgreSQL manual states that standard VACUUM "will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained". Under a retention window that deletes in age order, trailing pages emptying is the normal case rather than the special one, so truncation fires repeatedly and the file oscillates.
Same series, three methods, with the fit shown at two window choices:
| Method | Reports | Action it implies |
|---|---|---|
| Least-squares, whole post-fill window | +3.2% / day | Plan a migration |
| Least-squares, worst 20-minute window | +72% / day | Page someone |
| Count extensions, wait for quiet | settled | Ship it |
| Peak-to-trough, multi-cycle window | 0.84% band | Bounded, size it and move on |
4. How long do you have to run before a plateau is real?
At three of the four rates measured, the heap was still taking extensions nine to eleven retention cycles in.
A two-hour run against a one-hour retention window contains one deletion cycle: enough to see the fill and the first delete, not enough to distinguish a plateau from a slow slope. Here is where the last heap extension actually landed on each arm of the completed run:
| Rate | Last extension at | = retention cycles | Run length |
|---|---|---|---|
| 1,000 jobs/s | 3,690s | 1.02 | 11.4 h |
| 2,000 jobs/s | 34,261s | 9.52 | 11.4 h |
| 4,000 jobs/s | 34,352s | 9.54 | 11.4 h |
| 8,000 jobs/s | 38,767s | 10.77 | 11.5 h |
Only the 1,000 jobs/s arm reached a final extension early. The other three were still occasionally extending in the last hour of an eleven-hour run, which is the point: they are bounded in band terms and they never stop moving. "It stopped extending" is not a state a queue table under continuous load reliably reaches, so it is not a test you can wait for.
This also disposes of a shorter benchmark. At 8,000 jobs/s the last extension arrives at 10.77 retention cycles. A two-hour run sees one, a four-hour run sees four, and none of them can tell you what the twelve-hour run does.
5. What does one hour of retention actually cost?
Between 1.11 and 1.16 GB of heap per 1,000 jobs/s, rising slightly with rate.
Second half of each run, entirely post-fill. The first four rows are the configuration we ship; the last is a stock-autovacuum control at the same rate, included for reference and discussed in the next section.
| Rate | Trough | Peak | Band | Ended vs all-time peak | GB per 1k/s, at peak |
|---|---|---|---|---|---|
| 1,000 jobs/s | 1,109.1 MB | 1,109.9 MB | 0.07% | -0.03% | 1.110 |
| 2,000 jobs/s | 2,222.3 MB | 2,226.2 MB | 0.18% | -0.05% | 1.113 |
| 4,000 jobs/s | 4,483.0 MB | 4,581.8 MB | 2.20% | -2.02% | 1.145 |
| 8,000 jobs/s | 9,218.8 MB | 9,296.3 MB | 0.84% | -0.45% | 1.162 |
| 8,000, stock autovacuum | 11,764.0 MB | 12,151.8 MB | 3.30% | -3.19% | 1.519 |
The last column is the second-half peak divided by the rate. The cost per 1,000 jobs/s is not quite linear: 1.110, 1.113, 1.145 and 1.162 GB as the rate goes up eightfold. Every arm ended below its own peak.
6. Does autovacuum tuning change the shape or the size?
Both. On this run it holds the heap 23.6% smaller and the band four times tighter.
We set autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor to 0.01 on the job partition, against PostgreSQL defaults of 0.2 and 0.1. Both arms below are the same rig at 8,000 jobs/s with a one-hour retention window, compared over the window both runs cover (t=20,646s to t=37,715s), medians rather than single samples.
| Measure | Stock | Tuned | Delta |
|---|---|---|---|
| Heap, median | 12,108.7 MB | 9,250.4 MB | 23.6% smaller |
| Indexes, median | 2,793.5 MB | 2,349.4 MB | 15.9% smaller |
| Dead tuples, median | 22.0% | 4.4% | 5x lower |
| Peak-to-trough band | 3.30% | 0.84% | 4x tighter |
| Autovacuum cadence | 0.21 / min | 1.00 / min | at the naptime floor |
| Autoanalyze cadence | 0.25 / min | 0.98 / min | 4x more often |
autovacuum_naptime defaults to one minute, so one run per minute is the ceiling any scale factor can buy. The tuned arm sits on that ceiling for both vacuum and analyze. Any scale factor low enough to keep the table over threshold every naptime produces the same cadence, so 0.01 is not a tuned value so much as one comfortably clear of the floor at these row counts.
One caution on this pair: the stock control ran 10.5 hours against the tuned arm’s 11.5, and the window above is the overlap. The window above is a steady-state slice inside the span both runs cover. A longer stock run might close some of the heap gap, though its band is wider throughout.
A compressed retention window hides all of this
At a 360-second retention window this same A/B returns no difference at any rate. Both arms record exactly 20 autovacuums in 1,200 seconds, one per 60 seconds, because that is autovacuum_naptime and neither arm can beat it. The tuned arm asks for far more and gets one. If you compress the clock to make a bloat experiment finish sooner, this is the variable you have compressed out of it.
7. Is a bounded heap a bounded table?
Not on this run. The heap settled and the indexes did not.
Every "bounded" verdict above is about heap bytes. The indexes on the same table, over the same runs, measured with pg_indexes_size:
| Rate | Index size at end | Slope, post-fill | Heap at end |
|---|---|---|---|
| 1,000 jobs/s | 293.0 MB | +3.2 MB/h | 1,109.8 MB |
| 2,000 jobs/s | 585.2 MB | +6.1 MB/h | 2,225.2 MB |
| 4,000 jobs/s | 1,174.5 MB | +13.1 MB/h | 4,489.2 MB |
| 8,000 jobs/s | 2,387.2 MB | +26.4 MB/h | 9,254.8 MB |
At 8,000 jobs/s the indexes are 2.4 GB against a 9.25 GB heap, and they were still climbing when we stopped. A slope is the wrong instrument for the heap because the heap truncates; the indexes do not truncate, so a slope is the right instrument there, and it is positive at every rate after eleven hours.
We do not know where index size settles, or whether it does within a duration anyone would run. That is the open number from this work. It also means the practical answer to "how big does this table get" at 8,000 jobs/s is not 9.25 GB, it is at least 11.6 GB and still moving.
The sampling consequence: pg_indexes_size on its own line, on the same interval as pg_relation_size. A single "table size" number that sums them hides which half is moving, and a heap-only number denies the second half exists.
8. So what should you measure instead?
- Sample
pg_relation_sizeandpg_indexes_sizeseparately, on the leaf partitions, on a fixed interval. Thirty seconds was fine for a one-hour window. - Run for many retention cycles. At 8,000 jobs/s the last heap extension here landed at 10.77 cycles, so budget accordingly.
- Discard the fill phase explicitly rather than by eye. Retention has not fired yet, so nothing before the first deletion is steady state.
- For the heap, report the band (trough to peak) over the remaining window and where the run ended relative to its all-time peak. Do not report a slope.
- For the indexes, report the slope. They do not truncate, so a trend line means what it says.
- Read
n_dead_tupas a median over the steady window, never off the last sample. A sample landing just before an autovacuum catches the top of the sawtooth: on a separate arm of this rig (a per-queue partition comparison running a compressed 360-second retention window), one final sample read 27.0% dead against that arm’s own 13.2% median, which is a 2.0x overstatement from one badly timed read.
The decision rule the band gives you: call the heap bounded when the run ends at or below its all-time peak and the band over the last several cycles is stable. A series still setting new peaks at the end of the window is growing, whatever its band width. Band width alone is not the test; 3.30% and 0.84% are both bounded here.
What this does not show
The table is not shown to be bounded, only the heap. See section 7. Index growth was positive at every rate at the end of the run and we have no asymptote for it.
8,000 jobs/s is the top of the rig, not the top of the finding. Producer, consumer and Postgres share 4 vCPU, so above that rate this becomes a driver measurement rather than a retention one. We do not know where the retention window itself stops holding, and the per-rate heap cost is already mildly superlinear across the range we could measure.
The handler is a no-op. Real work holds a transaction while it runs; ours completes instantly. That maximises churn per second, which is conservative for bloat, but it means this run contains no long-running transactions. That is a separate mechanism and the subject of the companion post on the xmin horizon.
Dead-tuple counts are estimates from the statistics collector rather than pgstattuple. Heap and index sizes are exact.
This is not a comparison against other queues. There is a public multi-system Postgres queue benchmark with a different contract, different hardware, different job shape and chaos injection we do not run. The question here is narrower: where our own retention setting stops holding on this hardware. We also built no pg-boss-defaults arm, because at a steady drained rate the default seven-day window is arithmetic rather than a measurement. The companion post covers what happens when the rate is not drained, which is where that arithmetic stops applying.
One queue, one workload, no replicates. No OLAP alongside, no other tenants, no chaos injection, and one instance per rate point.
Sources & further reading
- PostgreSQL 18: Recovering Disk Space - the trailing-page truncation exception that makes the heap oscillate
- PostgreSQL 18: Automatic Vacuuming - defaults for
autovacuum_naptime(1 min),autovacuum_vacuum_scale_factor(0.2) andautovacuum_analyze_scale_factor(0.1) - PostgreSQL 18: pg_stat_all_tables -
n_dead_tup,autovacuum_countandautoanalyze_count, and why the tuple counts are estimates - PostgreSQL 18: Database Object Size Functions -
pg_relation_size,pg_indexes_sizeandpg_partition_tree - PostgreSQL 18: Table Partitioning - why a partitioned parent reports zero bytes
- pg-boss on GitHub (MIT) - version 12.27.0 was used throughout
- hardbyte/postgresql-job-queue-benchmarking - the public multi-system harness referenced above, testing a different contract than this run
Related