DuckDB Connection and Concurrency Management

DuckDB is an embedded engine, so “concurrency” here never means a connection pool talking to a remote server — it means threads and processes inside your own application contending for one in-process database instance, its single buffer pool, and its one memory ceiling. This guide is part of the Python & DuckDB integration workflows reference and lays out the concurrency model for spatial workloads: how a duckdb.connect() instance relates to the con.cursor() objects that fan out across threads, how DuckDB’s own intra-query parallelism differs from application-level concurrency, why a single connection serializes queries, and when to abandon threads for read-only multi-process access to a file database. Get this wrong and a spatial pipeline either silently serializes behind one transaction context or corrupts state by sharing a connection across threads; get it right and one shared GEOMETRY catalog feeds every worker with zero duplication.

Runtime Configuration & Memory Guardrails #

The first thing to internalize is that almost every knob you care about is set on the database instance, not on the connection you happen to be holding. memory_limit and threads are global to the instance: two cursors on the same in-memory database share one buffer pool and one ceiling, and a SET memory_limit issued from any cursor changes it for all of them. You cannot give worker A a 2 GB budget and worker B a 6 GB budget on the same instance — that requires two instances, which for a shared in-memory database is impossible by construction.

import duckdb

# ONE instance for the whole process. Every cursor below shares this catalog,
# buffer pool, and the single memory_limit/threads budget set here.
con = duckdb.connect(database=":memory:", config={
    "memory_limit": "8GB",   # instance-wide ceiling, NOT per-cursor; all cursors draw from it
    "threads": 4,            # DuckDB intra-query worker pool; shared across concurrent cursors too
    "temp_directory": "/var/lib/duckdb/spill",  # over-budget spill target; keep on local NVMe
    "preserve_insertion_order": False,          # lets parallel scans reorder rows — faster, order undefined
})
con.execute("INSTALL spatial; LOAD spatial;")  # register ST_ kernels before any cursor resolves them

Because the spatial extension supplies the ST_ function set, treat LOAD spatial as connection-scoped setup you run in every cursor’s initialization. INSTALL is global — it drops the extension binary into the local cache once — but the safe, version-independent rule is to issue LOAD spatial from each cursor before its first spatial query. The call is idempotent (a no-op if the instance already has it) and it guarantees ST_ resolution no matter which cursor executes first, which matters the moment you spawn workers that might each be the first to touch geometry.

Before you fan a spatial workload across threads or processes, confirm these preconditions:

• Exactly one duckdb.connect() instance exists per process for a given database — never one connect() per thread against :memory: (each would be a separate, empty database).
• Every worker uses its own con.cursor(); no Connection object is touched by two threads at once.
• LOAD spatial runs in each cursor’s setup so ST_ functions resolve regardless of execution order.
• memory_limit is sized for the sum of all concurrent cursors’ working sets, because they share one pool — not for a single query in isolation.
• threads accounts for application-level parallelism: N cursors each triggering DuckDB’s own threads-way parallelism can oversubscribe the box.

Trade-off: raising threads accelerates a single large scan but multiplies peak memory, because each intra-query worker can hold its own hash-build or sort buffer; when you also run several cursors concurrently, total in-flight threads become roughly cursors × threads, so lower threads before you lower memory_limit when a spatial join thrashes. The spill mechanics behind that ceiling are analyzed in the in-memory vs disk storage reference.

Primary Execution Pattern — one instance, a cursor per thread #

The canonical unit of DuckDB concurrency in Python is a single shared instance with a lightweight con.cursor() per thread. A cursor is a new connection object bound to the same underlying database: it inherits the catalog, sees the same tables, reads from the same buffer pool, but carries its own transaction context and its own active result set. That isolation of transaction and result state is exactly what makes it safe to run one cursor per worker thread, and it is why sharing a single Connection across threads — covered in depth in sharing DuckDB connections across threads — is unsafe.

The reason threads help at all is the GIL. DuckDB’s Python client releases the Global Interpreter Lock while a query executes inside the C++ engine, so two threads each driving their own cursor achieve real parallelism through C++ even though Python bytecode is still serialized. The GIL is only held while marshalling arguments in and Arrow buffers out; the spatial compute itself — the ST_Intersects sweep, the R-tree probe, the hash aggregate — runs GIL-free.

The cursor-per-worker fan-out #

The pattern that puts this model to work is a thread pool where each submitted task pulls a fresh cursor, loads spatial, runs its slice of the workload, and closes the cursor. Because the cursors share the catalog, every worker sees the same registered tables and any R-tree index built once by the main thread, without re-reading a byte from disk.

from concurrent.futures import ThreadPoolExecutor

con = duckdb.connect(":memory:", config={"threads": 4, "memory_limit": "8GB"})
con.execute("INSTALL spatial; LOAD spatial;")
con.execute("CREATE TABLE parcels AS SELECT * FROM read_parquet('parcels/*.parquet')")

