PostgreSQL 45 termes

Verrous coopératifs applicatifs gères par PostgreSQL. Contrairement aux verrous de table, ils n'ont de sens que pour l'application qui les utilise. Peuvent être session-level ou transaction-level.

Un panneau 'Occupe' sur une cabine : c'est l'application qui le respecte, pas la base qui bloque.

-- Verrou au niveau session
SELECT pg_advisory_lock(42);
-- ... logique exclusive ...
SELECT pg_advisory_unlock(42);

Cas d'usage : Éviter les exécutions concurrentes de cron jobs ou de migrations sur la même ressource logique.

PostgreSQL supporte nativement les colonnes de type tableau. Permet de stocker plusieurs valeurs du même type dans une seule colonne, avec des opérateurs dedies (@>, &&, ANY).

Une cellule Excel qui peut contenir une liste de valeurs au lieu d'une seule.

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  tags TEXT[] DEFAULT '{}'
);
SELECT * FROM posts WHERE 'sql' = ANY(tags);
SELECT * FROM posts WHERE tags @> '{sql,pg}';

Cas d'usage : Stocker des tags, des permissions ou de petites listes sans créer une table de jointure.

PostgreSQL 18 (25 septembre 2025) introduit un sous-système d'entrées/sorties asynchrones contrôle par le paramètre io_method, qui accélère les scans séquentiels, les bitmap heap scans et le VACUUM en regroupant les lectures disque via io_combine_limit et io_max_combine_limit.

Comme envoyer plusieurs coursiers chercher des colis en parallèle plutôt qu'un seul aller-retour a la fois.

# postgresql.conf
io_method = 'io_uring'   # ou 'worker', 'sync'
io_combine_limit = 16
io_max_combine_limit = 32

Cas d'usage : Accélérer les gros scans séquentiels et le VACUUM sur des tables volumineuses en I/O-bound.

Type d'index par défaut dans PostgreSQL. Structure en arbre équilibre optimisée pour les comparaisons d'égalité et de plage (<, >, BETWEEN, ORDER BY). PostgreSQL 18 (25 septembre 2025) ajoute le Skip Scan : un index multicolonne peut désormais être utilise même sans condition sur ses premières colonnes, la ou seul un scan complet était possible auparavant.

Un dictionnaire avec des onglets alphabétiques : tu trouves n'importe quel mot en quelques sauts.

CREATE INDEX idx_users_email
  ON users (email);
-- Equivalent a :
CREATE INDEX idx_users_email
  ON users USING btree (email);

Cas d'usage : Optimiser les requêtes de recherche, tri et filtrage sur des colonnes fréquemment interrogées.

Block Range Index, index extrêmement compact qui stocke les min/max par bloc de pages. Idéal pour les colonnes naturellement ordonnées comme les timestamps.

Au lieu d'indexer chaque page d'un livre, noter juste 'pages 1-50 : chapitres A-C'.

CREATE INDEX idx_logs_time
  ON logs USING brin (created_at)
  WITH (pages_per_range = 128);

Cas d'usage : Indexer des tables de logs ou de séries temporelles avec des milliards de lignes et un index minuscule.

Contrainte qui vérifie qu'une condition booléenne est vraie pour chaque ligne. Peut référencer plusieurs colonnes de la même table.

Un videur qui vérifie ta carte d'identité : si tu as moins de 18 ans, tu ne rentres pas.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  price NUMERIC CHECK (price > 0),
  discount NUMERIC CHECK (discount BETWEEN 0 AND 1)
);

Cas d'usage : Garantir les règles métier au niveau de la base : prix positif, âge minimum, stock non négatif.

Depuis PostgreSQL 18 (25 septembre 2025), les colonnes générées (GENERATED ALWAYS AS) sont virtuelles par défaut : leur valeur est recalculee a la lecture plutôt que stockée sur disque a l'écriture. Le mot-clé STORED reste disponible pour retrouver l'ancien comportement quand le stockage physique est préféré.

