Resource Usage / Memory

autovacuum_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used by each autovacuum worker process.
Data type integer
Default value -1
Allowed values -1-2097151
Parameter type dynamic
Documentation autovacuum_work_mem

dynamic_shared_memory_type

Attribute Value
Category Resource Usage / Memory
Description Selects the dynamic shared memory implementation used.
Data type enumeration
Default value posix
Allowed values posix
Parameter type read-only
Documentation dynamic_shared_memory_type

hash_mem_multiplier

Attribute Value
Category Resource Usage / Memory
Description Multiple of work_mem to use for hash tables.
Data type numeric
Default value 2
Allowed values 2
Parameter type read-only
Documentation hash_mem_multiplier

huge_pages

Attribute Value
Category Resource Usage / Memory
Description Enables/disables the use of huge memory pages. This setting is not applicable to servers having less than 4 vCores.
Data type enumeration
Default value try
Allowed values on,off,try
Parameter type static
Documentation huge_pages

Description

Huge pages are a feature that allows for memory to be managed in larger blocks. You can typically manage blocks of up to 2 MB, as opposed to the standard 4-KB pages.

Using huge pages can offer performance advantages that effectively offload the CPU:

  • They reduce the overhead associated with memory management tasks like fewer translation lookaside buffer (TLB) misses.
  • They shorten the time needed for memory management.

Specifically, in PostgreSQL, you can use huge pages only for the shared memory area. A significant part of the shared memory area is allocated for shared buffers.

Another advantage is that huge pages prevent the swapping of the shared memory area out to disk, which further stabilizes performance.

Recommendations

  • For servers that have significant memory resources, avoid disabling huge pages. Disabling huge pages could compromise performance.
  • If you start with a smaller server that doesn't support huge pages but you anticipate scaling up to a server that does, keep the huge_pages setting at TRY for seamless transition and optimal performance.

Azure-specific notes

For servers with four or more vCores, huge pages are automatically allocated from the underlying operating system. The feature isn't available for servers with fewer than four vCores. The number of huge pages is automatically adjusted if any shared memory settings are changed, including alterations to shared_buffers.

huge_page_size

Attribute Value
Category Resource Usage / Memory
Description The size of huge page that should be requested.
Data type integer
Default value 0
Allowed values 0
Parameter type read-only
Documentation huge_page_size

logical_decoding_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used for logical decoding.
Data type integer
Default value 65536
Allowed values 64-2147483647
Parameter type dynamic
Documentation logical_decoding_work_mem

maintenance_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used for maintenance operations such as VACUUM, Create Index.
Data type integer
Default value Depends on resources (vCores, RAM, or disk space) allocated to the server.
Allowed values 1024-2097151
Parameter type dynamic
Documentation maintenance_work_mem

Description

maintenance_work_mem is a configuration parameter in PostgreSQL. It governs the amount of memory allocated for maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE. Unlike work_mem, which affects memory allocation for query operations, maintenance_work_mem is reserved for tasks that maintain and optimize the database structure.

Key points

  • Vacuum memory cap: If you want to speed up the cleanup of dead tuples by increasing maintenance_work_mem, be aware that VACUUM has a built-in limitation for collecting dead tuple identifiers. It can use only up to 1 GB of memory for this process.
  • Separation of memory for autovacuum: You can use the autovacuum_work_mem setting to control the memory that autovacuum operations use independently. This setting acts as a subset of maintenance_work_mem. You can decide how much memory autovacuum uses without affecting the memory allocation for other maintenance tasks and data definition operations.

Azure-specific notes

The default value for the maintenance_work_mem server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server won't have any effect on the default value for the maintenance_work_mem server parameter of that instance.

Every time you change the product assigned to an instance, you should also adjust the value for the maintenance_work_mem parameter according to the values in the following formula.

The formula used to compute the value of maintenance_work_mem is (long)(82.5 * ln(memoryGiB) + 40) * 1024.

Based on the previous formula, the following table lists the values this server parameter would be set to depending on the amount of memory provisioned:

Memory size maintenance_work_mem
2 GiB 99328 KiB
4 GiB 157696 KiB
8 GiB 216064 KiB
16 GiB 274432 KiB
32 GiB 332800 KiB
48 GiB 367616 KiB
64 GiB 392192 KiB
80 GiB 410624 KiB
128 GiB 450560 KiB
160 GiB 468992 KiB
192 GiB 484352 KiB
256 GiB 508928 KiB
384 GiB 542720 KiB
432 GiB 552960 KiB
672 GiB 590848 KiB

max_prepared_transactions

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum number of simultaneously prepared transactions. When running a replica server, you must set this parameter to the same or higher value than on the primary server.
Data type integer
Default value 0
Allowed values 0-262143
Parameter type static
Documentation max_prepared_transactions

max_stack_depth

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum stack depth, in kilobytes.
Data type integer
Default value 2048
Allowed values 2048
Parameter type read-only
Documentation max_stack_depth

min_dynamic_shared_memory

Attribute Value
Category Resource Usage / Memory
Description Amount of dynamic shared memory reserved at startup.
Data type integer
Default value 0
Allowed values 0
Parameter type read-only
Documentation min_dynamic_shared_memory

shared_buffers

Attribute Value
Category Resource Usage / Memory
Description Sets the number of shared memory buffers used by the server. Unit is 8kb. Allowed values are inside the range of 10% - 75% of available memory.
Data type integer
Default value Depends on resources (vCores, RAM, or disk space) allocated to the server.
Allowed values 16-1073741823
Parameter type static
Documentation shared_buffers

Description

The shared_buffers configuration parameter determines the amount of system memory allocated to the PostgreSQL database for buffering data. It serves as a centralized memory pool that's accessible to all database processes.

When data is needed, the database process first checks the shared buffer. If the required data is present, it's quickly retrieved and bypasses a more time-consuming disk read. Shared buffers serve as an intermediary between the database processes and the disk, and effectively reduces the number of required I/O operations.

Azure-specific notes

The default value for the shared_buffers server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server don't have any effect on the default value for the shared_buffers server parameter of that instance.

Every time you change the product assigned to an instance, you should also adjust the value for the shared_buffers parameter according to the values in the following formulas.

For virtual machines with up to 2 GiB of memory, the formula used to compute the value of shared_buffers is memoryGib * 16384.

For virtual machines with more than 2 GiB, the formula used to compute the value of shared_buffers is memoryGib * 32768.

Based on the previous formula, the following table lists the values this server parameter would be set to depending on the amount of memory provisioned:

Memory size shared_buffers
2 GiB 32768
4 GiB 131072
8 GiB 262144
16 GiB 524288
32 GiB 1048576
48 GiB 1572864
64 GiB 2097152
80 GiB 2621440
128 GiB 4194304
160 GiB 5242880
192 GiB 6291456
256 GiB 8388608
384 GiB 12582912
432 GiB 14155776
672 GiB 22020096

shared_memory_type

Attribute Value
Category Resource Usage / Memory
Description Selects the shared memory implementation used for the main shared memory region.
Data type enumeration
Default value mmap
Allowed values mmap
Parameter type read-only
Documentation shared_memory_type

temp_buffers

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum number of temporary buffers used by each database session.
Data type integer
Default value 1024
Allowed values 100-1073741823
Parameter type dynamic
Documentation temp_buffers

work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files.
Data type integer
Default value 4096
Allowed values 4096-2097151
Parameter type dynamic
Documentation work_mem

Description

The work_mem parameter in PostgreSQL controls the amount of memory allocated for certain internal operations within each database session's private memory area. Examples of these operations are sorting and hashing.

Unlike shared buffers, which are in the shared memory area, work_mem is allocated in a per-session or per-query private memory space. By setting an adequate work_mem size, you can significantly improve the efficiency of these operations and reduce the need to write temporary data to disk.

