Sharing DuckDB Connections Across Threads

Handing one duckdb.Connection object to several worker threads is the fastest way to turn a correct spatial query into intermittent binder errors, scrambled result sets, and the occasional hard crash — and it fails precisely because a connection carries one transaction context and one active result at a time. This walkthrough sits under DuckDB connection and concurrency management and isolates a single rule: do not share a Connection across threads; create the database once and call con.cursor() to give each thread its own connection over the same catalog and buffer pool, remembering that every cursor must LOAD spatial before it resolves an ST_ function.

Root-Cause Analysis #

A DuckDB Connection is not free-threaded. It owns exactly one client transaction context and one pending result at any instant, and the Python client does not serialize concurrent access to it. When two threads call execute() on the same object, their statements interleave inside that single context: one thread’s LOAD spatial or BEGIN collides with another’s fetch, the pending result of one query is overwritten by the next, and the failure surfaces in one of a few recognizable shapes.

• Interleaved statements on one transaction context. Thread A’s query and thread B’s query share the connection’s single in-flight statement slot. The result you fetch may belong to the other thread’s query, or you get a binder/parse error from a half-applied statement — a nondeterministic bug that vanishes under a debugger’s slower timing.
• Overwritten result cursors. A Connection holds one result set. If thread B executes while thread A is still streaming rows, A’s result is invalidated mid-iteration, raising an “no open result set” style error or returning truncated geometry.
• Native-layer races (the crash-shaped failure). Because query execution drops the GIL in C++, two threads mutating the same connection’s internal state race with no Python lock protecting them, which can corrupt the connection object rather than raise a clean Python exception.
• Missing LOAD spatial in the worker’s context. Even once you switch to cursors, a worker whose cursor never loaded the spatial extension hits a “function ST_... does not exist” binder error the moment it runs geometry SQL.

The fix for the first three is structural — one cursor per thread — and the fourth is a one-line idempotent guard. None of them is solved by wrapping the shared connection in a Python lock: that just re-serializes every query behind one mutex and throws away the parallelism you came for.

Deterministic Configuration #

Build the database once, in the main thread, and configure the instance there. Everything a worker needs — tables, indexes, the loaded extension — is reached through a cursor, so the only per-worker setup is cursor() plus an idempotent LOAD spatial.

import duckdb

# ONE shared instance for the process. Threads will each take a cursor off this.
con = duckdb.connect(":memory:", config={
    "threads": 2,          # DuckDB per-query parallelism; keep low when many cursors run at once
    "memory_limit": "4GB", # instance-wide ceiling shared by every cursor — size for the SUM of workers
})
con.execute("INSTALL spatial; LOAD spatial;")   # register ST_ kernels on the instance
con.execute("""
    CREATE TABLE sites AS SELECT * FROM read_parquet('sites/*.parquet');
    CREATE INDEX idx_sites ON sites USING RTREE (geom);   -- built once, visible to every cursor
""")

Because the index and tables live on the shared instance, no worker rebuilds or re-reads them; the cursor sees the catalog as it stood when the cursor was created. Keep threads modest here — with four worker cursors each able to spawn DuckDB’s own threads-way parallelism, the effective thread count is the product, and oversubscribing cores inflates peak memory on the shared pool without adding throughput.

Optimized Execution Pattern — broken vs correct fan-out #

The broken version shares one connection across a pool. It may even appear to work on a warm run with light contention, which is what makes it dangerous in production.

# BROKEN: every worker drives the SAME Connection object.
con = duckdb.connect(":memory:")
con.execute("INSTALL spatial; LOAD spatial;")

def worker(bbox):
    # Race: this execute() collides with other threads' in-flight statement + result.
    return con.execute(
        "SELECT id FROM sites WHERE geom && ST_MakeEnvelope(?,?,?,?)", bbox
    ).fetchall()

with ThreadPoolExecutor(max_workers=8) as pool:
    results = list(pool.map(worker, bboxes))   # intermittent binder errors / wrong rows / crash

The correct version changes exactly one thing that matters: each worker calls con.cursor() to obtain its own connection over the shared instance, then loads spatial in that cursor before running geometry SQL.

from concurrent.futures import ThreadPoolExecutor

# CORRECT: one instance, a fresh cursor per worker.
con = duckdb.connect(":memory:", config={"threads": 2, "memory_limit": "4GB"})
con.execute("INSTALL spatial; LOAD spatial;")
con.execute("CREATE TABLE sites AS SELECT * FROM read_parquet('sites/*.parquet')")
con.execute("CREATE INDEX idx_sites ON sites USING RTREE (geom)")

