Faut-il pgbouncer obligatoirement en production ?

Quasiment toujours en production. Sans connection pooling, chaque connexion app = process PostgreSQL = environ 10 MB. 100 utilisateurs simultanés = 1 GB juste pour connexions. pgbouncer multiplexe 1000 clients sur 25 connexions backend efficacement.

REINDEX bloque-t-il l'écriture en PostgreSQL ?

REINDEX TABLE bloque. REINDEX CONCURRENTLY (depuis PostgreSQL 12) ne bloque pas mais prend plus de temps. À utiliser en production. Préférer toujours la version concurrente sauf maintenance planifiée avec downtime explicite et acceptable.

PostgreSQL tutoriel production : optimisation, indexation, performance

Lecture : 16 minutes · Niveau : développeur intermédiaire · Mise à jour : avril 2026

PostgreSQL est devenu le choix par défaut des développeurs en 2026, numéro un Stack Overflow Developer Survey plusieurs années consécutives. Mais le passage du « ça tourne en local » au « ça tient en production » nécessite des compétences spécifiques : configuration postgresql.conf, indexation stratégique, lecture d’EXPLAIN ANALYZE, partitioning, replication, monitoring. Ce tutoriel pratique couvre toute la chaîne pour une PME ou un freelance qui déploie une base PostgreSQL sérieuse — avec exemples exécutables, valeurs de référence, et astuces issues de l’expérience terrain.

Pour le contexte stratégique global (familles BDD, choix), voir le pillar Bases de données PME.

Sommaire

Installation production sur VPS Linux
Configuration postgresql.conf essentielle
Indexation : stratégie et types
EXPLAIN ANALYZE : lire un plan d’exécution
Optimisation des requêtes courantes
Partitioning pour grosses tables
Replication et haute disponibilité
Backup et restoration
Monitoring et observabilité
Sécurité production
Pièges fréquents
FAQ

1. Installation production sur VPS Linux

Prérequis. VPS Ubuntu 22.04 ou 24.04, 4 GB RAM minimum (8 GB recommandé), SSD.

Installation PostgreSQL 17.

sudo apt update
sudo apt install -y wget ca-certificates
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update
sudo apt install -y postgresql-17 postgresql-contrib-17

Vérification.

sudo systemctl status postgresql
sudo -u postgres psql -c "SELECT version();"

Création utilisateur applicatif.

sudo -u postgres psql
CREATE DATABASE monapp;
CREATE USER monapp_user WITH ENCRYPTED PASSWORD 'mot-de-passe-fort-aleatoire';
GRANT ALL PRIVILEGES ON DATABASE monapp TO monapp_user;
\c monapp
GRANT ALL ON SCHEMA public TO monapp_user;
\q

Activer extensions utiles.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;       -- recherche fuzzy
CREATE EXTENSION IF NOT EXISTS unaccent;      -- recherche sans accents
CREATE EXTENSION IF NOT EXISTS uuid-ossp;     -- UUIDs
CREATE EXTENSION IF NOT EXISTS vector;        -- pgvector pour embeddings IA
CREATE EXTENSION IF NOT EXISTS postgis;       -- géospatial si utile

2. Configuration postgresql.conf essentielle

Fichier /etc/postgresql/17/main/postgresql.conf. Valeurs de référence pour 8 GB RAM dédiés DB.

# Mémoire
shared_buffers = 2GB                    # 25% RAM
effective_cache_size = 6GB              # 75% RAM
work_mem = 16MB                         # par opération de tri/hash
maintenance_work_mem = 512MB            # VACUUM, CREATE INDEX

# Connexions
max_connections = 100                   # via pgbouncer si besoin plus
superuser_reserved_connections = 3

# WAL et checkpoints
wal_level = replica                     # ou 'logical' pour CDC
wal_buffers = 16MB
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min

# Disque
random_page_cost = 1.1                  # SSD (4.0 si HDD rotatif)
effective_io_concurrency = 200          # SSD