Key points

  • Private connection memory: work_mem is part of the private memory that each database session uses. This memory is distinct from the shared memory area that shared_buffers uses.
  • Query-specific usage: Not all sessions or queries use work_mem. Simple queries like SELECT 1 are unlikely to require work_mem. However, complex queries that involve operations like sorting or hashing can consume one or multiple chunks of work_mem.
  • Parallel operations: For queries that span multiple parallel back ends, each back end could potentially use one or multiple chunks of work_mem.

Monitoring and adjusting work_mem

It's essential to continuously monitor your system's performance and adjust work_mem as necessary, primarily if query execution times related to sorting or hashing operations are slow. Here are ways to monitor performance by using tools available in the Azure portal:

  • Query performance insight: Check the Top queries by temporary files tab to identify queries that are generating temporary files. This situation suggests a potential need to increase work_mem.
  • Troubleshooting guides: Use the High temporary files tab in the troubleshooting guides to identify problematic queries.
Granular adjustment

While you're managing the work_mem parameter, it's often more efficient to adopt a granular adjustment approach rather than setting a global value. This approach ensures that you allocate memory judiciously based on the specific needs of processes and users. It also minimizes the risk of encountering out-of-memory issues. Here's how you can go about it:

  • User level: If a specific user is primarily involved in aggregation or reporting tasks, which are memory intensive, consider customizing the work_mem value for that user. Use the ALTER ROLE command to enhance the performance of the user's operations.

  • Function/procedure level: If specific functions or procedures are generating substantial temporary files, increasing the work_mem value at the specific function or procedure level can be beneficial. Use the ALTER FUNCTION or ALTER PROCEDURE command to specifically allocate more memory to these operations.

  • Database level: Alter work_mem at the database level if only specific databases are generating high numbers of temporary files.

  • Global level: If an analysis of your system reveals that most queries are generating small temporary files, while only a few are creating large ones, it might be prudent to globally increase the work_mem value. This action facilitates most queries to process in memory, so you can avoid disk-based operations and improve efficiency. However, always be cautious and monitor the memory utilization on your server to ensure that it can handle the increased work_mem value.

Determining the minimum work_mem value for sorting operations

To find the minimum work_mem value for a specific query, especially one that generates temporary disk files during the sorting process, start by considering the temporary file size generated during the query execution. For instance, if a query is generating a 20-MB temporary file:

  1. Connect to your database by using psql or your preferred PostgreSQL client.
  2. Set an initial work_mem value slightly higher than 20 MB to account for additional headers when processing in memory. Use a command such as: SET work_mem TO '25MB'.
  3. Run EXPLAIN ANALYZE on the problematic query in the same session.
  4. Review the output for "Sort Method: quicksort Memory: xkB". If it indicates "external merge Disk: xkB", raise the work_mem value incrementally and retest until "quicksort Memory" appears. The appearance of "quicksort Memory" signals that the query is now operating in memory.
  5. After you determine the value through this method, you can apply it either globally or on more granular levels (as described earlier) to suit your operational needs.

autovacuum_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used by each autovacuum worker process.
Data type integer
Default value -1
Allowed values -1-2097151
Parameter type dynamic
Documentation autovacuum_work_mem

dynamic_shared_memory_type

Attribute Value
Category Resource Usage / Memory
Description Selects the dynamic shared memory implementation used.
Data type enumeration
Default value posix
Allowed values posix
Parameter type read-only
Documentation dynamic_shared_memory_type

hash_mem_multiplier

Attribute Value
Category Resource Usage / Memory
Description Multiple of work_mem to use for hash tables.
Data type numeric
Default value 2
Allowed values 2
Parameter type read-only
Documentation hash_mem_multiplier

huge_pages

Attribute Value
Category Resource Usage / Memory
Description Enables/disables the use of huge memory pages. This setting is not applicable to servers having less than 4 vCores.
Data type enumeration
Default value try
Allowed values on,off,try
Parameter type static
Documentation huge_pages

Description

Huge pages are a feature that allows for memory to be managed in larger blocks. You can typically manage blocks of up to 2 MB, as opposed to the standard 4-KB pages.

Using huge pages can offer performance advantages that effectively offload the CPU:

  • They reduce the overhead associated with memory management tasks like fewer translation lookaside buffer (TLB) misses.
  • They shorten the time needed for memory management.

Specifically, in PostgreSQL, you can use huge pages only for the shared memory area. A significant part of the shared memory area is allocated for shared buffers.

Another advantage is that huge pages prevent the swapping of the shared memory area out to disk, which further stabilizes performance.

Recommendations

  • For servers that have significant memory resources, avoid disabling huge pages. Disabling huge pages could compromise performance.
  • If you start with a smaller server that doesn't support huge pages but you anticipate scaling up to a server that does, keep the huge_pages setting at TRY for seamless transition and optimal performance.

Azure-specific notes

For servers with four or more vCores, huge pages are automatically allocated from the underlying operating system. The feature isn't available for servers with fewer than four vCores. The number of huge pages is automatically adjusted if any shared memory settings are changed, including alterations to shared_buffers.

huge_page_size

Attribute Value
Category Resource Usage / Memory
Description The size of huge page that should be requested.
Data type integer
Default value 0
Allowed values 0
Parameter type read-only
Documentation huge_page_size

logical_decoding_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used for logical decoding.
Data type integer
Default value 65536
Allowed values 64-2147483647
Parameter type dynamic
Documentation logical_decoding_work_mem

maintenance_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used for maintenance operations such as VACUUM, Create Index.
Data type integer
Default value Depends on resources (vCores, RAM, or disk space) allocated to the server.
Allowed values 1024-2097151
Parameter type dynamic
Documentation maintenance_work_mem

Description

maintenance_work_mem is a configuration parameter in PostgreSQL. It governs the amount of memory allocated for maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE. Unlike work_mem, which affects memory allocation for query operations, maintenance_work_mem is reserved for tasks that maintain and optimize the database structure.

Key points

  • Vacuum memory cap: If you want to speed up the cleanup of dead tuples by increasing maintenance_work_mem, be aware that VACUUM has a built-in limitation for collecting dead tuple identifiers. It can use only up to 1 GB of memory for this process.
  • Separation of memory for autovacuum: You can use the autovacuum_work_mem setting to control the memory that autovacuum operations use independently. This setting acts as a subset of maintenance_work_mem. You can decide how much memory autovacuum uses without affecting the memory allocation for other maintenance tasks and data definition operations.

Azure-specific notes

The default value for the maintenance_work_mem server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server won't have any effect on the default value for the maintenance_work_mem server parameter of that instance.

Every time you change the product assigned to an instance, you should also adjust the value for the maintenance_work_mem parameter according to the values in the following formula.

The formula used to compute the value of maintenance_work_mem is (long)(82.5 * ln(memoryGiB) + 40) * 1024.

Based on the previous formula, the following table lists the values this server parameter would be set to depending on the amount of memory provisioned:

Memory size maintenance_work_mem
2 GiB 99328 KiB
4 GiB 157696 KiB
8 GiB 216064 KiB
16 GiB 274432 KiB
32 GiB 332800 KiB
48 GiB 367616 KiB
64 GiB 392192 KiB
80 GiB 410624 KiB
128 GiB 450560 KiB
160 GiB 468992 KiB
192 GiB 484352 KiB
256 GiB 508928 KiB
384 GiB 542720 KiB
432 GiB 552960 KiB
672 GiB 590848 KiB

max_prepared_transactions

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum number of simultaneously prepared transactions. When running a replica server, you must set this parameter to the same or higher value than on the primary server.
Data type integer
Default value 0
Allowed values 0-262143
Parameter type static
Documentation max_prepared_transactions

max_stack_depth

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum stack depth, in kilobytes.
Data type integer
Default value 2048
Allowed values 2048
Parameter type read-only
Documentation max_stack_depth

min_dynamic_shared_memory

