⚡ BD02 — PostgreSQL Avancé

1. Transactions ACID

Une transaction est un bloc d'opérations qui s'exécute entièrement ou pas du tout.

-- Transaction explicite
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  -- En cas d'erreur :
  -- ROLLBACK;
COMMIT;

-- Savepoints
BEGIN;
  INSERT INTO logs (msg) VALUES ('étape 1');
  SAVEPOINT sp1;
  INSERT INTO logs (msg) VALUES ('étape 2');
  ROLLBACK TO SAVEPOINT sp1;  -- annule uniquement l'étape 2
  INSERT INTO logs (msg) VALUES ('étape 2 bis');
COMMIT;

Niveaux d'isolation

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ...
COMMIT;

2. JSONB — JSON Binaire

JSONB stocke le JSON sous forme binaire décomposée, permettant l'indexation et les requêtes performantes.

-- Créer une table avec JSONB
CREATE TABLE events (
  id         BIGSERIAL PRIMARY KEY,
  type       TEXT NOT NULL,
  payload    JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insertion
INSERT INTO events (type, payload) VALUES
  ('user.login',  '{"user_id": 42, "ip": "192.168.1.1", "device": "mobile"}'),
  ('order.placed','{"order_id": 101, "total": 149.99, "items": [{"id":1,"qty":2}]}');

-- Accès aux champs
SELECT payload->>'user_id' AS user_id FROM events WHERE type = 'user.login';

-- Opérateurs JSONB
-- -> retourne JSON, ->> retourne TEXT
-- #> chemin tableau, #>> chemin tableau texte
SELECT payload->'items'->0->>'id' AS first_item_id
FROM events WHERE type = 'order.placed';

-- Filtrage
SELECT * FROM events WHERE payload @> '{"device":"mobile"}';
SELECT * FROM events WHERE payload ? 'user_id';

-- Mise à jour partielle
UPDATE events
SET payload = payload || '{"country":"FR"}'::jsonb
WHERE type = 'user.login';

-- Index GIN sur JSONB (essentiel pour les perfs)
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- jsonb_set pour modifications profondes
UPDATE events
SET payload = jsonb_set(payload, '{device}', '"desktop"')
WHERE id = 1;

3. Full-Text Search

-- to_tsvector et to_tsquery
SELECT title FROM articles
WHERE to_tsvector('french', title || ' ' || body)
      @@ to_tsquery('french', 'postgres & (avancé | index)');

-- Colonne tsvector pré-calculée (performance)
ALTER TABLE articles ADD COLUMN search_vector tsvector;

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

-- Index GIN sur tsvector
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);

-- Trigger pour mise à jour auto
CREATE OR REPLACE FUNCTION update_search_vector() RETURNS trigger AS $$
BEGIN
  NEW.search_vector := to_tsvector('french',
    coalesce(NEW.title,'') || ' ' || coalesce(NEW.body,''));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_articles_search
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION update_search_vector();

-- Ranking des résultats
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('french', 'postgresql') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

-- websearch_to_tsquery (syntaxe Google-like)
SELECT * FROM articles
WHERE search_vector @@ websearch_to_tsquery('french', 'postgresql index performance');

4. CTE & Window Functions

Common Table Expressions (WITH)

-- CTE simple
WITH monthly_sales AS (
  SELECT DATE_TRUNC('month', created_at) AS month,
         SUM(amount) AS total
  FROM orders
  WHERE status = 'completed'
  GROUP BY 1
)
SELECT month, total,
       total - LAG(total) OVER (ORDER BY month) AS growth
FROM monthly_sales;

-- CTE récursive (hiérarchie)
WITH RECURSIVE org_chart 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, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, depth, REPEAT('  ', depth) || name AS tree
FROM org_chart ORDER BY depth, name;

Window Functions

SELECT
  name,
  department,
  salary,
  AVG(salary)   OVER (PARTITION BY department)              AS dept_avg,
  RANK()        OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  ROW_NUMBER()  OVER (ORDER BY salary DESC)                  AS global_rank,
  LAG(salary)   OVER (PARTITION BY department ORDER BY hire_date) AS prev_salary,
  LEAD(salary)  OVER (PARTITION BY department ORDER BY hire_date) AS next_salary,
  SUM(salary)   OVER (PARTITION BY department ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;

5. Triggers & Fonctions

-- Fonction PL/pgSQL d'audit
CREATE TABLE audit_log (
  id         BIGSERIAL PRIMARY KEY,
  table_name TEXT,
  operation  TEXT,
  old_data   JSONB,
  new_data   JSONB,
  changed_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger_fn() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, old_data, new_data)
  VALUES (
    TG_TABLE_NAME, TG_OP,
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN row_to_json(OLD)::jsonb END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN row_to_json(NEW)::jsonb END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_products_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn();

6. EXPLAIN ANALYZE

-- Analyser le plan d'exécution
EXPLAIN ANALYZE
SELECT u.email, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email;

-- Options avancées
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM products WHERE category_id = 5;

-- Métriques pg_stat_statements
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

7. Vues Matérialisées

-- Créer une vue matérialisée
CREATE MATERIALIZED VIEW mv_product_stats AS
SELECT
  p.id,
  p.name,
  COUNT(oi.id)       AS nb_orders,
  SUM(oi.quantity)   AS total_sold,
  AVG(oi.unit_price) AS avg_price
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name
WITH DATA;

-- Index sur la vue mat.
CREATE UNIQUE INDEX ON mv_product_stats (id);

-- Rafraîchir (CONCURRENTLY ne bloque pas les lectures)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_product_stats;

-- Requête sur la vue
SELECT * FROM mv_product_stats ORDER BY total_sold DESC LIMIT 20;