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

Ce que vous saurez faire à la fin

1. Installer PostgreSQL localement et se connecter
2. Créer des tables avec contraintes, index et types appropriés
3. Requêter avec SELECT, JOIN, agrégations, window functions
4. Optimiser avec EXPLAIN et ajouter les bons index
5. Sauvegarder, restaurer, migrer proprement

Durée : 4 heures. Pré-requis : Linux/Mac/Windows. Bases SQL souhaitables.

Étape 1 — Installation

# Ubuntu/Debian
sudo apt install -y postgresql-16 postgresql-contrib

# macOS
brew install postgresql@16
brew services start postgresql@16

# Docker (simple)
docker run -d --name pg \
  -e POSTGRES_PASSWORD=dev \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16

# Vérifier
psql --version

Étape 2 — Premier utilisateur et base

sudo -u postgres psql

postgres=# CREATE USER itsc WITH PASSWORD 'S3cretL0ng!';
postgres=# CREATE DATABASE itskillscenter OWNER itsc;
postgres=# GRANT ALL PRIVILEGES ON DATABASE itskillscenter TO itsc;
postgres=# \q

# Se connecter avec le nouvel utilisateur
psql -h localhost -U itsc itskillscenter

Étape 3 — Créer des tables avec contraintes

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE clients (
  id           BIGSERIAL PRIMARY KEY,
  nom          TEXT NOT NULL CHECK (char_length(nom) BETWEEN 2 AND 100),
  email        CITEXT UNIQUE,
  telephone    TEXT,
  ville        TEXT,
  cree_le      TIMESTAMPTZ NOT NULL DEFAULT now(),
  mis_a_jour   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE factures (
  id             BIGSERIAL PRIMARY KEY,
  numero         TEXT NOT NULL UNIQUE,
  client_id      BIGINT NOT NULL REFERENCES clients(id) ON DELETE RESTRICT,
  date_emission  DATE NOT NULL,
  montant_ht     NUMERIC(12,2) NOT NULL CHECK (montant_ht >= 0),
  taux_tva       NUMERIC(4,3) NOT NULL DEFAULT 0.18,
  statut         TEXT NOT NULL CHECK (statut IN ('brouillon','envoyee','payee','annulee'))
);

CREATE INDEX idx_factures_client ON factures(client_id);
CREATE INDEX idx_factures_statut_date ON factures(statut, date_emission DESC);

Étape 4 — Insertion et retour

INSERT INTO clients(nom, email, ville) VALUES
  ('SARL Dakar', 'contact@sarldakar.sn', 'Dakar'),
  ('Ets Thies', 'eu@ets.sn', 'Thies')
RETURNING id, nom;

-- Upsert (insert ou update)
INSERT INTO clients(email, nom, ville)
VALUES ('contact@sarldakar.sn', 'SARL Dakar SA', 'Dakar')
ON CONFLICT (email) DO UPDATE
  SET nom = EXCLUDED.nom,
      ville = EXCLUDED.ville,
      mis_a_jour = now()
RETURNING id, (xmax = 0) AS inserted;

Étape 5 — SELECT avec jointures

SELECT c.nom, COUNT(f.*) AS nb_factures,
       SUM(f.montant_ht) AS total_ht
FROM clients c
LEFT JOIN factures f
  ON f.client_id = c.id
  AND f.date_emission >= '2026-01-01'
GROUP BY c.id, c.nom
HAVING SUM(f.montant_ht) > 1000000
ORDER BY total_ht DESC
LIMIT 20;

Étape 6 — Window functions

-- Classement par CA, dans chaque région
SELECT region, commercial, ca,
  RANK()       OVER (PARTITION BY region ORDER BY ca DESC) AS rang,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY ca DESC) AS rang_dense,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY ca DESC) AS ligne
FROM ventes_agregees;

