PostgreSQL – Requêtes SQL avancées et optimisation

Guide de référence complet pour PostgreSQL : requêtes avancées, optimisation des performances, indexation et bonnes pratiques.

Table des matières

Types de données
Requêtes de base avancées
Jointures (JOINS)
Sous-requêtes et CTE
Fonctions de fenêtre (Window Functions)
Index et optimisation
Transactions et verrouillage
Fonctions d’agrégation avancées
JSON et JSONB
Performance et EXPLAIN

—

Types de données

Types numériques

Types texte

VARCHAR(n)      -- Longueur variable avec limite
CHAR(n)         -- Longueur fixe (padded)
TEXT            -- Longueur illimitée (recommandé)

Types date et heure

DATE                    -- Date seule (YYYY-MM-DD)
TIME                    -- Heure seule (HH:MM:SS)
TIMESTAMP              -- Date et heure sans timezone
TIMESTAMPTZ            -- Date et heure avec timezone (recommandé)
INTERVAL               -- Durée temporelle

Types spéciaux PostgreSQL

UUID                   -- Identifiant unique universel
JSONB                  -- JSON binaire (indexable, recommandé)
JSON                   -- JSON texte
ARRAY                  -- Tableau de valeurs
HSTORE                 -- Paires clé-valeur
INET                   -- Adresse IP
CIDR                   -- Bloc réseau
MACADDR               -- Adresse MAC
BOOLEAN               -- TRUE/FALSE/NULL

—

Requêtes de base avancées

SELECT avec opérateurs avancés

-- Recherche de motifs
SELECT  FROM users WHERE email LIKE '%@gmail.com';
SELECT  FROM users WHERE email ILIKE '%@GMAIL.COM';  -- Insensible à la casse
SELECT  FROM users WHERE name ~ '^[A-Z]';  -- Regex POSIX
SELECT  FROM users WHERE name ~ '^john';  -- Regex insensible

-- Opérateurs de comparaison
SELECT  FROM products WHERE price BETWEEN 10 AND 100;
SELECT  FROM orders WHERE status IN ('pending', 'processing');
SELECT  FROM products WHERE category IS NOT NULL;
SELECT  FROM users WHERE createdat > NOW() - INTERVAL '7 days';

-- ANY et ALL
SELECT  FROM products WHERE price > ALL (SELECT price FROM products WHERE category = 'budget');
SELECT  FROM products WHERE price = ANY (ARRAY[10, 20, 30]);

-- EXISTS
SELECT  FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customerid = c.id);

-- DISTINCT ON (PostgreSQL spécifique)
SELECT DISTINCT ON (customerid) 
FROM orders
ORDER BY customerid, createdat DESC;

CASE et logique conditionnelle

-- CASE simple
SELECT
    productname,
    price,
    CASE
        WHEN price < 20 THEN 'Bon marché'
        WHEN price < 100 THEN 'Moyen'
        ELSE 'Cher'
    END AS pricecategory
FROM products;

-- CASE avec agrégation
SELECT
    category,
    COUNT() as total,
    COUNT(CASE WHEN price < 50 THEN 1 END) as cheapcount,
    COUNT(CASE WHEN price >= 50 THEN 1 END) as expensivecount
FROM products
GROUP BY category;

-- COALESCE pour valeurs par défaut
SELECT name, COALESCE(phone, email, 'No contact') as contact FROM users;

-- NULLIF
SELECT productname, NULLIF(discount, 0) as validdiscount FROM products;

Tri et limitation

-- ORDER BY avancé
SELECT  FROM products ORDER BY price DESC, name ASC;
SELECT  FROM products ORDER BY price DESC NULLS LAST;

-- LIMIT et OFFSET (pagination)
SELECT  FROM products ORDER BY id LIMIT 20 OFFSET 40;  -- Page 3

-- FETCH (SQL standard)
SELECT  FROM products ORDER BY id OFFSET 40 ROWS FETCH FIRST 20 ROWS ONLY;

—

Jointures (JOINS)

Types de jointures

-- INNER JOIN (intersection)
SELECT u.name, o.orderdate, o.total
FROM users u
INNER JOIN orders o ON u.id = o.userid;