Attribute Value
Category Resource Usage / Memory
Description Amount of dynamic shared memory reserved at startup.
Data type integer
Default value 0
Allowed values 0
Parameter type read-only
Documentation min_dynamic_shared_memory

shared_buffers

Attribute Value
Category Resource Usage / Memory
Description Sets the number of shared memory buffers used by the server. Unit is 8kb. Allowed values are inside the range of 10% - 75% of available memory.
Data type integer
Default value Depends on resources (vCores, RAM, or disk space) allocated to the server.
Allowed values 16-1073741823
Parameter type static
Documentation shared_buffers

Description

The shared_buffers configuration parameter determines the amount of system memory allocated to the PostgreSQL database for buffering data. It serves as a centralized memory pool that's accessible to all database processes.

When data is needed, the database process first checks the shared buffer. If the required data is present, it's quickly retrieved and bypasses a more time-consuming disk read. Shared buffers serve as an intermediary between the database processes and the disk, and effectively reduces the number of required I/O operations.

Azure-specific notes

The default value for the shared_buffers server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server don't have any effect on the default value for the shared_buffers server parameter of that instance.

Every time you change the product assigned to an instance, you should also adjust the value for the shared_buffers parameter according to the values in the following formulas.

For virtual machines with up to 2 GiB of memory, the formula used to compute the value of shared_buffers is memoryGib * 16384.

For virtual machines with more than 2 GiB, the formula used to compute the value of shared_buffers is memoryGib * 32768.

Based on the previous formula, the following table lists the values this server parameter would be set to depending on the amount of memory provisioned:

Memory size shared_buffers
2 GiB 32768
4 GiB 131072
8 GiB 262144
16 GiB 524288
32 GiB 1048576
48 GiB 1572864
64 GiB 2097152
80 GiB 2621440
128 GiB 4194304
160 GiB 5242880
192 GiB 6291456
256 GiB 8388608
384 GiB 12582912
432 GiB 14155776
672 GiB 22020096

shared_memory_type

Attribute Value
Category Resource Usage / Memory
Description Selects the shared memory implementation used for the main shared memory region.
Data type enumeration
Default value mmap
Allowed values mmap
Parameter type read-only
Documentation shared_memory_type

temp_buffers

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum number of temporary buffers used by each database session.
Data type integer
Default value 1024
Allowed values 100-1073741823
Parameter type dynamic
Documentation temp_buffers

work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files.
Data type integer
Default value 4096
Allowed values 4096-2097151
Parameter type dynamic
Documentation work_mem

Description

The work_mem parameter in PostgreSQL controls the amount of memory allocated for certain internal operations within each database session's private memory area. Examples of these operations are sorting and hashing.

Unlike shared buffers, which are in the shared memory area, work_mem is allocated in a per-session or per-query private memory space. By setting an adequate work_mem size, you can significantly improve the efficiency of these operations and reduce the need to write temporary data to disk.

Key points

  • Private connection memory: work_mem is part of the private memory that each database session uses. This memory is distinct from the shared memory area that shared_buffers uses.
  • Query-specific usage: Not all sessions or queries use work_mem. Simple queries like SELECT 1 are unlikely to require work_mem. However, complex queries that involve operations like sorting or hashing can consume one or multiple chunks of work_mem.
  • Parallel operations: For queries that span multiple parallel back ends, each back end could potentially use one or multiple chunks of work_mem.

Monitoring and adjusting work_mem

It's essential to continuously monitor your system's performance and adjust work_mem as necessary, primarily if query execution times related to sorting or hashing operations are slow. Here are ways to monitor performance by using tools available in the Azure portal:

  • Query performance insight: Check the Top queries by temporary files tab to identify queries that are generating temporary files. This situation suggests a potential need to increase work_mem.
  • Troubleshooting guides: Use the High temporary files tab in the troubleshooting guides to identify problematic queries.
Granular adjustment

While you're managing the work_mem parameter, it's often more efficient to adopt a granular adjustment approach rather than setting a global value. This approach ensures that you allocate memory judiciously based on the specific needs of processes and users. It also minimizes the risk of encountering out-of-memory issues. Here's how you can go about it:

  • User level: If a specific user is primarily involved in aggregation or reporting tasks, which are memory intensive, consider customizing the work_mem value for that user. Use the ALTER ROLE command to enhance the performance of the user's operations.

  • Function/procedure level: If specific functions or procedures are generating substantial temporary files, increasing the work_mem value at the specific function or procedure level can be beneficial. Use the ALTER FUNCTION or ALTER PROCEDURE command to specifically allocate more memory to these operations.

  • Database level: Alter work_mem at the database level if only specific databases are generating high numbers of temporary files.

  • Global level: If an analysis of your system reveals that most queries are generating small temporary files, while only a few are creating large ones, it might be prudent to globally increase the work_mem value. This action facilitates most queries to process in memory, so you can avoid disk-based operations and improve efficiency. However, always be cautious and monitor the memory utilization on your server to ensure that it can handle the increased work_mem value.

Determining the minimum work_mem value for sorting operations

To find the minimum work_mem value for a specific query, especially one that generates temporary disk files during the sorting process, start by considering the temporary file size generated during the query execution. For instance, if a query is generating a 20-MB temporary file:

  1. Connect to your database by using psql or your preferred PostgreSQL client.
  2. Set an initial work_mem value slightly higher than 20 MB to account for additional headers when processing in memory. Use a command such as: SET work_mem TO '25MB'.
  3. Run EXPLAIN ANALYZE on the problematic query in the same session.
  4. Review the output for "Sort Method: quicksort Memory: xkB". If it indicates "external merge Disk: xkB", raise the work_mem value incrementally and retest until "quicksort Memory" appears. The appearance of "quicksort Memory" signals that the query is now operating in memory.
  5. After you determine the value through this method, you can apply it either globally or on more granular levels (as described earlier) to suit your operational needs.

autovacuum_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used by each autovacuum worker process.
Data type integer
Default value -1
Allowed values -1-2097151
Parameter type dynamic
Documentation autovacuum_work_mem

dynamic_shared_memory_type

Attribute Value
Category Resource Usage / Memory
Description Selects the dynamic shared memory implementation used.
Data type enumeration
Default value posix
Allowed values posix
Parameter type read-only
Documentation dynamic_shared_memory_type

hash_mem_multiplier

Attribute Value
Category Resource Usage / Memory
Description Multiple of work_mem to use for hash tables.
Data type numeric
Default value 1
Allowed values 1
Parameter type read-only
Documentation hash_mem_multiplier

huge_pages

Attribute Value
Category Resource Usage / Memory
Description Enables/disables the use of huge memory pages. This setting is not applicable to servers having less than 4 vCores.
Data type enumeration
Default value try
Allowed values on,off,try
Parameter type static
Documentation huge_pages

Description

Huge pages are a feature that allows for memory to be managed in larger blocks. You can typically manage blocks of up to 2 MB, as opposed to the standard 4-KB pages.

Using huge pages can offer performance advantages that effectively offload the CPU:

  • They reduce the overhead associated with memory management tasks like fewer translation lookaside buffer (TLB) misses.
  • They shorten the time needed for memory management.

Specifically, in PostgreSQL, you can use huge pages only for the shared memory area. A significant part of the shared memory area is allocated for shared buffers.

Another advantage is that huge pages prevent the swapping of the shared memory area out to disk, which further stabilizes performance.

Recommendations

  • For servers that have significant memory resources, avoid disabling huge pages. Disabling huge pages could compromise performance.
  • If you start with a smaller server that doesn't support huge pages but you anticipate scaling up to a server that does, keep the huge_pages setting at TRY for seamless transition and optimal performance.

Azure-specific notes

For servers with four or more vCores, huge pages are automatically allocated from the underlying operating system. The feature isn't available for servers with fewer than four vCores. The number of huge pages is automatically adjusted if any shared memory settings are changed, including alterations to shared_buffers.

