Some PostgreSQL tables do not grow because the product adds rows. They grow because the same rows get updated all day. Job state, session heartbeat, inventory count, delivery status, billing attempt, sync cursor. The table looks small from a business point of view and expensive from a storage point of view.
The pain is index churn. If an update changes indexed data, PostgreSQL must create new index entries. If the update can stay on the same heap page and no indexed column changes, PostgreSQL can use a HOT update: heap-only tuple. That is much cheaper.
HOT updates are one of those features that quietly decide whether an update-heavy table stays boring.
The rule for HOT updates
A HOT update can happen when the updated columns are not indexed and there is enough free space on the same heap page for the new tuple version. If you index every status, timestamp, and counter, you reduce the chance of HOT updates.
SELECT
relname,
n_tup_upd,
n_tup_hot_upd,
round((n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0)) * 100, 2) AS hot_update_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY hot_update_pct ASC
LIMIT 30;Fillfactor creates room for future updates
The default fillfactor packs pages tightly. That is good for mostly-read tables. For update-heavy tables, leaving space on each page can let the next row version fit locally and preserve HOT update opportunities.
ALTER TABLE job_runs SET (fillfactor = 80);
-- Repack or rewrite during a maintenance window if you need existing pages
-- to physically adopt the new free-space pattern.Index fewer volatile columns
A column that changes constantly is expensive to index. If the application updates last_seen_at every few seconds, indexing it globally may turn every heartbeat into index churn. Prefer indexes that match real read paths, partial indexes for active subsets, or summary tables for dashboards.
What I watch
- HOT update percentage by table.
- Dead tuple growth after update bursts.
- Index size growth on tables with stable row counts.
- Autovacuum duration and frequency.
- Queries that filter on volatile columns without enough selectivity.
The practical standard
The best PostgreSQL performance work is boring in the right way. Name the failure mode, capture the before plan or metric, make one change, and compare the exact same signal afterward. Anything else is just a more confident guess.