PostgreSQL

Qu’est-ce que PostgreSQL ?

PostgreSQL est une base de données relationnelle open-source réputée pour traiter les aspects fondamentaux avec une rigueur exemplaire. Elle stocke les données dans des tables, impose des règles via des contraintes, encapsule les modifications dans de véritables transactions et expose l’ensemble via le standard SQL. Développée en continu depuis les années 1980, elle est gouvernée par une large communauté plutôt que par une seule entreprise.

Cette longévité se reflète dans les détails. Postgres possède le système de types le plus riche de toutes les bases de données grand public, un mécanisme d’extension permettant à des tiers d’ajouter des fonctionnalités entièrement nouvelles, et un planificateur de requêtes capable de tout gérer, d’une simple recherche ponctuelle à une requête analytique avec fonctions de fenêtrage. C’est la base de données relationnelle par défaut pour les nouvelles applications dans la plupart des entreprises, et elle est disponible en tant que service managé presque partout.

Si vous devez choisir une seule base de données à apprendre en profondeur, c’est celle-ci qui vous offrira le meilleur retour sur investissement.

Pourquoi les équipes choisissent Postgres

Trois caractéristiques expliquent l’essentiel de sa popularité.

Il est fiable par défaut. Les contraintes sont appliquées au niveau du moteur, et non dans le code de l’application. Un CHECK ne peut pas être contourné par un service buggé, un FOREIGN KEY ne peut pas être ignoré par un traitement batch, et un index UNIQUE ne peut pas être victime d’une condition de concurrence entre deux requêtes simultanées. L’intégrité des données devient une propriété du schéma.

Il est extensible. Plutôt que d’intégrer chaque fonctionnalité dans le cœur du système, Postgres expose des hooks pour de nouveaux types, opérateurs, méthodes d’indexation et langages procéduraux. C’est ainsi que PostGIS, pgvector, TimescaleDB et des dizaines d’autres projets existent en tant qu’extensions plutôt qu’en tant que forks.

Il parle le SQL standard. Les compétences et les requêtes sont transférables entre les bases de données, les ORM et les outils. Vous n’avez pas à apprendre un dialecte propriétaire pour commencer.

Le modèle relationnel, en bref

Une base de données relationnelle stocke les données dans des tables composées de lignes et de colonnes. Chaque table possède une clé primaire qui identifie de manière unique une ligne, et les relations sont exprimées via des clés étrangères qui pointent vers d’autres tables. L’objectif est de stocker chaque information une seule fois et de laisser les jointures la réassembler.

Prenons l’exemple des utilisateurs et des commandes. Plutôt que de répéter l’e-mail d’un client sur chaque commande, vous stockez l’e-mail une seule fois dans users et référencez l’utilisateur depuis orders via user_id. C’est ce qu’on appelle la normalisation, et cela permet d’éviter l’anomalie classique où une ligne est mise à jour alors que mille autres conservent l’ancienne valeur.

La normalisation n’est pas une religion. La troisième forme normale est le choix par défaut le plus raisonnable, et une dénormalisation délibérée — un order_count en cache, une vue matérialisée — est une décision de performance que vous prendrez plus tard, en vous appuyant sur des mesures concrètes.

Les types de données indispensables

Postgres propose plus de types que la plupart des projets n’en demandent, mais quelques-uns sont essentiels au quotidien :

• text — chaîne de caractères de longueur variable sans limite arbitraire. Préférez-le à varchar(n), sauf si la limite correspond à une véritable règle métier.
• integer et bigint — nombres entiers. Utilisez bigint pour tout ce qui pourrait croître sans limite, comme les IDs issus d’une séquence.
• numeric(p, s) — arithmétique décimale exacte. C’est le type approprié pour l’argent, et non real ou double precision.
• timestamptz — un horodatage stocké en UTC avec gestion du fuseau horaire. Préférez-le toujours à timestamp pour les données applicatives.
• uuid — un identifiant 128 bits, utile lorsque les clients génèrent des IDs ou lorsque vous souhaitez éviter de divulguer le nombre d’entrées.
• jsonb — JSON binaire que vous pouvez indexer et requêter.
• Tableaux et types composites — text[], integer[] et types de lignes personnalisés, pratiques pour les tags et les petites listes ordonnées.

