Measuring GiST Index Bloat With pgstattuple

This page zooms in on one concrete task from the broader Detecting GiST Index Bloat workflow: writing the exact SQL that quantifies bloat on a PostGIS GiST index, and wrapping it in a Python routine that flags any index above a leaf_fragmentation threshold and prints a ready-to-review REINDEX plan. The running example is a fleet telemetry database whose vehicle_tracks table carries a geometry(Point,4326) column named position, updated on every device ping, with a spatial index idx_vehicle_tracks_position_gist that has been quietly inflating for weeks. The goal is a repeatable, low-risk measurement you can schedule, not a one-off manual poke.

Why the Naive Approach Fails

The tempting shortcut is to eyeball index size growth and rebuild whenever it “looks big”. That fails in two directions. First, raw size tells you nothing about packing — a large index may be perfectly dense because the table genuinely holds many rows, and rebuilding it wastes a maintenance window for no gain. Second, size alone hides fragmentation entirely: an index can be a modest size yet be riddled with half-empty, out-of-order leaf pages that inflate every scan.

The other common misstep is reaching straight for pgstattuple on every index:

sql
-- Slow and indiscriminate: full-scans every GiST index, business hours or not
SELECT * FROM pgstattuple('idx_vehicle_tracks_position_gist');
SELECT * FROM pgstattuple('idx_trip_segments_route_gist');

pgstattuple() reads the whole object. On a 30 GB position index that is a multi-minute full scan that evicts hot pages from shared buffers and competes with live telemetry writes. Running it blindly across a schema turns a diagnostic into an incident. The disciplined approach is tiered: probe cheaply first, then measure expensively only where the cheap probe raised a flag.

Production-Ready Implementation

The complete implementation is one SQL pair plus one Python module. The SQL side gives you the metrics; the Python side applies thresholds and emits the plan.

The SQL: cheap probe, then authoritative scan

pgstatindex() is the cheap probe. It returns structural metadata without a full scan, so it is safe to run across every GiST index:

sql
-- Cheap structural probe: run on all GiST indexes
SELECT
    'idx_vehicle_tracks_position_gist'::text        AS index_name,
    pg_relation_size('idx_vehicle_tracks_position_gist') AS index_bytes,
    ps.leaf_pages,
    ps.empty_pages,
    ps.deleted_pages,
    ps.avg_leaf_density,
    ps.leaf_fragmentation
FROM pgstatindex('idx_vehicle_tracks_position_gist') ps;

pgstattuple() is the authoritative scan, reserved for flagged indexes. It reports the share of the object that is genuinely free space and would be reclaimed by a rebuild:

sql
-- Authoritative full scan: run only on flagged indexes
SELECT
    table_len            AS index_bytes,
    tuple_count          AS live_entries,
    dead_tuple_count,
    dead_tuple_percent,
    free_space,
    free_percent
FROM pgstattuple('idx_vehicle_tracks_position_gist');

To discover every GiST index on the telemetry tables rather than hard-coding names, enumerate them from the catalog. This is the query the Python flagger iterates over:

sql
SELECT
    i.indexrelid::regclass::text AS index_name,
    t.relname                    AS table_name,
    pg_relation_size(i.indexrelid) AS index_bytes
FROM pg_index i
JOIN pg_class t  ON t.oid  = i.indrelid
JOIN pg_class ic ON ic.oid = i.indexrelid
JOIN pg_am   am  ON am.oid = ic.relam
WHERE am.amname = 'gist'
  AND t.relname IN ('vehicle_tracks', 'trip_segments')
ORDER BY pg_relation_size(i.indexrelid) DESC;

The Python flagger

The module below connects with psycopg (v3), enumerates GiST indexes, runs the cheap probe on each, escalates to the full scan only for indexes that breach the fragmentation or density gates, and prints a REINDEX plan. It never executes DDL itself — emitting a plan for human review is deliberate, since a rebuild consumes disk and I/O:

python
from __future__ import annotations

