A few settings carry most of the weight on a tuned PostgreSQL server. shared_buffers controls the database's own page cache, with a common starting point of about 25% of RAM on dedicated servers, leaving the rest to the operating system page cache. effective_cache_size is a planner hint rather than an allocation—it tells the optimizer how much OS plus database cache is realistically available, typically 50–75% of RAM—and influences index-scan versus sequential-scan decisions. work_mem is per-operation memory for sorts and hash joins; setting it high enough to avoid disk spills on complex queries yet low enough that a hundred concurrent queries do not exhaust RAM is the art, with 16–64 MB being typical. Per-session temp-table work benefits from raising temp_buffers. For diagnosing whether the database is I/O- or cache-bound, track_io_timing costs only a small overhead but unlocks I/O timing in EXPLAIN (BUFFERS, ANALYZE) and pg_stat_statements; pg_buffercache lets you inspect what is currently resident in shared_buffers; pg_stat_io (PG16+) reports per-backend I/O patterns; and a quick sanity check of cache hit rate can be done by computing sum(heap_blks_hit) over sum(heap_blks_hit) + sum(heap_blks_read) over pg_statio_user_tables, with ratios above 0.99 being desirable.
Connection management is the other big lever. PostgreSQL forks a backend process per connection, so large numbers of clients create context-switch and memory pressure. PgBouncer or an application-level pool is the standard answer. PgBouncer offers three pool modes: session, in which a client owns a connection until disconnect; transaction, where the connection is released back to the pool at COMMIT (the most efficient for typical OLTP workloads); and statement, in which the connection is reused after every single statement but which breaks features like prepared statements and session-level settings. The server-side cap is max_connections, adjustable globally and per-database or per-role via ALTER DATABASE / ALTER ROLE. Tablespaces offer another knob: hot tables can be placed on fast SSD storage while archive tables live on cheaper disks, all within a single PostgreSQL cluster. Connection limits and authentication are governed by pg_hba.conf, which decides who can connect from where using what method (trust, md5, scram-sha-256, peer, cert, ident), with scram-sha-256 being the modern default over md5 because it never transmits the password and provides a mutual challenge.