Spatial Performance Monitoring & Observability

A ride-hailing dispatch platform does not degrade because someone wrote one bad query — it degrades because thousands of small proximity lookups per second slowly outrun the resources allocated to them, and nobody is watching the right counters. A GiST index quietly bloats under continuous position updates, autovacuum falls behind on the driver_positions table, planner statistics drift, and the same ST_DWithin filter that returned in two milliseconds last week now takes forty. None of that trips a single error. Observability is the discipline of making these slow drifts visible before they become an incident, and this guide covers how to instrument, measure, and alert on PostGIS spatial performance in production Python services.

The workload assumed throughout is a dispatch backend with three hot tables: driver_positions (one geom geometry(Point,4326) row per driver, updated every few seconds), pickup_requests (rider origin points), and trips (in-flight and completed journeys). The query mix is overwhelmingly ST_DWithin radius filters and KNN <-> nearest-driver lookups. Monitoring this well is a natural companion to query plan analysis with EXPLAIN — EXPLAIN tells you why one query is slow, while observability tells you which queries to run EXPLAIN against and when the fleet-wide picture has changed. It also feeds back into spatial schema migrations and evolution, because the metrics you collect here justify when a reindex, a partition, or a column change is worth the operational risk.

What Observability Means for a Spatial Workload

Monitoring a spatial database is not the same as monitoring a generic OLTP database, because the failure modes are different. A key-value lookup on an integer primary key is cheap, predictable, and almost never regresses. A ST_DWithin proximity filter against a moving fleet is none of those things: its cost depends on the density of drivers near the query point, on how tightly the GiST index bounding boxes still group together after thousands of position updates, and on whether the planner’s selectivity estimate still reflects reality. The same SQL text can be fast in a rural service area and slow downtown at surge time.

Effective observability for this workload rests on four data sources exposed by PostgreSQL and its extensions. The first, and most valuable, is pg_stat_statements for spatial workloads, which normalises every executed statement and aggregates timing and I/O so you can rank the spatial queries that dominate cumulative latency. The second is bloat measurement, covered in detecting GiST index bloat, which tells you when the R-tree behind your proximity queries has degraded into fragmented, oversized pages. The third is vacuum and statistics health, covered in autovacuum tuning for geometry tables, which keeps dead tuples and stale histograms from silently poisoning your query plans. The fourth is the family of cumulative statistics views — pg_stat_user_tables, pg_stat_user_indexes, and pg_statio_user_tables — that expose scan counts, index usage, and buffer cache behaviour per relation.

Together these sources answer four operational questions: which spatial statements are expensive, whether the indexes serving them are healthy, whether maintenance is keeping up, and whether the hot data is resident in memory. The rest of this guide walks each source in turn, then shows how to fold them into dashboards and alerts and hand the results back to a Python collector.

Instrumenting Spatial Hotspots with pg_stat_statements

The single highest-leverage step is enabling pg_stat_statements. It must be loaded through shared_preload_libraries, which requires a restart, so it is worth doing before you have an incident rather than during one:

-- postgresql.conf (requires restart)
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.max = 10000
-- pg_stat_statements.track = top

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Once active, the extension collapses literal parameters so that a million dispatch lookups with different coordinates aggregate into one normalised row. That normalisation is exactly what makes it possible to see the cost of your proximity pattern as a whole rather than as noise. Filter for spatial functions and rank by cumulative time:

SELECT
    queryid,
    calls,
    round(mean_exec_time::numeric, 3)            AS mean_ms,
    round(total_exec_time::numeric / 1000, 1)    AS total_sec,
    round(100.0 * shared_blks_hit
          / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_pct,
    left(query, 80)                              AS query_sample
FROM pg_stat_statements
WHERE query ILIKE '%st_dwithin%'
   OR query ILIKE '%st_distance%'
   OR query LIKE '%<->%'
ORDER BY total_exec_time DESC
LIMIT 20;

The two columns that matter most are mean_exec_time and total_exec_time. A statement with a high mean but few calls is an occasional heavy report; a statement with a modest mean but enormous call count is your dispatch hot path, and shaving a millisecond off it saves more wall-clock time across the fleet than optimising anything else. The cache_hit_pct expression above, computed per statement, tells you whether a slow query is CPU-bound geometry work or I/O-bound cache misses. A dispatch service should aim to keep its top proximity statements above roughly 99 percent buffer hits.

Reset the counters after each deployment so a query’s statistics reflect the current code, not a mix of old and new plans:

SELECT pg_stat_statements_reset();

From here, drilling into an individual expensive statement — mapping it back to the API endpoint that issued it and snapshotting its counters on a schedule — is covered in finding slow ST_ function calls with pg_stat_statements. A minimal Python poll that stores a periodic snapshot looks like this:

import psycopg
from datetime import datetime, timezone

SNAPSHOT_SQL = """
    SELECT queryid, calls, total_exec_time, mean_exec_time,
           shared_blks_hit, shared_blks_read
    FROM pg_stat_statements
    WHERE query ILIKE '%st_dwithin%' OR query LIKE '%<->%'
    ORDER BY total_exec_time DESC
    LIMIT 25;
"""

def snapshot_spatial_hotspots(dsn: str) -> list[dict]:
    taken_at = datetime.now(timezone.utc)
    with psycopg.connect(dsn) as conn:
        with conn.cursor(row_factory=psycopg.rows.dict_row) as cur:
            cur.execute(SNAPSHOT_SQL)
            rows = cur.fetchall()
    for r in rows:
        r["taken_at"] = taken_at.isoformat()
    return rows

Because pg_stat_statements reports cumulative totals since the last reset, the collector should compute deltas between consecutive snapshots to derive a per-interval rate — that is what turns a slowly-growing counter into a latency graph.

Reading GiST Index Bloat

A GiST index is an R-tree of bounding boxes over your geometries. On an append-only table the tree stays compact, but driver_positions is the opposite: every position update is an MVCC delete-plus-insert that leaves a dead index entry behind and can split leaf pages. Over days of continuous updates the index grows several times larger than the data it describes, its bounding boxes overlap more, and every proximity scan visits more pages to find the same candidates.

The pgstattuple extension exposes the true internal state of an index:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Bloat signals for the proximity index on the moving fleet
SELECT
    pg_size_pretty(pg_relation_size('driver_positions_geom_gix')) AS index_size,
    round((s.leaf_fragmentation)::numeric, 1)                     AS leaf_frag_pct
FROM pgstatindex('driver_positions_geom_gix') s;

Read leaf_fragmentation as the percentage of leaf pages that are out of logical order. A value climbing past 50 percent, combined with an index that has grown far beyond the size implied by its live row count, is a reliable signal to rebuild:

-- Rebuild without blocking dispatch writes
REINDEX INDEX CONCURRENTLY driver_positions_geom_gix;

Because pgstattuple scans the whole index, it is not something to run every minute against a large relation — schedule it hourly or daily and store the result as a time series so you can see the bloat trend rather than a single point. The full measurement methodology, including how to estimate bloat cheaply from catalog sizes before paying for a full pgstattuple scan, lives in detecting GiST index bloat. What matters at this overview level is the principle: on a high-churn spatial table, index bloat is not an edge case, it is the default trajectory, and you must measure it on a schedule.

Autovacuum, ANALYZE, and Planner Statistics

The reason bloat and stale plans go together is that both are governed by vacuum. VACUUM reclaims dead tuples and updates the visibility map; ANALYZE refreshes the statistics the planner uses to estimate selectivity. On a geometry table receiving continuous updates, the default autovacuum thresholds — which trigger only after 20 percent of the table has changed — are far too lax. By the time autovacuum fires, tens of thousands of dead position rows have already accumulated and the geometry histogram is stale.

The fix is per-table tuning that reflects the write rate of a moving fleet:

ALTER TABLE driver_positions SET (
    autovacuum_vacuum_scale_factor  = 0.02,   -- vacuum at 2% dead rows
    autovacuum_analyze_scale_factor = 0.01,   -- re-analyze at 1% changed rows
    autovacuum_vacuum_cost_delay    = 2,      -- less I/O throttling
    autovacuum_vacuum_cost_limit    = 1000
);

Statistics quality matters more for geometry than for scalar columns because spatial distributions are skewed — dense city centres, empty highways. The default statistics target captures too coarse a picture of the bounding-box histogram, so raise it on the geometry column specifically and re-analyze:

ALTER TABLE driver_positions
    ALTER COLUMN geom SET STATISTICS 500;
ANALYZE driver_positions;

You can confirm that autovacuum is actually keeping pace by reading its per-table bookkeeping from pg_stat_user_tables:

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
    last_autovacuum,
    last_autoanalyze,
    autovacuum_count
FROM pg_stat_user_tables
WHERE relname IN ('driver_positions', 'pickup_requests', 'trips')
ORDER BY dead_pct DESC;

A dead_pct that keeps climbing between autovacuum runs means the daemon cannot keep up and you need more aggressive thresholds or more autovacuum workers. A last_autoanalyze that is hours stale on a fast-changing table is a direct explanation for why a GiST plan flipped to a sequential scan. The complete cadence — including scheduling a manual VACUUM (ANALYZE, VERBOSE) after bulk backfills and coordinating it with reindex windows — is developed in autovacuum tuning for geometry tables.

Reading the Cumulative Statistics Views

Beyond the three areas above, PostgreSQL’s cumulative statistics views give you per-relation and per-index visibility that no single query can. These are the counters a collector should scrape on a fixed interval.

pg_stat_user_indexes — Is the Index Even Used?

The most basic index question is whether the planner uses it at all. An index with idx_scan = 0 after weeks of traffic is pure write overhead — it slows every position update and every vacuum without serving a read:

SELECT
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size
FROM pg_stat_user_indexes
WHERE relname IN ('driver_positions', 'pickup_requests', 'trips')
ORDER BY idx_scan ASC;

If the GiST index on driver_positions.geom shows a low or zero scan count while the table is clearly serving proximity queries, that is a red flag: the planner has abandoned it, almost always because of stale statistics or an SRID mismatch that wraps geom in an implicit transform. This is precisely the situation where query plan analysis with EXPLAIN turns the symptom into a root cause.

pg_stat_user_tables — Sequential Scans and Tuple Churn

Sequential scans on a large spatial table are the clearest sign the index path has failed. Track the ratio of sequential to index scans over time:

SELECT
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    round(100.0 * idx_scan / nullif(seq_scan + idx_scan, 0), 1) AS idx_scan_pct,
    n_tup_upd,
    n_tup_hot_upd,
    round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1)      AS hot_upd_pct