Comme un total de facture recalcule a chaque affichage plutôt qu'imprime et fige des la création du document.

ALTER TABLE orders
  ADD COLUMN total_ttc numeric
  GENERATED ALWAYS AS (total_ht * 1.20) VIRTUAL;

-- Pour retrouver l'ancien comportement (stocke sur disque) :
-- GENERATED ALWAYS AS (total_ht * 1.20) STORED

Cas d'usage : Exposer un champ calcule (TTC, slug, total) sans payer le coût d'écriture ni le stockage disque a chaque insertion.

Index qui inclut des colonnes supplémentaires non-clé via INCLUDE. Permet un Index-Only Scan en fournissant toutes les colonnes nécessaires sans accéder a la table.

Un sommaire de livre qui inclut un résume de chaque chapitre : pas besoin de tourner les pages.

CREATE INDEX idx_orders_covering
  ON orders (user_id)
  INCLUDE (total, status);

Cas d'usage : Éliminer les accès table pour les requêtes fréquentes qui ne lisent que quelques colonnes.

Common Table Expression : sous-requête nommée définie avec WITH, utilisable comme une table temporaire dans la requête principale. Peut être recursive pour les hiérarchies.

Définir une variable intermédiaire dans un calcul complexe pour le rendre lisible étape par étape.

WITH active_users AS (
  SELECT * FROM users WHERE status = 'active'
)
SELECT a.name, COUNT(o.id) as orders
FROM active_users a
JOIN orders o ON a.id = o.user_id
GROUP BY a.name;

Cas d'usage : Décomposer des requêtes complexes en étapes lisibles, ou parcourir des structures arborescentes avec CTE recursive.

Type personnalise définissant un ensemble fixe de valeurs autorisées. Plus performant et type-safe qu'une contrainte CHECK sur un VARCHAR.

Un menu déroulant avec des options figées : tu ne peux choisir que ce qui est propose.

CREATE TYPE mood AS ENUM (
  'happy', 'sad', 'neutral'
);
CREATE TABLE entries (
  id SERIAL PRIMARY KEY,
  feeling mood NOT NULL
);

Cas d'usage : Définir des statuts, roles ou catégories avec un nombre fini de valeurs connues a l'avance.

EXPLAIN montre le plan d'exécution prévu d'une requête. EXPLAIN ANALYZE exécuté réellement la requête et montre les temps réels. Essentiel pour l'optimisation.

EXPLAIN = le GPS qui te montre l'itinéraire prévu. EXPLAIN ANALYZE = le GPS avec le temps réel du trajet.

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

Cas d'usage : Diagnostiquer les requêtes lentes, vérifier l'utilisation des index et optimiser les jointures.

Index créé sur une expression ou une fonction plutôt que sur une colonne brute. Permet d'indexer des transformations comme LOWER(), des extractions de date ou des calculs.

Un index de livre qui classe les titres sans accents ni majuscules pour une recherche simplifiée.

CREATE INDEX idx_users_lower_email
  ON users (LOWER(email));
-- La requete doit utiliser la meme expression
SELECT * FROM users
  WHERE LOWER(email) = '[email protected]';

Cas d'usage : Optimiser les recherches case-insensitive ou les filtres sur des champs transformes.

Contrainte referentielle qui garantit qu'une valeur dans une table existe dans la table référence. Supporte ON DELETE CASCADE, SET NULL et RESTRICT.

Un lien hypertexte garanti : il est impossible de pointer vers une page qui n'existe pas.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL
    REFERENCES users(id)
    ON DELETE CASCADE
);

Cas d'usage : Maintenir l'intégrité referentielle entre les tables, comme les commandes liées aux utilisateurs.

Generalized Inverted Index, optimise pour les valeurs composites comme les arrays, JSONB et le full-text search. Indexe chaque élément individuel du contenu.

L'index d'un livre de recettes : chaque ingrédient pointe vers toutes les recettes qui l'utilisent.

