Loading today’s stories
Build1 distinct publisher3 min readUpdated
A 10M-row benchmark on Postgres 17 found the partial index barely quicker than a composite, 41x smaller, and unusable under a generic plan, where throughput fell from 11,752 tps to 7.
The Engineer · Build desk
Compiled by The EngineerSomething wrong?How this is made
The 1,673x is not a bug, and its shape is legible. A partial index can only be used when the query's predicate is matched against the index's WHERE clause at the time the plan is built, and a generic plan is built with the value withheld. That is what the author's own second point had said before any of it was measured: with a parameter like `status = $1`, the planner may not be able to pick the index [12]. What the measurement adds is the depth of the hole underneath the caveat.
Seven transactions per second under a forced generic plan is the same number the table returned with no index on it at all [15]. So a partial-index deployment on a generic plan is not a slower deployment, it is an unindexed one, doing 0.06% of the work it did a moment earlier [16]. The composite `(status, created_at)` is scanned either way [3]. Read those together and the 6.9% [1] looks like a premium the composite pays for indifference to plan shape, which is a cheap premium.
Then size. 7.6 MB against 310.4 MB [2] on an instance with 1 GB of shared_buffers [6] is 0.74% of the buffer pool against 30.3% [17]. That is the actual argument for the partial index, and it is a capacity argument: the composite tree grows with the table, the partial one stops, because what it covers is the 5,000 pending rows [7] rather than the ten million dead ones around them.
The churn result bounds how far that holds. The partial index bloated 380x in fifteen minutes and autovacuum never fired once [5]. If that multiple is measured against the 7.6 MB baseline, the small index is briefly a 2.8 GB one, nine times the composite it was chosen instead of, and larger than the whole buffer pool [18]. And the two indexes are not additive insurance. Keep the composite as a hedge against generic plans and you are carrying 318 MB of index anyway [22], which spends the entire reason the partial one was interesting.
On the harness: 8 pgbench clients, 30 seconds, three repeats, median [8]; the claim transaction takes one row `FOR UPDATE SKIP LOCKED` and returns it to pending with a fresh `created_at` [14], so dead tuples accrue at one per claim but producer-side inserts never happen [13]. Each tier was seeded into a template database and copied per run, with the seed shuffled so physical order does not track `created_at` [21], which is the difference between a benchmark and a flattering one. For reference, the plain `(status)` index topped out near 6,400 tps at all three tiers [10]. Of the six things the author told someone in August, four survived contact with a measurement [20].
Follow any of these and your For You feed starts watching them — no settings page required.
Ranked by verification strength, evidence, and original report placement.
With status bound as a parameter and a generic plan forced, the partial index is not scanned at all: 11,752 tps becomes 7 tps and 0.68 ms becomes 1.1 seconds, a 1,673x regression. The composite index is untouched.
Against a composite index, the partial index measured 6.9% faster, which the author characterises as noise, concluding speed is the wrong reason to choose it.
At 10M rows the partial index is 41x smaller than the composite index, 7.6 MB against 310.4 MB, and it stops growing because it indexes the queue rather than the table.
Postgres declined the generic plan in 40 executions out of 40; the author had to set plan_cache_mode = force_generic_plan by hand.
Under sustained churn the partial index bloated 380x in fifteen minutes and autovacuum never ran once; the author's conclusion is that a small index means cheaper vacuuming, needed more often.
The test ran Postgres 17 in one container with shared_buffers 1 GB, work_mem 64 MB, and autovacuum left on.
Evidence-backed comparisons of source perspectives and observed adoption signals. Read the methodology
Which Builder, Operator, and Investor concerns the observed source mix emphasized—not a truth score.
Evidence, demonstrated adoption, hype gap, incentives, and confidence are assessed independently, each on its own current evidence. How these are measured.
Detailed, self-disclosing single-author benchmark
The source publishes enough to be re-run: Postgres version, shared_buffers/work_mem/autovacuum settings, table DDL, the exact claim transaction, four index strategies applied one at a time, pgbench parameters with medians over three repeats, template-database seeding with shuffled physical order, and index scan / pg_prepared_statements counters instead of timing inference. It also names its own limits (no producer-side inserts) and retracts an earlier wrong inference about plan_cache_mode = auto. Evidence is capped below high confidence because it is one author, one container, one Postgres version, with no independent replication and no absolute figure behind the 380x bloat multiplier.
No adoption signal in supplied sources
The cluster contains one lab benchmark and no deployment, usage-disclosure, release, pricing or licensing evidence. Nothing in the supplied material shows any team adopting, dropping or changing partial indexes on production queue tables, so adoption cannot be scored without inventing facts.
Headline cliff mildly overstated, disclosed in body
Slightly positive: the framing that a queue is 'one bound parameter from 7 tps' overstates default behaviour, because the 1,673x collapse required setting plan_cache_mode = force_generic_plan by hand and Postgres declined the generic plan in 40 of 40 executions — a caveat the article itself supplies, along with a retraction of its earlier 'degrades as it warms up' claim. The gap is small rather than large because the piece actively deflates its own most marketable finding (calling the 6.9% speed edge noise) and because the 41x footprint benefit is presented as an architectural choice, not a performance win; it is not negative because the footprint saving as headlined assumes the composite index is dropped, and a single container's numbers are presented with broad applicability.
Low commercial incentive, mild credibility incentive
The supplied source shows no vendor, sponsor, product or funding interest: it is an individual developer-platform post testing advice the same author gave earlier, and it publishes two results that make the author's prior guidance wrong. That structure argues against promotional distortion. A modest residual incentive remains — a self-audit and a dramatic 1,673x figure both serve author credibility and engagement on a developer platform — so the score is low but not zero.
Method-transparent but single-source and unreplicated
Confidence is moderate: the methodology is unusually explicit and internally consistent, and counter-based findings reduce reliance on interpretation. But there is exactly one publisher and one author, no independent reproduction, adoption is unmeasurable from the supplied material, one derived magnitude (absolute bloat size) is unsupported, and generalisation beyond Postgres 17 in a single container with a 5,000-row live set is untested.
build
Your ORM never puts a WHERE clause in an index, and that is where the seq scans live1 distinct publisher
build
Your "Index Only Scan" Did 2,847 Heap Fetches: Covering Indexes Are a Vacuum Problem1 distinct publisher
build
A NetworkPolicy in another repo broke invoicing while every dashboard reported success1 distinct publisher
build
The optional EntityManager is the bug: moving the transaction boundary into AsyncLocalStorage1 distinct publisher
Distinct publishers with included, body-backed reporting in this cluster.
dev.to
1 article · August 23, 2026