Compétence PostgreSQL — Administration, SQL Avancé et Exploitation
Vue d'ensemble
PostgreSQL (Postgres) est le SGBD relationnel open-source le plus avancé. Il supporte les transactions ACID, les index avancés (B-tree, Hash, GiST, GIN, SP-GiST, BRIN), le partitionnement natif, la replication logique, les extensions (PostGIS, TimescaleDB, pg_partman), et une conformité SQL exceptionnelle.
Cette compétence couvre l'installation, la configuration, le SQL avancé, la programmation PL/pgSQL, l'administration système, la réplication, le partitionnement, et l'optimisation des performances.
Quand l'utiliser
Activez cette compétence lorsque l'utilisateur :
Demande d'installer ou de configurer PostgreSQL (9.6 à 17+)
A besoin d'écrire des requêtes SQL avancées (CTE, fenêtrage, récursives)
Souhaite créer des fonctions, triggers, ou procédures stockées en PL/pgSQL
Veut configurer la réplication logique ou streaming
Demande du partitionnement de tables (range, list, hash)
A besoin de diagnostiquer des lenteurs (EXPLAIN ANALYZE, pg_stat_statements)
Veut sécuriser une instance (SSL, pg_hba.conf, rôle ACL)
# Fichier: /etc/postgresql/16/main/postgresql.conf
# Mémoire
shared_buffers = '4GB' # 25% de la RAM
effective_cache_size = '12GB' # 75% de la RAM
work_mem = '64MB' # par opération de tri
maintenance_work_mem = '1GB' # pour VACUUM, CREATE INDEX
# Écriture
wal_level = 'replica' # nécessaire pour la réplication
max_wal_size = '4GB'
min_wal_size = '1GB'
wal_buffers = '16MB'
synchronous_commit = 'on' # 'off' pour +10% perf en échange de risque
# Connexions
max_connections = 200
listen_addresses = 'localhost' # liste d'IP autorisées
# Planificateur
random_page_cost = 1.1 # 1.1 pour SSD, 4.0 pour HDD
effective_io_concurrency = 200 # SSD haute performance
default_statistics_target = 500 # meilleures stats = meilleurs plans
# Parallélisme
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
parallel_tuple_cost = 0.01
parallel_setup_cost = 100
# Autovacuum (critique)
autovacuum = on
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.005
autovacuum_vacuum_threshold = 50
autovacuum_naptime = '1min'
1.3 pg_hba.conf — Sécurité des connexions
# Fichier: /etc/postgresql/16/main/pg_hba.conf
# Authentification locale (socket Unix)
local all all peer
# IPv4 local
host all all 127.0.0.1/32 scram-sha-256
# Réplication
local replication replicator peer
host replication replicator 10.0.0.0/8 scram-sha-256
# Application distante avec certificat
hostssl all app_user 10.0.0.0/8 cert
2. SQL Avancé
2.1 CTE Récursives (WITH RECURSIVE)
-- Hiérarchie d'employés (table: employees(id, name, manager_id))WITHRECURSIVE org_tree AS (
-- Racine : le PDGSELECT id, name, manager_id, 1AS niveau, name::text AS chemin
FROM employees
WHERE manager_id ISNULLUNIONALL-- Récurrence : les N-1, N-2, etc.SELECT e.id, e.name, e.manager_id,
ot.niveau +1,
ot.chemin ||' -> '|| e.name
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT*FROM org_tree ORDERBY chemin;
2.2 Fenêtrage (Window Functions)
-- Rang, moyenne mobile, lead/lagSELECTdate, valeur,
ROW_NUMBER() OVER (PARTITIONBY capteur_id ORDERBYdate) AS rang,
AVG(valeur) OVER (
PARTITIONBY capteur_id
ORDERBYdateROWSBETWEEN7 PRECEDING ANDCURRENTROW
) AS moyenne_mobile_7j,
LAG(valeur, 1) OVER (PARTITIONBY capteur_id ORDERBYdate) AS valeur_precedente,
LEAD(valeur, 1) OVER (PARTITIONBY capteur_id ORDERBYdate) AS valeur_suivante,
valeur -LAG(valeur, 1) OVER (PARTITIONBY capteur_id ORDERBYdate) AS delta
FROM mesures_capteurs;
2.3 Aggrégation avec FILTER
SELECT
machine_id,
COUNT(*) AS total_cycles,
COUNT(*) FILTER (WHERE statut ='OK') AS cycles_ok,
COUNT(*) FILTER (WHERE statut ='ALARME') AS cycles_alarme,
ROUND(100.0*COUNT(*) FILTER (WHERE statut ='OK') /COUNT(*), 2) AS taux_conformite
FROM production_cycles
WHEREdate>=CURRENT_DATE-INTERVAL'30 days'GROUPBY machine_id;
2.4 Génération de séries et Gap Filling
-- Remplir les trous temporels avec des zérosWITH heures AS (
SELECT generate_series(
date_trunc('hour', NOW() -INTERVAL'24 hours'),
date_trunc('hour', NOW()),
'1 hour'::interval
) AS heure
)
SELECT
h.heure,
COALESCE(SUM(p.quantite), 0) AS production
FROM heures h
LEFTJOIN production p
ON date_trunc('hour', p.timestamp) = h.heure
GROUPBY h.heure
ORDERBY h.heure;
3. PL/pgSQL — Fonctions, Procédures et Triggers
3.1 Fonction avec paramètres
CREATEOR REPLACE FUNCTION calculer_statistiques(
p_machine_id INT,
p_periode_depuis TIMESTAMPTZ DEFAULT NOW() -INTERVAL'7 days'
)
RETURNSTABLE(
nb_cycles BIGINT,
temps_moyen_cycle NUMERIC,
taux_erreur NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGINRETURN QUERY
SELECTCOUNT(*)::BIGINT,
AVG(duree_cycle_ms)::NUMERIC,
ROUND(100.0*SUM(CASEWHEN statut !='OK'THEN1ELSE0END) /COUNT(*), 2)
FROM cycles
WHERE machine_id = p_machine_id
ANDtimestamp>= p_periode_depuis;
END;
$$;
3.2 Trigger d'audit et historisation
-- Table d'auditCREATE TABLE audit_changements (
id BIGSERIAL PRIMARY KEY,
table_name TEXT NOT NULL,
record_id INTNOT NULL,
old_data JSONB,
new_data JSONB,
changed_by TEXT,
changed_at TIMESTAMPTZ DEFAULT NOW()
);
-- Fonction trigger génériqueCREATEOR REPLACE FUNCTION audit_trigger_fn()
RETURNSTRIGGERLANGUAGE plpgsql
AS $$
BEGIN
IF TG_OP ='UPDATE'THENINSERT INTO audit_changements(table_name, record_id, old_data, new_data, changed_by)
VALUES (TG_TABLE_NAME, OLD.id, row_to_json(OLD)::JSONB, row_to_json(NEW)::JSONB, current_user);
RETURNNEW;
ELSIF TG_OP ='DELETE'THENINSERT INTO audit_changements(table_name, record_id, old_data, changed_by)
VALUES (TG_TABLE_NAME, OLD.id, row_to_json(OLD)::JSONB, current_user);
RETURNOLD;
END IF;
RETURNNULL;
END;
$$;
-- Attacher le trigger à une tableCREATETRIGGER audit_machines
AFTER UPDATEORDELETEON machines
FOREACHROWEXECUTEFUNCTION audit_trigger_fn();
3.3 Procédure avec transaction et rollback partiel (savepoint)
CREATEOR REPLACE PROCEDURE transferer_stock(
p_origine INT,
p_destination INT,
p_quantite INT
)
LANGUAGE plpgsql
AS $$
DECLARE
disponible INT;
BEGIN-- Vérification et lockSELECT quantite INTO disponible
FROM stocks WHERE produit_id = p_origine
FORUPDATE; -- verrouillage pessimiste
IF disponible < p_quantite THEN
RAISE EXCEPTION 'Stock insuffisant : % disponible, % requis', disponible, p_quantite;
END IF;
-- DébitUPDATE stocks SET quantite = quantite - p_quantite
WHERE produit_id = p_origine;
-- CréditUPDATE stocks SET quantite = quantite + p_quantite
WHERE produit_id = p_destination;
COMMIT;
END;
$$;
4. Partitionnement Natif
4.1 Partition Range (par mois)
-- Table partitionnéeCREATE TABLE mesures (
timestamp TIMESTAMPTZ NOT NULL,
machine_id INTNOT NULL,
temperature NUMERIC,
pression NUMERIC
) PARTITIONBYRANGE (timestamp);
-- Partitions mensuellesCREATE TABLE mesures_2025_01 PARTITIONOF mesures
FORVALUESFROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE mesures_2025_02 PARTITIONOF mesures
FORVALUESFROM ('2025-02-01') TO ('2025-03-01');
-- Index local (indépendant par partition)CREATE INDEX idx_mesures_machine_2025_01 ON mesures_2025_01 (machine_id);
CREATE INDEX idx_mesures_machine_2025_02 ON mesures_2025_02 (machine_id);
-- Fonction de création automatique de partitions mensuellesCREATEOR REPLACE FUNCTION creer_partition_si_necessaire()
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
debut DATE;
fin DATE;
nom_partition TEXT;
BEGINFOR i IN0..2 LOOP -- crée les 3 prochains mois
debut := date_trunc('month', CURRENT_DATE+ (i ||' months')::INTERVAL)::DATE;
fin := (debut +INTERVAL'1 month')::DATE;
nom_partition :='mesures_'|| TO_CHAR(debut, 'YYYY_MM');
IF NOTEXISTS (
SELECT1FROM pg_class WHERE relname = nom_partition
) THENEXECUTE format(
'CREATE TABLE %I PARTITION OF mesures FOR VALUES FROM (%L) TO (%L)',
nom_partition, debut, fin
);
END IF;
END LOOP;
END;
$$;
-- Activer dans postgresql.conf-- shared_preload_libraries = 'pg_stat_statements'-- puis redémarrerCREATE EXTENSION IF NOTEXISTS pg_stat_statements;
-- Top 10 des requêtes les plus lentesSELECT
queryid,
ROUND(total_exec_time::NUMERIC/1000, 2) AS total_seconds,
ROUND(mean_exec_time::NUMERIC, 2) AS mean_ms,
calls,
ROUND(shared_blks_hit::NUMERIC/ GREATEST(shared_blks_hit + shared_blks_read, 1) *100, 1) AS cache_hit_ratio,
query
FROM pg_stat_statements
WHERE query NOTLIKE'%pg_stat%'ORDERBY total_exec_time DESC
LIMIT 10;
5.2 PostGIS — Géospatial
CREATE EXTENSION IF NOTEXISTS postgis;
-- Table avec colonne géographiqueCREATE TABLE sites_industriels (
id SERIAL PRIMARY KEY,
nom TEXT,
geom GEOMETRY(Point, 4326)
);
-- Index spatialCREATE INDEX idx_sites_geom ON sites_industriels USING GIST (geom);
-- Requête de distanceSELECT nom, ST_DistanceSphere(geom, ST_SetSRID(ST_MakePoint(4.387, 45.441), 4326)) AS distance_m
FROM sites_industriels
ORDERBY geom <-> ST_SetSRID(ST_MakePoint(4.387, 45.441), 4326)
LIMIT 10;
5.3 pg_partman — Gestion automatisée du partitionnement
CREATE EXTENSION IF NOTEXISTS timescaledb;
-- Créer une hypertableSELECT create_hypertable('mesures', 'timestamp', chunk_time_interval =>INTERVAL'1 day');
-- Compression automatique (après 7 jours)ALTER TABLE mesures SET (
timescaledb.compress,
timescaledb.compress_segmentby ='machine_id'
);
SELECT add_compression_policy('mesures', INTERVAL'7 days');
-- Continuous aggregates (vues matérialisées automatiques)CREATE MATERIALIZED VIEW mesures_hourly
WITH (timescaledb.continuous) ASSELECT
time_bucket('1 hour', timestamp) AS heure,
machine_id,
AVG(temperature) AS temp_moyenne,
MAX(temperature) AS temp_max
FROM mesures
GROUPBY heure, machine_id;
6. Diagnostic des Performances
6.1 EXPLAIN ANALYZE
-- Plan d'exécution avec coûts réels
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT m.*, c.nom
FROM mesures m
JOIN capteurs c ON m.capteur_id = c.id
WHERE m.timestamp >='2025-06-01'AND m.temperature >100ORDERBY m.timestamp DESC;
6.2 Requêtes en cours et verrous
-- Sessions actives et leur requêteSELECT
pid,
now() - pg_stat_activity.query_start AS duree,
state,
wait_event_type ||': '|| wait_event AS attente,
query
FROM pg_stat_activity
WHERE state !='idle'AND pid != pg_backend_pid()
ORDERBY duree DESC;
-- Verrous bloquantsSELECT
blocked.pid AS pid_bloque,
blocked.query AS requete_bloquee,
blocking.pid AS pid_bloqueur,
blocking.query AS requete_bloquante
FROM pg_locks blocked
JOIN pg_stat_activity blocked_act ON blocked.pid = blocked_act.pid
JOIN pg_locks blocking ON blocked.locktype = blocking.locktype
AND blocked.database = blocking.database
AND blocked.relation = blocking.relation
AND blocked.pid != blocking.pid
JOIN pg_stat_activity blocking_act ON blocking.pid = blocking_act.pid
WHERENOT blocked.granted;
6.3 Taille des tables et index
SELECT
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_table_size(relid)) AS table_size,
pg_size_pretty(pg_indexes_size(relid)) AS index_size,
n_live_tup AS lignes_estimees,
n_dead_tup AS lignes_mortes,
ROUND(100.0* n_dead_tup / GREATEST(n_live_tup + n_dead_tup, 1), 2) AS dead_ratio
FROM pg_stat_user_tables
ORDERBY pg_total_relation_size(relid) DESC;
-- Subscription avec connexion au PublisherCREATE SUBSCRIPTION sub_production
CONNECTION 'host=192.168.1.10 port=5432 dbname=prod user=replicator password=***'
PUBLICATION pub_production
WITH (copy_data =true);
Pièges Courants
Autovacuum insuffisant. Des ratios élevés de n_dead_tup (dead rows) ralentissent les index et gonflent la taille. Solution : ajuster autovacuum_vacuum_scale_factor à 0.01 sur les tables fréquemment modifiées.
work_mem trop bas pour des tris volumineux. PostgreSQL écrit sur disque si work_mem est dépassé. Utiliser EXPLAIN ANALYZE pour repérer les External Sort.
Pas d'index adaptés aux filtres de requêtes. Une clause WHERE sur une colonne non indexée provoque un Seq Scan sur toute la table. Utiliser pg_stat_user_indexes pour détecter les index inutilisés.
Forcer des requêtes DISTINCT inutilesment.EXISTS est presque toujours plus rapide que DISTINCT ou IN sur de grands volumes.
Index B-tree sur des colonnes de faible cardinalité (booléens, statuts). Préférer un partial index : CREATE INDEX idx_alertes_critiques ON mesures(valeur) WHERE niveau = 'CRITIQUE'.
Oublier VACUUM FREEZE sur les tables partitionnées anciennes. Sur des partitions en lecture seule, ajouter un VACUUM FREEZE manuel pour éviter un wrap-around de XID.
