Cure HOT update degradation with fillfactor
Same table, same 1,200,000 updates. At fillfactor 100 only 319 of them were HOT and the heap grew to 59 MB. At fillfactor 70, 912,015 were HOT, 76.0%, and the heap stayed at 30 MB.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
A heap-only tuple update writes a new row version on the same page and skips the indexes entirely. When there is no free space on the page, PostgreSQL has to put the new version elsewhere and add an entry to every index on the table. The table and its indexes then grow far faster than the data does, and nothing in the query text changes to warn you.
courier_telemetry takes a steady stream of position updates. The column being written is not indexed, but the table and its indexes keep growing anyway and nobody can explain why.
Simple terms
When you update a row, PostgreSQL writes a fresh copy of it rather than editing in place. If there is spare room on the same page it can do a cheap HOT update that skips touching the indexes entirely. If the page is packed full, the new copy has to go on a different page and every index must be updated to point at it, so the table and its indexes balloon even though the data barely changed. Leaving a little free space on each page (a lower fillfactor) keeps more updates on-page and cheap.
Full runbook for this incident
- The full identify checklist, the exact signals that tell you it's this incident
- Every diagnostic query; lab output is attached only to the steps we actually captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
Card required. Cancel before day 7 and you are not charged.
More in this category
Other Query performance runbooks
Neighbouring incidents that share the same diagnostic surface.
Need the full procedure?
Pro runbooks finish the incident path
Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.