I am currently experimenting with a DuckDB / Ducklake setup using postgres as a catalog. I am experimenting with a handful of concurrent writers and many concurrent readers.
To reduce stress on the postgres backend, I'd like to use pgBouncer for connection pooling. I don't understand the ducklake extension well enough to judge what type of pooling to use.

From what I can tell, every query is enclosed in its own transaction with repeatable read. Also, DuckDB is not using postgres sequences, instead it calculates keys locally and simply retries on conflict. From this it looks like transaction pooling would be fine, the limitation would be the growing conflicts with concurrent writers, leading to many retries. However, I also see some temporary tables which makes me question if transaction pooling is possible.

Maybe someone has some deeper insights. Is it safe to use transaction pooling for the readers? Is it safe to use transaction pooling for the writers?

Thanks