L’exemple de l’argent est important à retenir : 0.1 + 0.2 n’est pas 0.3 en virgule flottante binaire. Stockez les montants sous forme d’unités mineures entières (total_cents) ou en numeric, mais jamais en float.

Création de tables et de contraintes

Un schéma est un contrat. Chaque contrainte que vous déclarez représente une catégorie de bugs que vous ne pourrez pas déployer en production.

CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  name       text NOT NULL CHECK (length(name) > 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  user_id     bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  status      text NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  created_at  timestamptz NOT NULL DEFAULT now(),
  metadata    jsonb NOT NULL DEFAULT '{}'::jsonb
);

Les choix effectués ici sont délibérés. NOT NULL élimine toute une famille de bugs liés aux données manquantes. UNIQUE sur email impose l’invariant au niveau de la base de données, qui est le seul endroit où une condition de concurrence (race condition) ne peut pas le contourner. La contrainte CHECK sur status documente et impose la machine à états. ON DELETE CASCADE définit ce qui se passe lorsqu’un utilisateur disparaît, évitant ainsi de laisser des données orphelines.

Privilégiez l’ajout de contraintes dans la même migration que celle qui crée la table. Ajouter une NOT NULL ou une clé étrangère plus tard implique de devoir d’abord nettoyer toutes les lignes invalides qui se sont accumulées pendant que la règle était absente.

Effectuer des requêtes avec des jointures

La plupart des requêtes réelles combinent plusieurs tables. Une jointure associe des lignes de deux relations selon une condition, et le choix du type de jointure détermine le traitement des lignes sans correspondance.

SELECT u.email,
       o.id,
       o.total_cents
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 50;

JOIN (ou INNER JOIN) ne conserve que les paires correspondantes. LEFT JOIN conserve toutes les lignes de gauche et remplit la partie droite avec NULL lorsqu’il n’y a pas de correspondance — c’est la méthode standard pour demander “quels utilisateurs n’ont aucune commande” :

