PostgreSQL is a powerful and highly configurable database system, offering extensive options to optimize performance, enhance security, and control system behavior. One of the most important aspects of PostgreSQL configuration is understanding parameter contexts. These contexts determine when and how each configuration parameter can be modified, whether at startup, per session, or dynamically while the system is running.
In this blog, we’ll take a deep dive into PostgreSQL parameter contexts, explore the different types, and highlight commonly used parameters with practical examples, helping DBAs and developers make informed configuration choices.
What is a Parameter Context in PostgreSQL?
In PostgreSQL, every configuration parameter has a context, which defines when and by whom it can be modified. This ensures that critical settings are adjusted safely, preventing unexpected behavior or system instability.
You can explore all parameters and their contexts using the `pg_settings` system view:
SELECT name, context, setting
FROM pg_settings;
The `pg_settings` view provides detailed information about every configuration parameter.
You can view the structure and query of this view by this command
\d+ pg_settings
Here’s its structure along with the importance of each column:
View "pg_catalog.pg_settings"
Column | Type | Description
-----------------+---------+------------------------------------------------------------
name | text | The name of the configuration parameter.
setting | text | The current value of the parameter.
unit | text | The unit of measurement (e.g., kB, ms) if applicable.
category | text | The category/group the parameter belongs to (e.g., Memory, WAL).
short_desc | text | A brief description of the parameter's purpose.
extra_desc | text | A more detailed explanation or usage notes.
context | text | Defines when and how the parameter can be changed (e.g., postmaster, superuser, session).
vartype | text | Data type of the parameter (e.g., integer, string, bool).
source | text | Where the current value was set (default, configuration file, SQL command, etc.).
min_val | text | Minimum allowed value for the parameter.
max_val | text | Maximum allowed value for the parameter.
enumvals | text[] | List of allowed values if the parameter is enumerated.
boot_val | text | The value used at server startup before any overrides.
reset_val | text | The value returned to when using `RESET` commands.
sourcefile | text | Path of the configuration file if the parameter was set there.
sourceline | integer | Line number in the configuration file where it was set.
pending_restart | boolean | Indicates if a server restart is required for changes to take effect.
Understanding each column in `pg_settings` allows DBAs and developers to safely and effectively tune PostgreSQL configurations for performance, stability, and security.
1. Postmaster Context
Definition:
Parameters with the postmaster context can only be set before the PostgreSQL server starts. These parameters are critical to the server’s operation and require a restart to take effect.
Examples:
- port – Defines the TCP port PostgreSQL listens on.
# postgresql.conf
port = 5432
- max_connections – Sets the maximum number of concurrent connections.
max_connections = 200
- shared_buffers – Allocates memory for caching data pages in RAM.
shared_buffers = 4GB
Use Case: These settings must be decided at startup because changing them on-the-fly can break running connections or memory allocation.
2. Superuser-Backend Context
Definition:
Parameters in this context can only be modified by a superuser and affect the current backend session.
Examples:
- log_statement – Controls which SQL statements are logged.
SET log_statement = 'all';
- search_path – Defines the schema search order.
SET search_path = schema1, schema2;
Use Case: Allows superusers to modify parameters for the session without affecting the entire server.
3. User Context
Definition:
Parameters in the user context can be changed by any role, for their current session.
Examples:
- Default transaction isolation – Defines the transaction isolation state for postgres
SET default_transaction_isolation = 'serializable';
- work_mem – Memory used for sorting operations in a session.
SET work_mem = '64MB';
- application_name – Sets the application name visible in server logs and statistics.
SET application_name = 'MyApp';
Use Case: Gives flexibility to users to adjust parameters according to their session requirements.
4. Internal Context
Definition:
These parameters are used internally by PostgreSQL and cannot be set by users. They control low-level behaviors of the system.
Examples:
- debug_print_parse – Used for debugging the query parser.
- debug_print_rewritten – Logs rewritten queries for internal use.
- debug_pretty_print – Formats debug output for readability.
Note: You won’t find these in pg_settings on standard Ubuntu/PostgreSQL installations because they are debug-only, internal parameters not included in production builds.
Use Case: Mainly for developers or DBAs debugging PostgreSQL internals.
5. Backend Context
Definition:
Parameters in this context can be changed for a single backend session, but not globally. They do not require superuser privileges.
Examples of Backend Parameters:
- log_min_duration_statement – Logs queries that exceed a specified duration.
SET log_min_duration_statement = 1000; -- 1000 milliseconds
- effective_cache_size – Provides an estimate of memory available for caching, helping the planner make better query optimization decisions.
- statement_timeout – Aborts statements that run longer than a specified time to prevent runaway queries:
SET statement_timeout = '5s'; -- Aborts queries running longer than 5 seconds
Use Case: Backend parameters are ideal when you want to tune performance or behavior for individual sessions without impacting other users or requiring administrative privileges.
6. SIGHUP Context
Definition:
Parameters with the sighup context can be changed by sending a SIGHUP signal to the PostgreSQL server. This reloads the configuration without restarting the server.
Examples:
- log_destination – Defines where logs are sent.
- logging_collector – Enables or disables the logging collector.
- log_line_prefix – Sets the format of log lines.
Use Case: Useful for changing logging and monitoring settings on a live server without downtime.
7. Superuser Context
Definition:
These parameters can only be modified by a superuser, either for the current session or globally.
Examples:
- autovacuum – Enables or disables the autovacuum feature.
SET autovacuum = off;
- max_wal_size – Controls the maximum size of WAL files.wal_level – Sets the level of WAL logging.
Use Case: Protects critical parameters that could affect database consistency or performance.
Conclusion
Understanding PostgreSQL parameter contexts is essential for safe and effective database configuration. Each context defines when and by whom a parameter can be modified, ensuring that changes are applied without compromising system stability or performance.
By knowing the right context for each parameter, DBAs and developers can:
- Tune PostgreSQL safely at the session level or server startup.
- Optimize performance for specific workloads.
- Avoid unexpected downtime or misconfigurations.
- Debug and monitor PostgreSQL effectively.
Whether you are a database administrator, developer, or PostgreSQL enthusiast, mastering parameter contexts empowers you to make informed decisions and fully leverage PostgreSQL’s configurability for robust and efficient database operations.