CREATE INDEX idx_posts_tags
  ON posts USING gin (tags);
CREATE INDEX idx_events_data
  ON events USING gin (data jsonb_path_ops);

Cas d'usage : Accélérer les recherches dans des colonnes JSONB, des arrays ou du texte avec tsvector.

Generalized Search Tree, index polyvalent pour les données géométriques, les ranges et le full-text. Supporte les requêtes de proximité, d'inclusion et de chevauchement.

Un plan de quartier qui sait répondre a 'quels bâtiments sont dans ce périmètre ?'.

CREATE INDEX idx_locations
  ON places USING gist (location);
-- Avec PostGIS
SELECT * FROM places
  WHERE ST_DWithin(location, point, 1000);

Cas d'usage : Requêtes geospatiales avec PostGIS ou recherche dans des plages de valeurs (ranges).

Depuis PostgreSQL 17 (26 septembre 2024), pg_basebackup accepte l'option --incremental pour ne sauvegarder que les blocs modifies depuis une sauvegarde de référence, grâce a des fichiers de résume WAL (summarize_wal). pg_combinebackup reconstitue ensuite une sauvegarde complète a partir de la chaîne incrementale.

Comme ne photocopier que les pages modifiées d'un classeur plutôt que tout le classeur a chaque sauvegarde.

# Sauvegarde complete de reference
pg_basebackup -D /backup/full -Ft -X none

# Sauvegarde incrementale basee sur le manifest precedent
pg_basebackup -D /backup/incr1 \
  --incremental=/backup/full/backup_manifest

# Reconstitution complete
pg_combinebackup /backup/full /backup/incr1 -o /backup/restored

Cas d'usage : Réduire le temps et l'espace disque des sauvegardes quotidiennes sur une base volumineuse a faible taux de modification.

Opérations de jointure entre tables. INNER retourne les correspondances. LEFT garde toutes les lignes gauches. RIGHT garde toutes les lignes droites. FULL garde tout.

INNER = invites présents aux deux fêtes. LEFT = tous les invites de la fête A, avec ceux aussi a la fête B. FULL = tous les invites des deux fêtes.

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;

Cas d'usage : Combiner des données de plusieurs tables pour des rapports, des APIs ou des dashboards.

Fonction SQL/JSON introduite dans PostgreSQL 17 (26 septembre 2024) qui transforme un document JSON en vue relationnelle exploitable dans une clause FROM. La clause COLUMNS définit le mapping de chaque chemin JSON vers une colonne typée, avec NESTED PATH pour extraire des niveaux imbriques.

Comme déplier une feuille pliée en accordéon (JSON imbrique) pour en faire un tableau plat lisible ligne par ligne.

SELECT jt.* FROM my_films,
  JSON_TABLE(js, '$.favorites[*]' COLUMNS (
    id FOR ORDINALITY,
    kind text PATH '$.kind',
    title text PATH '$.films[*].title' WITH WRAPPER
  )) AS jt;

Cas d'usage : Interroger un champ JSONB avec la même syntaxe qu'une table SQL classique, sans fonctions jsonb_* imbriquées.

Type de données binaire JSON dans PostgreSQL. Contrairement a JSON (texte brut), JSONB est parse et stocke en binaire, permettant l'indexation et les requêtes performantes.

JSON compile : tu perds la mise en forme originale mais tu gagnes la vitesse de recherche d'un index.

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL
);
SELECT * FROM events
  WHERE data->>'type' = 'click'
  AND (data->'meta'->>'count')::int > 5;

Cas d'usage : Stocker des données semi-structurées (logs, config, metadonnees) dans une base relationnelle.

JOIN ou la sous-requête droite peut référencer des colonnes de la table gauche. Permet des sous-requêtes corrélées dans le FROM, comme un forEach SQL.

Pour chaque client, ouvrir son dossier et y chercher ses 3 dernières commandes.

SELECT u.name, recent.title
FROM users u
LATERAL (
  SELECT title FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) recent;

