PostGIS and Database Workflows in PyQGIS

There is a point in every automation project where files stop being enough. Two people need to edit the same parcels; a nightly job has to append yesterday's readings without rewriting a hundred-megabyte shapefile; an analyst wants the answer to a question that is three joins and a spatial index away. That is the point at which the data moves into a database — usually PostGIS, sometimes a GeoPackage — and PyQGIS becomes the thing that talks to it.

This guide sits inside Spatial Data Processing & Automation and covers the database side of that section. It is the counterpart to the file-based workflows described in Vector Data Manipulation in PyQGIS: the same operations, but with a connection string instead of a path, a primary key instead of an implicit row order, and a transaction instead of a save button. If you have not yet met the Processing framework, read Batch Processing with PyQGIS first — almost every algorithm in it accepts a database layer wherever it accepts a file.

What a database changes about your code

The API you already know does not change. A PostGIS table loaded into a QgsVectorLayer iterates with getFeatures(), feeds processing.run(), renders with a symbol, and edits with startEditing() exactly as a shapefile does. What changes is everything the file format let you ignore.

Identity becomes explicit. A shapefile numbers its rows; PostGIS does not promise any order at all. QGIS needs a column that uniquely identifies a row so it can re-read, update and delete individual features. Give it one — an integer primary key is ideal — or you will meet the "layer is read-only" and "feature not found" failures that account for most first-day frustration.

Filtering moves to the server. When you set a subset string or a Processing algorithm asks for features in a rectangle, the postgres provider translates that into a WHERE clause. The database evaluates it against its spatial index and returns only matching rows. A file-based layer, by contrast, is filtered in QGIS after reading. On a table of ten million points this is the difference between a query that returns in milliseconds and a script that appears to hang.

Writes become transactional. Adding features to a GeoPackage or a PostGIS table happens inside a transaction that either commits entirely or rolls back entirely. That is a guarantee no shapefile can offer, and it is the reason a database is the right home for anything a scheduled job writes to unattended.

Credentials become a problem to solve. A path needs no password. A connection does, and hard-coding one in a script that ends up in version control is the single most common security mistake in GIS automation. QGIS's authentication database exists precisely so the script can refer to a stored credential by id.

Building a connection

Everything about a PostGIS layer is encoded in a data source URI. QgsDataSourceUri builds one correctly, including the quoting rules that trip up hand-assembled strings.

from qgis.core import QgsDataSourceUri, QgsVectorLayer

uri = QgsDataSourceUri()
uri.setConnection("db.example.org", "5432", "cityworks", "gis_reader", "")
uri.setAuthConfigId("a1b2c3d")               # stored credential, never a literal password
uri.setDataSource("public", "parcels", "geom", "", "gid")

layer = QgsVectorLayer(uri.uri(False), "Parcels", "postgres")
print(layer.isValid(), layer.featureCount())

Breakdown: setConnection() takes host, port, database, user and password as strings — leave the password empty and supply setAuthConfigId() instead, so the secret lives in QGIS's encrypted authentication database rather than in the file. setDataSource() takes schema, table, geometry column, an optional SQL filter and the key column; naming the key column explicitly is what makes the layer writable. uri.uri(False) renders the string with credentials expanded but omits the password when an auth config is in play. Checking isValid() immediately is not optional — an invalid layer fails silently later, often thousands of features into a loop.

The full walkthrough, including how to create the auth entry and how to reuse connections already configured in the QGIS browser, is in Connect to a PostGIS Database in PyQGIS.

Layers that are really queries

A PostGIS layer does not have to be a table. Any SQL statement that returns a geometry column and a unique identifier can back a layer, which means the whole expressive power of SQL — joins, window functions, aggregates, ST_ functions QGIS has no algorithm for — is available as a normal layer you can style, label and export.

sql = """(
    SELECT p.gid,
           p.geom,
           p.parcel_ref,
           count(t.id) AS tree_count
    FROM public.parcels p
    LEFT JOIN public.trees t ON ST_Contains(p.geom, t.geom)
    GROUP BY p.gid, p.geom, p.parcel_ref
)"""

uri.setDataSource("", sql, "geom", "", "gid")
trees_per_parcel = QgsVectorLayer(uri.uri(False), "Trees per parcel", "postgres")

Breakdown: The schema argument is empty and the table argument is a parenthesised subquery — that is how the provider distinguishes a query layer from a table. The gid passed as the key column must be unique across the result, which is why it appears in the GROUP BY. The aggregate runs on the server against PostGIS's GiST index, so a spatial count that would take minutes as a per-feature Python loop returns as fast as the database can scan. Load a PostGIS Query Layer in PyQGIS covers the subtleties: declaring the geometry type when the planner cannot infer it, avoiding the full-table scan QGIS runs to guess the extent, and when a materialised view beats a query layer.

For statements that return no geometry at all — creating a table, running VACUUM, updating rows in bulk — use the provider connection API rather than a layer:

