PostGIS Explained: When a Spatial Database Beats a Folder of Files #

Problem statement #

The folder started with three shapefiles. It now has ninety, plus parcels_v2_FINAL.gpkg, plus a backup directory nobody dares delete. Two people edit it. The nightly job reads a file that a colleague is halfway through rewriting.

data/
├── parcels_2024.shp          # which one is current?
├── parcels_2024_fixed.shp
├── parcels_v2_FINAL.gpkg
├── parcels_v2_FINAL_v3.gpkg
└── backup/                   # 4.2 GB

Files are an excellent way to store spatial data and a poor way to manage it. They have no concurrency control, no schema enforcement, no query engine, and no way to answer "which parcels changed last week" without opening all of them.

PostGIS is the usual next step. But it is not a straight upgrade — it adds a server to run, a schema to design, and a connection to configure. Knowing when it pays for itself is more useful than knowing how to install it.

Quick answer #

Move to PostGIS when at least two of these are true:

1. More than one person or process writes the data. Files have no locking worth the name; a database has transactions.
2. The data does not fit comfortably in memory. SQL filters before Python loads; read_file() does not.
3. You need attribute types that files cannot hold. Shapefile has no real dates, no booleans, and a 10-character column-name limit.
4. The interesting questions are relational. Joining parcels to owners to permits is one SQL query and three file reads.
5. You need history. A table with a valid-from column beats forty files named after dates.

Stay on files when the data is read-only, fits in memory, has one writer, and is handed to people who open it in QGIS.

# the shape of the difference
import geopandas as gpd
from sqlalchemy import create_engine

# files: read everything, then filter
gdf = gpd.read_file("parcels.gpkg")                      # 2.4 GB into RAM
big = gdf[(gdf.area > 10_000) & (gdf.ward == "Leith")]   # 812 rows survive

# PostGIS: filter, then read
engine = create_engine("postgresql://user:pass@localhost:5432/gis")
big = gpd.read_postgis(
    """
    SELECT parcel_id, ward, geom
    FROM parcels
    WHERE ward = 'Leith' AND ST_Area(geom) > 10000
    """,
    engine, geom_col="geom",
)                                                        # 812 rows over the wire

The second version moves 812 rows across the network instead of 2.4 GB into RAM. That difference is the whole argument.

What each one is actually good at #

Step-by-step solution #

What PostGIS actually is #

PostgreSQL is a relational database. PostGIS is an extension that teaches it about geometry: a geometry column type, several hundred ST_ functions, and spatial indexes.

CREATE EXTENSION postgis;

CREATE TABLE parcels (
    parcel_id  bigserial PRIMARY KEY,
    ward       text NOT NULL,
    surveyed   date,
    area_m2    double precision GENERATED ALWAYS AS (ST_Area(geom)) STORED,
    geom       geometry(Polygon, 27700) NOT NULL
);

CREATE INDEX parcels_geom_idx ON parcels USING GIST (geom);

Four things in that snippet have no equivalent in a shapefile: a real date type, a NOT NULL constraint, a generated column that cannot drift from the geometry, and a declared CRS the database enforces on every insert.

The geometry(Polygon, 27700) declaration is the part people underuse. It means the database rejects a MultiPolygon, and rejects anything in the wrong CRS, at write time. Half the data-cleaning work in a file-based pipeline is catching problems a typed column would have refused.

The spatial index is the performance story #

-- without the GIST index: scan every row, test every geometry
EXPLAIN ANALYZE SELECT * FROM parcels WHERE ST_Intersects(geom, :boundary);
--  Seq Scan on parcels  (cost=0.00..48210.00 rows=1 width=1284)
--  Execution Time: 3894.221 ms

-- with it: bounding-box lookup first, exact test on the survivors
--  Bitmap Heap Scan on parcels  (cost=4.31..112.44 rows=1 width=1284)
--  Execution Time: 11.408 ms

