Skip to content
Learni
Voir tous les tutoriels
Bases de données

Comment implémenter PostgreSQL avancé en 2026

18 minINTERMEDIATE
PostgreSQLBase de donnéesDockerSQL avancéPL/pgSQL
Read in English

Introduction

PostgreSQL est le SGBD relationnel open-source le plus robuste en 2026, surpassant MySQL en ACID compliance et extensions avancées comme JSONB ou full-text search. Ce tutoriel intermédiaire vous guide pour implémenter une base de données complète pour une app e-commerce : schémas normalisés, relations, indexes partiels, triggers pour audit, vues matérialisées et procédures PL/pgSQL.

Pourquoi c'est crucial ? Les bases mal optimisées causent 80% des bottlenecks en prod. Ici, chaque étape est concrète : code Docker prêt, SQL exécutable via psql. À la fin, vous aurez une DB scalable gérant 10k+ commandes/jour, avec monitoring intégré. Idéal pour devs fullstack ou data engineers cherchant des références bookmarkables. (142 mots)

Prérequis

  • Docker et Docker Compose installés
  • Client psql (via brew install libpq sur macOS ou équivalent)
  • Connaissances SQL de base (SELECT, INSERT, JOIN)
  • Environnement Linux/macOS/Windows avec WSL
  • 4GB RAM libre pour les tests

Installation via Docker Compose

docker-compose.yml
version: '3.8'
services:
  postgres:
    image: postgres:17-alpine
    container_name: postgres_app
    restart: always
    environment:
      POSTGRES_DB: ecommerce
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: securepass123
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
volumes:
  postgres_data:

Ce fichier lance PostgreSQL 17 en conteneur isolé avec DB 'ecommerce' initialisée. Le volume persiste les données ; init.sql s'exécute automatiquement au premier démarrage. Évitez les ports exposés en prod, utilisez un reverse proxy.

Script d'initialisation DB

init.sql
CREATE SCHEMA IF NOT EXISTS shop;

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

CREATE TABLE shop.products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INTEGER DEFAULT 0
);

CREATE TABLE shop.orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES shop.users(id) ON DELETE CASCADE,
  total DECIMAL(10,2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ce script crée un schéma 'shop' et trois tables relationnelles avec contraintes FK et timestamps. La CASCADE supprime les orders orphelins. Testez avec docker-compose up -d && psql -h localhost -U admin -d ecommerce.

Insertion de données de test

seed.sql
INSERT INTO shop.users (email, name) VALUES
  ('alice@example.com', 'Alice Dupont'),
  ('bob@example.com', 'Bob Martin');

INSERT INTO shop.products (name, price, stock) VALUES
  ('iPhone 17', 999.99, 50),
  ('MacBook Pro', 2499.99, 20);

INSERT INTO shop.orders (user_id, total, status) VALUES
  (1, 999.99, 'completed'),
  (2, 4999.98, 'pending');

Ces inserts peuplent les tables avec 2 users, 2 produits et 2 orders. Vérifiez avec SELECT * FROM shop.orders;. En prod, utilisez COPY pour des volumes massifs (>1M lignes).

Requêtes avancées avec JOIN et agrégats

queries.sql
SELECT 
  u.name,
  COUNT(o.id) as order_count,
  SUM(o.total) as total_spent,
  AVG(o.total) as avg_order
FROM shop.users u
LEFT JOIN shop.orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC;

-- Window function pour ranking
SELECT 
  p.name,
  p.price,
  RANK() OVER (ORDER BY p.price DESC) as price_rank
FROM shop.products p;

Cette query joint users et orders pour stats par client (avec HAVING pour filtrer). La window function RANK() classe les produits par prix sans sous-requête. Performant pour dashboards analytiques.

Indexes et optimisation

indexes.sql
CREATE INDEX CONCURRENTLY idx_orders_user_status ON shop.orders (user_id, status) WHERE status = 'pending';
CREATE INDEX idx_products_stock_price ON shop.products (stock DESC, price) USING BTREE;

-- Analyse performance
EXPLAIN ANALYZE SELECT * FROM shop.orders WHERE user_id = 1 AND status = 'pending';

-- Vacuum pour maintenance
VACUUM ANALYZE shop.orders;

Indexes partiels accélèrent les queries filtrées (ex: pending orders). CONCURRENTLY évite les locks en prod. EXPLAIN ANALYZE révèle les scans séquentiels à corriger ; VACUUM libère l'espace bloaté.

Triggers et audit trail

triggers.sql
CREATE TABLE shop.audit_log (
  id SERIAL PRIMARY KEY,
  table_name VARCHAR(50),
  record_id INTEGER,
  action VARCHAR(10),
  old_data JSONB,
  new_data JSONB,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION shop.audit_func()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'UPDATE' THEN
    INSERT INTO shop.audit_log (table_name, record_id, action, old_data, new_data)
    VALUES (TG_TABLE_NAME, OLD.id, 'UPDATE', row_to_json(OLD), row_to_json(NEW));
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO shop.audit_log (table_name, record_id, action, old_data)
    VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', row_to_json(OLD));
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_orders_trigger
  AFTER UPDATE OR DELETE ON shop.orders
  FOR EACH ROW EXECUTE FUNCTION shop.audit_func();

Ce trigger PL/pgSQL logue changes en JSONB pour traçabilité GDPR. Fonctionne sur UPDATE/DELETE ; row_to_json capture l'état complet. Testez avec UPDATE shop.orders SET status='shipped' WHERE id=1;.

Vues matérialisées et procédure

views.sql
CREATE MATERIALIZED VIEW shop.sales_summary AS
SELECT 
  date_trunc('month', order_date) as month,
  SUM(total) as monthly_revenue,
  COUNT(*) as order_count
FROM shop.orders
GROUP BY month
WITH DATA;

CREATE OR REPLACE PROCEDURE shop.refresh_summary()
LANGUAGE plpgsql
AS $$
BEGIN
  REFRESH MATERIALIZED VIEW shop.sales_summary;
  RAISE NOTICE 'Vue rafraîchie avec succès';
END;
$$;

-- Usage
CALL shop.refresh_summary();

Vue matérialisée agrège ventes mensuelles pour queries rapides (10x speedup). Procédure PL/pgSQL rafraîchit via cron job. Idéal pour BI ; rafraîchissez seulement sur deltas en prod.

Bonnes pratiques

  • Normalisez au 3NF mais dénormalisez pour reads intensifs (ex: colonnes calculées).
  • Utilisez connection pooling (PgBouncer) pour scaler à 1000+ connexions.
  • Monitorez avec pg_stat_statements et EXPLAIN pour queries lentes.
  • Backup pg_dump quotidien + WAL archiving pour PITR.
  • Sécurisez avec roles : REVOKE ALL sur public, GRANT minimal.

Erreurs courantes à éviter

  • Oublier VACUUM : Bloat >50% dégrade perf ; schedulez auto_vacuum.
  • Indexes inutiles : Vérifiez avec pg_stat_user_indexes ; drop si <5% hits.
  • Transactions longues : Limitez à <5s pour éviter locks ; utilisez SAVEPOINT.
  • JSONB sans GIN index : Queries lentes sur documents ; CREATE INDEX ON table USING GIN (jsonb_col).

Pour aller plus loin

Approfondissez avec TimescaleDB pour time-series, pgvector pour embeddings AI. Suivez nos formations Learni sur bases de données avancées ou explorez la doc officielle PostgreSQL 17.

PostgreSQL avancé : Tutoriel 2026 (55 chars) | Learni