Data Compression
  1. Home
  2. Query Engines & Cloud Analytics for GeoParquet
  3. DuckDB Spatial Extension for GeoParquet
  4. DuckDB vs PostGIS for Analytical Spatial Queries
Section: Query Engines & Cloud Analytics for GeoParquet 12 min read

DuckDB vs PostGIS for Analytical Spatial Queries

PostGIS is a transactional database with spatial types; DuckDB is an analytical engine that reads columnar files. That difference in kind, not degree, predicts almost every benchmark result you will see: PostGIS wins single-feature lookups under concurrency and every workload involving writes; DuckDB wins scans, aggregations, and anything where the answer summarises many rows. This page makes the comparison concrete, extending the DuckDB spatial extension for GeoParquet.

Quick Reference

Query shape Winner Margin Primary Use Case
One feature by identifier PostGIS 10–100× Feature detail endpoints
Small window, few features PostGIS 2–5× Map click-through, editing
Aggregate over a region DuckDB 5–20× Dashboards, statistics
Full-table statistics DuckDB 20–100× Reporting, QA sweeps
Join two large tables DuckDB 3–10× Analytical enrichment
Concurrent writes PostGIS only option Application state
Query files in object storage DuckDB only option Lakehouse analytics

Two Architectures, Two Question Shapes

PostGIS stores rows. A feature’s geometry and all its attributes live together on a page, and a GiST index maps a bounding box to the pages that hold matching rows. Answering “give me feature 8814” is a couple of index descents and one page read — microseconds, and it stays microseconds as the table grows because the index depth grows logarithmically. The machinery that makes that possible — multi-version rows, tuple headers, a write-ahead log, page-level locking — is also what makes it slower when a query has to visit every row.

DuckDB stores columns and reads files. Answering “what is the total area of residential parcels by district” means reading two columns out of eighteen, skipping every row group whose bounding box misses the region, and running a vectorised aggregation over compressed batches. There is no index descent because there is no index — the covering statistics in the file footer do the coarse skipping, and column pruning does the rest.

Ask each engine its own question and both look excellent. Ask each the other’s question and both look poor.

What each engine does with each question The upper half shows a single-feature lookup: PostGIS descends a GiST index in two steps and reads one page holding the whole row, while DuckDB must open the file footer, identify a candidate row group, and read the relevant column chunks of it — far more work for one answer. The lower half shows a regional aggregation: PostGIS visits every matching row and reads all its columns from disk pages, while DuckDB skips most row groups on their bounding boxes and reads only the two columns the aggregate needs. Question A — "give me feature 8814" PostGIS — index descent 2 index pages → 1 heap page → done ~0.4 ms, flat as the table grows DuckDB — footer, then chunks read footer → 1 row group → 18 chunks ~40 ms — a scan engine answering a lookup Question B — "total residential area by district" PostGIS — visit every matching row 4.2 M heap tuples, all 18 columns read ~52 s — a row store answering an aggregate DuckDB — skip, then scan 2 columns 4 of 320 row groups, 2 of 18 columns ~2.6 s, vectorised over compressed batches Each engine is excellent at its own question and poor at the other's. That is the whole comparison.

Running the Comparison Honestly

python
# Requires: duckdb>=1.0, psycopg[binary]>=3.2  (Python 3.10+)
from __future__ import annotations

import time
from dataclasses import dataclass

import duckdb
import psycopg


@dataclass(frozen=True)
class Timing:
    engine: str
    query: str
    seconds: float
    rows: int


QUERIES = {
    "lookup": {
        "duckdb": "SELECT * FROM read_parquet(?) WHERE uprn = ?",
        "postgis": "SELECT * FROM parcels WHERE uprn = %s",
    },
    "window": {
        "duckdb": (
            "SELECT count(*) FROM read_parquet(?) "
            "WHERE bbox.xmin <= ? AND bbox.xmax >= ? "
            "AND bbox.ymin <= ? AND bbox.ymax >= ?"
        ),
        "postgis": (
            "SELECT count(*) FROM parcels "
            "WHERE geom && ST_MakeEnvelope(%s, %s, %s, %s, 4326)"
        ),
    },
    "aggregate": {
        "duckdb": (
            "SELECT district, sum(ST_Area(ST_GeomFromWKB(geometry))) "
            "FROM read_parquet(?) WHERE use_class = 'residential' GROUP BY district"
        ),
        "postgis": (
            "SELECT district, sum(ST_Area(geom)) FROM parcels "
            "WHERE use_class = 'residential' GROUP BY district"
        ),
    },
}


def time_duckdb(sql: str, params: list) -> Timing:
    con = duckdb.connect()
    try:
        con.execute("INSTALL spatial; LOAD spatial;")
        start = time.perf_counter()
        rows = con.execute(sql, params).fetchall()
        return Timing("duckdb", sql[:32], time.perf_counter() - start, len(rows))
    except duckdb.Error as exc:
        raise RuntimeError(f"duckdb query failed: {exc}") from exc
    finally:
        con.close()


