PostGIS — Geospatial Types & Indexes

PostGIS is application-side. MonPG treats geometry and geography columns like any other type, but spatial indexes (GIST) get extra attention because they're notorious for bloat — heavy update workloads on geometry columns can push GiST bloat past 50% surprisingly fast.

What you get on the Indexes page when PostGIS is present: GIST index bloat percentage, suggestion to REINDEX CONCURRENTLY when bloat passes 30% on a hot path, and a small "PostGIS" badge on tables with geometry columns so you can see at a glance which tables are affected.

Enabling

Pure DDL, but the install adds a lot of system tables — around 50 of them. CREATE EXTENSION postgis; on managed PG. On Azure, add postgis to azure.extensions first.

Performance gotchas we flag

The three patterns the Index Advisor warns on:

• SRID mismatch in WHERE. The query plan shows a Filter step where the spatial index gets bypassed because of an implicit CRS conversion. The fix is to store data in the SRID you query in, not the other way round.
• ST_Distance without an index hint. The <-> operator with a KNN-aware index is what you want for nearest-neighbor; ST_Distance(...) with a < threshold misses the index entirely.
• GiST page splits. Slow inserts on heavily indexed tables. Periodic REINDEX is the standard fix.

None of these are pgsmart — every PostGIS expert knows them. We just make the diagnostic visible so you don't have to look for it.