duckdb and postgres connection management and thread safety in a multithreaded, long lived process

[provinzkraut]

I'm trying to optimise DuckDB + Postgres connection handling in a long-lived, multithreaded application, but, after reading the docs, I'm not quite sure what the best way is to put all the moving parts together, so I'm looking for some advice.

Preface

I only ever want to perform reads, through DuckDB into postgres, e.g. conn.sql("SELECT * FROM postgres_scan(...);").fetchall()

Postgres side

The two methods to connect to postgres:

1. ATTACH postgres
2. postgres_scan

From what I can tell, ATTACHing a postgres DB will establish a connection and keep it open, making it not a suitable method for this application, since it would not allow concurrent reads because they'd all share the same postgres connection.

I'm assuming (couldn't find anything about this in the docs, and a cursory look into the source also didn't yield a result) that postgres_scan creates an ad-hoc connection, so it feels like that's what I should use.

DuckDB side

According to this, the connection is inherently not thread safe. It does mention .cursor, but also says this about the cursor:

[...] does not open a new connection. Therefore, all cursors created from one connection cannot run queries at the same time.

This seems to imply that within the same DuckDBPyRelation, effectively no multithreading is possible or safe.

However, there's also this guide, which does show using threads and .cursor for concurrent reads / writes, leaving me a bit confused.

Baring this guide, I would have concluded that what I actually want is some sort of connection pool, with one DuckDBPyRelation per thread, but now I'm not sure what to think.

---

I would appreciate any insights in how this is supposed to be handled, or corrections if I'm missing something really obvious :)

[Michael-WhiteCapData]

Two points resolve most of the apparent contradiction.

First, ATTACH ... TYPE postgres does not force every query through one permanent PostgreSQL connection. The extension has its own connection pool and can open multiple PostgreSQL connections, including several for a single parallel table scan. Current controls include pg_connection_limit and the pool settings documented under PostgreSQL connection pooling.

Second, use one DuckDB client context per Python worker:

root = duckdb.connect(...)
root.execute("""
    ATTACH 'host=... dbname=...' AS pg
    (TYPE postgres, READ_ONLY)
""")

def worker(sql, params=()):
    local = root.cursor()
    try:
        return local.execute(sql, params).fetchall()
    finally:
        local.close()

The cursors share the database/catalog attachment, but each has independent query and transaction state. Do not concurrently execute through root itself. Also finish/commit setup on root before starting the worker contexts.

For a long-lived service, I would prefer the attached catalog over repeating credentials in every postgres_scan: it gives stable names, secret-manager integration, and lets the extension reuse its connection pool. Bound PostgreSQL concurrency explicitly rather than assuming one remote connection per Python thread, for example with the documented pool limit/settings. DuckDB can also parallelize one PostgreSQL scan internally, so Python worker count × DuckDB threads can otherwise create more remote work than expected.

The current extension overview lists pg_connection_cache and pg_connection_limit: PostgreSQL extension settings.

[evertlammerts]

To reiterate: yes, one cursor per thread is safe because each cursor gets independent query state. ATTACH postgres does use a connection pool internally, not a single connection. But do not share Relation objects across threads without synchronization, they'll race.