Verrous cooperatifs applicatifs geres par PostgreSQL. Contrairement aux verrous de table, ils n'ont de sens que pour l'application qui les utilise. Peuvent etre 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 : Eviter les executions concurrentes de cron jobs ou de migrations sur la meme ressource logique.
PostgreSQL supporte nativement les colonnes de type tableau. Permet de stocker plusieurs valeurs du meme type dans une seule colonne, avec des operateurs 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 creer une table de jointure.
Type d'index par defaut dans PostgreSQL. Structure en arbre equilibre optimisee pour les comparaisons d'egalite et de plage (<, >, BETWEEN, ORDER BY).
Un dictionnaire avec des onglets alphabetiques : 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 requetes de recherche, tri et filtrage sur des colonnes frequemment interrogees.
Block Range Index, index extremement compact qui stocke les min/max par bloc de pages. Ideal pour les colonnes naturellement ordonnees 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 series temporelles avec des milliards de lignes et un index minuscule.
Contrainte qui verifie qu'une condition booleenne est vraie pour chaque ligne. Peut referencer plusieurs colonnes de la meme table.
Un videur qui verifie ta carte d'identite : 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 regles metier au niveau de la base : prix positif, age minimum, stock non negatif.
Index qui inclut des colonnes supplementaires non-cle via INCLUDE. Permet un Index-Only Scan en fournissant toutes les colonnes necessaires sans acceder a la table.
Un sommaire de livre qui inclut un resume de chaque chapitre : pas besoin de tourner les pages.
CREATE INDEX idx_orders_covering
ON orders (user_id)
INCLUDE (total, status);Cas d'usage : Eliminer les acces table pour les requetes frequentes qui ne lisent que quelques colonnes.
Common Table Expression : sous-requete nommee definie avec WITH, utilisable comme une table temporaire dans la requete principale. Peut etre recursive pour les hierarchies.
Definir une variable intermediaire dans un calcul complexe pour le rendre lisible etape par etape.
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 : Decomposer des requetes complexes en etapes lisibles, ou parcourir des structures arborescentes avec CTE recursive.
Type personnalise definissant un ensemble fixe de valeurs autorisees. Plus performant et type-safe qu'une contrainte CHECK sur un VARCHAR.
Un menu deroulant avec des options figees : 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 : Definir des statuts, roles ou categories avec un nombre fini de valeurs connues a l'avance.
EXPLAIN montre le plan d'execution prevu d'une requete. EXPLAIN ANALYZE execute reellement la requete et montre les temps reels. Essentiel pour l'optimisation.
EXPLAIN = le GPS qui te montre l'itineraire prevu. EXPLAIN ANALYZE = le GPS avec le temps reel 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 requetes lentes, verifier l'utilisation des index et optimiser les jointures.
Index cree sur une expression ou une fonction plutot 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 simplifiee.
CREATE INDEX idx_users_lower_email
ON users (LOWER(email));
-- La requete doit utiliser la meme expression
SELECT * FROM users
WHERE LOWER(email) = 'alice@mail.com';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 reference. 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'integrite referentielle entre les tables, comme les commandes liees aux utilisateurs.
Generalized Inverted Index, optimise pour les valeurs composites comme les arrays, JSONB et le full-text search. Indexe chaque element individuel du contenu.
L'index d'un livre de recettes : chaque ingredient 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 : Accelerer les recherches dans des colonnes JSONB, des arrays ou du texte avec tsvector.
Generalized Search Tree, index polyvalent pour les donnees geometriques, les ranges et le full-text. Supporte les requetes de proximite, d'inclusion et de chevauchement.
Un plan de quartier qui sait repondre a 'quels batiments sont dans ce perimetre ?'.
CREATE INDEX idx_locations
ON places USING gist (location);
-- Avec PostGIS
SELECT * FROM places
WHERE ST_DWithin(location, point, 1000);Cas d'usage : Requetes geospatiales avec PostGIS ou recherche dans des plages de valeurs (ranges).
Operations 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 presents aux deux fetes. LEFT = tous les invites de la fete A, avec ceux aussi a la fete B. FULL = tous les invites des deux fetes.
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 donnees de plusieurs tables pour des rapports, des APIs ou des dashboards.
Type de donnees binaire JSON dans PostgreSQL. Contrairement a JSON (texte brut), JSONB est parse et stocke en binaire, permettant l'indexation et les requetes 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 donnees semi-structurees (logs, config, metadonnees) dans une base relationnelle.
JOIN ou la sous-requete droite peut referencer des colonnes de la table gauche. Permet des sous-requetes correlees dans le FROM, comme un forEach SQL.
Pour chaque client, ouvrir son dossier et y chercher ses 3 dernieres 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 : Recuperer les N derniers elements par groupe sans window function complexe.
Systeme de pub/sub integre a PostgreSQL. LISTEN s'abonne a un canal, NOTIFY envoie un message. Permet la communication asynchrone entre processus sans polling.
Un talkie-walkie integre a la base de donnees : un service parle, les autres ecoutent sur le meme canal.
-- Session 1
LISTEN order_created;
-- Session 2
NOTIFY order_created, '{"id": 42}';
-- Session 1 recoit la notificationCas d'usage : Notifier un service en temps reel quand une commande est creee, sans message broker externe.
Multi-Version Concurrency Control : mecanisme ou chaque transaction voit un snapshot coherent de la base. Les lectures ne bloquent jamais les ecritures et vice-versa.
Chaque client du restaurant recoit sa propre copie du menu, meme 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 gere si bien la concurrence sans verrouillage pessimiste.
Index cree 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 medecins de garde, pas tous les medecins de la ville.
CREATE INDEX idx_active_users
ON users (email)
WHERE status = 'active';Cas d'usage : Optimiser les requetes qui filtrent toujours sur la meme condition, comme les utilisateurs actifs.
pg_dump exporte une base en SQL ou en format custom. pg_restore reimporte 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.dumpCas d'usage : Sauvegarder une base avant une migration, cloner un environnement de production en staging.
Extension qui collecte les statistiques d'execution de toutes les requetes SQL : nombre d'appels, temps moyen, lignes retournees. Essentielle pour le monitoring en prod.
Un compteur de performances pour chaque requete, 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 requetes les plus lentes ou les plus frequentes pour optimiser la base en production.
Extension qui fournit la recherche par similarite basee sur les trigrammes. Permet la recherche floue (fuzzy matching) et le LIKE/ILIKE rapide grace aux index GIN ou GiST.
Un correcteur orthographique integre a ta base de donnees : il trouve 'PostgreSQL' meme 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 : Implementer une barre de recherche tolerante 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 symetrique.
Un coffre-fort integre directement dans la base de donnees.
CREATE EXTENSION pgcrypto;
-- UUID
SELECT gen_random_uuid();
-- Hash password
SELECT crypt('password', gen_salt('bf'));Cas d'usage : Generer des UUIDs ou hasher des mots de passe directement dans la base sans code applicatif.
Extension qui ajoute le support des donnees 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'unicite et la non-nullite d'une ou plusieurs colonnes. Cree automatiquement un index B-tree unique. Chaque table ne peut avoir qu'une seule PK.
Le numero de securite 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 maniere unique chaque ligne d'une table.
Client en ligne de commande officiel de PostgreSQL. Permet d'executer des requetes 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 connexionCas d'usage : Administrer et debugger une base PostgreSQL en ligne de commande.
SERIAL est un entier auto-incremente (1, 2, 3...). UUID est un identifiant unique universel de 128 bits. SERIAL est simple mais previsible, UUID est unique mondialement.
SERIAL = numero de ticket dans une file d'attente. UUID = numero 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 systemes distribues ou la securite. SERIAL pour la simplicite et la lisibilite.
Blocs de code PL/pgSQL stockes et executes cote serveur. Les fonctions retournent une valeur, les procedures (depuis PG 11) supportent les transactions internes.
Des macros Excel stockees dans le serveur : un appel declenche toute la logique sans transfert de donnees.
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 metier complexe cote serveur pour reduire les allers-retours reseau.
Requete imbriquee dans une autre requete, utilisable dans SELECT, FROM, WHERE ou HAVING. Peut etre correlee (reference la requete externe) ou non-correlee (independante).
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 resultat agrege ou creer des tables derivees pour des requetes complexes.
Structure de donnees relationnelle composee de lignes et colonnes avec un schema fixe. Chaque colonne a un type defini et chaque ligne represente un enregistrement.
Un tableau Excel avec des colonnes figees : impossible d'ajouter une cellule d'un type different.
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 donnees structurees avec des relations fortes et des contraintes d'integrite.
Fonctions executees automatiquement en reponse a un evenement (INSERT, UPDATE, DELETE) sur une table. Peuvent etre BEFORE ou AFTER et operent par ligne (FOR EACH ROW) ou par instruction.
Un detecteur de mouvement qui declenche 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.
VACUUM recupere l'espace des lignes mortes (MVCC). ANALYZE met a jour les statistiques du planificateur de requetes. VACUUM ANALYZE fait les deux. Autovacuum les execute automatiquement.
VACUUM = le ramasse-miettes qui recycle les pages usees. 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 recuperant l'espace et en mettant a jour les statistiques.
Une View est une requete nommee (virtuelle, recalculee a chaque appel). Une Materialized View stocke physiquement le resultat et doit etre rafraichie manuellement.
View = regarder par la fenetre en temps reel. 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 requetes complexes.
Journal de pre-ecriture ou chaque modification est enregistree avant d'etre appliquee aux fichiers de donnees. Garantit la durabilite et permet la replication et le point-in-time recovery.
Le brouillon du comptable : chaque operation est notee dans le journal avant d'etre reportee 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 donnees, configurer la replication streaming ou le PITR.
Fonctions qui operent sur un ensemble de lignes (fenetre) lie a la ligne courante sans reduire le nombre de resultats. Incluent ROW_NUMBER, RANK, LAG, LEAD, SUM OVER, etc.
Un coureur qui voit son classement en temps reel pendant la course, sans que la course s'arrete.
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 precedente/suivante.
Autres stacks