GeoPackage vs PostGIS as a Processing Backend

TL;DR: GeoPackage is a single-file, zero-setup store that is ideal for portable batch jobs and CI fixtures; PostGIS is a concurrent, multi-writer database with server-side spatial SQL that wins for shared or large datasets and parallel workers. Choose on three axes: concurrency, data size, and deployment constraints.

This page is part of the Standalone PyQGIS Scripts and Headless Execution guide, which sits inside the broader Headless Automation, CI/CD & Testing for PyQGIS reference. Picking the right processing backend for an automated pipeline is one of the earliest and most consequential decisions you make: it dictates whether your workers can scale out, whether filtering runs in the database or in Python, and whether the output is a file you can copy or a service you must provision.

The Decision in One View #

Both backends speak the same PyQGIS API through QgsVectorLayer, so the code that iterates features looks almost identical. The differences are operational: how many processes can write at once, how large the data can grow, how much a transaction costs, and how easily the result travels. The matrix below scores each backend across the criteria that actually decide the choice for a headless job.

Opening the Same Layer from Each Backend #

In PyQGIS the backend is chosen by the provider key and the data source string. A GeoPackage layer is opened through the ogr provider with a filesystem path; a PostGIS layer is opened through the postgres provider with a connection URI, which you should assemble with QgsDataSourceUri rather than hand-concatenating strings. The runnable script below opens the same logical layer (a parcels table) from both backends and applies an identical bounding-box filter so you can compare push-down behaviour directly.

"""
backend_open.py

Open the same 'parcels' layer from a GeoPackage and from PostGIS,
then apply an identical spatial filter to each. Runnable headless.
Compatible with QGIS 3.28+ LTR.
"""
from __future__ import annotations

from qgis.core import (
    QgsApplication,
    QgsDataSourceUri,
    QgsFeatureRequest,
    QgsRectangle,
    QgsVectorLayer,
)

# 1. Bootstrap a headless QGIS application.
QgsApplication.setPrefixPath("/usr", True)
qgs = QgsApplication([], False)
qgs.initQgis()


def open_geopackage(path: str, table: str) -> QgsVectorLayer:
    """Open one table of a GeoPackage via the ogr provider.

    The '|layername=' fragment selects a single table inside the
    multi-layer container; without it ogr returns the first layer.
    """
    uri = f"{path}|layername={table}"
    layer = QgsVectorLayer(uri, table, "ogr")
    if not layer.isValid():
        raise RuntimeError(f"GeoPackage layer failed to load: {uri}")
    return layer


def open_postgis(table: str, geom_col: str = "geom") -> QgsVectorLayer:
    """Open a PostGIS table via the postgres provider.

    QgsDataSourceUri escapes connection parameters correctly and keeps
    credentials out of the layer name shown in logs.
    """
    uri = QgsDataSourceUri()
    uri.setConnection("db.internal", "5432", "gisdata", "etl_worker", "secret")
    uri.setDataSource("public", table, geom_col, aKeyColumn="fid")
    layer = QgsVectorLayer(uri.uri(False), table, "postgres")
    if not layer.isValid():
        raise RuntimeError(f"PostGIS layer failed to load: {table}")
    return layer


def count_in_bbox(layer: QgsVectorLayer, bbox: QgsRectangle) -> int:
    """Count features intersecting a bounding box.

    setFilterRect is pushed to the backend spatial index by both the
    ogr (GeoPackage R*Tree) and postgres (GiST) providers, so only
    candidate features are streamed back into Python.
    """
    request = QgsFeatureRequest().setFilterRect(bbox)
    return sum(1 for _ in layer.getFeatures(request))


if __name__ == "__main__":
    aoi = QgsRectangle(392000, 5819000, 394000, 5821000)  # UTM 32N metres

    gpkg = open_geopackage("/data/cadastre.gpkg", "parcels")
    pg = open_postgis("parcels")

    print("GeoPackage parcels in AOI:", count_in_bbox(gpkg, aoi))
    print("PostGIS    parcels in AOI:", count_in_bbox(pg, aoi))

    qgs.exitQgis()

The key line to notice is setFilterRect. Both providers translate a bounding-box request into a spatial-index lookup at the source, so QGIS never materialises the full table in Python. Where the two diverge is in what happens once you move beyond a bounding box to attribute or geometric predicates in a setFilterExpression, which is the subject of the next section.

Architecture Breakdown #

Provider push-down: how filters reach the index #

A QgsFeatureRequest carries two independent filters that the data provider may or may not evaluate at the source. Understanding which one is pushed down is the difference between a query that touches a few hundred rows and one that streams millions into Python only to discard most of them. For the mechanics of spatial indexes themselves, see Spatial Indexing and Query Optimization in PyQGIS.

setFilterRect(rect) is the well-behaved case. The ogr GeoPackage provider maps it onto the container’s rtree_<table>_<geom> R*Tree virtual table that QGIS creates when a spatial index exists, and the postgres provider maps it onto a GiST index over the geometry column via a ST_Intersects bounding-box predicate. In both cases only candidate features cross the provider boundary, and the difference between the two backends here is marginal for a well-indexed table.

