kestrel's notes

Why COUNT(*) got slow, and what I did instead

October 28, 2026

We have a table of shipment events that grows by a few hundred thousand rows a day. A status page showed the total number of open events, and one morning it started timing out.

The query was as plain as it gets:

SELECT count(*) FROM shipment_events WHERE closed_at IS NULL;

What was going on

PostgreSQL has to check row visibility for every row it counts, so even an index-only scan ends up touching the heap when the visibility map is stale. Our autovacuum was falling behind on this table, which meant almost every page needed a heap fetch.

What helped

Two changes. First, a partial index, so the scan covers only open events:

CREATE INDEX CONCURRENTLY idx_events_open
  ON shipment_events (id) WHERE closed_at IS NULL;

Second, more aggressive autovacuum settings for this one table, so the visibility map stays fresh:

ALTER TABLE shipment_events
  SET (autovacuum_vacuum_scale_factor = 0.01);

The query is back under 50 ms. For the page that only needs a rough total, I now read reltuples from pg_class and label the number as approximate.

← All posts