Les bases de données relationnelles : PostgreSQL en pratique

PostgreSQL, référence open source

PostgreSQL est une base de données relationnelle open source reconnue pour sa fiabilité, ses performances, et ses fonctionnalités avancées. Souvent considérée comme la meilleure alternative aux solutions commerciales comme Oracle, elle est utilisée par des entreprises de toutes tailles, de la startup aux grands groupes. Pour un développeur, maîtriser PostgreSQL ouvre des possibilités dans presque tous les projets. Ce tutoriel présente les fondamentaux et les pratiques essentielles.

Installation et premier contact

Plusieurs options. Postgres.app sur macOS. Installeur Windows. Packages natifs sur Linux. Docker : un conteneur PostgreSQL se lance en une commande, idéal pour développement. Cloud managé : AWS RDS, Azure Database, Google Cloud SQL, Supabase, Neon pour éviter la gestion.

Un client graphique facilite la manipulation. pgAdmin est le client officiel. DBeaver, TablePlus, DataGrip sont des alternatives populaires. En ligne de commande, psql est puissant et rapide.

Les concepts SQL fondamentaux

Une base de données contient des schémas (namespaces). Un schéma contient des tables. Une table est un ensemble de lignes avec des colonnes typées. Les relations entre tables se font via des clés étrangères.

Les opérations de base sont CRUD : Create (insérer), Read (lire), Update (modifier), Delete (supprimer). Le SQL standardise ces opérations.

Créer une base et des tables

CREATE DATABASE mon_app; se connecte à la base : psql mon_app. Créer une table :

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

SERIAL génère automatiquement un identifiant unique. PRIMARY KEY est la clé primaire. UNIQUE impose l’unicité. NOT NULL interdit les valeurs nulles. DEFAULT donne une valeur par défaut.

Les types de données

PostgreSQL offre de nombreux types. Numériques : INTEGER, BIGINT, DECIMAL(p,s) pour les nombres précis (monnaie), REAL, DOUBLE PRECISION. Chaînes : VARCHAR(n), TEXT (sans limite). Dates : DATE, TIME, TIMESTAMP, INTERVAL. Booléen : BOOLEAN.

Types avancés. UUID pour les identifiants universels. JSON et JSONB (binary) pour stocker des documents JSON. ARRAY pour les tableaux. POINT, LINE, POLYGON pour le géométrique. ENUM pour les valeurs restreintes. Ces types riches distinguent PostgreSQL.

Les opérations de base

INSERT : INSERT INTO users (email, name) VALUES (‘amadou@example.com’, ‘Amadou’);. Pour plusieurs lignes : INSERT INTO users (email, name) VALUES (‘a@x.com’, ‘A’), (‘b@x.com’, ‘B’);.

SELECT : SELECT * FROM users; liste tout. SELECT name, email FROM users WHERE age > 25 ORDER BY name; avec filtre et tri. LIMIT 10 restreint le nombre de résultats. DISTINCT élimine les doublons.

UPDATE : UPDATE users SET name = ‘Amadou Diop’ WHERE id = 1;. Toujours avec WHERE pour éviter de modifier toute la table.

DELETE : DELETE FROM users WHERE id = 1;. Même prudence sur le WHERE.

Les jointures

Les jointures combinent des données de plusieurs tables. INNER JOIN retourne les lignes ayant une correspondance dans les deux tables. LEFT JOIN retourne toutes les lignes de gauche, avec NULL si pas de correspondance. RIGHT JOIN l’équivalent à droite. FULL OUTER JOIN retourne toutes les lignes des deux côtés.

SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id
WHERE orders.created_at > '2026-01-01';

Les clés étrangères

Les clés étrangères maintiennent l’intégrité référentielle. Une commande référence un utilisateur via user_id :

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

PostgreSQL empêche de créer une commande pour un utilisateur qui n’existe pas. ON DELETE CASCADE supprime les commandes quand l’utilisateur est supprimé ; ON DELETE SET NULL met le lien à NULL.

Les index pour la performance

Les index accélèrent les recherches. Une recherche sur une colonne sans index scanne toute la table ; avec index, elle accède directement aux lignes pertinentes. CREATE INDEX idx_users_email ON users(email);.

Indexez les colonnes utilisées en WHERE, JOIN, ORDER BY. Attention : trop d’index ralentissent les écritures (chaque INSERT/UPDATE doit mettre à jour les index). Équilibre à trouver selon les patterns d’usage.

Les index composites

Pour des requêtes avec plusieurs critères, un index composite peut être plus efficace. CREATE INDEX ON orders(user_id, created_at);. L’ordre des colonnes compte : l’index est efficace pour user_id seul ou user_id + created_at, mais pas pour created_at seul.

