postgresql.conf Deep Dive

Read, understand, and tune PostgreSQL parameters through pg_settings — and know which changes take effect immediately

PostgreSQL has over 300 configuration parameters. They control memory allocation, connection limits, WAL behaviour, query planning, logging verbosity, and much more. All of them are visible through the pg_settings system view — with their current value, their source (where the current value came from), their unit, and critically, their context (what is required for a change to take effect).\n\nThe context column is the key to safe administration. A parameter with context=postmaster requires a full server restart. One with context=sighup only needs a reload (pg_reload_conf()). One with context=superuser can be changed per-session with SET.\n\nALTER SYSTEM writes to postgresql.auto.conf — a file that is always read after postgresql.conf and therefore takes precedence. It keeps your hand-crafted postgresql.conf clean while still persisting changes across restarts.

Querying pg_settings

pg_settings exposes every parameter with its current value, source, unit, and context.

-- Find all parameters related to memory
SELECT name, setting, unit, context, source
FROM pg_settings
WHERE name LIKE '%mem%'
ORDER BY name;

The context Column

| context | How to apply the change | |---------|------------------------| | postmaster | Restart the server | | sighup | Reload: SELECT pg_reload_conf() | | superuser | SET command (session-level, superuser only) | | user | SET command (session-level, any user) |

SHOW — Read a Single Parameter

SHOW work_mem;
SHOW max_connections;
SHOW shared_buffers;

ALTER SYSTEM — Persist a Change

ALTER SYSTEM writes to postgresql.auto.conf, which is read after postgresql.conf.

-- Increase work_mem to 64 MB
ALTER SYSTEM SET work_mem = '64MB';

-- Apply immediately for sighup parameters
SELECT pg_reload_conf();
SELECT pg_sleep(0.1);

-- Revert to default
ALTER SYSTEM RESET work_mem;

The source Column

pg_settings.source tells you where the current value came from:

default — never explicitly set
configuration file — from postgresql.conf or postgresql.auto.conf
session — overridden with SET in this session
database / role — set with ALTER DATABASE/ROLE

pg_settings

A system view that exposes every PostgreSQL configuration parameter as a row. Key columns: name (parameter name), setting (current value), unit (e.g. kB, ms), context (what is needed to apply a change), source (where the current value came from), and pending_restart (true if the parameter was changed but needs a restart to take effect). It is the authoritative source of truth for live configuration.

postgresql.auto.conf

A configuration file created and maintained exclusively by ALTER SYSTEM. It lives in PGDATA alongside postgresql.conf and is always read after it, so its values take precedence. It should never be edited by hand — ALTER SYSTEM manages it atomically. This keeps your human-maintained postgresql.conf clean while still persisting programmatic changes across restarts.

🔌 Connect as postgres

Connect to the postgres database as the postgres superuser. ALTER SYSTEM requires superuser privileges.

psql -U postgres

psql (18.4) Type "help" for help. postgres=#

🔍 Explore pg_settings

Query pg_settings for all memory-related parameters. Look at name, setting, unit, context, and source — these four columns tell you everything you need to manage a parameter.

SELECT name, setting, unit, context, source FROM pg_settings WHERE name LIKE '%mem%' ORDER BY name;

📟 SHOW a Single Parameter

Use SHOW to retrieve the current value of work_mem. SHOW is the quickest way to check a single parameter during a live session.

SHOW work_mem;

work_mem ---------- 4MB (1 row)

✏️ ALTER SYSTEM — Persist a Change

Use ALTER SYSTEM SET to raise work_mem to 16MB. This writes to postgresql.auto.conf and survives restarts. Then call SELECT pg_reload_conf(); to load the change. work_mem has user context: it can also be changed with SET in a session, and this persistent change requires a reload, not a restart. Wait for the reload to return true before continuing.

ALTER SYSTEM SET work_mem = '16MB';

SELECT pg_reload_conf();

ALTER SYSTEM pg_reload_conf ---------------- t (1 row)

✅ Verify the Change

Confirm the new value took effect by querying pg_settings for work_mem. The row must show setting 16384, unit kB, and source "configuration file". If it still shows 4096 or "default", the change has not taken effect: reload the configuration and run the query again.

SELECT name, setting, unit, source FROM pg_settings WHERE name = 'work_mem';

🔎 Find Every Non-Default Parameter

Before tuning anything further, audit the server: query pg_settings for every parameter whose source is not "default". This tells you exactly what has already been customized on this cluster.

SELECT name, setting, unit, source FROM pg_settings WHERE source != 'default' ORDER BY name;

🐢 Tune log_min_duration_statement

Reload signals are asynchronous. After requesting the reload, wait briefly before reading the active setting. If SHOW still reports the old value, run it again.

Use ALTER SYSTEM to log any statement that takes 500 milliseconds or longer — the standard first step in finding slow queries. Run SELECT pg_reload_conf(); and confirm it returns true. Then run SHOW log_min_duration_statement; and confirm it reports 500ms. ALTER SYSTEM alone only writes the override; it does not activate the logging threshold.

ALTER SYSTEM SET log_min_duration_statement = 500;

SELECT pg_reload_conf();

SELECT pg_sleep(0.1);

SHOW log_min_duration_statement;

ALTER SYSTEM pg_reload_conf ---------------- t (1 row) log_min_duration_statement ----------------------------- 500ms (1 row)

↩️ Reset to Default

Use ALTER SYSTEM RESET work_mem; to remove the persisted override. The active value stays at 16MB until you run SELECT pg_reload_conf();. Confirm the reload returns true, then run SHOW work_mem; and check that it reports 4MB. In this lab the underlying value is the built-in default; other servers may have a value in postgresql.conf.

ALTER SYSTEM RESET work_mem;

SELECT pg_reload_conf();

SELECT pg_sleep(0.1);

SHOW work_mem;

ALTER SYSTEM pg_reload_conf ---------------- t (1 row) work_mem ---------- 4MB (1 row)

Lab 2.1.2 complete. You can now read, change, and verify PostgreSQL configuration:\n\n\n pg_settings : ✅ live view of all parameters\n Non-default audit : ✅ source != default\n SHOW <param> : ✅ quick single-parameter check\n ALTER SYSTEM SET : ✅ persist change to auto.conf\n log_min_duration_statement : ✅ slow-query logging tuned\n pg_reload_conf() : ✅ apply sighup-context changes\n ALTER SYSTEM RESET : ✅ remove override\n

Enable JavaScript to run the live terminal and track your progress.