Optimiser PostgreSQL pour une stack open source : tuning, maintenance et haute disponibilité

Optimiser PostgreSQL pour une stack open source : tuning, maintenance et haute disponibilité

PostgreSQL est le socle de données de la plupart des briques open source auto-hébergées : CKAN, Keycloak, Nextcloud, Gitea, Superset, Drupal. Par défaut, sa configuration est volontairement conservatrice pour fonctionner sur n’importe quelle machine. Résultat : sur un serveur de production avec 16 ou 32 Go de RAM, PostgreSQL n’exploite qu’une fraction de ses capacités. Cet article présente les leviers concrets pour optimiser ses performances, automatiser sa maintenance et préparer la haute disponibilité.

Les paramètres critiques à ajuster

La configuration par défaut de PostgreSQL (postgresql.conf) mérite d’être revue dès la mise en production. Voici les paramètres les plus impactants :

shared_buffers : la mémoire partagée allouée au cache de données. La valeur par défaut (128 Mo) est dérisoire. La recommandation courante est de la fixer à 25 % de la RAM totale du serveur, soit 4 Go pour un serveur de 16 Go. Au-delà de 8 Go, les gains deviennent marginaux sur la plupart des charges de travail.

effective_cache_size : ce paramètre n’alloue pas de mémoire, il indique au planificateur de requêtes la quantité de cache disponible (OS + PostgreSQL). Le fixer à 75 % de la RAM permet au planificateur de choisir des plans de requêtes plus agressifs, notamment l’utilisation d’index plutôt que des scans séquentiels.

work_mem : la mémoire allouée par opération de tri ou de jointure. Avec la valeur par défaut de 4 Mo, les tris complexes débordent sur disque. Pour une stack open data avec des requêtes analytiques, 64 Mo à 128 Mo est un bon point de départ. Attention : cette valeur est multipliée par le nombre de connexions actives et d’opérations parallèles.

maintenance_work_mem : la mémoire dédiée aux opérations de maintenance (VACUUM, CREATE INDEX, ALTER TABLE). La porter à 512 Mo ou 1 Go accélère considérablement ces opérations, notamment le reindexage des grosses tables CKAN.

wal_buffers : le tampon pour les Write-Ahead Logs. Le fixer à 64 Mo est suffisant pour la plupart des charges de travail. Au-delà, l’impact est négligeable.

Exemple de configuration pour un serveur 16 Go / 4 vCPU

# postgresql.conf — serveur 16 Go RAM, 4 vCPU, SSD
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 128MB
maintenance_work_mem = 1GB
wal_buffers = 64MB

# Parallélisme
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_worker_processes = 8

# Planificateur
random_page_cost = 1.1          # SSD (défaut 4.0 pour HDD)
effective_io_concurrency = 200  # SSD (défaut 1 pour HDD)

# WAL et checkpoints
checkpoint_completion_target = 0.9
wal_compression = on
max_wal_size = 2GB
min_wal_size = 512MB

# Logging
log_min_duration_statement = 500  # Log les requêtes > 500ms
log_checkpoints = on
log_lock_waits = on

L’outil PGTune permet de générer une configuration de départ adaptée à votre matériel. C’est un excellent point de départ, à affiner ensuite en fonction de la charge réelle.

Automatiser la maintenance avec pg_cron

PostgreSQL nécessite une maintenance régulière pour rester performant. Le processus autovacuum s’en charge partiellement, mais il est souvent insuffisant sur des bases très actives comme celles de CKAN ou Nextcloud. L’extension pg_cron permet de planifier des tâches de maintenance directement dans PostgreSQL :

-- Installer l'extension
CREATE EXTENSION pg_cron;

-- VACUUM ANALYZE hebdomadaire sur la base CKAN
SELECT cron.schedule('vacuum-ckan', '0 3 * * 0',
  'VACUUM (VERBOSE, ANALYZE) ckan_default');

-- REINDEX mensuel des tables volumineuses
SELECT cron.schedule('reindex-ckan', '0 4 1 * *',
  'REINDEX DATABASE ckan_default');

