DuckDB Spatial Analytical SQL for GIS

Pooling DuckDB Connections in Web Services

A connection pool solves a problem DuckDB does not have — connections are local objects, not network resources — and leaves untouched the one it does: several requests drawing on a single shared memory ceiling. This walkthrough, part of the connection and concurrency management reference, sets out what a web service should actually hold, how to bound concurrency by the constraint that binds, and the two failure modes specific to serving spatial queries from a request handler.

Root-Cause Analysis: why the usual pooling instinct misfires here

  • A connection is not a network resource. There is no handshake, no socket, no server-side session to reserve. Creating one is a local object allocation, so the cost a pool exists to amortise is close to zero.
  • The buffer pool is per database, not per connection. Opening several connections to the same file does not multiply the cache; opening several databases does. A pool of separate connections to the same path is therefore mostly harmless and mostly pointless.
  • The memory ceiling is shared. Every concurrent query draws on one memory_limit. Two heavy spatial overlays can exceed a ceiling that either one fits under comfortably, and no pool size expresses that — the bound has to be on concurrent queries.
  • One cursor per request is the isolation unit. A cursor has its own result set, session settings and transaction state while sharing the buffer pool and catalogue. That is exactly the split a request handler wants.
  • A slow query holds a thread, not a connection. Because the driver is synchronous, a long spatial scan occupies a worker thread for its duration. Bounding the worker pool is what protects the service; bounding a connection pool is not.

The distinguishing question is what runs out first under load. It is memory or worker threads, never connections, and a pool sized against connections protects the wrong resource.

What to hold, and at what scope The database per process, a cursor per request, a semaphore and a worker pool process-wide, and read-only attachment decided at startup. WHAT SCOPE WHY the database opened once, per process startup cost, one buffer pool a cursor per request isolation without duplication a concurrency semaphore per process memory is the shared resource a worker thread pool per process the driver is synchronous read-only attachment decided at startup removes the write lock

Nothing in this table is a connection pool, and nothing in it needs one.

Deterministic Configuration 

import asyncio, duckdb
from contextlib import contextmanager

# One database for the process. Read-only, because a query service should not
# be able to write and because it removes the single-writer lock entirely.
DB = duckdb.connect("gis.duckdb", read_only=True)
DB.execute("INSTALL spatial; LOAD spatial")
DB.execute("SET memory_limit = '6GB'")     # shared by every concurrent query
DB.execute("SET threads = 8")

# The bound that actually protects the service: concurrent queries, sized from
# the memory budget rather than from the core count.
QUERY_SLOTS = asyncio.Semaphore(3)

@contextmanager
def cursor():
    cur = DB.cursor()
    try:
        yield cur
    finally:
        cur.close()

Optimized Execution Pattern

The pattern is a shared read-only database, a cursor per request, and a semaphore that bounds how many spatial queries may be in flight at once.

# ANTI-PATTERN: a pool of connections to the same file, which amortises a
# cost that is already near zero — and no bound on concurrent queries, which
# is the resource that actually runs out.
POOL = [duckdb.connect("gis.duckdb") for _ in range(20)]

async def handler(request):
    con = POOL.pop()
    try:
        return con.execute(SPATIAL_QUERY, [request.bbox]).fetchall()
    finally:
        POOL.append(con)
# PATTERN: one database, a cursor per request, and a bound on concurrency.
# The dispatch to a thread is what keeps the event loop free.
async def handler(request):
    async with QUERY_SLOTS:                       # bounds memory, not connections
        return await asyncio.to_thread(_run, request.bbox)

def _run(bbox):
    with cursor() as cur:                         # isolation, shared buffer pool
        return cur.execute("""
            SELECT shop_id, name
            FROM shops
            WHERE geom && ST_MakeEnvelope(?, ?, ?, ?)
              AND ST_Intersects(geom, ST_MakeEnvelope(?, ?, ?, ?))
            LIMIT 200
        """, [*bbox, *bbox]).fetchall()

The LIMIT is not decoration. A request handler that can return an unbounded result set has an unbounded memory profile, and the semaphore bounds how many such queries run rather than how large each one is. Both bounds are needed.

Three resources, three bounds, none of them a connection pool Concurrent queries bound memory, the worker pool and a timeout bound threads, and a LIMIT bounds response size. RESOURCE EXHAUSTED BY BOUND BY memory concurrent heavy queries a query semaphore worker threads long synchronous calls pool size + a timeout response size a query with no limit LIMIT and pagination connections nothing — they are local objects no bound needed

The bottom row is the one a pool addresses, and it was never the constraint.

Sizing the semaphore

