Drop redundant indexes on contentnode and file

Aberta
  • Algo errado?
#6,350 0 comentários 0 reações 0 responsáveis Ver no GitHub

Mantenedores costumam responder em até 1 dia

Ninguém assumiu esta issue ainda.

Avaliação

Dificuldade
4/5
Tempo estimado
3-5 dias
Facilidade para iniciantes
20/100
Tipo de issue
Refatoração
Clareza
Claramente especificada
Status de atividade
Ativa
Stack de tecnologia
django, postgresql, python
Domínio
backend, databases

Direção de pesquisa

Read the model definitions around contentcuration/contentcuration/models.py:1934 and :2867, then inspect the migration history and the production index details listed in the issue. Check that #6348 has been on master for seven days with no additional scans on the checksum index before handling that pair. Done means a non-atomic migration drops the 12 listed indexes concurrently, model state matches the requested index changes, and makemigrations --check reports no changes.

Escrita pelo modelo de indexação a partir do texto da issue.

Descrição

DEV: backend

❌ 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:

Index GB Scans Last scan
contentcuration_contentnode_id_2b2d9339_like 35.6 0 —
contentcuration_contentnode_original_channel_id_041c1412_uniq 29.3 0 —
contentcuration_contentnode_original_channel_id_041c1412_like 29.4 0 —
contentcuration_contentno_original_source_node_id_da1f3b83_uniq 33.9 0 —
contentcuration_contentno_original_source_node_id_da1f3b83_like 33.8 271 2026-06-09, all from a manual debugging job
contentcuration_contentnode_content_id_2d487326_like 34.0 0 —
contentcuration_contentnode_language_id_584857c7_like 13.0 0 —
contentcuration_file_id_2b1c3d45_like 20.2 245,764,090 2026-10-27; equality lookups file_pkey serves at the same cost
contentcuration_file_checksum_b1b496dc_uniq 15.8 29,915 2026-10-27; moves to file_checksum_file_size_idx after #6348
contentcuration_file_checksum_b1b496dc_like 15.8 2 2026-08-08
contentcuration_file_contentnode_id_422a85ec_like 8.2 0 —
contentcuration_file_07d5d9a8 (file_format_id) 6.5 0 —
  • 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. learningequality/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 #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.

Linguagem predominante
Python
Estrelas
212
Forks
314
Merge médio
3d 19h
PRs com merge (30d)
41

Preparar o ambiente

Primeiros passos

  1. Leia a issue inteira e depois o guia de contribuição do projeto.
  2. Comente na issue dizendo que vai assumir — evita que duas pessoas façam o mesmo trabalho.
  3. Faça um fork do repositório e trabalhe em uma branch.
  4. Abra um pull request que referencie o número da issue.

Mais de learningequality/studio

Todas as issues de learningequality/studio

Issues semelhantes

Mais issues de Python

Receba novas issues na sua caixa de entrada

Um resumo curto de issues do GitHub para quem está começando.