-- LEFT JOIN (tous les enregistrements de gauche)
SELECT u.name, COUNT(o.id) as ordercount
FROM users u
LEFT JOIN orders o ON u.id = o.userid
GROUP BY u.id, u.name;

-- RIGHT JOIN (tous les enregistrements de droite)
SELECT u.name, o.orderdate
FROM users u
RIGHT JOIN orders o ON u.id = o.userid;

-- FULL OUTER JOIN (tous les enregistrements)
SELECT u.name, o.orderdate
FROM users u
FULL OUTER JOIN orders o ON u.id = o.userid;

-- CROSS JOIN (produit cartésien)
SELECT c.name, s.size
FROM colors c
CROSS JOIN sizes s;

Jointures multiples

-- Chaîne de jointures
SELECT
    u.name,
    o.orderdate,
    p.productname,
    oi.quantity,
    oi.price
FROM users u
INNER JOIN orders o ON u.id = o.userid
INNER JOIN orderitems oi ON o.id = oi.orderid
INNER JOIN products p ON oi.productid = p.id
WHERE o.status = 'completed';

Jointures avec conditions complexes

-- Jointure avec conditions multiples
SELECT 
FROM orders o
LEFT JOIN discounts d ON o.userid = d.userid
    AND o.createdat BETWEEN d.validfrom AND d.validto
    AND o.total >= d.minimumamount;

-- Auto-jointure
SELECT
    e1.name as employee,
    e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.managerid = e2.id;

LATERAL JOIN (jointures latérales)

-- Obtenir les 3 dernières commandes par client
SELECT
    c.name,
    recentorders.orderdate,
    recentorders.total
FROM customers c
LEFT JOIN LATERAL (
    SELECT orderdate, total
    FROM orders
    WHERE customerid = c.id
    ORDER BY orderdate DESC
    LIMIT 3
) recentorders ON true;

—

Sous-requêtes et CTE

Sous-requêtes scalaires

-- Sous-requête dans SELECT
SELECT
    name,
    price,
    (SELECT AVG(price) FROM products) as avgprice,
    price - (SELECT AVG(price) FROM products) as pricediff
FROM products;

-- Sous-requête dans WHERE
SELECT 
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Sous-requêtes corrélées

-- Produits plus chers que la moyenne de leur catégorie
SELECT p1.name, p1.category, p1.price
FROM products p1
WHERE p1.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = p1.category
);

CTE (Common Table Expressions) – WITH

-- CTE simple
WITH highvaluecustomers AS (
    SELECT
        customerid,
        SUM(total) as lifetimevalue
    FROM orders
    GROUP BY customerid
    HAVING SUM(total) > 1000
)
SELECT
    c.name,
    c.email,
    hvc.lifetimevalue
FROM highvaluecustomers hvc
JOIN customers c ON hvc.customerid = c.id;

-- CTE multiples
WITH
monthlysales AS (
    SELECT
        DATETRUNC('month', orderdate) as month,
        SUM(total) as revenue
    FROM orders
    GROUP BY DATETRUNC('month', orderdate)
),
avgmonthly AS (
    SELECT AVG(revenue) as avgrevenue
    FROM monthlysales
)
SELECT
    ms.month,
    ms.revenue,
    am.avgrevenue,
    ms.revenue - am.avgrevenue as variance
FROM monthlysales ms
CROSS JOIN avgmonthly am
ORDER BY ms.month;

CTE récursives

-- Hiérarchie d'employés (organigramme)
WITH RECURSIVE employeehierarchy AS (
    -- Ancre : top-level managers
    SELECT
        id,
        name,
        managerid,
        1 as level,
        name::TEXT as path
    FROM employees
    WHERE managerid IS NULL

    UNION ALL

    -- Récursion : subordonnés
    SELECT
        e.id,
        e.name,
        e.managerid,
        eh.level + 1,
        eh.path || ' > ' || e.name
    FROM employees e
    INNER JOIN employeehierarchy eh ON e.managerid = eh.id
)
SELECT  FROM employeehierarchy ORDER BY path;

