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,231,941-row table took 0.46 ms in DuckDB and 0.0093 ms in SQLite (measured) — a row-store shape DuckDB is bad at. Joining 13,472,195 points to 4,596 polygons took 50.9 s and 291 MB in DuckDB (benchmark) — 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"


print(which_engine(concurrent_writers=True, fetch_single_features=True, row_count=5_000_000,
                   shared_by_many_users=True, data_lives_in_files=False))
print(which_engine(concurrent_writers=False, fetch_single_features=False, row_count=13_472_195,
                   shared_by_many_users=False, data_lives_in_files=True))

PostGIS
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.

Writers, access pattern, data location and cost

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 0.46 ms in DuckDB against 0.0093 ms in SQLite, 49 times longer, 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 join and aggregate over 13.5 million rows completed in DuckDB in 50.9 s using 291 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).

The same question asked of both

The data used on this page

The points are GeoNames' full allCountries dump — every feature, about 13.5 million, rebuilt daily at download.geonames.org, so counts drift a little between runs — kept both as the 1.79 GB text file and as places.parquet (also linked as geonames.parquet) with name, latitude, longitude, feature class, country and population. The polygons are Natural Earth's 1:10m admin-1 states and provinces, 4,596 of them, as provinces.shp.

import os
import zipfile

import duckdb
import requests


def fetch(url, path):
    if not os.path.exists(path):
        with requests.get(url, stream=True, timeout=600) as reply:
            reply.raise_for_status()
            with open(path, "wb") as out:
                for chunk in reply.iter_content(1 << 20):
                    out.write(chunk)


fetch("https://download.geonames.org/export/dump/allCountries.zip", "allCountries.zip")   # about 420 MB
if not os.path.exists("allCountries.txt"):
    zipfile.ZipFile("allCountries.zip").extract("allCountries.txt")
if not os.path.exists("places.parquet"):
    duckdb.sql("""
        copy (select column01 as name, column04 as lat, column05 as lon, column06 as feature_class,
                     column08 as country, column14 as population
              from read_csv('allCountries.txt', delim = '\\t', header = false, quote = '',
                            all_varchar = false, sample_size = -1))
        to 'places.parquet' (format parquet)""")
if not os.path.exists("geonames.parquet"):
    os.link("places.parquet", "geonames.parquet")
fetch("https://naciscdn.org/naturalearth/10m/cultural/ne_10m_admin_1_states_provinces.zip", "provinces.zip")
if not os.path.exists("provinces.shp"):
    with zipfile.ZipFile("provinces.zip") as z:
        for member in z.namelist():
            if member.startswith("ne_10m_admin_1_states_provinces."):
                open("provinces" + os.path.splitext(member)[1], "wb").write(z.read(member))
n = duckdb.sql("select count(*) from read_parquet('places.parquet')").fetchone()[0]
print(f"{n:,} GeoNames points; places.parquet {os.path.getsize('places.parquet') / 1e6:.0f} MB, "
      f"allCountries.txt {os.path.getsize('allCountries.txt') / 1e9:.2f} GB")

13,472,195 GeoNames points; places.parquet 306 MB, allCountries.txt 1.79 GB

For the comparisons, the setup starts a throwaway PostGIS in Docker and loads the same data into it and into files: GeoNames' 109,000 points in Great Britain as points, and the provinces as areas. PGPORT and PGPASSWORD point libpq at the container.

import time

import geopandas as gpd
from sqlalchemy import create_engine

os.environ.update(PGPORT="55433", PGPASSWORD="guide")
if os.system("docker inspect guide-postgis >/dev/null 2>&1") != 0:
    os.system("docker run -d --name guide-postgis -e POSTGRES_USER=user -e POSTGRES_PASSWORD=guide "
              "-e POSTGRES_DB=gis -p 127.0.0.1:55433:5432 postgis/postgis:16-3.4 >/dev/null")
    time.sleep(10)
PG_DSN = "host=localhost port=55433 dbname=gis user=user password=guide"
engine = create_engine("postgresql+psycopg://user@localhost/gis")
gb = duckdb.sql("select name, lat, lon from read_parquet('places.parquet') where country = 'GB'").df()
points = gpd.GeoDataFrame(gb, geometry=gpd.points_from_xy(gb.lon, gb.lat), crs=4326)
areas = gpd.read_file("provinces.shp")[["name", "geometry"]]
points.rename_geometry("geom").to_postgis("points", engine, if_exists="replace")
areas.rename_geometry("geom").to_postgis("areas", engine, if_exists="replace")
gb.to_parquet("points.parquet")
areas.to_file("areas.gpkg")
print(f"PostGIS and files: {len(points):,} points, {len(areas):,} areas")

PostGIS and files: 109,179 points, 4,596 areas

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 * 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


export_postgis_to_parquet(PG_DSN, "public.points", "points_snapshot.parquet")

exported 109,179 rows from public.points to points_snapshot.parquet (SRID 4326)

With DuckDB 1.5 the postgres extension reads a PostGIS geometry column straight in as GEOMETRY, so it needs no conversion — wrapping it in st_geomfromwkb fails with No function matches … st_geomfromwkb(GEOMETRY). 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, a.name
"""

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, a.name
"""


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 [tuple(r) for r in pg_rows] == [tuple(r) for r in duck_rows], \
        "the two engines returned different groups — check the CRS on both sides"
    print(f"identical: {len(pg_rows)} groups, {sum(r[1] for r in pg_rows):,} points")


import psycopg

with psycopg.connect(PG_DSN) as pg:
    duck = duckdb.connect()
    duck.execute("load spatial")
    compare(pg, duck)

postgis   0.28s   duckdb   4.91s
identical: 246 groups, 100,495 points

On GeoNames' 109,000 British points, already loaded and indexed in PostGIS, the server answered in 0.28 s while DuckDB, reading the GeoPackage afresh, took 4.9 s: data that already lives indexed in a database is PostGIS's home ground. The a.name tie-break is not decoration either: the first run of this comparison failed its assertion with identical counts, because order by n desc leaves areas with equal counts in whatever order each engine produced them. 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: 0.46 ms each in DuckDB, 0.0093 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"


print(WorkloadProfile(3, 40, 2_000_000, 12, "database").recommend())
print(WorkloadProfile(1, 0, 13_472_195, 1, "files").recommend())

PostGIS — 3 concurrent writers; 12 users sharing the data; 40 single-feature lookups/s (measured: 0.46 ms each in DuckDB, 0.0093 ms in a row store)
DuckDB — analysis over files, 13,472,195-row scans

Example 4 — one writer per database file

import subprocess
import sys

duckdb.connect("shared.duckdb").execute("create or replace table t as select 1 as x").close()
holder = subprocess.Popen([sys.executable, "-c",
    "import duckdb, time; con = duckdb.connect('shared.duckdb'); print('open', flush=True); time.sleep(20)"],
    stdout=subprocess.PIPE, text=True)
holder.stdout.readline()                         # the first process now holds the file
second = subprocess.run([sys.executable, "-c", "import duckdb; duckdb.connect('shared.duckdb')"],
                        capture_output=True, text=True)
holder.terminate()
print("second process:", second.stderr.strip().splitlines()[-1].replace(os.getcwd() + os.sep, "")[:120])

second process: _duckdb.IOException: IO Error: Could not set lock on file "shared.duckdb": Conflicting lock is held in /usr/bin/python3.

Row versus column, and the import step

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 49× 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.01 s from Parquet and 0.56 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.

Attaching PostGIS, SRID errors and function gaps

• 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.0093 ms against DuckDB's 0.46 ms.