DuckDB or PostGIS: Which One a Workflow Needs #

Problem statement #

Both run spatial SQL. Both read and write the formats you care about. Both are open source and well maintained. And they are not alternatives — they answer different questions, and a project that picks one because it picked one is usually paying for it somewhere.

The distinction is not size. It is shape:

• PostGIS is a system of record: many clients, concurrent writers, transactions, roles, indexes tuned for fetching individual features.
• DuckDB is an analysis engine: one process, one writer, columnar storage, and a query planner built for scanning millions of rows.

Two measurements make the difference concrete. Fetching one row from an indexed 5,226,942-row table took 13.4 ms in DuckDB and 0.058 ms in SQLite — a row-store shape DuckDB is bad at. Joining 13,464,017 points to 4,596 polygons took 61.8 s and 305 MB in DuckDB — an analytical shape it is very good at.

Quick answer #

Ask what the workload does, not how big it is:

def which_engine(*, concurrent_writers, fetch_single_features, row_count,
                 shared_by_many_users, data_lives_in_files):
    if concurrent_writers or shared_by_many_users:
        return "PostGIS"                     # a system of record
    if fetch_single_features:
        return "PostGIS or SQLite/GeoPackage"  # row-store access pattern
    if data_lives_in_files and row_count > 500_000:
        return "DuckDB"                      # analysis over files
    if row_count < 200_000:
        return "GeoPandas"                   # neither is needed
    return "DuckDB"

Many projects want both: PostGIS holds the data, and DuckDB reads a Parquet export of it to answer analytical questions without loading the server.

Step-by-step solution #

1. Count the writers #

This is the first and most decisive question. PostGIS supports many concurrent writers with transactions, isolation and constraints. DuckDB supports one writer, and a second process opening the same database file for writing fails.

If more than one thing writes — an application, a scheduled loader, an editor — the answer is PostGIS and the rest of this article is optional.

2. Look at the access pattern, not the row count #

A table with 100 million rows queried as select … where id = ? is a transactional workload. A table with 2 million rows scanned in full for an aggregate is an analytical one.

The measured gap is stark: an indexed single-row lookup took 13.4 ms in DuckDB against 0.058 ms in SQLite, because reconstructing one row from columnar storage means visiting every column. PostGIS, being row-oriented with B-tree and GiST indexes, is in the same class as SQLite here.

Conversely, a full-table aggregate over 13.5 million rows completed in DuckDB in 61.8 s using 305 MB, streaming rather than materialising.

3. Ask where the data actually lives #

If the authoritative copy is a database, PostGIS is already the right place to query it. If it is a folder of Parquet, GeoPackage or shapefiles — which is the normal state of analytical GIS work — then loading it into a server before querying is an import step, a synchronisation problem and a second copy.

DuckDB reads those files in place. There is no import, so there is nothing to be stale.

4. Compare the operational cost honestly #

PostGIS needs a server: install, configure, back up, secure, upgrade, monitor. That is a real cost and it buys real things — durability, concurrency, access control, a query planner with statistics.

DuckDB needs pip install duckdb. That is the whole operational story, and it buys none of those things.

For a shared production database the server is obviously worth it. For one analyst answering one question about a Parquet file, it is not.

5. Use both where that is the honest answer #

The common architecture:

PostGIS               the system of record: writes, constraints, the API's queries
   ↓ nightly export
GeoParquet on disk    an analytical snapshot
   ↓ read in place
DuckDB                the analysis: joins, aggregates, ad-hoc questions

The export decouples the analyst from the production database — no long-running analytical query competing with the application, no risk of a mistake in a transaction.

6. Know what DuckDB does not have #

• No roles or row-level security. Access control is filesystem permissions.
• No concurrent writers.
• No replication or point-in-time recovery.
• No stored procedures, triggers or foreign keys in the sense a system of record needs.
• A smaller spatial function set than PostGIS, and some functions behave differently — ST_Transform needs always_xy := true and drops the CRS, and ST_Distance_Sphere takes POINT(latitude longitude).

