Short answer

A GeoPackage (.gpkg) is a single SQLite file that bundles vector tables, raster tiles, and metadata into one portable, OGC-standard container. PostGIS is a spatial extension to the PostgreSQL server that turns a database into a concurrent, indexed, query-driven store of geometry. Choose a GeoPackage when one analyst needs a portable, self-contained dataset; choose PostGIS when several people or services must read and write the same data at once, the volume grows past a few hundred megabytes, or you need transactions, access control, and SQL-driven processing.

They are not really competitors. A common production pattern is to keep the authoritative data in PostGIS and export task-scoped GeoPackages for field crews, clients, or offline QGIS sessions.

What each format actually is

A GeoPackage is defined by the OGC GeoPackage Encoding Standard. Under the hood it is an ordinary SQLite 3 database with a fixed set of metadata tables (gpkg_contents, gpkg_geometry_columns, gpkg_spatial_ref_sys) and a binary geometry encoding (a small GPKG header followed by standard WKB). Because it is one file, you can email it, drop it on a USB stick, or commit it to a release artifact. SQLite supports an R-tree index for spatial queries, and GDAL/QGIS create one automatically for feature tables.

PostGIS is a different animal. It is an extension you enable inside a PostgreSQL database with CREATE EXTENSION postgis;. It adds two geometric column types — geometry (planar, fast) and geography (computes on the spheroid, great for global distance) — plus the spatial_ref_sys table seeded from the EPSG registry, GiST spatial indexing, and several hundred ST_* functions for overlay, measurement, clustering, and topology. PostgreSQL gives you everything PostGIS sits on: MVCC concurrency, ACID transactions, roles and row-level security, foreign keys, triggers, materialized views, and connection pooling.

The deciding axis is the runtime model. A GeoPackage is a file your process opens directly; PostGIS is a server your processes connect to over a socket or TCP.

Concurrency: the line that usually decides it

SQLite, and therefore GeoPackage, uses file-level locking. One writer takes an exclusive lock on the whole file; readers are blocked while a write transaction is open. Enabling WAL mode (PRAGMA journal_mode=WAL) lets readers proceed during a write, but you still cannot have two simultaneous writers. Worse, putting a GeoPackage on an NFS or SMB network share is a known corruption risk because SQLite's locking assumptions break over many network filesystems. So a .gpkg is excellent for one editor, fragile for a team.

PostGIS inherits PostgreSQL's MVCC model: many writers and readers operate concurrently, each transaction sees a consistent snapshot, and conflicts are resolved at row granularity. If your editing layer is touched by three field-data importers and a web app at the same time, that is a PostGIS job, not a GeoPackage job.

Worked example: load, index, reproject

Suppose you have faults.gpkg (layer faults, EPSG:4326) and you want it in PostGIS reprojected to UTM 31N (EPSG:32631) for metric buffering.

Load and reproject in one step with ogr2ogr:

ogr2ogr -f PostgreSQL \
  PG:"host=db user=gis dbname=geo password=secret" \
  faults.gpkg faults \
  -nln faults_utm \
  -t_srs EPSG:32631 \
  -lco GEOMETRY_NAME=geom \
  -lco FID=fid \
  -lco SPATIAL_INDEX=GIST \
  -nlt PROMOTE_TO_MULTI

-t_srs does the actual coordinate transformation; -lco SPATIAL_INDEX=GIST builds the index on import; -nlt PROMOTE_TO_MULTI avoids mixed single/multi geometry errors. Then buffer 250 m and find faults near a target:

SELECT f.id
FROM faults_utm f
JOIN target t
  ON ST_DWithin(f.geom, t.geom, 250);

ST_DWithin uses the GiST index, so it stays fast on large tables, where a naive ST_Distance(...) < 250 would scan everything. Exporting a task subset back to a GeoPackage for a field tablet is the reverse:

ogr2ogr -f GPKG site_pack.gpkg \
  PG:"host=db user=gis dbname=geo" \
  -sql "SELECT * FROM faults_utm WHERE ST_Intersects(geom, ST_MakeEnvelope(...))"

SRID, ST_SetSRID, and ST_Transform

This is the single most common silent-error source when moving data into PostGIS. A SRID is just an integer key (usually the EPSG code) into spatial_ref_sys. Two distinct operations get confused:

  • ST_SetSRID(geom, 4326) relabels the geometry's SRID without moving a single coordinate. Use it only to repair metadata when coordinates are already in EPSG:4326 but the column says SRID 0.
  • ST_Transform(geom, 32631) reprojects, computing new coordinates from the geometry's current declared SRID to the target.

If your data is genuinely in lat/long but PostGIS thinks it is SRID 0, you must ST_SetSRID(geom, 4326) first, then ST_Transform(geom, 32631). Calling ST_SetSRID(geom, 32631) on lat/long values just stamps a lie: coordinates near (2.3, 48.8) get treated as if they were eastings/northings in metres, and every subsequent measurement is nonsense even though no error is raised. Enforce correctness with a typmod or constraint, e.g. geometry(MultiLineString, 32631), so the column rejects mislabelled inserts.

Performance and scale

For datasets up to a few hundred thousand features and a few hundred MB, a well-indexed GeoPackage in QGIS is genuinely fast and simple. Past that, PostGIS pulls ahead because of mature query planning, ANALYZE-driven statistics, partial and functional indexes, parallel query, and the ability to keep hot data in shared buffers. PostGIS also supports server-side raster (postgis_raster), partitioning, and out-database raster references, which a single SQLite file does not handle gracefully. If your "database" file is creeping toward several GB and queries are slowing, that is the migration signal.

Common pitfalls and why they happen

  • Editing a GeoPackage on a network share. It seems to work until two people save at once; SQLite's locking model was never designed for shared filesystems, and the file can corrupt. Keep .gpkg editing local.
  • Using ST_SetSRID to "fix" misplaced data. It hides the problem because no coordinates change and no error appears — the map just sits in the ocean or measures wrong. Diagnose with SELECT ST_SRID(geom), ST_Extent(geom) FROM t; before deciding.
  • No spatial index in PostGIS. A forgotten CREATE INDEX ... USING GIST (geom); turns ST_Intersects joins into full scans. Always index and then ANALYZE.
  • Treating PostGIS as a file store. Loading hundreds of unrelated layers into one schema with no naming or CRS convention recreates the mess people moved away from.
  • Assuming ogr2ogr exit 0 means correctness. It reports success even when geometry was promoted, truncated, or reprojected unexpectedly; verify counts and extent afterwards.

QA and validation

After any load or transform, run a short check before trusting the table:

SELECT count(*),
       ST_SRID(geom) AS srid,
       GeometryType(geom) AS gtype,
       bool_and(ST_IsValid(geom)) AS all_valid
FROM faults_utm
GROUP BY ST_SRID(geom), GeometryType(geom);

Confirm the row count matches the source, the SRID is what you intended, geometry type is consistent, and everything is valid. Repair with ST_MakeValid(geom) where needed. Compare ST_Extent(geom) against a known bounding box for the project area to catch a wrong-SRID load instantly.

Bathyl perspective

In Bathyl projects the rule of thumb is "authoritative store in PostGIS, distributable copies in GeoPackage." The database holds the version everyone queries and edits with transactions and access control; GeoPackages are cut from it for offline field work, client delivery, and reproducible snapshots. The choice is driven by who needs to write, how much data there is, and whether the result must be auditable — not by format preference.

Related reading

Sources