Hacker News Reader

Could you elaborate on why there'd be a smaller number of dead tuples? Are you just taking into account the autovacuuming that can take place as you continue through the iterations?

View on HN

Primarily, yes. Once autovacuum is marking dead tuple space as reusable (dead tuple space that was largely created by earlier iterations of the loop issuing your query), subsequent iterations can use that space for their new tuples, and the table should reach steady-state size, or at the very least, grow much more slowly. This is even more likely when you make autovacuum more aggressive on the table in question while you're doing this.

Additionally, however, if you're bulk rewriting the table in one query, autovacuum has no (or very limited) opportunity to mark dead tuples as truly dead — especially if there's any lock contention going on, so other, older xids are waiting for your bulk update to complete — so in the worst case your table is half dead tuples, and twice the size it needs to be.

Reply on news.ycombinator.com