Code examples #

Example 1 — the export that connects the two #

import duckdb


def export_postgis_to_parquet(pg_dsn, table, target, geometry_column="geom",
                              srid=4326, where=None):
    """Snapshot a PostGIS table as GeoParquet for analytical use."""
    con = duckdb.connect()
    con.execute("install spatial; load spatial;")
    con.execute("install postgres; load postgres;")
    con.execute(f"attach '{pg_dsn}' as pg (type postgres, read_only)")

    clause = f"where {where}" if where else ""
    con.execute(f"""
        copy (
            select * exclude {geometry_column},
                   st_geomfromwkb({geometry_column}) as geom
            from pg.{table} {clause}
        ) to '{target}' (format parquet, compression zstd)
    """)
    rows = con.execute(f"select count(*) from read_parquet('{target}')").fetchone()[0]
    print(f"exported {rows:,} rows from {table} to {target} (SRID {srid})")
    return target

The read_only attachment matters: an analytical process should not be able to write to the system of record by accident.

Example 2 — the same question in both, for comparison #

POSTGIS_SQL = """
    select a.name, count(*) as n
    from points p
    join areas a on st_intersects(p.geom, a.geom)
    group by 1 order by n desc
"""

DUCKDB_SQL = """
    select a.name, count(*) as n
    from read_parquet('points.parquet') p
    join st_read('areas.gpkg') a
      on st_intersects(st_point(p.lon, p.lat), a.geom)
    group by 1 order by n desc
"""


def compare(pg_connection, duck_connection):
    import time

    started = time.perf_counter()
    pg_rows = pg_connection.execute(POSTGIS_SQL).fetchall()
    pg_secs = time.perf_counter() - started

    started = time.perf_counter()
    duck_rows = duck_connection.execute(DUCKDB_SQL).fetchall()
    duck_secs = time.perf_counter() - started

    print(f"postgis {pg_secs:6.2f}s   duckdb {duck_secs:6.2f}s")
    assert [r[0] for r in pg_rows] == [r[0] for r in duck_rows], \
        "the two engines returned different groups — check the CRS on both sides"

The assertion is not decoration. A CRS mismatch in DuckDB returns zero rows without an error, so a comparison that only measures time can compare a correct answer with an empty one.

Example 3 — a decision record for the project #

from dataclasses import dataclass


@dataclass
class WorkloadProfile:
    concurrent_writers: int
    single_feature_lookups_per_second: float
    largest_analytical_scan_rows: int
    users: int
    authoritative_storage: str          # "database" | "files"

    def recommend(self) -> str:
        reasons = []
        if self.concurrent_writers > 1:
            reasons.append(f"{self.concurrent_writers} concurrent writers")
        if self.users > 3:
            reasons.append(f"{self.users} users sharing the data")
        if self.single_feature_lookups_per_second > 10:
            reasons.append(
                f"{self.single_feature_lookups_per_second:.0f} single-feature "
                f"lookups/s (measured: 13.4 ms each in DuckDB, 0.058 ms in a row store)")
        if reasons:
            return "PostGIS — " + "; ".join(reasons)

        if self.authoritative_storage == "files" and self.largest_analytical_scan_rows > 500_000:
            return (f"DuckDB — analysis over files, "
                    f"{self.largest_analytical_scan_rows:,}-row scans")
        if self.largest_analytical_scan_rows < 200_000:
            return "GeoPandas — neither engine is needed at this size"
        return "DuckDB, with a PostGIS export if the data lives in a database"

Explanation #

Why row and column orientation decide the shapes #

PostGIS stores a row's fields together, so fetching one feature is one seek and one contiguous read. DuckDB stores a column's values together, so fetching one feature means touching every column's storage.