def summarize_region(region_id: int):
    cur = con.cursor()                     # own transaction context, shared buffers/catalog
    try:
        cur.execute("LOAD spatial")        # idempotent; guarantees ST_ resolves in this cursor
        return cur.execute("""
            SELECT ?, COUNT(*), ST_Collect(geom)
            FROM parcels WHERE region_id = ?
        """, [region_id, region_id]).fetch_arrow_table()
    finally:
        cur.close()                        # free the cursor's result buffers promptly

with ThreadPoolExecutor(max_workers=4) as pool:
    tables = list(pool.map(summarize_region, region_ids))

Keep max_workers and the instance threads setting in a deliberate relationship: four workers each able to launch four intra-query threads is sixteen threads competing for cores. On an eight-core host, either cap max_workers at 2 with threads = 4, or set threads = 2 with four workers, and measure. This is the same connection-per-task discipline the async execution patterns guide applies with an event loop instead of a thread pool, including running async spatial queries in Python where the cursor is dispatched through asyncio.to_thread.

Read-only multi-process access to a file database #

Threads share one instance and one memory budget; they cannot give you fault isolation or a memory ceiling per worker. When you need either — or when the workers are genuinely separate OS processes such as Gunicorn workers or a job array — switch to a file database opened READ_ONLY by many processes at once. DuckDB permits any number of read-only openers of a file concurrently, but only a single process may hold it read-write.

# Each analytics process opens its OWN instance over the shared file, READ_ONLY.
con = duckdb.connect()                       # a private in-memory instance per process
con.execute("LOAD spatial")
con.execute("ATTACH 'parcels.duckdb' AS store (READ_ONLY)")  # concurrent readers are safe
con.execute("SELECT COUNT(*) FROM store.parcels WHERE ST_Area(geom) > 1e6")

The READ_ONLY attach is the process-level equivalent of the cursor fan-out: it shares the data (the file) rather than a buffer pool, so each process pays its own cache-warming cost but gets a fully isolated memory_limit and cannot be taken down by a sibling’s OOM. The write side must funnel through a single owner, exactly as the batch processing pipelines guide funnels partition writes through one process while readers fan out. The multi-tenant framing of this file boundary appears in the parent Python & DuckDB integration workflows overview.

Execution Plan & Concurrency Validation #

Two things need validating: that a query actually parallelizes internally, and that your application-level cursors are not silently serializing behind one connection. For the first, EXPLAIN ANALYZE reports per-operator threads and timing.

EXPLAIN ANALYZE
SELECT region_id, COUNT(*)
FROM parcels
WHERE ST_Intersects(geom, ST_MakeEnvelope(0, 0, 10, 10))
GROUP BY region_id;

Read the annotated plan for these signals:

• Intra-query parallelism engaged. The scan and HASH_GROUP_BY nodes should show work distributed across threads workers. If a heavy scan runs single-threaded despite threads > 1, the relation may be too small to partition, or preserve_insertion_order is forcing an ordered pipeline.
• No accidental serialization at the connection. If two “concurrent” cursors actually share one Connection object, their queries run one after another — wall-clock time is the sum, not the max. Confirm true overlap by timing the pool against a serial baseline; a speedup near 1× means the work never left one transaction context.
• Cardinality into the spatial predicate. As on any spatial join, rows reaching ST_Intersects far below N × M confirm a bounding-box pre-filter fired; the spatial indexing internals reference covers the R-tree that produces that pruning.

A quick live probe from Python confirms the instance is doing what you think and lets you watch the shared pool under concurrency:

print(con.execute("SELECT current_setting('threads'), current_setting('memory_limit')").fetchone())
# Peak allocation and spill are instance-wide — every cursor's usage rolls up here.
print(con.execute("SELECT sum(memory_usage_bytes) FROM duckdb_memory()").fetchone())

Performance Trade-offs #

The three concurrency strategies trade isolation against sharing. Pick by whether your workload is read-heavy or write-heavy and whether you need fault isolation, then verify against your own data.

The highest-leverage decision is read-heavy vs write-heavy. A read-mostly analytical fan-out belongs on cursors (cheapest, shares the warm buffer pool); a workload where every worker must write the same store belongs behind a single writer with readers attached READ_ONLY, because concurrent writers to one instance collide on transaction conflicts and concurrent writers to one file are simply not allowed. For validation and cross-checking a plan across builds, the same capture discipline used for vectorized aggregations applies here.

Edge Cases & Anti-Patterns #

One connect(":memory:") per thread (silent data disappearance). Calling duckdb.connect(":memory:") in each worker does not share a database — each call creates a brand-new, empty in-memory instance. A table created in one thread is invisible to the others, and joins return nothing. The fix is one connect() in the main thread, cursor() in each worker.

