When should I move from GeoPackage or shapefile to PostGIS?

Move to PostGIS when several people need to edit the same data concurrently, when datasets are too large or numerous for files, when an application backend must query the data, or when you need server-side spatial analysis, indexing, and access control. For single-analyst projects, files are usually enough.

What does PostGIS do that files cannot?

PostGIS gives concurrent multi-user editing with transactions, server-side spatial SQL (ST_Intersects, ST_Buffer, ST_Transform), GiST spatial indexes, true topology and triggers, row-level security, and integration with web and application backends. A file is a passive container; a database is an active query engine.

How do I load files into PostGIS?

Use ogr2ogr, for example: ogr2ogr -f PostgreSQL PG:'dbname=gis' data.gpkg -nln schema.table -lco GEOMETRY_NAME=geom -nlt PROMOTE_TO_MULTI. Reproject on load with -t_srs and create a spatial index afterward.

Can multiple people edit a PostGIS layer at once?

Yes. PostgreSQL provides ACID transactions and row-level locking, so many analysts can edit the same layer from QGIS or an application simultaneously without overwriting each other, unlike a shared file where the last save wins.

When to Move From GIS Files to PostGIS

Short answer

Move spatial data into PostGIS when the data stops being something one analyst opens and starts being something many people and systems must query, edit, and trust at the same time. Files (shapefile, GeoPackage, GeoJSON) are passive containers; PostGIS is an active query engine with concurrency control, server-side spatial SQL, and indexing. The concrete triggers are: concurrent multi-user editing, datasets too large or numerous to manage as files, an application or web backend that needs to query geometry live, and a need for transactions, access control, or auditable history. If you are a single analyst on a bounded project, a GeoPackage is simpler and entirely sufficient — do not stand up a database for it.

What PostGIS actually is

PostGIS is a spatial extension to PostgreSQL. Enabling it (CREATE EXTENSION postgis;) adds geometry and geography column types, hundreds of spatial functions, and GiST spatial indexing to a full relational database. The difference from a file is categorical:

A file stores geometry and attributes. To analyze, you load it into software and the software does the work.
A database stores the data and executes the analysis, server-side, returning only the answer. SELECT ST_Area(geom) FROM parcels WHERE ST_Intersects(geom, :aoi) runs in the engine; the client never downloads the parcels.

That shift — from moving data to clients toward sending queries to data — is the whole point.

The signals that you have outgrown files

1. Concurrent editing

The decisive one. A GeoPackage edited by two people is "last save wins": one analyst silently overwrites the other. PostgreSQL gives ACID transactions and row-level locking, so a whole team can edit the same fault layer or sample table from QGIS simultaneously, each change isolated until committed. For any multi-editor workflow, this alone justifies the move.

2. Scale and number of datasets

Once you have hundreds of layers, or single tables of tens of millions of features, file management becomes the bottleneck — naming, versioning, finding, indexing. A database with schemas, a catalog, and GiST indexes handles this calmly. A spatial query that crawls over a 50 million row shapefile returns in milliseconds against an indexed PostGIS table.

3. An application or web backend

If a web map, dashboard, or API must query geometry live (nearest features, point-in-polygon, dynamic buffers), it needs a database behind it. Serving from flat files means re-reading and re-parsing on every request; PostGIS answers with one indexed SQL statement and integrates directly with backends and tile servers (e.g. pg_tileserv, Martin).

4. Server-side analysis and integrity

PostGIS runs the full spatial toolkit in SQL — ST_Intersects, ST_Buffer, ST_Union, ST_Transform, ST_DWithin, topology, clustering — and lets you enforce rules with constraints, triggers, and the topology extension so geometry stays valid as it is edited. Files cannot enforce anything; they just store whatever you wrote.

5. Access control and history

Row-level security, per-role grants, and audit triggers give controlled, attributable access. A shared file on a drive offers none of this.

Worked example: load, index, query

Load a GeoPackage layer into PostGIS, reprojecting to a national grid, then query it.

# Load and reproject in one step
ogr2ogr -f PostgreSQL \
  PG:"host=db dbname=gis user=gis" \
  boreholes.gpkg boreholes \
  -nln field.boreholes \
  -t_srs EPSG:25832 \
  -lco GEOMETRY_NAME=geom \
  -lco FID=id \
  -nlt PROMOTE_TO_MULTI

-t_srs EPSG:25832 reprojects to ETRS89 / UTM 32N on load, so everything in the schema shares a CRS.
-lco GEOMETRY_NAME=geom names the geometry column predictably.
-nlt PROMOTE_TO_MULTI avoids single/multi mismatch errors.

Then create the spatial index (ogr2ogr usually does, but confirm) and run analysis server-side:

CREATE INDEX boreholes_geom_idx ON field.boreholes USING GIST (geom);
ANALYZE field.boreholes;

-- All boreholes within 500 m of a fault, server-side
SELECT b.id, b.depth_m
FROM field.boreholes b
JOIN structure.faults f
  ON ST_DWithin(b.geom, f.geom, 500)   -- metres, because CRS is metric
GROUP BY b.id, b.depth_m;

Because the CRS is projected and metric, ST_DWithin(..., 500) means 500 metres directly. (In a geographic CRS you would cast to geography or transform first, or the "500" would be degrees.) Always confirm the SRID with SELECT Find_SRID('field','boreholes','geom'); before trusting distance or area results.

When files are still the right answer

Single-analyst, bounded projects — a GeoPackage is simpler, portable, and needs no server.
Final deliverables and archives — clients want a file, not database credentials. Export from PostGIS with ogr2ogr -f GPKG.
Web exchange of small datasets — GeoJSON over an API.

PostGIS and files are not rivals; the database is the system of record, files are how data enters and leaves it.

Common pitfalls and why they happen

Standing up a database for a one-person task. The operational overhead (backups, roles, connection management) outweighs the benefit unless concurrency, scale, or a backend is genuinely in play.
Forgetting the spatial index. Without a GiST index every spatial query is a full table scan; people then conclude "PostGIS is slow" when they simply never indexed.
Mixed SRIDs. Loading layers in different CRSs and then running ST_Intersects across them returns nothing or errors. Standardize on one projected CRS at load with -t_srs.
Treating geography vs geometry casually. Distance/area on geometry are in the CRS units; on geography they are in metres on the ellipsoid. Choosing the wrong type gives answers off by orders of magnitude.
No backup discipline. A file you can copy; a database you must back up (pg_dump). Skipping this turns the system of record into a single point of failure.

QA checklist

Confirm the geometry column SRID and that all layers in a schema share one projected CRS.
Ensure a GiST spatial index exists and statistics are current (ANALYZE).
Validate geometries on load (ST_IsValid, repair with ST_MakeValid).
Set roles and grants so editing is controlled and attributable.
Establish pg_dump backups and test a restore before relying on the database.

Bathyl perspective

We move data into PostGIS at the moment it becomes shared infrastructure — many editors, live application queries, or analysis that should run once on the server rather than repeatedly on each laptop. The database becomes the single CRS-consistent, indexed, backed-up system of record, and files become the controlled way data arrives and ships. For a lone analyst on a finite job, we are just as happy to stay in a GeoPackage; the tool follows the workflow, not the other way around.

When to Use PostGIS Instead of Files