# Logging
log_min_duration_statement = 500        # log requêtes > 500ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0

# Statistiques
track_io_timing = on
shared_preload_libraries = 'pg_stat_statements'

# Autovacuum
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

Appliquer.

sudo systemctl restart postgresql

Vérifier.

SHOW shared_buffers;
SHOW effective_cache_size;

Outils d’aide.
– pgtune.leopard.in.ua : générateur configuration selon hardware.
– Modifier seulement avec compréhension — chaque paramètre a impact mémoire/perf.

3. Indexation : stratégie et types

Types d’index PostgreSQL.
– B-tree (défaut) : tri, égalité, ranges. 95 % des cas.
– Hash : égalité stricte uniquement, rarement meilleur que B-tree.
– GIN : full-text, JSONB, arrays, trigram (pg_trgm).
– GiST : géospatial, ranges, similarity.
– BRIN : Block Range Index — données chronologiques massives, peu d’espace.
– SP-GiST : structures non-équilibrées.
– Hash, BRIN partial : niches spécifiques.

Création d’index.

-- B-tree simple
CREATE INDEX idx_users_email ON users(email);

-- Composite (ordre important !)
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- Partiel (économise espace)
CREATE INDEX idx_orders_active ON orders(created_at DESC)
  WHERE status = 'active';

-- INCLUDE (covering index, évite lectures table)
CREATE INDEX idx_orders_user_covering ON orders(user_id)
  INCLUDE (total, status);

-- GIN sur JSONB
CREATE INDEX idx_products_attrs ON products USING GIN(attributes);

-- Trigram pour recherche LIKE '%xxx%'
CREATE INDEX idx_users_name_trgm ON users USING GIN(name gin_trgm_ops);

-- BRIN pour timestamp massive
CREATE INDEX idx_logs_created_brin ON logs USING BRIN(created_at);

Concurrent (sans bloquer écriture).

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Identifier index manquants.

SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_scan DESC;

Identifier index inutilisés.

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_%';

Bonnes pratiques.
– Indexer les colonnes utilisées en WHERE, JOIN, ORDER BY.
– Composite : colonne la plus sélective en premier.
– Index partiels pour filtres récurrents (réduit taille).
– INCLUDE pour covering indexes (évite Bitmap Heap Scan).
– Pas trop d’index : ralentit écritures.
– REINDEX périodique sur tables très modifiées.

4. EXPLAIN ANALYZE : lire un plan d’exécution

Commande.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;

Sortie type.

Index Scan using idx_orders_user_id on orders
  (cost=0.43..8.45 rows=1 width=64) (actual time=0.012..0.014 rows=1 loops=1)
  Index Cond: (user_id = 42)
  Buffers: shared hit=4
Planning Time: 0.082 ms
Execution Time: 0.030 ms

Lire le plan.
– Seq Scan : lecture séquentielle entière → mauvais signe sur grosse table sans WHERE.
– Index Scan : utilisation index → bon.
– Index Only Scan : requête satisfaite par index seul → excellent.
– Bitmap Heap Scan : utilisation index puis lecture table → ok, mais covering index fait mieux.
– Hash Join, Merge Join, Nested Loop : différentes stratégies de jointure selon volumes.

Métriques clés.
– cost=… : estimation coût (relatif).
– rows=… : nombre de lignes estimé / réel (écart = stats à mettre à jour).
– actual time=… : temps réel.
– Buffers : pages lues (hit = cache, read = disque).
– Execution Time : total.

Outils visualisation.
– explain.dalibo.com (français) : visualiser plan EXPLAIN.
– pev.dalibo.com : navigation interactive plan.

Statistiques à jour.

ANALYZE orders;     -- table spécifique
ANALYZE;            -- toutes

L’autovacuum le fait automatiquement, mais ANALYZE manuel après gros import.

5. Optimisation des requêtes courantes