FROM pg_stat_user_tables
WHERE relname IN ('driver_positions', 'pickup_requests', 'trips');

Two derived ratios are worth alerting on. A falling idx_scan_pct on driver_positions means proximity queries are increasingly falling back to full scans. A low hot_upd_pct means position updates are not using heap-only-tuple optimisation — often because a fill factor is too high or an update touches an indexed column — which accelerates both table and index bloat.

pg_statio_user_tables — Buffer Cache Hit Ratio

The buffer cache hit ratio is the difference between a proximity lookup served from RAM and one that pays for disk I/O. For a latency-sensitive dispatch table you want the working set of active drivers resident in shared_buffers essentially all the time:

SELECT
    relname,
    heap_blks_read,
    heap_blks_hit,
    round(100.0 * heap_blks_hit
          / nullif(heap_blks_hit + heap_blks_read, 0), 3) AS heap_hit_pct,
    idx_blks_read,
    idx_blks_hit,
    round(100.0 * idx_blks_hit
          / nullif(idx_blks_hit + idx_blks_read, 0), 3)   AS idx_hit_pct
FROM pg_statio_user_tables
WHERE relname IN ('driver_positions', 'pickup_requests', 'trips');

A heap_hit_pct or idx_hit_pct that drifts below about 99 for the dispatch table means the hot data no longer fits in cache. The remedy is not always more RAM — a bloated index inflates the working set, so bloat control and cache residency are directly linked. This is another reason the metrics reinforce each other: a bloat alert and a cache-miss alert on the same table usually share one root cause.

From Metrics to Dashboards and Alerts

Raw catalog queries are diagnostics, not monitoring. Monitoring means scraping these views on an interval, storing the results as a time series, and alerting on thresholds. The counters split into two shapes: cumulative totals that must be differenced (everything in pg_stat_statements, seq_scan, idx_scan, block counters) and instantaneous gauges you can read directly (n_dead_tup, index size, leaf_fragmentation).

