Tuning PostgreSQL Server Parameters

Last modified: August 13, 2026

Introduction

Every Mendix on Azure cluster stores the data of all its Mendix app environments in a single shared Azure Database for PostgreSQL Flexible Server. You can change the server parameters of that server yourself, either in the Microsoft Azure Portal or with the Azure CLI. This is useful when the default configuration does not suit your workload, for example when complex OQL queries spill sorts and hash joins to disk instead of keeping them in memory.

This document describes how to change these parameters, which parameters are safe to tune, and which parameters you must leave alone.

Tuning the database for your own apps is a customer responsibility under the shared responsibility model for Mendix on Azure. You choose the values, you validate them against your own workload, and you own the result. Mendix and Microsoft remain responsible for operating the underlying PostgreSQL service itself.

Prerequisites

Before you begin, make sure to fulfill the following prerequisites:

  • Ensure that you have the Owner or Contributor role on the Mendix on Azure Managed Application, so that you can open the resources in the Managed Resource Group of your Mendix on Azure environment.
  • Ensure that you have a representative test cluster available. Validate every change there before you apply it to a production cluster.
  • Ensure that you know which apps share the server. All app environments in the cluster use the same PostgreSQL server, so a parameter change affects all of them.

Changing Parameters in the Microsoft Azure Portal

To change a server parameter in the Microsoft Azure Portal, perform the following steps:

  1. Sign in to the Microsoft Azure Portal.
  2. Go to the Managed Resource Group of your Mendix on Azure environment.
  3. Select the PostgreSQL server resource (type: Azure Database for PostgreSQL Flexible Server).
  4. Go to Settings > Server parameters.
  5. Search for the parameter name, set the new value, and click Save.

If the parameter requires a restart, the Azure Portal marks it as pending restart and the new value only takes effect after you restart the server.

Changing Parameters with the Azure CLI

To set a parameter with the Azure CLI, use the following command:

az postgres flexible-server parameter set \
  --resource-group <resource-group> \
  --server-name <server-name> \
  --name work_mem \
  --value 131072

Memory parameters are expressed in kilobytes, so the value 131072 sets work_mem to 128 MB.

To check whether a parameter you changed is waiting for a server restart, use the following command:

az postgres flexible-server parameter show \
  --resource-group <resource-group> \
  --server-name <server-name> \
  --name max_worker_processes \
  --query isConfigPendingRestart

Changing Parameters with SQL

Some parameters can also be set from a PostgreSQL session, without modifying the server configuration. This takes effect immediately and affects nothing outside the session or role you change.

Setting a Parameter for the Current Session

To set a parameter for the current session only, use the following statements:

SET work_mem = '128MB';
SET hash_mem_multiplier = 3.0;
SET max_parallel_workers_per_gather = 4;

Setting a Parameter for a Database Role

To set a parameter for a database role, so that it applies to every new session opened by that role, use the following statements:

ALTER ROLE mendix SET work_mem = '128MB';
ALTER ROLE mendix SET hash_mem_multiplier = 3.0;
ALTER ROLE mendix SET max_parallel_workers_per_gather = 4;

Verifying Values

To verify the values that are in effect, use the following statements:

SHOW work_mem;
SELECT name, setting, source FROM pg_settings WHERE source <> 'default';

Parameters You Can Tune

The following parameters are validated for use on Mendix on Azure. The context of a parameter determines how you can change it and whether a restart is needed.

Parameter Context How to Change Restart Required
work_mem USERSET SQL, Azure Portal, or Azure CLI No
hash_mem_multiplier USERSET SQL, Azure Portal, or Azure CLI No
max_parallel_workers_per_gather USERSET SQL, Azure Portal, or Azure CLI No
max_parallel_workers SIGHUP Azure Portal or Azure CLI Only if you also raise max_worker_processes to accommodate the new value
max_worker_processes POSTMASTER Azure Portal or Azure CLI only, not SQL Yes, always
random_page_cost USERSET SQL, Azure Portal, or Azure CLI No

The following values are starting points, not targets. Measure the effect on your own queries before you keep a value.