The measured 230× difference between DuckDB and SQLite on a point lookup is that property, not a tuning failure. Nothing you configure changes it, because it is the layout.

The same property inverts for scans: reading one column of a nineteen-column table costs one column in DuckDB and the whole table in a row store. A one-column aggregate over 13.5 million rows took 0.03 s from Parquet and 1.30 s from the row-oriented equivalent.

Why the import step is a real cost #

A PostGIS workflow over file-based data has a loading stage: read the files, write the tables, keep them in sync as the files change. That stage is where "the database is out of date" bugs live, and it is repeated every time the source data updates.

DuckDB removes it. The query reads the current file, so there is no second copy and nothing to synchronise. For an analyst whose inputs arrive as monthly Parquet exports, that is the whole argument.

Why concurrency is the hard boundary #

Everything else in this comparison is a trade-off with numbers on both sides. Concurrent writing is not: DuckDB has one writer, and a system with two writers needs a system that supports two writers.

That single fact settles the question for any application backend, any multi-user editing workflow and any pipeline where a loader and an application both write. It is worth asking first, because it makes the rest of the analysis unnecessary.

Why the answer is often "both" #

The two engines fail in complementary ways, which makes the combination natural rather than redundant. PostGIS holds the truth and serves the application; DuckDB reads an export and answers analytical questions without touching the production server.

The export also protects the database. A long analytical scan against a live PostGIS instance competes with the application for I/O and connections; the same scan against a Parquet snapshot competes with nothing.

Edge cases or notes #

• DuckDB's postgres extension can attach a live PostGIS database and query it — useful for exports and for joining across both.
• PostGIS raises on mixed SRIDs; DuckDB returns zero rows. Check the CRS explicitly in DuckDB.
• PostGIS has far more spatial functions, including topology, routing extensions and raster support.
• DuckDB has no ST_MakeValid equivalent in every version — check what your version provides before relying on it.
• A GeoPackage is a row store too and is often the right answer for a small, edited, single-user dataset.
• DuckDB can write PostGIS tables through the extension, which makes it a reasonable ETL tool into a system of record.
• Backups differ completely. A DuckDB file is a file; a PostGIS database needs a backup strategy.
• Below a couple of hundred thousand features, GeoPandas beats both on convenience and, measurably, on small joins.

Internal links #

• DuckDB spatial explained: a spatial database that is just a file — what DuckDB is
• PostGIS explained: when to use it — what PostGIS is for
• Columnar or row storage: why DuckDB is fast on wide tables — the underlying difference
• Spatial SQL versus GeoPandas — the third option
• PostGIS spatial indexes explained — the row-store index model
• How DuckDB uses (and does not use) a spatial index — the analytical one
• How to use DuckDB as the engine in a GIS pipeline — the analytical architecture
• How to benchmark DuckDB against GeoPandas honestly — measuring on your own data

FAQ #

Should I use DuckDB or PostGIS? #

PostGIS if anything writes concurrently, if many users share the data, or if the workload fetches individual features. DuckDB for analysis over files. Many projects use both.

Can DuckDB replace PostGIS? #

No. It has one writer, no roles, no replication, and it is about 230 times slower at single-row lookups. It is an analysis engine, not a system of record.

Is DuckDB faster than PostGIS? #

For analytical scans over columnar files, generally yes — it reads only the columns a query names. For fetching one feature by id, decisively no.

Do I have to import my data into DuckDB? #

No, and that is much of the point. It queries Parquet, CSV, shapefiles and GeoPackages in place, so there is no second copy to keep in sync.

Can DuckDB read from a PostGIS database? #

Yes, through the postgres extension. Attach the database read-only and query it, which is also the cleanest way to export a snapshot to GeoParquet.

What about GeoPackage or SQLite? #

They are row stores, which makes them the right answer for small single-user datasets and for anything that fetches individual features. Measured, SQLite did a point lookup in 0.058 ms against DuckDB's 13.4 ms.