Top requêtes lentes.

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Cibler en priorité les requêtes à mean_exec_time haut OU calls * mean_exec_time haut (impact total).

Patterns à éviter.

N+1 query. Une requête + N requêtes par résultat. Solution : JOIN ou eager loading ORM.

-- Mauvais (N+1)
SELECT * FROM users;
-- puis pour chaque user : SELECT * FROM orders WHERE user_id = ?

-- Bon
SELECT u.*, o.* FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

SELECT *. Récupérer toutes colonnes alors que peu nécessaires. Préférer colonnes explicites.

OR vs UNION.

-- Souvent plus lent
SELECT * FROM products WHERE name LIKE '%phone%' OR description LIKE '%phone%';

-- Souvent plus rapide
SELECT * FROM products WHERE name LIKE '%phone%'
UNION
SELECT * FROM products WHERE description LIKE '%phone%';

LIMIT sans ORDER BY. Résultat non-déterministe.

OFFSET grand. Lent sur grosses tables. Préférer keyset pagination :

-- Mauvais
SELECT * FROM orders ORDER BY created_at DESC OFFSET 100000 LIMIT 20;

-- Bon (curseur)
SELECT * FROM orders WHERE created_at < '2026-04-01 12:34:56'
ORDER BY created_at DESC LIMIT 20;

COUNT(*) sur grosse table. Lent. Alternatives :
– pg_class.reltuples pour estimation rapide.
– Compteur dénormalisé si besoin exact + temps réel.

JSONB queries. Indexer avec GIN, requêter avec opérateurs ->, ->>, @>.

CTE matérialisées. En PostgreSQL 12+, CTE peuvent être inlinées. Préfixer WITH ... AS MATERIALIZED si besoin matérialisation forcée.

6. Partitioning pour grosses tables

Quand partitionner. Tables > 100 GB ou > 100M lignes avec patterns d’accès clairs (par date, par tenant, par région).

Partitioning par date (le plus commun).

CREATE TABLE orders (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    user_id INT NOT NULL,
    total NUMERIC(10,2)
) PARTITION BY RANGE (created_at);

-- Partition mensuelle
CREATE TABLE orders_2026_04 PARTITION OF orders
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE orders_2026_05 PARTITION OF orders
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

Automatisation via pg_partman.

sudo apt install postgresql-17-partman

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table => 'public.orders',
    p_control => 'created_at',
    p_type => 'native',
    p_interval => 'monthly'
);

-- Maintenance auto via cron
SELECT partman.run_maintenance();

Bénéfices.
– Queries avec filtre date : scan d’une seule partition (partition pruning).
– Maintenance : DROP partition ancienne plutôt que DELETE + VACUUM.
– Index plus petits par partition.

Limitations.
– Index global pas supporté nativement (chaque partition son index).
– Foreign keys vers table partitionnée limitées.
– Migration table existante = re-création.

7. Replication et haute disponibilité

Streaming replication (built-in PostgreSQL).
– Réplique continue WAL d’un primary vers replica(s).
– Replica en read-only par défaut.
– Failover manuel ou via outil (Patroni, pg_auto_failover, repmgr).

Configuration primary (extrait).

# postgresql.conf
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB

# pg_hba.conf
host replication replicator <REPLICA_IP>/32 scram-sha-256

Configuration replica.

sudo -u postgres pg_basebackup -h <PRIMARY_IP> -U replicator -D /var/lib/postgresql/17/main -P -R
sudo systemctl start postgresql

Logical replication (depuis PG 10).
– Réplique sélective (par table).
– Versions PostgreSQL différentes possibles.
– Utile pour migrations progressive ou CDC.

Patroni + etcd.
– Failover automatique.
– Standard 2026 pour HA PostgreSQL self-hosted.
– Setup complexe mais robuste.

Solutions managed avec HA intégrée.
– AWS RDS Multi-AZ.
– Aurora PostgreSQL.
– Aiven, Supabase, Neon.

