# Database & queries

> What does a data-model decision cost once the data volume is real?

- Status: Open
- Record count: 3
- Source: https://www.muhammetsafak.com.tr/en/research/program/veritabani/
- Language: en-US
- Author: Muhammet Şafak

---
What index strategy, query plans and connection handling cost once the data volume is real — schema and migration decisions included.

## Findings ledger

### A partial index makes a queue table forty-one times smaller — for as long as the planner picks it

- Question: On a Postgres queue table with millions of dead rows, what does a partial index buy, and when does the planner refuse to use it?
- Finding: At 10 million dead rows a partial index sustains 11,537 claims per second where the same table without one manages 7. Against a composite index the throughput difference is small (6.9%) but the size difference is not: 7.6 MB against 310.4 MB, and the partial one does not grow with the table because it indexes only the 5,000 live rows. None of that is the real finding. The moment the planner switches a prepared statement to a generic plan the partial index stops being used at all — 11,752 tps becomes 7, and 0.68 ms becomes 1.1 seconds. A factor of 1,635. The composite index is untouched under the same conditions.
- https://www.muhammetsafak.com.tr/en/research/partial-index-queue-table/
- Markdown: https://www.muhammetsafak.com.tr/en/research/partial-index-queue-table.md

### The partial index grew three hundred and five times in fifteen minutes — and autovacuum never ran

- Question: Under sustained churn, does a partial index stay small on a queue table, and do the default autovacuum settings keep up with it?
- Finding: With the live set holding steady at five thousand rows the partial index went from 0.125 MB to 38.2 MB — three hundred and five times. Its smallness comes from the live set, its bloat rate comes from throughput, and nothing connects the two. The composite index bloated less in proportion (42%) and more in absolute terms (+126 MB), and while bloating it stopped fitting in memory: its latency went from 0.52 ms to 61 seconds and its backlog climbed to 126,000. Fifteen minutes produced 1.75 million dead rows and autovacuum **did not run once** — the default threshold scales with the whole table (50 + 0.2 × 10 million ≈ 2 million) while the churn happens in a tiny subset.
- https://www.muhammetsafak.com.tr/en/research/queue-table-bloat-and-autovacuum/
- Markdown: https://www.muhammetsafak.com.tr/en/research/queue-table-bloat-and-autovacuum.md

### Postgres never turned the partial index into a generic plan: forty executions, forty custom plans

- Question: Does Postgres switch a partial-index query to a generic plan on its own inside a prepared statement — or is the 1,635-fold cliff something you have to opt into?
- Finding: Postgres declines. On the partial index all forty executions used a custom plan — the counter reads 40/0. The reason it declines is the disaster itself: a generic plan cannot use the partial index, so its estimated cost comes out high and the planner does not choose it. The composite index switches at the sixth execution exactly as documented (5/35) and loses nothing by it. So the 1,635-fold cliff is real but fenced: reaching it takes writing `plan_cache_mode = force_generic_plan`.
- https://www.muhammetsafak.com.tr/en/research/when-does-the-planner-go-generic/
- Markdown: https://www.muhammetsafak.com.tr/en/research/when-does-the-planner-go-generic.md