Understanding memory units in PostgreSQL

In this recipe, we will be discussing the memory components of PostgreSQL instances.

Getting ready

PostgreSQL uses several memory components for each unique usage. That is, it uses dedicated memory areas for transactions, sort/join operations, maintenance operations, and so on. If the configured memory component doesn't fit the usage of the live application, then we may hit a performance issue, where PostgreSQL tries for more I/O.

How to do it...

Let us discuss about, how to tune the major PostgreSQL memory components:

shared_buffers

This is the major memory area that PostgreSQL uses for executing the transactions. On most Linux operating systems, it is recommended to allocate at least 25% of RAM as shared buffers, by leaving 75% of RAM to OS, OS cache, and for other PostgreSQL memory components. PostgreSQL provide multiple ways to create these shared buffers. The supported shared memory creation techniques are POSIX shared memory, System V shared memory, memory mapped files, and windows, in which we specify one method as an argument to the default_shared_memory_type.

temp_buffers

PostgreSQL utilizes this memory area for holding the temporary tables of each session, which will be cleared when the connection is closed.

work_mem

This is the memory area that PostgreSQL tries to allocate for each session when the query requires any joining, sorting, or hashing operations. We need to be a bit cautious while tuning this parameter, as this memory will be allocated for each connection whenever it is needed.

maintenance_work_mem

PostgreSQL utilizes this memory area for performing maintenance operations such as VACUUM, ALTER TABLE, CREATE INDEX, and so on. Autovacuum worker processes also utilize this memory area, if the autovacuum_work_mem parameter is set to -1.

wal_buffers

PostgreSQL utilizes this memory area for holding incoming transactions, which will be flushed immediately to disk (pg_xlog files) on every commit operation. By default, this parameter is configured to utilize 3% of the shared_buffers memory to hold incoming transactions. This setting may not be sufficient for busy databases, where multiple concurrent commits happen frequently.

max_stack_depth

PostgreSQL utilizes this memory area for function call/expression execution stacks. By default, it's configured to use 2 MB, which we can increase up to the kernel's configured stack size (ulimit -s).

effective_cache_size

This is a logical setting for PostgreSQL instances, which gives a hint about how much cache (shared_buffers and OS cache) is available at this moment. Based on this cache setting, the optimizer will generate a better plan for the SQL. It is recommended to set the 75% of RAM as a value for this parameter.

How it works...

In the preceding memory components, not all will get instantiated during PostgreSQL startup. Only shared_buffers and wal_buffers will be initialized, and the remaining memory components will be initialized whenever their presence is needed.

Note

For more information about these parameters, refer to the following URL: https://www.postgresql.org/docs/9.6/static/runtime-config-resource.html.