PostgreSQL is the default relational database for many cloud-native applications. Its default configuration is conservative; production performance requires tuning, monitoring, and query optimisation.
The configuration parameters that matter
postgresql.conf defaults are conservative for broad compatibility. The parameters that make a significant difference for production: shared_buffers (typically 25% of RAM for dedicated servers), effective_cache_size (tells the query planner how much memory is available for caching, set to 50-75% of RAM), work_mem (memory per sort operation, increase cautiously as it multiplies by active queries and connections), and max_connections (each connection uses RAM; use PgBouncer for connection pooling at scale).
EXPLAIN ANALYZE for query optimisation
EXPLAIN ANALYZE executes the query and shows the actual execution plan with timing at each node. The critical output: Seq Scan on large tables indicates a missing or unused index. Hash Join on large sets indicates a join optimisation opportunity. Sort nodes consuming significant time indicate missing indexes on ORDER BY or GROUP BY columns. The query planner's estimates vs actual row counts (large discrepancies indicate stale statistics, run ANALYZE).
Index strategy
PostgreSQL supports multiple index types (B-tree, GIN, GiST, BRIN, hash). B-tree is the default and right for most range and equality queries. GIN indexes support full-text search and JSONB containment queries. BRIN (Block Range Index) is extremely small and efficient for naturally ordered data (timestamps in append-only tables). Partial indexes (WHERE clause on the index) dramatically reduce index size for selectively queried subsets of data.
Connection pooling with PgBouncer
PostgreSQL connections are expensive: each connection spawns a backend process. At hundreds of concurrent connections, the process overhead degrades performance. PgBouncer (transaction pooling mode) allows thousands of application connections to share a small pool of PostgreSQL server connections (20-100 is typical). The pool maintains persistent PostgreSQL connections and assigns them to application connections for the duration of a transaction.