Recommandation PME.
– Production critique : managed (RDS, Aurora, Aiven) avec HA Multi-AZ.
– Production sensible : Patroni self-hosted si compétences présentes.
– Production standard : single primary + replica simple + backup robuste suffit.

8. Backup et restoration

pg_dump (logique, table par table).

# Dump complet
pg_dump -h localhost -U monapp_user -F c -b -f /backup/monapp_2026-04-26.dump monapp

# Restaurer
pg_restore -h localhost -U monapp_user -d monapp /backup/monapp_2026-04-26.dump

pg_basebackup (physique, full).

sudo -u postgres pg_basebackup -h localhost -U replicator -D /backup/base -F t -z -P

Continuous archiving (PITR — Point-In-Time Recovery).

# postgresql.conf
archive_mode = on
archive_command = 'rclone copy %p s3:bucket/wal/%f'

Permet restauration à n’importe quel moment dans le passé.

Outils production.
– pgBackRest : standard 2026, parallélisable, compression, encryption, S3-compatible.
– Barman : alternative pour multi-instances.
– WAL-G : continuous archiving cloud-native.

Stratégie 3-2-1.
– 3 copies : prod + replica + backup distant.
– 2 supports : disque + cloud (S3, R2, B2).
– 1 hors-site : autre région.

Test de restauration.
– Trimestriel obligatoire. Sans test = pas de backup.
– Restaurer sur staging, vérifier intégrité, performance.

Coût backup.
– pgBackRest sur S3/R2 : ~0,015 USD/GB/mois R2 (Cloudflare), ~0,023 USD/GB AWS S3.
– Pour DB 100 GB : ~1,5-2,3 USD/mois backup.

9. Monitoring et observabilité

Métriques essentielles.
– Connexions actives / max.
– Requêtes par seconde, latence p50/p95/p99.
– Cache hit ratio (>95 % bon).
– Slow queries (> 500 ms).
– Disk usage, WAL accumulation.
– Replication lag (si applicable).
– Locks et deadlocks.

Outils.
– pg_stat_statements (built-in) : top queries.
– pgwatch2 : dashboards complets self-hosted.
– Prometheus + postgres_exporter + Grafana : standard observabilité.
– Datadog APM : si budget.
– pganalyze : analyse SaaS dédiée PostgreSQL.

Requêtes utiles diagnostic.

-- Connexions actives
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

-- Requêtes lentes en cours
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC LIMIT 10;

-- Cache hit ratio
SELECT
  sum(blks_hit) * 100.0 / NULLIF(sum(blks_hit + blks_read), 0) AS cache_hit_ratio
FROM pg_stat_database;

-- Tailles tables
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;

10. Sécurité production

Connexions.
– TLS obligatoire (ssl = on dans postgresql.conf).
– pg_hba.conf : authentification scram-sha-256 (pas md5 obsolète).
– Pas de connexion super-user depuis applicatif.

Comptes applicatifs.
– Un user par application/service.
– Permissions minimales (SELECT/INSERT/UPDATE explicites par table).
– Mot de passe fort, rotation périodique.
– Audit log activé (extension pgaudit).

Network.
– DB dans VPC privé, jamais exposée internet.
– Bastion / VPN pour accès admin.
– Firewall : seulement IP applicative autorisée.

Données sensibles.
– Chiffrement at-rest (LUKS, RDS encryption, etc.).
– Colonnes critiques : pgcrypto pour chiffrement application-level.
– Pas de PII en logs.

Updates et patches.
– Mises à jour mineures mensuelles minimum.
– Mises à jour majeures planifiées avec test staging.
– Veille CVEs PostgreSQL (subscribe pgsql-announce).

Audit conformité.
– pgaudit pour traces opérations sensibles.
– Export logs vers SIEM si compliance requise.
– Documentation accès et procédures.

11. Pièges fréquents

