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.

PostgreSQL – Requêtes SQL avancées et optimisation

Table des matières

Types de données

Types numériques

Types texte

Types date et heure

Types spéciaux PostgreSQL

Requêtes de base avancées

SELECT avec opérateurs avancés

CASE et logique conditionnelle

Tri et limitation

Jointures (JOINS)

Types de jointures

Jointures multiples

Jointures avec conditions complexes

LATERAL JOIN (jointures latérales)

Sous-requêtes et CTE

Sous-requêtes scalaires

Sous-requêtes corrélées

CTE (Common Table Expressions) – WITH

CTE récursives

Fonctions de fenêtre (Window Functions)

Concepts de base

Fonctions d’agrégation fenêtrées

Fonctions de valeur

Clauses de fenêtre

Index et optimisation

Types d’index

Gestion des index

Stratégies d’indexation

Transactions et verrouillage

Transactions de base

Niveaux d’isolation

Verrouillage (Locking)

Gestion des deadlocks

Fonctions d’agrégation avancées

Agrégations standards

Agrégations statistiques

Agrégations avancées

GROUPING SETS, ROLLUP, CUBE

Filtres d’agrégation

JSON et JSONB

Créer du JSON

Requêtes JSON

Manipulation JSON

Fonctions JSON avancées

Performance et EXPLAIN

EXPLAIN et EXPLAIN ANALYZE

Interpréter EXPLAIN

Statistiques de la base

Requêtes lentes

Maintenance et VACUUM

Optimisations de requêtes

Bonnes pratiques

Conception de schéma

Performances

Sécurité

Maintenance

Backup complet

Backup avec compression

Restore

Pièges courants à éviter

Ressources supplémentaires

Commandes utiles

Extensions recommandées

Articles similaires

Laisser un commentaire Annuler la réponse.