Set per-table autovacuum and analyze thresholds on large tables

❌ This issue is not open for contribution. Visit Contributing guidelines to learn about the contributing process and how to find suitable issues.

Overview

• Default autovacuum_vacuum_scale_factor = 0.2 defers vacuum until 20% of a table's rows are dead — 3.36M tuples on contentnode, 11.9M on assessmentitem.
• Neither table has ever been vacuumed or analyzed, leaving the planner without statistics for the largest table in the database.

Complexity: Low
Target branch: hotfixes

Context

• ALTER TABLE ... SET (autovacuum_*) is metadata-only and does not rewrite the table, but takes a brief ACCESS EXCLUSIVE lock. On contentnode that can queue behind a long-running transaction and block everything behind it — gunicorn's timeout is 4000s, so long transactions are possible.
• pg_class.reloptions is currently null for all three tables, and Django exposes no model-level API for storage parameters.
• The app image has no psql, so ANALYZE must be issued through a database cursor.

The Change

• A migration should set per-table autovacuum and analyze thresholds on contentnode, assessmentitem, and file: autovacuum_vacuum_scale_factor = 0.01 and autovacuum_analyze_scale_factor = 0.005.
• file should additionally get autovacuum_vacuum_insert_scale_factor = 0.05 — at 113M insert-heavy rows the dead-tuple threshold alone never fires, but the visibility map still needs maintaining.
• The migration should set a lock_timeout and fail rather than retry, so a blocked ACCESS EXCLUSIVE acquisition does not queue readers behind repeated attempts.
• A general-purpose management command should run ANALYZE against tables named at invocation, issued through a database cursor.
• make deploy-migrate should invoke that command for the three tables, per the procedure at Makefile:32-40.

Acceptance Criteria

General

• pg_class.reloptions on contentcuration_contentnode, contentcuration_assessmentitem, and contentcuration_file contains autovacuum_vacuum_scale_factor=0.01 and autovacuum_analyze_scale_factor=0.005
• contentcuration_file additionally has autovacuum_vacuum_insert_scale_factor=0.05
• The migration reverses cleanly, resetting all three tables to no storage parameters
• The migration aborts with a non-zero exit when it cannot acquire its lock within lock_timeout
• A management command runs ANALYZE against tables passed as arguments
• make deploy-migrate invokes that command for the three tables

Testing

• last_analyze is non-null on all three tables after the deploy step runs
• last_autovacuum becomes non-null on contentnode within 24 hours of the thresholds applying
• Unit test covers the command's table-argument handling and its error on an unknown table

AI usage

Drafted with Claude Code, which measured the vacuum state, analyze state, and row counts cited here through read-only queries against the production database. I confirmed the deploy-migrate procedure and migration precedent against the Studio source, and set the threshold values and the fail-loudly behaviour.

Start by reviewing the migration conventions and the deploy procedure at Makefile:32-40, then locate the management-command entry point used for database operations. Implement the reversible table settings, lock-timeout failure behavior, and table-argument validation described here. Done means the migration, ANALYZE command, deploy invocation, and listed tests satisfy the acceptance criteria.