Queries against large sets of table rows may need a lot of server memory to sort, join, aggregate, or sub select data. Azure Database for PostgreSQL – Flexible Server exposes number of important parameters on portal parameters page that mirror important global settings exposed by PostgreSQL via postgres.conf configuration file.
You can navigate to Flexible Server global parameters page via Azure Portal in Azure Portal -> Azure Database for PosgreSQL Servers -> Your Flexible Server -> Server Parameters. But before we discuss some of the memory related parameters, lets first take a look at general Postgres memory architecture.
Figure 1. PostgreSQL Flexible Server parameters page in Azure Portal.
Memory usage in Postgres can be classified in two main categories.
First is known as Local Memory. This is memory allocated by each Postgres backend process for itself. In PostgreSQL, each backend process allocates local memory for query processing; each area is divided into sub-areas whose sizes are either fixed or variable. These sub areas are listed below:
PostgreSQL execution engine, aka Executor, uses this area for sorting tuples by ORDER BY and DISTINCT operations, and for joining tables by merge-join and hash-join operations.
This memory are is used by maintenance operations , such as VACUUM, REINDEX, etc.
Executor uses this area for temporary table storage
Second is Shared Memory , which is memory used by all Postgres backend processes. This area is allocated by a PostgreSQL server when it starts up. This area is also divided into several sub-areas, such as:
PostgreSQL supports the WAL (Write ahead log) mechanism to ensure that no data is lost after a server failure. WAL data is really a transaction log in PostgreSQL and WAL buffer is a buffering area of the WAL data before writing it to a persistent storage.
Commit Log(CLOG) keeps the states of all transactions (e.g., in_progress, committed, aborted) for Concurrency Control (CC) mechanism.
Figure 2. PostgreSQL memory architecture
Based on above here are some important server parameters that impact performance you may wish to know about for memory management in Azure Database for PostgreSQL – Flexible Server:
Figure 3. Shared Buffers parameter on Flexible Server parameters page in Azure Portal
The example below shows how to allow specific query use up to 128 MB physical memory to perform sorting and then resets work_mem value for the current session to the current default value.
SET work_mem = ‘128MB’; SELECT * FROM products ORDER BY LOWER(product_name); RESET work_mem;
This parameter estimates how much memory is available for disk caching by the operating system and within the database itself. The PostgreSQL query planner decides whether it’s fixed in RAM or not. Index scans are most likely to be used against higher values; otherwise, sequential scans will be used if the value is low. The default value of effective_cache_size = 4GB. If your Flexible Server has higher than 8 GB of RAM you can look at setting the value to the 50% of total system memory.
We are hoping that you find this blog article helpful and are always interested how you plan to use Flexible Server offering to drive performance to your critical applications. Additional information on topics discussed above can be found in following documents:
We’re always eager to get your feedback, so please reach out via email to Ask Azure DB for PostgreSQL.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.