A compact Python collector using psycopg can gather everything a dashboard needs in one poll:

import psycopg

COLLECTOR_QUERIES = {
    "table_health": """
        SELECT relname, n_live_tup, n_dead_tup, seq_scan, idx_scan,
               last_autovacuum, last_autoanalyze
        FROM pg_stat_user_tables
        WHERE relname IN ('driver_positions','pickup_requests','trips')
    """,
    "cache_io": """
        SELECT relname, heap_blks_hit, heap_blks_read,
               idx_blks_hit, idx_blks_read
        FROM pg_statio_user_tables
        WHERE relname IN ('driver_positions','pickup_requests','trips')
    """,
    "index_usage": """
        SELECT indexrelname, idx_scan,
               pg_relation_size(indexrelid) AS idx_bytes
        FROM pg_stat_user_indexes
        WHERE relname IN ('driver_positions','pickup_requests','trips')
    """,
}

def collect(dsn: str) -> dict[str, list[dict]]:
    out: dict[str, list[dict]] = {}
    with psycopg.connect(dsn, connect_timeout=5) as conn:
        with conn.cursor(row_factory=psycopg.rows.dict_row) as cur:
            for name, sql in COLLECTOR_QUERIES.items():
                cur.execute(sql)
                out[name] = cur.fetchall()
    return out

Run this from a scheduled worker, difference the cumulative fields against the previous poll, and emit gauges and rates to your time-series backend. The dashboards that matter for dispatch are a mean-latency panel driven by pg_stat_statements deltas, a cache-hit-ratio panel per table, a dead-tuple-percentage panel, and an index-size trend that exposes bloat before a scan ever notices it.

Alert thresholds should be tied to user-visible symptoms, not to arbitrary internals:

Set statement_timeout on the application role so a pathological proximity query — one issued without a bounding radius, say — cannot pin a connection and cascade into the pool:

ALTER ROLE dispatch_app SET statement_timeout = '3000ms';

Correlating Database Metrics with Application Latency

Database counters are necessary but not sufficient; the number that matters to a rider is end-to-end dispatch latency. Close the loop by tagging application traces with the queryid that pg_stat_statements assigns, so a spike in a specific normalised statement can be joined to a spike in a specific endpoint. A thin timing wrapper around the dispatch query is enough to emit that correlation:

import time
import psycopg

def find_nearby_drivers(conn: psycopg.Connection, lon: float, lat: float,
                        radius_m: float, limit: int = 10) -> list[tuple]:
    sql = """
        SELECT driver_id,
               ST_Distance(geom::geography,
                           ST_SetSRID(ST_MakePoint(%(lon)s, %(lat)s), 4326)::geography) AS dist_m
        FROM driver_positions
        WHERE ST_DWithin(
                  geom::geography,
                  ST_SetSRID(ST_MakePoint(%(lon)s, %(lat)s), 4326)::geography,
                  %(radius)s)
          AND status = 'available'
        ORDER BY geom <-> ST_SetSRID(ST_MakePoint(%(lon)s, %(lat)s), 4326)
        LIMIT %(limit)s;
    """
    start = time.perf_counter()
    with conn.cursor() as cur:
        cur.execute(sql, {"lon": lon, "lat": lat, "radius": radius_m, "limit": limit})
        rows = cur.fetchall()
    elapsed_ms = (time.perf_counter() - start) * 1000
    # emit elapsed_ms to your metrics backend, tagged by endpoint
    return rows

When the application-side timer and the database-side mean_exec_time diverge, the gap is time spent outside query execution — connection acquisition, network, serialisation — and points you at the pool rather than at PostGIS. When they move together, the database is the bottleneck and the areas above tell you which lever to pull. The broader family of proximity and KNN patterns this instruments is catalogued in Mastering Core Spatial Query Patterns.

Production Checklist

