Because DuckDB uses file-level locking, and unlike Postgres it has no connection pooler and no lock manager. File locking is the only concurrency control it has.
Two things follow. In read-only mode, multiple connections coexist fine. But if any process opens the file in write mode, even briefly, such as a data import script, it blocks all other connections until it releases the lock. And if a process crashes or is killed while holding a write lock, it can leave behind a stale .wal (write-ahead log) file that blocks every subsequent connection.
The architectural answer is to make sure the web server never needs write access. Open read-only for anything web-facing, and keep writes to offline processes (imports, migrations) that run one at a time and close their connection when done.
To recover from a stale .wal that is blocking read-only connections: open the database in write mode once to flush the WAL, then close and switch back to read-only. Deleting the .wal file is a last resort and may lose data. Worth adding to your health checks: monitor for the presence of .wal files, because this fails quietly until every connection is blocked.
Full DuckDB checklist: https://www.tigzig.com/security/duckdb. Related: https://www.tigzig.com/agents-faq/how-to-secure-duckdb-behind-a-public-api.
← All Agents FAQ