Geospatial Software Development: The Layer Where Teams Actually Get Stuck

The three places geospatial work stalls

If you recognise more than one row, they are usually the same root cause: an architecture that was correct for the first dataset and was never revisited when the second arrived.

Where PostGIS slows, and why

The default spatial index in PostGIS is GiST, which implements an R-tree, the structure Guttman introduced at ACM SIGMOD in 1984 (reference) and which Beckmann and colleagues improved as the R*-tree (reference). PostgreSQL exposes it through the GiST framework of Hellerstein, Naughton and Pfeffer (VLDB 1995), which is why the same index machinery serves both text and geometry.

That history matters because it explains the failure mode. An R-tree indexes bounding boxes, so a query against large, complex geometry objects passes the index cheaply and then does expensive exact geometry work on the candidates. Three consequences, all documented in the manual:

• Index choice. The PostGIS manual documents SP-GiST as a lossy bounding-box alternative and BRIN as roughly ten times faster to build than GiST, much smaller, and lossy: excellent for large, naturally ordered data, wrong for random access patterns.
• Object size. Geometries above roughly 4 KB (about 200 vertices) hit TOAST and fragment storage, which is a documented performance cliff. The manual's own remedy is ST_Subdivide() to break large polygons into indexable pieces.
• Geometry against geography. geometry is planar and Cartesian; geography is spherical, more accurate, and more CPU-costly. Choosing geography by default for a city-scale dataset is a performance decision disguised as a correctness one.

A real example from the OpenStreetMap community is instructive: a query over a 53-million-row nodes table returned 78 rows in 22 seconds. The fix was not hardware, it was a partial GiST index filtered on the tag value, turning a full-table consideration into a small one.

The diagnostic sequence, in order:

-- 1. What is the planner actually doing?
EXPLAIN (ANALYZE, BUFFERS)
SELECT p.id FROM parcels p
WHERE ST_Intersects(p.geom, ST_MakeEnvelope(-47.1, -22.4, -47.0, -22.3, 4326));

-- 2. How big are the geometries?
SELECT ST_NPoints(geom) AS vertices, pg_column_size(geom) AS bytes
FROM parcels ORDER BY 1 DESC LIMIT 10;

-- 3. Is the index being used, and is it the right one?
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'parcels';

If step 1 shows a sequential scan, the index is missing or unusable by that predicate. If step 2 returns geometries in the tens of thousands of vertices, ST_Subdivide will help more than any index change.

Partitioning, and the refresh nobody plans for

The second stall point appears on a calendar rather than in a query plan: the dataset is refreshed annually or monthly, and the refresh takes longer every cycle until it does not fit in a maintenance window.

The pattern to avoid is DELETE followed by VACUUM on one very large table: the deletes are expensive, the table bloats, and the index rebuild is worse. The documented alternative is native table partitioning by a natural key, usually region or date: the refresh becomes detaching last year's partition and attaching this year's, which is a metadata operation rather than a data operation.

This exact question was worked through on the PostgreSQL mailing list for a dataset of a hundred million French cadastral parcels, where a team was weighing one huge table against a hundred tables joined by UNION ALL; the recommendation was native partitioning, precisely to make the annual refresh a detach-and-reattach.

When a spatial workload outgrows PostGIS

This is the section a vendor page will not write. PostGIS is the right default and it is not the right answer forever.

The research on distributed spatial processing is worth knowing before choosing: spatial partitioning and indexing techniques for cluster-scale spatial joins were formalised in work such as Eldawy and Mokbel's SpatialHadoop at ICDE 2015 (reference), and the same partitioning ideas underlie today's engines.

The honest rule: move analytical scans off PostGIS before you move the application off it. Most teams that think they have outgrown PostGIS have actually outgrown running analytics on their production database.

For Seek Research we advised against a commercial GIS platform: the licensing and rollout cost was out of proportion to the job, so the data stayed in SQL Server and PostGIS and the map went into Power BI inside SharePoint, where the team already worked.

Tile pipelines: the bill is the request count

The third stall point is a cloud bill, and the mechanism surprises people. Serving a tile pyramid as individual files on object storage means one HTTP request per tile per viewer, and object storage bills per request. Traffic that looks modest in bytes can be enormous in operations.

The documented alternative is a single-file archive read with HTTP range requests: one object, many ranged reads, and no per-tile request cost. PMTiles v3 is the format most teams reach for, and the tile content itself is usually Mapbox Vector Tile, generated with tippecanoe and served either statically or from PostGIS through Martin or pg_tileserv.

The effect is documented publicly. A team serving roughly 75,000 vector tiles from object storage for a wildfire map reported paying about USD 500 a month in request operations, and moving to a single-file PMTiles archive reduced it to pennies. The peer-reviewed literature reaches the same conclusion from the other direction: cloud-optimised tile archive formats measurably change the cost and performance of planet-scale tilesets (Tremmel, ISPRS Archives, FOSS4G 2023 academic track, reference).