This is the same R-tree idea GeoPandas uses via sindex, with one difference that matters: the database index is persistent. GeoPandas builds one per session, on data it has already loaded into memory. PostGIS built one when you created it and uses it on 40 million rows without loading any of them.

Where GeoPandas fits after the move #

You do not stop using GeoPandas. You change what it receives.

# PostGIS does the selection; GeoPandas does the analysis
sql = """
    SELECT p.parcel_id, p.ward, p.geom
    FROM parcels p
    JOIN wards w ON ST_Within(p.geom, w.geom)
    WHERE w.name = %(ward)s
      AND p.surveyed > %(since)s
"""
gdf = gpd.read_postgis(sql, engine, geom_col="geom",
                       params={"ward": "Leith", "since": "2024-01-01"})

# now do the thing SQL is bad at
gdf["cluster"] = cluster_parcels(gdf)

The division that works: SQL for selection, joining and aggregation; Python for modelling, iteration and anything with a library behind it. Trying to do k-means in SQL is as unpleasant as trying to join 40 million rows in pandas.

What you give up #

• A server to run. Someone has to back it up, upgrade it and hold the credentials.
• Portability. You cannot email a database. Exporting to GeoPackage for delivery becomes a pipeline step.
• Simplicity in version control. A shapefile has a checksum. A database has a migration history, which is better, but only once you set it up.
• Offline work. Files work on a train.

Code examples #

Example 1: loading a folder of files into PostGIS once #

import geopandas as gpd
from pathlib import Path
from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg://user:pass@localhost:5432/gis")

for path in sorted(Path("data").glob("*.gpkg")):
    gdf = gpd.read_file(path)
    gdf = gdf.to_crs(27700)                       # one CRS for the whole database
    gdf.columns = [c.lower() for c in gdf.columns]
    gdf.to_postgis(path.stem, engine, if_exists="replace", index=False)
    print(f"{path.name:32s} {len(gdf):>8,} rows")

Then create the indexes, which to_postgis does not do for you:

CREATE INDEX ON parcels USING GIST (geom);
ANALYZE parcels;

Skipping ANALYZE is a common reason a freshly loaded table stays slow: the planner has no statistics and keeps choosing a sequential scan.

Example 2: the query that files cannot answer cheaply #

-- parcels with no building, within 200 m of a school, surveyed this year
SELECT p.parcel_id, p.ward, ST_Area(p.geom) AS area_m2
FROM parcels p
WHERE p.surveyed >= date_trunc('year', now())
  AND NOT EXISTS (
      SELECT 1 FROM buildings b WHERE ST_Intersects(b.geom, p.geom)
  )
  AND EXISTS (
      SELECT 1 FROM schools s WHERE ST_DWithin(s.geom, p.geom, 200)
  )
ORDER BY area_m2 DESC
LIMIT 50;

In GeoPandas that is three file reads, two spatial joins, an anti-join and a sort — and every one of those materialises in memory. In PostGIS it is one query that touches three indexes and returns 50 rows.

Example 3: keeping files in the workflow anyway #

# database is the source of truth; files are the deliverable
def export_ward(ward: str, out_dir: Path) -> Path:
    gdf = gpd.read_postgis(
        "SELECT * FROM parcels WHERE ward = %(w)s",
        engine, geom_col="geom", params={"w": ward},
    )
    out = out_dir / f"{ward.lower()}_parcels.gpkg"
    gdf.to_file(out, driver="GPKG", layer="parcels")
    return out

This is the pattern most teams land on: PostGIS holds the canonical data, and every delivery is a generated GeoPackage with a timestamp. Nobody edits the deliverable, because regenerating it is one function call.

Explanation #

The deep difference is where the filtering happens.

A file format is a container. To find the 812 parcels in Leith, something must read every parcel, because there is nowhere else the information could be. GeoPackage helps — it is SQLite underneath and carries an R-tree index — but GeoPandas still reads matching rows into a DataFrame before you can do anything with them, and a read_file() with no bbox or where reads all of them.

