Hacktoberfest 2026：メンテナが10月に向けて印を付けた、オープンで初心者向けの issue。 Hacktoberfest の issue を見る

Drop redundant indexes on contentnode and file

オープン
  • 何か問題がありますか？
#6,350 コメント 0 件 リアクション 0 件 担当者 0 名 GitHub で見る

メンテナーはふだん 1 日以内に返信

まだ誰も着手していません。

評価

難易度
4/5
見積もり時間
3〜5日
初心者へのやさしさ
20/100
issue の種類
リファクタリング
明瞭さ
明確に書かれている
活発さ
活発
技術スタック
django, postgresql, python
領域
backend, databases

調査の方向性

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.

索引モデルが issue の本文から書いたものです。

説明

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.

主要言語
Python
スター
212
フォーク
314
平均マージ
3日 6時間
マージ済み PR（30日）
56

環境構築

はじめの一歩

  1. issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
  2. 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
  3. リポジトリをフォークし、ブランチを切って変更します。
  4. issue 番号を参照したプルリクエストを送ります。

learningequality/studio のほかの issue

learningequality/studio の issue をすべて見る

似ている issue

Python の issue をもっと見る

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。