Vos requêtes SQL 10 000x plus rapide, durablement

Alain Lesage

Devoxx France 2025

Qui suis-je ? D’où viens-je ?

  • Industries centenaires
    • Développement web
    • Intégration DevOps
    • BI & Data Science
    • DBA Oracle
  • DALIBO
    • DBA PostgreSQL

PostgreSQL

30 ans déjà !

PostgreSQL 🐘

LA référence !

2024 Stack Overflow Developer Survey

ℹ️ Message de service

  • Pas de paramètre magique
  • Pas de “quick win”

Le métier de DBA ?

Que fait un DBA ?

  • administration
    • installation d’instances
    • construction de replicas
    • maintenance
    • sauvegardes
    • droits utilisateurs
  • optimisation
    • analyse des plans d’exécution
    • création d’index

Que fait VRAIMENT un DBA ?

  • conçoit
    • modèle de données
    • architecture (réplication, HA)
  • conseille
    • utilisation (OLTP, OLAP, monolithe, µ-services)
    • niveau de service (RTO, RPO, SLO…)
    • ingénierie (choix des outils, intégration)
  • décide
    • pour 5 à 10 ans au moins

Bases de Données Relationnelles

Bases de Données

  • Atomicité
    • transactions (BEGIN, COMMIT, ROLLBACK)
  • Cohérence
    • contraintes (UNIQUE, NOT NULL, CHECK…)
  • Isolation
    • Multiversion Concurrency Control (MVCC)
  • Durabilité
    • journalisation et double écriture

Modèle Relationnel

  • Depuis 1970
  • Clés primaires & clés étrangères
  • Indexation
  • Langage SQL

MVCC Exemple

INSERT INTO ma_table VALUES
  ('Bob',     1000),
  ('Alice',   2000),
  ('Charlie',  800);
xmin xmax nom montant
1 Bob 1000
1 Alice 2000
1 Charlie 800

MVCC Exemple

UPDATE ma_table SET montant = 1500 WHERE nom = 'Bob';
xmin xmax nom montant
1 2 Bob 1000
1 Alice 2000
1 Charlie 800
2 Bob 1500

MVCC Exemple

DELETE FROM ma_table WHERE nom = 'Charlie';
xmin xmax nom montant
1 2 Bob 1000
1 Alice 2000
1 3 Charlie 800
2 Bob 1500 

BEGIN;
UPDATE ma_table SET montant = montant - 250 WHERE nom = 'Alice';
UPDATE ma_table SET montant = montant + 250 WHERE nom = 'Bob';
COMMIT;
xmin xmax nom montant
1 2 Bob 1000
1 4 Alice 2000
1 3 Charlie 800
2 4 Bob 1500
4 Alice 1750
4 Bob 1750

Maintenance

  • processus autovacuum
    • marque les lignes mortes
    • permet leur recyclage
    • pratique…
    • …mais pas magique !
  • Commande VACUUM

Le gruyère de la performance

Matériel

  • CPU
    • performances mono-thread (IPC)
    • performances parallélisation
  • Mémoire
    • quantité
    • bande passante
  • Disque
    • débit
    • latence

Indexation

  • Coûteuse…
    • en stockage
    • en écriture
  • …mais indispensable !
    • en lecture

Indexation 

CREATE INDEX i0 ON maTable(c1);                     -- monocolonne
CREATE INDEX i1 ON maTable(c1,c2);                  -- multicolonne
CREATE INDEX i2 ON maTable(c2) WHERE c1= "pending"; -- partiel
CREATE INDEX i3 ON maTable(c1) INCLUDE c2;          -- couvrant
CREATE INDEX i4 ON maTable(c1, upper(c2));          -- fonctionnel

Index B-Tree

  • Indexation des valeurs
  • Arbre binaire, classique, efficace ✅
  • Volumétrie et fragmentation ⚠️
  • Probablement 100 % de vos index aujourd’hui 💯

Index BRIN

CREATE INDEX idx_log_ts_btree ON log(ts);
CREATE INDEX idx_log_ts_brin ON log USING brin (ts);

postgres=# \di+ 
       Nom        | Type  | Table | Méthode d'accès | Taille   
------------------+-------+-------+-----------------+----------
 idx_log_ts_brin  | index | log   | brin            | 24 kB  
 idx_log_ts_btree | index | log   | btree           | 21 MB
  • Mon index préféré 👆
  • Indexation de plages de données
  • Idéal pour les grosses volumétries ordonnées

