Skip to content

PostgreSQL Aide-mémoire

Système de base de données relationnelle open-source puissant, aux fonctionnalités avancées, extensible et conforme à SQL.

01

Premiers pas

Connexion avec psql

psql est le client en ligne de commande de PostgreSQL. Les méta-commandes commencent par un antislash (\). Utilisez \l pour lister les bases, \d pour décrire les tables et \? pour l'aide. Les chaînes de connexion (postgresql://) sont pratiques et supportées par la plupart des drivers. Utilisez \i fichier.sql pour exécuter un fichier SQL. Réglez la variable d'env PGPASSWORD ou le fichier ~/.pgpass pour éviter de taper les mots de passe.

postgresql
# connect to a database
psql -h localhost -p 5432 -U postgres -d mydb
# -h host, -p port, -U user, -d database

# connect via connection string
psql "postgresql://user:pass@localhost:5432/mydb"

# common psql meta-commands
\l              # list databases
\c mydb         # connect to mydb
\dt             # list tables
\d users        # describe table
\dn             # list schemas
\df             # list functions
\du             # list roles/users
\q              # quit
\?              # help on meta-commands
\timing         # toggle query timing

Créer et supprimer des bases

CREATE DATABASE crée une nouvelle base ; on peut préciser owner, encoding, locale et template. template0 est un template propre sans données propres à la locale ; template1 (défaut) peut être personnalisé. DROP DATABASE exige l'absence de connexions actives — WITH (FORCE) (13+) les déconnecte d'abord. Impossible de supprimer la base à laquelle vous êtes connecté. ALTER DATABASE RENAME met à jour le nom mais pas le répertoire sur disque.

postgresql
# create a database
CREATE DATABASE mydb
  WITH OWNER alice
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8'
  TEMPLATE template0
  CONNECTION LIMIT 100;

# create from a template (clone)
CREATE DATABASE mydb_copy TEMPLATE mydb;

# drop a database (must disconnect first)
DROP DATABASE IF EXISTS mydb;

# drop with force (PostgreSQL 13+)
DROP DATABASE IF EXISTS mydb WITH (FORCE);

# rename a database
ALTER DATABASE mydb RENAME TO newdb;

Schémas et search_path

Les schémas sont des namespaces pour les objets de base — comme des dossiers pour les tables. Le schéma par défaut est 'public'. search_path détermine quels schémas sont parcourus lors du référencement de noms non qualifiés. Utilisez les schémas pour organiser les apps multi-tenant, séparer les préoccupations ou gérer les versions. CASCADE supprime les objets dépendants ; RESTRICT (défaut) refuse s'il en existe. Les schémas sont plus flexibles que les bases pour le multi-tenant sur une même connexion.

postgresql
# create a schema
CREATE SCHEMA IF NOT EXISTS app_schema AUTHORIZATION alice;

# set the search path (schema lookup order)
SET search_path TO app_schema, public;
SHOW search_path;

# create a table in a specific schema
CREATE TABLE app_schema.users (id serial PRIMARY KEY);

# move a table between schemas
ALTER TABLE public.users SET SCHEMA app_schema;

# list objects in a schema
SELECT * FROM information_schema.tables
WHERE table_schema = 'app_schema';

# drop a schema (and its objects)
DROP SCHEMA IF EXISTS app_schema CASCADE;

Configuration et réglages

Les réglages PostgreSQL vivent dans postgresql.conf. SHOW lit la valeur courante ; SET la change pour la session ; SET LOCAL pour la transaction courante seulement ; ALTER DATABASE/ROLE fixe des défauts persistants. Certains réglages (shared_buffers, max_connections) exigent un redémarrage du serveur ; d'autres (work_mem, statement_timeout) s'appliquent immédiatement. Utilisez pg_reload_conf() pour appliquer les changements du fichier de config sans redémarrage. Surveillez log_min_duration_statement pour les requêtes lentes.

postgresql
# view a setting
SHOW shared_buffers;
SHOW max_connections;

# set a parameter for the session
SET work_mem = '64MB';
SET statement_timeout = '30s';

# set a parameter for a transaction
BEGIN;
SET LOCAL work_mem = '256MB';
-- heavy sort here
COMMIT;  -- reverts to previous value

# set at database or role level (persists)
ALTER DATABASE mydb SET log_min_duration_statement = 100;
ALTER ROLE alice SET search_path TO app_schema, public;

# reload config without restart
SELECT pg_reload_conf();

# view config file location
SHOW config_file;

Scripting psql et variables

psql supporte le scripting avec variables (\set), inclusion de fichiers (\i), redirection de sortie (\o) et exécution shell (\!). Les variables se substituent avec :varname. Utilisez \set pour des boucles et conditionnelles de scripting. Le flag -f exécute un fichier et quitte, utile pour les traitements en lot et migrations. Pour du scripting complexe, considérez des fonctions PL/pgSQL ou des outils externes comme sqitch ou Flyway pour la gestion des migrations.

postgresql
# run a SQL file
psql -d mydb -f script.sql
psql -d mydb < script.sql

# inside psql
\i /path/to/script.sql

# use psql variables
\set table_name 'users'
SELECT * FROM :table_name;

# prompt for input
\echo -n 'Enter user ID: ' \set user_id `read x && echo $x`
SELECT * FROM users WHERE id = :user_id;

# output to file
\o results.txt
SELECT * FROM users;
\o

# execute shell command
\! date
02

Tables et contraintes

Créer des tables

serial crée automatiquement une séquence pour les entiers auto-incrémentés ; utilisez plutôt GENERATED ALWAYS AS IDENTITY (standard SQL, préféré depuis PG10) pour les nouvelles tables. Contraintes : PRIMARY KEY (unique + not null), UNIQUE, NOT NULL, CHECK, REFERENCES (clé étrangère). ON DELETE CASCADE supprime les lignes dépendantes quand le parent est supprimé ; SET NULL met la FK à NULL. Les tables TEMP disparaissent après la session — parfaites pour des résultats intermédiaires.

postgresql
CREATE TABLE users (
  id          serial PRIMARY KEY,
  username    varchar(50) UNIQUE NOT NULL,
  email       varchar(255) UNIQUE NOT NULL,
  password    text NOT NULL,
  age         integer CHECK (age >= 0 AND age <= 150),
  role        varchar(20) NOT NULL DEFAULT 'user',
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

# create with a foreign key
CREATE TABLE posts (
  id          serial PRIMARY KEY,
  user_id     integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title       text NOT NULL,
  body        text,
  published   boolean DEFAULT false
);

# create a temporary table (session-scoped)
CREATE TEMP TABLE temp_stats AS
  SELECT user_id, count(*) FROM posts GROUP BY user_id;

Colonnes identity (PG10+)

GENERATED ALWAYS AS IDENTITY est le remplaçant standard SQL de serial — il lie la séquence à la colonne, donc supprimer la colonne supprime la séquence (serial laisse des séquences orphelines). ALWAYS empêche les insertions manuelles de l'ID (utilisez OVERRIDING SYSTEM VALUE pour forcer). BY DEFAULT se comporte comme serial (autorise l'insertion manuelle). Utilisez les colonnes identity pour les nouvelles tables ; serial convient aux existantes. Les deux utilisent des séquences sous le capot.