-- Génération de séries numériques
WITH RECURSIVE numbers AS (
    SELECT 1 as n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT  FROM numbers;

—

Fonctions de fenêtre (Window Functions)

Concepts de base

-- ROWNUMBER : numérotation séquentielle
SELECT
    name,
    category,
    price,
    ROWNUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank
FROM products;

-- RANK : classement avec égalités
SELECT
    name,
    score,
    RANK() OVER (ORDER BY score DESC) as rank,
    DENSERANK() OVER (ORDER BY score DESC) as denserank
FROM students;

-- NTILE : division en n groupes
SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;

Fonctions d’agrégation fenêtrées

-- SUM, AVG, COUNT avec fenêtre
SELECT
    orderdate,
    total,
    SUM(total) OVER (ORDER BY orderdate) as runningtotal,
    AVG(total) OVER (ORDER BY orderdate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as movingavg7days,
    COUNT() OVER (PARTITION BY customerid) as customerordercount
FROM orders;

-- MIN, MAX avec fenêtre
SELECT
    productid,
    saledate,
    price,
    MIN(price) OVER (PARTITION BY productid) as lowestprice,
    MAX(price) OVER (PARTITION BY productid) as highestprice
FROM sales;

Fonctions de valeur

-- LAG : valeur précédente
-- LEAD : valeur suivante
SELECT
    orderdate,
    total,
    LAG(total, 1) OVER (ORDER BY orderdate) as previoustotal,
    LEAD(total, 1) OVER (ORDER BY orderdate) as nexttotal,
    total - LAG(total, 1) OVER (ORDER BY orderdate) as difffromprevious
FROM orders;

-- FIRSTVALUE, LASTVALUE
SELECT
    employeename,
    department,
    salary,
    FIRSTVALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) as highestindept,
    LASTVALUE(salary) OVER (
        PARTITION BY department
        ORDER BY salary DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as lowestindept
FROM employees;

-- NTHVALUE
SELECT
    productname,
    price,
    NTHVALUE(price, 2) OVER (ORDER BY price DESC) as secondhighestprice
FROM products;

Clauses de fenêtre

-- ROWS vs RANGE
SELECT
    orderdate,
    total,
    -- ROWS : nombre physique de lignes
    SUM(total) OVER (ORDER BY orderdate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as sum3rows,
    -- RANGE : plage logique de valeurs
    SUM(total) OVER (ORDER BY orderdate RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW) as sum3days
FROM orders;

-- Frame clauses
-- UNBOUNDED PRECEDING : depuis le début
-- UNBOUNDED FOLLOWING : jusqu'à la fin
-- CURRENT ROW : ligne actuelle
-- n PRECEDING : n lignes avant
-- n FOLLOWING : n lignes après

—

Index et optimisation

Types d’index

-- B-Tree (par défaut, pour la plupart des cas)
CREATE INDEX idxusersemail ON users(email);
CREATE INDEX idxordersdate ON orders(orderdate);

-- Index composite
CREATE INDEX idxordersuserdate ON orders(userid, orderdate);

-- Index unique
CREATE UNIQUE INDEX idxusersemailunique ON users(email);

-- Index partiel (avec condition)
CREATE INDEX idxactiveusers ON users(lastlogin)
WHERE status = 'active';

-- Index d'expression
CREATE INDEX idxusersloweremail ON users(LOWER(email));
CREATE INDEX idxordersyear ON orders(EXTRACT(YEAR FROM orderdate));

-- GIN (pour recherche full-text, JSONB, arrays)
CREATE INDEX idxproductssearch ON products USING GIN(totsvector('french', name || ' ' || description));
CREATE INDEX idxtags ON articles USING GIN(tags);
CREATE INDEX idxmetadata ON products USING GIN(metadata);

-- GiST (pour données géométriques, recherche full-text)
CREATE INDEX idxlocations ON stores USING GIST(location);

-- BRIN (pour très grandes tables avec données ordonnées)
CREATE INDEX idxlogstimestamp ON logs USING BRIN(createdat);

-- Hash (pour égalité uniquement, rarement utilisé)
CREATE INDEX idxusersidhash ON users USING HASH(id);

Gestion des index

-- Lister les index
SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pgindexes
WHERE tablename = 'orders';

-- Taille des index
SELECT
    indexname,
    pgsizepretty(pgrelationsize(indexrelid)) as size
FROM pgstatuserindexes
WHERE schemaname = 'public'
ORDER BY pgrelationsize(indexrelid) DESC;

-- Supprimer un index
DROP INDEX IF EXISTS idxusersemail;

-- Reconstruire un index
REINDEX INDEX idxusersemail;
REINDEX TABLE orders;

-- Index concurrent (sans bloquer les écritures)
CREATE INDEX CONCURRENTLY idxuserscreated ON users(createdat);
DROP INDEX CONCURRENTLY idxoldindex;

Stratégies d’indexation

-- Index covering (include columns PostgreSQL 11+)
CREATE INDEX idxusersemailcovering ON users(email)
INCLUDE (name, createdat);

-- Index multi-colonnes : ordre important
-- Bon pour : WHERE userid = X AND status = Y
CREATE INDEX idxordersuserstatus ON orders(userid, status);

-- Moins bon pour : WHERE status = Y (n'utilise pas l'index)
-- Créer un index séparé si nécessaire :
CREATE INDEX idxordersstatus ON orders(status);

—

Transactions et verrouillage

Transactions de base

-- Transaction simple
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Rollback en cas d'erreur
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    -- Erreur détectée
ROLLBACK;

-- Savepoints
BEGIN;
    UPDATE products SET price = price  1.1;
    SAVEPOINT priceupdate;

    UPDATE products SET stock = 0 WHERE discontinued = true;
    -- Erreur : annuler seulement le stock
    ROLLBACK TO SAVEPOINT priceupdate;

COMMIT;

Niveaux d’isolation

-- READ UNCOMMITTED (non supporté, équivalent à READ COMMITTED)
-- READ COMMITTED (par défaut)
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- REPEATABLE READ
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SERIALIZABLE (le plus strict)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Verrouillage (Locking)

-- SELECT FOR UPDATE (verrouillage exclusif)
BEGIN;
    SELECT  FROM products WHERE id = 1 FOR UPDATE;
    -- Autres transactions doivent attendre
    UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- SELECT FOR SHARE (verrouillage partagé)
SELECT  FROM products WHERE id = 1 FOR SHARE;

-- NOWAIT : échec immédiat si verrouillé
SELECT  FROM products WHERE id = 1 FOR UPDATE NOWAIT;

-- SKIP LOCKED : ignorer les lignes verrouillées
SELECT  FROM queuejobs
WHERE status = 'pending'
ORDER BY priority DESC
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Verrouillage de table
LOCK TABLE products IN ACCESS EXCLUSIVE MODE;

Gestion des deadlocks

-- Toujours verrouiller dans le même ordre
-- MAUVAIS :
-- Transaction 1: UPDATE tablea; UPDATE tableb;
-- Transaction 2: UPDATE tableb; UPDATE tablea;  -- DEADLOCK!

-- BON :
-- Transaction 1: UPDATE tablea; UPDATE tableb;
-- Transaction 2: UPDATE tablea; UPDATE tableb;  -- OK

-- Utiliser des timeouts
SET locktimeout = '5s';
SET statementtimeout = '30s';

—

Fonctions d’agrégation avancées

Agrégations standards

-- COUNT, SUM, AVG, MIN, MAX
SELECT
    category,
    COUNT() as productcount,
    COUNT(DISTINCT supplierid) as suppliercount,
    SUM(stock) as totalstock,
    AVG(price) as avgprice,
    MIN(price) as minprice,
    MAX(price) as maxprice
FROM products
GROUP BY category;

-- HAVING
SELECT
    customerid,
    COUNT() as ordercount,
    SUM(total) as totalspent
FROM orders
GROUP BY customerid
HAVING COUNT() > 5 AND SUM(total) > 1000;

Agrégations statistiques

SELECT
    category,
    -- Écart-type
    STDDEV(price) as pricestddev,
    STDDEVPOP(price) as pricestddevpop,
    STDDEVSAMP(price) as pricestddevsamp,
    -- Variance
    VARIANCE(price) as pricevariance,
    VARPOP(price) as pricevarpop,
    VARSAMP(price) as pricevarsamp,
    -- Corrélation
    CORR(price, salescount) as pricesalescorrelation,
    -- Régression linéaire
    REGRSLOPE(salescount, price) as regressionslope,
    REGRINTERCEPT(salescount, price) as regressionintercept
FROM products
GROUP BY category;

Agrégations avancées

-- STRINGAGG : concaténation
SELECT
    orderid,
    STRINGAGG(productname, ', ' ORDER BY productname) as products
FROM orderitems
GROUP BY orderid;

-- ARRAYAGG : agrégation en tableau
SELECT
    category,
    ARRAYAGG(name ORDER BY price DESC) as products,
    ARRAYAGG(DISTINCT supplierid) as suppliers
FROM products
GROUP BY category;

-- JSONBAGG : agrégation en JSON
SELECT
    category,
    JSONBAGG(
        JSONBBUILDOBJECT(
            'name', name,
            'price', price,
            'stock', stock
        )
    ) as products
FROM products
GROUP BY category;

-- BOOLAND, BOOLOR
SELECT
    category,
    BOOLAND(instock) as allinstock,
    BOOLOR(instock) as anyinstock
FROM products
GROUP BY category;

GROUPING SETS, ROLLUP, CUBE

-- GROUPING SETS : groupements multiples
SELECT
    category,
    supplierid,
    SUM(sales) as totalsales
FROM products
GROUP BY GROUPING SETS (
    (category, supplierid),
    (category),
    (supplierid),
    ()
);

-- ROLLUP : hiérarchie de groupements
SELECT
    EXTRACT(YEAR FROM orderdate) as year,
    EXTRACT(MONTH FROM orderdate) as month,
    COUNT() as orders,
    SUM(total) as revenue
FROM orders
GROUP BY ROLLUP (year, month);

-- CUBE : toutes les combinaisons
SELECT
    category,
    brand,
    COUNT() as count
FROM products
GROUP BY CUBE (category, brand);

Filtres d’agrégation

-- FILTER clause (PostgreSQL 9.4+)
SELECT
    category,
    COUNT() as totalproducts,
    COUNT() FILTER (WHERE price < 50) as cheapproducts,
    COUNT() FILTER (WHERE price >= 50 AND price < 200) as mediumproducts,
    COUNT() FILTER (WHERE price >= 200) as expensiveproducts,
    AVG(price) FILTER (WHERE instock = true) as avgpriceinstock
FROM products
GROUP BY category;

—

JSON et JSONB

Créer du JSON

-- JSONBUILDOBJECT
SELECT JSONBUILDOBJECT(
    'id', id,
    'name', name,
    'email', email,
    'createdat', createdat
) as userjson
FROM users;

-- JSONBUILDARRAY
SELECT JSONBUILDARRAY(id, name, email) FROM users;

-- ROWTOJSON
SELECT ROWTOJSON(users) as userjson FROM users;

-- JSONAGG
SELECT JSONAGG(users) as allusers FROM users;

-- JSONBOBJECTAGG
SELECT JSONBOBJECTAGG(key, value) as settings
FROM usersettings
WHERE userid = 1;

Requêtes JSON

-- Opérateurs d'accès
SELECT
    metadata->>'name' as name,                    -- Texte
    metadata->'price' as pricejson,              -- JSON
    metadata->'tags'->0 as firsttag,            -- Élément tableau
    metadata#>'{address,city}' as city,          -- Chemin
    metadata#>>'{address,city}' as citytext     -- Chemin en texte
FROM products;

-- Opérateurs de test
SELECT  FROM products WHERE metadata ? 'featured';  -- Clé existe
SELECT  FROM products WHERE metadata ?| ARRAY['sale', 'new'];  -- Au moins une clé
SELECT  FROM products WHERE metadata ?& ARRAY['featured', 'instock'];  -- Toutes les clés

-- Opérateurs de contenance
SELECT  FROM products WHERE metadata @> '{"category": "electronics"}';
SELECT  FROM products WHERE '{"category": "electronics"}' <@ metadata;

-- Fonctions de requête
SELECT  FROM products WHERE JSONBEXISTS(metadata, 'featured');
SELECT  FROM products WHERE JSONBEXISTSANY(metadata, ARRAY['sale', 'new']);
SELECT  FROM products WHERE JSONBEXISTSALL(metadata, ARRAY['featured', 'instock']);

Manipulation JSON

-- Ajouter/modifier
UPDATE products
SET metadata = metadata || '{"featured": true}'::jsonb
WHERE id = 1;

UPDATE products
SET metadata = JSONBSET(metadata, '{price}', '29.99'::jsonb)
WHERE id = 1;

UPDATE products
SET metadata = JSONBINSERT(metadata, '{tags, 0}', '"new"'::jsonb)
WHERE id = 1;

-- Supprimer
UPDATE products
SET metadata = metadata - 'oldfield'
WHERE id = 1;

UPDATE products
SET metadata = metadata #- '{tags, 0}'  -- Supprimer par chemin
WHERE id = 1;

-- Extraire
SELECT JSONBEXTRACTPATH(metadata, 'address', 'city') FROM users;
SELECT JSONBEXTRACTPATHTEXT(metadata, 'name') FROM products;

Fonctions JSON avancées

-- JSONBEACH : décomposer en lignes
SELECT key, value
FROM products, JSONBEACH(metadata)
WHERE id = 1;

-- JSONBARRAYELEMENTS
SELECT JSONBARRAYELEMENTS(tags) as tag
FROM articles
WHERE id = 1;

-- JSONBARRAYLENGTH
SELECT id, JSONBARRAYLENGTH(metadata->'tags') as tagcount
FROM articles;

-- JSONBPRETTY : formater
SELECT JSONBPRETTY(metadata) FROM products WHERE id = 1;

-- JSONBSTRIPNULLS
SELECT JSONBSTRIPNULLS(metadata) FROM products;

—

Performance et EXPLAIN

EXPLAIN et EXPLAIN ANALYZE

-- EXPLAIN : plan d'exécution
EXPLAIN
SELECT  FROM orders WHERE userid = 123;

-- EXPLAIN ANALYZE : exécution réelle
EXPLAIN ANALYZE
SELECT  FROM orders WHERE userid = 123;

-- Options détaillées
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, COSTS, TIMING)
SELECT
    u.name,
    COUNT(o.id) as ordercount
FROM users u
LEFT JOIN orders o ON u.id = o.userid
GROUP BY u.id, u.name;

Interpréter EXPLAIN

Types de scan :
Seq Scan         : Scan complet (lent pour grandes tables)
Index Scan       : Utilise un index (rapide)
Index Only Scan  : Données dans l'index uniquement (très rapide)
Bitmap Scan      : Scan d'index multiples
CTE Scan         : Scan d'une CTE

Types de jointures :
Nested Loop      : Bon pour petites tables, index disponibles
Hash Join        : Bon pour tables moyennes, jointures d'égalité
Merge Join       : Bon pour grandes tables triées

Métriques importantes :
cost=0.00..100.00 : Coût estimé (début..total)
rows=1000         : Nombre de lignes estimé
width=32          : Largeur moyenne d'une ligne (octets)
actual time       : Temps réel d'exécution
Planning Time     : Temps de planification
Execution Time    : Temps d'exécution total

Statistiques de la base

-- Statistiques de tables
SELECT
    schemaname,
    tablename,
    pgsizepretty(pgtotalrelationsize(schemaname||'.'||tablename)) as size,
    nlivetup as rowcount,
    ndeadtup as deadrows,
    lastvacuum,
    lastautovacuum,
    lastanalyze,
    lastautoanalyze
FROM pgstatusertables
ORDER BY pgtotalrelationsize(schemaname||'.'||tablename) DESC;

-- Statistiques d'index
SELECT
    schemaname,
    tablename,
    indexname,
    idxscan as indexscans,
    idxtupread as tuplesread,
    idxtupfetch as tuplesfetched,
    pgsizepretty(pgrelationsize(indexrelid)) as indexsize
FROM pgstatuserindexes
ORDER BY idxscan ASC;  -- Index peu utilisés en premier

-- Index inutilisés
SELECT
    schemaname,
    tablename,
    indexname,
    pgsizepretty(pgrelationsize(indexrelid)) as size
FROM pgstatuserindexes
WHERE idxscan = 0
    AND indexrelname NOT LIKE '%pkey'
ORDER BY pgrelationsize(indexrelid) DESC;

-- Cache hits
SELECT
    schemaname,
    tablename,
    heapblksread as diskreads,
    heapblkshit as cachehits,
    ROUND(
        heapblkshit::numeric / NULLIF(heapblkshit + heapblksread, 0)  100,
        2
    ) as cachehitratio
FROM pgstatiousertables
ORDER BY heapblksread DESC;

Requêtes lentes

-- Activer le log des requêtes lentes
-- Dans postgresql.conf :
-- logmindurationstatement = 1000  -- Log queries > 1s

-- Voir les requêtes actives
SELECT
    pid,
    now() - querystart as duration,
    state,
    query
FROM pgstatactivity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Tuer une requête
SELECT pgcancelbackend(pid);  -- Annulation gracieuse
SELECT pgterminatebackend(pid);  -- Terminaison forcée

Maintenance et VACUUM

-- VACUUM : nettoyer les lignes mortes
VACUUM users;
VACUUM VERBOSE users;
VACUUM ANALYZE users;  -- + mettre à jour les statistiques
VACUUM FULL users;     -- Récupérer l'espace disque (bloque la table)

-- ANALYZE : mettre à jour les statistiques
ANALYZE users;
ANALYZE VERBOSE users;

-- REINDEX : reconstruire les index
REINDEX TABLE users;
REINDEX INDEX idxusersemail;
REINDEX DATABASE mydb;  -- (connexion exclusive requise)

-- Auto-vacuum (généralement automatique)
-- Configuration dans postgresql.conf

Optimisations de requêtes

-- 1. Utiliser les index appropriés
CREATE INDEX idxordersuserdate ON orders(userid, orderdate);

-- 2. Limiter les colonnes sélectionnées
-- MAUVAIS
SELECT  FROM users;
-- BON
SELECT id, name, email FROM users;

-- 3. Utiliser EXISTS au lieu de IN pour les sous-requêtes
-- MOINS PERFORMANT
SELECT  FROM users WHERE id IN (SELECT userid FROM orders);
-- PLUS PERFORMANT
SELECT  FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.userid = u.id);

-- 4. Éviter les fonctions sur colonnes indexées dans WHERE
-- MAUVAIS (n'utilise pas l'index)
SELECT  FROM users WHERE LOWER(email) = 'test@example.com';
-- BON (utilise un index fonctionnel)
CREATE INDEX idxusersloweremail ON users(LOWER(email));
SELECT  FROM users WHERE LOWER(email) = 'test@example.com';

-- 5. Utiliser LIMIT pour les grandes requêtes
SELECT  FROM orders ORDER BY createdat DESC LIMIT 100;

-- 6. Partitionner les très grandes tables (> 10M lignes)
-- Voir la documentation PostgreSQL sur le partitionnement

-- 7. Utiliser des vues matérialisées pour les calculs coûteux
CREATE MATERIALIZED VIEW salessummary AS
SELECT
    DATETRUNC('month', orderdate) as month,
    COUNT() as ordercount,
    SUM(total) as revenue
FROM orders
GROUP BY DATETRUNC('month', orderdate);

-- Rafraîchir la vue
REFRESH MATERIALIZED VIEW salessummary;
REFRESH MATERIALIZED VIEW CONCURRENTLY salessummary;  -- Sans bloquer

-- 8. Utiliser la pagination avec curseurs pour gros datasets
BEGIN;
DECLARE mycursor CURSOR FOR SELECT  FROM largetable;
FETCH 100 FROM mycursor;
-- ...
CLOSE mycursor;
COMMIT;

—

Bonnes pratiques

Conception de schéma

Toujours utiliser des clés primaires

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    createdat TIMESTAMPTZ DEFAULT NOW()
);