-- Évolution mois par mois
SELECT mois, ca,
       LAG(ca)  OVER (ORDER BY mois) AS ca_m_1,
       ca - LAG(ca) OVER (ORDER BY mois) AS evolution,
       SUM(ca) OVER (ORDER BY mois) AS cumul,
       AVG(ca) OVER (ORDER BY mois
                     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moy_mobile_3m
FROM ventes_mois;

Étape 7 — CTE (WITH)

WITH top_clients AS (
  SELECT client_id, SUM(montant_ht) AS total
  FROM factures
  WHERE statut = 'payee' AND date_emission >= '2026-01-01'
  GROUP BY client_id
  ORDER BY total DESC
  LIMIT 10
)
SELECT c.nom, tc.total
FROM top_clients tc
JOIN clients c ON c.id = tc.client_id;

Étape 8 — CTE récursive

-- Afficher tous les subordonnés d'un manager
WITH RECURSIVE equipe AS (
  SELECT id, nom, manager_id, 1 AS niveau
  FROM employes
  WHERE id = 42
  UNION ALL
  SELECT e.id, e.nom, e.manager_id, eq.niveau + 1
  FROM employes e
  JOIN equipe eq ON e.manager_id = eq.id
)
SELECT repeat('  ', niveau - 1) || nom AS hierarchie
FROM equipe
ORDER BY niveau, nom;

Étape 9 — Transactions

BEGIN;
  UPDATE comptes SET solde = solde - 50000 WHERE id = 1;
  UPDATE comptes SET solde = solde + 50000 WHERE id = 2;
  SELECT id, solde FROM comptes WHERE id IN (1,2);
COMMIT;
-- ou ROLLBACK; en cas d'erreur

-- Isolation stricte
BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- ... opérations sensibles
COMMIT;

Étape 10 — JSON/JSONB

CREATE TABLE evenements (
  id BIGSERIAL PRIMARY KEY,
  ts TIMESTAMPTZ DEFAULT now(),
  payload JSONB NOT NULL
);

INSERT INTO evenements(payload) VALUES
  ('{"type":"signup","user":"aminata","plan":"pro"}'),
  ('{"type":"order","user":"aminata","montant":45000}');

CREATE INDEX idx_ev_type ON evenements ((payload->>'type'));
CREATE INDEX idx_ev_payload ON evenements USING GIN (payload);

-- Requêter
SELECT payload->>'user' AS user, COUNT(*)
FROM evenements
WHERE payload @> '{"type":"signup"}'
GROUP BY 1;

-- Extraction typée
SELECT (payload->>'montant')::NUMERIC AS mt
FROM evenements WHERE payload ? 'montant';

Étape 11 — Recherche full-text français

ALTER TABLE clients ADD COLUMN recherche tsvector
  GENERATED ALWAYS AS (
    to_tsvector('french', coalesce(nom,'') || ' ' || coalesce(ville,''))
  ) STORED;

CREATE INDEX idx_clients_recherche ON clients USING GIN(recherche);

SELECT nom, ts_rank(recherche, q) AS score
FROM clients, plainto_tsquery('french', 'dakar sarl') q
WHERE recherche @@ q
ORDER BY score DESC;

Étape 12 — EXPLAIN pour optimiser

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM factures
WHERE client_id = 42 AND statut = 'envoyee'
ORDER BY date_emission DESC
LIMIT 50;

-- Points à analyser:
-- "Seq Scan" sur table > 100k lignes = index manquant
-- "actual time=X..Y" réel vs "cost=" estimé
-- Si le ratio est très différent → ANALYZE la table

Solutions courantes :

-- Ajouter un index manquant
CREATE INDEX idx_factures_client_statut ON factures(client_id, statut) 
WHERE statut != 'brouillon';

-- Mettre à jour les statistiques
ANALYZE factures;

-- Voir l'utilisation des index
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Étape 13 — Sauvegarde et restauration

# Dump complet
pg_dump -h localhost -U itsc itskillscenter > backup.sql

# Dump binaire compressé (plus rapide à restaurer)
pg_dump --format=custom --compress=9 -d itskillscenter > backup.dump

# Restauration
psql -U itsc -d itskillscenter < backup.sql
pg_restore -U itsc -d itskillscenter --clean --if-exists backup.dump

# Dump automatisé quotidien
crontab -e
# 0 2 * * * pg_dump -Fc itsc | gzip > /backup/itsc-$(date +\%F).gz

Étape 14 — Migrations avec un outil

# Installer Flyway ou Liquibase, ou pour Node.js: Prisma, Drizzle, Knex

npm install -D knex pg

# knexfile.js
module.exports = {
  client: "pg",
  connection: process.env.DATABASE_URL,
  migrations: { directory: "./migrations" }
};

# Créer une migration
npx knex migrate:make ajouter_table_abonnements

# Exécuter
npx knex migrate:latest

# Rollback
npx knex migrate:rollback

Étape 15 — Sécurité

-- Rôles par privilège minimal
CREATE ROLE app_read LOGIN PASSWORD 'xxx';
GRANT CONNECT ON DATABASE itskillscenter TO app_read;
GRANT USAGE ON SCHEMA public TO app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;

CREATE ROLE app_write LOGIN PASSWORD 'yyy';
GRANT SELECT, INSERT, UPDATE ON factures, clients TO app_write;

-- Row-Level Security (multi-tenant)
ALTER TABLE clients ENABLE ROW LEVEL SECURITY;
CREATE POLICY client_isolation ON clients
  USING (tenant_id = current_setting('app.tenant_id')::int);

-- Chiffrement de colonne sensible
CREATE EXTENSION pgcrypto;
INSERT INTO clients(nom, email_chiffre) VALUES
  ('SARL', pgp_sym_encrypt('contact@sarl.sn', current_setting('app.key')));

Checklist

✓ Mots de passe dans des rôles dédiés (pas postgres)
✓ SSL forcé pour connexions distantes
✓ Index sur colonnes filtrées/jointes
✓ ANALYZE régulier (ou autovacuum actif)
✓ Sauvegarde quotidienne + test de restauration mensuel
✓ Migrations versionnées (knex/flyway/sqitch)
✓ EXPLAIN ANALYZE avant de déployer une requête lourde
✓ Row-Level Security si multi-tenant
✓ Monitoring (pg_stat_statements)