# Anti-pattern: N separate empty databases, no shared catalog.
def worker():  # each call → its own empty :memory: instance
    c = duckdb.connect(":memory:"); c.execute("SELECT * FROM parcels")  # ERROR: no such table

# Fix: share one instance; hand each worker a cursor.
con = duckdb.connect(":memory:")
def worker():
    return con.cursor().execute("SELECT * FROM parcels").fetch_arrow_table()

Expecting a per-cursor memory_limit (shared blast radius). Setting memory_limit from a cursor changes the whole instance, so one runaway ST_Union across cursors can starve every co-tenant thread. If workers need independent budgets, they need independent instances — which for shared data means the READ_ONLY multi-process model, not cursors.

Concurrent writers to one instance (transaction conflict). DuckDB uses optimistic MVCC: two cursors updating the same table raise a TransactionContext conflict on commit rather than blocking. Do not treat cursors as a write-scaling mechanism; serialize writes through one cursor and fan out only reads.

# Detect the conflict pattern instead of retrying blindly forever.
try:
    cur.execute("UPDATE parcels SET geom = ST_MakeValid(geom) WHERE NOT ST_IsValid(geom)")
except duckdb.TransactionException:
    ...  # another cursor already mutated overlapping rows — funnel writes through one owner

Forgetting LOAD spatial in a worker (function-not-found). A cursor that runs a spatial query before the extension’s ST_ functions are resolvable in its context fails with a binder error. Because the call is idempotent, the cheap fix is to run LOAD spatial unconditionally in every worker’s setup rather than reasoning about which cursor touches geometry first — the narrower thread-safety mechanics are dissected in sharing DuckDB connections across threads.

Attaching a file read-write from two processes (lock failure). A second process opening the same file without READ_ONLY fails to acquire the write lock. Reserve read-write for exactly one process; give every other opener (READ_ONLY).

Concurrency Regression Analysis #

Concurrency bugs are the ones that pass every single-threaded test and then serialize, deadlock on writes, or blow the shared budget under load. Bake a small load harness into CI that asserts the two properties you actually depend on: that cursor fan-out overlaps, and that the instance stays under its ceiling. Capture a wall-clock speedup baseline and a peak-memory baseline, then diff them across builds the way the batch processing pipelines guide diffs plan shape.

import duckdb, time
from concurrent.futures import ThreadPoolExecutor

con = duckdb.connect(":memory:", config={"threads": 2, "memory_limit": "4GB"})
con.execute("INSTALL spatial; LOAD spatial;")
con.execute("CREATE TABLE parcels AS SELECT * FROM read_parquet('parcels/*.parquet')")

SQL = "SELECT COUNT(*) FROM parcels WHERE ST_Area(geom) > ?"

def run(threshold):
    cur = con.cursor(); cur.execute("LOAD spatial")
    return cur.execute(SQL, [threshold]).fetchone()[0]

thresholds = [1e5, 5e5, 1e6, 5e6] * 8

t0 = time.perf_counter()
for t in thresholds: run(t)                       # serial baseline
serial = time.perf_counter() - t0

t0 = time.perf_counter()
with ThreadPoolExecutor(max_workers=4) as pool:   # cursor-per-worker fan-out
    list(pool.map(run, thresholds))
parallel = time.perf_counter() - t0

speedup = serial / parallel
peak = con.execute("SELECT max(memory_usage_bytes) FROM duckdb_memory()").fetchone()[0]

# A speedup near 1.0 means the cursors serialized — likely a shared Connection object.
assert speedup > 1.5, f"concurrency regression: speedup {speedup:.2f} (cursors not overlapping)"
# Peak must respect the shared instance ceiling, not the per-query estimate.
assert peak < 4 * 1024**3, f"instance exceeded shared memory_limit: {peak} bytes"

Three fields are worth tracking build over build: the observed speedup (a collapse toward 1× flags an accidentally shared connection or a scan too small to parallelize), instance-wide peak memory (creep toward the ceiling predicts a spill or OOM under real concurrency), and any TransactionException count in the logs (a rising count means write contention that a single-writer redesign would remove). For write-heavy pipelines that fail these assertions, the durable fallback is the process-pool-plus-READ_ONLY model rather than more threads, and the Arrow handoff that keeps results zero-copy across that boundary is covered in DuckDB to GeoPandas sync.

Related #

See also

• Sharing DuckDB connections across threads — the thread-safety rule in detail: never share a Connection, always cursor() per worker.
• Async execution patterns — the same connection-per-task discipline over an asyncio event loop, including running async spatial queries in Python.
• Batch processing pipelines — single-writer, many-reader partitioning for large-scale spatial ETL.

Up: Python & DuckDB integration workflows