Définir des contraintes appropriées

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    userid BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
    status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
    createdat TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Utiliser TIMESTAMPTZ pour les dates

-- BON
createdat TIMESTAMPTZ DEFAULT NOW()
-- ÉVITER
createdat TIMESTAMP DEFAULT NOW()

Normaliser mais pas excessivement

3NF pour la plupart des cas

Dénormaliser stratégiquement pour les performances

Performances

Indexer les colonnes de recherche fréquentes

-- Colonnes dans WHERE, JOIN, ORDER BY
CREATE INDEX idxordersuserdate ON orders(userid, orderdate);

Utiliser EXPLAIN pour analyser

EXPLAIN ANALYZE SELECT  FROM orders WHERE userid = 123;

Limiter les résultats

SELECT  FROM largetable LIMIT 1000;

Utiliser les connexions pooling

PgBouncer en production

Connection pooling dans l’application

Configurer les paramètres PostgreSQL

-- sharedbuffers : 25% de la RAM
-- effectivecachesize : 50-75% de la RAM
-- workmem : ajuster selon les requêtes
-- maintenanceworkmem : pour VACUUM, CREATE INDEX

Sécurité

Utiliser des paramètres préparés

-- BON (préparé)
PREPARE getuser AS
SELECT  FROM users WHERE email = $1;
EXECUTE getuser('user@example.com');