• Load pg_stat_statements via shared_preload_libraries; set pg_stat_statements.max Load `pg_stat_statements` via `shared_preload_libraries`; set `pg_stat_statements.max` high enough to hold your spatial statement variety
• Rank spatial statements by total_exec_time Rank spatial statements by `total_exec_time` weekly; investigate the top proximity and KNN entries
• Reset pg_stat_statements Reset `pg_stat_statements` after each deployment so plan changes are measured cleanly
• Schedule pgstattuple/pgstatindex Schedule `pgstattuple`/`pgstatindex` bloat checks on geometry indexes; store the trend, not a snapshot
• REINDEX INDEX CONCURRENTLY GiST indexes when leaf_fragmentation `REINDEX INDEX CONCURRENTLY` GiST indexes when `leaf_fragmentation` exceeds 50%
• Set autovacuum_vacuum_scale_factor = 0.02 and autovacuum_analyze_scale_factor = 0.01 Set `autovacuum_vacuum_scale_factor = 0.02` and `autovacuum_analyze_scale_factor = 0.01` on high-churn geometry tables
• Raise STATISTICS to 500 on the geometry column and ANALYZE Raise `STATISTICS` to 500 on the geometry column and `ANALYZE` after backfills
• Alert on idx_scan_pct falling on driver_positions Alert on `idx_scan_pct` falling on `driver_positions` — the earliest sign a GiST plan flipped to a sequential scan
• Keep the buffer hit ratio above 0.99 for the dispatch table; a persistent drop means the working set outgrew Keep the buffer hit ratio above 0.99 for the dispatch table; a persistent drop means the working set outgrew `shared_buffers`
• Difference cumulative counters between polls; never graph raw totals
• Tie alert thresholds to user-visible latency, and set a role-level Tie alert thresholds to user-visible latency, and set a role-level `statement_timeout`
• Correlate application traces with pg_stat_statements queryid Correlate application traces with `pg_stat_statements` `queryid` to separate database time from pool time

Frequently Asked Questions

Which single extension gives the most PostGIS observability value?

The clear answer is pg_stat_statements. It normalises every executed statement and aggregates call counts, mean and total execution time, and shared-buffer I/O. For a dispatch workload dominated by ST_DWithin and KNN lookups it immediately ranks the spatial statements that consume the most cumulative time, which is where index and query work pays off first. Everything else you monitor — bloat, vacuum, cache ratios — is best interpreted as an explanation for a movement you first noticed there.

How do I know if a GiST index on a geometry column is bloated?

Query pgstattuple on the index and watch leaf_fragmentation and the ratio of index size to live tuples. On a table like driver_positions that receives constant position updates, leaf_fragmentation above 50 percent or an index that has grown several times larger than its row count warrants a REINDEX INDEX CONCURRENTLY. Because the scan is expensive, run it on a schedule and store the result as a time series so you react to the trend rather than one reading.

Why does a healthy GiST index suddenly start losing to sequential scans?

Almost always stale statistics. After a burst of inserts or updates the planner still uses the old row estimates and geometry histogram, so it may misjudge selectivity and pick a sequential scan. Aggressive autovacuum plus a raised statistics target on the geometry column keeps pg_stats current and preserves the index plan. Watching idx_scan_pct in pg_stat_user_tables catches the flip early, and an EXPLAIN (ANALYZE, BUFFERS) on the offending query confirms the cause.

What cache hit ratio should a spatial table target?

For a latency-sensitive dispatch table, keep the shared-buffer hit ratio above roughly 0.99. A ratio drifting toward 0.95 or lower means the working set of active driver positions no longer fits in cache and each proximity lookup is paying disk I/O, which surfaces directly as rising mean_exec_time in pg_stat_statements. Because index bloat inflates the working set, fixing bloat often restores the hit ratio without adding RAM.

Related Topics

• pg_stat_statements for Spatial Workloads — enabling the extension and ranking normalised ST_ statements by cumulative time
• Detecting GiST Index Bloat — measuring leaf fragmentation and oversize on high-churn geometry indexes
• Autovacuum Tuning for Geometry Tables — per-table scale factors and statistics targets that keep plans stable
• Spatial Schema Migrations & Evolution — sibling section on evolving geometry schemas safely once monitoring justifies a change
• Query Plan Analysis with EXPLAIN — turning a monitoring symptom into a root-cause plan diagnosis