Bases de données PostgreSQL : fondamentaux pour développeurs

PostgreSQL est devenu en 2026 le système de gestion de base de données relationnelle de référence pour les nouveaux projets : open-source, conforme SQL standard, doté d’extensions de classe production (PostGIS, pgvector, TimescaleDB), et constamment amélioré sur les performances. La version 18.3, sortie en février 2026, multiplie par trois les performances I/O sur les workloads en lecture grâce à un nouveau sous-système d’I/O asynchrone, et apporte les colonnes générées virtuelles, le skip-scan sur index B-tree multi-colonnes, ainsi que les contraintes temporelles. Ce tutoriel pose les fondamentaux qu’un développeur doit maîtriser pour livrer du code qui parle proprement à PostgreSQL en production.

Prérequis

• Un poste Linux, macOS ou Windows
• Droits administrateur ou Docker installé
• Un terminal et un éditeur de texte
• Notions élémentaires de programmation (variables, types)
• Une heure devant soi pour suivre l’ensemble des étapes
• Niveau attendu : débutant SQL, à l’aise en ligne de commande

Étape 1 — Installer PostgreSQL 18

Trois méthodes coexistent en 2026, du plus simple au plus contrôlé : Docker pour développer, paquet système pour un VPS, build source pour les besoins très spécifiques. Pour ce tutoriel, on choisit Docker — l’installation prend 30 secondes et ne pollue pas le système.

docker run --name pg18 \
  -e POSTGRES_PASSWORD=motdepasse \
  -e POSTGRES_DB=ma_base \
  -p 5432:5432 \
  -d postgres:18.3

L’image officielle postgres:18.3 télécharge environ 150 Mo. Le conteneur démarre immédiatement, écoute sur le port 5432 du localhost, et expose une base nommée ma_base avec un utilisateur postgres et le mot de passe choisi. Vérifier que c’est bien parti : docker ps | grep pg18 doit afficher STATUS Up. Si le port 5432 est déjà occupé, mapper sur 5433 avec -p 5433:5432.

Étape 2 — Se connecter avec psql

psql est le client en ligne de commande officiel, plus pratique qu’une interface graphique pour apprendre — chaque commande est explicite et reproductible.

# Sur Linux/macOS, psql est dans le paquet postgresql-client
sudo apt install postgresql-client    # Ubuntu/Debian
brew install libpq && brew link --force libpq   # macOS

# Connexion
psql -h localhost -U postgres -d ma_base
# (saisir le mot de passe quand demandé)

L’invite ma_base=# indique que vous êtes connecté avec les droits superuser. Quelques meta-commandes utiles dès le départ : \dt liste les tables, \d nom_table décrit une table, \q quitte, \? affiche l’aide complète. Toutes les commandes SQL standard (SELECT, INSERT, etc.) finissent par un point-virgule sinon psql attend la suite de la requête.

Étape 3 — Créer une table avec contraintes

La force de PostgreSQL est l’expressivité du schéma : types riches, contraintes déclaratives, valeurs par défaut. On modélise ici un mini-blog avec une table d’auteurs et une table d’articles liées par une clé étrangère.

