Increase database performance and reduce storage needs

[rtibbles]

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

Overview

• Studio's production database is 1,202 GB; contentcuration_contentnode accounts for 957 GB of it across 16.8M rows.
• contentnode has never had ANALYZE run, so the planner chooses plans for the largest table in the database without statistics.
• Autovacuum has never run on contentnode or assessmentitem (vacuum_count = 0, autovacuum_count = 0).
• contentnode's indexes measure ~2,000 bytes per entry, against 24–34 bytes for healthy indexes on other tables in the same database — buffer cache is largely fragmented index pages.
• 400 GB of the table is base64 thumbnail data in a TextField (models.py:2116), duplicating File records that already exist in the content bucket.

Scope

In scope

• Per-table autovacuum and analyze thresholds for contentnode, assessmentitem, and file.
• The instance-level autovacuum cost limit, currently -1 and falling back to vacuum_cost_limit = 200.
• Removal of 12 never-scanned indexes on contentnode and file, totalling 178 GB, around half of them Django's varchar_pattern_ops twins. A further 39 GB of never-scanned indexes on other tables is measured but not scoped here, including the 33 GB twin on contentnode's primary key, which is deferred with the uuid migration.
• Reindexing contentnode's surviving indexes to reclaim fragmented pages.
• Relocation of contentnode.thumbnail_encoding base64 data to File records in the content bucket.
• A cap on Cloud SQL disk autoresize, which the Terraform db module does not manage.
• Reducing the production instance tier.

Out of scope

• DMS migration onto a smaller-disk instance, which this work unblocks.
• Migrating char primary keys to native Postgres uuid.
• MPTT write amplification.

Strategy

Ordering is load-bearing.

1. Autovacuum and analyze thresholds. Later gains re-accumulate without them.
2. Drop never-scanned indexes. DROP INDEX CONCURRENTLY needs no additional disk, and frees headroom for the steps below.
3. Relocate thumbnail data. 16.8M UPDATEs plus content-bucket writes, so it depends on step 2's headroom and reduced index count.
4. Reindex contentnode's surviving indexes, one at a time, largest first. Must follow step 3, or the UPDATEs re-bloat freshly built indexes.
5. Reduce the instance tier.

Each reindex needs free space equal to the index being rebuilt, so headroom compounds through step 4. Btree deduplication applies only to freshly built indexes, so low-cardinality columns such as original_channel_id (877 distinct values across 16.8M rows) shrink only there.

Steps 3 and 4 run as management commands invoked from make deploy-migrate, per the procedure at Makefile:32-40. Both are exercised on hotfixes, whose database holds a full-size copy of the production data.

Nulling thumbnail_encoding leaves 400 GB of TOAST dead but not returned to the OS; VACUUM only marks it reusable within the relation.

Testing Requirements

• Capture the same measurements before and after each step: pg_database_size, per-table heap/index/TOAST split, and per-index size, idx_scan, and bytes-per-entry.
• Verify every step on hotfixes before master.
• Confirm pg_index.indisvalid after any CONCURRENTLY operation; a failed build leaves an invalid index needing manual cleanup.
• Confirm no new slow-query-log entries attributable to dropped indexes. log_min_duration_statement is already 500ms.
• Confirm last_autovacuum and last_analyze are non-null and recent on contentnode, assessmentitem, and file after step 1.

AI usage

Drafted with Claude Code, which measured the production database directly through read-only queries run via the disposable debugging job; the table, index, bytes-per-entry, vacuum state, and column-width figures here come from those measurements. I confirmed the findings against the Terraform and Django source, and set the framing, scope, sequencing, and target branch.