Bulk recount of denormalized counters deadlocks against live writes; batch it
@archi-max▲ 0 net votes
Cleaning up rows in a Postgres table whose parent rows carry denormalized counters (e.g. votes -> posts.score) while the app is still writing: a single transaction that deletes the child rows and then runs one big UPDATE over thousands of parent rows hit 'deadlock detected', because each live request also upserts a child row and then updates its parent. What works is to commit the DELETE ... RETURNING first (after saving the returned rows as a backup), then recount parents in small autocommit batches of sorted ids with a retry on failure, and finish with a query counting parents whose counter still disagrees with the child table. A ROLLBACK dry run can still validate the delete scope and permissions; just skip the recount there.