Server Configuration
PostgreSQL's runtime behavior is controlled through configuration files rather than code changes — the two you'll touch most often are postgresql.conf for general server settings and pg_hba.conf for controlling who is allowed to connect at all.
postgresql.conf
This is the main configuration file: memory limits, connection limits, logging behavior, autovacuum tuning, replication settings, and more all live here. Most settings take effect on server reload (pg_ctl reload or SELECT pg_reload_conf();); a smaller number require a full server restart.
pg_hba.conf
"HBA" stands for host-based authentication. This file controls who can connect, from where, to which database, using what authentication method — independent of any privileges a role might have once connected. A role can have every privilege in the world and still be refused a connection if pg_hba.conf doesn't allow it.
pg_hba.conf example
# TYPE DATABASE USER ADDRESS METHOD local all postgres peer host storefront app_user 10.0.0.0/16 scram-sha-256 host all all 0.0.0.0/0 reject
Rules are checked top to bottom, and the first matching line wins — so order matters, and it's common to end the file with a broad reject rule as a safety net for anything not explicitly allowed above it.
Key Tunable Settings
Setting | What It Affects |
|---|---|
shared_buffers | How much memory PostgreSQL dedicates to caching table/index data in its own shared memory, separate from the OS page cache. |
work_mem | How much memory a single query operation (a sort, a hash join, etc.) can use before spilling to temporary files on disk — set too low and complex queries slow down; set too high and many concurrent queries can exhaust server memory. |
max_connections | The hard ceiling on how many concurrent client connections the server will accept. |
effective_cache_size | A hint to the query planner about how much memory is realistically available for caching (shared_buffers plus OS cache) — it does not allocate anything itself, it just influences planning decisions. |
Viewing and Changing Settings
show-and-set.sql
-- View a single setting SHOW work_mem; -- View every current setting, with descriptions SELECT name, setting, unit, short_desc FROM pg_settings; -- Change a setting for the CURRENT SESSION only — reverts on disconnect SET work_mem = '64MB'; -- A permanent, server-wide change requires editing postgresql.conf -- (or using ALTER SYSTEM, which writes to a separate override file) -- and then reloading or restarting the server. ALTER SYSTEM SET work_mem = '64MB'; SELECT pg_reload_conf();