Drop redundant indexes on contentnode and file #6350

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

Overview

contentcuration_contentnode and contentcuration_file carry 12 indexes, about 275 GB, that production doesn't need: unscanned since stats were reset on 2026-06-09, or duplicating another index that serves the same lookups at the same cost. Eight are the varchar_pattern_ops _like twins Django adds to every indexed CharField, including Studio's char primary keys and foreign keys to them. Every insert, and every update that can't be done as a HOT update, writes to all of them. These indexes should be dropped.

Complexity: Medium
Target branch: hotfixes

Context

Production master, stats since 2026-06-09:

• The plain indexes on content_id, language_id and file.contentnode_id are in use and stay.
• varchar_pattern_ops supports = as well as LIKE. On production, EXPLAIN gives contentcuration_file_id_2b1c3d45_like and file_pkey the same cost (0.69..8.71) for id = '…', and the planner breaks the tie towards _like; id = ANY(…) already uses file_pkey. No code does a pattern lookup (__startswith, __contains, __regex) on an id.
• Declaring an index in Meta.indexes instead of db_index=True creates no _like twin: node_id already does this (models.py:1934, :2867), and node_id_idx has no twin on production. Remove varchar_pattern_ops index for modality kolibri#14076 did the same for modality.

The Change

• A non-atomic migration should drop the 12 indexes with DROP INDEX CONCURRENTLY, so reads and writes on both tables aren't blocked.
• The model state should match the database: original_channel_id, original_source_node_id, File.checksum and File.file_format should have no index, while content_id, ContentNode.language and File.contentnode should keep their plain index, declared in Meta.indexes.
• The primary keys' _like twins have no representation in Django's model state, so their drops should be database-only operations.

Out of Scope

• The file.slideshow_slide index, pending a look at which slides exist in production.

Acceptance Criteria

General

• The file.checksum pair ships only after Make file_checksum_file_size_idx unconditional again #6348 has been on master for 7 days, with no increase in idx_scan on contentcuration_file_checksum_b1b496dc_uniq over those 7 days
• After deploy to hotfixes, none of the 12 indexes in Context exist
• The plain indexes on content_id, language_id and file.contentnode_id keep their OIDs, so they weren't rebuilt
• makemigrations --check reports no changes
• In the 7 days after deploy to master, the slow query log (log_min_duration_statement = 500ms) shows no new entries filtering on a column whose index was dropped

AI usage

Drafted with Claude Code, which measured index usage on production through read-only queries in the disposable debugging job and traced which code paths query each column. I chose which indexes to drop or keep, the gate on the checksum pair, and the scope.