PostgreSQL Preset Options Settings Query Guide

PostgreSQL Preset Options Settings Query Guide

This PostgreSQL query retrieves preset option settings from the pg_settings system view. Preset options are read-only, compiled-in values that describe fundamental characteristics of the PostgreSQL installation — they cannot be changed at runtime.

Purpose and Overview

Preset options expose compile-time constants and build-time decisions that are baked into the PostgreSQL binary. Unlike tunable parameters, these values are fixed for the life of the installation and can only change by reinstalling or recompiling PostgreSQL.

Knowing these values is essential for understanding storage layout, compatibility constraints, and the capabilities of the installed binary.

SQL Script

 1SELECT
 2    name,
 3    setting,
 4    -- category,
 5    vartype,
 6    short_desc,
 7    -- enumvals,
 8    source,
 9    min_val,
10    max_val,
11    boot_val,
12    reset_val,
13    -- not available on PostgreSQL < 9.5
14    pending_restart
15FROM
16    pg_settings
17WHERE
18    -- Preset Options
19    category ILIKE '%Preset Options%';

Code Breakdown

Filter Condition

1WHERE category ILIKE '%Preset Options%'

Selects only rows belonging to the Preset Options category in pg_settings. The ILIKE operator makes the match case-insensitive.

Selected Columns

name: The configuration parameter name such as block_size or server_version_num.

setting: The current value — for preset options this is always the compiled-in value.

vartype: The data type (integer, bool, string, etc.).

short_desc: A brief description of what the parameter represents.

source: Always default for preset options since they cannot be overridden.

min_val / max_val: Typically NULL for preset options as they are not configurable.

boot_val: The compiled-in default, which for preset options equals the current value.

reset_val: Same as the current value for preset options.

pending_restart: Always false for preset options since they are not configurable.

Key Preset Parameters

block_size — The size in bytes of a disk block (page). Typically 8192 bytes (8 KB). Affects storage layout and I/O behaviour. Changing this requires a full cluster rebuild.

wal_block_size — The size of a WAL disk block, also typically 8192 bytes. Determines WAL write granularity.

segment_size — The number of blocks per segment file, which determines the maximum size of individual data files (typically 1 GB).

wal_segment_size — The number of blocks per WAL segment file. In PostgreSQL 11+ this is a size in bytes (default 16 MB); earlier versions expressed it as a block count.

server_version — The PostgreSQL version string, for example 14.5.

server_version_num — The numeric version, for example 140005. Useful for conditional logic in scripts that need to branch by version.

data_checksums — Whether data page checksums are enabled on this cluster. Set at initdb time and cannot be changed without pg_checksums offline.

data_directory_mode — The Unix file permissions mode of the data directory.

integer_datetimes — Whether timestamps are stored as 64-bit integers (always true in modern PostgreSQL).

max_function_args — Maximum number of arguments a function can accept (typically 100).

max_identifier_length — Maximum length of SQL identifiers such as table and column names (typically 63 bytes).

max_index_keys — Maximum number of columns in a composite index (typically 32).

ssl_library — The SSL library used to build this PostgreSQL binary (e.g., OpenSSL).

Practical Applications

Compatibility Verification

Before running scripts that depend on specific block sizes or version features, query preset options to confirm the installation meets requirements.

Cross-Server Comparison

When comparing two PostgreSQL instances, diffing their preset options confirms they were built with the same compile-time settings — important for replication pairs and migration targets.

Documenting Infrastructure

Include preset option output in server documentation or runbooks to capture the fixed characteristics of each database server.

Checksum Status

Query data_checksums to verify whether corruption detection is enabled on the cluster, a common compliance and reliability requirement.

Version Compatibility

Requires PostgreSQL 9.5 or later due to the pending_restart column. Tested on PostgreSQL 9.5, 9.6, 10.x, 11.x, 12.x, and 13.0.

Best Practices

Capture at provisioning time: Record preset options when a server is first set up. They should never change, so any change detected later warrants investigation.

Verify checksum status: Always check data_checksums on production servers. Clusters without checksums cannot detect silent data corruption.

Use server_version_num in scripts: Prefer the numeric form for version comparisons in automation — it is easier to compare with ::int casting than parsing the string form.

References

• PostgreSQL Documentation - Preset Options - Official reference for all preset option parameters
• PostgreSQL Documentation - pg_settings View - Detailed reference for the pg_settings catalog view
• Source SQL Script - postgres_settings_preset.sql - Original script by Hari Sekhon