PostGIS Best Practices

How to Use This List

• Apply on every new geospatial table and ETL pipeline.
• Re-audit after bulk shapefile or GeoJSON imports.
• Pair with spatial index article before production map launch.

A - Data Model

• Store SRID with every geometry. Use typmod geometry(Type, 4326) or enforce ST_SRID(geom) = 4326.
• Validate inputs on ingest. ST_IsValid(geom) constraint or ETL quarantine table for rejects.
• Use geography for GPS meter radii. Cast or column-type geography for ST_DWithin in meters.
• Use projected geometry for local planar work. UTM for city parcels, not 4326 geometry distance.
• Document lon/lat order. ST_MakePoint(longitude, latitude) in team style guide.

B - Indexes and Queries

• Create GiST index on columns used in spatial WHERE. geom and geography as queried.
• Prefilter with && envelope before ST_Intersects. Index-friendly two-stage pattern.
• Use ST_DWithin instead of ST_Distance sort on full table. Radius cap then order small set.
• Build indexes CONCURRENTLY on large tables. Plan ANALYZE after import.
• Apply tenant_id filter before spatial op. Shrinks index scan range in SaaS.

C - Performance and APIs

• Store simplified geometries for low zoom layers. ST_SimplifyPreserveTopology offline.
• LIMIT features per map tile request. Reject world-scale bounding boxes from clients.
• Serve tiles via tile server when possible. PostGIS is source of truth, not render farm.
• Emit GeoJSON at API boundary. Do not store GeoJSON text instead of geometry type.
• Monitor ST_NPoints on ingest. Complex polygons alert before GiST bloat.

D - Operations

• Pin PostGIS version in allowlist ADR. ALTER EXTENSION postgis UPDATE with smoke tests.
• Run postgis_extensions_upgrade when docs require. After PG major or PostGIS minor bumps.
• REINDEX GiST after massive shapefile loads. Schedule CONCURRENTLY where supported.
• Keep PostGIS in extensions schema. Avoid public search_path clutter.
• Test known distance pairs. NYC to LA geodesic sanity query in CI staging.

FAQs

Related

• PostGIS Basics - Postgres vs tile servers
• Geometry vs Geography - type selection
• Spatial Indexes - GiST patterns
• PostGIS Performance - simplify and prefilter
• Extensions Best Practices - version pins

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, and Patroni 3.x.