Configure Read/Write Splitting for Mattermost with a Patroni-Managed PostgreSQL Cluster

Marco Kundt

Updated July 08, 2026 14:19

Applies to: Mattermost Server (self-managed) on PostgreSQL, high-availability cluster deployments using Patroni + HAProxy + pgBouncer

Symptoms: Read queries are not offloaded to PostgreSQL replicas in a Patroni cluster, so all traffic lands on the primary.

🛑 Problem

In the standard Patroni stack (Patroni + HAProxy + pgBouncer behind a VIP), the proxy layer routes by node role, not by SQL statement type, so it cannot split reads from writes on its own. HAProxy is a layer-4 load balancer that picks a node using Patroni's REST health checks (GET /primary and GET /replica), and pgBouncer is a connection pooler, not a read/write splitter. If Mattermost addresses this cluster with a single DataSource and an empty DataSourceReplicas, every query (reads and writes) hits the primary, and the replicas serve only streaming replication and failover standby.

For the full explanation of node-role versus SQL-aware proxies and why DataSourceReplicas is always required in this class of setup, see the companion article: Why DataSourceReplicas Is Always Recommended: Node-Role vs SQL-Aware Proxies.

Symptoms

All read and write load concentrated on the PostgreSQL primary node;
replica nodes show streaming-replication traffic only, with no query load.

Additional symptoms:

SqlSettings.DataSourceReplicas is left empty ([]).
Primary node connection count and CPU are high while replicas are idle.

✅ Solution

Let Mattermost perform the read/write split by pointing DataSource (writes) and DataSourceReplicas (reads) at two separate HAProxy listeners: one bound to Patroni's /primary health check and one bound to /replica. The examples below assume 3 Mattermost application nodes and a 3-node PostgreSQL cluster behind a VIP (a single floating IP reassigned to the current primary.

Configure Mattermost DataSource and DataSourceReplicas

Edit SqlSettings in /opt/mattermost/config/config.json on each application node. Point DataSource at the VIP primary port (5000) and DataSourceReplicas at the VIP replica port (5001). Every read hits the replica endpoint, and HAProxy balances it across the healthy replicas behind it.

"SqlSettings": {
    "DriverName": "postgres",
    "DataSource": "postgres://mmuser:PASSWORD@<VIP>:5000/mattermost?sslmode=verify-ca&sslrootcert=/opt/mattermost/certs/ca.crt&connect_timeout=10&binary_parameters=yes",
    "DataSourceReplicas": ["postgres://mmuser:PASSWORD@<VIP>:5001/mattermost?sslmode=verify-ca&sslrootcert=/opt/mattermost/certs/ca.crt&connect_timeout=10&binary_parameters=yes"],
    "DataSourceSearchReplicas": [],
    "MaxIdleConns": 50,
    "MaxOpenConns": 100
}

⚠️ Important: A restart of the Mattermost server is required for SqlSettings connection-string changes to take effect. These settings are not reloadable from the System Console.

Size the Connection Pools

Base pool sizes on Mattermost's MaxOpenConns: 100 with headroom for supporting processes. Worst-case connection math for 3 application nodes:

100 MaxOpenConns x 3 app nodes x 2 datasources (writer + reader) = 600 max client connections
Per-node worst case = 300 inbound connections to any single node's pgBouncer
(a node answers either /primary OR /replica, never both)

Configure HAProxy

On each HAProxy instance, define one listener for writes (checks /primary) and one for reads (checks /replica). Replace 10.0.0.x:6432 with each node's pgBouncer address and 8008 with the Patroni REST port.

global
    maxconn 750   # >= sum of both listeners

# Writes: only the leader answers /primary, so all connections here go to the primary
listen primary
    bind *:5000
    option httpchk HEAD /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    maxconn 350
    server node1 10.0.0.1:6432 maxconn 100 check port 8008
    server node2 10.0.0.2:6432 maxconn 100 check port 8008
    server node3 10.0.0.3:6432 maxconn 100 check port 8008

# Reads: only healthy replicas answer /replica; HAProxy load-balances across them
listen replicas
    bind *:5001
    option httpchk HEAD /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    maxconn 350
    server node1 10.0.0.1:6432 maxconn 100 check port 8008
    server node2 10.0.0.2:6432 maxconn 100 check port 8008
    server node3 10.0.0.3:6432 maxconn 100 check port 8008

Configure pgBouncer

On each pgBouncer node set:

pool_mode = transaction
max_client_conn = 400      # >= HAProxy per-server maxconn (350), small slack for admin/psql
default_pool_size = 100
reserve_pool_size = 20

Configure PostgreSQL

On each PostgreSQL node set:

max_connections = 200
superuser_reserved_connections = 5

⚠️ Important: The max_connections, maxconn, and MaxOpenConns values above leave sensible headroom for supporting processes (pgBackRest, Patroni, admin connections, monitoring). Treat them as a starting point and adjust over time to match actual demand. Monitor Mattermost's connection metrics (Grafana) alongside pgBouncer and PostgreSQL host monitoring.

Account for Replica Lag, Consistency, and Failover

Use the defaults and keep the following behavior in mind:

Lag / consistency: Replication is asynchronous, so replicas trail the primary slightly and a replica read can occasionally miss the latest write. Mattermost already routes reads that must be current to the primary, so impact is negligible. Note that ReplicaLagSettings only reports lag as metrics; it does not reroute traffic.
Failover: Because DataSource and DataSourceReplicas point at the /primary and /replica endpoints, a Patroni promotion requires no Mattermost configuration change. If all replicas are down, reads fall back to the primary.

Additional Resources

For more information, see:

Why DataSourceReplicas Is Always Recommended: Node-Role vs SQL-Aware Proxies

High Availability Cluster-Based Deployment - Database Configuration

Patroni HAProxy Template Reference