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	1-1000
Parameter type	dynamic
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	1-1000
Parameter type	dynamic
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-1000
Parameter type	dynamic
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-1000
Parameter type	dynamic
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-1000
Parameter type	dynamic
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.