Hacktoberfest 2026 : les issues que les mainteneurs ont marquées pour octobre, ouvertes et accessibles aux débutants. Parcourir les issues Hacktoberfest

Drop redundant indexes on contentnode and file

Ouverte
  • Quelque chose ne va pas ?
#6,350 0 commentaires 0 réactions 0 personnes assignées Voir sur GitHub

Les mainteneurs répondent en général sous 1 jour

Personne n'a encore pris cette issue.

Évaluation

Difficulté
4/5
Temps estimé
3-5 jours
Accessibilité débutants
20/100
Type d'issue
Refactorisation
Clarté
Clairement spécifiée
Activité
Active
Stack technique
django, postgresql, python
Domaine
backend, databases

Piste de recherche

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.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Description

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.

Langage dominant
Python
Étoiles
212
Forks
314
Merge moyen
3 j 12 h
PR mergées (30 j)
51

Préparer son environnement

Par où commencer

  1. Lisez l'issue en entier, puis le guide de contribution du projet.
  2. Signalez en commentaire que vous la prenez — cela évite que deux personnes fassent le même travail.
  3. Forkez le dépôt et travaillez sur une branche.
  4. Ouvrez une pull request qui référence le numéro de l'issue.

Autres issues de learningequality/studio

Toutes les issues de learningequality/studio

Issues similaires

Plus d'issues Python

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.