SQLAlchemy pool with DuckDB dialect: diagnosing intermittent exhaustion across forked workers

The most likely cause of intermittent pool exhaustion in a gunicorn-style environment is the inheritance of the SQLAlchemy Engine and its QueuePool from the parent process. When a process forks, the child inherits the memory state of the parent, including the pool's internal connection tracking. However, the underlying native DuckDB handles are not safely shareable across process boundaries, leading to a state where the pool believes connections are active or available when they are actually stale or invalid.

Fork Safety and Engine Lifecycle

SQLAlchemy engines and pools are not fork-safe. If the engine is instantiated at the module level (before the fork), every worker inherits the same pool state. This often manifests as intermittent exhaustion because the child processes may be attempting to use inherited connections that are logically "checked out" or corrupted, effectively reducing the available pool size to zero or causing deadlocks.

• Recommendation: Create the Engine instance inside the worker process after the fork. In Gunicorn, this is typically handled within the post_fork hook.
• Alternative: If the engine must be defined globally, call engine.dispose() immediately after the fork in each worker. This clears the inherited pool and forces the worker to create its own fresh connections.

DuckDB Connection Release and Rollbacks

DuckDB's silent rollback of uncommitted transactions upon close() affects the transactional state, not the pool availability. A connection is only returned to the SQLAlchemy QueuePool when the SQLAlchemy session or connection object is explicitly closed or garbage collected.

If you are seeing exhaustion while workload is low, it suggests a reference leak. Because DuckDB is an embedded database, a native connection may remain open as long as any derived cursor or result set is still in scope. If your application leaks these objects, the SQLAlchemy connection remains "checked out," regardless of whether a transaction was rolled back.

The Role of pool_pre_ping

Enabling pool_pre_ping=True is generally ineffective for DuckDB. This feature is designed to detect dropped TCP connections to a remote server (e.g., PostgreSQL). Since DuckDB is an in-process library, there is no network socket to "ping." It will not detect or fix the state corruption caused by process forking; only proper engine disposal or per-worker instantiation solves that issue.

Verification Steps

1. Isolate Forking: Run the service with workers=1. If the exhaustion disappears, the issue is confirmed as fork-inheritance of the pool.
2. Trace Checkouts: Enable SQLAlchemy pool logging to identify leaked connections:

   import logging
   logging.getLogger('sqlalchemy.pool').setLevel(logging.DEBUG)

3. Audit Lifecycle: Verify that no Engine is instantiated at the top level of your application module before the web server forks its workers.

Missing Diagnostic: Are you using a file-backed DuckDB database? If so, please specify if workers are connecting in read_only=True mode, as file-locking contention can sometimes mimic pool exhaustion by blocking connection acquisition.