PostgreSQL

À quoi ça sert

PostgreSQL (souvent abrégé Postgres) est une base de données SQL serveur : contrairement à SQLite qui est un simple fichier, Postgres tourne comme un processus séparé auquel plusieurs applications peuvent se connecter en même temps via le réseau.

Concrètement, on s'en sert pour :

• Stocker les données métier d'une API en production (utilisateurs, produits, commandes…).
• Servir plusieurs clients en parallèle — c'est précisément ce que SQLite ne sait pas bien faire.
• Persister des données complexes — Postgres gère nativement le JSON, les tableaux, les types géographiques (PostGIS), les recherches plein-texte, etc.
• Stocker le tracking MLflow — le serveur MLflow utilise typiquement Postgres comme backend store pour les runs, paramètres, métriques.
• Servir de base à Supabase — Supabase, c'est Postgres + une couche d'outils par-dessus.

Un exemple d'usage

On lance un Postgres en local (via Docker), on crée une base, et on s'y connecte depuis une appli Python avec SQLAlchemy :

# Lancer un Postgres dans Docker en une commande
docker run -d --name pg \
    -e POSTGRES_PASSWORD=secret \
    -e POSTGRES_DB=appdb \
    -p 5432:5432 \
    postgres:16

# Se connecter avec psql (client en CLI)
docker exec -it pg psql -U postgres -d appdb

from sqlalchemy import create_engine, text

# Format : postgresql+psycopg://user:password@host:port/database
engine = create_engine("postgresql+psycopg://postgres:secret@localhost:5432/appdb")

with engine.connect() as conn:
    conn.execute(text("CREATE TABLE IF NOT EXISTS users (id SERIAL, name TEXT)"))
    conn.execute(text("INSERT INTO users (name) VALUES ('Alice')"))
    conn.commit()
    rows = conn.execute(text("SELECT * FROM users")).fetchall()
print(rows)   # [(1, 'Alice')]

En quelques lignes, tu as un Postgres prêt, une connexion depuis Python, une table créée et un premier enregistrement. Le passage de SQLite à Postgres dans un projet existant se résume souvent à changer la connection string dans la config.

How-to : installer et utiliser PostgreSQL

1. Lancer un Postgres en local (Docker, recommandé)

   L'image officielle postgres est la façon la plus simple — pas besoin de l'installer sur ta machine, et chaque projet peut avoir sa propre version. Avec Docker :

   bash Copier

   docker run -d --name pg \
       -e POSTGRES_USER=app \
       -e POSTGRES_PASSWORD=secret \
       -e POSTGRES_DB=appdb \
       -p 5432:5432 \
       -v pgdata:/var/lib/postgresql/data \
       postgres:16

   Le -v pgdata:/var/lib/postgresql/data est important : il monte un volume Docker pour persister les données entre redémarrages du conteneur (sinon, tu perds tout à chaque docker rm).

   docker-compose, encore mieux

   En pratique, on déclare la DB dans un docker-compose.yml aux côtés de l'API et des autres services. docker compose up et tout démarre ensemble — c'est le pattern standard d'un projet FastAPI + Postgres.

2. Se connecter avec psql

   psql est le client SQL en ligne de commande de Postgres. Pour entrer dans la DB du conteneur :

   bash Copier

   docker exec -it pg psql -U app -d appdb

   Une fois dans psql, quelques commandes utiles :

   sql Copier

   \l                      -- lister les bases
   \c appdb                -- se connecter à une base
   \dt                     -- lister les tables
   \d users                -- décrire la table users
   \du                     -- lister les utilisateurs
   \q                      -- quitter

3. Créer une base, un utilisateur, des droits

   sql Copier

   -- Connecté en tant que superuser (postgres)
   CREATE DATABASE projet_devia;
   CREATE USER devia WITH PASSWORD 'un_mdp_solide';
   GRANT ALL PRIVILEGES ON DATABASE projet_devia TO devia;
   
   -- Droits plus fins (recommandé en prod)
   \c projet_devia
   GRANT USAGE ON SCHEMA public TO devia;
   GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO devia;

4. Se connecter depuis Python

   Le pilote moderne est psycopg (v3). SQLAlchemy reste recommandé par-dessus, pour pouvoir changer de DB sans toucher au code.

   bash Copier

   uv add "sqlalchemy[postgresql]" psycopg

   python Copier

   from sqlalchemy import create_engine
   
   DATABASE_URL = "postgresql+psycopg://devia:un_mdp_solide@localhost:5432/projet_devia"
   engine = create_engine(DATABASE_URL)

   Connection string : à ne jamais commiter

   Le mot de passe ne doit jamais finir dans le code. Stocke-le dans un .env (gitignoré) et lis-le via Pydantic Settings — c'est le pattern standard.