huge_page_size

Attribute Value
Category Resource Usage / Memory
Description The size of huge page that should be requested.
Data type integer
Default value 0
Allowed values 0
Parameter type read-only
Documentation huge_page_size

logical_decoding_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used for logical decoding.
Data type integer
Default value 65536
Allowed values 64-2147483647
Parameter type dynamic
Documentation logical_decoding_work_mem

maintenance_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used for maintenance operations such as VACUUM, Create Index.
Data type integer
Default value Depends on resources (vCores, RAM, or disk space) allocated to the server.
Allowed values 1024-2097151
Parameter type dynamic
Documentation maintenance_work_mem

Description

maintenance_work_mem is a configuration parameter in PostgreSQL. It governs the amount of memory allocated for maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE. Unlike work_mem, which affects memory allocation for query operations, maintenance_work_mem is reserved for tasks that maintain and optimize the database structure.

Key points

  • Vacuum memory cap: If you want to speed up the cleanup of dead tuples by increasing maintenance_work_mem, be aware that VACUUM has a built-in limitation for collecting dead tuple identifiers. It can use only up to 1 GB of memory for this process.
  • Separation of memory for autovacuum: You can use the autovacuum_work_mem setting to control the memory that autovacuum operations use independently. This setting acts as a subset of maintenance_work_mem. You can decide how much memory autovacuum uses without affecting the memory allocation for other maintenance tasks and data definition operations.

Azure-specific notes

The default value for the maintenance_work_mem server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server won't have any effect on the default value for the maintenance_work_mem server parameter of that instance.

Every time you change the product assigned to an instance, you should also adjust the value for the maintenance_work_mem parameter according to the values in the following formula.

The formula used to compute the value of maintenance_work_mem is (long)(82.5 * ln(memoryGiB) + 40) * 1024.

Based on the previous formula, the following table lists the values this server parameter would be set to depending on the amount of memory provisioned:

Memory size maintenance_work_mem
2 GiB 99328 KiB
4 GiB 157696 KiB
8 GiB 216064 KiB
16 GiB 274432 KiB
32 GiB 332800 KiB
48 GiB 367616 KiB
64 GiB 392192 KiB
80 GiB 410624 KiB
128 GiB 450560 KiB
160 GiB 468992 KiB
192 GiB 484352 KiB
256 GiB 508928 KiB
384 GiB 542720 KiB
432 GiB 552960 KiB
672 GiB 590848 KiB

max_prepared_transactions

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum number of simultaneously prepared transactions. When running a replica server, you must set this parameter to the same or higher value than on the primary server.
Data type integer
Default value 0
Allowed values 0-262143
Parameter type static
Documentation max_prepared_transactions

max_stack_depth

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum stack depth, in kilobytes.
Data type integer
Default value 2048
Allowed values 2048
Parameter type read-only
Documentation max_stack_depth

min_dynamic_shared_memory

Attribute Value
Category Resource Usage / Memory
Description Amount of dynamic shared memory reserved at startup.
Data type integer
Default value 0
Allowed values 0
Parameter type read-only
Documentation min_dynamic_shared_memory

shared_buffers

Attribute Value
Category Resource Usage / Memory
Description Sets the number of shared memory buffers used by the server. Unit is 8kb. Allowed values are inside the range of 10% - 75% of available memory.
Data type integer
Default value Depends on resources (vCores, RAM, or disk space) allocated to the server.
Allowed values 16-1073741823
Parameter type static
Documentation shared_buffers

Description

The shared_buffers configuration parameter determines the amount of system memory allocated to the PostgreSQL database for buffering data. It serves as a centralized memory pool that's accessible to all database processes.

When data is needed, the database process first checks the shared buffer. If the required data is present, it's quickly retrieved and bypasses a more time-consuming disk read. Shared buffers serve as an intermediary between the database processes and the disk, and effectively reduces the number of required I/O operations.

Azure-specific notes

The default value for the shared_buffers server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server don't have any effect on the default value for the shared_buffers server parameter of that instance.

Every time you change the product assigned to an instance, you should also adjust the value for the shared_buffers parameter according to the values in the following formulas.

For virtual machines with up to 2 GiB of memory, the formula used to compute the value of shared_buffers is memoryGib * 16384.

For virtual machines with more than 2 GiB, the formula used to compute the value of shared_buffers is memoryGib * 32768.

Based on the previous formula, the following table lists the values this server parameter would be set to depending on the amount of memory provisioned:

Memory size shared_buffers
2 GiB 32768
4 GiB 131072
8 GiB 262144
16 GiB 524288
32 GiB 1048576
48 GiB 1572864
64 GiB 2097152
80 GiB 2621440
128 GiB 4194304
160 GiB 5242880
192 GiB 6291456
256 GiB 8388608
384 GiB 12582912
432 GiB 14155776
672 GiB 22020096

shared_memory_type

Attribute Value
Category Resource Usage / Memory
Description Selects the shared memory implementation used for the main shared memory region.
Data type enumeration
Default value mmap
Allowed values mmap
Parameter type read-only
Documentation shared_memory_type

temp_buffers

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum number of temporary buffers used by each database session.
Data type integer
Default value 1024
Allowed values 100-1073741823
Parameter type dynamic
Documentation temp_buffers

work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files.
Data type integer
Default value 4096
Allowed values 4096-2097151
Parameter type dynamic
Documentation work_mem

Description

The work_mem parameter in PostgreSQL controls the amount of memory allocated for certain internal operations within each database session's private memory area. Examples of these operations are sorting and hashing.

Unlike shared buffers, which are in the shared memory area, work_mem is allocated in a per-session or per-query private memory space. By setting an adequate work_mem size, you can significantly improve the efficiency of these operations and reduce the need to write temporary data to disk.

Key points

  • Private connection memory: work_mem is part of the private memory that each database session uses. This memory is distinct from the shared memory area that shared_buffers uses.
  • Query-specific usage: Not all sessions or queries use work_mem. Simple queries like SELECT 1 are unlikely to require work_mem. However, complex queries that involve operations like sorting or hashing can consume one or multiple chunks of work_mem.
  • Parallel operations: For queries that span multiple parallel back ends, each back end could potentially use one or multiple chunks of work_mem.

Monitoring and adjusting work_mem

It's essential to continuously monitor your system's performance and adjust work_mem as necessary, primarily if query execution times related to sorting or hashing operations are slow. Here are ways to monitor performance by using tools available in the Azure portal:

  • Query performance insight: Check the Top queries by temporary files tab to identify queries that are generating temporary files. This situation suggests a potential need to increase work_mem.
  • Troubleshooting guides: Use the High temporary files tab in the troubleshooting guides to identify problematic queries.
Granular adjustment

While you're managing the work_mem parameter, it's often more efficient to adopt a granular adjustment approach rather than setting a global value. This approach ensures that you allocate memory judiciously based on the specific needs of processes and users. It also minimizes the risk of encountering out-of-memory issues. Here's how you can go about it:

  • User level: If a specific user is primarily involved in aggregation or reporting tasks, which are memory intensive, consider customizing the work_mem value for that user. Use the ALTER ROLE command to enhance the performance of the user's operations.

  • Function/procedure level: If specific functions or procedures are generating substantial temporary files, increasing the work_mem value at the specific function or procedure level can be beneficial. Use the ALTER FUNCTION or ALTER PROCEDURE command to specifically allocate more memory to these operations.

  • Database level: Alter work_mem at the database level if only specific databases are generating high numbers of temporary files.

  • Global level: If an analysis of your system reveals that most queries are generating small temporary files, while only a few are creating large ones, it might be prudent to globally increase the work_mem value. This action facilitates most queries to process in memory, so you can avoid disk-based operations and improve efficiency. However, always be cautious and monitor the memory utilization on your server to ensure that it can handle the increased work_mem value.

