08 — Configuring PostgreSQL: The Three Config Files

"Edit a file and hope" was my configuration strategy, which made every failed restart a scavenger hunt. The framing that organized it: Postgres behavior is governed by three config files, each with a single job, and a service manager restarts the process to apply them [1]. Once I knew which file owns which concern, configuration stopped being guesswork and became three focused checklists: runtime knobs (postgresql.conf), who-can-connect rules (pg_hba.conf), and OS-user mapping (pg_ident.conf).

The three files, one job each

• postgresql.conf — the runtime knobs. Memory (shared_buffers, work_mem), connections (max_connections), WAL, logging, autovacuum, and the planner cost constants. The file is large, but most of it stays at sensible defaults; I touch maybe a dozen lines [1].
• pg_hba.conf (Host-Based Authentication) — who can connect, from where, to which database, as which user, and by which auth method [4]. The rules are matched top-down; the first matching line wins.
• pg_ident.conf — maps operating-system usernames to database roles, used only when the auth method is peer or ident.

Managing the process

A config change doesn't take effect until the server reloads or restarts [2][3]. Three tools do this, depending on the install:

• systemd — systemctl restart postgresql (or reload for non-restart-needed changes). The standard on modern Linux.
• pg_ctl — the lower-level utility: pg_ctl restart -D /var/lib/postgresql/data.
• pg_ctlcluster — Debian/Ubuntu's wrapper for managing multiple clusters side by side.

The distinction between reload and restart matters: reload re-reads config without dropping connections (for most postgresql.conf changes); restart cycles the process (needed for a few settings like shared_buffers). Many settings can also be changed at runtime with ALTER SYSTEM SET ... without editing the file directly.

The knobs worth knowing

A handful of postgresql.conf settings cover most tuning needs:

• shared_buffers — the shared memory cache, typically 25% of RAM. The most impactful single setting.
• work_mem — per-sort, per-hash memory before spilling to disk. Small increases can hugely help big analytical queries.
• maintenance_work_mem — memory for VACUUM and index builds; set higher than work_mem.
• max_connections — raise carefully; each connection is a backend process. Better to pool with PgBouncer than to set this in the thousands.
• effective_cache_size — tells the planner how much OS cache is available; set to ~50–75% of RAM so the planner picks index-friendly plans.

These are starting points, not dogma. The roadmap's guidance — measure, change one knob at a time, measure again — is the whole method.

Logging: the diagnostic feed

Logging is configured in postgresql.conf, and it's the feed every analysis tool consumes [5]. The settings I make sure are on:

• logging_collector = on — capture logs to files instead of stderr.
• log_line_prefix — include timestamp, pid, database, user, and (critically) the query duration tag.
• log_min_duration_statement — log any statement slower than N milliseconds (the "slow query log").

With those, the log files become the input for tools like PgBadger (which turns logs into HTML reports of slow queries, errors, and load patterns) and the basis for the performance work covered later.

Extensions: Postgres as a platform

The configuration chapter also includes extensions — loadable modules that add types, functions, or features [6]. Extensions are installed with CREATE EXTENSION and turn Postgres into a platform:

• pg_stat_statements — query execution statistics (essential for finding slow queries).
• pgcrypto — cryptographic functions.
• uuid-ossp — UUID generation.
• postgis — geographic/spatial types and indexing.
• pg_buffercache, pgrowsecurity, the pg_trgm trigram index for fuzzy text search.

The habit: before writing custom logic, check whether an extension already does it. The Postgres extension ecosystem is one of its real competitive advantages.

How I use this

The three-files model is the habit. When something behaves unexpectedly, I ask which file governs it: connection refused or wrong auth → pg_hba.conf; memory or planner behavior → postgresql.conf; OS-user mismatch → pg_ident.conf. I change one setting at a time and use ALTER SYSTEM SET for runtime changes to avoid manual file edits. Logging is the first thing I turn on with log_min_duration_statement set — without it, tuning is guesswork. And on any new cluster, I install pg_stat_statements immediately, because every later performance conversation depends on it.

References

[1] PostgreSQL Global Development Group, "Runtime Configuration," 2024. [Online]. Available: https://www.postgresql.org/docs/current/runtime-config.html

[2] DigitalOcean, "What is systemd?," 2023. [Online]. Available: https://www.digitalocean.com/community/tutorials/what-is-systemd

[3] PostgreSQL Global Development Group, "pg_ctl," 2024. [Online]. Available: https://www.postgresql.org/docs/current/app-pg-ctl.html

[4] PostgreSQL Global Development Group, "The pg_hba.conf file," 2024. [Online]. Available: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

[5] BetterStack, "PostgreSQL logging: everything you need to know," 2024. [Online]. Available: https://betterstack.com/community/guides/logging/how-to-start-logging-with-postgresql/

[6] PostgreSQL Global Development Group, "CREATE EXTENSION," 2024. [Online]. Available: https://www.postgresql.org/docs/current/sql-createextension.html