Three checks if your tile bill is climbing:

1. Are you billed per request or per gigabyte? Look at the operations line, not the transfer line.
2. What is your CDN cache hit ratio? A low ratio on a tile workload usually means cache keys include something they should not.
3. Are you re-generating tiles that never change? Static basemap layers should be generated once and archived, not rendered on demand.

Imagery ingestion that survives the second year

The pattern that works at one scene and fails at ten thousand is downloading whole scenes, converting them, and re-tiling on every read. Two standards exist precisely to avoid it.

Cloud Optimized GeoTIFF is an ordinary GeoTIFF organised so that a client can fetch only the bytes it needs over an HTTP range request, with internal overviews for lower zoom levels. The practical effect is that a viewer reading one city block from a continental mosaic transfers kilobytes rather than gigabytes, and your pipeline stops copying data it will not use.

STAC (the SpatioTemporal Asset Catalog) is the metadata layer that makes those assets findable by time and space without a database scan. Together they replace the folder-and-filename convention that every imagery pipeline starts with and no imagery pipeline survives.

The volumes are worth stating, because they explain why the pattern matters:

At those cadences the question is not whether your pipeline can process a scene but whether it can keep up with revisit while staying inside a budget. For vector-heavy analytical work the equivalent move is GeoParquet in object storage read directly by DuckDB spatial or a warehouse engine; for multi-dimensional raster stacks it is Zarr. GDAL remains the tool that reads all of it.

The standards worth knowing, briefly

• OGC Simple Features defines the geometry model (points, lines, polygons, WKT and WKB) that makes spatial data portable between systems (OGC 06-103r4, also ISO 19125).
• WMS, WFS and WMTS are the older service interfaces you will meet in government and utility integrations.
• OGC API: Features is the modern, JSON-and-HTTP successor, and the one to prefer for new interfaces.
• GeoPackage is a single-file, SQLite-based container, useful for shipping datasets to field devices.
• EPSG:4326 and EPSG:3857 are the two coordinate reference systems most disagreements are actually about.

Knowing which of these a client's existing systems speak usually tells you more about integration cost than any architecture diagram.

How to tell which of the three you have

A short diagnostic, in the order that finds problems fastest:

Most engagements we take on start with one of these seven numbers being unknown, and finding it changes the plan.

Frequently asked questions

Why is PostGIS slow with millions of rows?

Usually not row count itself. The common causes are an index that does not match the query pattern, geometries above roughly 4 KB hitting TOAST and fragmenting storage, or geography used where geometry would be sufficient. The PostGIS manual documents all three, and EXPLAIN (ANALYZE, BUFFERS) will tell you which one you have.

Which spatial index should I use: GiST, SP-GiST or BRIN?

GiST is the default and the right answer for most workloads. SP-GiST is a lossy bounding-box alternative worth testing for point data. BRIN builds about ten times faster than GiST and is far smaller, but it is lossy and only suits large, naturally ordered data. A partial index filtered on a common predicate often beats all three.

What is the difference between geometry and geography in PostGIS?

geometry treats coordinates as planar and Cartesian; geography treats them as being on a sphere. Geography is more accurate over long distances and more CPU-costly on every operation. For city or regional extents, geometry in a projected SRID is usually both faster and accurate enough.

How much does map tile hosting cost at scale, and what drives the bill?

The request count, not the bytes. Individual tile files on object storage mean one billable operation per tile per viewer. A single-file archive such as PMTiles read over HTTP range requests collapses that; one documented case went from about USD 500 a month to pennies.

What is a Cloud Optimized GeoTIFF and when should I use one?

A GeoTIFF organised so clients can fetch only the bytes they need over HTTP range requests, with internal overviews. Use it for any raster served from object storage, which in practice means any raster in a cloud pipeline.

Can BigQuery, Snowflake or DuckDB replace PostGIS?

For analytical scans, often yes. For transactional workloads and precise geometry operations, no. And each has trade-offs worth knowing before you commit: DuckDB spatial, for instance, does not store a per-value SRID, so coordinate-system discipline moves into your pipeline. Most teams that believe they have outgrown PostGIS have outgrown running analytics on their production database.

The short version

• Geospatial systems stall in three places: spatial queries past roughly ten million features, tile pipelines billed by request count, and imagery ingestion that never got past its first pattern.
• Query slowness is an index, geometry-size or geography problem before it is a hardware problem.
• Annual refreshes belong in table partitioning, so the refresh is a detach and reattach.
• Tile cost is architecture: a single-file archive over HTTP range requests changes the arithmetic.
• Imagery belongs in Cloud Optimized GeoTIFF with a STAC catalogue before it belongs in a bigger instance.
• Move analytics off PostGIS before you move the application off it.