from qgis.core import QgsProviderRegistry

metadata = QgsProviderRegistry.instance().providerMetadata("postgres")
connection = metadata.createConnection("cityworks")     # a saved browser connection
rows = connection.executeSql("SELECT postgis_version()")
print(rows[0][0])

Breakdown: createConnection() with a name resolves a connection already saved in the QGIS browser, so the script inherits credentials the user configured interactively. The returned object exposes tables(), schemas(), createVectorTable(), dropVectorTable() and executeSql() — a small, provider-neutral database API that works identically against GeoPackage and SpatiaLite. Anything returning rows comes back as a list of lists.

Writing results back

Analysis that ends in a temporary layer helps nobody. Getting results into durable storage is where the database repays the setup cost, and PyQGIS offers three routes depending on what you are writing.

For a whole layer at once — the output of a Processing chain, a reprojected copy, a nightly extract — QgsVectorFileWriter writes to GeoPackage and QgsVectorLayerExporter writes to PostGIS:

from qgis.core import QgsVectorFileWriter, QgsProject, QgsCoordinateTransformContext

options = QgsVectorFileWriter.SaveVectorOptions()
options.driverName = "GPKG"
options.layerName = "flood_zones"
options.actionOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer

error, message, _, _ = QgsVectorFileWriter.writeAsVectorFormatV3(
    result_layer,
    "/data/outputs/analysis.gpkg",
    QgsCoordinateTransformContext(),
    options,
)
if error != QgsVectorFileWriter.NoError:
    raise RuntimeError(message)

Breakdown: CreateOrOverwriteLayer replaces one layer inside the GeoPackage and leaves the others alone — CreateOrOverwriteFile would delete the whole container, which is a genuinely destructive mistake to make in a nightly job. Checking the returned error code matters because the writer reports failure through its return value rather than by raising. Write a Vector Layer to GeoPackage in PyQGIS works through the options object, multi-layer containers, and why a GeoPackage is a better default than a shapefile for every output you produce.

For incremental writes — appending today's rows to a table that already exists — go through the provider inside a transaction, which is covered in Append Features to a PostGIS Table in PyQGIS.

Keeping automated database work safe

Unattended scripts touching shared data need a discipline that interactive work can get away with skipping.

Never write from the script that reads. Run analysis against a read-only role and write through a role that only has rights to the output schema. A misdirected UPDATE in a scheduled job is much cheaper to prevent than to reverse.

Wrap multi-step writes in one transaction. If you delete yesterday's rows and insert today's, do both or neither. Between them the table is empty, and a job that dies in that window leaves the map blank for everyone.

Set a statement timeout. A query that would normally take two seconds can take two hours after a plan flips, and the scheduled job that waits for it silently holds a connection all night.

Log the row counts. Every scheduled write should report how many features it read, how many it wrote and how long it took. When a source feed quietly breaks, a count that drops from 40 000 to 12 is the signal that finds it — see the logging patterns in Headless QGIS and Server Automation.

Reading efficiently once the table is large

A database layer behaves like any other layer, which is exactly what makes it easy to use badly. The provider will happily stream ten million rows into a Python loop if you ask it to, and the request you send decides whether the work happens on the server or on your laptop.

The single most valuable habit is to attach a subset string to the layer rather than filtering in Python:

layer.setSubsetString("survey_date >= current_date - 30 AND status = 'active'")
print(layer.featureCount())

Breakdown: The subset string is appended to the provider's WHERE clause, so PostgreSQL evaluates it against its indexes and featureCount() returns the filtered number without a single feature crossing the network. The expression is SQL — not QGIS expression syntax — because it is passed through to the database, which is a genuine difference from the expression engine described in Working with QGIS Expressions. Clearing it is setSubsetString(""), and forgetting to clear it is a common source of "half my data disappeared".

For a per-feature loop, narrow the request as well:

from qgis.core import QgsFeatureRequest

request = (
    QgsFeatureRequest()
    .setSubsetOfAttributes(["parcel_ref", "area_m2"], layer.fields())
    .setFilterRect(canvas_extent)
)

for feature in layer.getFeatures(request):
    process(feature)

Breakdown: The attribute subset becomes a narrower SELECT, and the rectangle becomes an ST_Intersects against the GiST index — both evaluated by PostGIS. On a wide table this routinely turns a minute into a second, and it is the same lever covered in detail in Speed Up Feature Iteration with QgsFeatureRequest. Two things that do not get pushed down are worth knowing: a QGIS expression using functions PostgreSQL has no equivalent for, and any ordering the provider cannot express — both quietly fall back to evaluation inside QGIS.

Finally, remember that the database has its own diagnostics. connection.executeSql("EXPLAIN ANALYZE " + statement) returns PostgreSQL's own plan, which answers "why is this slow" far more directly than timing the Python around it.

Moving a file-based workflow into the database