Index Génériques (GIN/GiST) 

CREATE INDEX ON stock_jsonb USING gin (document_jsonb);
CREATE INDEX ON mes_points USING gist (p);
  • GIN efficace mais coûteux à maintenir
  • GiST plus léger, peut être suffisant
  • Données non structurées (JSON, TEXT…)
  • Full text search avec pg_trgm
  • Données spatiales avec PostgGIS

Statistiques

  • supervision
  • planification
  • processus autovacuum
  • ANALYZE

Statistiques de supervision

  • 43 vues à votre disposition (v17)
    • pg_stat_activity,
    • pg_stat_user_tables,
    • pg_stat_io,
    • …
  • Extension pg_stat_statements
  • Sonde check_pgactivity
  • Console de supervision PoWA
  • Console d’administration temBoard

PoWA

Statistiques de planification 

postgres=# \d pg_stats
 ...
 schemaname          | name     
 tablename           | name     
 attname             | name     -- nom de la colonne
 null_frac           | real     -- % de valeurs nulles
 avg_width           | integer  -- taille moyenne des valeurs en octets
 n_distinct          | real     -- nombre de valeurs distinctes
 most_common_vals    | anyarray -- valeurs les plus fréquentes
 most_common_freqs   | real[]   -- fréquences des valeurs fréquentes
 histogram_bounds    | anyarray -- histogramme
 correlation         | real     -- ordre physique des données => BRIN
 ...

Statistiques multivariées

CREATE STATISTICS stat_dpt ON code_postal, departement FROM adresse ;

Big Data ? NoSQL ?

  • PostgreSQL tient le coup sur des bases de dizaines de To
    • pour peu que le matériel suive…
  • Partitionnement parfois utile
    • pour faciliter la maintenance
    • pour faciliter l’administration
  • pgvector, TimescaleDB, CitusData…

Vis ma vie

  • Une histoire de requête lente
  • Une histoire de micro-services

Une histoire de requête lente

EXPLAIN ANALYZE SELECT column_a, created_at
FROM table_a
WHERE 
 external_id = (SELECT id FROM table_b WHERE unique_key = '_UNiquEv4L_')
 AND LOWER(column_b) = 'a2bc-34de-45f0'
 AND some_id = -1234
 AND other_id = (SELECT id FROM table_c WHERE some_id = table_a.some_id)
ORDER BY created_at DESC
LIMIT 1

Une histoire de requête lente

CREATE INDEX idx_fonctionnel_sur_mesure 
ON table_a(external_id, LOWER(column_b), some_id);

Une histoire de requête lente

ANALYZE table_a;

Une histoire de requête lente

  • Durée originale : 5 min 🐌
  • Durée index fonctionnel : 1 min 🐢
  • Durée après ANALYZE : 30 ms 🐎
  • Visualisation sur explain.dalibo.com

Une histoire de micro-services

  • Principe
    • Isolation des fonctionnalités
    • Conteneurisation & Orchestration
    • multitenant PostgreSQL

Une histoire de micro-services

Une histoire de micro-services

  • Problème
    • Cohérence des données entre services
    • Jointures entre services (Foreign Data Wrapper)

Une histoire de micro-services

Une histoire de micro-services

  • Solution
    • un schéma par service
    • séparation logique conservée
    • garantie ACID globale
    • jointures possibles nativement
    • toujours sur une seule instance

Une histoire bonus …

les ORM ❤️

Une histoire bonus : les ORM

  • Problèmes
    • toutes les colonnes systématiquement sélectionnées
    • requêtes avec des dizaines de jointures

Une histoire bonus : les ORM

  • Solution
  ALTER SYSTEM SET `join_collapse_limit` = 12 ; -- 8 par
  ALTER SYSTEM SET `from_collapse_limit` = 12 ; -- défaut

À retenir

  • PostgreSQL est un outil efficace (et libre !)
    • + de 50 ans de recherche
    • + de 30 ans de développement
  • Votre PGaaS ne connaît
    • ni votre métier
    • ni votre application
  • Une décision = 10 ans

Liens utiles

DALIBO : 20 ans de contribution ! 🎂🎉

dali.bo/labs

  • et PostgreSQL 18 !

Merci !

des questions ?

LinkedIN : @lesagealain