Cas d'usage : Récupérer les N derniers éléments par groupe sans window function complexe.

Système de pub/sub intégré a PostgreSQL. LISTEN s'abonne a un canal, NOTIFY envoie un message. Permet la communication asynchrone entre processus sans polling.

Un talkie-walkie intégré a la base de données : un service parle, les autres écoûtent sur le même canal.

-- Session 1
LISTEN order_created;
-- Session 2
NOTIFY order_created, '{"id": 42}';
-- Session 1 recoit la notification

Cas d'usage : Notifier un service en temps réel quand une commande est créée, sans message broker externe.

Depuis PostgreSQL 17 (26 septembre 2024), les slots de réplication logique peuvent être synchronises vers un standby physique via sync_réplication_slots, et l'utilitaire pg_createsubscriber créé un abonne logique a partir d'un standby physique. pg_upgrade préserve désormais aussi les slots et abonnements logiques existants.

Avant, basculer sur un standby faisait perdre la place ou en étaient les abonnes logiques, comme perdre le signet d'un livre au changement de bibliothécaire. Désormais le signet suit le changement.

# Sur le primaire
psql -c "ALTER SYSTEM SET sync_replication_slots = on;"

# Creer un abonne logique a partir d'un standby physique
pg_createsubscriber --pgdata=/var/lib/postgresql/standby \
  --publication=pub1 --subscription=sub1 \
  --database=app --socket-directory=/tmp

Cas d'usage : Basculer (failover) un primaire sans casser les abonnements de réplication logique en aval.

Commande introduite dans PostgreSQL 15 (2022) qui combine INSERT, UPDATE et DELETE en une seule instruction conditionnelle via des clauses WHEN MATCHED / WHEN NOT MATCHED. PostgreSQL 17 (26 septembre 2024) y ajoute WHEN NOT MATCHED BY SOURCE et le support de RETURNING avec la fonction merge_action().

Comme trier un stock en une seule passe : ce qui existe déjà est mis a jour, ce qui est nouveau est ajoute, ce qui a disparu est retire.

MERGE INTO stock s
USING deliveries d ON d.sku = s.sku
WHEN MATCHED THEN
  UPDATE SET qty = s.qty + d.qty
WHEN NOT MATCHED THEN
  INSERT (sku, qty) VALUES (d.sku, d.qty)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
RETURNING merge_action(), s.sku;

Cas d'usage : Synchroniser une table cible avec une source (upsert + suppression) en une seule requête atomique.

Multi-Version Concurrency Control : mécanisme ou chaque transaction voit un snapshot cohérent de la base. Les lectures ne bloquent jamais les écritures et vice-versa.

Chaque client du restaurant reçoit sa propre copie du menu, même si le chef met a jour les plats en cuisine.

-- Transaction A lit des donnees
BEGIN;
SELECT * FROM accounts WHERE id = 1;
-- Transaction B modifie en parallele
-- A ne voit pas les changements de B
COMMIT;

Cas d'usage : Comprendre pourquoi PostgreSQL gère si bien la concurrence sans verrouillage pessimiste.

Index créé avec une clause WHERE qui n'indexe qu'un sous-ensemble des lignes de la table. Plus petit et plus rapide qu'un index complet.

Un annuaire qui ne liste que les médecins de garde, pas tous les médecins de la ville.

CREATE INDEX idx_active_users
  ON users (email)
  WHERE status = 'active';

Cas d'usage : Optimiser les requêtes qui filtrent toujours sur la même condition, comme les utilisateurs actifs.

PostgreSQL découpé une table logique en plusieurs tables physiques (partitions) selon une stratégie RANGE, LIST ou HASH déclarée via PARTITION BY. Chaque partition se crée avec CREATE TABLE ... PARTITION OF, et le partition pruning éliminé automatiquement les partitions non concernées par une requête.

