SKU-dependent PostgreSQL parameters in Azure HorizonDB

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.

  1. Connect to your Azure HorizonDB cluster with psql or another PostgreSQL client. For connection instructions, see Connect and query.

  2. 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.