Hacktoberfest 2026: die Issues, die Maintainer für den Oktober markiert haben – offen und einsteigerfreundlich. Hacktoberfest-Issues durchsuchen

Drop redundant indexes on contentnode and file

Offen
  • Stimmt etwas nicht?
#6,350 0 Kommentare 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen

Maintainer antworten meist innerhalb von 1 Tag

Dieses Issue hat noch niemand übernommen.

Bewertung

Schwierigkeit
4/5
Geschätzter Aufwand
3-5 Tage
Anfängerfreundlichkeit
20/100
Issue-Typ
Refactoring
Klarheit
Klar beschrieben
Aktivitätsstatus
Aktiv
Tech-Stack
django, postgresql, python
Bereich
backend, databases

Rechercherichtung

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.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Beschreibung

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.

Vorherrschende Sprache
Python
Sterne
212
Forks
314
Ø Merge
3 T. 19 Std.
Gemergte PRs (30 T.)
41

Entwicklungsumgebung

Erste Schritte

  1. Lesen Sie das ganze Issue und danach den Beitragsleitfaden des Projekts.
  2. Schreiben Sie ins Issue, dass Sie es übernehmen — das erspart doppelte Arbeit.
  3. Forken Sie das Repository und arbeiten Sie in einem Branch.
  4. Öffnen Sie einen Pull Request, der die Issue-Nummer nennt.

Mehr aus learningequality/studio

Alle Issues in learningequality/studio

Ähnliche Issues

Weitere Issues zu Python

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.