postgresql
# preferred over serial (SQL standard)
CREATE TABLE products (
  id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

# allow manual override (like serial behavior)
CREATE TABLE products (
  id    integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

# restart a sequence
ALTER TABLE products ALTER COLUMN id RESTART WITH 1000;

# view identity info
SELECT * FROM pg_sequences WHERE sequencename = 'products_id_seq';

Modifier des tables (ALTER)

ALTER TABLE fait évoluer le schéma sans recréer les tables. Ajouter une colonne avec un défaut (non volatile) est rapide depuis PG11 (pas de réécriture complète). Changer le type d'une colonne peut exiger une réécriture complète et une clause USING pour la conversion. CASCADE supprime les objets dépendants (vues, clés étrangères) — avec prudence. Ajoutez des contraintes avec noms pour une gestion future plus simple. Utilisez IF EXISTS/IF NOT EXISTS pour rendre les scripts idempotents.

postgresql
# add a column
ALTER TABLE users ADD COLUMN bio text;
ALTER TABLE users ADD COLUMN score numeric DEFAULT 0;

# drop a column
ALTER TABLE users DROP COLUMN IF EXISTS bio;
ALTER TABLE users DROP COLUMN bio CASCADE;  -- drop dependent objects

# rename a column
ALTER TABLE users RENAME COLUMN username TO handle;

# change column type
ALTER TABLE users ALTER COLUMN age TYPE smallint USING age::smallint;

# set/drop default
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'member';
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;

# add constraint
ALTER TABLE users ADD CONSTRAINT email_check
  CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$');

Contraintes

Les contraintes garantissent l'intégrité des données au niveau base — préférez-les toujours aux vérifications applicatives. Les clés composées conviennent aux tables de jonction. ON UPDATE CASCADE propage les changements de PK vers les FK ; ON DELETE SET NULL annule la FK (la colonne FK doit être nullable). Les contraintes DEFERRABLE vérifient au commit (pas par instruction), utiles pour les références circulaires. Nommez vos contraintes pour une gestion facile — les noms auto-générés sont laids et varient selon les bases.

postgresql
# named constraint for easy management
ALTER TABLE users
  ADD CONSTRAINT positive_age CHECK (age >= 0);

# composite primary key
CREATE TABLE enrollments (
  student_id integer REFERENCES students(id),
  course_id  integer REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

# unique constraint on multiple columns
ALTER TABLE users ADD CONSTRAINT unique_email_domain
  UNIQUE (email, domain);

# foreign key with actions
ALTER TABLE posts
  ADD FOREIGN KEY (user_id) REFERENCES users(id)
  ON UPDATE CASCADE ON DELETE SET NULL;

# drop a constraint
ALTER TABLE users DROP CONSTRAINT positive_age;

# defer a constraint until commit
SET CONSTRAINTS unique_email_domain DEFERRED;

Héritage de tables

L'héritage de tables de PostgreSQL est unique — les tables enfants héritent des colonnes des parents. Interroger le parent retourne les lignes de tous les enfants sauf avec ONLY. Utile pour le partitionnement de tables (avant le partitionnement déclaratif) et la modélisation de taxonomies. Mais l'héritage a des caprices : les clés étrangères ne se propagent pas, et les contraintes UNIQUE ne s'appliquent que par table. Pour les nouveaux besoins de partitionnement, utilisez le partitionnement déclaratif (PARTITION BY) — plus robuste et mieux supporté.

postgresql
# PostgreSQL supports table inheritance
CREATE TABLE people (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE employees (
  salary numeric,
  dept   text
) INHERITS (people);

CREATE TABLE customers (
  credit_limit numeric
) INHERITS (people);

# query parent sees all children
SELECT * FROM people;          -- includes employees and customers
SELECT * FROM ONLY people;     -- only people, not children

# insert into a child
INSERT INTO employees (name, salary, dept)
  VALUES ('Alice', 50000, 'Engineering');
03

Types de données

Types numériques

Utilisez des types entiers dimensionnés aux données — smallint pour les petites plages, bigint pour les grandes. numeric/decimal sont exacts (essentiel pour l'argent) mais plus lents que real/double. N'utilisez jamais float pour l'argent — les erreurs d'arrondi s'accumulent. serial est legacy ; préférez GENERATED AS IDENTITY. Le type money dépend de la locale et est rigide — utilisez numeric(10,2). Pour l'auto-incrément avec trous, les séquences peuvent sauter des nombres au rollback/crash (voulu pour la performance).

postgresql
# integer types (use the smallest that fits)
smallint    -- 2 bytes, -32768 to 32767
integer     -- 4 bytes, -2B to 2B
bigint      -- 8 bytes, very large

# auto-incrementing (use identity, not serial)
id integer GENERATED ALWAYS AS IDENTITY

# decimal/numeric (exact)
numeric(precision, scale)   -- e.g. numeric(10,2) for currency
decimal                     -- alias for numeric

# floating point (inexact, faster)
real        -- 4 bytes, 6 decimal digits
double precision  -- 8 bytes, 15 decimal digits

# special
serial / bigserial   -- legacy auto-increment (prefer identity)
money                -- currency (prefer numeric)

# examples
CREATE TABLE products (
  price numeric(10,2),        -- $99999999.99
  weight real,                -- 1.5 (kg)
  quantity integer DEFAULT 0
);

Types texte et caractères

Dans PostgreSQL, varchar, char et text ont des performances identiques — text est préféré pour l'illimité, varchar(n) quand on a besoin d'une contrainte de longueur. char(n) comble avec des espaces et est rarement utile. Changer la longueur d'un varchar est une opération de métadonnées seule (rapide). LIKE utilise les wildcards % et _ ; ILIKE est insensible à la casse ; ~ est la regex POSIX. Pour la recherche plein texte, utilisez tsvector/tsquery (couvert plus loin). Stockez toujours le texte en text/varchar, pas en bytea.

postgresql
# three text types (all variable-length)
varchar(n)   -- varchar with max length
char(n)      -- fixed-length, padded with spaces
text         -- unlimited length (preferred)

# in practice, all three perform the same
# varchar(n) adds a length check; char(n) pads (rarely useful)

CREATE TABLE articles (
  title    varchar(200) NOT NULL,
  slug     varchar(100) UNIQUE,
  body     text,
  summary  text DEFAULT ''
);

# string functions
SELECT length(body), char_length(title), octet_length(title)
FROM articles;

# change length limit (fast, no rewrite)
ALTER TABLE articles ALTER COLUMN title TYPE varchar(500);

# pattern matching
SELECT * FROM articles WHERE title LIKE '%postgres%';
SELECT * FROM articles WHERE title ILIKE '%postgres%'; -- case-insensitive
SELECT * FROM articles WHERE slug ~ '^[a-z0-9-]+$';   -- regex

Types date et heure

Utilisez toujours timestamptz (pas timestamp) pour les timestamps — il stocke en UTC et convertit vers le fuseau de session à l'affichage. timestamp (sans tz) est naïf et cause des bugs dans les apps multi-fuseaux. interval est puissant pour l'arithmétique ('1 day', '2 hours', '3 months'). date_trunc arrondit vers le bas à une unité. Les chaînes de format de to_char/to_timestamp sont flexibles. Réglez timezone par session ou rôle — les données sont stockées en UTC quoi qu'il arrive.

postgresql
# date/time types
date              -- date only (4 bytes)
time              -- time only (8 bytes)
timetz            -- time with time zone (12 bytes)
timestamp         -- timestamp without tz (8 bytes)
timestamptz       -- timestamp WITH time zone (8 bytes, preferred)
interval          -- time span (16 bytes)

# always use timestamptz for timestamps
CREATE TABLE events (
  id         serial PRIMARY KEY,
  created_at timestamptz DEFAULT now(),
  scheduled  timestamptz,
  duration   interval
);

# common operations
SELECT now();                               -- current timestamp
SELECT current_date;                         -- today
SELECT created_at + interval '1 hour';       -- add time
SELECT age('2025-01-01', '2024-01-01');       -- interval between
SELECT date_trunc('month', created_at);       -- truncate to month
SELECT to_char(created_at, 'YYYY-MM-DD HH:MI:SS');
SELECT to_timestamp('2025/01/15', 'YYYY/MM/DD');
SELECT EXTRACT(YEAR FROM created_at);

# timezone handling
SET timezone = 'UTC';
SET timezone = 'Asia/Shanghai';

JSON et JSONB

jsonb est du JSON binaire — plus rapide à interroger et indexable, mais légèrement plus lent à insérer et ne préserve pas l'ordre des clés. Utilisez jsonb pour tout nouveau travail. -> retourne du jsonb, ->> du texte. @> (containment) est le plus puissant des opérateurs JSON et se marie bien aux index GIN. jsonb_set modifie un chemin, || fusionne les objets, - retire les clés. Pour des requêtes fréquentes sur un champ JSON, ajoutez un index GIN ou un index d'expression sur la valeur extraite. JSONB est idéal pour des schémas flexibles dans une base relationnelle.

postgresql
# json stores text; jsonb stores binary (faster, preferred)
CREATE TABLE products (
  id    serial PRIMARY KEY,
  data  jsonb
);

# insert JSON
INSERT INTO products (data) VALUES
  ('{"name": "Widget", "price": 19.99, "tags": ["sale", "new"]}');

# query JSON fields
SELECT data->'name'        FROM products;  -- returns jsonb
SELECT data->>'name'       FROM products;  -- returns text
SELECT data->'tags'->0     FROM products;  -- first tag (jsonb)

# filter by JSON
SELECT * FROM products WHERE data->>'name' = 'Widget';
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';  -- containment

# modify JSON
UPDATE products SET data = jsonb_set(data, '{price}', '29.99');
UPDATE products SET data = data || '{"sale": true}';        -- merge
UPDATE products SET data = data - 'sale';                    -- remove key

# index JSON (GIN index)
CREATE INDEX idx_products_data ON products USING GIN (data);
CREATE INDEX idx_products_name ON products ((data->>'name'));

Tableaux et types personnalisés

Les tableaux PostgreSQL sont indexés à partir de 1 (contrairement à la plupart des langages). ANY et @> sont les principaux opérateurs de requête ; unnest() expand les tableaux en lignes (idéal pour les jointures). Les tableaux sont puissants mais à utiliser avec discernement — si vous interrogez/filtrez fréquemment les éléments, une table normalisée peut être meilleure. Les Enums sont ordonnés et validés à l'insertion. Les types composites permettent des colonnes structurées. Pour des données imbriquées complexes, considérez JSONB plutôt que des tableaux de composites.

postgresql
# array columns
CREATE TABLE teams (
  id      serial PRIMARY KEY,
  name    text,
  members text[]           -- array of text
);

INSERT INTO teams (name, members) VALUES
  ('Engineering', ARRAY['Alice', 'Bob', 'Carol']),
  ('Sales', '{"Dave", "Eve"}');

# query arrays
SELECT * FROM teams WHERE 'Alice' = ANY(members);
SELECT * FROM teams WHERE members @> ARRAY['Alice'];  -- contains
SELECT members[1] FROM teams;            -- 1-indexed!
SELECT array_length(members, 1) FROM teams;
SELECT unnest(members) FROM teams WHERE id = 1;  -- expand to rows

# custom enum type
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TABLE persons (id serial PRIMARY KEY, current_mood mood);
INSERT INTO persons (current_mood) VALUES ('happy');

# composite type
CREATE TYPE address AS (street text, city text, zip text);
CREATE TABLE contacts (id serial, addr address);
04

Opérations CRUD

INSERT

RETURNING est une fonctionnalité PostgreSQL qui retourne les lignes insérées/mises à jour/supprimées — élimine le besoin d'un SELECT séparé après l'insert. ON CONFLICT (UPSERT) est puissant : DO UPDATE utilise EXCLUDED pour référencer la ligne proposée, DO NOTHING saute en silence. L'insert multi-lignes est bien plus rapide que des inserts individuels. INSERT ... SELECT copie des données entre tables. Pour les chargements en masse, utilisez COPY (encore plus rapide) — il contourne l'overhead de parsing SQL.

postgresql
# basic insert
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);

# multi-row insert
INSERT INTO users (username, email) VALUES
  ('bob', '[email protected]'),
  ('carol', '[email protected]'),
  ('dave', '[email protected]');

# insert with RETURNING (get auto-generated values)
INSERT INTO users (username, email)
VALUES ('eve', '[email protected]')
RETURNING id, username, created_at;

# insert from a query (INSERT ... SELECT)
INSERT INTO archive_users (username, email)
SELECT username, email FROM users WHERE active = false;

# upsert (insert or update on conflict)
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email
RETURNING id;

# do nothing on conflict
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username) DO NOTHING;

UPDATE

Incluez toujours WHERE sauf intention de mettre à jour toutes les lignes. UPDATE ... FROM autorise les jointures dans les mises à jour (non standard mais puissant). RETURNING montre ce qui a changé — essentiel pour l'audit. Les expressions CASE permettent des mises à jour conditionnelles en une instruction. Les mises à jour ne changeant aucune valeur sont des no-ops (pas de WAL, pas de déclenchement de trigger au niveau instruction). Les grosses mises à jour peuvent enfler la table — batchez et VACUUM ensuite.

postgresql
# basic update
UPDATE users SET email = '[email protected]' WHERE id = 1;

# update multiple columns
UPDATE users
SET email = '[email protected]', age = 31, updated_at = now()
WHERE id = 1;

# update with RETURNING
UPDATE users SET role = 'admin' WHERE id = 1
RETURNING id, username, role;

# update from another table
UPDATE posts p
SET title = p.title || ' (archived)'
FROM users u
WHERE p.user_id = u.id AND u.role = 'admin';

# conditional update with CASE
UPDATE products
SET price = CASE
  WHEN price < 10 THEN price * 1.2
  WHEN price < 50 THEN price * 1.1
  ELSE price
END;

# update all rows (be careful!)
UPDATE users SET status = 'active';

DELETE et TRUNCATE

DELETE retire les lignes une à une (déclenche les triggers, visible dans les transactions). TRUNCATE retire toutes les lignes instantanément (DDL, pas transactionnel de la même façon, réinitialise les séquences avec RESTART IDENTITY). Utilisez TRUNCATE pour vider des tables entières — des ordres de grandeur plus rapide. CASCADE tronque les tables avec clés étrangères référençant celle-ci. DELETE avec RETURNING sert aux logs d'audit. Utilisez toujours WHERE avec DELETE — sans lui, vous supprimez tout (sans risque dans une transaction, catastrophique si commité).

postgresql
# basic delete
DELETE FROM users WHERE id = 1;

# delete with RETURNING
DELETE FROM users WHERE active = false
RETURNING id, username;

# delete based on a join
DELETE FROM posts
WHERE user_id IN (
  SELECT id FROM users WHERE role = 'deleted'
);

# delete using USING (join syntax)
DELETE FROM posts p
USING users u
WHERE p.user_id = u.id AND u.status = 'deleted';

# truncate (fast, resets sequences, DDL-like)
TRUNCATE TABLE posts;
TRUNCATE TABLE posts, comments;          -- multiple tables
TRUNCATE TABLE posts RESTART IDENTITY;   -- reset sequences
TRUNCATE TABLE posts CASCADE;            -- also truncates FK refs

# delete all rows (slower than TRUNCATE, but transactional)
DELETE FROM posts;

Bases de SELECT

DISTINCT ON est une extension PostgreSQL qui retourne la première ligne par groupe — puissant pour les requêtes « dernier par catégorie » (il faut ORDER BY la colonne du DISTINCT ON d'abord). LIMIT/OFFSET est une pagination simple mais lente pour de grands offsets — utilisez la pagination keyset (WHERE id > last_id) pour de meilleures performances. Spécifiez toujours les colonnes explicitement en production (pas SELECT *) pour éviter les surprises aux changements de schéma. Utilisez FETCH FIRST n ROWS ONLY pour la syntaxe standard SQL.

postgresql
# basic select
SELECT id, username, email FROM users;

# filtering
SELECT * FROM users WHERE age >= 18 AND role = 'user';
SELECT * FROM users WHERE role IN ('admin', 'moderator');
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE username LIKE 'al%';

# sorting and limiting
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT * FROM users ORDER BY age DESC, username ASC
OFFSET 20 LIMIT 10;  -- pagination (or use FETCH)

# distinct
SELECT DISTINCT role FROM users;
SELECT DISTINCT ON (user_id) * FROM posts
ORDER BY user_id, created_at DESC;  -- latest post per user

# column aliases and expressions
SELECT
  username AS name,
  EXTRACT(YEAR FROM created_at) AS join_year,
  age * 365 AS age_in_days
FROM users;

MERGE (PG15+)

MERGE (standard SQL, PG15+) est plus puissant que ON CONFLICT : il peut insérer, mettre à jour ET supprimer en une instruction selon des conditions de match. Utilisez-le pour des synchronisations complexes (ETL, merges de data warehouse). Pour des upserts simples, ON CONFLICT est plus concis et largement supporté. MERGE exige une source (table ou requête) et une condition de match. Les clauses WHEN MATCHED/NOT MATCHED définissent les actions. Chaque branche peut porter des conditions AND optionnelles pour un contrôle fin.

postgresql
# MERGE: insert, update, or delete in one statement (PG15+)
MERGE INTO products p
USING new_prices n
ON p.sku = n.sku
WHEN MATCHED AND p.price != n.price THEN
  UPDATE SET price = n.price, updated_at = now()
WHEN MATCHED AND n.discontinued = true THEN
  DELETE
WHEN NOT MATCHED THEN
  INSERT (sku, name, price) VALUES (n.sku, n.name, n.price);

# simpler upsert (still works, often preferred)
INSERT INTO products (sku, name, price) VALUES ('A1', 'Widget', 9.99)
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price;

# MERGE with a source query
MERGE INTO inventory i
USING (SELECT product_id, sum(qty) AS total FROM orders GROUP BY product_id) o
ON i.product_id = o.product_id
WHEN MATCHED THEN UPDATE SET quantity = i.quantity - o.total;
05

Interrogation et filtrage

WHERE et conditions

ILIKE est le LIKE insensible à la casse (extension PostgreSQL). ~ est la regex POSIX (sensible à la casse), ~* insensible, !~ la négation. IS DISTINCT FROM traite NULL comme valeur comparable (NULL = NULL est NULL, pas vrai). ANY/ALL marchent avec tableaux et sous-requêtes. Pour du matching de texte complexe, considérez la recherche plein texte (tsvector) ou les index trigram (pg_trgm) pour la performance. Indexez toujours les colonnes utilisées dans les WHERE sur les grandes tables.

postgresql
# comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price <> 0;        -- not equal
SELECT * FROM products WHERE name IS DISTINCT FROM 'Test';

# logical operators
SELECT * FROM products
WHERE (price > 50 AND category = 'electronics')
   OR (price < 10 AND category = 'books');

# IN, BETWEEN, LIKE/ILIKE
SELECT * FROM products WHERE category IN ('books', 'toys');
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM products WHERE name LIKE '_o%';     -- second char 'o'
SELECT * FROM products WHERE name ILIKE '%widget%';

# IS NULL / IS NOT NULL
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM products WHERE description IS NOT DISTINCT FROM NULL;

# ANY/ALL with arrays
SELECT * FROM products WHERE id = ANY(ARRAY[1,2,3]);
SELECT * FROM products WHERE price > ALL(ARRAY[10, 20, 30]);

# regex matching
SELECT * FROM products WHERE name ~ '^[A-C]';       -- starts with A-C
SELECT * FROM products WHERE name ~* 'widget';       -- case-insensitive
SELECT * FROM products WHERE name !~ 'test';         -- does not match

ORDER BY et pagination

La pagination keyset (WHERE id > last_id) est drastiquement plus rapide que OFFSET sur les grands jeux — OFFSET scanne et jette des lignes. ORDER BY random() est lent sur les grandes tables ; utilisez TABLESAMPLE pour l'échantillonnage statistique. NULLS FIRST/LAST contrôle où se trient les NULL (le défaut varie selon ASC/DESC). Incluez toujours un ORDER BY en paginant — sans lui, l'ordre des lignes est indéfini. Pour une pagination stable, triez par une colonne unique.

postgresql
# basic sorting
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY last_name ASC, first_name ASC;

# sort by expression
SELECT * FROM products ORDER BY (price * 1.1) DESC;
SELECT * FROM events ORDER BY created_at::date;

# NULLS handling
SELECT * FROM users ORDER BY last_login DESC NULLS LAST;
SELECT * FROM users ORDER BY last_login DESC NULLS FIRST;

# LIMIT/OFFSET pagination (simple but slow for large offsets)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;   -- page 1
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;  -- page 2

# keyset pagination (faster, use for large datasets)
SELECT * FROM users WHERE id > 100 ORDER BY id LIMIT 10;

# FETCH (SQL standard)
SELECT * FROM users ORDER BY id FETCH FIRST 10 ROWS ONLY;

# random rows
SELECT * FROM products ORDER BY random() LIMIT 5;  -- slow on large tables
SELECT * FROM products TABLESAMPLE BERNOULLI(1);    -- 1% sample

GROUP BY et agrégats

GROUP BY replie les lignes par colonnes de groupement ; les agrégats (count, sum, avg, min, max) calculent par groupe. HAVING filtre les groupes (WHERE filtre les lignes avant groupement). ROLLUP ajoute des sous-totaux à chaque niveau ; CUBE toutes les combinaisons ; GROUPING SETS permet de préciser exactement les groupements voulus — tous puissants pour le reporting. count(*) compte les lignes ; count(colonne) les valeurs non nulles. Utilisez count(DISTINCT col) pour les comptages uniques.

postgresql
# basic grouping
SELECT category, count(*) FROM products GROUP BY category;
SELECT user_id, sum(amount) FROM orders GROUP BY user_id;

# multiple aggregates
SELECT
  category,
  count(*)           AS total,
  avg(price)         AS avg_price,
  min(price)         AS min_price,
  max(price)         AS max_price,
  sum(price)         AS total_value
FROM products
GROUP BY category;

# HAVING (filter on aggregates, like WHERE for groups)
SELECT user_id, count(*) AS order_count
FROM orders
GROUP BY user_id
HAVING count(*) > 5;

# GROUP BY with multiple columns
SELECT category, status, count(*)
FROM products
GROUP BY category, status
ORDER BY category, status;

# GROUP BY ROLLUP/CUBE (subtotals)
SELECT category, status, count(*)
FROM products
GROUP BY ROLLUP (category, status);  -- adds subtotals and grand total

# GROUP BY GROUPING SETS
SELECT category, status, count(*)
FROM products
GROUP BY GROUPING SETS ((category, status), (category), ());

DISTINCT et opérations ensemblistes

UNION combine les résultats et retire les doublons (lent) ; UNION ALL les garde (rapide, préféré quand on sait qu'il n'y a pas de doublons ou que cela importe peu). INTERSECT retourne les lignes communes ; EXCEPT la différence. Les opérations ensemblistes exigent des types et comptages de colonnes compatibles. DISTINCT ON est un joyau PostgreSQL : il retourne la première ligne par groupe selon le ORDER BY — parfait pour les requêtes « dernier/le plus cher par catégorie ». Toutes les opérations ensemblistes s'enchaînent mais utilisez des parenthèses pour la précédence.

postgresql
# distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT category, status FROM products;

# DISTINCT ON (first row per group)
SELECT DISTINCT ON (category) *
FROM products
ORDER BY category, price DESC;  -- most expensive per category

# UNION (combine, remove duplicates)
SELECT 'user' AS type, username FROM users
UNION
SELECT 'admin' AS type, username FROM admins;

# UNION ALL (faster, keeps duplicates)
SELECT username FROM active_users
UNION ALL
SELECT username FROM inactive_users;

# INTERSECT (common rows)
SELECT product_id FROM orders_2024
INTERSECT
SELECT product_id FROM orders_2025;

# EXCEPT (rows in first but not second)
SELECT product_id FROM all_products
EXCEPT
SELECT product_id FROM discontinued_products;

# set operations with ORDER BY (applies to the whole result)
SELECT username FROM users
UNION
SELECT username FROM admins
ORDER BY username;

CASE et logique conditionnelle

Les expressions CASE ajoutent de la logique si/alors/sinon au SQL. Le CASE simple matche une valeur ; le CASE « searched » évalue des conditions. La clause FILTER (PostgreSQL) est plus propre que CASE dans les agrégats pour le comptage conditionnel. CASE peut apparaître dans SELECT, WHERE, ORDER BY et même GROUP BY. Utilisez-le pour les pivots, le formatage conditionnel et la logique métier appartenant à la base. Pour une logique multi-branches complexe, considérez une fonction ou une vue.

postgresql
# simple CASE
SELECT
  name,
  CASE category
    WHEN 'book' THEN 'Media'
    WHEN 'laptop' THEN 'Electronics'
    ELSE 'Other'
  END AS category_group
FROM products;

# searched CASE (more flexible)
SELECT
  name,
  CASE
    WHEN price < 10 THEN 'Cheap'
    WHEN price < 50 THEN 'Moderate'
    WHEN price < 100 THEN 'Expensive'
    ELSE 'Luxury'
  END AS price_tier
FROM products;

# CASE in aggregate (pivot-like)
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'active') AS active_count,
  count(*) FILTER (WHERE status = 'inactive') AS inactive_count
FROM users;

# CASE for conditional sorting
SELECT * FROM products
ORDER BY
  CASE WHEN featured THEN 0 ELSE 1 END,
  name;
06

Jointures

Jointures INNER et OUTER

INNER JOIN ne retourne que les lignes correspondantes. LEFT JOIN garde toutes les lignes de gauche (colonnes de droite à NULL sans correspondance) — la jointure la plus courante pour « inclure les données liées si elles existent ». RIGHT JOIN est rarement utilisé (réécrivez en LEFT). FULL OUTER JOIN garde tout. Le WHERE p.id IS NULL après un LEFT JOIN est le motif « anti-join » pour trouver les lignes sans correspondance. Qualifiez toujours les noms de colonnes par alias de table (u.id, p.user_id) pour éviter l'ambiguïté.

postgresql
# INNER JOIN (only matching rows)
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

# LEFT JOIN (all left rows, matching right or NULL)
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

# RIGHT JOIN (all right rows, matching left or NULL)
SELECT u.username, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;

# FULL OUTER JOIN (all rows from both, NULLs where no match)
SELECT u.username, p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;

# find rows with no match (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL;  -- users with no posts

Jointures multi-tables

PostgreSQL peut joindre beaucoup de tables, mais chaque jointure ajoute du coût — assurez-vous qu'elles utilisent des index. Les self-joins servent aux données hiérarchiques (mais considérez les CTE récursives pour les hiérarchies profondes). En joignant avec agrégation, LEFT JOIN + count(p.id) (pas count(*)) donne 0 pour les utilisateurs sans articles. L'optimiseur choisit l'ordre des jointures, mais écrire des requêtes claires aide. Pour des requêtes multi-tables complexes, considérez une vue.

postgresql
# join three tables
SELECT u.username, p.title, c.text AS comment
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id;

# join with different conditions
SELECT
  u.username,
  p.title,
  l.name AS liked_by
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN likes l ON p.id = l.post_id AND l.user_id != u.id;

# self-join (e.g., employees and their managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

# join with aggregation
SELECT u.username, count(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY post_count DESC;

CROSS JOIN et LATERAL

CROSS JOIN produit un produit cartésien (chaque ligne de A avec chaque ligne de B) — rarement voulu, mais utile avec generate_series pour générer des données. LATERAL est puissant : il permet à une sous-requête de référencer les colonnes de la requête externe, comme une fonction par ligne. Idéal pour les requêtes « top N par groupe » (bien plus propre que les fonctions de fenêtre pour les cas simples). LEFT JOIN LATERAL ... ON true inclut les lignes sans correspondance.

postgresql
# CROSS JOIN (Cartesian product — every combination)
SELECT u.username, c.name
FROM users u
CROSS JOIN categories c;
-- same as: SELECT ... FROM users, categories;

# useful for generating combinations
SELECT generate_series(1, 5) AS n, c.name
FROM categories c
CROSS JOIN generate_series(1, 5);

# LATERAL (subquery can reference outer query)
SELECT u.username, recent.title
FROM users u
CROSS JOIN LATERAL (
  SELECT title FROM posts
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) recent;

# LATERAL with LEFT JOIN (include users with no posts)
SELECT u.username, recent.title
FROM users u
LEFT JOIN LATERAL (
  SELECT title FROM posts WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 3
) recent ON true;

Stratégies de jointure (EXPLAIN)

PostgreSQL choisit les stratégies de jointure selon taille des tables, index et statistiques. Nested Loop est O(N*M) — bien pour les petites tables ou lookups indexés. Hash Join bâtit une table de hachage sur la plus petite entrée puis sonde — excellent pour les grandes jointures non triées. Merge Join exige les deux entrées triées — génial quand les index fournissent l'ordre. Ne forcez pas les stratégies en production ; laissez l'optimiseur décider. Utilisez EXPLAIN (ANALYZE) pour voir les timings réels et ajuster index/statistiques si nécessaire.

postgresql
# see how PostgreSQL joins tables
EXPLAIN SELECT * FROM users u JOIN posts p ON u.id = p.user_id;

# join strategies you'll see:
#   Nested Loop:   for each left row, scan right (good for small/indexed)
#   Hash Join:     hash one table, probe with other (good for large unsorted)
#   Merge Join:    both sorted, merge (good for sorted/indexed large tables)

# force a join strategy (for testing, not production)
SET enable_nestloop = off;
SET enable_hashjoin = off;
SET enable_mergejoin = off;

# check actual execution stats
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users u JOIN posts p ON u.id = p.user_id;

# join removal: PostgreSQL can remove unnecessary joins
# (e.g., LEFT JOIN to a table only used for FK validation)
EXPLAIN SELECT u.* FROM users u LEFT JOIN posts p ON u.id = p.user_id;

Conseils de performance des jointures

L'optimisation de jointure au plus fort impact est l'indexation des colonnes de clés étrangères — PostgreSQL ne crée PAS automatiquement d'index pour les FK. Gardez les statistiques à jour avec ANALYZE (l'auto-vacuum le fait, mais un ANALYZE manuel après les gros chargements aide). Évitez d'envelopper les colonnes de jointure dans des fonctions (empêche l'usage d'index). Ne sélectionnez que les colonnes nécessaires. Pour les tables énormes, considérez le partitionnement pour que l'optimiseur puisse élaguer. Surveillez les jointures lentes avec pg_stat_statements.

postgresql
# always index foreign key columns
CREATE INDEX idx_posts_user_id ON posts(user_id);

# index join columns on both sides
CREATE INDEX idx_orders_product_id ON orders(product_id);
CREATE INDEX idx_products_id ON products(id);  -- PK already indexed

# composite index for multi-column joins
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

# analyze tables for accurate statistics
ANALYZE users;
ANALYZE posts;

# avoid joining on expressions (prevents index use)
-- BAD:  JOIN ON lower(a.email) = lower(b.email)
-- GOOD: JOIN ON a.email = b.email (normalize case on insert)

# limit columns selected (reduces I/O)
SELECT u.username, p.title  -- not SELECT *
FROM users u JOIN posts p ON u.id = p.user_id;

# partition large tables for join pruning
SELECT * FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2025-01-01';  -- prunes partitions
07

Agrégations et fonctions de fenêtre

Fonctions d'agrégation

PostgreSQL a des agrégats riches au-delà de count/sum/avg. string_agg et array_agg concatènent les valeurs en chaîne/tableau — parfait pour éviter les requêtes N+1. bool_or/bool_and agrègent les booléens. percentile_cont calcule les percentiles exacts (médiane = 0.5) — très utile en analytique. mode() retourne la valeur la plus fréquente. La clause FILTER (count(*) FILTER (WHERE...)) est plus propre que CASE pour l'agrégation conditionnelle. ORDER BY dans les agrégats contrôle l'ordre de sortie.

postgresql
# standard aggregates
SELECT
  count(*)              AS total_rows,
  count(distinct category) AS unique_categories,
  sum(price)            AS total_value,
  avg(price)            AS avg_price,
  min(price)            AS min_price,
  max(price)            AS max_price,
  stddev(price)         AS std_dev,
  variance(price)       AS variance
FROM products;

# string aggregation
SELECT
  user_id,
  string_agg(tag, ', ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY user_id;

# array aggregation
SELECT
  user_id,
  array_agg(title ORDER BY created_at DESC) AS recent_titles
FROM posts
GROUP BY user_id;

# boolean aggregation
SELECT
  bool_or(published)    AS any_published,
  bool_and(published)   AS all_published
FROM posts;

# statistical aggregates
SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY price) AS median,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY price) AS p95,
  mode() WITHIN GROUP (ORDER BY price) AS most_common
FROM products;

Fonctions de fenêtre

Les fonctions de fenêtre calculent sur un ensemble de lignes liées à la ligne courante — elles ne replient pas les lignes comme GROUP BY. PARTITION BY définit le groupe de fenêtre ; ORDER BY l'ordre à l'intérieur. ROW_NUMBER est toujours unique ; RANK saute des numéros après les égalités ; DENSE_RANK ne saute pas. LAG/LEAD accèdent à d'autres lignes — parfait pour l'analyse de séries temporelles. Les clauses de cadre (ROWS BETWEEN ...) contrôlent quelles lignes sont incluses dans la fenêtre. Les fonctions de fenêtre s'évaluent après WHERE/GROUP BY/HAVING.

postgresql
# ROW_NUMBER: unique sequential number
SELECT
  title,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rank
FROM products;

# RANK and DENSE_RANK (handle ties)
SELECT
  title,
  category,
  price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS cat_rank,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

# LAG and LEAD (compare to previous/next row)
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change,
  LEAD(revenue, 1) OVER (ORDER BY date) AS next_day
FROM daily_sales;

# running total
SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

Cadres de fenêtre et NTILE

Les cadres de fenêtre contrôlent quelles lignes une fonction de fenêtre voit. Le cadre par défaut des agrégats est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (somme courante). Pour LAST_VALUE, il faut étendre le cadre à UNBOUNDED FOLLOWING sinon il retourne la ligne courante (le cadre par défaut s'arrête au courant). NTILE divise les lignes en N seaux — utile pour quartiles/deciles. FIRST_VALUE/LAST_VALUE retournent les valeurs des lignes de bord du cadre. Comprendre les cadres est la clé de la maîtrise des fonctions de fenêtre.

postgresql
# frame types
#   ROWS:   exact row count
#   RANGE:  logical range (e.g., same value)
#   GROUPS: groups of peer rows

SELECT
  date,
  revenue,
  # running sum from start to current
  SUM(revenue) OVER (ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,

  # 7-day moving average
  AVG(revenue) OVER (ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,

  # sum of all rows (no frame = entire partition)
  SUM(revenue) OVER () AS grand_total
FROM daily_sales;

# NTILE: divide rows into N equal buckets
SELECT
  name,
  price,
  NTILE(4) OVER (ORDER BY price DESC) AS price_quartile
FROM products;

# FIRST_VALUE / LAST_VALUE
SELECT
  name,
  category,
  price,
  FIRST_VALUE(name) OVER (PARTITION BY category ORDER BY price DESC) AS most_expensive,
  LAST_VALUE(name) OVER (
    PARTITION BY category ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS least_expensive
FROM products;

Clause FILTER

La clause FILTER est une extension PostgreSQL plus propre et souvent plus rapide que CASE dans les agrégats. Particulièrement utile pour les rapports façon pivot où l'on a besoin de plusieurs agrégats conditionnels. La requête scanne la table une fois et calcule tous les agrégats en une passe — bien plus efficace que plusieurs sous-requêtes. FILTER fait partie du standard SQL (contrairement à bien des extensions PG) et est supporté par d'autres bases aussi.

postgresql
# FILTER (PostgreSQL extension, cleaner than CASE)
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'active') AS active,
  count(*) FILTER (WHERE status = 'inactive') AS inactive,
  count(*) FILTER (WHERE status = 'banned') AS banned,
  sum(amount) FILTER (WHERE status = 'active') AS active_revenue
FROM orders
GROUP BY user_id;

# equivalent with CASE (more verbose)
SELECT
  count(*) AS total,
  count(CASE WHEN status = 'active' THEN 1 END) AS active,
  sum(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_revenue
FROM orders
GROUP BY user_id;

# FILTER with multiple aggregates in one pass (efficient)
SELECT
  category,
  count(*) FILTER (WHERE price > 100) AS expensive_count,
  avg(price) FILTER (WHERE price > 100) AS expensive_avg,
  count(*) FILTER (WHERE price <= 100) AS cheap_count,
  avg(price) FILTER (WHERE price <= 100) AS cheap_avg
FROM products
GROUP BY category;

Common Table Expressions (CTE)

Les CTE (clause WITH) améliorent la lisibilité et permettent la réutilisation de sous-requêtes. PostgreSQL 12+ inline les CTE non récursives par défaut (donc performance équivalente aux sous-requêtes) ; avant 12, les CTE étaient des clôtures d'optimisation. Les CTE RECURSIVES sont puissantes pour les données hiérarchiques/en arbre — la requête d'ancrage amorce, la partie récursive croît. Les CTE avec RETURNING permettent un mouvement de données complexe (supprimer d'une table, archiver dans une autre) en une instruction. Utilisez les CTE pour découper les requêtes complexes en étapes lisibles.

postgresql
# basic CTE (WITH clause)
WITH active_users AS (
  SELECT id, username FROM users WHERE active = true
)
SELECT au.username, count(p.id) AS post_count
FROM active_users au
LEFT JOIN posts p ON au.id = p.user_id
GROUP BY au.username;

# multiple CTEs (can reference each other)
WITH user_stats AS (
  SELECT user_id, count(*) AS post_count FROM posts GROUP BY user_id
),
top_users AS (
  SELECT user_id FROM user_stats WHERE post_count > 10
)
SELECT u.username, us.post_count
FROM users u
JOIN top_users tu ON u.id = tu.user_id
JOIN user_stats us ON u.id = us.user_id;

# recursive CTE (tree traversal)
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth FROM org_tree ORDER BY depth;

# CTE for DML (data modification)
WITH deleted AS (
  DELETE FROM users WHERE active = false RETURNING id
)
INSERT INTO archived_users (user_id)
SELECT id FROM deleted;
08

Index

Index B-Tree

Le B-tree est le type d'index par défaut et le plus courant — il supporte égalité, plage et tri. Les index composites se parcourent de gauche à droite, donc égalité d'abord, puis plage/tri. Les index partiels (clause WHERE) économisent l'espace et sont plus rapides — utilisez-les pour les requêtes filtrées courantes (ex. WHERE active = true). Les index d'expression permettent d'indexer des résultats de fonctions. CONCURRENTLY construit sans bloquer les écritures (plus lent mais sûr en production). Indexez toujours les clés étrangères !

postgresql
# create a B-tree index (default)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

# unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

# composite index (order matters!)
CREATE INDEX idx_posts_user_created
  ON posts(user_id, created_at DESC);

# partial index (only rows matching the condition)
CREATE INDEX idx_active_users ON users(last_login)
  WHERE active = true;

# expression index (index on a function)
CREATE INDEX idx_users_lower_email ON users(lower(email));
# query must use the same expression:
#   SELECT * FROM users WHERE lower(email) = '[email protected]'

# check index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';

# drop an index
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email;  -- no lock, slower

Index GIN et GiST

Les index GIN sont idéals quand chaque ligne a plusieurs valeurs indexées (clés JSONB, éléments de tableau, tokens de texte). Ils supportent @> (contient), ? (clé existe) et l'opérateur plein texte @@. Les index GIN sont plus grands mais gèrent ces requêtes efficacement. GiST sert aux données géométriques (points, polygones) et types de plage — supporte le chevauchement (&&), containment (@>), contained (<@). GIN et GiST sont plus lents à construire/mettre à jour que le B-tree mais permettent des requêtes impossibles au B-tree. GIN pour JSONB et plein texte ; GiST pour spatiale et plages.

postgresql
# GIN (Generalized Inverted Index) — for multi-value columns
# great for JSONB, arrays, full-text search

CREATE INDEX idx_products_data ON products USING GIN (data);        -- JSONB
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);              -- array
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector); -- full-text

# query patterns that use GIN
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';
SELECT * FROM posts WHERE tags && ARRAY['python'];
SELECT * FROM articles WHERE search_vector @@ to_tsquery('postgres & index');

# GiST (Generalized Search Tree) — for geometric/range data
CREATE INDEX idx_locations ON places USING GIST (location);  -- point/polygon
CREATE INDEX idx_events ON events USING GIST (during);       -- tsrange

# geometric queries
SELECT * FROM places
WHERE location <@ box '(0,0),(10,10)';

# range queries
SELECT * FROM events
WHERE during && tsrange('2025-01-01', '2025-02-01');

BRIN et autres types d'index

BRIN (Block Range INdex) est extrêmement compact (Ko pas Go) — il stocke min/max par plage de blocs. Efficace quand l'ordre physique des lignes matche l'ordre des requêtes (ex. logs de séries temporelles où les nouvelles données s'ajoutent). BRIN donne un filtrage « suffisant » qui élimine la plupart des blocs ; PostgreSQL vérifie ensuite les lignes restantes. Pour les tables énormes en append-only, BRIN change la donne. Les index Hash ne supportent que l'égalité (pas les plages) mais sont rapides pour les lookups ponctuels. SP-GiST convient aux données déséquilibrées comme les tables de routage IP.

postgresql
# BRIN (Block Range Index) — tiny, for naturally ordered data
# best for huge tables where data is physically ordered (e.g., time-series)

CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);
# index is kilobytes, not gigabytes!

# BRIN works by storing min/max for each block range
# effective when physical order matches logical order (e.g., append-only logs)

# compare sizes:
#   B-tree on 1B rows: ~20GB
#   BRIN on 1B rows:   ~2MB (10000x smaller)

# SP-GiST (space-partitioned GiST) — for non-balanced data
CREATE INDEX idx_prefix ON routes USING SPGiST (prefix);  -- e.g., IP routing

# Hash index (equality only, crash-safe since PG10)
CREATE INDEX idx_users_session ON users USING HASH (session_token);
# only supports = operator, not ranges or sorting

# check what index types are available
SELECT amname FROM pg_am WHERE amtype = 'i';

Maintenance et analyse des index

Surveillez l'usage des index avec pg_stat_user_indexes — les index inutilisés gaspillent l'espace et ralentissent les écritures. Retirez les doublons (mêmes colonnes, même table). REINDEX reconstruit les index enflés (fréquent avec mises à jour/suppressions lourdes) ; CONCURRENTLY (12+) évite le verrouillage. L'enflure d'index est normale — l'autovacuum maintient les index mais parfois un REINDEX manuel s'impose. Pour les très gros index, considérez REINDEX CONCURRENTLY ou pg_repack (outil tiers qui reconstruit sans verrous). Testez toujours les changements d'index en staging.

postgresql
# find unused indexes (candidates for removal)
SELECT
  schemaname, relname, indexrelname,
  idx_scan AS scans,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

# find duplicate indexes
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
       (array_agg(idx::text))[1] AS indexes
FROM (
  SELECT indexrelid::regclass AS idx, indrelid::regclass AS rel
  FROM pg_index
  GROUP BY indrelid, indkey
  HAVING count(*) > 1
) sub;

# REINDEX (rebuild a bloated index)
REINDEX INDEX idx_users_email;
REINDEX TABLE CONCURRENTLY users;  -- PG12+, no lock

# check index bloat
SELECT
  relname, pg_size_pretty(pg_relation_size(relid)) AS size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_relation_size(relid) DESC;

Stratégie d'indexation

Commencez par PK (auto-indexée) et FK (manuelle). Ajoutez des index pour les chemins de requêtes chauds. Les index composites suivent la règle du préfixe gauche — ordonnez les colonnes par sélectivité et patterns de requête. INCLUDE (covering index) permet aux requêtes d'être satisfaites par l'index seul (index-only scan) sans fetch de la ligne — énorme pour les charges en lecture. N'indexez pas tout : chaque index ajoute un coût d'écriture. Revoyez et éclaircissez régulièrement via pg_stat_user_indexes. Faites toujours EXPLAIN pour vérifier l'usage d'index.

postgresql
# 1. index primary keys (auto-created)
# 2. index foreign keys (NOT auto-created!)
CREATE INDEX idx_posts_user_id ON posts(user_id);

# 3. index columns in WHERE, JOIN, ORDER BY, GROUP BY
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_created ON orders(created_at);

# 4. use composite indexes for multi-column queries
# leftmost prefix rule: (a, b, c) helps WHERE a=?, WHERE a=? AND b=?
# but NOT WHERE b=? alone
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

# 5. partial indexes for common filters
CREATE INDEX idx_active_orders ON orders(created_at)
  WHERE status = 'active';

# 6. covering indexes (INCLUDE for index-only scans)
CREATE INDEX idx_products_cat_price ON products(category, price)
  INCLUDE (name);  -- name available without heap fetch

# 7. don't over-index — every index slows writes
# drop indexes used < 10 times/month (check pg_stat_user_indexes)

# verify the index is used
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
09

Transactions et concurrence

Bases des transactions

Les transactions groupent les instructions en une unité atomique — tout réussit ou tout échoue. BEGIN démarre, COMMIT sauve, ROLLBACK annule. Les savepoints permettent un rollback partiel dans une transaction — utile pour la récupération d'erreurs dans les opérations complexes. Par défaut, PostgreSQL auto-committe chaque instruction. Pour des opérations multi-instructions (transferts, inserts multi-lignes), utilisez toujours des transactions explicites. Gardez les transactions courtes pour réduire contention de verrous et deadlocks.

postgresql
# explicit transaction
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- or ROLLBACK to undo

# savepoints (partial rollback)
BEGIN;
  INSERT INTO orders (user_id, amount) VALUES (1, 50);
  SAVEPOINT order_inserted;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
  -- oops, wrong product
  ROLLBACK TO order_inserted;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 200);
COMMIT;

# transaction control
BEGIN;
  -- statements
SAVEPOINT my_savepoint;
  -- more statements
ROLLBACK TO my_savepoint;  -- undo back to savepoint
RELEASE my_savepoint;      -- remove savepoint (commit its changes)
COMMIT;
-- or ROLLBACK to undo everything

# auto-commit (default — each statement is its own transaction)
SET autocommit = on;  -- default

Niveaux d'isolation

Le défaut de PostgreSQL (READ COMMITTED) convient à la plupart des apps — pas de lectures sales, mais une relecture dans la même transaction peut voir de nouvelles données. REPEATABLE READ dans PostgreSQL est plus fort que le standard SQL — il prévient aussi les fantômes (via l'isolation par snapshot). SERIALIZABLE est le plus fort mais peut avorter des transactions en conflit — votre app doit rejouer sur SQLSTATE 40001. Pour la plupart des apps web, READ COMMITTED suffit. Utilisez SERIALIZABLE pour la justesse critique (registres financiers) avec logique de retry.

postgresql
# set isolation level for a transaction
BEGIN ISOLATION LEVEL READ COMMITTED;   -- default
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;

# or set it for the session
SET default_transaction_isolation = 'serializable';

# READ COMMITTED (default):
#   - each query sees committed data at query start
#   - no dirty reads, but non-repeatable reads and phantoms possible

# REPEATABLE READ:
#   - each query sees snapshot from transaction start
#   - no non-repeatable reads, but serialization anomalies possible
#   - PostgreSQL's RR is stronger than SQL standard (no phantoms!)

# SERIALIZABLE:
#   - strongest isolation, behaves as if transactions ran one at a time
#   - may abort with serialization failure (retry needed)
#   - uses SSI (Serializable Snapshot Isolation)

# check current level
SHOW transaction_isolation;

# handle serialization failures (retry)
BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- work
COMMIT;  -- may fail with SQLSTATE 40001 -> retry the transaction

Verrous et deadlocks

SELECT FOR UPDATE verrouille les lignes pour mise à jour (empêche la modification concurrente) — utile pour les motifs « lire, vérifier, mettre à jour ». Les verrous advisory sont des verrous applicatifs à clés entières — parfaits pour coordonner des processus distribués. Les deadlocks surviennent quand deux transactions s'attendent mutuellement ; PostgreSQL les détecte et avorte l'une (attrapez et rejouez). Pour éviter les deadlocks, acquérez les verrous dans un ordre cohérent entre transactions. Gardez les transactions courtes. Surveillez pg_locks pour les sessions bloquées.

postgresql
# row-level locks (acquired by UPDATE, DELETE, SELECT FOR UPDATE)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;        -- lock row
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE; -- weaker lock
SELECT * FROM accounts WHERE id = 1 FOR SHARE;         -- allow reads, block writes

# advisory locks (application-level locks)
SELECT pg_advisory_lock(12345);          -- session-level
SELECT pg_advisory_unlock(12345);
SELECT pg_try_advisory_lock(12345);      -- non-blocking
SELECT pg_advisory_xact_lock(12345);     -- transaction-level (auto-released)

# view active locks
SELECT pid, mode, granted, query
FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

# terminate a blocked session
SELECT pg_cancel_backend(pid);    -- cancel query
SELECT pg_terminate_backend(pid); -- kill session

# deadlocks (PostgreSQL detects and resolves by killing one transaction)
-- Transaction A: UPDATE users WHERE id=1; UPDATE users WHERE id=2;
-- Transaction B: UPDATE users WHERE id=2; UPDATE users WHERE id=1;
-- -> ERROR: deadlock detected

MVCC et isolation par snapshot

Le MVCC de PostgreSQL signifie que les lecteurs ne bloquent jamais les rédacteurs et inversement — chaque transaction voit un snapshot. Les mises à jour créent de nouvelles versions de lignes ; les anciennes demeurent jusqu'à ce que VACUUM les retire. Les transactions longues empêchent VACUUM de nettoyer les vieilles versions, causant de l'enflure. Gardez les transactions courtes ! Surveillez les sessions « idle in transaction » — elles détiennent des snapshots et bloquent le nettoyage. Le wraparound des ID de transaction est un sérieux problème (l'autovacuum le prévient, mais surveillez si l'autovacuum peine).

postgresql
# PostgreSQL uses MVCC (Multi-Version Concurrency Control)
# each transaction sees a consistent snapshot of data

# visible transactions
SELECT txid_current();           -- current transaction ID
SELECT txid_snapshot_xmin(txid_current_snapshot());

# see long-running transactions (can prevent vacuum)
SELECT
  pid,
  age(clock_timestamp(), xact_start) AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY xact_start;

# long transactions block vacuum (old row versions can't be removed)
# check for this:
SELECT
  pid,
  age(txid_current(), backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;

# vacuum and the transaction ID wraparound
# (autovacuum handles this, but monitor it!)
SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000;

Motifs de verrouillage

FOR UPDATE verrouille pessimistement les lignes — pour la cohérence critique (transferts). Le verrouillage optimiste (colonne de version) est mieux sous faible contention : pas de verrous, juste un retry au conflit. SKIP LOCKED est parfait pour les files de tâches — plusieurs workers saisissent des tâches différentes sans se bloquer. NOWAIT échoue vite si verrouillé (pour « tenter sans attendre »). Les verrous advisory coordonnent les opérations applicatives (migrations, cron). Choisissez le pessimiste sous forte contention, l'optimiste sous faible.

postgresql
# pessimistic locking (lock before work)
BEGIN;
  SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
  -- now safe to modify
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

# optimistic locking (check version, retry on conflict)
-- uses a version column
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;  -- 0 rows = conflict, retry

# SKIP LOCKED (process queue items without blocking)
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;
-- grabs 10 jobs, skips locked ones (other workers don't wait)

# NOWAIT (fail immediately if locked)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock if row is locked

# named lock for coordination
SELECT pg_advisory_lock(hashtext('migrate_users'));
-- only one session can hold this lock
SELECT pg_advisory_unlock(hashtext('migrate_users'));
10

Vues et vues matérialisées

Créer des vues

Les vues sont des requêtes sauvegardées qui se comportent comme des tables. Les vues simples (une table, pas d'agrégats) sont automatiquement updatables — inserts/updates passent à la table sous-jacente. WITH CHECK OPTION empêche les inserts/updates rendant la ligne invisible à travers la vue. Utilisez les vues pour simplifier des requêtes complexes, imposer la sécurité (accès au niveau colonne) et fournir une API stable. CASCADE supprime les objets dépendants. Les vues ne stockent pas de données — elles rejouent la requête à chaque fois.

postgresql
# simple view
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE active = true;

# use it like a table
SELECT * FROM active_users WHERE username LIKE 'a%';

# view with computed columns
CREATE VIEW user_summary AS
SELECT
  u.id,
  u.username,
  count(p.id) AS post_count,
  max(p.created_at) AS last_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;

# updatable view (simple views can be inserted/updated through)
CREATE VIEW user_emails AS
SELECT id, email FROM users;
INSERT INTO user_emails (id, email) VALUES (1, '[email protected]');  -- works!

# WITH CHECK OPTION (prevent inserts that wouldn't be visible)
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true
WITH CHECK OPTION;
-- INSERT with active=false fails

# modify or drop a view
ALTER VIEW active_users RENAME TO current_users;
DROP VIEW IF EXISTS active_users CASCADE;

Vues matérialisées

Les vues matérialisées stockent le résultat de la requête — bien plus rapides à interroger mais périmées jusqu'au rafraîchissement. Utilisez-les pour des agrégats coûteux (dashboards, rapports). CONCURRENTLY rafraîchit sans verrouiller la vue (les lecteurs continuent) mais exige un index unique. Planifiez les rafraîchissements avec pg_cron ou des outils externes. Pour des données quasi temps réel, rafraîchissez toutes les quelques minutes ; pour l'analytique historique, chaque jour. Le compromis est fraîcheur vs vitesse — les vues matérialisées sont la clé de l'analytique rapide sur grandes tables.

postgresql
# create a materialized view (stores the result)
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
  date_trunc('day', created_at) AS day,
  product_id,
  count(*) AS order_count,
  sum(amount) AS total_revenue
FROM orders
GROUP BY 1, 2
WITH DATA;  -- or WITH NO DATA to populate later

# refresh (re-runs the query)
REFRESH MATERIALIZED VIEW sales_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;  -- needs unique index

# create a unique index for concurrent refresh
CREATE UNIQUE INDEX idx_sales_summary_day_product
ON sales_summary(day, product_id);

# schedule refresh (use pg_cron extension or external scheduler)
-- pg_cron: SELECT cron.schedule('0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary');

# drop
DROP MATERIALIZED VIEW IF EXISTS sales_summary;

# use it (like a regular table)
SELECT * FROM sales_summary WHERE day = '2025-01-01';

Vues de sécurité

Les vues sont un outil de sécurité puissant : exposez seulement les colonnes/lignes qu'un utilisateur doit voir sans accorder l'accès à la table. Accordez SELECT sur la vue, pas sur la table sous-jacente. Pour la sécurité au niveau ligne, considérez plutôt les politiques RLS (Row Level Security) intégrées de PostgreSQL — plus robustes et compatibles avec l'accès direct aux tables. Les vues avec variables de session (current_setting) permettent le filtrage par utilisateur. Utilisez les vues pour créer une couche API stable qui masque la complexité du schéma aux applications.

postgresql
# column-level security (hide sensitive columns)
CREATE VIEW public_users AS
SELECT id, username, created_at FROM users;
-- password, email columns are hidden

# row-level security via view
CREATE VIEW user_posts AS
SELECT * FROM posts WHERE user_id = current_setting('app.current_user_id')::int;

-- set the user context per session
SET app.current_user_id = '42';
SELECT * FROM user_posts;  -- only sees user 42's posts

# view with joins for simplified access
CREATE VIEW order_details AS
SELECT
  o.id AS order_id,
  o.created_at,
  u.username,
  p.name AS product_name,
  oi.quantity,
  oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

# grant access to view but not underlying tables
GRANT SELECT ON public_users TO app_readonly;
REVOKE SELECT ON users FROM app_readonly;

Gestion des vues

CREATE OR REPLACE VIEW a des limites — on peut ajouter des colonnes à la fin mais pas en retirer ni réordonner. Pour des changements significatifs, supprimez et recréez (mais vérifiez d'abord les dépendances). Les vues peuvent dépendre d'autres vues ; supprimer une vue de base exige CASCADE. Utilisez pg_views pour inspecter les définitions. Le bloc DO pour rafraîchir toutes les vues matérialisées est pratique pour les scripts de maintenance — mais utilisez CONCURRENTLY quand possible pour éviter le verrouillage.

postgresql
# list views
SELECT viewname, definition FROM pg_views WHERE schemaname = 'public';

# get view definition
\d+ view_name  -- in psql

# create or replace (limited — can't change columns)
CREATE OR REPLACE VIEW user_summary AS
SELECT id, username, email FROM users WHERE active = true;
-- can add columns at the end, but can't remove/reorder

# to change columns significantly, drop and recreate
DROP VIEW IF EXISTS user_summary;
CREATE VIEW user_summary AS
SELECT id, username FROM users WHERE active = true;

# view dependencies (what depends on this view)
SELECT dependee.relname AS view, depender.relname AS depends_on
FROM pg_depend d
JOIN pg_class dependee ON d.objid = dependee.oid
JOIN pg_class depender ON d.refobjid = depender.oid
WHERE dependee.relname = 'user_summary';

# refresh all materialized views
DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT matviewname FROM pg_matviews WHERE schemaname = 'public' LOOP
    EXECUTE 'REFRESH MATERIALIZED VIEW ' || r.matviewname;
  END LOOP;
END $$;

Vues récursives

Les vues récursives (CREATE RECURSIVE VIEW) enveloppent une CTE récursive — parfaites pour le parcours d'arbres/graphes. Incluez toujours une limite de profondeur/distance dans la partie récursive pour prévenir les boucles infinies (cycles dans les données). La colonne path (concaténation de texte) est un moyen simple de suivre et afficher le chemin parcouru. Pour des hiérarchies très profondes, considérez une closure table ou l'extension ltree pour de meilleures performances. Les vues récursives sont pratiques mais peuvent être lentes sur les grands jeux — ajoutez des index sur les colonnes de jointure.

postgresql
# recursive view for hierarchical data
CREATE RECURSIVE VIEW employee_tree(id, name, manager_id, level, path) AS
SELECT
  id, name, manager_id, 0, name::text
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
  e.id, e.name, e.manager_id, et.level + 1,
  et.path || ' > ' || e.name
FROM employees e
JOIN employee_tree et ON e.manager_id = et.id;

# query the tree
SELECT * FROM employee_tree ORDER BY path;

# find all subordinates (2 levels deep)
SELECT * FROM employee_tree
WHERE path LIKE 'CEO > Engineering > %' AND level <= 3;

# view for graph traversal (e.g., friend connections)
CREATE RECURSIVE VIEW friend_chain(person1, person2, distance, path) AS
SELECT person1, person2, 1, person1 || '->' || person2
FROM friendships
UNION
SELECT
  fc.person1, f.person2, fc.distance + 1,
  fc.path || '->' || f.person2
FROM friend_chain fc
JOIN friendships f ON fc.person2 = f.person1
WHERE fc.distance < 6;  -- limit depth to prevent infinite loops
11

Fonctions et opérateurs

Fonctions de chaînes

PostgreSQL a des fonctions de chaînes complètes. position() et strpos() trouvent des sous-chaînes. substring() avec FROM/FOR est standard SQL ; substr() plus compact. split_part() est pratique pour les données délimitées. initcap() capitalise chaque mot. lpad/rpad bourrent les chaînes à largeur fixe (utile pour formater). string_to_array/array_to_string convertissent entre chaînes et tableaux. Pour le matching de motifs, LIKE/ILIKE (wildcards), ~ (regex) et SIMILAR TO (motif SQL) couvrent la plupart des besoins.

postgresql
# length and position
SELECT length('hello'), char_length('hello'), octet_length('hello');
SELECT position('lo' in 'hello');          -- 4
SELECT strpos('hello', 'lo');               -- 4

# substring and slicing
SELECT substring('hello' from 2 for 3);     -- 'ell'
SELECT substring('hello' from 2);           -- 'ello'
SELECT substr('hello', 2, 3);               -- 'ell'
SELECT left('hello', 3);                    -- 'hel'
SELECT right('hello', 3);                   -- 'llo'

# case and trim
SELECT upper('hello'), lower('HELLO'), initcap('hello world');
SELECT trim('  hello  '), ltrim('xxhello', 'x'), rtrim('helloxx', 'x');
SELECT btrim('xxhelloxx', 'x');             -- trim both sides

# split and join
SELECT split_part('a,b,c', ',', 2);         -- 'b' (1-indexed)
SELECT string_to_array('a,b,c', ',');       -- {a,b,c}
SELECT array_to_string(ARRAY['a','b','c'], ', ');  -- 'a, b, c'

# replace and pad
SELECT replace('hello', 'l', 'L');          -- 'heLLo'
SELECT lpad('5', 3, '0');                   -- '005'
SELECT rpad('5', 3, '0');                   -- '500'
SELECT repeat('ab', 3);                     -- 'ababab'
SELECT reverse('hello');                    -- 'olleh'

Fonctions date/heure

now() et current_timestamp sont équivalents (heure de début de transaction). EXTRACT/date_part obtiennent les composantes. date_trunc arrondit vers le bas à une unité — essentiel pour le bucketing temporel. age() retourne des intervalles (lisibles humainement). to_char/to_date/to_timestamp gèrent formatage/parsing. generate_series avec des dates est incroyablement utile pour combler les trous des séries temporelles (LEFT JOIN contre elle pour montrer les jours à zéro). Utilisez toujours timestamptz ; l'arithmétique de dates respecte le fuseau de session.

postgresql
# current date/time
SELECT now(), current_timestamp, transaction_timestamp();
SELECT current_date, current_time;

# extract components
SELECT EXTRACT(YEAR FROM now()), EXTRACT(MONTH FROM now());
SELECT date_part('dow', now());     -- day of week (0=Sunday)
SELECT date_part('epoch', now());   -- Unix timestamp

# date arithmetic
SELECT now() + interval '1 day';
SELECT now() - interval '2 hours';
SELECT age('2025-01-01');                    -- interval since date
SELECT age('2025-01-01', '2024-01-01');      -- interval between dates
SELECT date '2025-01-15' - date '2025-01-01'; -- 14 (integer days)

# truncation and rounding
SELECT date_trunc('month', now());           -- first day of month, 00:00
SELECT date_trunc('hour', now());            -- current hour, 00 minutes

# formatting
SELECT to_char(now(), 'YYYY-MM-DD HH:MI:SS');
SELECT to_char(now(), 'Day, DD Mon YYYY');
SELECT to_char(12345.678, 'FM999,999.00');
SELECT to_date('2025/01/15', 'YYYY/MM/DD');
SELECT to_timestamp(1700000000);             -- Unix epoch

# generate series (date ranges)
SELECT generate_series(
  '2025-01-01'::date,
  '2025-01-31'::date,
  '1 day'::interval
);

Fonctions numériques et mathématiques

round() à deux arguments marche sur numeric (pas double — castez d'abord). Les fonctions trigonométriques utilisent des radians (radians()/degrees() pour convertir). random() retourne 0-1 ; combinez avec des maths pour des plages. generate_series est le cheval de bataille de PostgreSQL — il génère des lignes à la volée pour séquences, plages de dates et comblement de trous. gcd/lcm (13+) sont pratiques. Pour les calculs financiers, utilisez toujours numeric (exact) — les fonctions flottantes comme round sur double peuvent avoir des problèmes de précision.

postgresql
# rounding
SELECT round(3.14159, 2);    -- 3.14
SELECT ceil(3.1), ceiling(3.1);  -- 4
SELECT floor(3.9);           -- 3
SELECT trunc(3.14159, 2);    -- 3.14 (truncate, no rounding)

# power and roots
SELECT power(2, 10);         -- 1024
SELECT sqrt(16);             -- 4
SELECT cbrt(27);             -- 3
SELECT exp(1);               -- 2.718... (e^x)
SELECT ln(10), log(100);     -- natural log, base-10 log

# trigonometry (radians!)
SELECT sin(radians(90));     -- 1 (convert degrees to radians)
SELECT degrees(1.5708);      -- ~90
SELECT pi();                 -- 3.14159...

# random
SELECT random();             -- 0 to 1
SELECT floor(random() * 100)::int;  -- 0 to 99
SELECT setseed(0.5);         -- seed for reproducibility

# sequences and ranges
SELECT generate_series(1, 10);
SELECT generate_series(1, 10, 2);   -- 1, 3, 5, 7, 9
SELECT generate_series(0, 1, 0.1);  -- 0.0, 0.1, ..., 1.0

# absolute, sign, gcd
SELECT abs(-5), sign(-5);    -- 5, -1
SELECT gcd(12, 8);           -- 4 (PG 13+)
SELECT lcm(12, 8);           -- 24 (PG 13+)

Fonctions conditionnelles et NULL

COALESCE retourne la première valeur non NULL — essentiel pour les défauts. NULLIF retourne NULL si les valeurs matchent — plus propre que CASE pour éviter la division par zéro. GREATEST/LEAST comparent des valeurs (les NULL se propagent dans GREATEST mais pas toujours dans LEAST). CASE est la conditionnelle générale. ISNULL/NOTNULL sont des raccourcis PG. La comparaison de lignes (ROW(a,b) < ROW(c,d)) compare lexicographiquement — utile pour la pagination par clé composite. Comprendre le comportement de NULL (NULL = NULL est NULL, pas vrai) est crucial dans PostgreSQL.

postgresql
# COALESCE (return first non-NULL)
SELECT COALESCE(nickname, username, email, 'anonymous') FROM users;

# NULLIF (return NULL if two values are equal)
SELECT NULLIF(status, '') FROM users;  -- '' becomes NULL
-- useful to avoid division by zero:
SELECT total / NULLIF(count, 0) FROM stats;  -- NULL instead of error

# GREATEST / LEAST
SELECT GREATEST(1, 2, 3);        -- 3
SELECT LEAST(NULL, 1, 2);        -- NULL (NULLs propagate)
SELECT GREATEST(a, b, c) FROM numbers;

# CASE expressions
SELECT
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age >= 65 THEN 'senior'
    ELSE 'adult'
  END AS age_group
FROM users;

# ISNULL / NOTNULL (PostgreSQL shortcuts)
SELECT * FROM users WHERE email ISNULL;    -- IS NULL
SELECT * FROM users WHERE email NOTNULL;   -- IS NOT NULL

# row comparison
SELECT ROW(1, 2) < ROW(1, 3);  -- true (compares column by column)

Fonctions de tableaux et JSON

Les fonctions de tableaux permettent une manipulation de données puissante dans la base : unnest expand les tableaux en lignes (idéal pour les jointures), array_agg est l'inverse. Opérateurs JSONB : -> et #> retournent du jsonb, ->> et #>> du texte. @> (contient) est le plus important des opérateurs JSONB et utilise les index GIN. ? vérifie l'existence de clé. jsonb_build_object construit du JSON depuis des colonnes. jsonb_set/||/- modifient le JSON. Ces fonctions font de JSONB un type de données de première classe — flexible et interrogeable.

postgresql
# array functions
SELECT array_length(ARRAY[1,2,3], 1);    -- 3
SELECT array_append(ARRAY[1,2], 3);       -- {1,2,3}
SELECT array_prepend(1, ARRAY[2,3]);      -- {1,2,3}
SELECT array_concat(ARRAY[1,2], ARRAY[3,4]); -- {1,2,3,4}
SELECT array_remove(ARRAY[1,2,3], 2);     -- {1,3}
SELECT array_replace(ARRAY[1,2,1], 1, 9); -- {9,2,9}
SELECT unnest(ARRAY[1,2,3]);              -- 3 rows
SELECT array_agg(name) FROM users;        -- aggregate to array

# JSONB functions
SELECT data->'name'           -- jsonb (key access)
SELECT data->>'name'          -- text (key access)
SELECT data#>'{address,city}' -- jsonb (path access)
SELECT data#>>'{address,city}'-- text (path access)
SELECT jsonb_build_object('name', username, 'age', age) FROM users;
SELECT jsonb_object_keys(data) FROM products;  -- keys as rows
SELECT jsonb_array_elements(data->'tags') FROM products;  -- expand array

# JSONB predicates
SELECT data @> '{"active": true}'     -- contains
SELECT data ? 'name'                   -- key exists
SELECT data ?| ARRAY['name','email']   -- any key exists
SELECT data ?& ARRAY['name','email']   -- all keys exist

# modify JSONB
SELECT jsonb_set(data, '{price}', '29.99')
SELECT data || '{"new": true}'         -- merge
SELECT data - 'old_key'                -- remove key
SELECT data #- '{nested,old_key}'      -- remove nested key
12

Triggers

Fonctions de trigger

Les fonctions de trigger doivent retourner trigger et s'écrivent en PL/pgSQL (ou autres langages). TG_OP, TG_TABLE_NAME, OLD, NEW sont des variables spéciales disponibles dans les triggers. Les triggers BEFORE peuvent modifier NEW (et sauter des opérations en retournant NULL). Les AFTER servent aux effets de bord (logs d'audit, notifications). FOR EACH ROW se déclenche par ligne ; FOR EACH STATEMENT une fois. Les triggers sont puissants mais peuvent causer des effets de bord cachés — réservez-les aux préoccupations transverses (audit, timestamps), pas à la logique métier.

postgresql
# create a trigger function (must return trigger)
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# attach the trigger
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

# now any UPDATE on users sets updated_at automatically
UPDATE users SET email = '[email protected]' WHERE id = 1;
-- updated_at is set automatically

# audit log trigger
CREATE OR REPLACE FUNCTION audit_log()
RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_table (table_name, operation, user_name, changed_at, row_data)
  VALUES (
    TG_TABLE_NAME, TG_OP, current_user, now(),
    to_jsonb(OLD)  -- or NEW for insert
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit
  AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION audit_log();

Triggers statement vs row

Les triggers FOR EACH ROW se déclenchent par ligne (peuvent être lents pour les opérations en masse). FOR EACH STATEMENT se déclenche une fois (ne peut pas accéder aux OLD/NEW individuels, mais peut utiliser des transition tables pour le bulk). Les BEFORE peuvent modifier/sauter des lignes ; les AFTER servent aux effets de bord. La clause WHEN filtre quand le trigger se déclenche (évaluée par ligne). Les transition tables (REFERENCING NEW TABLE/OLD TABLE) donnent aux triggers statement accès à toutes les lignes modifiées — génial pour auditer des opérations en masse sans coût par ligne.

postgresql
# ROW trigger (fires once per affected row)
CREATE TRIGGER log_each_change
  AFTER UPDATE ON products
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION log_price_change();

# STATEMENT trigger (fires once per statement, regardless of rows)
CREATE TRIGGER refresh_summary
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH STATEMENT
  EXECUTE FUNCTION refresh_sales_summary();

# BEFORE vs AFTER
# BEFORE: can modify NEW, skip the operation (return NULL), or change values
# AFTER: cannot modify the row, used for side effects (logs, notifications)

# conditional trigger (WHEN clause — evaluated per row)
CREATE TRIGGER check_high_value
  BEFORE INSERT ON orders
  FOR EACH ROW
  WHEN (NEW.amount > 10000)
  EXECUTE FUNCTION require_approval();

# transition tables (NEW/OLD as sets, for statement triggers)
CREATE TRIGGER audit_orders
  AFTER INSERT ON orders
  REFERENCING NEW TABLE AS new_rows
  FOR EACH STATEMENT
  EXECUTE FUNCTION log_bulk_insert();

Event triggers (DDL)

Les event triggers se déclenchent sur les commandes DDL (CREATE/ALTER/DROP) — utiles pour auditer les changements de schéma ou prévenir des opérations dangereuses en production. Ils utilisent tg_event et tg_tag pour identifier la commande. Cas d'usage courants : empêcher DROP TABLE, loguer tout le DDL pour conformité, générer automatiquement des scripts de migration. Les event triggers se déclenchent APRÈS la commande (ddl_command_end) ou peuvent intercepter (ddl_command_start). Ils sont au niveau base, pas table. Avec parcimonie — ils ajoutent un overhead à chaque opération DDL.

postgresql
# event triggers fire on DDL events (CREATE, ALTER, DROP)
CREATE OR REPLACE FUNCTION no_drop_table()
RETURNS event_trigger AS $$
BEGIN
  IF tg_event = 'ddl_command_end' AND tg_tag = 'DROP TABLE' THEN
    RAISE EXCEPTION 'Dropping tables is not allowed in production';
  END IF;
END;
$$ LANGUAGE plpgsql;

# register the event trigger
CREATE EVENT TRIGGER protect_tables
  ON ddl_command_end
  WHEN tag IN ('DROP TABLE')
  EXECUTE FUNCTION no_drop_table();

# log all DDL changes
CREATE OR REPLACE FUNCTION log_ddl()
RETURNS event_trigger AS $$
DECLARE
  obj record;
BEGIN
  INSERT INTO ddl_log (event, tag, user_name, object_identity)
  VALUES (tg_event, tg_tag, current_user, NULL);
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER ddl_logger
  ON ddl_command_end
  EXECUTE FUNCTION log_ddl();

# drop an event trigger
DROP EVENT TRIGGER IF EXISTS protect_tables;

Gestion des triggers

Désactivez les triggers temporairement pour les chargements en masse (bien plus rapide) — mais pensez à les réactiver ! session_replication_role = 'replica' désactive tous les triggers et règles — pour les imports en masse ou la réplication. ALTER TABLE ... DISABLE TRIGGER est plus ciblé. Réactivez toujours les triggers après les chargements. Surveillez l'usage des triggers — ils peuvent causer des problèmes de performance cachés (chaque trigger ajoute un coût par ligne). information_schema.triggers liste tous les triggers ; \d+ dans psql est plus rapide pour l'inspection.

postgresql
# list triggers
SELECT
  event_object_table AS table_name,
  trigger_name,
  action_timing,       -- BEFORE/AFTER/INSTEAD OF
  event_manipulation,  -- INSERT/UPDATE/DELETE
  action_statement     -- function call
FROM information_schema.triggers;

# or use psql
\d+ users  -- shows triggers on the table

# enable/disable triggers
ALTER TABLE users DISABLE TRIGGER set_updated_at;
ALTER TABLE users ENABLE TRIGGER set_updated_at;
ALTER TABLE users DISABLE TRIGGER ALL;    -- all triggers
ALTER TABLE users ENABLE TRIGGER ALL;

# session_replication_role (disable for replication)
SET session_replication_role = 'replica';
-- triggers don't fire (useful for bulk data loads)
SET session_replication_role = 'origin';  -- re-enable

# drop a trigger
DROP TRIGGER IF EXISTS set_updated_at ON users;

# rename a trigger
ALTER TRIGGER set_updated_at ON users RENAME TO update_timestamp;

Motifs de triggers courants

Motifs courants de triggers : horodatage automatique (updated_at), logs d'audit (suivre tous les changements pour conformité), application de règles métier (validation croisée de tables) et synchronisation de données dénormalisées (maintenir des compteurs). Chaque motif résout un vrai problème mais ajoute complexité et overhead. Considérez les alternatives : defaults pour les timestamps, audit au niveau application, contraintes CHECK pour les règles simples, vues matérialisées pour la dénormalisation. Utilisez les triggers quand la logique doit vivre dans la base (plusieurs apps accèdent aux données, ou la règle est non négociable).

postgresql
# 1. Auto-update updated_at
CREATE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

# 2. Audit log (track all changes)
CREATE FUNCTION audit() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, op, old_data, new_data, changed_by, changed_at)
  VALUES (TG_TABLE_NAME, TG_OP,
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END,
    current_user, now());
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

# 3. Enforce business rules
CREATE FUNCTION check_budget() RETURNS trigger AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM departments d
    WHERE d.id = NEW.dept_id AND d.budget < NEW.amount
  ) THEN
    RAISE EXCEPTION 'Amount exceeds department budget';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# 4. Sync denormalized data
CREATE FUNCTION update_post_count() RETURNS trigger AS $$
BEGIN
  UPDATE users SET post_count = (
    SELECT count(*) FROM posts WHERE user_id = NEW.user_id
  ) WHERE id = NEW.user_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
13

Procédures stockées et PL/pgSQL

Fonctions PL/pgSQL

PL/pgSQL est le langage procédural de PostgreSQL — il ajoute variables, conditionnelles, boucles et gestion d'erreurs au SQL. Les fonctions retournent une valeur ou une table. SELECT INTO affecte des résultats de requête à des variables. RETURN QUERY retourne des lignes d'une requête. Les paramètres peuvent avoir des défauts. Utilisez les fonctions pour encapsuler la logique réutilisable, les requêtes complexes et les règles métier. Gardez les fonctions simples et bien nommées. Pour la performance, considérez les fonctions SQL (LANGUAGE sql) pour les requêtes simples — l'optimiseur peut les inliner.

postgresql
# basic function
CREATE OR REPLACE FUNCTION add(a integer, b integer)
RETURNS integer AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

# function with variables and logic
CREATE OR REPLACE FUNCTION get_user_post_count(user_id integer)
RETURNS integer AS $$
DECLARE
  post_count integer;
BEGIN
  SELECT count(*) INTO post_count
  FROM posts WHERE posts.user_id = get_user_post_count.user_id;

  IF post_count IS NULL THEN
    RETURN 0;
  END IF;

  RETURN post_count;
END;
$$ LANGUAGE plpgsql;

# function returning a table (set-returning)
CREATE OR REPLACE FUNCTION get_recent_posts(limit_count integer DEFAULT 10)
RETURNS TABLE(id integer, title text, created_at timestamptz) AS $$
BEGIN
  RETURN QUERY
  SELECT posts.id, posts.title, posts.created_at
  FROM posts
  ORDER BY posts.created_at DESC
  LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

# call functions
SELECT get_user_post_count(42);
SELECT * FROM get_recent_posts(5);

Structures de contrôle

PL/pgSQL a toutes les structures de contrôle : IF/ELSIF/ELSE, CASE, LOOP/EXIT/CONTINUE, WHILE, FOR (plages d'entiers) et FOREACH (itération de tableaux). Utilisez EXIT WHEN pour sortir des boucles. FOR i IN 1..n itère des plages (inclusives). FOREACH itère les tableaux. EXECUTE exécute du SQL dynamique (avec format() pour l'injection sûre d'identifiants). Gardez les boucles en base petites — les grandes boucles devraient être du SQL ensembliste autant que possible. Pour les opérations en masse, utilisez INSERT/UPDATE avec SELECT plutôt que des boucles ligne par ligne.

postgresql
# IF/ELSIF/ELSE
CREATE OR REPLACE FUNCTION categorize_price(price numeric)
RETURNS text AS $$
BEGIN
  IF price IS NULL THEN RETURN 'unknown';
  ELSIF price < 10 THEN RETURN 'cheap';
  ELSIF price < 50 THEN RETURN 'moderate';
  ELSE RETURN 'expensive';
  END IF;
END;
$$ LANGUAGE plpgsql;

# CASE expression (inside BEGIN...END)
CREATE OR REPLACE FUNCTION get_discount(tier text)
RETURNS numeric AS $$
BEGIN
  RETURN CASE tier
    WHEN 'gold'   THEN 0.20
    WHEN 'silver' THEN 0.10
    WHEN 'bronze' THEN 0.05
    ELSE 0.00
  END;
END;
$$ LANGUAGE plpgsql;

# LOOP (with EXIT)
CREATE OR REPLACE FUNCTION find_first_null(table_name text)
RETURNS integer AS $$
DECLARE i integer := 1; val text;
BEGIN
  LOOP
    EXECUTE format('SELECT col FROM %I WHERE id = %s', table_name, i) INTO val;
    EXIT WHEN val IS NULL;
    i := i + 1;
    EXIT WHEN i > 1000;  -- safety limit
  END LOOP;
  RETURN i;
END;
$$ LANGUAGE plpgsql;

# WHILE and FOR loops
CREATE OR REPLACE FUNCTION sum_n(n integer)
RETURNS integer AS $$
DECLARE total integer := 0;
BEGIN
  FOR i IN 1..n LOOP
    total := total + i;
  END LOOP;
  RETURN total;
END;
$$ LANGUAGE plpgsql;

Procédures stockées (PG11+)

Les procédures (PG11+) diffèrent des fonctions : elles peuvent gérer des transactions (COMMIT/ROLLBACK à l'intérieur), ne retournent pas de valeurs comme les fonctions (utilisez plutôt des paramètres OUT) et s'appellent avec CALL. Utilisez les procédures pour les opérations multi-instructions exigeant du contrôle de transaction (ETL, migrations de données). Utilisez les fonctions pour les calculs et requêtes retournant des valeurs. Les procédures sont la façon standard SQL d'encapsuler la logique transactionnelle. Avant PG11, les fonctions ne pouvaient pas commiter/annuler — les procédures comblent ce vide.

postgresql
# PROCEDURE (unlike FUNCTION, can manage transactions)
CREATE OR REPLACE PROCEDURE transfer_money(
  from_account integer,
  to_account integer,
  amount numeric
) AS $$
BEGIN
  -- inside a procedure, we can use COMMIT/ROLLBACK
  UPDATE accounts SET balance = balance - amount WHERE id = from_account;
  UPDATE accounts SET balance = balance + amount WHERE id = to_account;

  -- check for negative balance
  IF EXISTS (SELECT 1 FROM accounts WHERE id = from_account AND balance < 0) THEN
    ROLLBACK;
    RAISE EXCEPTION 'Insufficient funds';
  END IF;

  COMMIT;
END;
$$ LANGUAGE plpgsql;

# call a procedure (CALL, not SELECT)
CALL transfer_money(1, 2, 100.00);

# procedure with output parameters
CREATE OR REPLACE PROCEDURE get_stats(
  OUT user_count integer,
  OUT post_count integer
) AS $$
BEGIN
  SELECT count(*) INTO user_count FROM users;
  SELECT count(*) INTO post_count FROM posts;
END;
$$ LANGUAGE plpgsql;

CALL get_stats(?, ?);  -- output params

Gestion des erreurs

RAISE EXCEPTION avorte la transaction avec une erreur personnalisée. Les blocs EXCEPTION attrapent les erreurs (comme try/catch) — courants pour la dégradation gracieuse. SQLSTATE est le code d'erreur à 5 caractères ; SQLERRM le message. USING HINT/DETAIL ajoute un contexte utile. RAISE NOTICE est parfait pour le débogage (visible dans psql). Attrapez des erreurs précises (division_by_zero, unique_violation) plutôt que OTHERS pour éviter de masquer de vrais bugs. Dans EXCEPTION, la transaction est dans un état avorté — on ne peut que commit/rollback, pas exécuter plus de requêtes (jusqu'aux procédures PG11+).

postgresql
# RAISE for messages and exceptions
CREATE OR REPLACE FUNCTION validate_age(age integer)
RETURNS void AS $$
BEGIN
  IF age < 0 THEN
    RAISE EXCEPTION 'Age cannot be negative: %', age
      USING HINT = 'Please provide a valid age (0-150)';
  ELSIF age > 150 THEN
    RAISE EXCEPTION 'Age seems unrealistic: %', age;
  ELSE
    RAISE NOTICE 'Age validated: %', age;
  END IF;
END;
$$ LANGUAGE plpgsql;

# TRY/CATCH with EXCEPTION block
CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric AS $$
DECLARE result numeric;
BEGIN
  result := a / b;
  RETURN result;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Division by zero, returning NULL';
    RETURN NULL;
  WHEN OTHERS THEN
    RAISE NOTICE 'Unexpected error: %', SQLERRM;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

# RAISE levels: DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION
# EXCEPTION aborts the transaction; others just log

# get error details
SELECT sqlstate, message, detail, hint, context
FROM pg_stat_activity;  -- or in exception handler: SQLSTATE, SQLERRM

SQL dynamique

Le SQL dynamique (EXECUTE) exécute des chaînes comme SQL — essentiel quand les noms de tables/colonnes sont variables. Utilisez toujours format() avec %I (identifiants) et %L (littéraux) pour prévenir l'injection SQL — ne concaténez jamais la saisie utilisateur directement ! USING passe les paramètres en sécurité (comme les prepared statements). Le SQL dynamique est puissant mais opaque au planificateur et aux outils d'analyse statique. Utilisez-le quand nécessaire (noms de tables dynamiques, génération de DDL) mais préférez le SQL statique quand possible. Testez à fond — les erreurs de SQL dynamique sont à l'exécution, pas à la compilation.

postgresql
# EXECUTE for dynamic queries
CREATE OR REPLACE FUNCTION count_rows(table_name text)
RETURNS integer AS $$
DECLARE result integer;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I', table_name) INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

# format() for safe SQL construction
#   %I = identifier (table/column name, quoted)
#   %L = literal (value, quoted)
#   %s = simple string substitution (UNSAFE — avoid for user input)
SELECT format('SELECT * FROM %I WHERE id = %L', 'users', 42);
-- 'SELECT * FROM users WHERE id = 42'

# dynamic SQL with USING for parameters
CREATE OR REPLACE FUNCTION get_user_by(field text, value text)
RETURNS setof users AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT * FROM users WHERE %I = $1', field
  ) USING value;
END;
$$ LANGUAGE plpgsql;

# bulk operations with dynamic SQL
CREATE OR REPLACE FUNCTION archive_old_data(days_old integer)
RETURNS integer AS $$
DECLARE deleted_count integer;
BEGIN
  EXECUTE format(
    'WITH deleted AS (
       DELETE FROM logs WHERE created_at < now() - interval '%s days'
       RETURNING *
     ) SELECT count(*) FROM deleted'
  ) INTO deleted_count USING days_old;

  RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;
14

Sécurité et permissions

Utilisateurs et rôles

Dans PostgreSQL, utilisateurs et rôles sont la même entité — un « user » n'est qu'un rôle avec le privilège LOGIN. Créez des rôles de groupe (sans LOGIN) pour gérer les permissions de façon centrale, puis GRANT-les aux rôles de login. VALID UNTIL fixe l'expiration du mot de passe. CONNECTION LIMIT plafonne les connexions concurrentes par rôle. SET search_path par rôle est excellent pour l'isolation multi-tenant. Utilisez toujours des rôles pour le groupement de permissions plutôt que d'accorder aux utilisateurs individuels. DROP ROLE exige d'abord de retirer tous ses privilèges et appartenances.

postgresql
# create a role (users are roles that can log in)
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';
CREATE ROLE analyst WITH LOGIN PASSWORD 'secret' VALID UNTIL '2025-12-31';

# create a role that cannot log in (for grouping)
CREATE ROLE readonly;

# alter a role
ALTER ROLE app_user WITH PASSWORD 'new_secret';
ALTER ROLE app_user WITH VALID UNTIL 'infinity';
ALTER ROLE app_user CONNECTION LIMIT 10;
ALTER ROLE app_user SET search_path TO app_schema, public;

# grant role to another (inherit permissions)
GRANT readonly TO app_user;
GRANT readonly TO analyst;

# view roles
\du  -- in psql
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;

# drop a role (must revoke privileges first)
REVOKE readonly FROM app_user;
DROP ROLE app_user;

Accorder des privilèges

Privilèges : SELECT/INSERT/UPDATE/DELETE/TRUNCATE/REFERENCES/TRIGGER pour les tables ; USAGE/CREATE pour les schémas ; USAGE/SELECT pour les séquences ; EXECUTE pour les fonctions ; CONNECT/CREATE/TEMPORARY pour les bases. Les grants au niveau colonne sont puissants pour la sécurité. ALTER DEFAULT PRIVILEGES accorde les permissions sur les objets futurs — essentiel quand de nouvelles tables sont créées fréquemment. Accordez toujours USAGE de séquence aux rôles qui insèrent dans des tables à colonnes serial/identity. Révoquez les privilèges excédentaires pour un accès au moindre privilège.

postgresql
# grant table privileges
GRANT SELECT, INSERT, UPDATE ON products TO app_user;
GRANT ALL ON products TO admin;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

# grant column-level privileges
GRANT SELECT (id, name, email) ON users TO app_user;
-- app_user can only see those columns

# grant schema privileges
GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT CREATE ON SCHEMA app_schema TO developer;

# grant sequence privileges (for serial/identity)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

# grant database privileges
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT CREATE ON DATABASE mydb TO developer;
GRANT TEMPORARY ON DATABASE mydb TO app_user;

# grant function privileges
GRANT EXECUTE ON FUNCTION get_user_post_count(integer) TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;

# revoke privileges
REVOKE INSERT, UPDATE ON products FROM app_user;

# alter default privileges (for future objects)
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
  IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

Row Level Security (RLS)

Row Level Security (RLS) impose le contrôle d'accès par ligne — les utilisateurs ne voient que les lignes correspondant à la politique. USING filtre les lectures ; WITH CHECK valide les écritures. Réglez des variables de session propres à l'app (SET app.user_id) pour identifier l'utilisateur courant. FORCE RLS soumet même le propriétaire de table aux politiques. L'attribut de rôle BYPASSRLS saute tout le RLS (pour les admins). RLS est puissant pour les apps multi-tenant — pas besoin de vues par utilisateur. Attention : les politiques RLS doivent être correctes, car elles filtrent en silence. Testez à fond avec différents utilisateurs.

postgresql
# enable RLS on a table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

# create a policy (users see only their own posts)
CREATE POLICY user_posts ON posts
  FOR SELECT
  USING (user_id = current_setting('app.user_id')::int);

# policy for all operations
CREATE POLICY user_posts_all ON posts
  USING (user_id = current_setting('app.user_id')::int)
  WITH CHECK (user_id = current_setting('app.user_id')::int);

# USING:    filter existing rows (SELECT, UPDATE, DELETE)
# WITH CHECK: validate new rows (INSERT, UPDATE)

# set the user context per session
SET app.user_id = '42';
SELECT * FROM posts;  -- only sees user 42's posts

# bypass RLS (for admins)
ALTER TABLE posts FORCE ROW LEVEL SECURITY;  -- even owner respects RLS
GRANT BYPASSRLS TO admin_role;  -- role-level bypass

# policy for admins (see all)
CREATE POLICY admin_all ON posts
  FOR ALL
  TO admin_role
  USING (true)
  WITH CHECK (true);

# disable RLS
ALTER TABLE posts DISABLE ROW LEVEL SECURITY;

Authentification et SSL

pg_hba.conf est la première ligne de défense — il contrôle quels hôtes/utilisateurs peuvent se connecter et comment. Utilisez toujours scram-sha-256 (pas md5 ni trust). hostssl exige SSL pour la connexion. sslmode=require garantit le chiffrement ; verify-full vérifie aussi le certificat (le plus sûr). En production : SSL pour toutes les connexions distantes, restreignez pg_hba.conf aux IP connues, et scram-sha-256. Rechargez avec pg_reload_conf() après édition. Surveillez pg_stat_activity pour les connexions non autorisées.

postgresql
# pg_hba.conf controls who can connect and how
# host  database  user  address  method
#   host all all 127.0.0.1/32 scram-sha-256
#   host all all 0.0.0.0/0   scram-sha-256
#   hostssl all all 0.0.0.0/0 scram-sha-256  # require SSL

# reload pg_hba.conf
SELECT pg_reload_conf();

# authentication methods:
#   trust:       no password (NEVER use in production)
#   md5:         legacy password hash (deprecated)
#   scram-sha-256:  modern password auth (default, recommended)
#   cert:        client certificate
#   peer:        OS username matches DB username (local only)

# set password encryption
SET password_encryption = 'scram-sha-256';
ALTER ROLE app_user PASSWORD 'secret';  -- re-hash with scram

# SSL connections
SHOW ssl;                  -- is SSL enabled?
SHOW ssl_cert_file;
SHOW ssl_key_file;

# connect with SSL
psql "postgresql://user@host/db?sslmode=require"
# sslmode: disable, allow, prefer, require, verify-ca, verify-full

# view active connections
SELECT pid, usename, client_addr, ssl, ssl_cipher
FROM pg_stat_activity;

Chiffrement et audit

pgcrypto fournit le chiffrement au niveau colonne : pgp_sym_encrypt/decrypt pour un chiffrement réversible, crypt/gen_salt pour le hash de mots de passe. Utilisez bcrypt (bf) pour les mots de passe — lent par conception (résiste au brute force). Pour la conformité, pgAudit logue toutes les opérations DDL et DML dans le log serveur. Pour les données sensibles, préférez le chiffrement à la couche application (les clés ne doivent pas vivre dans la base). Hash toujours les mots de passe (jamais de chiffrement réversible). Surveillez pg_stat_activity pour auditer les requêtes actives. Combinez ces outils pour une défense en profondeur.

postgresql
# encrypt columns with pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
  id serial PRIMARY KEY,
  email text,
  ssn bytea  -- encrypted
);

INSERT INTO users (email, ssn) VALUES
  ('[email protected]', pgp_sym_encrypt('123-45-6789', 'secret_key'));

SELECT pgp_sym_decrypt(ssn, 'secret_key') AS ssn FROM users;

# one-way hashing (passwords)
SELECT crypt('password', gen_salt('bf'));      -- bcrypt
SELECT crypt('password', gen_salt('md5'));     -- MD5 (weak)
-- store the hash, verify with:
SELECT crypt('password', stored_hash) = stored_hash;

# audit with pgAudit extension
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'write, ddl';
SELECT pg_reload_conf();
-- logs all writes and DDL to the PostgreSQL log

# session auditing
SELECT
  pid, usename, application_name, client_addr,
  query_start, state, query
FROM pg_stat_activity
WHERE state = 'active';
15

Backup et restauration

Bases de pg_dump

pg_dump crée un backup logique (instructions SQL pour recréer la base). Le format custom (-F c) est compressé et supporte la restauration parallèle et sélective — préférez-le toujours. Le format directory (-F d) permet dump et restore parallèles. pg_dumpall backupe toutes les bases plus rôles et tablespaces (pour les backups de cluster complets). --clean --if-exists rend les dumps idempotents (restauration multiple possible). Pour les grandes bases, considérez --jobs pour le dump parallèle (format directory seulement).

postgresql
# backup a single database
pg_dump -h localhost -U postgres -d mydb -F c -f mydb.dump
# -F c: custom format (compressed, supports parallel restore)
# -F p: plain SQL (human-readable, default)
# -F d: directory format (parallel, multiple files)

# backup with options
pg_dump -d mydb -F c -f mydb.dump \
  --no-owner           -- don't include ownership commands
  --no-privileges      -- don't include GRANT/REVOKE
  --schema=app_schema  -- only this schema
  --table=users        -- only this table
  --data-only          -- only data, no schema
  --schema-only        -- only schema, no data
  --clean              -- include DROP statements
  --if-exists          -- use IF EXISTS in DROPs

# backup all databases
pg_dumpall -h localhost -U postgres -f all_databases.sql
# includes roles, tablespaces, and all databases

# compress a plain SQL dump
pg_dump -d mydb | gzip > mydb.sql.gz

# backup a remote database
pg_dump "postgresql://user:pass@remote-host:5432/mydb" -F c -f mydb.dump

pg_restore

pg_restore restaure depuis les formats custom/directory (pas le SQL pur — utilisez psql pour cela). --jobs=N active la restauration parallèle (bien plus rapide en multi-cœur). --clean --if-exists supprime les objets existants d'abord. --no-owner est essentiel pour restaurer vers un autre utilisateur. --list montre le contenu du dump ; --table/--schema pour une restauration sélective. Pour les grandes restaurations, désactivez triggers et contraintes temporairement, puis réactivez. Testez toujours les restaurations — un backup irréstaurable ne vaut rien.

postgresql
# restore from custom format
pg_restore -h localhost -U postgres -d mydb -F c mydb.dump

# restore with options
pg_restore -d mydb mydb.dump \
  --clean              -- drop objects before recreating
  --if-exists          -- use IF EXISTS in DROPs
  --no-owner           -- don't restore ownership
  --schema=app_schema  -- only this schema
  --table=users        -- only this table
  --data-only          -- only data
  --jobs=4             -- parallel restore (4 threads)
  --verbose

# restore to a new database
createdb newdb
pg_restore -d newdb mydb.dump

# restore from plain SQL
psql -d mydb -f mydb.sql
gunzip -c mydb.sql.gz | psql -d mydb

# list contents of a dump
pg_restore -l mydb.dump

# restore specific items
pg_restore -d mydb mydb.dump --table=users --table=posts

# generate SQL to inspect (without restoring)
pg_restore mydb.dump > mydb.sql
pg_restore --list mydb.dump  -- list items with indices

Backup physique et PITR

pg_basebackup crée une copie binaire de tout le répertoire de données — plus rapide que pg_dump pour les grandes bases et supporte la PITR. L'archivage WAL (Write-Ahead Log) est essentiel à la PITR : archivez tous les fichiers WAL, puis restaurez le backup de base + rejouez le WAL jusqu'à n'importe quel point dans le temps. recovery_target_time permet de récupérer à un timestamp précis (annuler des erreurs comme un DROP TABLE accidentel). pg_create_restore_point crée une cible de récupération nommée. La PITR est l'étalon-or du backup — combinez avec pg_dump pour les backups logiques.

postgresql
# physical backup with pg_basebackup (online)
pg_basebackup -h localhost -U repluser -D /backup/base \
  -Ft -z -P          -- tar format, compressed, progress
  --wal-method=stream  -- stream WAL files separately

# set up WAL archiving (postgresql.conf)
#   archive_mode = on
#   archive_command = 'cp %p /archive/%f'
SELECT pg_reload_conf();

# Point-in-Time Recovery (PITR):
# 1. Restore base backup
# 2. Create recovery.signal file
# 3. Configure recovery (postgresql.conf or recovery.conf):
#      restore_command = 'cp /archive/%f %p'
#      recovery_target_time = '2025-01-15 14:30:00'
#      recovery_target_action = 'promote'
# 4. Start PostgreSQL — it replays WAL to the target time

# create a recovery point
SELECT pg_create_restore_point('before_migration');

# check WAL archiving status
SELECT * FROM pg_stat_archiver;

Mise en place de la réplication

La réplication par streaming : le primaire envoie le WAL aux réplicas en temps réel. pg_basebackup -R met en place un réplica en une commande. Les réplicas sont en lecture seule (hot standby) et peuvent servir des requêtes de lecture. Surveillez le lag de réplication (pg_stat_replication sur le primaire, pg_last_xact_replay_timestamp sur le réplica). La promotion (pg_ctl promote) fait d'un réplica un primaire — pour le failover. Pour un failover automatique, utilisez des outils comme Patroni, repmgr ou pg_auto_failover. La réplication en cascade réduit la charge du primaire en chaînant les réplicas.

postgresql
# on primary (postgresql.conf):
#   wal_level = replica
#   max_wal_senders = 10
#   hot_standby = on  (for replica)

# create replication role
CREATE ROLE repluser WITH REPLICATION LOGIN PASSWORD 'secret';

# in pg_hba.conf, allow replication connections:
#   host replication repluser 0.0.0.0/0 scram-sha-256

# on replica, create standby using pg_basebackup:
pg_basebackup -h primary-host -U repluser -D /var/lib/postgresql/data \
  -Fp -Xs -P -R
# -R creates standby.signal and configures primary_conninfo

# monitor replication
# on primary:
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_current_wal_lsn();

# on replica:
SELECT * FROM pg_stat_wal_receiver;
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

# promote a replica to primary (failover)
pg_ctl promote -D /var/lib/postgresql/data

# cascading replication
#   replica can also be a primary to other replicas
#   set cascade_level or use primary_conninfo pointing to another replica

Réplication logique

La réplication logique (PG10+) réplique les changements au niveau table — plus flexible que la réplication physique : versions PostgreSQL différentes, tables sélectives, schémas différents de chaque côté. Le publisher crée une PUBLICATION ; le subscriber une SUBSCRIPTION. Les données initiales se copient, puis les changements streament en temps réel. Les conflits (ex. INSERT avec PK existante) arrêtent la réplication — gérez avec SKIP ou des correctifs manuels. Parfait pour les mises à niveau sans interruption (répliquer de l'ancienne vers la nouvelle version, puis basculer). Utilisez-la pour le partage de données entre bases ou la réplication partielle.

postgresql
# logical replication (PG10+) — replicate specific tables
# on publisher:
CREATE PUBLICATION my_pub FOR TABLE users, posts;

# add more tables to the publication
ALTER PUBLICATION my_pub ADD TABLE comments;

# on subscriber:
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=publisher-host user=repluser dbname=mydb'
  PUBLICATION my_pub;

# initial data copy happens automatically
# subsequent changes are replicated in real-time

# monitor logical replication
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_replication_slots;

# replicate only specific operations
CREATE PUBLICATION insert_only FOR TABLE logs WITH (publish = 'insert');

# control conflict resolution
# (subscriber applies changes; conflicts cause replication to stop)
# handle with: ALTER SUBSCRIPTION ... SKIP or manual resolution

# cross-version replication (e.g., PG14 -> PG16)
# logical replication works across major versions!

# remove
DROP SUBSCRIPTION my_sub;
DROP PUBLICATION my_pub;
16

Performance et réglages

EXPLAIN

EXPLAIN montre le plan de requête ; ANALYZE exécute la requête et montre les timings réels. Cherchez Seq Scan sur les grandes tables (index manquant), un grand « Rows Removed by Filter » (requête inefficace) et les tris externes (augmentez work_mem). Index Only Scan est le plus rapide (exige un covering index). Bitmap Scan est entre Seq Scan et Index Scan. Les chiffres de « cost » sont des estimations du planificateur ; « actual time » est réel. Utilisez toujours EXPLAIN (ANALYZE, BUFFERS) pour les réglages — il révèle le vrai goulot. Comparez lignes estimées vs réelles pour repérer les statistiques périmées.

postgresql
# basic EXPLAIN (query plan without running)
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

# EXPLAIN ANALYZE (actually runs the query, shows real stats)
EXPLAIN ANALYZE SELECT * FROM users JOIN posts ON users.id = posts.user_id;

# with buffer stats
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table WHERE id = 42;

# with format (JSON, TEXT, XML, YAML)
EXPLAIN (FORMAT JSON) SELECT * FROM users;

# common plan nodes:
#   Seq Scan:        full table scan (slow for large tables)
#   Index Scan:      index lookup + heap fetch
#   Index Only Scan: index-only (fastest, needs covering index)
#   Bitmap Scan:     bitmap of matching rows, then fetch
#   Hash Join:       hash one table, probe with other
#   Nested Loop:     for each left row, scan right (small datasets)
#   Sort:            explicit sort (look for "Sort Method: external merge" = bad)

# warning signs:
#   "Seq Scan on large_table" -> missing index
#   "Sort Method: external disk" -> increase work_mem
#   "Rows Removed by Filter: 999999" -> bad filter, missing index

VACUUM et ANALYZE

Le MVCC de PostgreSQL crée des lignes mortes au UPDATE/DELETE — VACUUM les récupère. L'autovacuum tourne automatiquement (gardez-le ON !) mais peut exiger un réglage pour les tables à fortes écritures. VACUUM FULL réécrit la table (récupère l'espace pour l'OS) mais prend un verrou exclusif — utilisez plutôt pg_repack pour la réorganisation en ligne. ANALYZE met à jour les statistiques utilisées par le planificateur — critique après les gros chargements. Des statistiques périmées causent de mauvais plans. Réglez autovacuum_scale_factor par table (plus bas pour les grandes tables à fortes écritures). Surveillez n_dead_tup pour détecter le retard de vacuum.

postgresql
# VACUUM (reclaim space from dead rows, don't lock)
VACUUM users;            -- regular vacuum
VACUUM FULL users;       -- rewrites table (locks, reclaims to OS)
VACUUM ANALYZE users;    -- vacuum + update statistics
VACUUM (VERBOSE, ANALYZE) users;  -- with output

# autovacuum (should be ON in production)
SHOW autovacuum;  -- should be on
SHOW autovacuum_vacuum_threshold;     -- default 50
SHOW autovacuum_vacuum_scale_factor;  -- default 0.2 (20% dead rows)

# tune autovacuum per table
ALTER TABLE users SET (
  autovacuum_vacuum_scale_factor = 0.1,  -- vacuum at 10% dead rows
  autovacuum_analyze_scale_factor = 0.05
);

# ANALYZE (update planner statistics — critical for query plans)
ANALYZE users;         -- sample the table
ANALYZE;               -- all tables

# check dead tuples (need vacuuming)
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

# check autovacuum activity
SELECT pid, datname, query
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%';

Mémoire et configuration

shared_buffers est le cache partagé de PostgreSQL (25 % de la RAM est la recommandation classique). effective_cache_size informe le planificateur du cache OS (50-75 % de la RAM, ce n'est qu'un indice). work_mem est par opération de tri/hash — trop élevé avec beaucoup de connexions peut épuiser la mémoire. maintenance_work_mem accélère VACUUM et CREATE INDEX. Pour les SSD, réglez random_page_cost=1.1 (le défaut 4.0 suppose des HDD lents). Ces réglages affectent drastiquement la performance — benchmarkez toujours les changements.

postgresql
# key memory settings (in postgresql.conf)
SHOW shared_buffers;        -- default 128MB (set to 25% of RAM)
SHOW effective_cache_size;  -- default 4GB (set to 50-75% of RAM)
SHOW work_mem;              -- default 4MB (per-sort/hash memory)
SHOW maintenance_work_mem;  -- default 64MB (for VACUUM, CREATE INDEX)

# recommended production settings (example for 16GB RAM):
#   shared_buffers = 4GB
#   effective_cache_size = 12GB
#   work_mem = 64MB          (careful: per-operation, not global)
#   maintenance_work_mem = 512MB
#   max_connections = 100
#   random_page_cost = 1.1   (for SSDs; default 4.0 is for HDDs)

# check current connections vs max
SELECT count(*), state FROM pg_stat_activity GROUP BY state;
SHOW max_connections;

# WAL settings for write performance
SHOW wal_buffers;        -- default -1 (auto-tuned to 1/32 shared_buffers)
SHOW checkpoint_timeout; -- default 5min
SHOW max_wal_size;       -- default 1GB

# parallel query
SHOW max_parallel_workers;        -- default 8
SHOW max_parallel_workers_per_gather; -- default 2
SET max_parallel_workers_per_gather = 4;  -- more parallelism

# reload config
SELECT pg_reload_conf();

Optimisation des requêtes

Les plus gros gains : indexez les colonnes FK, utilisez COPY pour les chargements en masse, évitez OFFSET pour la pagination (keyset), et n'enveloppez pas les colonnes indexées dans des fonctions. EXISTS est souvent plus rapide que IN pour les sous-requêtes. SELECT * empêche les index-only scans et gaspille l'I/O. Les inserts batchés (multi-lignes ou COPY) sont 10 à 100x plus rapides que les inserts individuels. Après les gros chargements, lancez ANALYZE pour que le planificateur ait des statistiques précises. Faites toujours EXPLAIN (ANALYZE) avant et après les changements pour vérifier les améliorations.

postgresql
# 1. use indexes (check with EXPLAIN)
# bad:  SELECT * FROM users WHERE lower(email) = '[email protected]'
# good: SELECT * FROM users WHERE email = '[email protected]'
#   (or use an expression index on lower(email))

# 2. avoid SELECT * (reduces I/O, enables index-only scans)
SELECT id, username FROM users WHERE active = true;

# 3. use EXISTS instead of IN for subqueries
-- BAD:  SELECT * FROM users WHERE id IN (SELECT user_id FROM posts)
-- GOOD: SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM posts WHERE user_id = u.id)

# 4. batch inserts (much faster than row-by-row)
INSERT INTO logs (msg) VALUES ('a'), ('b'), ('c');  -- one statement
-- or use COPY for bulk loads:
COPY logs FROM '/path/to/file.csv' WITH (FORMAT csv);

# 5. use CTEs or subqueries for readability, but check performance
# (PG12+ inlines CTEs; before that, CTEs were optimization fences)

# 6. avoid OFFSET for pagination (slow for large offsets)
-- BAD:  SELECT * FROM posts ORDER BY id OFFSET 100000 LIMIT 10
-- GOOD: SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 10

# 7. use partial indexes for common filtered queries
CREATE INDEX idx_active_users ON users(last_login) WHERE active = true;

# 8. analyze after bulk loads
ANALYZE products;  -- update statistics for the planner

Surveiller les requêtes

pg_stat_statements est l'outil de monitoring le plus précieux — il suit les comptages de requêtes, temps total/moyen et lignes. Activez-le dans shared_preload_libraries. pg_stat_activity montre les requêtes en temps réel (cherchez les longues durées et « idle in transaction »). Surveillez le pourcentage de tuples morts pour l'enflure. Trouvez les index inutilisés (idx_scan=0) à supprimer — ils gaspillent l'espace et ralentissent les écritures. Réglez log_min_duration_statement pour loguer automatiquement les requêtes lentes. Combinez ces outils avec EXPLAIN pour identifier et corriger les goulots systématiquement.

postgresql
# find slow queries (needs pg_stat_statements extension)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
  query,
  calls,
  mean_exec_time AS avg_ms,
  total_exec_time AS total_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

# currently running queries
SELECT
  pid,
  now() - query_start AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

# long-running transactions (block vacuum)
SELECT
  pid,
  now() - xact_start AS transaction_duration,
  state,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

# table sizes (find bloat candidates)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup, n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

# index usage (find unused indexes)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
17

Partitionnement

Partitionnement par plages

Le partitionnement par plages convient aux séries temporelles. PostgreSQL route automatiquement les inserts vers la bonne partition et élague les partitions dans les requêtes (ne scanne que les pertinentes). Créez une partition DEFAULT pour attraper les données hors plage (ou planifiez un job de partitionnement pour créer les partitions futures). Chaque partition est une table séparée — on peut les gérer indépendamment (supprimer les vieilles données avec DROP TABLE au lieu d'un DELETE lent). Le sous-partitionnement (plage puis hash) marche pour les très grands jeux. Automatisez la création avec l'extension pg_partman.

postgresql
# partition by date range (most common for time-series)
CREATE TABLE orders (
  id          bigserial,
  created_at  timestamptz NOT NULL,
  user_id     integer NOT NULL,
  amount      numeric
) PARTITION BY RANGE (created_at);

# create monthly partitions
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders_2025_02 PARTITION OF orders
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

# create a default partition (catches anything outside range)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

# insert (automatically routed to correct partition)
INSERT INTO orders (created_at, user_id, amount)
VALUES ('2025-01-15', 1, 100.00);  -- goes to orders_2025_01

# query (partition pruning — only scans relevant partitions)
SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

# sub-partition (e.g., range by date, then hash by user)
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
  PARTITION BY HASH (user_id);

Partitionnement par listes

Le partitionnement par listes sert aux valeurs discrètes (régions, catégories, IDs de tenants). Chaque partition détient les lignes de valeurs précises. Comme le partitionnement par plages, PostgreSQL élague les partitions dans les requêtes. Génial pour les apps multi-tenant — les données de chaque tenant sont isolées, peuvent être backupées/restaurées indépendamment et déplacées vers un stockage différent. Utilisez DEFAULT pour les valeurs inattendues. Pour ajouter une partition à une nouvelle valeur, CREATE TABLE ... FOR VALUES IN (...). Détachez une partition avec ALTER TABLE ... DETACH PARTITION (utile pour l'archivage).

postgresql
# partition by discrete values (e.g., region, category)
CREATE TABLE users (
  id       serial,
  name     text,
  region   text NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE users_us PARTITION OF users
  FOR VALUES IN ('US', 'CA');

CREATE TABLE users_eu PARTITION OF users
  FOR VALUES IN ('UK', 'DE', 'FR', 'ES');

CREATE TABLE users_apac PARTITION OF users
  FOR VALUES IN ('CN', 'JP', 'AU', 'SG');

CREATE TABLE users_other PARTITION OF users DEFAULT;

# insert routes automatically
INSERT INTO users (name, region) VALUES ('Alice', 'US');  -- users_us

# query prunes to relevant partition
SELECT * FROM users WHERE region = 'JP';  -- only scans users_apac

# multi-region apps benefit: partition by tenant
CREATE TABLE tenant_data (
  id          serial,
  tenant_id   integer NOT NULL,
  data        jsonb
) PARTITION BY LIST (tenant_id);

CREATE TABLE tenant_1 PARTITION OF tenant_data FOR VALUES IN (1);
CREATE TABLE tenant_2 PARTITION OF tenant_data FOR VALUES IN (2);

Partitionnement par hachage

Le partitionnement par hachage distribue les lignes uniformément entre les partitions en hachant la clé de partition — génial pour répartir la charge. Les requêtes filtrant sur la clé de partition (WHERE user_id = 42) élagent vers une partition ; les requêtes par plage (WHERE user_id > 100) scannent toutes. Le nombre de partitions est fixé à la création (le modulus doit être une puissance de 2) — en ajouter ensuite exige de recréer la table. Choisissez le compte de partitions selon la taille de données attendue. Utilisez le hash quand vous avez besoin d'une distribution uniforme et interrogez toujours par la clé de partition.

postgresql
# partition by hash (distribute evenly)
CREATE TABLE events (
  id          bigserial,
  user_id     integer NOT NULL,
  event_type  text,
  data        jsonb
) PARTITION BY HASH (user_id);

# create 4 partitions (modulus 4, remainder 0-3)
CREATE TABLE events_0 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_1 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_2 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_3 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

# insert distributes by hash of user_id
INSERT INTO events (user_id, event_type) VALUES (42, 'login');  -- routes to a partition

# query by user_id prunes to one partition
SELECT * FROM events WHERE user_id = 42;

# note: range queries on user_id scan ALL partitions
SELECT * FROM events WHERE user_id > 100;  -- scans all 4

# adding partitions later requires recreating (modulus must be power of 2)
# plan partition count upfront!

Maintenance des partitions

DETACH PARTITION transforme une partition en table autonome — parfait pour archiver les vieilles données sans verrouiller le parent. ATTACH PARTITION ajoute une table existante comme nouvelle partition (schéma et contraintes doivent matcher). Utilisez les tablespaces pour le stockage par paliers (déplacer les vieilles partitions vers des disques moins chers). pg_partman automatise la création et maintenance des partitions — essentiel pour les séries temporelles. Constraint exclusion (réglé à 'partition' par défaut) est le mécanisme d'élagage. Planifiez toujours la création des partitions à l'avance — manquer de partitions futures cause des échecs d'INSERT.

postgresql
# detach a partition (keep data, remove from parent)
ALTER TABLE orders DETACH PARTITION orders_2024_01;
-- now orders_2024_01 is a standalone table

# detach and archive
ALTER TABLE orders DETACH PARTITION orders_2024_01;
ALTER TABLE orders_2024_01 RENAME TO orders_archive_2024_01;
-- move to cheaper storage, export, etc.

# attach an existing table as a partition
CREATE TABLE orders_2025_03 (LIKE orders);
ALTER TABLE orders ATTACH PARTITION orders_2025_03
  FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

# move partition to different tablespace (tiered storage)
CREATE TABLESPACE cold_storage LOCATION '/mnt/cold';
ALTER TABLE orders_2024_01 SET TABLESPACE cold_storage;

# create future partitions automatically (pg_partman extension)
CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.orders', 'created_at', 'native', 'monthly');
-- pg_partman auto-creates future partitions

# constraint exclusion (older partitioning, avoid for new work)
SET constraint_exclusion = on;  -- default 'partition' is usually fine

# check partition info
SELECT
  inhrelid::regclass AS partition,
  pg_get_expr(c.relpartbound, c.oid) AS partition_bounds
FROM pg_inherits
JOIN pg_class c ON inhrelid = c.oid
WHERE inhparent = 'orders'::regclass;

Stratégie de partitionnement

Partitionnez quand les tables deviennent grandes (>100 Go) ou quand vous avez besoin d'un cycle de vie de données rapide (supprimer des partitions au lieu de DELETE). Choisissez la clé de partition selon votre motif de requête le plus courant — l'élagage ne marche que si vous filtrez sur la clé. Les contraintes uniques sur tables partitionnées DOIVENT inclure la clé de partition (PG l'impose). Les index sur tables partitionnées s'appliquent automatiquement à toutes les partitions. Évitez trop de partitions (overhead de planification). Pour les séries temporelles, les partitions mensuelles sont un point d'équilibre courant. Utilisez pg_partman pour l'automatisation.

postgresql
# when to partition:
#   - table > 100GB (hard to vacuum, slow queries)
#   - time-series data (drop old data fast)
#   - multi-tenant (isolate tenants)
#   - parallel maintenance (vacuum per partition)

# choosing partition key:
#   - most common filter in queries (enables pruning)
#   - time for time-series (created_at, ordered_at)
#   - tenant_id for multi-tenant

# choosing partition type:
#   - RANGE: time-series, ordered data
#   - LIST: discrete categories (region, tenant)
#   - HASH: even distribution, always query by key

# partition count:
#   - too few:  doesn't help (large partitions)
#   - too many: planning overhead (1000+ partitions slow planning)
#   - sweet spot: 50-500 partitions

# indexes on partitioned tables
CREATE INDEX idx_orders_user ON orders(user_id);  -- creates index on all partitions
CREATE INDEX idx_orders_created ON orders(created_at);

# unique constraint must include partition key
CREATE TABLE orders (...) PARTITION BY RANGE (created_at);
ALTER TABLE orders ADD CONSTRAINT unique_order
  UNIQUE (id, created_at);  -- must include created_at!

# foreign keys TO partitioned tables (PG12+)
ALTER TABLE order_items ADD FOREIGN KEY (order_id, order_date)
  REFERENCES orders(id, created_at);
18

Fonctionnalités avancées

Recherche plein texte

PostgreSQL a une recherche plein texte intégrée : tsvector (texte prétraité), tsquery (termes de recherche) et l'opérateur @@. Les index GIN rendent les recherches rapides. to_tsvector tokenise et racinise les mots (anglais, chinois, etc. — configurez la langue). ts_rank score les résultats ; ts_headline surligne les matchs. Le tsvector_update_trigger maintient automatiquement la colonne de recherche. Pour une recherche simple, cela rivalise avec Elasticsearch — aucun service externe requis. Pour une recherche complexe à grande échelle, considérez Elasticsearch/OpenSearch, mais commencez par le FTS de PostgreSQL.

postgresql
# create a search column (tsvector)
ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector =
  to_tsvector('english', title || ' ' || body);

# index the search vector (GIN)
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

# search
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & index');

# match any word (OR)
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('postgres | mysql');

# phrase search
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('full text search');

# rank results
SELECT
  title,
  ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query
ORDER BY rank DESC;

# highlight matches
SELECT
  ts_headline('english', body, query) AS highlighted
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query;

# auto-update search_vector with a trigger
CREATE TRIGGER articles_search_update
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);

Requêtes JSONB avancées

Les expressions de chemin JSONB (PG12+) utilisent le langage de chemin SQL/JSON ($) pour des requêtes puissantes — filtres, prédicats et extraction en une expression. jsonb_agg et jsonb_build_object construisent du JSON depuis des données relationnelles — parfait pour les réponses API. L'index GIN jsonb_path_ops est plus petit et plus rapide que le GIN par défaut mais ne supporte que @> et les opérateurs de chemin (@?, @@). Utilisez jsonb_path_query pour extraire les éléments de tableau correspondants, et jsonb_agg pour bâtir des tableaux JSON depuis des lignes — parfait pour générer des réponses API directement en SQL sans ORM.

postgresql
# JSONB path queries
SELECT data#>>'{address,city}' FROM users;  -- text at path

# JSON Path (PG12+) — powerful JSON querying
SELECT * FROM products
WHERE data @? '$.tags[*] ? (@ == "sale")';

# find products with price > 100 in JSON
SELECT * FROM products
WHERE data @? '$.price ? (@ > 100)';

# extract values with JSON Path
SELECT jsonb_path_query(data, '$.tags[*]') FROM products;

# JSONB aggregation (build JSON from rows)
SELECT
  jsonb_agg(jsonb_build_object('id', id, 'name', name)) AS products
FROM products
WHERE category = 'electronics';

# group by JSON field
SELECT
  data->>'category' AS category,
  count(*) AS count
FROM products
GROUP BY data->>'category'
ORDER BY count DESC;

# JSONB containment and existence
SELECT * FROM products WHERE data @> '{"color": "red"}';
SELECT * FROM products WHERE data ? 'discount';
SELECT * FROM products WHERE data ?| ARRAY['discount', 'sale'];
SELECT * FROM products WHERE data ?& ARRAY['discount', 'sale'];

# JSONB with GIN index for fast queries
CREATE INDEX idx_products_data ON products USING GIN (data jsonb_path_ops);
# jsonb_path_ops: smaller index, supports @> and path operators

Géospatial avec PostGIS

PostGIS transforme PostgreSQL en base spatiale complète — ajoutez-le avec CREATE EXTENSION. Utilisez geography pour les coordonnées GPS (lat/lng) et geometry pour les coordonnées projetées. ST_DWithin trouve les points dans une distance ; ST_Distance mesure précisément. L'opérateur <-> active les requêtes KNN (plus proches voisins) qui utilisent efficacement l'index GiST. Créez toujours un index GiST sur les colonnes geometry/geography. Notez que ST_MakePoint prend (lng, lat) — longitude d'abord ! Pour des charges géospatiales sérieuses, PostGIS rivalise avec les systèmes GIS dédiés.

postgresql
-- enable the PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- create a table with geography (lat/lng)
CREATE TABLE places (
  id serial PRIMARY KEY,
  name text,
  location geography(POINT, 4326)
);

-- insert a point (lng, lat — note the order!)
INSERT INTO places (name, location)
VALUES ('Eiffel Tower', ST_MakePoint(2.2945, 48.8584)::geography);

-- find places within 5 km
SELECT name, ST_Distance(location, ST_MakePoint(2.3522, 48.8566)::geography) AS meters
FROM places
WHERE ST_DWithin(location, ST_MakePoint(2.3522, 48.8566)::geography, 5000);

-- bounding box query (uses GiST index)
SELECT name FROM places
WHERE location && ST_MakeEnvelope(2.2, 48.8, 2.4, 48.9, 4326);

-- find the nearest 10 places (KNN query)
SELECT name, location <-> ST_MakePoint(2.35, 48.85)::geography AS dist
FROM places
ORDER BY dist
LIMIT 10;

-- compute the area of a polygon (in square meters)
SELECT ST_Area(ST_GeomFromText(
  'POLYGON((2.3 48.8, 2.4 48.8, 2.4 48.9, 2.3 48.9, 2.3 48.8))', 4326)::geography
) AS sq_meters;

Foreign Data Wrappers (FDW)

Les Foreign Data Wrappers (FDW) permettent d'interroger des sources de données externes comme si elles étaient des tables locales. postgres_fdw se connecte à d'autres serveurs PostgreSQL ; file_fdw lit les fichiers CSV. D'autres FDW existent pour MySQL, Oracle, SQLite, API REST, etc. Les requêtes sont poussées vers le serveur distant quand possible (WHERE, JOIN), mais les jointures inter-serveurs ramènent les données localement. FDW est génial pour l'intégration de données, migrations et requêtes fédérées sans ETL. La performance est plus lente que les tables locales à cause de l'overhead réseau.

postgresql
-- enable the postgres_fdw extension (foreign PostgreSQL)
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- create a foreign server
CREATE SERVER foreign_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote.host', dbname 'remotedb', port '5432');

-- map a local user to the foreign server
CREATE USER MAPPING FOR local_user
  SERVER foreign_db
  OPTIONS (user 'remote_user', password 'secret');

-- import a schema from the foreign server
IMPORT FOREIGN SCHEMA public
  FROM SERVER foreign_db
  INTO remote_schema;

-- query a foreign table like a local one
SELECT * FROM remote_schema.users WHERE active = true;

-- file_fdw: read CSV files as tables
CREATE EXTENSION file_fdw;
CREATE SERVER csv_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE logs (
  id int, level text, message text, ts timestamp
) SERVER csv_server
  OPTIONS (filename '/var/log/app.csv', format 'csv', header 'true');

-- join local and remote data
SELECT l.id, u.name FROM local_orders l
  JOIN remote_schema.users u ON l.user_id = u.id;

Verrous advisory et concurrence

Les verrous advisory sont des verrous définis par l'application, non liés à une ligne de table — parfaits pour la coordination. Les verrous de session persistent jusqu'à libération explicite ou fin de session ; les verrous de transaction se libèrent au COMMIT/ROLLBACK. pg_try_advisory_lock est non bloquant — génial pour les motifs « un seul worker ». Combinez avec FOR UPDATE SKIP LOCKED pour un traitement sûr des files de tâches. Les verrous s'identifient par bigint ou deux ints. Associez toujours lock et unlock dans les chemins d'erreur. Les verrous advisory sont le socle des systèmes de tâches d'arrière-plan concurrents sûrs dans PostgreSQL.

postgresql
-- acquire a session-level advisory lock (held until session ends)
SELECT pg_advisory_lock(12345);

-- release it
SELECT pg_advisory_unlock(12345);

-- transaction-level lock (auto-released on COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(67890);
-- critical section here
COMMIT;  -- lock auto-released

-- try-lock (non-blocking, returns true/false)
SELECT pg_try_advisory_lock(12345);  -- true if acquired, false if held

-- two-key variant (namespace + id)
SELECT pg_advisory_lock(100, 42);

-- use case: ensure only one worker runs a job
SELECT CASE
  WHEN pg_try_advisory_lock(999) THEN run_job()
  ELSE 'skipped: another worker is running'
END;

-- use case: per-row processing lock
SELECT id, pg_advisory_lock(hashtext(id::text))
FROM tasks WHERE status = 'pending'
LIMIT 10 FOR UPDATE SKIP LOCKED;
19

Extensions

Gestion des extensions

Les extensions ajoutent des fonctionnalités au-delà du cœur de PostgreSQL — installez avec CREATE EXTENSION. pg_available_extensions montre ce qui est installé sur disque ; pg_extension ce qui est chargé. Les extensions s'installent par base. Certaines (comme pg_stat_statements) exigent aussi une entrée de config dans shared_preload_libraries. CASCADE supprime les objets dépendants. Épinglez toujours les versions d'extensions en production pour la reproductibilité. Les extensions sont une force clé de PostgreSQL — elles en font une plateforme, pas juste une base.

postgresql
-- list all available extensions
SELECT * FROM pg_available_extensions ORDER BY name;

-- list installed extensions
SELECT * FROM pg_extension;

-- install an extension (requires superuser or privileges)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- install in a specific schema
CREATE EXTENSION postgis SCHEMA geo;

-- update an extension to a new version
ALTER EXTENSION pg_stat_statements UPDATE TO '1.10';

-- remove an extension
DROP EXTENSION IF EXISTS postgis CASCADE;

-- check extension versions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

pg_stat_statements (profiling de requêtes)

pg_stat_statements est l'extension PostgreSQL la plus précieuse — elle enregistre les statistiques d'exécution de chaque requête. Trouvez les requêtes lentes par total_exec_time (impact) ou mean_exec_time (coût par requête). Un rows/call élevé suggère des index manquants. L'extension normalise les requêtes (en remplaçant les littéraux par $1) pour que les requêtes similaires s'agrègent. Réinitialisez les stats après les déploiements pour suivre une performance fraîche. C'est le premier outil à saisir pour optimiser — il montre exactement où va le temps.

postgresql
-- enable in postgresql.conf first:
-- shared_preload_libraries = 'pg_stat_statements'
-- then restart PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- find the slowest queries by total time
SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- find queries with highest average time
SELECT query, calls, round(mean_exec_time, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- find queries that scan the most rows
SELECT query, rows, calls, rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY rows DESC
LIMIT 10;

-- reset stats (e.g., after a deploy)
SELECT pg_stat_statements_reset();

pgcrypto (chiffrement et hash)

pgcrypto fournit le hash (digest, HMAC), la génération d'UUID (gen_random_uuid) et le chiffrement (PGP symétrique/asymétrique). gen_random_uuid() est la façon standard de générer un UUID v4 (intégré depuis PG13, mais pgcrypto l'apporte aux versions antérieures). Pour le stockage de mots de passe, préférez bcrypt ou argon2 dans le code applicatif — le hash de pgcrypto sert à l'intégrité des données, pas au stockage de mots de passe. Le chiffrement PGP convient pour chiffrer des colonnes au repos. Gérez toujours les clés de chiffrement hors de la base en production.

postgresql
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- hash a password with SHA-256
SELECT encode(digest('mypassword', 'sha256'), 'hex');

-- generate a UUID v4
SELECT gen_random_uuid();

-- HMAC (keyed hashing)
SELECT encode(hmac('message', 'secretkey', 'sha256'), 'hex');

-- encrypt data with PGP symmetric encryption
SELECT armor(pgp_sym_encrypt('sensitive data', 'my_passphrase'))
  AS encrypted_blob;

-- decrypt data
SELECT pgp_sym_decrypt(
  dearmor('-----BEGIN PGP MESSAGE-----...-----END PGP MESSAGE-----'),
  'my_passphrase'
) AS decrypted;

-- store encrypted columns
CREATE TABLE secrets (
  id serial PRIMARY KEY,
  data bytea  -- store pgp_sym_encrypt() output here
);

INSERT INTO secrets (data)
VALUES (pgp_sym_encrypt('credit card number', 'passphrase'));

UUID et génération d'ID

Utilisez gen_random_uuid() (PG13+) pour des UUID aléatoires — intégré, sans extension. uuid-ossp fournit les UUID v1 (temporels) et v5 (namespace) au besoin. Pour les entiers auto-incrémentés, préférez GENERATED ALWAYS AS IDENTITY (PG10+) à serial — standard SQL, prévient les inserts manuels accidentels et gère proprement les permissions. RETURNING id obtient l'ID inséré en un aller-retour. Les UUID sont parfaits pour les systèmes distribués ; les colonnes identity plus simples pour les apps mono-nœud.

postgresql
-- built-in UUID v4 (PostgreSQL 13+)
SELECT gen_random_uuid();

-- using uuid-ossp extension (for other UUID versions)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1();  -- time-based
SELECT uuid_generate_v4();  -- random
SELECT uuid_generate_v5('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'name'); -- namespace+name

-- UUID primary key
CREATE TABLE events (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  data jsonb,
  created_at timestamptz DEFAULT now()
);

-- identity columns (PostgreSQL 10+, preferred over serial)
CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

-- compare: serial vs identity
-- serial: old, separate sequence, can be overridden
-- identity: SQL standard, tied to table, cleaner

-- get the last inserted ID
INSERT INTO users (name) VALUES ('Alice') RETURNING id;

Catalogue d'extensions utiles

L'écosystème d'extensions de PostgreSQL est vaste. pg_trgm active le matching flou de texte (opérateur %) et les requêtes LIKE rapides. PostGIS ajoute le géospatial. pg_partman automatise la création de partitions pour les séries temporelles. TimescaleDB et Citus sont des extensions tierces pour séries temporelles et scaling horizontal respectivement. hstore est legacy — utilisez jsonb. unaccent retire les diacritiques pour la recherche internationalisée. Parcourez les extensions disponibles avec pg_available_extensions, et consultez PGXN (PostgreSQL Extension Network) pour les extensions communautaires.

postgresql
-- PostGIS: geospatial data
CREATE EXTENSION postgis;

-- pg_trgm: trigram fuzzy text search
CREATE EXTENSION pg_trgm;
SELECT * FROM users WHERE name % 'Jon';  -- fuzzy match
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

-- btree_gin / btree_gist: B-tree support in GIN/GiST
CREATE EXTENSION btree_gin;

-- hstore: key-value store (legacy, use jsonb instead)
CREATE EXTENSION hstore;

-- pg_partman: partition management
CREATE EXTENSION pg_partman;

-- timescaledb: time-series optimization (third-party)
-- citus: distributed PostgreSQL (third-party)

-- intarray: integer array operations
CREATE EXTENSION intarray;
SELECT * FROM posts WHERE tags && ARRAY[1,2,3];

-- unaccent: remove accents for search
CREATE EXTENSION unaccent;
SELECT unaccent('café');  -- 'cafe'
20

Monitoring et dépannage

Requêtes actives (pg_stat_activity)

pg_stat_activity est votre vue temps réel de ce que fait PostgreSQL. Vérifiez state (active, idle, idle in transaction). Les requêtes longues et les sessions idle-in-transaction sont des problèmes courants — les secondes détiennent des verrous et empêchent le vacuum. pg_cancel_backend envoie un SIGINT (la requête s'annule, la session survit) ; pg_terminate_backend tue la session entièrement. Investiguez toujours avant de tuer. Réglez idle_in_transaction_session_timeout pour tuer automatiquement les transactions bloquées. Surveillez les comptes de connexions pour ne pas atteindre max_connections.

postgresql
-- see all active queries
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  wait_event_type,
  wait_event,
  query_start,
  now() - query_start AS duration,
  query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- find long-running queries (> 5 minutes)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes';

-- count connections by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- terminate a runaway query (use with caution!)
SELECT pg_cancel_backend(12345);     -- cancel the query
SELECT pg_terminate_backend(12345);  -- kill the session

-- find idle-in-transaction sessions (hold locks)
SELECT pid, usename, xact_start,
  now() - xact_start AS xact_duration,
  query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

Verrous et blocage

La contention de verrous est un problème de production courant. pg_locks montre tous les verrous actifs ; les verrous non accordés signifient qu'une session attend. La requête de chaîne de blocage identifie exactement qui bloque qui — essentielle pour déboguer deadlocks et requêtes lentes. AccessExclusive (issu d'ALTER TABLE, DROP, VACUUM FULL) bloque tout — exécutez dans des fenêtres de maintenance. Utilisez lock_timeout pour éviter que des sessions n'attendent indéfiniment. Les transactions longues sont le coupable habituel — gardez-les courtes et commitez promptement.

postgresql
-- see all locks
SELECT
  pid,
  relation::regclass AS table_name,
  mode,
  granted,
  query
FROM pg_locks
WHERE granted = false;

-- find blocking chains (who blocks whom)
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks ul ON ul.locktype = bl.locktype
  AND ul.relation IS NOT DISTINCT FROM bl.relation
  AND ul.granted
JOIN pg_stat_activity blocking ON ul.pid = blocking.pid;

-- lock modes: AccessShare, RowShare, RowExclusive,
--   ShareUpdateExclusive, Share, ShareRowExclusive,
--   Exclusive, AccessExclusive

-- check for table-level locks
SELECT relation::regclass, mode, pid, granted
FROM pg_locks
WHERE locktype = 'relation' AND granted = false;

-- set a lock timeout for a session
SET lock_timeout = '5s';
SET statement_timeout = '30s';

Vacuum et enflure

PostgreSQL utilise MVCC — UPDATE/DELETE créent des tuples morts que VACUUM récupère. L'autovacuum tourne automatiquement mais peut exiger un réglage pour les tables grandes ou à fortes écritures. n_dead_tup montre le nettoyage en attente ; un dead_pct élevé signifie que l'autovacuum ne suit pas. VACUUM FULL récupère l'espace disque mais prend un verrou AccessExclusive — utilisez pg_repack pour retirer l'enflure en ligne. Les transactions longues bloquent le vacuum (elles voient les vieilles lignes), causant enflure et risque de wraparound d'ID de transaction. Surveillez xid_age pour prévenir le wraparound (échec forcé vers 2 milliards).

postgresql
-- check table bloat (dead tuples)
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
  last_autovacuum,
  last_manual_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- manual vacuum (analyze updates planner stats)
VACUUM ANALYZE users;
VACUUM FULL users;  -- reclaims space, locks table!

-- check autovacuum settings
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;

-- tune autovacuum per table
ALTER TABLE big_table SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

-- check if any transactions prevent vacuum
SELECT pid, age(clock_timestamp(), xact_start) AS xact_age, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
ORDER BY xact_age DESC;

-- check wraparound risk (transaction ID age)
SELECT
  relname,
  age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind IN ('r', 't', 'm')
ORDER BY xid_age DESC LIMIT 10;

Analyse des logs

Le logging se configure dans postgresql.conf. log_min_duration_statement logue les requêtes lentes (0 pour toutes, 100 pour >100 ms). log_lock_waits attrape la contention de verrous. Le logging CSV permet d'interroger les logs comme table étrangère (file_fdw). Préfixes courants : %t (timestamp), %p (PID), %u (utilisateur), %d (base). En production, utilisez un collecteur de logs comme pgBadger pour parser et visualiser. Ne loguez pas chaque requête en production (overhead I/O) — utilisez pg_stat_statements pour l'analyse agrégée.

postgresql
-- in postgresql.conf, enable slow query logging:
-- log_min_duration_statement = 100  -- log queries > 100ms
-- log_line_prefix = '%t [%p] %u@%d '
-- log_lock_waits = on
-- log_temp_files = 0
-- log_autovacuum_min_duration = 0

-- check current log settings
SHOW log_min_duration_statement;
SHOW log_destination;

-- view server logs (if using CSV logging)
CREATE FOREIGN TABLE pg_log (
  log_time timestamp,
  user_name text,
  database_name text,
  process_id int,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos int,
  context text,
  query text,
  query_pos int,
  location text,
  file_name text,
  file_line_num int,
  application_name text
) SERVER pglog
  OPTIONS (filename '/var/log/postgresql/postgres.csv', format 'csv');

-- query logs with SQL
SELECT log_time, error_severity, message
FROM pg_log
WHERE error_severity = 'ERROR'
ORDER BY log_time DESC LIMIT 20;

Health checks et métriques

Métriques de santé clés : ratio de hit du cache (visez >99 % — s'il est bas, augmentez shared_buffers), ratio d'usage des index (bas signifie index manquants ou mauvaises requêtes), tailles de tables (attraper l'enflure tôt) et comptes de connexions. pg_stat_user_tables et pg_statio_user_tables sont des mines de données opérationnelles. Mettez en place le monitoring avec Prometheus + postgres_exporter pour les dashboards. Surveillez : tuples morts croissants, ratio de hit du cache en déclin, connexions croissantes, et tables croissant plus vite que prévu. Des health checks réguliers préviennent les urgences de minuit.

postgresql
-- database size
SELECT pg_size_pretty(pg_database_size('mydb'));

-- table sizes (with indexes)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- cache hit ratio (should be > 99%)
SELECT
  sum(heap_blks_hit) AS hits,
  sum(heap_blks_read) AS reads,
  round(sum(heap_blks_hit)::numeric /
    NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) AS hit_ratio_pct
FROM pg_statio_user_tables;

-- index usage ratio
SELECT
  sum(idx_scan) AS index_scans,
  sum(seq_scan) AS seq_scans,
  round(sum(idx_scan)::numeric / NULLIF(sum(idx_scan) + sum(seq_scan), 0) * 100, 2) AS index_usage_pct
FROM pg_stat_user_tables;

-- connection stats
SELECT count(*) AS total,
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity;

-- uptime and version
SELECT version();
SELECT pg_postmaster_start_time();

Was this helpful?