Connexions non poolées. Chaque requête ouvre une connexion = écroulement à 1000 req/s. pgbouncer obligatoire en production. Mode transaction pour 95 % des cas.

VACUUM oublié. Tables grossissent, performance se dégrade. Autovacuum doit être bien configuré. Surveiller n_dead_tup.

Pas d’index sur foreign key. Slow queries garanties au moindre JOIN. Toujours indexer FKs.

Index sur colonne avec UPDATE fréquent. Coût écriture multiplié. Auditer trade-off.

Long-running transactions. Bloque autovacuum, accumule WAL. Toujours timeout transactions applicatives.

SELECT *. Récupère colonnes inutiles, utilise plus de mémoire et bande passante. Listing explicite.

ENUM rigides. Difficile à étendre. Préférer table de référence + foreign key.

Schema sans contraintes. NOT NULL, FK, CHECK : indispensables pour intégrité long terme.

Pas de monitoring. « Tout va bien » = aveugle. pg_stat_statements + Grafana minimum.

Backup non testé. « On a des backups » sans test = espoir. Test trimestriel obligatoire.

Mises à jour majeures retardées. PG 12 → 17 = risqué. Mises à jour majeures tous les 1-2 ans, sinon migration douloureuse.

Stockage sur HDD rotatif. SSD obligatoire en 2026. random_page_cost = 1.1 sur SSD vs 4.0 sur HDD.

FAQ

Combien de RAM pour une PostgreSQL production ?

Règle : 25 % shared_buffers, 75 % effective_cache_size. Pour PME : 8 GB RAM minimum (4 GB possible pour MVP), 16-32 GB pour production sérieuse, 64+ GB pour gros volumes. Surveiller cache hit ratio (>95 %) — si bas, ajouter RAM.

Faut-il pgbouncer obligatoirement ?

Quasiment toujours en production. Sans connection pooling, chaque connexion app = process PostgreSQL = ~10 MB. 100 utilisateurs simultanés = 1 GB juste pour connexions. pgbouncer multiplexe 1000 clients sur 25 connexions backend.

Comment passer de PostgreSQL 14 à PostgreSQL 17 ?

pg_upgrade pour upgrade en place (downtime court ~minutes). Logical replication pour migration progressive sans downtime (plus complexe). Toujours tester sur staging avec dataset prod avant.

Quelle taille max raisonnable pour une base PostgreSQL ?

Single instance : confortable jusqu’à 1-2 TB. Au-delà : partitioning recommandé. Au-delà 10 TB : envisager sharding (Citus, Postgres XL) ou changer architecture (data warehouse séparé).

PostgreSQL JSONB peut-il vraiment remplacer MongoDB ?

Pour la majorité des cas : oui. PostgreSQL JSONB + GIN index couvre 80-90 % des usages MongoDB avec en plus les transactions ACID et les jointures relationnelles. MongoDB reste meilleur pour scaling horizontal natif et schémas très évolutifs.

Comment estimer la taille d’index ?

SELECT pg_size_pretty(pg_relation_size(‘idx_name’)). Index B-tree typique : 5-15 % de la taille de la table. Index GIN sur JSONB : peut atteindre 30-50 %. Auditer avant de créer.

REINDEX bloque-t-il l’écriture ?

REINDEX TABLE bloque. REINDEX CONCURRENTLY (PG 12+) ne bloque pas mais prend plus de temps. À utiliser en production.

Comment gérer les migrations de schéma sans downtime ?

Migrations en deux étapes : (1) ajouter colonne nullable + backfill async, (2) ajouter contrainte NOT NULL une fois backfill fini. Outils : sqitch, flyway, alembic. Toujours tester sur staging.

Articles liés (cluster Bases de données)

Voir aussi : Linux administration avancée PME, Performance Linux troubleshooting, Django backend Python guide, Laravel backend PHP guide.

Article mis à jour le 26 avril 2026. Pour signaler une erreur ou suggérer une amélioration, écrivez-nous.