Comment optimiser PostgreSQL pour la production en 2026

CREATE DATABASE perf_test;
\c perf_test;

-- Table parent partitionnée par range (date)
CREATE TABLE sales (
  id SERIAL,
  sale_date DATE NOT NULL,
  amount DECIMAL(10,2),
  customer_id INT,
  metadata JSONB,
  PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (sale_date);

-- Partitions pour 2025-2026
CREATE TABLE sales_2025 PARTITION OF sales
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE sales_2026 PARTITION OF sales
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- Insérer 100k lignes de test (simulé)
INSERT INTO sales (sale_date, amount, customer_id, metadata)
SELECT
  ('2025-01-01'::DATE + (random() * 365)::INT) AS sale_date,
  random() * 1000 AS amount,
  (random() * 1000)::INT AS customer_id,
  jsonb_build_object('category', 'tech', 'tags', array['laptop', 'sale']) AS metadata
FROM generate_series(1, 100000);

CREATE INDEX idx_sales_date_brin ON sales USING BRIN (sale_date);
CREATE INDEX idx_sales_amount_btree ON sales (amount) WHERE amount > 500;
CREATE INDEX idx_sales_metadata_gin ON sales USING GIN (metadata);
CREATE INDEX idx_sales_customer_trgm ON sales USING gin (customer_id gin_trgm_ops); -- Nécessite pg_trgm

-- ANALYZE pour stats précises
ANALYZE sales;

-- Vacuum pour réclamation espace
VACUUM ANALYZE sales_2025;

-- Ajouter colonnes FTS
ALTER TABLE sales ADD COLUMN tsv tsvector;
CREATE INDEX idx_sales_tsv_gin ON sales USING GIN(tsv);

-- Trigger pour mise à jour auto (config_fr pour français)
CREATE OR REPLACE FUNCTION sales_fts_trigger() RETURNS trigger AS $$
BEGIN
  NEW.tsv := to_tsvector('french', coalesce(metadata::text, ''));
  RETURN NEW;
END$$ LANGUAGE plpgsql;

CREATE TRIGGER sales_fts_update BEFORE INSERT OR UPDATE ON sales
  FOR EACH ROW EXECUTE FUNCTION sales_fts_trigger();

-- Refresh sur données existantes
UPDATE sales SET metadata = metadata WHERE id <= 100000;

-- Query FTS avec ranking
SELECT id, sale_date, ts_rank(tsv, query) AS rank
FROM sales, plainto_tsquery('french', 'laptop vente') query
WHERE tsv @@ query
ORDER BY rank DESC
LIMIT 10;

-- Vue matérialisée : CA mensuel
CREATE MATERIALIZED VIEW sales_monthly_mv AS
SELECT
  DATE_TRUNC('month', sale_date) AS month,
  SUM(amount) AS total,
  COUNT(*) AS count,
  AVG(amount) AS avg
FROM sales
GROUP BY month;

CREATE UNIQUE INDEX idx_mv_month ON sales_monthly_mv (month);

-- Query rapide
SELECT * FROM sales_monthly_mv WHERE month >= '2025-06-01';

-- Refresh périodique (cron/job)
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_monthly_mv;

WITH ranked_sales AS (
  SELECT
    sale_date,
    amount,
    customer_id,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY sale_date DESC) as rn,
    LAG(amount) OVER (PARTITION BY customer_id ORDER BY sale_date) as prev_amount
  FROM sales
  WHERE sale_date >= '2025-01-01'
)
SELECT
  customer_id,
  SUM(amount) FILTER (WHERE rn = 1) as latest_monthly,
  AVG(amount - COALESCE(prev_amount, 0)) as growth
FROM ranked_sales
GROUP BY customer_id
HAVING COUNT(*) > 5
ORDER BY growth DESC
LIMIT 20;

-- EXPLAIN ANALYZE pour valider
EXPLAIN (ANALYZE, BUFFERS) WITH ranked_sales AS ... [même query];