-- Éviter la concaténation (SQL injection)

Principe du moindre privilège

-- Créer des rôles spécifiques
CREATE ROLE appreadonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO appreadonly;

CREATE ROLE appreadwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appreadwrite;

Utiliser SSL pour les connexions

-- Dans postgresql.conf
ssl = on

Row Level Security (RLS)

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY userdocuments ON documents
    FOR ALL
    TO appuser
    USING (userid = currentuserid());

Maintenance

Sauvegardes régulières

Backup complet
pgdump -U postgres -d mydb -F c -f backup.dump

Backup avec compression
pgdump -U postgres -d mydb | gzip > backup.sql.gz

Restore
pgrestore -U postgres -d mydb backup.dump

Monitoring

Surveiller les requêtes lentes

Surveiller la taille des tables et index

Surveiller les connexions actives

Surveiller le cache hit ratio

Maintenance régulière

-- Automatique avec autovacuum (recommandé)
-- Manuel si nécessaire
VACUUM ANALYZE;
REINDEX DATABASE mydb;

—

Pièges courants à éviter

N+1 queries

-- MAUVAIS : 1 query + N queries
SELECT  FROM users;  -- puis pour chaque user :
SELECT  FROM orders WHERE userid = ?;

-- BON : 1 seule query avec JOIN
SELECT u., o.
FROM users u
LEFT JOIN orders o ON u.id = o.userid;

