PostgreSQL configuration parameters control database behavior and performance. These settings are categorized based on how changes take effect.

Parameter Context Types

Managing Parameters with pg_settings

The system view pg_settings exposes all runtime-configurable parameters:

• name: Parameter name.
• setting: Current value.
• unit: Unit of measurement (e.g., ms, kB).
• category: Logical grouping.
• short_desc, extra_desc: Descriptions.
• context: Indicates required action for changes (see above).
• vartype: Data type (bool, integer, etc.).
• source: Origin of current value (e.g., configuration file, override).
• min_val, max_val: Valid numeric range.
• enumvals: Allowed values for enum types.
• boot_val: Default at server start.
• reset_val: Value restored by RESET in session.
• sourcefile, sourceline: Location in config file (if applicable).
• pending_restart: true if change requires restart.

Viewing Parameters

SHOW maintenance_work_mem;
-- or
SELECT current_setting('maintenance_work_mem');

Modifying Parameters

Session-level:

SET work_mem = '64MB';
-- or
SELECT set_config('work_mem', '64MB', false);

Global persistent changes (via postgresql.auto.conf):

ALTER SYSTEM SET shared_buffers = '1GB';
ALTER SYSTEM RESET shared_buffers;
ALTER SYSTEM RESET ALL;

Changes made with ALTER SYSTEM take effect after reloading (for sighup/backend contexts) or restarting (for postmaster).

Applying Changes

• Reload config (no restart):

  pg_ctl reload
  -- or in SQL:
  SELECT pg_reload_conf();
  

• Restart required: Check pending_restart = true in pg_settings.

Common Parameter Groups

Connection Settings

• listen_addresses: IP addresses to accept connections ('*' for all).
• port: TCP port (default: 5432).
• max_connections: Maximum concurrent connections.
• superuser_reserved_connections: Slots reserved for superusers.
• unix_socket_directories: Path for Unix domain sockets.

Memory Management

• shared_buffers: Shared memory for caching (typically 25% of RAM).
• temp_buffers: Per-session temp buffer size.
• work_mem: Memory for sorts/hashes per operation.
• maintenance_work_mem: Memory for VACUUM, CREATE INDEX, etc.
• max_stack_depth: Maximum safe stack depth for function calls.

Logging

• logging_collector: Enables log file output.
• log_directory, log_filename: Log location and naming.
• log_rotation_age, log_rotation_size: Rotation triggers.
• log_truncate_on_rotation: Overwrite existing files on rotation.
• log_destination: Output target (stderr, csvlog, etc.).

Query Logging

• log_statement: Log none, ddl, or all statements.
• log_min_duration_statement: Log queries exceeding this duration (ms).

WAL and Replication

• wal_level: Controls WAL detail (minimal, replica, logical).
• fsync: Ensures WAL writes reach disk.
• synchronous_commit: Cnotrols transaction commit durability.
• full_page_writes: Writes full page images to WAL on first change post-checkpoint.
• wal_buffers: Memory buffer for WAL records.
• max_wal_size, min_wal_size: WAL file retention bounds.
• wal_keep_size: Minimum WAL retained for standby replication (MB).
• wal_sender_timeout, wal_receiver_timeout: Replication connection timeouts.

Replication Control

• max_wal_senders: Max concurrent replication connections.
• max_replication_slots: Max replication slots.
• synchronous_standby_names: Defines synchronous standbys.
• hot_standby: Allows read queries on standby.
• hot_standby_feedback: Prevents vacuuming of rows needed by standby.

Timeouts

• statement_timeout: Cancel queries exceeding this duration (ms).
• lock_timeout: Fail if lock wait exceeds threshold.
• idle_in_transaction_session_timeout: Terminate idle transactions.
• checkpoint_timeout: Max interval between checkpoints (seconds).
• archive_timeout: Force WAL switch after inactivity (seconds).

Vacuum Behaviro

• vacuum_cost_delay: Sleep when cost limit exceeded.
• vacuum_cost_page_hit/miss/dirty: Cost units for buffer/page operations.
• vacuum_cost_limit: Total cost before delay.

Parallelism and I/O

• max_worker_processes: Total background worker slots.
• max_parallel_workers: Global limit for parallel workers.
• max_parallel_workers_per_gather: Per-query parallel worker cap.
• max_parallel_maintenance_workers: For index builds, etc.
• effective_io_concurrency: Concurrent I/O ops (SSD/NVMe benefit).
• maintenance_io_concurrency: I/O concurrency for maintenance tasks.