Determining the minimum work_mem value for sorting operations

To find the minimum work_mem value for a specific query, especially one that generates temporary disk files during the sorting process, start by considering the temporary file size generated during the query execution. For instance, if a query is generating a 20-MB temporary file:

  1. Connect to your database by using psql or your preferred PostgreSQL client.
  2. Set an initial work_mem value slightly higher than 20 MB to account for additional headers when processing in memory. Use a command such as: SET work_mem TO '25MB'.
  3. Run EXPLAIN ANALYZE on the problematic query in the same session.
  4. Review the output for "Sort Method: quicksort Memory: xkB". If it indicates "external merge Disk: xkB", raise the work_mem value incrementally and retest until "quicksort Memory" appears. The appearance of "quicksort Memory" signals that the query is now operating in memory.
  5. After you determine the value through this method, you can apply it either globally or on more granular levels (as described earlier) to suit your operational needs.

autovacuum_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used by each autovacuum worker process.
Data type integer
Default value -1
Allowed values -1-2097151
Parameter type dynamic
Documentation autovacuum_work_mem

dynamic_shared_memory_type

Attribute Value
Category Resource Usage / Memory
Description Selects the dynamic shared memory implementation used.
Data type enumeration
Default value posix
Allowed values posix
Parameter type read-only
Documentation dynamic_shared_memory_type

hash_mem_multiplier

Attribute Value
Category Resource Usage / Memory
Description Multiple of work_mem to use for hash tables.
Data type numeric
Default value 1
Allowed values 1
Parameter type read-only
Documentation hash_mem_multiplier

huge_pages

Attribute Value
Category Resource Usage / Memory
Description Enables/disables the use of huge memory pages. This setting is not applicable to servers having less than 4 vCores.
Data type enumeration
Default value try
Allowed values on,off,try
Parameter type static
Documentation huge_pages

Description

Huge pages are a feature that allows for memory to be managed in larger blocks. You can typically manage blocks of up to 2 MB, as opposed to the standard 4-KB pages.

Using huge pages can offer performance advantages that effectively offload the CPU:

  • They reduce the overhead associated with memory management tasks like fewer translation lookaside buffer (TLB) misses.
  • They shorten the time needed for memory management.

Specifically, in PostgreSQL, you can use huge pages only for the shared memory area. A significant part of the shared memory area is allocated for shared buffers.

Another advantage is that huge pages prevent the swapping of the shared memory area out to disk, which further stabilizes performance.

Recommendations

  • For servers that have significant memory resources, avoid disabling huge pages. Disabling huge pages could compromise performance.
  • If you start with a smaller server that doesn't support huge pages but you anticipate scaling up to a server that does, keep the huge_pages setting at TRY for seamless transition and optimal performance.

Azure-specific notes

For servers with four or more vCores, huge pages are automatically allocated from the underlying operating system. The feature isn't available for servers with fewer than four vCores. The number of huge pages is automatically adjusted if any shared memory settings are changed, including alterations to shared_buffers.

logical_decoding_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used for logical decoding.
Data type integer
Default value 65536
Allowed values 64-2147483647
Parameter type dynamic
Documentation logical_decoding_work_mem

maintenance_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used for maintenance operations such as VACUUM, Create Index.
Data type integer
Default value Depends on resources (vCores, RAM, or disk space) allocated to the server.
Allowed values 1024-2097151
Parameter type dynamic
Documentation maintenance_work_mem

Description

maintenance_work_mem is a configuration parameter in PostgreSQL. It governs the amount of memory allocated for maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE. Unlike work_mem, which affects memory allocation for query operations, maintenance_work_mem is reserved for tasks that maintain and optimize the database structure.

Key points

  • Vacuum memory cap: If you want to speed up the cleanup of dead tuples by increasing maintenance_work_mem, be aware that VACUUM has a built-in limitation for collecting dead tuple identifiers. It can use only up to 1 GB of memory for this process.
  • Separation of memory for autovacuum: You can use the autovacuum_work_mem setting to control the memory that autovacuum operations use independently. This setting acts as a subset of maintenance_work_mem. You can decide how much memory autovacuum uses without affecting the memory allocation for other maintenance tasks and data definition operations.

Azure-specific notes

The default value for the maintenance_work_mem server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server won't have any effect on the default value for the maintenance_work_mem server parameter of that instance.

Every time you change the product assigned to an instance, you should also adjust the value for the maintenance_work_mem parameter according to the values in the following formula.

The formula used to compute the value of maintenance_work_mem is (long)(82.5 * ln(memoryGiB) + 40) * 1024.

Based on the previous formula, the following table lists the values this server parameter would be set to depending on the amount of memory provisioned:

Memory size maintenance_work_mem
2 GiB 99328 KiB
4 GiB 157696 KiB
8 GiB 216064 KiB
16 GiB 274432 KiB
32 GiB 332800 KiB
48 GiB 367616 KiB
64 GiB 392192 KiB
80 GiB 410624 KiB
128 GiB 450560 KiB
160 GiB 468992 KiB
192 GiB 484352 KiB
256 GiB 508928 KiB
384 GiB 542720 KiB
432 GiB 552960 KiB
672 GiB 590848 KiB

max_prepared_transactions

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum number of simultaneously prepared transactions. When running a replica server, you must set this parameter to the same or higher value than on the primary server.
Data type integer
Default value 0
Allowed values 0-262143
Parameter type static
Documentation max_prepared_transactions

max_stack_depth

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum stack depth, in kilobytes.
Data type integer
Default value 2048
Allowed values 2048
Parameter type read-only
Documentation max_stack_depth

shared_buffers

Attribute Value
Category Resource Usage / Memory
Description Sets the number of shared memory buffers used by the server. Unit is 8kb. Allowed values are inside the range of 10% - 75% of available memory.
Data type integer
Default value Depends on resources (vCores, RAM, or disk space) allocated to the server.
Allowed values 16-1073741823
Parameter type static
Documentation shared_buffers

Description

The shared_buffers configuration parameter determines the amount of system memory allocated to the PostgreSQL database for buffering data. It serves as a centralized memory pool that's accessible to all database processes.

When data is needed, the database process first checks the shared buffer. If the required data is present, it's quickly retrieved and bypasses a more time-consuming disk read. Shared buffers serve as an intermediary between the database processes and the disk, and effectively reduces the number of required I/O operations.

Azure-specific notes

The default value for the shared_buffers server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server don't have any effect on the default value for the shared_buffers server parameter of that instance.

Every time you change the product assigned to an instance, you should also adjust the value for the shared_buffers parameter according to the values in the following formulas.

For virtual machines with up to 2 GiB of memory, the formula used to compute the value of shared_buffers is memoryGib * 16384.

For virtual machines with more than 2 GiB, the formula used to compute the value of shared_buffers is memoryGib * 32768.

Based on the previous formula, the following table lists the values this server parameter would be set to depending on the amount of memory provisioned:

Memory size shared_buffers
2 GiB 32768
4 GiB 131072
8 GiB 262144
16 GiB 524288
32 GiB 1048576
48 GiB 1572864
64 GiB 2097152
80 GiB 2621440
128 GiB 4194304
160 GiB 5242880
192 GiB 6291456
256 GiB 8388608
384 GiB 12582912
432 GiB 14155776
672 GiB 22020096

shared_memory_type

Attribute Value
Category Resource Usage / Memory
Description Selects the shared memory implementation used for the main shared memory region.
Data type enumeration
Default value mmap
Allowed values mmap
Parameter type read-only
Documentation shared_memory_type

temp_buffers

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum number of temporary buffers used by each database session.
Data type integer
Default value 1024
Allowed values 100-1073741823
Parameter type dynamic
Documentation temp_buffers

work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files.
Data type integer
Default value 4096
Allowed values 4096-2097151
Parameter type dynamic
Documentation work_mem

