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

Alain Lesage

Devfest Nantes 2025

Question ?

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

PostgreSQL 🐘

LA référence !

2025 Stack Overflow Developer Survey

ℹ️ Message de service

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

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

Matériel

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

Indexation

foo=# \d demo
                   Table « public.demo »
 Colonne |   Type    | Collationnement | NULL-able | Par défaut
---------+-----------+-----------------+-----------+------------
 k       | integer   |                 | not null  |
 tags    | integer[] |                 | not null  |
Index :
    "demo_idx_k_btree" btree (k)
    "demo_idx_tags_gin" gin (tags)

Indexation

sans index

INSERT INTO demo
  SELECT g, 
  ARRAY[(random()*1000),(random()*1000),(random()*1000)] 
  FROM generate_series(1, 500000) AS g;
INSERT 0 500000
Durée : 1988,356 ms (00:01,988)

avec index

INSERT INTO demo
  SELECT g,
  ARRAY[(random()*1000),(random()*1000),(random()*1000)] 
  FROM generate_series(1, 500000) AS g;
INSERT 0 500000
Durée : 5108,528 ms (00:05,109) -- + 257 % !!

Indexation

  • Coûteuse…
    • en stockage
    • en écriture
  • …mais indispensable !
    • en lecture
  • questions
    • quoi indexer
    • comment indexer

Indexation 

CREATE TABLE maTable (
  c1 NUMERIC,
  c2 TEXT
);

CREATE INDEX i0 ON maTable(c1);                     -- monocolonne

Indexation 

CREATE TABLE maTable (
  c1 NUMERIC,
  c2 TEXT
);

CREATE INDEX i1 ON maTable(c1,c2);                  -- multicolonne

Indexation 

CREATE TABLE maTable (
  c1 NUMERIC,
  c2 TEXT
);

CREATE INDEX i2 ON maTable(c1) WHERE c2= "pending"; -- partiel

Indexation 

CREATE TABLE maTable (
  c1 NUMERIC,
  c2 TEXT
);

CREATE INDEX i3 ON maTable(c1) INCLUDE c2;          -- couvrant

Indexation 

CREATE TABLE maTable (
  c1 NUMERIC,
  c2 TEXT
);

CREATE INDEX i4 ON maTable(c1, upper(c2));          -- fonctionnel

Index B-Tree

  • Indexation des valeurs
  • Arbre balancé, 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
  • Stockage colonne avec citus_columnar
  • pgvector, TimescaleDB, CitusData…

Vis ma vie

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

Une histoire de requête lente

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
Limit  (cost=1570746.37..1570746.38 rows=1 width=671) 
       (actual time=240016.770..240016.781 rows=1 loops=1)
   ->  Sort  (cost=1570746.37..1570746.39 rows=10 width=671) 
             (actual time=240016.767..240016.776 rows=1 loops=1)
         Sort Key: table_a.created_at DESC
         Sort Method: top-N heapsort  Memory: 25kB
         InitPlan 1 (returns $0)
           ->  Index Scan using unique_key_constraint on table_b 
           (cost=0.27..8.29 rows=1 width=4) 
           (actual time=0.567..0.575 rows=1 loops=1)
                 Index Cond: ((unique_key)::text = '_UNiquEv41ue_'::text)
         ->  Bitmap Heap Scan on table_a  (cost=33130.70..1570737.91 rows=10 width=671) 
                ...
Planning time: 18.432 ms
Execution time: 240021.889 ms

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

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