PostGIS in Production: Why Geo Queries Get Slow After the Demo

The first PostGIS feature usually ships fast. Store a point, ask what is nearby, show pins on a map. Nobody wants a separate geo service once Postgres answers the demo query in a few milliseconds.

Then the workload changes. Users search within 50 miles. Drivers update location every few seconds. Tenants have different service regions. Someone imports data with the wrong SRID. The same function names are still in the query, but the planner is now doing far more work than the team thinks.

Most slow geo systems are not slow because PostGIS is weak. They are slow because the query asks the database to calculate distance before it has narrowed the search space.

The framework: prune first, measure second, rank last

Good production geo queries usually follow one pattern:

1. Use an index-aware spatial predicate to cut the candidate set.
2. Apply tenant, status, and permission filters in a way the planner can combine.
3. Rank or calculate exact distance only after the candidate set is small.

The bad pattern does the expensive math across too many rows and hopes hardware saves it.

-- Correct math, painful shape at scale.
SELECT id, name
FROM locations
WHERE ST_Distance(
  geog,
  ST_SetSRID(ST_Point($1, $2), 4326)::geography
) < 5000;

This can be mathematically right and operationally wrong. The database has to evaluate distance too broadly.

Use ST_DWithin as the radius gate

For radius search, I want ST_DWithin in the WHERE clause because it is index-aware. It can use a spatial index to reduce the rows before exact distance or ranking logic runs.

CREATE INDEX locations_geog_gix
ON locations
USING gist (geog);

SELECT id, name
FROM locations
WHERE ST_DWithin(
  geog,
  ST_SetSRID(ST_Point($1, $2), 4326)::geography,
  5000
)
ORDER BY geog <-> ST_SetSRID(ST_Point($1, $2), 4326)::geography
LIMIT 50;

The important part is the shape: radius gate first, nearest ordering second. This lets the index do pruning work before the query ranks rows.

Do not hide the constant from KNN

The <-> operator is useful for nearest-neighbor ordering with GiST. The trap is subtle: the index-assisted KNN path depends on the operator being used in ORDER BY with a constant comparison value.

That is why I avoid wrapping the search point in a subquery or CTE when tuning KNN. It may look cleaner, but it can prevent the index-assisted path you were trying to get.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM locations
ORDER BY geog <-> ST_SetSRID(ST_Point(-73.9857, 40.7484), 4326)::geography
LIMIT 25;

Read the plan. If the query is not using the spatial index, the SQL may still be correct but the serving shape is wrong.

Geometry versus geography is a product decision

geometry uses the units of its coordinate system. With SRID 4326, those units are degrees. geography uses earth-distance semantics and distances in meters, which is what most product teams mean when they say "within 5 kilometers."

I use geography for broad earth-distance features where meters matter more than local projection performance. I use geometry with an appropriate projected SRID when the area is local and performance or GIS operations matter.

The mistake is not choosing one or the other. The mistake is storing coordinates as one type and writing product logic as if they were the other.

SRID bugs are production bugs

SRID mismatches are not a GIS footnote. They create wrong answers, slow plans, and sometimes both. If the column is transformed inside the predicate, the index may no longer be useful in the way you expected.

-- Good habit: normalize incoming data before it reaches the hot query path.
INSERT INTO locations (organization_id, geog)
VALUES (
  $1,
  ST_SetSRID(ST_Point($2, $3), 4326)::geography
);

-- Audit what the table says it stores.
SELECT Find_SRID('public', 'locations', 'geom');

For feeds from mobile apps, partners, or GIS exports, assume bad coordinates will arrive. Add ingestion checks. Detect impossible latitude and longitude values. Reject unknown SRIDs before they become support tickets.

Tenant filters can beat the spatial index

SaaS geo queries are rarely global. They usually include tenant, status, region, category, or permission filters.

SELECT id
FROM locations
WHERE organization_id = $1
  AND status = 'active'
  AND ST_DWithin(
    geog,
    ST_SetSRID(ST_Point($2, $3), 4326)::geography,
    5000
  )
ORDER BY geog <-> ST_SetSRID(ST_Point($2, $3), 4326)::geography
LIMIT 25;

This is where production surprises happen. A spatial index helps with location. A B-tree index helps with tenant and status. The planner has to decide how to combine them. If one tenant owns most rows and another owns almost none, the same query shape can have very different behavior.

For some systems, the best fix is not another spatial function. It is a better data shape: partition by region, separate hot moving objects from cold static locations, or add a coarse geohash/region column that narrows the search before spatial work begins.

The moving-object problem is different

Stores and warehouses are mostly static. Drivers, vehicles, devices, and field teams are not. High-frequency location updates can turn a spatial index into a write-amplification problem.

If objects move constantly, I separate the questions:

• What is the authoritative location history?
• What is the current serving position?
• How stale can the serving position be?
• Does every update need to hit the spatially indexed table?

Sometimes the right answer is a current-position table optimized for serving plus an append-only history table for analytics. Treating both as one table can make writes and reads fight each other.

How I triage a slow geo query

• Run EXPLAIN (ANALYZE, BUFFERS) with production-like parameter values.
• Confirm the query uses an index-aware predicate such as ST_DWithin.
• Confirm the spatial index is GiST or SP-GiST where appropriate.
• Check whether transformations happen on the column side.
• Measure rows removed by tenant/status filters after the spatial scan.
• Compare tenants with different row counts and geographic density.
• Check whether KNN ordering is actually index-assisted.

When PostGIS is the right production choice

Keep geo inside Postgres when the product data already lives there, the query shapes are known, and the team can explain how the spatial index prunes work. PostGIS is excellent when schema, SRID, and query shape are treated as production design, not just GIS syntax.

Consider a separate serving layer when updates are extremely high frequency, ranking becomes domain-specific, or geo search needs to scale independently from the OLTP database.

The mistake is not using PostGIS. The mistake is assuming a correct spatial function automatically becomes a scalable serving query.