Description

The work_mem parameter in PostgreSQL controls the amount of memory allocated for certain internal operations within each database session's private memory area. Examples of these operations are sorting and hashing.

Unlike shared buffers, which are in the shared memory area, work_mem is allocated in a per-session or per-query private memory space. By setting an adequate work_mem size, you can significantly improve the efficiency of these operations and reduce the need to write temporary data to disk.

Key points

  • Private connection memory: work_mem is part of the private memory that each database session uses. This memory is distinct from the shared memory area that shared_buffers uses.
  • Query-specific usage: Not all sessions or queries use work_mem. Simple queries like SELECT 1 are unlikely to require work_mem. However, complex queries that involve operations like sorting or hashing can consume one or multiple chunks of work_mem.
  • Parallel operations: For queries that span multiple parallel back ends, each back end could potentially use one or multiple chunks of work_mem.

Monitoring and adjusting work_mem

It's essential to continuously monitor your system's performance and adjust work_mem as necessary, primarily if query execution times related to sorting or hashing operations are slow. Here are ways to monitor performance by using tools available in the Azure portal:

  • Query performance insight: Check the Top queries by temporary files tab to identify queries that are generating temporary files. This situation suggests a potential need to increase work_mem.
  • Troubleshooting guides: Use the High temporary files tab in the troubleshooting guides to identify problematic queries.
Granular adjustment

While you're managing the work_mem parameter, it's often more efficient to adopt a granular adjustment approach rather than setting a global value. This approach ensures that you allocate memory judiciously based on the specific needs of processes and users. It also minimizes the risk of encountering out-of-memory issues. Here's how you can go about it:

  • User level: If a specific user is primarily involved in aggregation or reporting tasks, which are memory intensive, consider customizing the work_mem value for that user. Use the ALTER ROLE command to enhance the performance of the user's operations.

  • Function/procedure level: If specific functions or procedures are generating substantial temporary files, increasing the work_mem value at the specific function or procedure level can be beneficial. Use the ALTER FUNCTION or ALTER PROCEDURE command to specifically allocate more memory to these operations.

  • Database level: Alter work_mem at the database level if only specific databases are generating high numbers of temporary files.

  • Global level: If an analysis of your system reveals that most queries are generating small temporary files, while only a few are creating large ones, it might be prudent to globally increase the work_mem value. This action facilitates most queries to process in memory, so you can avoid disk-based operations and improve efficiency. However, always be cautious and monitor the memory utilization on your server to ensure that it can handle the increased work_mem value.

Determining the minimum work_mem value for sorting operations

To find the minimum work_mem value for a specific query, especially one that generates temporary disk files during the sorting process, start by considering the temporary file size generated during the query execution. For instance, if a query is generating a 20-MB temporary file:

  1. Connect to your database by using psql or your preferred PostgreSQL client.
  2. Set an initial work_mem value slightly higher than 20 MB to account for additional headers when processing in memory. Use a command such as: SET work_mem TO '25MB'.
  3. Run EXPLAIN ANALYZE on the problematic query in the same session.
  4. Review the output for "Sort Method: quicksort Memory: xkB". If it indicates "external merge Disk: xkB", raise the work_mem value incrementally and retest until "quicksort Memory" appears. The appearance of "quicksort Memory" signals that the query is now operating in memory.
  5. After you determine the value through this method, you can apply it either globally or on more granular levels (as described earlier) to suit your operational needs.

autovacuum_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used by each autovacuum worker process.
Data type integer
Default value -1
Allowed values -1-2097151
Parameter type dynamic
Documentation autovacuum_work_mem

dynamic_shared_memory_type

Attribute Value
Category Resource Usage / Memory
Description Selects the dynamic shared memory implementation used.
Data type enumeration
Default value posix
Allowed values posix
Parameter type read-only
Documentation dynamic_shared_memory_type

hash_mem_multiplier

Attribute Value
Category Resource Usage / Memory
Description Multiple of work_mem to use for hash tables.
Data type numeric
Default value 1
Allowed values 1
Parameter type read-only
Documentation hash_mem_multiplier

huge_pages

Attribute Value
Category Resource Usage / Memory
Description Enables/disables the use of huge memory pages. This setting is not applicable to servers having less than 4 vCores.
Data type enumeration
Default value try
Allowed values on,off,try
Parameter type static
Documentation huge_pages

Description

Huge pages are a feature that allows for memory to be managed in larger blocks. You can typically manage blocks of up to 2 MB, as opposed to the standard 4-KB pages.

Using huge pages can offer performance advantages that effectively offload the CPU:

  • They reduce the overhead associated with memory management tasks like fewer translation lookaside buffer (TLB) misses.
  • They shorten the time needed for memory management.

Specifically, in PostgreSQL, you can use huge pages only for the shared memory area. A significant part of the shared memory area is allocated for shared buffers.

Another advantage is that huge pages prevent the swapping of the shared memory area out to disk, which further stabilizes performance.

Recommendations

  • For servers that have significant memory resources, avoid disabling huge pages. Disabling huge pages could compromise performance.
  • If you start with a smaller server that doesn't support huge pages but you anticipate scaling up to a server that does, keep the huge_pages setting at TRY for seamless transition and optimal performance.

Azure-specific notes

For servers with four or more vCores, huge pages are automatically allocated from the underlying operating system. The feature isn't available for servers with fewer than four vCores. The number of huge pages is automatically adjusted if any shared memory settings are changed, including alterations to shared_buffers.

maintenance_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used for maintenance operations such as VACUUM, Create Index.
Data type integer
Default value Depends on resources (vCores, RAM, or disk space) allocated to the server.
Allowed values 1024-2097151
Parameter type dynamic
Documentation maintenance_work_mem

Description

maintenance_work_mem is a configuration parameter in PostgreSQL. It governs the amount of memory allocated for maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE. Unlike work_mem, which affects memory allocation for query operations, maintenance_work_mem is reserved for tasks that maintain and optimize the database structure.

Key points

  • Vacuum memory cap: If you want to speed up the cleanup of dead tuples by increasing maintenance_work_mem, be aware that VACUUM has a built-in limitation for collecting dead tuple identifiers. It can use only up to 1 GB of memory for this process.
  • Separation of memory for autovacuum: You can use the autovacuum_work_mem setting to control the memory that autovacuum operations use independently. This setting acts as a subset of maintenance_work_mem. You can decide how much memory autovacuum uses without affecting the memory allocation for other maintenance tasks and data definition operations.

Azure-specific notes

The default value for the maintenance_work_mem server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server won't have any effect on the default value for the maintenance_work_mem server parameter of that instance.

Every time you change the product assigned to an instance, you should also adjust the value for the maintenance_work_mem parameter according to the values in the following formula.

The formula used to compute the value of maintenance_work_mem is (long)(82.5 * ln(memoryGiB) + 40) * 1024.

Based on the previous formula, the following table lists the values this server parameter would be set to depending on the amount of memory provisioned:

Memory size maintenance_work_mem
2 GiB 99328 KiB
4 GiB 157696 KiB
8 GiB 216064 KiB
16 GiB 274432 KiB
32 GiB 332800 KiB
48 GiB 367616 KiB
64 GiB 392192 KiB
80 GiB 410624 KiB
128 GiB 450560 KiB
160 GiB 468992 KiB
192 GiB 484352 KiB
256 GiB 508928 KiB
384 GiB 542720 KiB
432 GiB 552960 KiB
672 GiB 590848 KiB

max_prepared_transactions

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum number of simultaneously prepared transactions. When running a replica server, you must set this parameter to the same or higher value than on the primary server.
Data type integer
Default value 0
Allowed values 0-262143
Parameter type static
Documentation max_prepared_transactions

max_stack_depth

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum stack depth, in kilobytes.
Data type integer
Default value 2048
Allowed values 2048
Parameter type read-only
Documentation max_stack_depth