import sys
from dataclasses import dataclass

import psycopg
from psycopg.rows import dict_row

# --- Thresholds. Flag an index only when it is BOTH fragmented AND sparse,
#     and above a minimum size so tiny indexes are never rebuilt. ---
LEAF_FRAGMENTATION_MAX = 30.0    # percent; above this the tree is disordered
AVG_LEAF_DENSITY_MIN = 55.0      # percent; below this leaves are half empty
MIN_INDEX_BYTES = 256 * 1024 * 1024  # ignore indexes smaller than 256 MB

ENUMERATE_GIST = """
    SELECT
        i.indexrelid::regclass::text AS index_name,
        t.relname                    AS table_name,
        pg_relation_size(i.indexrelid) AS index_bytes
    FROM pg_index i
    JOIN pg_class t  ON t.oid  = i.indrelid
    JOIN pg_class ic ON ic.oid = i.indexrelid
    JOIN pg_am   am  ON am.oid = ic.relam
    WHERE am.amname = 'gist'
      AND t.relname = ANY(%(tables)s)
    ORDER BY pg_relation_size(i.indexrelid) DESC;
"""

# %s for the index name is safe here because the value comes from the catalog
# enumeration above, not from user input. pgstatindex takes a regclass.
PROBE_INDEX = """
    SELECT avg_leaf_density, leaf_fragmentation
    FROM pgstatindex(%s);
"""

FULL_SCAN = """
    SELECT dead_tuple_percent, free_percent
    FROM pgstattuple(%s);
"""


@dataclass
class Candidate:
    index_name: str
    table_name: str
    index_bytes: int
    avg_leaf_density: float
    leaf_fragmentation: float
    free_percent: float

    @property
    def approx_reclaimable_bytes(self) -> int:
        return int(self.index_bytes * self.free_percent / 100.0)


def find_bloated_indexes(dsn: str, tables: list[str]) -> list[Candidate]:
    candidates: list[Candidate] = []
    with psycopg.connect(dsn, row_factory=dict_row) as conn:
        # Keep the audit read-only and non-blocking.
        conn.read_only = True
        with conn.cursor() as cur:
            cur.execute(ENUMERATE_GIST, {"tables": tables})
            indexes = cur.fetchall()

        for idx in indexes:
            name = idx["index_name"]
            size = idx["index_bytes"]
            if size < MIN_INDEX_BYTES:
                continue

            # Cheap probe first.
            with conn.cursor() as cur:
                cur.execute(PROBE_INDEX, (name,))
                probe = cur.fetchone()

            frag = float(probe["leaf_fragmentation"])
            density = float(probe["avg_leaf_density"])
            if frag <= LEAF_FRAGMENTATION_MAX or density >= AVG_LEAF_DENSITY_MIN:
                continue  # not bloated enough to justify the full scan

            # Escalate to the authoritative scan only for suspects.
            with conn.cursor() as cur:
                cur.execute(FULL_SCAN, (name,))
                scan = cur.fetchone()

            candidates.append(
                Candidate(
                    index_name=name,
                    table_name=idx["table_name"],
                    index_bytes=size,
                    avg_leaf_density=density,
                    leaf_fragmentation=frag,
                    free_percent=float(scan["free_percent"]),
                )
            )
    return candidates


def emit_reindex_plan(candidates: list[Candidate]) -> str:
    if not candidates:
        return "-- No GiST indexes exceeded the bloat thresholds.\n"

    lines = ["-- REINDEX plan (review before running; each rebuild is online but I/O heavy)"]
    total_reclaim = 0
    for c in candidates:
        mb = c.index_bytes / (1024 * 1024)
        reclaim_mb = c.approx_reclaimable_bytes / (1024 * 1024)
        total_reclaim += c.approx_reclaimable_bytes
        lines.append(
            f"-- {c.index_name} on {c.table_name}: "
            f"{mb:.0f} MB, frag={c.leaf_fragmentation:.1f}%, "
            f"density={c.avg_leaf_density:.1f}%, free={c.free_percent:.1f}% "
            f"(~{reclaim_mb:.0f} MB reclaimable)"
        )
        lines.append(f"REINDEX INDEX CONCURRENTLY {c.index_name};")
    lines.append(f"-- Estimated total reclaim: {total_reclaim / (1024 * 1024):.0f} MB")
    return "\n".join(lines) + "\n"


