Memory settings and connection pooling

PostgreSQL uses a process per connection. Each backend costs memory and adds contention, so throughput peaks at a modest number of active connections (often a few times the CPU core count) and degrades beyond it.

Key memory settings:

• shared_buffers: PostgreSQL's page cache shared by all backends. Typical start: ~25% of RAM. The OS page cache holds much of the rest, which is why effective_cache_size (a planner hint, not an allocation) is set to ~50–75% of RAM.
• work_mem: memory per sort or hash operation, per query node, per parallel worker. A complex query can use several multiples of it, times every connection, so the global value stays modest (4–64 MB) and you raise it per session or role for reporting jobs.
• maintenance_work_mem: for VACUUM, CREATE INDEX; larger values speed them up.
• max_connections: keep it in the hundreds, not thousands.

Other first-pass settings: random_page_cost = 1.1 on SSDs, checkpoint_timeout/max_wal_size to spread checkpoints, and PostgreSQL 18's io_method for asynchronous I/O.

Connection pooling: application pools are per process, and with many app instances or serverless functions the total explodes. PgBouncer in transaction mode multiplexes thousands of client connections onto a small server pool. Trade-off: session state (session-level SET, advisory locks, LISTEN) doesn't carry across transactions; recent PgBouncer versions support protocol-level prepared statements.