Les fonctions d’agrégation

COUNT, SUM, AVG, MIN, MAX. Souvent avec GROUP BY. SELECT user_id, COUNT(*) as nb_commandes, SUM(total) as total FROM orders GROUP BY user_id;. HAVING filtre les groupes (comme WHERE filtre les lignes).

Les transactions

Une transaction regroupe plusieurs opérations qui réussissent ou échouent ensemble. BEGIN; puis les requêtes, puis COMMIT; pour valider ou ROLLBACK; pour annuler.

Exemple : transférer 100 d’un compte à un autre. Si l’une des opérations échoue, on revient à l’état initial. Cette atomicité est essentielle pour préserver la cohérence des données.

Les contraintes

Au-delà des clés, plusieurs contraintes sécurisent les données. CHECK impose une condition : CHECK (age >= 0). UNIQUE impose l’unicité. NOT NULL interdit les nulls. Ces contraintes empêchent les données incohérentes dès l’insertion.

Le JSON dans PostgreSQL

JSONB stocke des documents JSON avec indexation et requêtes efficaces. Combine les avantages du relationnel strict et du NoSQL flexible. CREATE TABLE products (id SERIAL, metadata JSONB);. Requêtes : SELECT * FROM products WHERE metadata->>’category’ = ‘electronics’;.

Les fonctions stockées

PostgreSQL supporte des fonctions SQL, PL/pgSQL (langage procédural), Python, JavaScript. Pour la logique complexe proche des données, ces fonctions peuvent être performantes. Utilisation modérée : la logique métier dans l’application reste généralement plus maintenable.

Les backups

pg_dump exporte une base. pg_restore la restaure. Pour les bases productives, automatiser des backups réguliers. Les services cloud gérés le font automatiquement.

Tester régulièrement la restauration : un backup jamais testé est un backup incertain.

Les migrations de schéma

Le schéma évolue avec l’application. Gérer ces changements avec des outils de migration : Prisma Migrate, TypeORM, Knex, Flyway, Liquibase. Chaque changement devient un fichier versionné appliqué ordonnancement. Cette discipline évite les divergences entre environnements.

La sécurité

Principes. Utilisateurs avec droits minimaux (pas de root applicatif). Connexions en TLS (SSL) en production. Requêtes paramétrées pour prévenir les injections SQL. Sauvegardes chiffrées. Audit des accès pour les données sensibles.

Les performances

EXPLAIN analyse un plan de requête : PostgreSQL montre comment il va l’exécuter. Identifie les scans complets de table (souvent signe d’index manquant), les jointures coûteuses, les tris mémoire. EXPLAIN ANALYZE exécute la requête et donne les chiffres réels.

Configuration : ajuster shared_buffers, work_mem, effective_cache_size selon la mémoire disponible. Les valeurs par défaut sont conservatrices.

Les fonctionnalités avancées

Vues : CREATE VIEW simplifie les requêtes complexes récurrentes. Triggers : code exécuté automatiquement sur INSERT/UPDATE/DELETE. Fonctions window : calculs sur des fenêtres de données (rangs, moyennes glissantes). Common Table Expressions (WITH) : requêtes plus lisibles, récursivité possible.

Ces fonctionnalités se découvrent au fur et à mesure. Pour la plupart des applications, les bases suffisent amplement.

L’extension d’écosystème

PostgreSQL dispose de nombreuses extensions. PostGIS pour le SIG (géographique). pg_trgm pour la recherche floue. hstore pour les paires clé-valeur. TimescaleDB pour les séries temporelles. Ces extensions transforment PostgreSQL en outil polyvalent.

Les alternatives et quand utiliser quoi

PostgreSQL versus MySQL : fonctionnellement comparables ; PostgreSQL historiquement plus avancé, MySQL plus simple. Relationnel versus NoSQL (MongoDB, DynamoDB) : le relationnel reste la référence pour données structurées avec relations ; NoSQL pour des besoins spécifiques (documents libres, très haute charge avec modèle simple).

En 2026, PostgreSQL est un choix par défaut sûr pour la plupart des projets.

Conclusion : investissement durable

Apprendre SQL et PostgreSQL est un des investissements les plus durables en technologie. Le SQL n’a pas changé fondamentalement depuis des décennies et ne changera probablement pas fondamentalement demain. Les compétences transfèrent entre toutes les bases relationnelles. Pour un développeur, c’est une compétence qui reste pertinente toute une carrière. Pratiquez sur un projet concret, explorez les fonctionnalités avancées au fur et à mesure des besoins, et construisez progressivement une vraie maîtrise.