Postgres

PostgreSQL’s work_mem parameter is one of the most impactful yet misunderstood configuration settings for database performance. This post explores how adjusting work_mem can dramatically improve query execution times, especially for operations involving sorting, hashing, and joins. Understanding work_mem work_mem specifies the amount of memory PostgreSQL can use for internal sort operations and hash tables before writing to temporary disk files. The default value is 4MB, which is conservative to ensure PostgreSQL runs on smaller machines. ...

Sizing Pool size ≈ CPU cores * 2–4 per service instance; avoid per-request opens. For PgBouncer tx-mode: disable session features; avoid session-prepared statements. Timeouts & limits Set ConnMaxLifetime, ConnMaxIdleTime, MaxOpenConns, MaxIdleConns. Add statement timeouts; enforce context deadlines on queries. Instrumentation Track pool acquire latency, in-use/idle, wait count, timeouts. Log slow queries; sample EXPLAIN ANALYZE in staging for heavy ones. Hygiene Use prepared statements judiciously; reuse sqlx.Named/pgx prepared for hot paths. Prefer keyset pagination; cap result sizes; parameterize everything. Checklist Pool sized and monitored. Query timeouts set; slow logs reviewed. No per-request connections; connections closed via context cancellation.

Pooling Use a pool (pg/pgbouncer); size = (CPU * 2–4) per app instance; avoid per-request connections. For PgBouncer in transaction mode, avoid session features (temp tables, session prep statements). Query hygiene Parameterize queries; prevent plan cache thrash; set statement timeout. Add indexes; avoid wild % patterns; paginate with keyset when possible. Monitor slow queries; cap max rows returned; avoid huge JSON blobs. App settings Set statement_timeout, idle_in_transaction_session_timeout. Use prepared statements judiciously; for PgBouncer, prefer server-prepared off or use pgbouncer session mode. Pool instrumentation: queue wait time, checkout latency, timeouts. OS/DB basics Keep Postgres on same AZ/region; latency kills. Tune work_mem, shared_buffers, effective_cache_size appropriately (DB side). Use EXPLAIN (ANALYZE, BUFFERS) in staging for heavy queries. Checklist Pool sized and monitored; PgBouncer for many short connections. Query timeouts set; slow logs monitored. Key indexes present; pagination optimized. App-level metrics for pool wait, query latency, error rates.