Most projects arrive at PostGIS from a folder of shapefiles, and the migration is more about discipline than code. Four decisions do most of the work.

Load once, with the types you want. QgsVectorLayerExporter.exportLayer() creates the table and copies the features in one call, inferring column types from the source. Where the source is a shapefile, that inference carries the shapefile's limitations — dates as strings, everything truncated to ten characters — so create the table explicitly first when the schema matters:

from qgis.core import QgsVectorLayerExporter, QgsCoordinateReferenceSystem

error, message = QgsVectorLayerExporter.exportLayer(
    source_layer,
    uri.uri(False),
    "postgres",
    QgsCoordinateReferenceSystem("EPSG:27700"),
    False,                       # onlySelected
    {"overwrite": True},
)
if error != QgsVectorLayerExporter.NoError:
    raise RuntimeError(message)

Breakdown: The options dictionary passes provider-specific flags; overwrite replaces an existing table, and its absence makes the export fail rather than silently append. Naming the CRS explicitly sets the geometry column's SRID constraint, which is what later rejects mis-projected inserts instead of storing them. The error code is returned rather than raised, following the same pattern as the file writer.

Add the constraints the files never had. A primary key, a NOT NULL on the columns that matter, a unique constraint on the natural key, and a spatial index. Each one converts a silent data problem into an immediate, specific error.

Decide what is authoritative. A pipeline that reads shapefiles and writes PostGIS every night has two copies of the truth and will eventually disagree with itself. Pick one, and make the other a derived export.

Keep the outputs in a separate schema from the inputs, with different roles for reading and writing. That single arrangement prevents an entire category of accident, and costs one CREATE SCHEMA.

GeoPackage as the small end of the same idea

Not every project needs a server. A GeoPackage is a single SQLite file with the same fundamentals — tables, indexes, transactions, SQL — and PyQGIS reaches it through the ogr provider with a path|layername=name data source. It holds many layers, stores styles, survives being emailed, and does not corrupt the way a shapefile does when a column name exceeds ten characters or a value contains a non-ASCII character.

Use a GeoPackage when one machine writes at a time, and PostGIS when several people or processes write concurrently. The migration path between them is short: the same QgsVectorLayer, the same algorithms, and one changed line where the data source is built. That is the practical reason to write file-based automation against the database-shaped API from the beginning — see Automating Shapefile to GeoJSON Conversion for the equivalent format-conversion patterns.

Key takeaways

Name the key column. A PostGIS layer without a unique integer identifier loads read-only and misbehaves on re-reads. This single omission causes most beginner failures.
Let the server filter. Subset strings and bounding-box requests are pushed down to PostGIS and answered by its index; filtering in Python after reading throws away the entire benefit of the database.
Keep secrets in the authentication database. setAuthConfigId() exists so no script ever needs a literal password.
A query can be a layer. Any SQL returning geometry and a unique id backs a stylable, exportable layer — often replacing an entire Processing chain.
Write transactionally. Delete-then-insert belongs inside one transaction so a failure never leaves the table empty.
GeoPackage first, PostGIS when shared. The API is the same; only the concurrency guarantees differ.

Frequently Asked Questions

Why is my PostGIS layer read-only? Almost always because QGIS could not identify a unique key column. Pass the primary key explicitly as the last argument to setDataSource(). For query layers, make sure the column you nominate really is unique across the result — a join that duplicates rows silently breaks identity even when the column is a primary key in its own table.

Should I use psycopg2 instead of the postgres provider? For pure SQL with no map involved, a direct driver is perfectly reasonable, but it adds a dependency QGIS does not ship and duplicates connection handling. The provider connection API — QgsProviderRegistry.instance().providerMetadata("postgres").createConnection(name) — gives you executeSql() using the credentials the user already configured, which is usually the better trade in a plugin.

How do I avoid storing a password in my script? Create an authentication configuration in QGIS (Settings → Options → Authentication), note its id, and call uri.setAuthConfigId(id). The password is stored encrypted in the QGIS authentication database and never appears in your source. For servers, an environment variable read at startup, or a PostgreSQL service file, are the usual alternatives.

Is GeoPackage fast enough for large datasets? For a single reader or writer, yes — SQLite handles tens of millions of rows with a spatial index. It falls down on concurrency: writers take an exclusive lock, so two scheduled jobs writing at once will block each other. That constraint, rather than raw size, is what pushes a project to PostGIS.

Can Processing algorithms write straight into PostGIS? Yes. Pass a postgres: output URI where the algorithm expects an OUTPUT, or run the algorithm to a temporary layer and export it with QgsVectorLayerExporter. The second form is easier to reason about because the write becomes an explicit, loggable step in your script.

Why is loading my query layer so slow before anything renders? QGIS runs the query once to determine the extent and geometry type. Declare both in the URI — set the geometry type and SRID when building the data source — so it can skip that probe, or back the query with a materialised view that carries proper statistics.