PolarDB for PostgreSQL pre-tunes certain parameters from the community edition to deliver better performance and stability in a cloud environment. Some defaults differ from standard PostgreSQL. This reference covers the parameters that affect performance and behavior, organized by category.
Some parameters cannot be modified in the console. Log in to the cluster and use the command line to view and modify them.
Important parameters
The three parameters below have PolarDB-specific defaults that differ from the community edition. Modify them in the console as needed.
synchronous_commit
Controls when a transaction returns success to the client, relative to WAL durability guarantees. Default: off.
Setting details:
`off` (default): Transactions return success without waiting for WAL to reach disk. The maximum data loss window is 3 ×
wal_writer_delay(default: 10 ms). Use this when throughput matters more than strict durability.`on`: Transactions wait for WAL to be written to disk before returning success. Use this when your application requires strict per-transaction durability.
`remote_write`: Transactions wait for WAL to be written to the standby's file system, but not necessarily flushed to disk.
`local`: Transactions wait for WAL to be written to the local disk only, with no standby requirement.
`remote_apply`: Transactions wait for WAL to be applied to the standby's transaction log. This provides the strongest consistency guarantee but increases commit latency.
To check the current setting, run SHOW synchronous_commit;.
wal_level
Controls how much information is written to write-ahead logging (WAL). Default: replica.
Changing this parameter restarts the database. Plan accordingly.
| Value | WAL content | Use when |
|---|---|---|
replica (default) | Supports read-only standby nodes | Standard deployment; reduces WAL volume and improves write performance |
logical | Adds data required for logical replication | Your workload uses logical replication |
To check the current setting, run SHOW wal_level;.
log_statement
Sets which SQL statements are written to the audit log. Default: ddl.
| Value | What gets logged |
|---|---|
none | Nothing |
ddl (default) | DDL statements: CREATE, ALTER, DROP |
mod | DDL and DML statements: INSERT, UPDATE, DELETE |
all | All SQL statements |
Logging fewer statements improves database performance. The SQL Explorer and Audit feature adjusts this parameter automatically: enabling it sets log_statement = all; disabling it sets log_statement = ddl.
To check the current setting, run SHOW log_statement;.
Parameter reference
The following sections list configurable cluster parameters by category.
Connection and authentication
| Parameter | Description | Default | Range | Unit |
|---|---|---|---|---|
authentication_timeout | Maximum time to wait for a client to complete authentication. The server closes the connection if the limit is exceeded, preventing idle clients from holding connection resources. | 60 | 1–600 | seconds |
ssl | Enables Secure Sockets Layer (SSL) encrypted connections. | OFF | ON/OFF | — |
Autovacuum
The autovacuum process reclaims space from dead tuples (rows left by deleted or updated data) and refreshes table statistics. Both functions are critical for maintaining query performance over time.
Even when autovacuum is disabled, the system still triggers vacuum processes to prevent transaction ID wraparound.
| Parameter | Description | Default | Range | Unit |
|---|---|---|---|---|
autovacuum | Enables the autovacuum background process. Requires track_counts = on. | ON | ON/OFF | — |
autovacuum_max_workers | Maximum number of autovacuum worker processes (excluding the launcher) that can run concurrently. | 5 | 1–262,143 | processes |
autovacuum_naptime | Minimum pause between autovacuum rounds on a given database. Each round issues VACUUM and ANALYZE commands for tables that need them. | 30 | 1–2,147,483 | seconds |
autovacuum_vacuum_threshold | Minimum number of changed tuples (inserted, updated, or deleted) required to trigger VACUUM. Applied together with autovacuum_vacuum_scale_factor. | 50 | 0–2,147,483,647 | tuples |
autovacuum_vacuum_scale_factor | Fraction of the table size added to autovacuum_vacuum_threshold to determine the VACUUM trigger point: autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × table row count). | 0.02 | 0–100 | percent |
autovacuum_analyze_threshold | Minimum number of changed tuples required to trigger ANALYZE. Applied together with autovacuum_analyze_scale_factor. | 50 | 0–2,147,483,647 | tuples |
autovacuum_analyze_scale_factor | Fraction of the table size added to autovacuum_analyze_threshold: autovacuum_analyze_threshold + (autovacuum_analyze_scale_factor × table row count). | 0.05 | 0–2,147,483,647 | percent |
autovacuum_freeze_max_age | Maximum transaction ID age a table can reach before a forced VACUUM runs to prevent transaction ID wraparound. The system triggers this vacuum even when autovacuum is disabled. | 500,000,000 | 100,000–2,000,000,000 | transaction IDs |
autovacuum_multixact_freeze_max_age | Maximum multixact ID age before a forced VACUUM runs to prevent multixact ID wraparound. The system triggers this vacuum even when autovacuum is disabled. | 700,000,000 | 10,000–2,000,000,000 | transaction IDs |
autovacuum_vacuum_cost_delay | Cost delay applied to autovacuum operations. Set to -1 to use the vacuum_cost_delay value. | 0 | -1–100 | milliseconds |
autovacuum_vacuum_cost_limit | Total cost limit for autovacuum operations, distributed proportionally across all running worker processes. Set to -1 to use the vacuum_cost_limit value. | 10,000 | -1–10,000 | cost value |
Checkpoints and background writing
| Parameter | Description | Default | Range | Unit |
|---|---|---|---|---|
checkpoint_timeout | Maximum interval between automatic WAL checkpoints. | 30 | 30–86,400 | seconds |
bgwriter_delay | Pause between background writer rounds that flush dirty pages to disk. | 600,000 | 10–10,000 | milliseconds |
backend_flush_after | Number of dirty pages a backend process can write before the OS flushes the file cache to disk. Set to 0 to disable. | 0 | 0–256 | blocks |
full_page_writes | Writes the full content of a data page to WAL on its first modification after a checkpoint. Prevents partial-page corruption after power failures. | OFF | ON/OFF | — |
wal_buffers | Shared memory used to buffer WAL data before it is written to disk. | 16 MB | -1–262,143 (up to 2,097,144 KB) | WAL blocks (8 KB each) |
wal_level | See Important parameters. Changing this parameter restarts the database. | replica | replica/logical | — |
wal_writer_delay | Maximum pause between walwriter flush rounds. | 600,000 | 1–10,000 | milliseconds |
Query planning and execution
| Parameter | Description | Default | Range |
|---|---|---|---|
constraint_exclusion | Controls whether the planner uses CHECK constraints to prune query results. partition applies optimization only to partitioned tables and UNION ALL subqueries. | partition | on/off/partition |
cpu_index_tuple_cost | Planner cost estimate for processing each index entry during an index scan. | 0.005 | 0–1.79769e+308 |
cpu_operator_cost | Planner cost estimate for processing each operator or function in a query. | 0.0025 | 0–1.79769e+308 |
cpu_tuple_cost | Planner cost estimate for processing each row in a query. | 0.01 | 0–1.79769e+308 |
enable_partition_pruning | Enables partition pruning during planning and execution to skip scanning irrelevant partitions. | ON | ON/OFF |
seq_page_cost | Planner cost estimate for fetching a page in a sequential scan. | 1 | 0–1.79769e+308 |
jit | Enables Just-In-Time (JIT) compilation for eligible queries to accelerate execution. | OFF | ON/OFF |
Timeout settings
| Parameter | Description | Default | Range | Unit |
|---|---|---|---|---|
deadlock_timeout | Time a transaction waits for a lock before the system checks for a deadlock. | 1,000 | 0–2,147,483,647 | milliseconds |
idle_in_transaction_session_timeout | Maximum time a session can remain idle inside a transaction. The session is terminated when this limit is exceeded. Set to 0 to disable. | 3,600,000 | 0–2,147,483,647 | milliseconds |
lock_timeout | Maximum time an operation waits to acquire a lock. The operation fails with an error when this limit is exceeded. Set to 0 to wait indefinitely. | 0 | 0–2,147,483,647 | milliseconds |
statement_timeout | Maximum execution time for a single SQL statement. The statement is cancelled when this limit is exceeded. Set to 0 for no limit. | 0 | 0–262,143 | milliseconds |
Logging and auditing
| Parameter | Description | Default | Range | Unit |
|---|---|---|---|---|
logging_collector | Enables the log collector process, which captures and writes log output during cluster operation. | ON | ON/OFF | — |
log_connections | Logs each successful client connection. | OFF | ON/OFF | — |
log_disconnections | Logs each client disconnection. | OFF | ON/OFF | — |
log_error_verbosity | Controls how much detail is included in error log messages. terse: severity, message, and position only. default: adds hints and detail. verbose: all available fields. | default | terse/default/verbose | — |
log_min_duration_statement | Logs any SQL statement that runs longer than the specified threshold. Set to 0 to log all statements. Set to -1 to disable slow query logging. | 5,000 | 0–2,147,483,647 | milliseconds |
log_statement | See Important parameters. | ddl | none/ddl/mod/all | — |
Replication and high availability
| Parameter | Description | Default | Range |
|---|---|---|---|
hot_standby | Allows read-only nodes to accept read-only queries during recovery. | ON | ON/OFF |
hot_standby_feedback | Sends feedback from read-only nodes to the primary node about active queries. When enabled, the primary delays cleaning up row versions still needed by read-only node queries, reducing query conflicts. | ON | ON/OFF |
max_replication_slots | Maximum number of replication slots the cluster supports. | 64 | 0–262,143 |
Resource management
| Parameter | Description | Default | Range | Unit |
|---|---|---|---|---|
dynamic_shared_memory_type | Implementation method for dynamic shared memory. | posix | posix/sysv/mmap | — |
gin_pending_list_limit | Maximum size of the pending list for a Generalized Inverted Index (GIN) index in fast update mode. | 4,096 | 64–2,097,151 | KB |
huge_pages | Requests large memory pages from the operating system when available. | ON | ON/OFF | — |
max_files_per_process | Maximum number of file handles each background process can open simultaneously. | 10,000 | 64–2,147,483,647 | — |
max_locks_per_transaction | Maximum number of locks a single transaction can hold. | 64 | 10–2,147,483,647 | — |
max_prepared_transactions | Maximum number of transactions that can be in the prepared state (two-phase commit protocol) simultaneously. | 800 | 0–262,143 | — |
max_worker_processes | Maximum number of background worker processes the cluster can start, including parallel query workers. | 256 | 0–262,143 | — |
temp_file_limit | Maximum total size of temporary files (used for sorting, hashing, and similar operations) a single backend process can create. Set to -1 for no limit. | 104,857,600 (100 GB) | -1–2,147,483,647 | KB |
Formatting and behavior
| Parameter | Description | Default | Range |
|---|---|---|---|
datestyle | Display format for date and time values. Combine an output format (ISO, Postgres, SQL, or German) with a date order (DMY, MDY, or YMD). | ISO,YMD | See description |
default_with_oids | Deprecated. The current version no longer supports OIDs, so this can only be set to OFF. | OFF | OFF only |
extra_float_digits | Number of extra significant digits displayed for floating-point values beyond standard precision. Higher values produce more accurate output. | 0 | -15–3 |
fsync | Forces data changes to be flushed from the OS cache to disk after operations such as COMMIT. Disabling this improves write performance but risks data corruption on a system crash. | ON | ON/OFF |
timezone | Time zone used to display and interpret timestamps in the session. For available values, see the parameter modification range in the console. | UTC | Standard time zone names, e.g., Asia/Shanghai, UTC |