setFilterExpression(expr) is where the backends and your expression text interact. The provider attempts to compile the QGIS expression into native SQL. Simple attribute comparisons such as "landuse" = 'residential' and "area_m2" > 5000 compile cleanly for both ogr and postgres, so the WHERE clause runs in the engine. But many spatial and QGIS-specific functions have no SQL equivalent the provider can emit — when compilation fails, QGIS falls back to fetching every feature and evaluating the expression row by row in Python. PostGIS compiles a wider range of expressions than the SQLite backend because PostGIS exposes a rich native spatial SQL surface, so an expression that pushes down against PostGIS may silently fall back to full iteration against a GeoPackage. Always confirm what actually happens rather than assuming:

from qgis.core import QgsFeatureRequest

request = QgsFeatureRequest().setFilterExpression('"landuse" = \'residential\'')
# compilationFailed() is True when the provider could not translate the
# expression to native SQL and QGIS is iterating in Python instead.
_ = list(layer.getFeatures(request))
print("fell back to Python evaluation:", request.compilationFailed())

When compilation fails on a large layer, either simplify the expression to attribute predicates the provider can push down, or — with PostGIS — move the heavy work into a server-side query and open the result as a layer.

Concurrency and locking #

GeoPackage inherits SQLite’s locking model: a single database-level write lock. Concurrent readers coexist happily, and with write-ahead logging (WAL) a reader can proceed while one writer is active, but two processes cannot commit writes to the same file at once. A second writer generally fails with database is locked after the busy timeout expires. For a headless pipeline this means a GeoPackage suits a single-writer topology — one process that reads inputs and writes outputs sequentially.

PostGIS uses multi-version concurrency control, so many workers insert, update, and delete concurrently with row-level locking and no table-wide stall. This is what makes it the right choice when you fan a job out across parallel workers that all write to a shared table. The trade-off is transaction cost: every commit is a network round-trip, so batch many features per transaction rather than committing per feature. If your parallelism is really about reading a shared dataset while each worker writes to its own output, a shared read-only GeoPackage plus per-worker output files is often simpler than standing up a database.

Portability and CI fixtures #

Portability is where GeoPackage is decisively ahead. The entire dataset — geometries, attributes, spatial indexes, styles, and metadata — is one OGC-standard file you can copy, check into a Git LFS store, attach to a bug report, or mount into a container. That makes it the natural fixture format for automated tests: a small .gpkg committed alongside the test suite gives every run a deterministic, offline input with zero service dependencies. PostGIS in CI means spinning up a database service container, waiting for it to become healthy, loading a schema, and tearing it down — worthwhile when you are specifically testing PostGIS-provider code paths, but overhead you should avoid when a file fixture would prove the same behaviour. A pragmatic pattern is to develop and test against a GeoPackage fixture and reserve a PostGIS service container for the subset of integration tests that exercise concurrency or server-side SQL.

Best Practices #

• Default to GeoPackage, escalate to PostGIS on a concrete need. Single-writer, portable, sub-tens-of-gigabytes jobs rarely justify a server. Move to PostGIS when you hit concurrent writers, tens of millions of rows, or a genuinely shared dataset.
• Build PostGIS connections with QgsDataSourceUri, never string concatenation. It escapes parameters, keeps credentials out of layer names in logs, and lets you toggle SSL and the key column cleanly.
• Always create a spatial index. A GeoPackage without its R*Tree and a PostGIS table without a GiST index both force full scans, and setFilterRect push-down silently degrades to a sequential read.
• Check request.compilationFailed() on hot paths. It is the definitive signal that a setFilterExpression fell back to per-feature Python evaluation instead of running as native SQL.
• Batch writes. Wrap many features in one edit/commit for a GeoPackage to reduce file-lock churn, and one transaction for PostGIS to amortise network round-trips.
• Keep one writer per GeoPackage. For parallel-writer pipelines choose PostGIS, or give each worker a private GeoPackage and merge outputs at the end.
• Use a GeoPackage fixture in CI for deterministic, offline tests, and reserve a PostGIS service container for the integration tests that specifically need concurrency or server-side spatial SQL.

Return to the parent guide, Standalone PyQGIS Scripts and Headless Execution, for the surrounding bootstrap and execution patterns these backends plug into.

Related

• Standalone PyQGIS Scripts and Headless Execution — parent guide covering QgsApplication bootstrap and the headless execution model these backends run inside
• Optimizing Feature Iteration with QgsVectorLayer.getFeatures — how QgsFeatureRequest, subset-of-attributes, and geometry flags shape what each provider returns
• Vector and Raster Data Access Patterns in PyQGIS — broader reference on provider abstractions and reading strategies across data sources
• Spatial Indexing and Query Optimization in PyQGIS — how R*Tree and GiST indexes accelerate the bounding-box push-down described above