SELECT u.email, count(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email
HAVING count(o.id) = 0;

Les agrégats comme count, sum, avg, min et max regroupent des ensembles de lignes en une seule. Chaque colonne du SELECT qui ne se trouve pas dans un agrégat doit apparaître dans le GROUP BY. La clause WHERE filtre les lignes avant le regroupement ; HAVING filtre les groupes après. Confondre les deux est une source courante de résultats déroutants.

Index et EXPLAIN ANALYZE

Un index est une structure triée qui permet au planificateur de trouver des lignes sans avoir à scanner l’intégralité de la table. Par défaut, il s’agit d’un B-tree, qui gère les prédicats d’égalité et de plage, et supporte directement ORDER BY.

CREATE INDEX orders_user_created_idx
  ON orders (user_id, created_at DESC);

Un index composite comme celui-ci couvre les requêtes qui filtrent sur user_id et trient par created_at. L’ordre des colonnes est crucial : la colonne la plus à gauche doit apparaître dans la requête pour que l’index soit utile au filtrage. C’est la règle du “leftmost-prefix”, et c’est pourquoi la conception des index commence par l’analyse des requêtes, et non des colonnes.

D’autres types d’index couvrent des besoins différents :

• GIN — index inversés pour jsonb, les tableaux et la recherche plein texte (tsvector).
• GiST — géométrie, plages et recherche du plus proche voisin, très utilisé par PostGIS.
• BRIN — index minuscules pour des données naturellement ordonnées, comme des tables de timestamps en ajout seul (append-only).
• Hash — recherches basées uniquement sur l’égalité, rarement nécessaires puisque le B-tree les gère.

Ne devinez jamais si un index est utilisé. Demandez-le au planificateur :

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

Le résultat affiche l’arbre du plan, le coût estimé et — avec ANALYZE — le nombre réel de lignes et le temps d’exécution.

Limit  (cost=0.43..12.94 rows=10 width=48)
       (actual time=0.031..0.079 rows=10 loops=1)
  ->  Index Scan Backward using orders_user_created_idx on orders
        (cost=0.43..521.10 rows=417 width=48)
        (actual time=0.029..0.070 rows=10 loops=1)
        Index Cond: (user_id = 42)
Planning Time: 0.184 ms
Execution Time: 0.108 ms

Lisez-le de l’intérieur vers l’extérieur. Ici, le Index Scan avec Index Cond: (user_id = 42) confirme que l’index composite remplit son rôle. Recherchez un Seq Scan sur une table volumineuse là où vous attendiez un Index Scan, ainsi qu’un écart important entre les lignes estimées et réelles, ce qui signifie généralement que les statistiques sont obsolètes. Exécutez ANALYZE orders; pour les rafraîchir, et EXPLAIN (ANALYZE, BUFFERS) pour voir quelle quantité de données a été lue depuis le cache par rapport au disque.

Transactions et MVCC

Une transaction regroupe des instructions afin qu’elles soient toutes appliquées ou qu’aucune ne le soit. Postgres implémente les transactions via le MVCC : au lieu de verrouiller les lignes pour les lecteurs, il conserve plusieurs versions et fournit un instantané (snapshot) à chaque transaction.

BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 5000 WHERE id = 1;
UPDATE accounts SET balance_cents = balance_cents + 5000 WHERE id = 2;
COMMIT;

Si une erreur survient, ROLLBACK annule l’intégralité de la transaction. Le code de l’application doit encapsuler les écritures multi-étapes dans une transaction et être prêt à recommencer l’opération en cas d’échec de sérialisation lors de l’utilisation des niveaux d’isolation les plus stricts.

Les niveaux d’isolation déterminent ce qu’une transaction peut observer :

• Read Committed (par défaut) — chaque instruction voit un instantané récent ; adapté à la plupart des charges de travail.
• Repeatable Read — l’ensemble de la transaction voit un seul instantané ; utile lorsque vous lisez les mêmes lignes à plusieurs reprises.
• Serializable — les transactions se comportent comme si elles étaient exécutées les unes après les autres ; c’est la garantie la plus forte, et celle qui nécessite le plus souvent des tentatives de réexécution.

Le MVCC a un coût : les lignes mises à jour et supprimées laissent derrière elles des tuples morts. L’Autovacuum les récupère en arrière-plan. Les transactions trop longues retardent ce nettoyage et provoquent un gonflement (bloat) des tables ; veillez donc à garder vos transactions courtes et à surveiller le vacuum sur les tables à fort taux de modification.

Le pooling de connexions avec PgBouncer

Postgres gère chaque connexion client via un processus distinct du système d’exploitation. C’est une approche robuste, mais cela signifie que les connexions sont plus lourdes que dans MySQL, et quelques centaines de clients inactifs peuvent consommer une quantité mémoire significative. La limite pratique est de max_connections, et le fait de la dépasser produit des erreurs plutôt qu’une dégradation progressive des performances.

La solution consiste à utiliser un pooler de connexions. PgBouncer se place entre l’application et Postgres, maintient un petit pool de connexions réelles et multiplexe de nombreuses connexions clients sur celles-ci. Il propose trois modes :

• Session pooling — une connexion serveur est conservée pendant toute la session du client.
• Transaction pooling — une connexion serveur n’est conservée que pour la durée d’une transaction ; c’est le choix le plus courant pour les applications web.
• Statement pooling — une connexion est conservée pour une seule instruction ; c’est le mode le plus agressif et le plus restrictif.

Le transaction pooling rend inopérantes les fonctionnalités liées à la session, telles que SET, les advisory locks maintenus entre plusieurs instructions et LISTEN. Utilisez SET LOCAL à l’intérieur des transactions et vérifiez que votre ORM se comporte correctement en mode transaction.

Quel que soit votre choix, mettez également en place un pool côté application. Un pool par processus avec un maximum raisonnable, couplé à un pooler en amont, constitue l’architecture standard. N’ouvrez jamais une nouvelle connexion à chaque requête.

JSONB : quand l’utiliser

jsonb stocke le JSON sous une forme binaire décomposée qui permet l’indexation et propose un ensemble riche d’opérateurs. C’est un outil véritablement utile, mais il est également facile d’en abuser.

SELECT id, metadata->>'coupon' AS coupon
FROM orders
WHERE metadata @> '{"channel": "mobile"}';

L’opérateur de confinement @> peut utiliser un index GIN :

CREATE INDEX orders_metadata_idx ON orders USING gin (metadata);

Utilisez JSONB pour les données dont la structure est réellement variable : payloads de webhooks, réponses d’API tierces, attributs définis par l’utilisateur, feature flags. Ne l’utilisez pas simplement pour éviter d’écrire une migration. Les champs que vous filtrez, joignez, contraignez ou agrégez devraient être des colonnes avec des types réels. Le test est simple : si vous vous retrouvez à caster metadata->>'price' en nombre dans la plupart de vos requêtes, cela aurait dû être un integer.

Une voie intermédiaire consiste à adopter une approche hybride : les champs stables en colonnes, et tout le reste (optionnel) dans un sac attributes JSONB. Cela vous permet d’avoir des contraintes et des index là où c’est important, tout en gardant de la flexibilité là où ce n’est pas nécessaire.

Des extensions qui repoussent les limites

CREATE EXTENSION installe un module groupé dans une base de données. En voici quelques-unes qu’il est utile de connaître :

• pg_stat_statements — enregistre le texte normalisé des requêtes avec le timing et les E/S ; c’est la première chose à activer lors de l’analyse des performances.
• PostGIS — types et fonctions géographiques ; c’est la raison pour laquelle beaucoup d’équipes choisissent Postgres pour les données de localisation.
• pgvector — colonnes de vecteurs et index de plus proches voisins approximatifs pour les embeddings et la recherche sémantique.
• pgcrypto — fonctions cryptographiques telles que gen_random_uuid() sur les versions plus anciennes.
• pg_trgm — index de trigrammes pour des LIKE rapides et le fuzzy matching.

N’activez que ce dont vous avez besoin, et gardez à l’esprit que les fournisseurs managés nécessitent souvent un paramétrage ou une demande de support avant qu’une extension puisse être installée.

Rôles et permissions

Postgres distingue les rôles (qui peuvent se connecter ou posséder des objets) des privilèges (ce qu’un rôle est autorisé à faire). Appliquez toujours le principe du moindre privilège.

CREATE ROLE app_user LOGIN PASSWORD 'secret';

GRANT CONNECT ON DATABASE shop TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public TO app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

La ligne ALTER DEFAULT PRIVILEGES est cruciale car elle s’applique aux tables créées ultérieurement, et pas seulement aux tables existantes. Évitez de connecter l’application en tant que propriétaire de la base de données ou superutilisateur ; une application compromise ne devrait pas être capable de DROP TABLE. Pour les migrations, utilisez un rôle distinct disposant des droits DDL.

Sauvegardes et récupération

Il existe deux types de sauvegardes. Les sauvegardes logiques utilisent pg_dump pour produire un script portable ou une archive d’une base de données, et pg_dumpall pour capturer les rôles et les paramètres globaux. Elles sont simples et compatibles entre différentes versions, mais plus lentes à restaurer à grande échelle.

pg_dump --format=custom --file=shop.dump shop
pg_restore --dbname=shop_restore shop.dump

Les sauvegardes physiques copient le répertoire de données et le journal d’écriture anticipée (write-ahead log). pg_basebackup combiné à l’archivage continu du WAL permet la récupération à un instant T (point-in-time recovery), vous permettant de restaurer vos données à un moment précis avant une migration défectueuse. C’est l’approche utilisée par les fournisseurs managés.

Quel que soit votre choix, la règle reste la même : automatisez la sauvegarde, stockez-la en dehors de l’hôte principal et restaurez-la régulièrement dans une base de données de test. Une sauvegarde que vous n’avez jamais restaurée est un espoir, pas un plan.

Recherche plein texte sans service tiers

Postgres intègre nativement la recherche plein texte, ce qui suffit souvent pour éviter de gérer un cluster de recherche séparé. Cela fonctionne en convertissant le texte en un tsvector de lexèmes et les requêtes en un tsquery, puis en les faisant correspondre.

ALTER TABLE posts
  ADD COLUMN search tsvector
    GENERATED ALWAYS AS (
      to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
    ) STORED;

CREATE INDEX posts_search_idx ON posts USING gin (search);

SELECT id, title
FROM posts
WHERE search @@ plainto_tsquery('english', 'connection pooling')
ORDER BY ts_rank(search, plainto_tsquery('english', 'connection pooling')) DESC
LIMIT 20;

Une colonne générée permet de synchroniser le vecteur automatiquement, et l’index GIN rend la @@ rapide. plainto_tsquery convertit en toute sécurité l’entrée utilisateur en requête, tandis que ts_rank trie par pertinence. Vous pouvez ajouter ts_headline pour mettre en évidence les correspondances dans les résultats.

Ne vous tournez vers un moteur de recherche dédié que si vous avez besoin de facettes, d’une tolérance aux fautes de frappe (fuzzy search) à grande échelle ou d’un réglage de la pertinence entre plusieurs documents. Pour la plupart des applications, la version intégrée permet de supprimer une pièce mobile entière de votre infrastructure.

Vues et vues matérialisées

Une vue est une requête enregistrée qui se comporte comme une table. Elle ne stocke pas de données ; c’est un moyen nommé d’encapsuler une structure commune et de maintenir la cohérence des permissions.

CREATE VIEW paid_orders AS
SELECT id, user_id, total_cents, created_at
FROM orders
WHERE status = 'paid';

SELECT * FROM paid_orders WHERE created_at >= now() - interval '7 days';

Une vue matérialisée stocke quant à elle le résultat, ce qui rend les agrégations coûteuses rapides à lire, au prix d’une potentielle obsolescence des données.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
       sum(total_cents)             AS revenue_cents
FROM orders
WHERE status = 'paid'
GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

CONCURRENTLY se rafraîchit sans bloquer les lecteurs, mais nécessite un index unique sur la vue. Planifiez vos rafraîchissements en fonction de votre tolérance aux données obsolètes, et n’oubliez pas qu’une vue matérialisée est un cache : elle peut toujours être reconstruite à partir des tables de base.

Modifier les schémas en toute sécurité

Sur une base de données en production, le DDL impose des verrous. L’objectif est d’éviter les verrous ACCESS EXCLUSIVE prolongés qui bloquent les lectures et les écritures pendant la réécriture d’une table.

ALTER TABLE orders ADD COLUMN channel text;
ALTER TABLE orders ALTER COLUMN channel SET DEFAULT 'web';

CREATE INDEX CONCURRENTLY orders_channel_idx ON orders (channel);

L’ajout d’une colonne nullable sans valeur par défaut est instantané dans les versions récentes de Postgres, et la définition d’une valeur par défaut ne modifie que les métadonnées. CREATE INDEX CONCURRENTLY construit l’index sans maintenir de verrou d’écriture, bien qu’il ne puisse pas être exécuté à l’intérieur d’une transaction et puisse échouer, laissant un index invalide qu’il faudra supprimer et relancer. L’ajout d’une contrainte NOT NULL sur une table volumineuse doit être effectué par étapes : ajoutez une contrainte CHECK validée séparément, puis convertissez-la.

Versionnez chaque modification sous forme de migration afin que chaque environnement atteigne le même schéma dans le même ordre. Ne modifiez jamais une table de production manuellement ; vous oublieriez forcément ce que vous avez fait.

Chargement massif avec COPY

L’insertion de lignes une par une est la méthode la plus lente pour charger des données. Postgres propose COPY, qui diffuse les données via une seule commande et peut être dix fois plus rapide qu’une boucle de INSERT.

COPY orders (user_id, status, total_cents, created_at)
FROM '/data/orders.csv'
WITH (FORMAT csv, HEADER true);

COPY (SELECT id, email FROM users WHERE created_at > now() - interval '30 days')
TO '/tmp/recent-users.csv'
WITH (FORMAT csv, HEADER true);

COPY s’exécute sur le serveur, le fichier doit donc être lisible par le processus de la base de données ; utilisez \copy dans psql pour lire depuis le client à la place. Pour le code applicatif, l’API copy du driver permet de diffuser les lignes sans passer par un fichier temporaire. Enveloppez le chargement dans une transaction lorsque vous avez besoin d’une opération atomique (tout ou rien), et supprimez ou reconstruisez les index après des chargements très volumineux, car la maintenance des index pendant une insertion massive est le coût principal.

Les insertions par lots (batch inserts) constituent un compromis plus simple lorsque COPY n’est pas envisageable :

INSERT INTO orders (user_id, status, total_cents)
VALUES (1, 'paid', 1999),
       (2, 'paid', 4599),
       (3, 'pending', 999);

Bonnes pratiques

• Choisissez text, bigint, numeric, timestamptz et jsonb de manière réfléchie plutôt que d’utiliser par défaut varchar et les floats.
• Déclarez NOT NULL, UNIQUE, CHECK et les clés étrangères dans la même migration que celle qui crée la table.
• Concevez vos index à partir de requêtes réelles et validez-les avec EXPLAIN ANALYZE.
• Gardez des transactions courtes et choisissez le niveau d’isolation le plus faible possible tout en restant correct.
• Placez un pooler tel que PgBouncer devant la base de données et gérez également le pooling au niveau de l’application.
• Utilisez JSONB pour les données réellement variables, et non comme un moyen d’éviter la conception du schéma.
• Accordez le privilège minimum aux rôles de l’application et réservez le DDL à un rôle de migration.
• Activez pg_stat_statements, surveillez l’autovacuum et restaurez périodiquement une sauvegarde.
• Versionnez chaque modification de schéma sous forme de migration afin que les environnements restent synchronisés.

Erreurs courantes

• Stocker de l’argent dans des double precision et perdre des centimes à cause des arrondis.
• Utiliser timestamp au lieu de timestamptz et stocker accidentellement des heures locales.
• Oublier WHERE sur un UPDATE ou DELETE et modifier ainsi chaque ligne.
• Créer un index par colonne au lieu d’index composites correspondant à la requête.
• Ouvrir une nouvelle connexion par requête et épuiser max_connections.
• Maintenir une transaction ouverte pendant un appel HTTP ou une interaction utilisateur.
• Considérer que NULL est équivalent à NULL ; les comparaisons nécessitent IS NULL et IS NOT NULL.
• Supposer qu’un index est utilisé sans vérifier le plan d’exécution de la requête.
• Exécuter les requêtes de l’application en tant que propriétaire de la base de données ou superutilisateur.

Et après ?

Postgres récompense l’approfondissement, et la suite logique est de maîtriser le langage qu’il utilise : consultez le guide SQL pour perfectionner vos connaissances sur les jointures, les CTE et les fonctions de fenêtrage (window functions). Si vous envisagez une alternative, MySQL couvre l’autre base de données relationnelle open-source dominante, MongoDB explique le modèle documentaire, et Redis présente la couche de mise en cache qui se place généralement devant un stockage relationnel.