-- Nettoyage des connexions inactives depuis plus de 30 min
SELECT cron.schedule('kill-idle', '*/10 * * * *',
  $$SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE state = ''idle''
    AND state_change < now() - interval ''30 minutes''
    AND pid <> pg_backend_pid()$$);

Surveiller les performances avec pg_stat_statements

L’extension pg_stat_statements est indispensable pour identifier les requêtes lentes et les goulets d’étranglement. Elle collecte des statistiques sur toutes les requêtes exécutées :

-- Activer dans postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements;

-- Top 10 des requêtes les plus lentes (temps total cumulé)
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round((100 * total_exec_time /
    sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  left(query, 120) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Ces statistiques se couplent naturellement avec Grafana et le dashboard PostgreSQL Database pour une visualisation en temps réel. Si vous avez déjà déployé la stack Prometheus/Grafana/Loki (voir notre article sur l’observabilité), l’exporter postgres_exporter fait le pont entre les deux.

Pooling de connexions avec PgBouncer

Chaque connexion PostgreSQL consomme environ 10 Mo de RAM. Quand CKAN, Keycloak, Nextcloud et Gitea ouvrent chacun 20 à 50 connexions, le serveur peut rapidement saturer. PgBouncer s’interpose comme un pool de connexions léger :

# pgbouncer.ini

[databases]

ckan = host=127.0.0.1 port=5432 dbname=ckan_default keycloak = host=127.0.0.1 port=5432 dbname=keycloak nextcloud = host=127.0.0.1 port=5432 dbname=nextcloud

[pgbouncer]

listen_port = 6432 listen_addr = 0.0.0.0 auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction # Le plus efficace pour les applications web default_pool_size = 20 max_client_conn = 200 max_db_connections = 50

Le mode transaction est le plus adapté aux applications web : la connexion au serveur PostgreSQL est libérée dès que la transaction se termine, ce qui permet de servir 200 clients avec seulement 50 connexions réelles.

Préparer la haute disponibilité avec la réplication streaming

Pour les environnements de production critiques, PostgreSQL propose la réplication streaming native. Un serveur secondaire (replica) reçoit les WAL en temps réel et peut prendre le relais en cas de panne du primaire :

# Sur le primaire — postgresql.conf
wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB

# Sur le primaire — pg_hba.conf
host replication replicator 10.0.0.0/24 scram-sha-256

# Sur le secondaire — initialisation
pg_basebackup -h primary.local -D /var/lib/postgresql/16/main \
  -U replicator -Fp -Xs -P -R

Le flag -R crée automatiquement le fichier standby.signal et configure la connexion au primaire. Pour le basculement automatique (failover), des outils comme Patroni (avec etcd ou Consul) ou repmgr ajoutent une couche d’orchestration qui détecte les pannes et promeut automatiquement le secondaire.

Intégration Docker Compose

Si votre PostgreSQL tourne dans un conteneur Docker (ce qui est courant dans une stack auto-hébergée), la configuration se monte via un volume :

# docker-compose.yml (extrait)
services:
  postgres:
    image: postgres:16-alpine
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./postgresql.conf:/etc/postgresql/postgresql.conf:ro
    command: postgres -c config_file=/etc/postgresql/postgresql.conf
    shm_size: '4g'   # Aligner avec shared_buffers
    deploy:
      resources:
        limits:
          memory: 16g
        reservations:
          memory: 8g
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 10s
      timeout: 5s
      retries: 5

Le paramètre shm_size est crucial : Docker limite par défaut la mémoire partagée à 64 Mo, ce qui fait échouer un shared_buffers de 4 Go. Alignez-le avec la valeur de shared_buffers.

En résumé

PostgreSQL est un moteur de base de données remarquable, mais ses performances par défaut sont bridées. En ajustant une dizaine de paramètres, en mettant en place PgBouncer, en automatisant la maintenance et en activant le monitoring avec pg_stat_statements, on obtient un socle de données solide, performant et observable — exactement ce qu’exige une stack open source en production. La réplication streaming et Patroni permettent ensuite de passer à la haute disponibilité quand le contexte l’exige.