Parameter Transactional Apps Reporting and Analytical Queries Purpose
work_mem 32-64 MB 128-256 MB Memory available per sort or hash operation before it spills to disk
hash_mem_multiplier 2.0 3.0 Multiplier applied to work_mem for hash-based operations
max_parallel_workers_per_gather 2-4 4-8 Parallel workers a single query may use
max_parallel_workers 4 8-16 Parallel workers available across all queries, capped by max_worker_processes
max_worker_processes 8 (default) 16 Total background worker slots on the server, raise only to make room for a higher max_parallel_workers
random_page_cost 1.1 1.1 Cost estimate for random reads, lowered because the server uses SSD storage

Parameters You Must Not Change

Parameter Impact of Changing It
shared_buffers Out-of-memory conditions and server crashes. The value is sized to the compute tier of the server.
max_connections Memory exhaustion, or app environments that can no longer connect. The value is sized to the compute tier of the server.
shared_preload_libraries Loads untrusted code into the server process at startup, and can prevent the server from starting at all.
fsync, synchronous_commit Permanent and unrecoverable data loss if the server or the underlying host fails.
wal_level Breaks the read replica and the automated backup and point-in-time restore capability of the server.
max_wal_size, checkpoint_timeout Long recovery times after a failure, and unpredictable restart behavior.

To change the compute tier or the storage performance tier of the server instead, use the Edit Cluster flow in the Mendix on Azure Portal, as described in Configuring Mendix on Azure.

Testing a Change

To measure whether a parameter change helps, perform the following steps:

  1. Establish a baseline. In your SQL client, enable timing by running the following command:

    \timing on

  2. Capture the query plan before the change by running the following command:

    EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

  3. Apply the tuning in the current session only by running the following command:

    SET work_mem = '128MB';
SET max_parallel_workers_per_gather = 4;

  4. Run the same query again and compare the execution time and the plan. Look for the disappearance of external merge or disk-based hash operations, and for the appearance of Parallel nodes in the plan.

  5. Keep the change only if the improvement is repeatable across several runs. Then apply it at role level or at server level.

Troubleshooting

The following section lists common outcomes and how to address them.

Raising Max_parallel_workers_per_gather Does Not Increase Speed

The query is not faster after raising the value of themax_parallel_workers_per_gather parameter.

Solution

Run SHOW max_parallel_workers;. If it is 0 or too low, the server has no worker slots to hand out, and max_parallel_workers needs raising first. Confirm with EXPLAIN that the plan contains Parallel nodes at all, because not every query can be parallelized.

Query Spills

The query still spills to disk.

Solution

Raise work_mem further in a session and re-run EXPLAIN (ANALYZE, BUFFERS). Sort and hash nodes report whether they used memory or disk.

Out-of-Memory Errors

Apps report out-of-memory errors or dropped connections after a change

Solution

Lower work_mem immediately and reset any other memory-related change. The shared server is used by every app environment in the cluster.

Value Does Not Take Effect

A new value does not take effect.

Solution

Check whether the parameter is waiting for a restart, as described in Changing Parameters with the Azure CLI.

Undo Change

You need to undo a session, role, or server change.

Solution

Perform one of the following actions, depending on the type of change that you want to undo:

  • To undo a change to the current session, run RESET work_mem;.
  • To undo a role change, run ALTER ROLE mendix RESET work_mem;.
  • To undo a server change, set the parameter back to its default value in Settings > Server parameters in the Microsoft Azure Portal.

Best Practices

Apply the following practices when tuning server parameters:

  • Change one parameter at a time, so that you can attribute any change in behavior to it.
  • Test in a non-production cluster first, then at session level, then at role level, and only then at server level.
  • Prefer session-level and role-level changes over server-level changes. They are reversible without a restart and they do not affect other app environments.
  • Record the original value of every parameter you change, so that you can revert it.
  • Monitor CPU, memory, and connection metrics for at least 24 hours after a change.
  • Avoid parameter changes as a first response to slow queries. Missing indexes and inefficient OQL are more common causes, and fixing those in the app benefits every environment.

Read More