CREATE TABLE auteurs (
  id          BIGSERIAL PRIMARY KEY,
  email       TEXT UNIQUE NOT NULL,
  nom         TEXT NOT NULL,
  cree_le     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE articles (
  id          BIGSERIAL PRIMARY KEY,
  auteur_id   BIGINT NOT NULL REFERENCES auteurs(id) ON DELETE CASCADE,
  titre       TEXT NOT NULL,
  contenu     TEXT,
  publie      BOOLEAN NOT NULL DEFAULT false,
  publie_le   TIMESTAMPTZ,
  CONSTRAINT  publie_coherent CHECK (
    (publie = false AND publie_le IS NULL) OR
    (publie = true  AND publie_le IS NOT NULL)
  )
);

Trois éléments structurants. BIGSERIAL génère un identifiant numérique auto-incrémenté de 8 octets — préférable à SERIAL (4 octets) dès qu’on s’attend à dépasser deux milliards de lignes. TIMESTAMPTZ stocke les dates avec fuseau horaire, ce qui évite les bugs classiques d’écart d’heure entre serveur et client. La CHECK CONSTRAINT garantit l’invariant métier : un article publié a forcément une date de publication, un brouillon n’en a pas. PostgreSQL refuse les INSERT qui violent cette règle, ce qui blinde l’intégrité côté base sans dépendre du code applicatif.

Étape 4 — Insérer, lire, mettre à jour

Les quatre opérations CRUD (Create, Read, Update, Delete) suivent la syntaxe SQL standard. PostgreSQL ajoute la clause RETURNING qui rend l’identifiant généré disponible immédiatement, ce qui évite un aller-retour réseau supplémentaire.

-- Insérer un auteur et récupérer son id
INSERT INTO auteurs (email, nom)
VALUES ('aida@example.org', 'Aïda Sow')
RETURNING id;

-- Insérer un article publié
INSERT INTO articles (auteur_id, titre, contenu, publie, publie_le)
VALUES (1, 'Premier post', 'Contenu du post.', true, now());

-- Sélectionner les articles publiés avec le nom de l'auteur
SELECT a.titre, au.nom, a.publie_le
FROM articles a
JOIN auteurs au ON au.id = a.auteur_id
WHERE a.publie = true
ORDER BY a.publie_le DESC
LIMIT 10;

-- Mettre à jour conditionnellement
UPDATE articles SET publie = false, publie_le = NULL
WHERE id = 1
RETURNING *;

Le RETURNING dans INSERT et UPDATE est l’astuce la plus utile au quotidien : elle remplace le pattern insert puis select en un seul aller-retour. Côté lecture, le JOIN sur la clé étrangère est instantané grâce à l’index implicite sur la PRIMARY KEY ; à l’inverse, sans index sur auteur_id, la même requête sur 100 000 articles prendrait 200 ms au lieu de 2 ms. C’est l’objet de l’étape suivante.

Étape 5 — Index et plans d’exécution

Un index transforme une recherche linéaire (parcours complet de la table) en lookup logarithmique. Bien posés, les index font passer une requête de plusieurs secondes à quelques millisecondes ; mal posés, ils alourdissent les écritures sans bénéfice.

-- Index sur la clé étrangère (à faire systématiquement)
CREATE INDEX articles_auteur_id_idx ON articles(auteur_id);

-- Index partiel : seulement les articles publiés
CREATE INDEX articles_publies_idx ON articles(publie_le DESC)
WHERE publie = true;

-- Vérifier que l'index est utilisé
EXPLAIN ANALYZE
SELECT * FROM articles WHERE publie = true ORDER BY publie_le DESC LIMIT 10;

L’index partiel (clause WHERE dans CREATE INDEX) ne contient que les lignes utiles : pour 1 % de publiés sur 1 million d’articles, il pèse 100× moins qu’un index complet. EXPLAIN ANALYZE exécute réellement la requête et affiche le plan choisi par le planificateur. La ligne Index Scan using articles_publies_idx confirme que l’index est utilisé ; Seq Scan indique l’inverse — soit l’index manque, soit la requête le contourne pour cause de statistiques obsolètes (résoudre avec ANALYZE articles).

Étape 6 — Transactions et isolation

Une transaction garantit qu’un groupe d’opérations s’effectue ou échoue de manière atomique : aucun état intermédiaire visible aux autres clients. C’est la propriété indispensable pour les opérations multi-lignes (paiement débit + crédit, transfert d’inventaire).

BEGIN;
  UPDATE comptes SET solde = solde - 100 WHERE id = 1;
  UPDATE comptes SET solde = solde + 100 WHERE id = 2;
  -- vérifier qu'aucun solde n'est négatif avant de commit
  SELECT 1 / (SELECT count(*) FROM comptes WHERE solde < 0);
COMMIT;
-- ou ROLLBACK; pour annuler

Si la division par zéro échoue (un solde est devenu négatif), la transaction passe en état aborted et le COMMIT est refusé. PostgreSQL propose quatre niveaux d’isolation (read uncommitted, read committed, repeatable read, serializable) ; read committed est le défaut et convient à 95 % des cas. Pour les workflows financiers, basculer sur SET TRANSACTION ISOLATION LEVEL SERIALIZABLE avant BEGIN évite les anomalies subtiles de lecture concurrente.

Étape 7 — Joins, sous-requêtes et CTE

Les requêtes complexes combinent plusieurs tables et des étapes intermédiaires. Les Common Table Expressions (CTE, syntaxe WITH) rendent ces requêtes lisibles en nommant chaque étape.

-- Articles publiés des auteurs ayant écrit plus de trois articles
WITH prolifiques AS (
  SELECT auteur_id, count(*) AS nb
  FROM articles
  WHERE publie = true
  GROUP BY auteur_id
  HAVING count(*) > 3
)
SELECT au.nom, p.nb, a.titre, a.publie_le
FROM prolifiques p
JOIN auteurs au   ON au.id = p.auteur_id
JOIN articles a   ON a.auteur_id = p.auteur_id AND a.publie = true
ORDER BY au.nom, a.publie_le DESC;

La CTE prolifiques sert de table virtuelle réutilisable dans la suite de la requête. Lue de haut en bas, la requête se comprend comme un raisonnement métier : trouver les auteurs prolifiques, joindre leurs articles, ordonner. Sans CTE, on aurait imbriqué une sous-requête dans la clause FROM — fonctionnel mais nettement moins lisible. PostgreSQL 18 optimise désormais les CTE comme des sous-requêtes inline (materialization automatique seulement quand pertinent), ce qui supprime la pénalité de performance des anciennes versions.

Étape 8 — Sauvegarde et restauration

La sauvegarde régulière est non-négociable en production. PostgreSQL fournit deux outils canoniques : pg_dump pour exporter une base sous forme de script SQL ou de format binaire, pg_restore pour la restaurer.

# Dump format custom (compressé, restaurable partiellement)
pg_dump -h localhost -U postgres -F c -f ma_base.dump ma_base

# Restauration sur une nouvelle base
createdb -h localhost -U postgres ma_base_restore
pg_restore -h localhost -U postgres -d ma_base_restore ma_base.dump

Le format custom (-F c) est le défaut pratique : compression gzip, restauration sélective table par table possible, plus rapide que le SQL pur sur les grosses bases. En production, automatiser via cron : 0 3 * * * pg_dump -F c -f /backup/ma_base_$(date +\%F).dump ma_base. Pour les volumes importants, basculer sur pg_basebackup + WAL archiving qui permet la point-in-time recovery à la seconde près. Tester la restauration au moins une fois par mois — un backup non testé n’en est pas un.

Erreurs fréquentes

Pour explorer plus loin

• Documentation officielle PostgreSQL 18
• Notes de release PostgreSQL 18.0
• Tutoriel SQL Getting Started
• Image Docker officielle postgres sur Docker Hub
• Outil GUI pgAdmin 4 ou DBeaver