Comme classer des archives papier par année dans des cartons séparés plutôt que dans un seul immense classeur : on ne sort que le carton de l'année recherchée.

CREATE TABLE measurement (
  city_id int, logdate date, peaktemp int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2026m01 PARTITION OF measurement
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

Cas d'usage : Archiver ou purger de très grandes tables temporelles (logs, mesures) en droppant une partition entière en une opération instantanée.

pg_dump exporte une base en SQL ou en format custom. pg_restore réimporté un dump au format custom. Ensemble, ils permettent les backups et les migrations.

pg_dump = photocopier tous les dossiers du bureau. pg_restore = reclasser les photocopies dans un nouveau bureau.

pg_dump -Fc -U postgres mydb > backup.dump
pg_restore -U postgres -d newdb backup.dump

Cas d'usage : Sauvegarder une base avant une migration, cloner un environnement de production en staging.

Extension qui collecte les statistiques d'exécution de toutes les requêtes SQL : nombre d'appels, temps moyen, lignes retournées. Essentielle pour le monitoring en prod.

Un compteur de performances pour chaque requête, comme un tableau de bord de Formule 1.

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Cas d'usage : Identifier les requêtes les plus lentes ou les plus fréquentes pour optimiser la base en production.

Extension qui fournit la recherche par similarité basée sur les trigrammes. Permet la recherche floue (fuzzy matching) et le LIKE/ILIKE rapide grâce aux index GIN ou GiST.

Un correcteur orthographique intégré a ta base de données : il trouve 'PostgreSQL' même si tu tapes 'Postgre'.

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_trgm ON users
  USING gin (name gin_trgm_ops);
SELECT * FROM users
  WHERE name % 'Alic'
  ORDER BY similarity(name, 'Alic') DESC;

Cas d'usage : Implémenter une barre de recherche tolérante aux fautes de frappe sans Elasticsearch.

Extension fournissant des fonctions de chiffrement : gen_random_uuid(), crypt() pour le hashing de mots de passe, pgp_sym_encrypt/decrypt pour le chiffrement symétrique.

Un coffre-fort intégré directement dans la base de données.

CREATE EXTENSION pgcrypto;
-- UUID
SELECT gen_random_uuid();
-- Hash password
SELECT crypt('password', gen_salt('bf'));

Cas d'usage : Générer des UUIDs ou hasher des mots de passe directement dans la base sans code applicatif.

pgvector (version 0.8.6) est l'extension PostgreSQL de référence pour stocker des vecteurs (type vector, jusqu'a 16 000 dimensions) et effectuer des recherches par similarité. Son index HNSW (Hierarchical Navigable Small World) offre de meilleures performances de requête que l'index IVFFlat, au prix d'un temps de construction plus long.

HNSW construit un réseau de raccourcis a plusieurs échelles, comme un plan d'autoroutes puis de routes locales, pour rejoindre le point le plus proche plus vite qu'en parcourant la carte a plat (IVFFlat).

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE items (
  id bigserial PRIMARY KEY,
  embedding vector(768)
);
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

SELECT id FROM items
  ORDER BY embedding <=> '[0.1, 0.2]' LIMIT 5;

Cas d'usage : Recherche de similarité sémantique (RAG, recommandation) directement en SQL, sans base vectorielle dédiée séparée.

Extension qui ajoute le support des données geospatiales a PostgreSQL. Fournit des types (geometry, geography), des fonctions (ST_Distance, ST_Contains) et des index spatiaux.

Transformer PostgreSQL en GPS intelligent capable de calculer des distances et des zones sur une carte.

CREATE EXTENSION postgis;
SELECT name FROM restaurants
WHERE ST_DWithin(
  location,
  ST_MakePoint(2.35, 48.86)::geography,
  1000
);

Cas d'usage : Applications de cartographie, calcul de zones de livraison ou analyse geospatiale.

Contrainte qui garantit l'unicité et la non-nullité d'une ou plusieurs colonnes. Créé automatiquement un index B-tree unique. Chaque table ne peut avoir qu'une seule PK.

