Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
PostgreSQL configuration parameters, also called Grand Unified Configuration (GUC) parameters, control database behavior. They define settings for connections, memory use, query planning, and maintenance.
Some PostgreSQL GUC defaults in Azure HorizonDB depend on your cluster's SKU, including its available vCPUs and memory. A default value on one SKU might differ from the default on another. Review these settings when you choose a SKU or change compute size.
SKU-dependent parameters
The following table lists parameters and their SKU-dependent default values. Parameter availability also depends on the PostgreSQL version.
| Parameter | Description | SKU-dependent value |
|---|---|---|
max_connections |
Maximum number of concurrent connections to PostgreSQL. | 128 for 2 vCores; 256 for 4 vCores; 1,024 for 6 vCores and above. |
shared_buffers |
Memory allocated to the shared buffer cache for database pages. | 60% of RAM for 2 to 4 vCores; 72% of RAM for 6 vCores and above. |
effective_cache_size |
Query planner estimate of the memory available for caching data. This setting doesn't allocate memory. | The shared_buffers size expressed in 8-KiB pages. For example, for 2 vCores, shared_buffers is approximately 9.6 GiB (60% of 16 GiB RAM), or 1,258,240 pages. |
maintenance_work_mem |
Memory budget for maintenance operations, such as VACUUM and CREATE INDEX. |
64 MiB per vCPU, with a 128 MiB minimum and 4 GiB maximum. |
work_mem |
Base memory budget for query operations, such as sorts and hash tables, before they write to temporary files. | 1 MiB per vCPU, with a 2 MiB minimum and 128 MiB maximum. |
autovacuum_max_workers |
Maximum number of autovacuum workers that can run concurrently. | For up to 32 vCores: floor(log2(vCPUs)) + 1. For 64 vCores and above: floor(vCPUs / 16) + 4. Minimum of 2 workers. |
autovacuum_vacuum_cost_limit |
Vacuum cost limit that controls how much work autovacuum performs before a cost-based delay. | See the autovacuum vacuum cost limit table. |
autovacuum_work_mem |
Memory budget for each autovacuum worker. | 2% of RAM, with a 128 MiB minimum and 4 GiB maximum. |
vacuum_buffer_usage_limit |
Size of the shared-buffer access strategy used by VACUUM and ANALYZE. Available in PostgreSQL 16 and later. |
2 MiB per vCPU, with a 16 MiB minimum and 128 MiB maximum. |
autovacuum_worker_slots |
Number of backend slots reserved for autovacuum workers. Available in PostgreSQL 18 and later. | 16 slots for fewer than 128 vCores; 24 slots for 128 vCores and above. |
wal_buffers |
Shared memory used to hold write-ahead log (WAL) data that hasn't yet been written to disk. | 128 MiB. |
max_wal_size |
Soft limit on WAL size before PostgreSQL triggers an automatic checkpoint. Review this setting with your storage configuration, not vCPU count alone. | 12 GiB. |
maintenance_io_concurrency |
Number of concurrent I/O operations PostgreSQL uses for maintenance work. | 32 for up to 16 vCores; 64 for 32 vCores and above. |
Don't assume that every parameter value changes with every SKU change. Storage configuration, PostgreSQL version, and parameter overrides can also affect the values that apply to your cluster.
Autovacuum vacuum cost limit
The default value of autovacuum_vacuum_cost_limit depends on the number of vCores in your SKU.
| vCores | autovacuum_vacuum_cost_limit |
|---|---|
| 2 | 400 |
| 4 | 600 |
| 8 | 1,000 |
| 16 | 1,600 |
| 32 | 2,400 |
| 64 | 3,600 |
| 96 | 4,800 |
| 128 | 6,000 |
| 192 | 8,000 |
Check your cluster's settings
Check the effective parameter values on your cluster instead of copying defaults from a different SKU. Depending on the parameter, a parameter group, database, role, or session override can affect the active value.
Connect to your Azure HorizonDB cluster with
psqlor another PostgreSQL client. For connection instructions, see Connect and query.Run this read-only query to check all the parameters in the table:
SELECT name, current_setting(name) AS current_value, source, pending_restart FROM pg_settings WHERE name IN ( 'max_connections', 'shared_buffers', 'effective_cache_size', 'maintenance_work_mem', 'work_mem', 'autovacuum_max_workers', 'autovacuum_vacuum_cost_limit', 'autovacuum_work_mem', 'vacuum_buffer_usage_limit', 'autovacuum_worker_slots', 'wal_buffers', 'max_wal_size', 'maintenance_io_concurrency' ) ORDER BY name;
The query returns only parameters available in your PostgreSQL version. For example, autovacuum_worker_slots doesn't appear in PostgreSQL 17 results.
Interpret the results
Use the following columns to check the active configuration:
| Column | Description |
|---|---|
current_value |
Active value for the current session, formatted like the output of SHOW. Memory values include units when applicable. |
source |
PostgreSQL configuration source, such as a configuration file, database, role, or session setting. This column doesn't identify the SKU or parameter group name. |
pending_restart |
true means a configuration change requires a PostgreSQL restart before it takes effect. The query still shows the currently active value. |
These values are configuration values, not measurements of current memory use. Some values indicate inheritance. For example, an autovacuum_work_mem value of -1 inherits maintenance_work_mem, and an autovacuum_vacuum_cost_limit value of -1 inherits vacuum_cost_limit.
Save the results before a SKU change, then run the query again after the change completes. Compare results from the same database and role, with the same session overrides, so that configuration overrides don't obscure SKU-related differences.
For information about viewing and customizing parameters, see Parameters in Azure HorizonDB.