Increase database performance and reduce storage needs #6078

❌ 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,337 GB; contentcuration_contentnode accounts for 1,028 GB of it across 16.6M rows: 125 GB heap, 472 GB indexes, 431 GB TOAST.
• Autovacuum runs on contentnode only once 20% of its rows are dead, which has happened once since 2026-06-09.
• contentnode's indexes measure ~2,000 bytes per entry, against 24–32 bytes for healthy indexes on other tables in the same database — buffer cache is largely fragmented index pages.
• About 355 GB of the TOAST is base64 thumbnail data in thumbnail_encoding, a TextField. It holds the user's cropped thumbnail and crop settings, which no File record stores.

Scope

In scope

• A per-table autovacuum scale factor for contentnode.
• The instance-level autovacuum cost limit.
• Removal of 12 redundant indexes on contentnode and file, about 275 GB, eight of them Django's varchar_pattern_ops twins.
• Storing each thumbnail as a single 400×225 PNG File and dropping thumbnail_encoding.
• Removing slideshow support and its 6.6 GB index (Remove slideshow support #6353).

Out of scope

• Compacting tables, reducing the instance tier and shrinking the disk, which ride on the prod database recreate in learningequality/infrastructure#735.
• Migrating char primary keys to native Postgres uuid.
• MPTT write amplification.

Strategy

Ordering is load-bearing.

1. Autovacuum scale factor (Set a per-table autovacuum scale factor on contentnode #6079). Later gains re-accumulate without it.
2. Drop unused indexes (Make file_checksum_file_size_idx unconditional again #6348, then Drop redundant indexes on contentnode and file #6350). DROP INDEX CONCURRENTLY needs no additional disk.
3. Store thumbnails as Files (Store each thumbnail as a single 400×225 PNG and stop using thumbnail_encoding #6351, then Drop ContentNode.thumbnail_encoding #6352). Rewrites 10.85M file rows, so it follows step 2 rather than churning indexes about to be dropped.
4. Recreate the prod database (learningequality/infrastructure#735), copying the data logically so tables and indexes arrive compacted, on a smaller tier and disk.

Dropping thumbnail_encoding leaves its ~355 GB of TOAST on disk until step 4 rewrites the table.

Every step is exercised on hotfixes, whose database holds a full-size copy of the production data.

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 on contentnode is recent 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, last taken on 2026-10-27. I confirmed the findings against the Terraform and Django source, and set the framing, scope, sequencing, and target branch.