Dropping a Spatial Index Safely

Problem Statement

An index has been identified as unused and the change ticket says “drop it”. The operation takes under a second and is effectively irreversible on any timescale that matters during an incident, because recreating a GiST index on a large table is hours of work. This page covers the sequence that makes the drop safe, complementing the concurrent index build that created it.

Why the Naive Approach Fails

DROP INDEX idx_parcels_centroid; does exactly what it says, and two things go wrong with it.

The lock is the first. DROP INDEX needs ACCESS EXCLUSIVE on the table. On an idle table that is instant; on a table with a long-running report in progress, the drop waits — and every query arriving behind it waits too, because the lock queue is ordered. A one-second operation becomes a four-minute outage.

The asymmetry is the second, and it is worse. If the index turns out to have been needed, the recovery is a rebuild measured in hours, during which the queries that depended on it are running sequential scans.

Seconds to remove, hours to restore Dropping a 640 megabyte GiST index takes under a second. Recreating it concurrently on the same table takes 94 minutes, during which the queries that used it fall back to sequential scans. The asymmetry is why the reversible experiment comes first. The two directions are not comparable drop < 1 second recreate 94 minutes CONCURRENTLY — sequential scans throughout Any decision whose undo costs ten thousand times more than the do deserves a rehearsal first. Hiding the index is that rehearsal: the same observable effect, and a one-second undo.

Production-Ready Implementation

Step zero, before anything else: capture the statement that would bring it back.

sql
SELECT indexdef || ';' AS recreate_statement
FROM pg_indexes
WHERE indexname = 'idx_parcels_centroid';
-- CREATE INDEX idx_parcels_centroid ON public.parcels USING gist (ST_Centroid(geom));

Paste that into the change ticket. It costs ten seconds and it is the only thing standing between a bad decision and an unstructured afternoon.

Then hide the index rather than dropping it:

sql
-- Reversible: the planner ignores the index, PostgreSQL keeps maintaining it.
BEGIN;
UPDATE pg_index SET indisvalid = false
WHERE indexrelid = 'idx_parcels_centroid'::regclass;
COMMIT;

-- to restore, at any time, instantly:
-- UPDATE pg_index SET indisvalid = true WHERE indexrelid = 'idx_parcels_centroid'::regclass;

Modifying a system catalogue is normally a bad idea, and this is the documented exception the PostgreSQL community uses for exactly this purpose. The flag is the same one a failed concurrent build sets, so the state is one the database already understands.

Observe for a full cycle — at minimum a week, and long enough to include any monthly job:

python
import psycopg

WATCHED = ("/api/parcels/search", "/api/parcels/export")


def latency_snapshot(dsn: str) -> dict[str, float]:
    """Mean execution time per tagged endpoint, for before/after comparison."""
    out = {}
    with psycopg.connect(dsn) as conn, conn.cursor() as cur:
        for endpoint in WATCHED:
            cur.execute("""
                SELECT coalesce(sum(total_exec_time) / nullif(sum(calls), 0), 0)
                FROM pg_stat_statements
                WHERE query LIKE %s
            """, (f"/* {endpoint} */%",))
            out[endpoint] = round(cur.fetchone()[0], 2)
    return out

Comparing the snapshot from before hiding with one taken a week later is the evidence the drop is safe. If nothing moved, the index was not contributing.

Finally, the drop itself:

sql
-- outside any transaction; weak lock; safe on a live table
SET lock_timeout = '5s';
DROP INDEX CONCURRENTLY IF EXISTS idx_parcels_centroid;
What each drop form does to the lock queue A plain DROP INDEX waits for an ACCESS EXCLUSIVE lock behind a running report, and every query arriving meanwhile queues behind it. DROP INDEX CONCURRENTLY takes a SHARE UPDATE EXCLUSIVE lock, which coexists with readers and writers, so nothing queues. The lock, not the work, is what makes a drop risky DROP INDEX long report holds ACCESS SHARE DROP waits for ACCESS EXCLUSIVE every arriving query queues behind the DROP DROP INDEX CONCURRENTLY long report continues undisturbed DROP proceeds nothing queues, nothing waits Both remove the same index. Only one of them can be run at 10 a.m.

