In September, my search engine had a job that kept getting slower. Once per PageRank cycle, it wrote the new authority score into every row of the url table, about 150 million rows. Right after a database upgrade, that step took 64.5 minutes. Two weeks later, one run was still going after 13.5 hours. Because the crawlers pause while it runs, crawling had been stopped for roughly 100 to 120 of the previous 190 hours.
The cause was table bloat, and the fix that went in was half right. Writing this up meant going back to PostgreSQL's documentation, which showed that the half that sounded most convincing couldn't have worked for the column that mattered.
What the database was doing
The diagnosis was done live on the production database. pg_stat_activity showed the update waiting mostly on DataFileRead, real random disk reads rather than locks or CPU, with each chunk taking 5 to 35 seconds instead of the under 2 seconds the original timing implied. pg_stat_user_tables showed dead rows in url climbing to 12.8 million, about 8.5% of the table, while the update was still running: the job was generating the bloat that slowed down its own later chunks, and every following cycle started from a worse position.
The link table, with 694 million rows, had its own problem: 84.8 million dead rows, 12.2%, and no autovacuum run since the upgrade had reset its statistics.
Fix one: autovacuum thresholds
Autovacuum decides when to clean a table using a scale factor, by default 0.2: it waits until about 20% of the rows are dead. On a 694-million-row table, that's around 139 million dead rows before it even starts. The fix set autovacuum_vacuum_scale_factor to 0.05 on just these two tables, so cleanup starts at about 5% and does less work each time. It's a storage parameter, changed with a lock that doesn't block reads or writes, so it went in while the slow job was still running.
That part is sound. It doesn't stop bloat from being created, but it stops it from piling up between cleanups.
Fix two: fillfactor, and why it couldn't help authority
The second change set fillfactor = 85 on url, leaving 15% of each page empty. The reasoning, written down in the design note at the time, was that this would let future authority updates be HOT updates instead of rewriting all twelve of the table's indexes.
HOT, heap-only tuple, updates are PostgreSQL's way of updating a row without adding new index entries for the new version. PostgreSQL's documentation lists the conditions, and there are two. There must be enough free space on the page for the new row, which is what fillfactor provides. And the update must not modify any column referenced by the table's indexes (BRIN indexes excepted).
The same design note points out that one of url's twelve indexes covers authority itself: according to the migration that created it, it's on (host_id, crawled, boilerplate, authority). The job's whole purpose was to change authority. So every one of those 150 million updates modified an indexed column, and none of them could be a HOT update, whatever the fillfactor. The free space would sit unused by the very job it was meant for.
The fillfactor change wasn't useless. The note also mentions quality and category updates, and no index in the project's migrations references those columns, so those updates can be HOT when the page has room. It just wasn't a fix for the authority job.
What would have addressed it
For the authority update to be HOT, authority would have to stop being an indexed column of that table. That means either an index that no longer includes it, if the queries using it could live without it, or keeping a frequently rewritten score in a narrow table of its own, so that rewriting it never touches the big table's indexes. Both are real design changes, with query plans to check first, not a storage parameter. The note's own next steps were different: reassess after a couple of weeks, and then consider pg_repack or a scheduled VACUUM FULL to rebuild the bloated table.
No reassessment was ever recorded: the project's last commit is from ten days later, and the project has since been shut down. So there's no measured result to report for either change, and I'm not going to invent one.
What generalises
If a job updates every row of a large table on a schedule, check two things before reaching for fillfactor: whether the column it writes is in any index, and when autovacuum will start on a table that size. The first decides whether HOT updates are possible at all. The second decides how much dead weight builds up between cleanups. A fix whose mechanism can be checked against the documentation in five minutes should be checked before it's written down as the reason the problem will go away.
Leave a comment
Comments are reviewed before they appear.