def worker(bbox):
    cur = con.cursor()              # ← own transaction context + result set; shares catalog/buffers
    try:
        cur.execute("LOAD spatial") # ← idempotent guard so ST_ resolves in THIS cursor
        return cur.execute("""
            SELECT id, name
            FROM sites
            WHERE geom && ST_MakeEnvelope(?, ?, ?, ?)          -- R-tree MBR pre-filter
              AND ST_Intersects(geom, ST_MakeEnvelope(?, ?, ?, ?))  -- exact topology on survivors
        """, [*bbox, *bbox]).fetch_arrow_table()
    finally:
        cur.close()                 # release the cursor's result buffers promptly

with ThreadPoolExecutor(max_workers=4) as pool:
    tables = list(pool.map(worker, bboxes))

The annotated difference is the whole lesson: con.execute(...) in the broken form shares one transaction slot, while con.cursor().execute(...) gives each thread an isolated context that still points at the same in-memory tables and the same R-tree — so the fan-out is genuinely parallel (query execution drops the GIL in C++) with zero data duplication. The two-stage && then ST_Intersects shape is the same index-first pattern documented for point-in-polygon optimization; here it simply runs once per cursor.

Diagnostic Queries & Plan Validation #

The tell for accidental connection sharing is that a threaded run is no faster — or is flakier — than a serial one. Time both and confirm the cursors genuinely overlap; the C++ engine releases the GIL during execution, so real cursor fan-out shows wall-clock time closer to the slowest task than to the sum.

import time

def timed(fn, args):
    t0 = time.perf_counter()
    for a in args: fn(a)
    return time.perf_counter() - t0

serial = timed(worker, bboxes)                      # baseline
t0 = time.perf_counter()
with ThreadPoolExecutor(max_workers=4) as pool:
    list(pool.map(worker, bboxes))
parallel = time.perf_counter() - t0
# speedup ≈ 1.0 → cursors serialized (shared Connection); > 1 → true overlap
assert serial / parallel > 1.3, "no parallel speedup — check for a shared Connection object"

Validate that each cursor’s query still uses the R-tree rather than degrading to a full scan under contention. Run EXPLAIN ANALYZE from a single cursor and confirm the && predicate drives an index scan, not a NESTED_LOOP_JOIN; the count of rows reaching ST_Intersects should sit far below N × M, the signature that the R-tree index internals pruned before topology ran. Watch the shared pool while the fan-out runs, since every cursor draws from one ceiling:

-- Instance-wide peak allocation: all cursors' usage rolls up into this one budget.
SELECT sum(memory_usage_bytes) AS bytes FROM duckdb_memory();

A peak that climbs toward memory_limit as you add workers is the shared-blast-radius symptom — more cursors do not get more memory, they divide the same pool.

Geometry Validation & Fallback Routing #

Concurrency multiplies the cost of invalid geometry: an unclosed ring or self-intersection that throws inside ST_Intersects will fail one worker while its siblings keep running, producing a partial result that is easy to miss. Quarantine and repair on the shared instance before the fan-out, so every cursor reads validated geometry.

-- Repair once on the shared instance, then rebuild the index the cursors share.
UPDATE sites SET geom = ST_MakeValid(geom) WHERE NOT ST_IsValid(geom);
DROP INDEX IF EXISTS idx_sites;
CREATE INDEX idx_sites ON sites USING RTREE (geom);   -- MBRs now match the fixed geometry

When the workload is write-heavy, cursors are the wrong tool entirely: concurrent writers to one instance collide on optimistic-MVCC transaction conflicts rather than scaling. Fall back to a process pool where each process owns an isolated instance and attaches the shared file READ_ONLY for reads while a single owner performs the writes — the model the parent connection and concurrency management guide details, and the same single-writer discipline the batch processing pipelines guide uses at job scale. For asyncio services that need this cursor isolation over an event loop instead of a thread pool, the dispatch mechanics are in running async spatial queries in Python.

Related #

See also

• Running async spatial queries in Python — the same cursor-per-task isolation dispatched through asyncio rather than a thread pool.
• Batch processing pipelines — the single-writer, many-reader fallback for write-heavy fan-out.

Up: DuckDB connection and concurrency management · Python & DuckDB integration workflows