Diagnostic Tax, Pricing & Migration
Failed concurrent index migration leaves invalid index
A deploy timed out, a pod got killed, or a lock wait ran long, right in the middle of a migration that was supposed to be safe. Saleor builds its index migrations with Django's concurrent operations precisely so a big table like product_product never locks during an upgrade. But when that concurrent build gets interrupted, Postgres cannot roll it back the way it would a normal index. It leaves a broken index sitting in the catalog, and the very next deploy fails trying to create the same index again. Here is why that happens and a small script that finds the broken index and plans the fix.
Saleor is a Django application, and its zero-downtime migration guidance directs contributors to build index migrations with AddIndexConcurrently or RemoveIndexConcurrently under atomic = False, which compiles to Postgres CREATE INDEX CONCURRENTLY. That command builds an index in several non-transactional passes so large tables are not locked, but if the build is interrupted, Postgres cannot roll the DDL back atomically. It leaves an index catalogued with pg_index.indisvalid = false, which still takes up disk space, gets silently skipped by the query planner, and blocks the next deploy's migration because Postgres refuses to create another index with the same name while the invalid one exists. Run a small Python or Node.js script that queries pg_index directly for invalid indexes, cross references the migration that created them, and plans a dry run guarded DROP INDEX CONCURRENTLY followed by a migration replay. Full code, tests, and a dry run guard are below.
The problem in plain words
Saleor's own zero-downtime migration guidance is clear about why concurrent index operations exist at all: a plain CREATE INDEX takes a lock that blocks writes to the table for as long as the build takes, and on a table the size of product_product or order_order that can be minutes of blocked writes during an upgrade. So Django's AddIndexConcurrently and RemoveIndexConcurrently operations, run under atomic = False, compile to Postgres CREATE INDEX CONCURRENTLY instead, which builds the index without holding that lock.
The tradeoff is that a concurrent build is not one transaction. Postgres does it in multiple passes, scanning the table more than once and waiting out any transactions that were already in flight when it started. If something interrupts that process, a deploy that times out mid-migration, a pod that gets killed, a lock wait timeout, a dropped database connection, or a uniqueness violation discovered on one of the later passes, Postgres has no clean way to undo it. A normal transaction would just roll back. This one cannot, so it leaves a half built index sitting in the catalog, marked invalid.
Why it happens
- Saleor's zero-downtime migration guidance tells contributors to build index migrations with
AddIndexConcurrentlyorRemoveIndexConcurrentlyunderatomic = False, which is exactly what Django ticket #21039 added support for so PostgresCREATE INDEX CONCURRENTLYcould be used from a migration at all. CREATE INDEX CONCURRENTLYbuilds in multiple passes and is not wrapped in a single transaction, which is the entire point, it lets large tables likeproduct_productandorder_orderstay writable during the build instead of locking for the full duration.- An interruption mid-build, a deploy timeout, a pod killed by the orchestrator, a lock wait timeout, a dropped database connection, or a uniqueness violation found on a later pass, means Postgres cannot roll the DDL back atomically the way it would for a plain
CREATE INDEX. - What is left behind is a partially built index catalogued with
pg_index.indisvalid = false, and sometimesindisready = falseif the build never even finished. It still occupies disk space and the planner silently ignores it, but Postgres will not let a second index of the same name be created while it exists, so the upgrade pipeline keeps failing on the same migration until someone intervenes.
This is a database catalog problem, so nothing about it shows up in Saleor's GraphQL API. The first sign is usually the next deployment's migration step erroring with something like relation already exists, and the fix is not obvious unless you already know to look at pg_index directly.
An invalid index is not a lock problem, it is a leftover catalog entry that Postgres refuses to reuse or silently replace. Dropping and rebuilding it is itself a DDL action, so it deserves the same care as the original migration. Never reach for a plain REINDEX as the safe fix, since pre Postgres 12 REINDEX, and even REINDEX CONCURRENTLY under contention, can re-lock the table, which throws away the entire reason the index was made concurrent in the first place.
The fix, as a flow
The script queries Postgres directly for every index where indisvalid is false, decides what to do with each one through a pure function, and either logs the plan under a dry run or executes a DROP INDEX CONCURRENTLY in an autocommit session followed by replaying the Django migration that originally created it, so AddIndexConcurrently rebuilds it cleanly.
Build it step by step
Get a Postgres connection, not a Saleor API token
An invalid index is a row in pg_index, so this is a database catalog check, not a GraphQL call. Use the same DATABASE_URL the Saleor API and worker containers already point at, with a role that can read pg_catalog and, when you actually repair, can run DDL.
pip install psycopg[binary]
export DATABASE_URL="postgres://user:pass@host:5432/saleor"
export DRY_RUN="true" # start safe, change to false to actually dropnpm install pg
export DATABASE_URL="postgres://user:pass@host:5432/saleor"
export DRY_RUN="true" // start safe, change to false to actually dropQuery pg_index for anything invalid
Join pg_index to pg_class and pg_namespace to read back the schema, index name, table name, and both indisvalid and indisready, filtered to rows where indisvalid is false. This is the exact detection query, run straight against the Saleor database.
INVALID_INDEX_QUERY = """
SELECT n.nspname AS schema_name,
c.relname AS index_name,
t.relname AS table_name,
i.indisvalid,
i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE i.indisvalid = false;
"""
def fetch_invalid_indexes(conn):
with conn.cursor() as cur:
cur.execute(INVALID_INDEX_QUERY)
cols = [d.name for d in cur.description]
return [dict(zip(cols, row)) for row in cur.fetchall()]const INVALID_INDEX_QUERY = `
SELECT n.nspname AS schema_name,
c.relname AS index_name,
t.relname AS table_name,
i.indisvalid,
i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE i.indisvalid = false;
`;
async function fetchInvalidIndexes(client) {
const { rows } = await client.query(INVALID_INDEX_QUERY);
return rows;
}Cross reference the owning migration
Grep each Django app's migrations/ folder for AddIndexConcurrently and index=Index(... name=...) so you know which app_label and migration file created the invalid index. This confirms it was Saleor's own concurrent migration, not something created by hand, and gives you the exact migration to replay later.
import re
import pathlib
INDEX_NAME_RE = re.compile(r"""name=["']([\w-]+)["']""")
def find_owning_migration(index_name, migrations_root):
for path in pathlib.Path(migrations_root).rglob("migrations/*.py"):
text = path.read_text(errors="ignore")
if "AddIndexConcurrently" not in text and "index=Index(" not in text:
continue
if index_name in INDEX_NAME_RE.findall(text):
app_label = path.parents[1].name
migration_name = path.stem
return {"app_label": app_label, "migration_name": migration_name}
return Noneimport fs from "node:fs";
import path from "node:path";
const INDEX_NAME_RE = /name=["']([\w-]+)["']/g;
export function findOwningMigration(indexName, migrationsRoot) {
const files = walk(migrationsRoot).filter(
(p) => p.includes(`${path.sep}migrations${path.sep}`) && p.endsWith(".py")
);
for (const file of files) {
const text = fs.readFileSync(file, "utf8");
if (!text.includes("AddIndexConcurrently") && !text.includes("index=Index(")) continue;
const names = [...text.matchAll(INDEX_NAME_RE)].map((m) => m[1]);
if (names.includes(indexName)) {
const parts = file.split(path.sep);
const appLabel = parts[parts.length - 3];
const migrationName = path.basename(file, ".py");
return { appLabel, migrationName };
}
}
return null;
}
function walk(dir) {
const out = [];
for (const entry of fs.readdirSync(dir, { withFileTypes: true })) {
const full = path.join(dir, entry.name);
if (entry.isDirectory()) out.push(...walk(full));
else out.push(full);
}
return out;
}Decide, with one pure function
Keep the decision in its own function that takes the rows from the detection query and the dry run flag, and returns a plain action record for each invalid index. Deduplicate by schema and index name, and never let anything but log_only come out when dry run is true, so the repair path is impossible to trigger by accident.
def plan_invalid_index_repair(rows, dry_run):
seen = set()
plan = []
for row in rows:
if row.get("indisvalid") is not False:
continue
key = (row["schema_name"], row["index_name"])
if key in seen:
continue
seen.add(key)
schema_name, index_name, table_name = row["schema_name"], row["index_name"], row["table_name"]
plan.append({
"index_name": index_name,
"table_name": table_name,
"action": "log_only" if dry_run else "drop_concurrently",
"sql": f'DROP INDEX CONCURRENTLY IF EXISTS "{schema_name}"."{index_name}";',
"requires_migration_replay": True,
"indisready": row.get("indisready"),
})
return planexport function planInvalidIndexRepair(rows, dryRun) {
const seen = new Set();
const plan = [];
for (const row of rows) {
if (row.indisvalid !== false) continue;
const key = `${row.schema_name}.${row.index_name}`;
if (seen.has(key)) continue;
seen.add(key);
const { schema_name: schemaName, index_name: indexName, table_name: tableName } = row;
plan.push({
index_name: indexName,
table_name: tableName,
action: dryRun ? "log_only" : "drop_concurrently",
sql: `DROP INDEX CONCURRENTLY IF EXISTS "${schemaName}"."${indexName}";`,
requires_migration_replay: true,
indisready: row.indisready,
});
}
return plan;
}Execute the drop outside a transaction, then replay the migration
When an action is drop_concurrently, run the DROP INDEX CONCURRENTLY statement on an autocommit connection, since Postgres refuses to run CONCURRENTLY operations inside a transaction block. Then re-run the originating migration with manage.py migrate app_label migration_name so Django's AddIndexConcurrently recreates the index cleanly.
import subprocess
def drop_invalid_index(conn, sql):
conn.autocommit = True
with conn.cursor() as cur:
cur.execute(sql)
def replay_migration(app_label, migration_name):
subprocess.run(
["python", "manage.py", "migrate", app_label, migration_name],
check=True,
)import { execFile } from "node:child_process";
import { promisify } from "node:util";
const execFileAsync = promisify(execFile);
async function dropInvalidIndex(client, sql) {
await client.query(sql); // client obtained with autocommit, one statement per connection
}
async function replayMigration(appLabel, migrationName) {
await execFileAsync("python", ["manage.py", "migrate", appLabel, migrationName]);
}Wire it together with a dry run guard
The loop fetches invalid indexes, plans the repair, and for every entry either logs the SQL and the migration it would replay, or actually drops the index and re-runs the migration when DRY_RUN=false. Run it after any deploy that touched an index migration, since that is exactly when a partially built index is most likely to appear.
Always start with DRY_RUN=true and read the planned SQL before flipping it off. Never substitute a plain REINDEX for the drop and rebuild, since it can re-lock the table and undo the entire reason the index was built concurrently in the first place.
The full code
Here is the complete script in one file for each language. It reads the database connection from the environment, queries pg_index for invalid rows, plans the repair with a pure function, and respects the dry run flag before touching anything.
"""Find Postgres indexes left invalid by an interrupted Saleor migration
and plan a safe drop and rebuild.
Saleor's zero-downtime migration guidance builds index migrations with
Django's AddIndexConcurrently under atomic = False, which compiles to
Postgres CREATE INDEX CONCURRENTLY. That build runs in multiple
non-transactional passes, and if it is interrupted (deploy timeout, killed
pod, lock wait timeout, dropped connection, or a uniqueness violation on a
later pass), Postgres cannot roll it back atomically. It leaves a
partially built index catalogued with pg_index.indisvalid = false, which
also blocks the next deploy's migration from creating the same index name
again.
Under DRY_RUN=true (the default) this only logs the SQL it would run and
the migration it would replay. When DRY_RUN=false it drops the invalid
index with DROP INDEX CONCURRENTLY on an autocommit connection and re-runs
the originating migration. Run after any deploy that touched an index
migration. Safe to run again and again.
"""
import os
import logging
import subprocess
logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("repair_invalid_index")
DATABASE_URL = os.environ["DATABASE_URL"]
MIGRATIONS_ROOT = os.environ.get("MIGRATIONS_ROOT", ".")
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"
INVALID_INDEX_QUERY = """
SELECT n.nspname AS schema_name,
c.relname AS index_name,
t.relname AS table_name,
i.indisvalid,
i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE i.indisvalid = false;
"""
def plan_invalid_index_repair(rows, dry_run):
"""Pure decision logic. No I/O.
rows: result set from the pg_index query, each a dict with
schema_name, index_name, table_name, indisvalid, indisready.
Returns one action record per distinct invalid (schema, index) pair.
"""
seen = set()
plan = []
for row in rows:
if row.get("indisvalid") is not False:
continue
key = (row["schema_name"], row["index_name"])
if key in seen:
continue
seen.add(key)
schema_name, index_name, table_name = row["schema_name"], row["index_name"], row["table_name"]
plan.append({
"index_name": index_name,
"table_name": table_name,
"action": "log_only" if dry_run else "drop_concurrently",
"sql": f'DROP INDEX CONCURRENTLY IF EXISTS "{schema_name}"."{index_name}";',
"requires_migration_replay": True,
"indisready": row.get("indisready"),
})
return plan
def fetch_invalid_indexes(conn):
with conn.cursor() as cur:
cur.execute(INVALID_INDEX_QUERY)
cols = [d.name for d in cur.description]
return [dict(zip(cols, row)) for row in cur.fetchall()]
def find_owning_migration(index_name, migrations_root):
import re
import pathlib
index_name_re = re.compile(r"""name=["']([\w-]+)["']""")
for path in pathlib.Path(migrations_root).rglob("migrations/*.py"):
text = path.read_text(errors="ignore")
if "AddIndexConcurrently" not in text and "index=Index(" not in text:
continue
if index_name in index_name_re.findall(text):
return {"app_label": path.parents[1].name, "migration_name": path.stem}
return None
def drop_invalid_index(conn, sql):
conn.autocommit = True
with conn.cursor() as cur:
cur.execute(sql)
def replay_migration(app_label, migration_name):
subprocess.run(["python", "manage.py", "migrate", app_label, migration_name], check=True)
def run():
import psycopg
conn = psycopg.connect(DATABASE_URL)
try:
rows = fetch_invalid_indexes(conn)
plan = plan_invalid_index_repair(rows, DRY_RUN)
for item in plan:
owner = find_owning_migration(item["index_name"], MIGRATIONS_ROOT)
owner_desc = f'{owner["app_label"]}.{owner["migration_name"]}' if owner else "unknown migration"
if item["action"] == "log_only":
log.info("[DRY RUN] %s -- would rebuild via migration %s", item["sql"], owner_desc)
continue
log.warning("Dropping invalid index %s on %s", item["index_name"], item["table_name"])
drop_invalid_index(conn, item["sql"])
if owner:
log.info("Replaying migration %s to rebuild %s", owner_desc, item["index_name"])
replay_migration(owner["app_label"], owner["migration_name"])
else:
log.error("No owning migration found for %s, rebuild it manually", item["index_name"])
log.info(
"Done. %d invalid index(es) %s.",
len(plan), "found (dry run)" if DRY_RUN else "repaired",
)
finally:
conn.close()
if __name__ == "__main__":
run()/**
* Find Postgres indexes left invalid by an interrupted Saleor migration
* and plan a safe drop and rebuild.
*
* Saleor's zero-downtime migration guidance builds index migrations with
* Django's AddIndexConcurrently under atomic = False, which compiles to
* Postgres CREATE INDEX CONCURRENTLY. That build runs in multiple
* non-transactional passes, and if it is interrupted (deploy timeout,
* killed pod, lock wait timeout, dropped connection, or a uniqueness
* violation on a later pass), Postgres cannot roll it back atomically. It
* leaves a partially built index catalogued with indisvalid = false,
* which also blocks the next deploy's migration from creating the same
* index name again.
*
* Under DRY_RUN=true (the default) this only logs the SQL it would run
* and the migration it would replay. When DRY_RUN=false it drops the
* invalid index with DROP INDEX CONCURRENTLY and re-runs the originating
* migration. Run after any deploy that touched an index migration.
*
* Guide: https://www.allanninal.dev/saleor/invalid-index-after-failed-migration/
*/
import { pathToFileURL } from "node:url";
import fs from "node:fs";
import path from "node:path";
import { execFile } from "node:child_process";
import { promisify } from "node:util";
const execFileAsync = promisify(execFile);
const DATABASE_URL = process.env.DATABASE_URL || "postgres://user:pass@localhost:5432/saleor";
const MIGRATIONS_ROOT = process.env.MIGRATIONS_ROOT || ".";
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";
const INVALID_INDEX_QUERY = `
SELECT n.nspname AS schema_name,
c.relname AS index_name,
t.relname AS table_name,
i.indisvalid,
i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE i.indisvalid = false;
`;
/**
* Pure decision logic. No I/O.
*
* rows: result set from the pg_index query, each an object with
* schema_name, index_name, table_name, indisvalid, indisready.
* Returns one action record per distinct invalid (schema, index) pair.
*/
export function planInvalidIndexRepair(rows, dryRun) {
const seen = new Set();
const plan = [];
for (const row of rows) {
if (row.indisvalid !== false) continue;
const key = `${row.schema_name}.${row.index_name}`;
if (seen.has(key)) continue;
seen.add(key);
const { schema_name: schemaName, index_name: indexName, table_name: tableName } = row;
plan.push({
index_name: indexName,
table_name: tableName,
action: dryRun ? "log_only" : "drop_concurrently",
sql: `DROP INDEX CONCURRENTLY IF EXISTS "${schemaName}"."${indexName}";`,
requires_migration_replay: true,
indisready: row.indisready,
});
}
return plan;
}
async function fetchInvalidIndexes(client) {
const { rows } = await client.query(INVALID_INDEX_QUERY);
return rows;
}
const INDEX_NAME_RE = /name=["']([\w-]+)["']/g;
function walk(dir) {
const out = [];
for (const entry of fs.readdirSync(dir, { withFileTypes: true })) {
const full = path.join(dir, entry.name);
if (entry.isDirectory()) out.push(...walk(full));
else out.push(full);
}
return out;
}
export function findOwningMigration(indexName, migrationsRoot) {
const files = walk(migrationsRoot).filter(
(p) => p.includes(`${path.sep}migrations${path.sep}`) && p.endsWith(".py")
);
for (const file of files) {
const text = fs.readFileSync(file, "utf8");
if (!text.includes("AddIndexConcurrently") && !text.includes("index=Index(")) continue;
const names = [...text.matchAll(INDEX_NAME_RE)].map((m) => m[1]);
if (names.includes(indexName)) {
const parts = file.split(path.sep);
const appLabel = parts[parts.length - 3];
const migrationName = path.basename(file, ".py");
return { appLabel, migrationName };
}
}
return null;
}
async function dropInvalidIndex(client, sql) {
await client.query(sql);
}
async function replayMigration(appLabel, migrationName) {
await execFileAsync("python", ["manage.py", "migrate", appLabel, migrationName]);
}
export async function run() {
const { Client } = await import("pg");
const client = new Client({ connectionString: DATABASE_URL });
await client.connect();
try {
const rows = await fetchInvalidIndexes(client);
const plan = planInvalidIndexRepair(rows, DRY_RUN);
for (const item of plan) {
const owner = findOwningMigration(item.index_name, MIGRATIONS_ROOT);
const ownerDesc = owner ? `${owner.appLabel}.${owner.migrationName}` : "unknown migration";
if (item.action === "log_only") {
console.log(`[DRY RUN] ${item.sql} -- would rebuild via migration ${ownerDesc}`);
continue;
}
console.warn(`Dropping invalid index ${item.index_name} on ${item.table_name}`);
await dropInvalidIndex(client, item.sql);
if (owner) {
console.log(`Replaying migration ${ownerDesc} to rebuild ${item.index_name}`);
await replayMigration(owner.appLabel, owner.migrationName);
} else {
console.error(`No owning migration found for ${item.index_name}, rebuild it manually`);
}
}
console.log(`Done. ${plan.length} invalid index(es) ${DRY_RUN ? "found (dry run)" : "repaired"}.`);
} finally {
await client.end();
}
}
if (import.meta.url === pathToFileURL(process.argv[1]).href) {
run().catch((err) => { console.error(err); process.exit(1); });
}Add a test
The planning rule is the part most worth testing, because it decides which index gets dropped and rebuilt and which is left alone. Because plan_invalid_index_repair is pure, no database connection, no Django, the test just feeds in plain rows and checks the plan.
from repair_invalid_index import plan_invalid_index_repair
def row(**over):
base = {
"schema_name": "public",
"index_name": "product_product_name_idx",
"table_name": "product_product",
"indisvalid": False,
"indisready": True,
}
base.update(over)
return base
def test_empty_input_returns_empty_plan():
assert plan_invalid_index_repair([], dry_run=True) == []
def test_valid_index_is_excluded():
plan = plan_invalid_index_repair([row(indisvalid=True)], dry_run=True)
assert plan == []
def test_invalid_and_not_ready_still_plans_drop_concurrently():
plan = plan_invalid_index_repair([row(indisready=False)], dry_run=False)
assert plan[0]["action"] == "drop_concurrently"
assert plan[0]["indisready"] is False
def test_invalid_and_ready_still_plans_drop_concurrently():
plan = plan_invalid_index_repair([row(indisready=True)], dry_run=False)
assert plan[0]["action"] == "drop_concurrently"
assert plan[0]["indisready"] is True
def test_dry_run_true_never_emits_anything_but_log_only():
plan = plan_invalid_index_repair([row(indisready=False), row(indisready=True)], dry_run=True)
assert all(item["action"] == "log_only" for item in plan)
def test_deduplicates_by_schema_and_index_name():
rows = [row(), row()]
plan = plan_invalid_index_repair(rows, dry_run=True)
assert len(plan) == 1
def test_sql_uses_drop_index_concurrently_if_exists():
plan = plan_invalid_index_repair([row()], dry_run=True)
assert plan[0]["sql"] == 'DROP INDEX CONCURRENTLY IF EXISTS "public"."product_product_name_idx";'
def test_requires_migration_replay_is_always_true():
plan = plan_invalid_index_repair([row()], dry_run=True)
assert plan[0]["requires_migration_replay"] is Trueimport { test } from "node:test";
import assert from "node:assert/strict";
import { planInvalidIndexRepair } from "./repair-invalid-index.js";
const row = (over = {}) => ({
schema_name: "public",
index_name: "product_product_name_idx",
table_name: "product_product",
indisvalid: false,
indisready: true,
...over,
});
test("empty input returns empty plan", () => {
assert.deepEqual(planInvalidIndexRepair([], true), []);
});
test("valid index is excluded", () => {
const plan = planInvalidIndexRepair([row({ indisvalid: true })], true);
assert.deepEqual(plan, []);
});
test("invalid and not ready still plans drop_concurrently", () => {
const plan = planInvalidIndexRepair([row({ indisready: false })], false);
assert.equal(plan[0].action, "drop_concurrently");
assert.equal(plan[0].indisready, false);
});
test("invalid and ready still plans drop_concurrently", () => {
const plan = planInvalidIndexRepair([row({ indisready: true })], false);
assert.equal(plan[0].action, "drop_concurrently");
assert.equal(plan[0].indisready, true);
});
test("dry run true never emits anything but log_only", () => {
const plan = planInvalidIndexRepair([row({ indisready: false }), row({ indisready: true })], true);
assert.ok(plan.every((item) => item.action === "log_only"));
});
test("deduplicates by schema and index name", () => {
const plan = planInvalidIndexRepair([row(), row()], true);
assert.equal(plan.length, 1);
});
test("sql uses drop index concurrently if exists", () => {
const plan = planInvalidIndexRepair([row()], true);
assert.equal(plan[0].sql, 'DROP INDEX CONCURRENTLY IF EXISTS "public"."product_product_name_idx";');
});
test("requires_migration_replay is always true", () => {
const plan = planInvalidIndexRepair([row()], true);
assert.equal(plan[0].requires_migration_replay, true);
});Case studies
A 15 minute deploy budget cut off an index build on order_order
A team upgraded Saleor and picked up a new index migration on order_order alongside a dozen other apps. The table had grown large enough that the concurrent build took longer than the CI pipeline's 15 minute deploy timeout, and the pipeline killed the job mid build. The very next deploy attempt failed immediately with relation already exists on the same index name, and nobody on the team knew Postgres could leave a half finished index behind.
Running the detection query against pg_index found the exact index with indisvalid = false, a grep of the migrations folder matched it to the app and migration number, and the dry run output showed precisely which DROP INDEX CONCURRENTLY statement to run. Dropping it and replaying the migration in a maintenance window with a longer timeout fixed the pipeline for good.
A Kubernetes eviction interrupted a build on product_product
During a rolling upgrade, the pod running the migration job got evicted for a memory limit right as a concurrent index build on product_product was partway through its second pass. Postgres left the index invalid, and it sat there quietly using disk space while the query planner ignored it and nobody noticed anything was visibly broken, until the next release tried to add a different index and Postgres complained about a name collision from an unrelated earlier migration.
The team added the detection script as a post deploy check. It flagged the invalid index within the first run after the eviction, and because it was caught before the next migration was even attempted, the drop and replay happened in minutes instead of becoming a mystery during the following release.
After this runs as a post deploy check, an interrupted concurrent index build gets caught the same day it happens instead of surfacing as a cryptic relation already exists error on some unrelated future migration. The invalid index is dropped and rebuilt through the same Django migration that created it, so the fix stays consistent with how Saleor expects its schema to be managed, and the next deploy proceeds cleanly.
FAQ
Why does a Saleor migration leave an invalid Postgres index?
Saleor's zero-downtime migration guidance uses Django's AddIndexConcurrently under atomic = False, which compiles to Postgres CREATE INDEX CONCURRENTLY so large tables like product_product or order_order are not locked during the build. That build runs in multiple non-transactional passes, and if it is interrupted, for example a deploy timeout, a killed pod, a lock wait timeout, or a uniqueness violation found late in the build, Postgres cannot roll it back atomically the way a plain CREATE INDEX would. It leaves a partially built index catalogued with pg_index.indisvalid set to false.
How do I find invalid indexes left behind by a failed Saleor migration?
This is a database catalog problem, not something exposed over Saleor's GraphQL endpoint, so you check it with a direct query against pg_index, pg_class, and pg_namespace on the same Postgres database Saleor's DATABASE_URL points at, filtering to indisvalid = false. Cross reference the index name against each Django app's migrations folder, grepping for AddIndexConcurrently, to confirm it belongs to a Saleor authored concurrent migration and record which app_label and migration created it.
Is REINDEX a safe fix for an invalid index?
No, plain REINDEX and even REINDEX CONCURRENTLY under contention can re-lock the table, which defeats the entire reason the index was built concurrently in the first place. The safer path is to drop the invalid index with DROP INDEX CONCURRENTLY IF EXISTS in an autocommit session and then re-run the originating Django migration so AddIndexConcurrently recreates it cleanly, all guarded behind a dry run flag since this is a corrective write.
Related field notes
Citations
On the problem:
- PostgreSQL Documentation: CREATE INDEX, CONCURRENTLY caveats and invalid index on failure. postgresql.org/docs/current/sql-createindex.html
- Django ticket #21039: support Postgres CREATE INDEX CONCURRENTLY in migrations. code.djangoproject.com/ticket/21039
- saleor/saleor GitHub repository, the Django based commerce platform. github.com/saleor/saleor
On the solution:
- Zero Downtime Migrations, Saleor Commerce Documentation. docs.saleor.io/developer/community/zero-downtime-migrations
- Django documentation: database migration operations, AddIndexConcurrently and RemoveIndexConcurrently. docs.djangoproject.com/en/stable/ref/contrib/postgres/operations
- Django documentation: writing non-atomic migrations. docs.djangoproject.com/en/stable/howto/writing-migrations
Stuck on a tricky one?
If you have a problem in Saleor checkout, stock, channels, or fulfillment that you would rather hand off, this is the kind of work I do. Message me and we can work through it together.
Did this unblock a stuck deploy?
If this saved you from a mystery relation already exists error, you can buy me a coffee. It is the best way to keep these field notes free and growing.
Buy me a coffee on Ko-fi