def time_postgis(dsn: str, sql: str, params: tuple) -> Timing:
    try:
        with psycopg.connect(dsn) as con, con.cursor() as cur:
            # Discard the first run so both engines are measured warm; a cold
            # PostGIS buffer cache would flatter DuckDB unfairly.
            cur.execute(sql, params)
            cur.fetchall()
            start = time.perf_counter()
            cur.execute(sql, params)
            rows = cur.fetchall()
            return Timing("postgis", sql[:32], time.perf_counter() - start, len(rows))
    except psycopg.Error as exc:
        raise RuntimeError(f"postgis query failed: {exc}") from exc

Validation

Report all three query shapes together. A comparison that reports only the aggregate is not a comparison; it is an advertisement.

text
query shape   engine     seconds   rows      note
lookup        postgis      0.0004      1     GiST index descent
lookup        duckdb       0.041       1     footer + one row group
window        postgis      0.019   1,284     index + heap fetch
window        duckdb       0.088   1,284     4 row groups scanned
aggregate     postgis     52.4         41     4.2 M heap tuples
aggregate     duckdb       2.6          41     2 of 18 columns, 4 row groups

Expected ranges: PostGIS 10–100× ahead on lookups, 2–5× ahead on small windows, and DuckDB 5–20× ahead on aggregates over a substantial fraction of the table. If your numbers disagree by a lot, check that PostGIS has the right index and that the GeoParquet is spatially sorted with covering columns — an unfair setup on either side produces a misleading result.

The usual answer is both, split by query shape One conversion pipeline produces two outputs from the same validated source. The serving path loads into PostGIS, which backs the application's feature lookups, editing, and transactional writes. The analytical path writes GeoParquet to object storage, which DuckDB queries in place for dashboards, statistics, and ad hoc analysis. A note observes that the two are kept consistent because both derive from the same pipeline run rather than by synchronising two live systems. Conversion pipeline one validated source, two outputs PostGIS — serving feature lookups · editing · transactions high concurrency, small results GeoParquet + DuckDB — analysis dashboards · statistics · ad hoc SQL low concurrency, large scans Consistency comes from a shared pipeline run, not from synchronising two live systems. That is what makes running both cheap enough to be the default answer rather than a compromise. The cost the query benchmark leaves out Two paths for the same dataset. The PostGIS path requires an ingestion job, a second copy of the data on database storage, an index build, and a pipeline that keeps it current — all before any query runs. The DuckDB path queries the GeoParquet the conversion pipeline already produced. On query time alone the comparison is close; including everything above it is not. PostGIS path GeoParquet ingestion job second copy index build query four stages to maintain, plus disk, plus a job that keeps them in step DuckDB path GeoParquet query the file the pipeline already produced

Edge Cases and Caveats

Benchmarks run against an unindexed PostGIS table. Without a GiST index on the geometry column and a btree on the lookup key, PostGIS sequential-scans and loses every comparison — including ones it should win by two orders of magnitude. Verify the indexes exist and are being used before publishing any number.

Benchmarks run against unsorted GeoParquet. The mirror-image mistake: a file written in insertion order without covering statistics cannot skip row groups, so DuckDB reads everything and loses comparisons it should win. Both engines need their homework done before the comparison means anything.

Concurrency measured with one client. DuckDB is an embedded single-process engine; PostGIS serves hundreds of connections. A single-client benchmark hides the difference entirely, and the difference is often the deciding factor. If the workload has concurrency, measure with concurrency.

Forgetting the load step in the accounting. PostGIS query time excludes the hours of ingestion that put the data there, and the disk that second copy occupies, and the pipeline that keeps it current. DuckDB queries the GeoParquet the pipeline already produced. For data that is only analysed, that difference frequently outweighs the query times.

Frequently Asked Questions

Is DuckDB faster than PostGIS for spatial queries?

For analytical queries that scan and aggregate over large fractions of a table, usually by a wide margin, because it reads only the columns it needs, decodes them in a vectorised loop, and never pays for row-level transaction machinery. For a query that returns one feature by identifier, PostGIS with a suitable index is faster and will stay faster, because an index lookup is inherently cheaper than any scan. Neither is faster in general; they are shaped for different questions.

Can DuckDB replace PostGIS as an application database?

No, and it does not try to. PostGIS provides concurrent writers, transactional isolation, row-level locking, constraints, triggers, and a connection model built for many simultaneous clients. DuckDB is an embedded analytical engine: one process, one writer, no network protocol. Where an application needs a database, it needs a database — the useful comparison is between DuckDB and a data warehouse, not between DuckDB and an OLTP store.

Do I have to load data into DuckDB before querying it?

No, and that is much of the appeal. DuckDB queries GeoParquet in place, whether on local disk or in object storage, using the file’s own footer statistics to skip row groups. There is no load step, no second copy, and no synchronisation problem. PostGIS by contrast requires ingestion into its own storage, which is a real cost in time, disk, and pipeline complexity for data that only needs to be analysed.

← Back to DuckDB Spatial Extension for GeoParquet