How many indexes are too many on a hot table?
One more index on a hot table that already carries many
Note: this works and blocks nothing, but a performance regression is likely.
What happens
Every index on a table is maintained on every INSERT, on every DELETE, and on every UPDATE that touches an indexed column (or cannot use a HOT update). On a table taking sustained write traffic, the ninth index is not free: it is another page write, more WAL, and more vacuum work on the hottest path in the system. The right response is usually to drop an index nobody uses before adding one.
Why it is dangerous on a populated table
Nothing blocks, but the cost scales with the table and shows up as a slower query plan or a longer maintenance window rather than an outage.
Fires on
CREATE INDEX CONCURRENTLY idx_events_region ON events (region);The safe pattern
Before adding, retire: the live snapshot names the indexes with zero scans since the last stats reset, drop those CONCURRENTLY first, or fold the new column into an existing composite index. On a table with little write traffic this rule stays silent.
-- Retire an unused index first (own migration): DROP INDEX CONCURRENTLY idx_events_legacy_flag;
-- Then, in its own migration, add the one you need:
CREATE INDEX CONCURRENTLY idx_events_region ON events (region);Fixtures
The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.
Fires (1)
SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_events_region ON events (region);Stays silent (1)
SET lock_timeout = '5s';
DROP INDEX CONCURRENTLY idx_events_legacy_flag;
CREATE INDEX CONCURRENTLY idx_events_region ON events (region);How to check locally
This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:
npx bolvrk check migration.sql --remote