Database Services for Logistics & Supply Chain

Identifies bloated GiST spatial indexes on high-update vehicle telemetry tables and flags unindexed geofence boundary scans.

-- 1. Inspect GiST spatial index size and page count on fleet coordinates
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname LIKE %gist% OR indexrelname LIKE %spatial%
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 5;

-- 2. Identify active queries executing long-running spatial ST_Contains / ST_DWithin
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != idle AND (query ILIKE %ST_DWithin% OR query ILIKE %ST_Contains%)
ORDER BY duration DESC LIMIT 5;

Monitors live write rates, dead tuple accumulation, and autovacuum freeze urgency on real-time vehicle GPS ping partitions.

-- 1. Inspect dead tuple buildup and autovacuum status on telemetry partitions
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_tuple_pct,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname LIKE %gps% OR relname LIKE %telemetry% OR relname LIKE %shipment%
ORDER BY n_dead_tup DESC LIMIT 5;

-- 2. Monitor WAL write throughput and buffer checkpoint activity
SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time,
       buffers_checkpoint, buffers_clean, buffers_backend
FROM pg_stat_bgwriter;