Le numéro de sécurité sociale d'une ligne : unique, obligatoire et jamais vide.

CREATE TABLE users (
  id SERIAL PRIMARY KEY
);
-- Composite
CREATE TABLE order_items (
  order_id INT, product_id INT,
  PRIMARY KEY (order_id, product_id)
);

Cas d'usage : Identifier de manière unique chaque ligne d'une table.

Client en ligne de commande officiel de PostgreSQL. Permet d'exécuter des requêtes SQL, d'administrer la base et d'utiliser des meta-commandes comme \dt, \d, \l.

Le terminal de commande de PostgreSQL, comme mongosh pour MongoDB.

psql -U postgres -d mydb
\dt          -- lister les tables
\d users     -- decrire une table
\conninfo    -- infos de connexion

Cas d'usage : Administrer et debugger une base PostgreSQL en ligne de commande.

Mécanisme qui restreint, ligne par ligne, les données visibles ou modifiables selon le role connecte, en complément des privilèges SQL classiques. Une fois ENABLE ROW LEVEL SECURITY active sur une table, aucune ligne n'est accessible tant qu'aucune CREATE POLICY explicite n'existe.

Comme donner a chaque locataire d'un immeuble une clé qui n'ouvre que sa propre porte, même s'ils partagent le même bâtiment (la même table).

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON accounts
  USING (
    tenant_id = current_setting('app.current_tenant_id')::int
  )
  WITH CHECK (
    tenant_id = current_setting('app.current_tenant_id')::int
  );

Cas d'usage : Isoler les données de plusieurs clients (multi-tenant) dans une seule table partagée sans dupliquer la base.

SERIAL est un entier auto-incrémenté (1, 2, 3...). UUID est un identifiant unique universel de 128 bits, généralement génère aléatoirement (UUIDv4). PostgreSQL 18 (25 septembre 2025) ajoute la fonction native uuidv7(), qui encode un timestamp dans les premiers bits pour rester triable chronologiquement et limiter la fragmentation d'index que provoque UUIDv4.

SERIAL = numéro de ticket dans une file d'attente. UUID = numéro de passeport, unique dans le monde entier.

CREATE TABLE orders (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  seq SERIAL
);

Cas d'usage : UUID pour les systèmes distribues ou la sécurité. SERIAL pour la simplicité et la lisibilité.

Blocs de code PL/pgSQL stockes et exécutés cote serveur. Les fonctions retournent une valeur, les procédures (depuis PG 11) supportent les transactions internes.

Des macros Excel stockées dans le serveur : un appel déclenché toute la logique sans transfert de données.

CREATE FUNCTION get_user_orders(uid INT)
RETURNS TABLE (id INT, total NUMERIC) AS $$
BEGIN
  RETURN QUERY
    SELECT o.id, o.total
    FROM orders o WHERE o.user_id = uid;
END;
$$ LANGUAGE plpgsql;

Cas d'usage : Encapsuler de la logique métier complexe cote serveur pour réduire les allers-retours réseau.

Requête imbriquée dans une autre requête, utilisable dans SELECT, FROM, WHERE ou HAVING. Peut être corrélée (référence la requête externe) ou non-corrélée (indépendante).

Une question dans une question : 'Qui gagne plus que la moyenne des salaires ?'

SELECT name FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees
);
-- Ou dans FROM
SELECT * FROM (
  SELECT name, COUNT(*) as cnt
  FROM orders GROUP BY name
) sub WHERE cnt > 5;

Cas d'usage : Filtrer sur un résultat agrégé ou créer des tables dérivées pour des requêtes complexes.

Structure de données relationnelle composée de lignes et colonnes avec un schema fixe. Chaque colonne a un type défini et chaque ligne représente un enregistrement.

Un tableau Excel avec des colonnes figées : impossible d'ajouter une cellule d'un type différent.

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

Cas d'usage : Stocker des données structurées avec des relations fortes et des contraintes d'intégrité.