Configuration and Tuning Knobs

lock_timeout applies even to the concurrent form, which still needs a brief lock at the start. Five seconds plus a retry is the standard shape.

Autocommit is required. DROP INDEX CONCURRENTLY cannot run inside a transaction block, so a migration tool that wraps statements needs the same escape as the build did.

IF EXISTS makes the operation idempotent, which matters because a partially completed concurrent drop can leave the index in an invalid state that a re-run must tolerate.

Retention of the recreate statement is a process knob rather than a database one: keep it until at least one full release and one full reporting cycle have passed without incident.

What the hide-first step costs and saves For a 640-megabyte index: hiding it costs continued write maintenance for the observation week, dropping it immediately saves that but risks a 94-minute rebuild, and the observed outcome across twelve retirements was one index restored during the trial and no incidents. Twelve index retirements, with and without the trial hidden for a week first 12 retired · 1 restored during the trial, 0 incidents dropped immediately the 1 restore would have been a 94-minute rebuild One in twelve is enough to justify the week. The cost of being wrong is asymmetric.

Verification Steps

sql
-- the index is gone, and nothing invalid was left behind
SELECT indexname FROM pg_indexes WHERE indexname = 'idx_parcels_centroid';
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

-- the space was actually reclaimed
SELECT pg_size_pretty(pg_total_relation_size('parcels')) AS table_total;

-- and the queries that might have used it did not regress
SELECT queryid, calls, round(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
WHERE query ILIKE '%parcels%'
ORDER BY total_exec_time DESC
LIMIT 10;

Compare that final result against the snapshot taken before the index was hidden. A mean time that moved by more than measurement noise on any statement means the index was contributing after all — and the recreate statement from step zero is what you reach for.

Gotchas Checklist

  • DROP INDEX CONCURRENTLY cannot be used on an index backing a constraint. PostgreSQL refuses; drop the constraint instead, which drops its index with it.
  • The hidden-index trick affects the planner immediately but not running statements. Sessions with a cached plan may continue using the index until they re-plan; wait a few minutes before drawing conclusions.
  • Do not leave an index hidden indefinitely. It costs full write maintenance and provides nothing, which is the worst of both states — decide within the observation window.
  • On a partitioned table, drop the partition indexes. Dropping the parent’s index template detaches and drops the children too, which is usually what you want but is worth being deliberate about.
  • Record the drop somewhere durable. Six months later, “why is this query slow on production but not on staging?” is answered by a schema difference nobody remembers creating.

The Paper Trail a Safe Drop Leaves Behind

An index drop that goes wrong is recoverable in principle and painful in practice, and the difference between the two is entirely determined by what was written down beforehand.

Capture four things before the drop runs. The exact definition, from pg_indexes.indexdef, so the recreate is a copy-paste rather than a reconstruction. The size, from pg_relation_size, so the space actually reclaimed can be confirmed afterwards. The scan counts and the statistics reset timestamp, so the justification for the drop is auditable months later. And the plans for the queries you believe touch the table, captured with EXPLAIN and stored in the migration’s pull request.

That last item is the one most often skipped and the one that pays. After the drop, rerunning the same EXPLAIN statements answers the only question that matters — did anything change? — in seconds, and without needing production traffic to reveal it. A plan that switched from an index scan to a bitmap heap scan on a small table is fine. A plan that switched to a sequential scan on a large one is a restore, immediately, using the definition captured in step one.

Two operational details make the drop itself uneventful. Use DROP INDEX CONCURRENTLY, which takes only a SHARE UPDATE EXCLUSIVE lock and lets reads and writes continue; the plain form takes an ACCESS EXCLUSIVE lock and will queue behind — and then block — every query on the table. And run it outside a transaction block, because the concurrent form is not permitted inside one, which is the single most common reason the statement fails in a migration framework that wraps everything in a transaction by default.

If the drop is interrupted, the index is left marked invalid rather than removed. That state is harmless for reads, but it must be cleaned up with a second drop, and it is worth checking for explicitly rather than discovering later.