SELECT au lieu de colonnes spécifiques

-- Éviter
SELECT  FROM largetable;
-- Préférer
SELECT id, name, email FROM largetable;

Manque d’index sur les clés étrangères

-- TOUJOURS indexer les FK
CREATE INDEX idxordersuserid ON orders(userid);

Utiliser OFFSET pour la pagination profonde

-- LENT pour grandes offsets
SELECT  FROM products ORDER BY id LIMIT 20 OFFSET 100000;

-- RAPIDE : cursor-based pagination
SELECT  FROM products WHERE id > 100020 ORDER BY id LIMIT 20;

Ne pas utiliser les transactions appropriées

-- TOUJOURS utiliser des transactions pour opérations multiples
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Ignorer les NULL dans les comparaisons

-- NULL != NULL et NULL != valeur
SELECT  FROM users WHERE deletedat IS NULL;  -- BON
SELECT  FROM users WHERE deletedat = NULL;   -- MAUVAIS (toujours false)

Conversions de types implicites

-- Peut empêcher l'utilisation d'index
CREATE INDEX idxusersid ON users(id);  -- id est INTEGER
SELECT  FROM users WHERE id = '123';  -- Conversion de type, peut être lent

-- Préférer :
SELECT  FROM users WHERE id = 123;

—

Ressources supplémentaires

Commandes utiles

-- Version PostgreSQL
SELECT version();

-- Taille de la base
SELECT pgsizepretty(pgdatabasesize('mydb'));

-- Liste des tables
dt
SELECT tablename FROM pgtables WHERE schemaname = 'public';

-- Décrire une table
d+ users

-- Liste des connexions
SELECT  FROM pgstatactivity;

-- Extensions disponibles
SELECT * FROM pgavailableextensions;

-- Installer une extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgtrgm";  -- Recherche floue
CREATE EXTENSION IF NOT EXISTS "hstore";

Extensions recommandées

pgstatstatements : Statistiques de requêtes

pg_trgm : Recherche de similarité

uuid-ossp : Génération d’UUID

hstore : Paires clé-valeur

PostGIS : Données géospatiales

pgcrypto : Fonctions de cryptographie

—

Version : PostgreSQL 16
Dernière mise à jour : Décembre 2024

Ce guide couvre les aspects essentiels et avancés de PostgreSQL pour le développement web professionnel.