if __name__ == "__main__":
    dsn = "postgresql://monitor:secret@replica.internal:5432/fleet"
    flagged = find_bloated_indexes(dsn, ["vehicle_tracks", "trip_segments"])
    sys.stdout.write(emit_reindex_plan(flagged))

Running the module against a bloated fleet database prints something like:

-- REINDEX plan (review before running; each rebuild is online but I/O heavy)
-- idx_vehicle_tracks_position_gist on vehicle_tracks: 30720 MB, frag=37.4%, density=48.1%, free=44.2% (~13578 MB reclaimable)
REINDEX INDEX CONCURRENTLY idx_vehicle_tracks_position_gist;
-- Estimated total reclaim: 13578 MB

Configuration and Tuning Knobs

Threshold constants are the primary knobs. LEAF_FRAGMENTATION_MAX and AVG_LEAF_DENSITY_MIN gate flagging; requiring both to trip prevents rebuilding an index that is merely fragmented but still dense. MIN_INDEX_BYTES protects small indexes from ever being flagged, since reclaiming half of a 40 MB index is not worth a maintenance action.

Connection and safety settings for psycopg:

Setting Value Reason
conn.read_only = True enabled Guarantees the audit performs no writes
DSN target a hot standby Offloads the pgstattuple scan cost from the primary
statement_timeout 600000 (10 min) Caps a full scan on an unexpectedly huge index

Apply a statement timeout so a runaway pgstattuple scan cannot pin resources indefinitely:

python
with conn.cursor() as cur:
    cur.execute("SET statement_timeout = '600s'")

Scheduling. Run the flagger nightly from cron or pg_cron, capturing its output for review; only the emitted REINDEX INDEX CONCURRENTLY statements are ever executed against the primary, and only after a human confirms the plan.

Verification Steps

Confirm the measurement matches reality on one flagged index. Record the pre-rebuild metrics, run the emitted statement manually, then re-measure:

sql
-- Before
SELECT avg_leaf_density, leaf_fragmentation FROM pgstatindex('idx_vehicle_tracks_position_gist');
SELECT free_percent FROM pgstattuple('idx_vehicle_tracks_position_gist');

-- Execute the plan
REINDEX INDEX CONCURRENTLY idx_vehicle_tracks_position_gist;

-- After: density should climb toward 90, fragmentation and free_percent should collapse
SELECT avg_leaf_density, leaf_fragmentation FROM pgstatindex('idx_vehicle_tracks_position_gist');
SELECT free_percent FROM pgstattuple('idx_vehicle_tracks_position_gist');

If the post-rebuild free_percent is close to the flagger’s approx_reclaimable estimate expressed as a fraction of the old size, your thresholds and estimate are calibrated correctly.

Gotchas Checklist

  • Never interpolate index names with f-strings into the SQL text. Here the names come from a trusted catalog query and are passed as parameters to pgstatindex(%s); a name sourced from anywhere else must still be passed as a parameter, never concatenated.
  • pgstattuple on the primary during peak hours can cause a latency spike by evicting hot buffers. Point the flagger at a read replica, or restrict full scans to off-peak windows.
  • leaf_fragmentation alone is a noisy signal. A recently built index can show moderate fragmentation for benign reasons; always gate on low avg_leaf_density as well before flagging.
  • The emitted plan is not auto-executed on purpose. REINDEX INDEX CONCURRENTLY transiently doubles the index’s disk footprint; confirm free space before running it on a 30 GB index.
  • A hot standby can measure but cannot rebuild. The REINDEX statements must run on the primary and will replicate; do not expect the replica to self-heal.