shared_buffers

Attribute Value
Category Resource Usage / Memory
Description Sets the number of shared memory buffers used by the server. Unit is 8kb. Allowed values are inside the range of 10% - 75% of available memory.
Data type integer
Default value Depends on resources (vCores, RAM, or disk space) allocated to the server.
Allowed values 16-1073741823
Parameter type static
Documentation shared_buffers

Description

The shared_buffers configuration parameter determines the amount of system memory allocated to the PostgreSQL database for buffering data. It serves as a centralized memory pool that's accessible to all database processes.

When data is needed, the database process first checks the shared buffer. If the required data is present, it's quickly retrieved and bypasses a more time-consuming disk read. Shared buffers serve as an intermediary between the database processes and the disk, and effectively reduces the number of required I/O operations.

Azure-specific notes

The default value for the shared_buffers server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server don't have any effect on the default value for the shared_buffers server parameter of that instance.

Every time you change the product assigned to an instance, you should also adjust the value for the shared_buffers parameter according to the values in the following formulas.

For virtual machines with up to 2 GiB of memory, the formula used to compute the value of shared_buffers is memoryGib * 16384.

For virtual machines with more than 2 GiB, the formula used to compute the value of shared_buffers is memoryGib * 32768.

Based on the previous formula, the following table lists the values this server parameter would be set to depending on the amount of memory provisioned:

Memory size shared_buffers
2 GiB 32768
4 GiB 131072
8 GiB 262144
16 GiB 524288
32 GiB 1048576
48 GiB 1572864
64 GiB 2097152
80 GiB 2621440
128 GiB 4194304
160 GiB 5242880
192 GiB 6291456
256 GiB 8388608
384 GiB 12582912
432 GiB 14155776
672 GiB 22020096

shared_memory_type

Attribute Value
Category Resource Usage / Memory
Description Selects the shared memory implementation used for the main shared memory region.
Data type enumeration
Default value mmap
Allowed values mmap
Parameter type read-only
Documentation shared_memory_type

temp_buffers

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum number of temporary buffers used by each database session.
Data type integer
Default value 1024
Allowed values 100-1073741823
Parameter type dynamic
Documentation temp_buffers

work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files.
Data type integer
Default value 4096
Allowed values 4096-2097151
Parameter type dynamic
Documentation work_mem

Description

The work_mem parameter in PostgreSQL controls the amount of memory allocated for certain internal operations within each database session's private memory area. Examples of these operations are sorting and hashing.

Unlike shared buffers, which are in the shared memory area, work_mem is allocated in a per-session or per-query private memory space. By setting an adequate work_mem size, you can significantly improve the efficiency of these operations and reduce the need to write temporary data to disk.

Key points

  • Private connection memory: work_mem is part of the private memory that each database session uses. This memory is distinct from the shared memory area that shared_buffers uses.
  • Query-specific usage: Not all sessions or queries use work_mem. Simple queries like SELECT 1 are unlikely to require work_mem. However, complex queries that involve operations like sorting or hashing can consume one or multiple chunks of work_mem.
  • Parallel operations: For queries that span multiple parallel back ends, each back end could potentially use one or multiple chunks of work_mem.

Monitoring and adjusting work_mem

It's essential to continuously monitor your system's performance and adjust work_mem as necessary, primarily if query execution times related to sorting or hashing operations are slow. Here are ways to monitor performance by using tools available in the Azure portal:

  • Query performance insight: Check the Top queries by temporary files tab to identify queries that are generating temporary files. This situation suggests a potential need to increase work_mem.
  • Troubleshooting guides: Use the High temporary files tab in the troubleshooting guides to identify problematic queries.
Granular adjustment

While you're managing the work_mem parameter, it's often more efficient to adopt a granular adjustment approach rather than setting a global value. This approach ensures that you allocate memory judiciously based on the specific needs of processes and users. It also minimizes the risk of encountering out-of-memory issues. Here's how you can go about it:

  • User level: If a specific user is primarily involved in aggregation or reporting tasks, which are memory intensive, consider customizing the work_mem value for that user. Use the ALTER ROLE command to enhance the performance of the user's operations.

  • Function/procedure level: If specific functions or procedures are generating substantial temporary files, increasing the work_mem value at the specific function or procedure level can be beneficial. Use the ALTER FUNCTION or ALTER PROCEDURE command to specifically allocate more memory to these operations.

  • Database level: Alter work_mem at the database level if only specific databases are generating high numbers of temporary files.

  • Global level: If an analysis of your system reveals that most queries are generating small temporary files, while only a few are creating large ones, it might be prudent to globally increase the work_mem value. This action facilitates most queries to process in memory, so you can avoid disk-based operations and improve efficiency. However, always be cautious and monitor the memory utilization on your server to ensure that it can handle the increased work_mem value.

Determining the minimum work_mem value for sorting operations

To find the minimum work_mem value for a specific query, especially one that generates temporary disk files during the sorting process, start by considering the temporary file size generated during the query execution. For instance, if a query is generating a 20-MB temporary file:

  1. Connect to your database by using psql or your preferred PostgreSQL client.
  2. Set an initial work_mem value slightly higher than 20 MB to account for additional headers when processing in memory. Use a command such as: SET work_mem TO '25MB'.
  3. Run EXPLAIN ANALYZE on the problematic query in the same session.
  4. Review the output for "Sort Method: quicksort Memory: xkB". If it indicates "external merge Disk: xkB", raise the work_mem value incrementally and retest until "quicksort Memory" appears. The appearance of "quicksort Memory" signals that the query is now operating in memory.
  5. After you determine the value through this method, you can apply it either globally or on more granular levels (as described earlier) to suit your operational needs.

autovacuum_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used by each autovacuum worker process.
Data type integer
Default value -1
Allowed values -1-2097151
Parameter type dynamic
Documentation autovacuum_work_mem

dynamic_shared_memory_type

Attribute Value
Category Resource Usage / Memory
Description Selects the dynamic shared memory implementation used.
Data type enumeration
Default value posix
Allowed values posix
Parameter type read-only
Documentation dynamic_shared_memory_type

huge_pages

Attribute Value
Category Resource Usage / Memory
Description Enables/disables the use of huge memory pages. This setting is not applicable to servers having less than 4 vCores.
Data type enumeration
Default value try
Allowed values on,off,try
Parameter type static
Documentation huge_pages

Description

Huge pages are a feature that allows for memory to be managed in larger blocks. You can typically manage blocks of up to 2 MB, as opposed to the standard 4-KB pages.

Using huge pages can offer performance advantages that effectively offload the CPU:

  • They reduce the overhead associated with memory management tasks like fewer translation lookaside buffer (TLB) misses.
  • They shorten the time needed for memory management.

Specifically, in PostgreSQL, you can use huge pages only for the shared memory area. A significant part of the shared memory area is allocated for shared buffers.

Another advantage is that huge pages prevent the swapping of the shared memory area out to disk, which further stabilizes performance.

Recommendations

  • For servers that have significant memory resources, avoid disabling huge pages. Disabling huge pages could compromise performance.
  • If you start with a smaller server that doesn't support huge pages but you anticipate scaling up to a server that does, keep the huge_pages setting at TRY for seamless transition and optimal performance.

Azure-specific notes

For servers with four or more vCores, huge pages are automatically allocated from the underlying operating system. The feature isn't available for servers with fewer than four vCores. The number of huge pages is automatically adjusted if any shared memory settings are changed, including alterations to shared_buffers.

maintenance_work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum memory to be used for maintenance operations such as VACUUM, Create Index.
Data type integer
Default value Depends on resources (vCores, RAM, or disk space) allocated to the server.
Allowed values 1024-2097151
Parameter type dynamic
Documentation maintenance_work_mem

Description