Fonctions exécutées automatiquement en réponse a un evenement (INSERT, UPDATE, DELETE) sur une table. Peuvent être BEFORE ou AFTER et opèrent par ligne (FOR EACH ROW) ou par instruction.

Un détecteur de mouvement qui déclenche automatiquement une alarme quand quelqu'un entre.

CREATE FUNCTION update_timestamp()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER set_timestamp
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION update_timestamp();

Cas d'usage : Mettre a jour automatiquement un timestamp, maintenir un audit log ou propager des modifications.

Fonction native ajoutée en PostgreSQL 18 (25 septembre 2025) qui génère des UUID version 7 : contrairement a UUIDv4 (aléatoire), les premiers bits d'un UUIDv7 encodent un timestamp, ce qui les rend triables chronologiquement et limite la fragmentation des index B-tree observée avec UUIDv4.

Comme numéroter des tickets dans l'ordre d'arrivée (uuidv7) plutôt que de tirer un numéro au hasard dans un sac (uuidv4) : le tri redevient naturel.

CREATE TABLE events (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  payload jsonb
);
-- Les id inseres restent ordonnes
-- chronologiquement dans l'index

Cas d'usage : Remplacer un UUIDv4 comme clé primaire quand l'ordre d'insertion doit rester exploitable, sans revenir a un SERIAL prévisible.

VACUUM récupéré l'espace des lignes mortes (MVCC). ANALYZE met a jour les statistiques du planificateur de requêtes. VACUUM ANALYZE fait les deux. Autovacuum les exécute automatiquement. PostgreSQL 17 (26 septembre 2024) réduit significativement la consommation mémoire de VACUUM grâce a un nouveau système de gestion mémoire dédiée.

VACUUM = le ramasse-miettes qui recycle les pages usées. ANALYZE = le recensement qui met a jour les cartes du quartier.

VACUUM ANALYZE users;
-- Verifier autovacuum
SELECT relname, last_vacuum, last_analyze
FROM pg_stat_user_tables;

Cas d'usage : Maintenir les performances de la base en récupérant l'espace et en mettant a jour les statistiques.

Une View est une requête nommée (virtuelle, recalculee a chaque appel). Une Materialized View stocke physiquement le résultat et doit être rafraîchie manuellement.

View = regarder par la fenêtre en temps réel. Materialized View = une photo de la vue, rapide a consulter mais a actualiser.

CREATE MATERIALIZED VIEW monthly_sales AS
  SELECT date_trunc('month', created_at) as m,
    SUM(total) as revenue
  FROM orders GROUP BY m;
REFRESH MATERIALIZED VIEW CONCURRENTLY
  monthly_sales;

Cas d'usage : Materialized views pour des dashboards lourds a calculer, Views pour simplifier des requêtes complexes.

Journal de pre-écriture ou chaque modification est enregistrée avant d'être appliquée aux fichiers de données. Garantit la durabilite et permet la réplication et le point-in-time recovery.

Le brouillon du comptable : chaque opération est notée dans le journal avant d'être reportée dans le grand livre.

-- Verifier la taille du WAL
SELECT pg_size_pretty(
  pg_wal_lsn_diff(
    pg_current_wal_lsn(),
    '0/0'
  )
);

Cas d'usage : Comprendre la durabilite des données, configurer la réplication streaming ou le PITR.

Fonctions qui opèrent sur un ensemble de lignes (fenêtre) lie a la ligne courante sans réduire le nombre de résultats. Incluent ROW_NUMBER, RANK, LAG, LEAD, SUM OVER, etc.

Un coureur qui voit son classement en temps réel pendant la course, sans que la course s'arrête.

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) as rank,
  salary - LAG(salary) OVER (ORDER BY salary DESC)
    as diff_with_prev
FROM employees;

Cas d'usage : Calculer des classements, des moyennes mobiles ou comparer chaque ligne avec la précédenté/suivante.