🐘 5) Modélisations par objets

Fillfactor

Quand une table est très souvent mise à jour, baisser le fillfactor réserve de la place dans les pages → moins de HOT UPDATE → moins de bloat.

ALTER TABLE orders SET (fillfactor = 90);
ALTER INDEX idx_orders_customer SET (fillfactor = 90);
VACUUM (FULL, ANALYZE) orders;

Partitionnement

À envisager si > 100 M lignes, si archivage mensuel, ou si on veut paralléliser VACUUM/REINDEX.

-- table partitionnée par date
CREATE TABLE logs (
  ts timestamptz NOT NULL,
  payload jsonb
) PARTITION BY RANGE (ts);

CREATE TABLE logs_2025_10
  PARTITION OF logs FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');

Index – bonnes pratiques

• Éviter les doublons : pg_indexes + requête de détection
• Index partiels : WHERE active, WHERE deleted_at IS NULL
• Multi-colonnes : ordre = la colonne la plus sélective d’abord
• BRIN : parfait pour très grandes tables append-only ou datées

-- index partiel
CREATE INDEX idx_users_active ON users (country, created_at)
WHERE active = true;

-- index BRIN (gros scans ordonnés)
CREATE INDEX idx_logs_brin_ts ON logs USING BRIN (ts);

TOAST & données larges

Éviter de stocker d’énormes blobs dans la même table que du trafic OLTP. Si besoin : compresser côté client, ou stocker ailleurs et ne garder qu’un pointeur.

Détection bloat

SELECT schemaname, relname,
       pg_size_pretty(pg_table_size(relid)) AS tbl_size,
       (n_dead_tup / NULLIF(n_live_tup,0))::numeric(10,2) AS dead_ratio
FROM pg_stat_all_tables
WHERE n_dead_tup > 100000
ORDER BY 4 DESC;

Global vs par colonne

Le paramètre global default_statistics_target donne un niveau moyen. Mais pour des colonnes très corrélées (user_id + status + country), il faut monter la stat par colonne.

-- global
ALTER SYSTEM SET default_statistics_target = 100;
SELECT pg_reload_conf();

Colonnes difficiles

Ex : colonnes avec distribution très inégale (10% active = true, 90% false) → augmenter à 200.

ALTER TABLE users
  ALTER COLUMN active SET STATISTICS 200;
ANALYZE users;

ANALYZE ciblé

• Après un gros import
• Après un changement de distribution
• Après création d’index partiel

ANALYZE users (country, status, active);

Voir les stats existantes

SELECT attrelid::regclass AS table,
       attname,
       n_distinct,
       most_common_vals
FROM pg_stats
WHERE schemaname='public'
  AND tablename='users'
ORDER BY attname;

Quand le planner se trompe

• essayer enable_nestloop / enable_hashjoin / enable_mergejoin en session
• vérifier les colonnes corrélées → stats multi-cols (PG ≥ 13 : extended stats)

CREATE STATISTICS s_users_country_status (dependencies)
  ON country, status
  FROM users;
ANALYZE users;

Pourquoi par rôle ?

Parce que tu ne peux pas mettre work_mem=256MB globalement, mais tu peux le faire pour ton rôle ETL, pas pour Django.

Exemples

-- rôle appli (latence)
ALTER ROLE app_user
  SET statement_timeout = '3s';
ALTER ROLE app_user
  SET search_path = public, app_schema;

-- rôle ETL (gros tri)
ALTER ROLE etl_user
  SET work_mem = '256MB';
ALTER ROLE etl_user
  SET maintenance_work_mem = '1GB';

SET LOCAL (dans une transac)

BEGIN;
  SET LOCAL work_mem = '128MB';
  -- job d'index
COMMIT;

Contrôler le search_path

Pour éviter les surprises (extensions, schémas perso), fixer un search_path court.

ALTER ROLE app_user SET search_path = public, app;

Timeouts ciblés

ALTER ROLE reporting SET statement_timeout = '30s';
ALTER ROLE admin     SET lock_timeout = '5s';

Vue d’ensemble

SELECT rolname, rolconfig
FROM pg_roles
ORDER BY rolname;

Ça te permet de voir rapidement qui a un work_mem débile ou un search_path foireux.