Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

Drop redundant indexes on contentnode and file

Aperta
  • Qualcosa non va?
#6,350 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
20/100
Tipo di issue
Refactoring
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
django, postgresql, python
Ambito
backend, databases

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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.

Lingua principale
Python
Stelle
212
Fork
314
Merge medio
3g 12h
PR unite (30g)
51

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di learningequality/studio

Tutte le issue di learningequality/studio

Issue simili

Altre issue su Python

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.