Session pooling or transaction pooling for low‑traffic PostgreSQL workloads?

Goal: Reduce the operating cost of a low‑traffic PostgreSQL deployment by minimizing idle backend memory consumption through PgBouncer pooling while keeping the application functional.

Constraint: Session pooling preserves temporary tables, prepared statements, and session‑level GUCs but allocates a dedicated backend for each pooled connection, increasing memory use even when idle. Transaction pooling frees backends after each transaction, lowering baseline memory, but discards session state, requiring the application to avoid or recreate temporary objects and prepared statements per transaction. Uncertainty remains about which mode offers the best trade‑off given occasional traffic bursts and the need to preserve compatibility without code changes.

Which pooling mode yields the lowest memory footprint without breaking applications that rely on temporary tables or prepared statements? How can we size the backend pool to avoid latency spikes during infrequent transaction bursts?