maintenance_work_mem is a configuration parameter in PostgreSQL. It governs the amount of memory allocated for maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE. Unlike work_mem, which affects memory allocation for query operations, maintenance_work_mem is reserved for tasks that maintain and optimize the database structure.

Key points

  • Vacuum memory cap: If you want to speed up the cleanup of dead tuples by increasing maintenance_work_mem, be aware that VACUUM has a built-in limitation for collecting dead tuple identifiers. It can use only up to 1 GB of memory for this process.
  • Separation of memory for autovacuum: You can use the autovacuum_work_mem setting to control the memory that autovacuum operations use independently. This setting acts as a subset of maintenance_work_mem. You can decide how much memory autovacuum uses without affecting the memory allocation for other maintenance tasks and data definition operations.

Azure-specific notes

The default value for the maintenance_work_mem server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server won't have any effect on the default value for the maintenance_work_mem server parameter of that instance.

Every time you change the product assigned to an instance, you should also adjust the value for the maintenance_work_mem parameter according to the values in the following formula.

The formula used to compute the value of maintenance_work_mem is (long)(82.5 * ln(memoryGiB) + 40) * 1024.

Based on the previous formula, the following table lists the values this server parameter would be set to depending on the amount of memory provisioned:

Memory size maintenance_work_mem
2 GiB 99328 KiB
4 GiB 157696 KiB
8 GiB 216064 KiB
16 GiB 274432 KiB
32 GiB 332800 KiB
48 GiB 367616 KiB
64 GiB 392192 KiB
80 GiB 410624 KiB
128 GiB 450560 KiB
160 GiB 468992 KiB
192 GiB 484352 KiB
256 GiB 508928 KiB
384 GiB 542720 KiB
432 GiB 552960 KiB
672 GiB 590848 KiB

max_prepared_transactions

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum number of simultaneously prepared transactions. When running a replica server, you must set this parameter to the same or higher value than on the primary server.
Data type integer
Default value 0
Allowed values 0-262143
Parameter type static
Documentation max_prepared_transactions

max_stack_depth

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum stack depth, in kilobytes.
Data type integer
Default value 2048
Allowed values 2048
Parameter type read-only
Documentation max_stack_depth

shared_buffers

Attribute Value
Category Resource Usage / Memory
Description Sets the number of shared memory buffers used by the server. Unit is 8kb. Allowed values are inside the range of 10% - 75% of available memory.
Data type integer
Default value Depends on resources (vCores, RAM, or disk space) allocated to the server.
Allowed values 16-1073741823
Parameter type static
Documentation shared_buffers

Description

The shared_buffers configuration parameter determines the amount of system memory allocated to the PostgreSQL database for buffering data. It serves as a centralized memory pool that's accessible to all database processes.

When data is needed, the database process first checks the shared buffer. If the required data is present, it's quickly retrieved and bypasses a more time-consuming disk read. Shared buffers serve as an intermediary between the database processes and the disk, and effectively reduces the number of required I/O operations.

Azure-specific notes

The default value for the shared_buffers server parameter is calculated when you provision the instance of Azure Database for PostgreSQL flexible server, based on the product name that you select for its compute. Any subsequent changes of product selection to the compute that supports the flexible server don't have any effect on the default value for the shared_buffers server parameter of that instance.

Every time you change the product assigned to an instance, you should also adjust the value for the shared_buffers parameter according to the values in the following formulas.

For virtual machines with up to 2 GiB of memory, the formula used to compute the value of shared_buffers is memoryGib * 16384.

For virtual machines with more than 2 GiB, the formula used to compute the value of shared_buffers is memoryGib * 32768.

Based on the previous formula, the following table lists the values this server parameter would be set to depending on the amount of memory provisioned:

Memory size shared_buffers
2 GiB 32768
4 GiB 131072
8 GiB 262144
16 GiB 524288
32 GiB 1048576
48 GiB 1572864
64 GiB 2097152
80 GiB 2621440
128 GiB 4194304
160 GiB 5242880
192 GiB 6291456
256 GiB 8388608
384 GiB 12582912
432 GiB 14155776
672 GiB 22020096

temp_buffers

Attribute Value
Category Resource Usage / Memory
Description Sets the maximum number of temporary buffers used by each database session.
Data type integer
Default value 1024
Allowed values 100-1073741823
Parameter type dynamic
Documentation temp_buffers

work_mem

Attribute Value
Category Resource Usage / Memory
Description Sets the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files.
Data type integer
Default value 4096
Allowed values 4096-2097151
Parameter type dynamic
Documentation work_mem

Description

The work_mem parameter in PostgreSQL controls the amount of memory allocated for certain internal operations within each database session's private memory area. Examples of these operations are sorting and hashing.

Unlike shared buffers, which are in the shared memory area, work_mem is allocated in a per-session or per-query private memory space. By setting an adequate work_mem size, you can significantly improve the efficiency of these operations and reduce the need to write temporary data to disk.

Key points

  • Private connection memory: work_mem is part of the private memory that each database session uses. This memory is distinct from the shared memory area that shared_buffers uses.
  • Query-specific usage: Not all sessions or queries use work_mem. Simple queries like SELECT 1 are unlikely to require work_mem. However, complex queries that involve operations like sorting or hashing can consume one or multiple chunks of work_mem.
  • Parallel operations: For queries that span multiple parallel back ends, each back end could potentially use one or multiple chunks of work_mem.

Monitoring and adjusting work_mem

It's essential to continuously monitor your system's performance and adjust work_mem as necessary, primarily if query execution times related to sorting or hashing operations are slow. Here are ways to monitor performance by using tools available in the Azure portal:

  • Query performance insight: Check the Top queries by temporary files tab to identify queries that are generating temporary files. This situation suggests a potential need to increase work_mem.
  • Troubleshooting guides: Use the High temporary files tab in the troubleshooting guides to identify problematic queries.
Granular adjustment

While you're managing the work_mem parameter, it's often more efficient to adopt a granular adjustment approach rather than setting a global value. This approach ensures that you allocate memory judiciously based on the specific needs of processes and users. It also minimizes the risk of encountering out-of-memory issues. Here's how you can go about it:

  • User level: If a specific user is primarily involved in aggregation or reporting tasks, which are memory intensive, consider customizing the work_mem value for that user. Use the ALTER ROLE command to enhance the performance of the user's operations.

  • Function/procedure level: If specific functions or procedures are generating substantial temporary files, increasing the work_mem value at the specific function or procedure level can be beneficial. Use the ALTER FUNCTION or ALTER PROCEDURE command to specifically allocate more memory to these operations.

  • Database level: Alter work_mem at the database level if only specific databases are generating high numbers of temporary files.

  • Global level: If an analysis of your system reveals that most queries are generating small temporary files, while only a few are creating large ones, it might be prudent to globally increase the work_mem value. This action facilitates most queries to process in memory, so you can avoid disk-based operations and improve efficiency. However, always be cautious and monitor the memory utilization on your server to ensure that it can handle the increased work_mem value.

Determining the minimum work_mem value for sorting operations

To find the minimum work_mem value for a specific query, especially one that generates temporary disk files during the sorting process, start by considering the temporary file size generated during the query execution. For instance, if a query is generating a 20-MB temporary file:

  1. Connect to your database by using psql or your preferred PostgreSQL client.
  2. Set an initial work_mem value slightly higher than 20 MB to account for additional headers when processing in memory. Use a command such as: SET work_mem TO '25MB'.
  3. Run EXPLAIN ANALYZE on the problematic query in the same session.
  4. Review the output for "Sort Method: quicksort Memory: xkB". If it indicates "external merge Disk: xkB", raise the work_mem value incrementally and retest until "quicksort Memory" appears. The appearance of "quicksort Memory" signals that the query is now operating in memory.
  5. After you determine the value through this method, you can apply it either globally or on more granular levels (as described earlier) to suit your operational needs.