5. Types Postgres utiles à connaître

   Postgres a des types riches au-delà du SQL standard, qu'on utilise souvent en MLOps :

   sql Copier

   CREATE TABLE predictions (
       id          SERIAL PRIMARY KEY,
       created_at  TIMESTAMPTZ DEFAULT NOW(),
       user_id     UUID NOT NULL,
       inputs      JSONB,           -- JSON indexable
       score       NUMERIC(5, 3),    -- précis (5 chiffres, 3 décimales)
       tags        TEXT[]           -- tableau de strings
   );

   JSONB est particulièrement utile pour stocker les inputs/outputs variables d'un modèle ML sans figer un schéma.

6. Backup et restore

   pg_dump exporte une base entière (schéma + données) dans un fichier SQL. psql peut le rejouer.

   bash Copier

   # Export
   docker exec pg pg_dump -U app appdb > backup.sql
   
   # Restore
   cat backup.sql | docker exec -i pg psql -U app -d appdb

7. Migrations de schéma (Alembic)

   En prod, on ne modifie jamais le schéma à la main : on utilise des migrations versionnées dans Git. Alembic (intégré à SQLAlchemy) gère ça :

   bash Copier

   uv add alembic
   uv run alembic init migrations
   uv run alembic revision --autogenerate -m "add users"
   uv run alembic upgrade head

   Chaque commande crée un fichier de migration dans migrations/versions/ — versionné dans Git, rejouable en CI/CD pour mettre à jour la prod automatiquement.

8. Optimiser : index et EXPLAIN

   Quand une requête devient lente, on regarde son plan d'exécution avec EXPLAIN ANALYZE puis on ajoute un index si besoin :

   sql Copier

   EXPLAIN ANALYZE SELECT * FROM predictions WHERE user_id = '…';
   
   -- Si l'on voit Seq Scan sur une grosse table, ajouter un index :
   CREATE INDEX idx_predictions_user_id ON predictions(user_id);

Aide-mémoire

docker run -d --name pg -e POSTGRES_PASSWORD=… -p 5432:5432 postgres:16
docker exec -it pg psql -U postgres
docker exec pg pg_dump -U app appdb > backup.sql

\l, \c db, \dt, \d table, \du, \q
\timing      -- afficher le temps d'exécution
\x           -- affichage vertical (utile pour wide rows)

postgresql+psycopg://user:password@host:port/dbname
-- Exemple local :
postgresql+psycopg://app:secret@localhost:5432/appdb

SERIAL, BIGSERIAL          -- auto-increment
UUID                       -- identifiant unique
TIMESTAMPTZ                -- date+heure avec fuseau
JSONB                      -- JSON binaire, indexable
TEXT[]                     -- tableau de strings
NUMERIC(p, s)              -- précis (financier)

PostgreSQL et le reste de l'écosystème

• SQLAlchemy — la couche Python qu'on met systématiquement entre Postgres et le code. On écrit du Python, SQLAlchemy traduit en SQL Postgres. Permet aussi de basculer SQLite ↔ Postgres en changeant juste la connection string.
• SQLite — la base de poche utilisée pendant le développement et les tests. Postgres prend le relais en prod. Avec SQLAlchemy, le code reste identique.
• Supabase — du Postgres managé avec auth et API REST par-dessus. Si tu veux les bénéfices de Postgres sans gérer le serveur, c'est l'option.
• FastAPI — pile classique : FastAPI expose une API qui parle à Postgres via SQLAlchemy. Les modèles d'entrée/sortie sont validés par Pydantic.
• Docker / Kubernetes — Postgres tourne quasi toujours dans un conteneur en dev et en prod. L'image officielle est l'une des plus utilisées au monde.
• MLflow — peut utiliser Postgres comme tracking backend à la place de SQLite (--backend-store-uri postgresql+psycopg://...). Indispensable dès que plusieurs personnes loguent en même temps.
• Prefect — utilise aussi Postgres comme base de métadonnées en self-hosted (suivi des flux, états des runs).
• Grafana — peut brancher Postgres comme source pour des dashboards SQL (à côté de Prometheus et Loki).

Pour aller plus loin

• Site officiel : postgresql.org
• Documentation officielle (référence) : postgresql.org/docs/current
• Image Docker officielle : hub.docker.com/_/postgres
• Alembic (migrations) : alembic.sqlalchemy.org
• « PostgreSQL Exercises » (tuto interactif) : pgexercises.com