Designing a HA PostgreSQL Backend for Mattermost Message Store

Requirements

Mattermost must persistently store every channel message, file attachment, and searchable metadata while providing ACID guarantees. Reads need to be low‑latency to support real‑time messaging, and the system should scale horizontally by adding read replicas for query load.

Smallest Suitable Design

A single Mattermost server node connects to a dedicated PostgreSQL 13+ instance. The database is configured for WAL archiving and synchronous replication to a standby server, giving automatic failover with minimal data loss.

Example Mattermost database configuration (config.json)

{
  "SqlSettings": {
    "DriverName": "postgres",
    "DataSource": "postgres://mattermost:PASSWORD@PG_PRIMARY_HOST:5432/mattermost?sslmode=require&connect_timeout=10",
    "MaxIdleConns": 20,
    "MaxOpenConns": 100,
    "ConnMaxLifetime": 3600
  }
}

Replace PASSWORD and PG_PRIMARY_HOST with your service‑account password and the primary DB hostname. The sslmode=require flag forces TLS encryption.

PostgreSQL primary settings (postgresql.conf)

# WAL archiving
archive_mode = on
archive_command = 'test ! -f /mnt/walarchive/%f && cp %p /mnt/walarchive/%f'
# Synchronous replication to one standby
synchronous_standby_names = '1 * (pg_standby)'
# Basic tuning for a dedicated DB server
max_connections = 200
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 256MB
checkpoint_timeout = 15min
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1

After editing, restart PostgreSQL: sudo systemctl restart postgresql. Ensure the standby has a matching postgresql.conf and a recovery.conf (or standby.signal in PG 13+) pointing to the primary.

Trust/Data Boundaries

The Mattermost application trusts the database layer for data integrity. Network traffic between Mattermost and PostgreSQL must be TLS‑encrypted; verify this with:

# Run inside the PostgreSQL container or host as a privileged user
SELECT usename, ssl IS TRUE AS ssl_enabled
FROM pg_stat_ssl
WHERE usename = 'mattermost';

The result should show ssl_enabled = true. Limit the Mattermost service account to the mattermost schema via:

REVOKE CREATE ON SCHEMA public FROM mattermost;
GRANT USAGE, CREATE ON SCHEMA mattermost TO mattermost;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA mattermost TO mattermost;
ALTER DEFAULT PRIVILEGES IN SCHEMA mattermost GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO mattermost;

Operational Checks

• Replication lag: SELECT pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn() AS lag_bytes FROM pg_stat_replication;
• Connection pool usage: Mattermost reports SqlSettings.MaxOpenConns usage via its /api/v2/system/db endpoint (requires admin token).
• Query latency: monitor the 95th‑percentile of pg_stat_statements.mean_time for the Mattermost database.
• WAL archive health: ensure archive_status column in pg_stat_archiver shows zero failed attempts.
• Disk usage: alert when pg_database_size('mattermost') exceeds 80 % of the allocated data directory.

Set alerts in your monitoring system (e.g., Prometheus + Alertmanager) with thresholds: replication lag > 5 s, connection utilization > 80 %, average query latency > 200 ms, WAL archive failures > 0, disk usage > 80 %.

Failure Modes and Design‑Change Triggers

Loss of the primary PostgreSQL instance causes Mattermost to switch to read‑only mode until a standby is promoted. After promotion, writes resume automatically.

Sustained write latency > 200 ms or replication lag > 5 s for more than five minutes indicates the single‑primary design is becoming a bottleneck. At that point consider:

• Adding read replicas and configuring Mattermost to use them for read‑only queries (via DataSourceReplicas in config.json).
• Migrating to a clustered PostgreSQL solution such as Citus (for horizontal sharding) or Patroni‑managed PostgreSQL clusters for automated failover and load balancing.

Changing the database schema outside Mattermost’s approved migration scripts can break plugin compatibility and upgrade paths; always use the mattermost plugin CLI or the official upgrade process.

Practical Verification Steps

1. Confirm TLS: run the SQL query above and verify ssl_enabled = true for the Mattermost user.
2. Check replication health: execute the lag query on the primary; ensure the result is below your alert threshold (e.g., < 1 MB).
3. Simulate failover: stop the primary (sudo systemctl stop postgresql), observe Mattermost API returning 503 Service Unavailable for writes, then promote the standby (pg_ctl promote -D /var/lib/postgresql/13/main). After promotion, verify that a new post can be created via the Mattermost UI or API.
4. Review connection pool: call GET /api/v2/system/db with an admin token and ensure inUse stays well below max during peak load.

These steps give confidence that the HA PostgreSQL backend meets Mattermost’s reliability and performance requirements without introducing unverified claims.