A database is a query engine wrapped around storage. It has statistics about the data, indexes over it, and a planner that decides how to combine them. It answers questions without materialising the data anywhere, and it does that on data far larger than the memory of the machine asking.

The second difference is concurrency. Two processes writing one shapefile corrupt it, and there is no mechanism that prevents this. Two processes writing one PostGIS table produce a correct result, because that is what transactions are for. As soon as a workflow has a scheduled writer and a human editor, this stops being theoretical.

The third is constraints. NOT NULL, CHECK, geometry(Polygon, 27700), foreign keys — every one of those is a data-quality problem you no longer have to write cleaning code for, because bad data never gets in.

None of that makes files obsolete. The right architecture for most teams is a database as the system of record and files as the interchange format, which is exactly how the rest of the data world works.

Edge cases or notes #

• GeoPackage is a database too. If you need indexes and SQL but not concurrency or a server, GPKG plus sqlite3 gets you a surprising distance for free.
• One CRS per table, chosen deliberately. Mixed-CRS tables are legal via geometry(Geometry, 0) and are a permanent source of confusion. Reproject on the way in.
• geography is not geometry. geography computes on the spheroid and is right for global distances; geometry is planar, faster, and right for a projected national grid. Pick one per column.
• to_postgis replaces the table, dropping indexes and constraints with it. Use if_exists="append" after the initial load, or recreate the index afterwards.
• PostGIS needs its own tuning. Default work_mem and shared_buffers are sized for a laptop; large spatial joins want more.
• Backups are now your problem. A folder of files is backed up by copying the folder. A database needs pg_dump on a schedule.
• Not every team should run a server. Managed Postgres with PostGIS enabled removes most of the operational objection.

Internal links #

• How to connect GeoPandas to PostGIS — the connection string, drivers and first query
• How to write a GeoDataFrame to PostGIS — loading data in without losing types or CRS
• How to run spatial SQL queries from Python with PostGIS — pushing the work down to the database
• PostGIS write fails on SRID or geometry type — the errors a typed column produces, and why they are useful
• How to load and write PostGIS layers from PyQGIS — the same database from QGIS
• GIS vector file formats compared — what you are moving away from, and what it does well
• Spatial indexes explained: R-trees and why spatial joins are fast — the idea the GIST index implements
• How to process a very large GeoPackage in chunks — the file-based alternative when a server is not an option

FAQ #

Is PostGIS faster than GeoPandas? #

For selection, filtering and joins over large tables, yes — by orders of magnitude, because of the persistent index and because it does not load the data. For row-wise computation on data already in memory, GeoPandas is faster, because there is no network in between.

Do I still need GeoPandas if I have PostGIS? #

Yes. read_postgis returns a GeoDataFrame, and everything you did with it before still applies. The change is that it now receives a filtered result rather than an entire dataset.

Can I use PostGIS without writing SQL? #

Partly. GeoPandas can read a whole table by name and write with to_postgis. But the reason to use a database is to push work into it, and that means SQL — at least WHERE clauses and joins.

Is GeoPackage enough instead? #

Often, yes. It gives you one file, multiple layers, real types, and an R-tree index. What it does not give you is concurrent writers, server-side query planning, or user permissions.

How much data justifies a database? #

Size is the weakest of the five signals. Ten thousand rows edited by three people justifies a database; ten million read-only rows queried by one script may not.

What about the CRS — does PostGIS store it? #

Yes, as an SRID on the geometry column, and it enforces it. geometry(Polygon, 27700) rejects an insert in EPSG:4326 rather than silently accepting it, which is the opposite of how shapefiles behave.

Can PostGIS handle rasters? #

There is a postgis_raster extension, but most teams keep rasters as Cloud-Optimised GeoTIFFs on object storage and reference them from the database. Vector in the database, raster beside it, is the common split.