The useful bound is derived from memory rather than from cores. Take the memory limit, divide by the peak working set of the heaviest query the service can serve, and round down — that is how many can run at once without exceeding the ceiling. For a service whose heaviest query is a bounded bbox lookup, that number is large; for one that can be asked for a dissolve, it may be two.

The corollary is that a service with a wide range of query costs should not have one bound. Splitting the endpoints into cheap and expensive, with separate semaphores, stops a single heavy request from starving a hundred trivial ones — and it makes the cost model visible in the code rather than hidden in a shared queue.

Diagnostic Queries & Plan Validation

Two measurements tell you whether the bounds are right, and both come from the service rather than from the engine.

# Queue depth and query duration, sampled. Rising queue depth with flat
# duration means the bound is too tight; rising duration means it is too loose.
import time
STATS = {"waiting": 0, "running": 0}

async def handler(request):
    STATS["waiting"] += 1
    async with QUERY_SLOTS:
        STATS["waiting"] -= 1; STATS["running"] += 1
        t0 = time.monotonic()
        try:
            return await asyncio.to_thread(_run, request.bbox)
        finally:
            STATS["running"] -= 1
            _record_duration(time.monotonic() - t0)

Rising queue depth with stable durations means requests are waiting for a slot that the machine could afford to grant. Rising durations mean the concurrent queries are competing, and the bound should tighten rather than loosen.

Two spatial-specific failure modes in a request handler A user-supplied bounding box can request the whole layer; a long scan holds a worker thread that request cancellation does not free. FAILURE HOW IT PRESENTS GUARD an unbounded result set memory exhausted, no single heavy query LIMIT, and validate the bbox area a long scan holding a thread cancellation does not free it a timeout plus an engine interrupt

Both come from the bbox being a user-supplied parameter, which is unusual among API inputs.

Validating the bounding box

A bounding box is an unusual API parameter in that its cost is unbounded and user-controlled: a request for the whole world is syntactically identical to a request for one street. Validating its area against a maximum is the cheapest guard available and it belongs in the handler rather than in the SQL, because rejecting it before a slot is acquired keeps the semaphore free for requests that will succeed.

The second guard is a limit on the result, which catches the case where a small box happens to contain a dense cluster. The two together mean the worst case a handler can produce is bounded by a number you chose rather than by what the client asked for.

MAX_AREA_M2 = 25_000_000            # 5 km × 5 km

def validate(bbox) -> None:
    xmin, ymin, xmax, ymax = bbox
    if (xmax - xmin) * (ymax - ymin) > MAX_AREA_M2:
        raise ValueError("requested area too large; zoom in or use the export endpoint")

Geometry Validation & Fallback Routing

Where a query must be allowed to run long, give it a timeout that reaches the engine rather than only the coroutine.

async def handler_with_timeout(request, seconds: float = 5.0):
    async with QUERY_SLOTS:
        cur = DB.cursor()
        task = asyncio.create_task(asyncio.to_thread(_run_on, cur, request.bbox))
        try:
            return await asyncio.wait_for(asyncio.shield(task), timeout=seconds)
        except asyncio.TimeoutError:
            cur.interrupt()          # reaches the engine; the task then unwinds
            await task               # await it, or the thread leaks
            raise
        finally:
            cur.close()

Frequently Asked Questions

Do I need a connection pool for DuckDB?

No. A connection is a local object rather than a network resource, so there is no handshake to amortise, and connections to the same database share one buffer pool anyway. What a service needs is one database opened at startup, a cursor per request, and a bound on concurrent queries.

What should the concurrency bound be?

Derived from memory: the memory limit divided by the peak working set of the heaviest query the service can serve. For a bounded bbox lookup that number is large; for anything that can be asked for a dissolve it may be two or three. Cores are the wrong denominator, because each query already uses all of them.

Should the database be opened read-only?

Wherever the service does not write, yes. It removes the single-writer lock from the picture, allows other processes to read the same file concurrently, and makes it structurally impossible for a request handler to mutate the data.

How do I stop one request from starving the others?

Split the endpoints by cost and give each its own semaphore. A single shared bound means one dissolve request occupies a slot that a hundred bbox lookups could have used, and the cheap endpoints inherit the latency of the expensive one.

Does cancelling a request stop the query?

Not on its own. Cancelling the asyncio task abandons the waiter while the worker thread and the engine continue. Reaching the engine requires an interrupt on the cursor, after which the task has to be awaited so the thread unwinds — abandoning it leaks the thread and everything it holds.

What is the most common way a spatial API falls over?

An unbounded bounding box. It is a user-supplied parameter whose cost is unbounded, and a request for the whole layer looks syntactically identical to a request for one street. Validating its area before acquiring a slot is the cheapest guard there is.

Up: DuckDB Connection and Concurrency Management