PostgreSQL Database Configuration and Session Settings Cheat Sheet

SELECT current_setting('server_version_num') AS server_version_num;

SELECT name, setting, unit, vartype, context, source, sourcefile, sourceline,
       min_val, max_val, enumvals, boot_val, reset_val, pending_restart
FROM pg_settings
WHERE name IN (
  'statement_timeout',
  'work_mem',
  'shared_buffers',
  'max_wal_senders'
)
ORDER BY name;

Note: pg_settings.setting uses the parameter's implicit unit. Preserve unit-qualified values in automation to avoid ambiguous conversions.

BEGIN READ ONLY;
SET LOCAL statement_timeout = '2min';
SET LOCAL work_mem = '256MB';

SELECT customer_id, count(*) AS event_count
FROM app.events
WHERE occurred_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'
GROUP BY customer_id
ORDER BY event_count DESC
LIMIT 100;

COMMIT;

Note: work_mem can be consumed by multiple plan nodes and parallel workers. Choose a value from workload tests, not from one query's apparent input size.

ALTER ROLE report_reader
IN DATABASE appdb
SET default_transaction_read_only = on;

ALTER ROLE report_reader
IN DATABASE appdb
SET statement_timeout = '15s';

SELECT r.rolname, d.datname, s.setconfig
FROM pg_db_role_setting AS s
JOIN pg_roles AS r ON r.oid = s.setrole
JOIN pg_database AS d ON d.oid = s.setdatabase
WHERE r.rolname = 'report_reader';

Note: Reconnect as the target role to verify effective values. Read-only mode is a safety default, not a complete privilege boundary.

-- Run outside an explicit transaction.
ALTER SYSTEM SET log_min_duration_statement = '1s';
SELECT pg_reload_conf();

SELECT name, setting, context, source, sourcefile, pending_restart
FROM pg_settings
WHERE name = 'log_min_duration_statement';

-- Rollback procedure:
ALTER SYSTEM RESET log_min_duration_statement;
SELECT pg_reload_conf();

Note: ALTER SYSTEM success only confirms that the file was written. Verify the effective source and value after reload on the intended server.

SELECT sourcefile, sourceline, seqno, name, setting, applied, error
FROM pg_file_settings
WHERE error IS NOT NULL OR NOT applied
ORDER BY sourcefile, sourceline;

SELECT name, setting, unit, context, source, pending_restart
FROM pg_settings
WHERE name IN ('shared_buffers', 'max_connections', 'max_wal_senders')
ORDER BY name;

Note: An unapplied row without an error can simply be overridden by a later entry. Review seqno and every include file before removing it.

GRANT SET ON PARAMETER statement_timeout TO app_operator;

SELECT p.parname AS parameter_name,
       pg_get_userbyid(a.grantee) AS grantee,
       a.privilege_type, a.is_grantable
FROM pg_parameter_acl AS p
CROSS JOIN LATERAL aclexplode(p.paracl) AS a
WHERE p.parname = 'statement_timeout';

REVOKE SET ON PARAMETER statement_timeout FROM app_operator;

Note: pg_parameter_acl and parameter GRANT require PostgreSQL 15 or later. Gate migrations by server_version_num when supporting older releases.

SELECT name, setting, unit, context, source, pending_restart
FROM pg_settings
WHERE name IN (
  'max_connections', 'shared_buffers', 'work_mem', 'maintenance_work_mem',
  'max_worker_processes', 'max_wal_senders', 'max_replication_slots',
  'wal_level', 'synchronous_commit'
)
ORDER BY name;

Note: Do not copy values mechanically between primary and standby. Recovery, archival, read-only workload, and promotion requirements can justify documented differences.