modèles-postgres

Modèles PostgreSQL

Référence rapide pour les bonnes pratiques PostgreSQL. Pour des conseils détaillés, utilisez l'agent database-reviewer.

Quand activer

• Écrire des requêtes SQL ou des migrations
• Concevoir des schémas de base de données
• Résoudre les requêtes lentes
• Implémenter la sécurité au niveau des lignes (Row Level Security)
• Configurer le pooling de connexions

Référence rapide

Aide-mémoire des index

Motif de requête	Type d'index	Exemple
WHERE col = valeur	B-tree (par défaut)	CREATE INDEX idx ON t (col)
WHERE col > valeur	B-tree	CREATE INDEX idx ON t (col)
WHERE a = x AND b > y	Composite	CREATE INDEX idx ON t (a, b)
WHERE jsonb @> '{}'	GIN	CREATE INDEX idx ON t USING gin (col)
WHERE tsv @@ requête	GIN	CREATE INDEX idx ON t USING gin (col)
Plages temporelles	BRIN	CREATE INDEX idx ON t USING brin (col)

Aide-mémoire des types de données

Cas d'usage	Type correct	À éviter
IDs	bigint	int, UUID aléatoire
Chaînes	text	varchar(255)
Horodatages	timestamptz	timestamp
Monnaie	numeric(10,2)	float
Drapeaux	boolean	varchar, int

Motifs courants

Ordre des index composites :

-- Colonnes d'égalité d'abord, puis colonnes de plage
CREATE INDEX idx ON commandes (statut, cree_le);
-- Fonctionne pour : WHERE statut = 'en_attente' AND cree_le > '2024-01-01'

Index couvrant :

CREATE INDEX idx ON utilisateurs (email) INCLUDE (nom, cree_le);
-- Évite un accès à la table pour SELECT email, nom, cree_le

Index partiel :

CREATE INDEX idx ON utilisateurs (email) WHERE supprime_le IS NULL;
-- Index plus petit, inclut uniquement les utilisateurs actifs

Politique RLS (optimisée) :

CREATE POLICY politique ON commandes
  USING ((SELECT auth.uid()) = utilisateur_id);  -- Encapsuler dans SELECT !

UPSERT :

INSERT INTO parametres (utilisateur_id, cle, valeur)
VALUES (123, 'theme', 'sombre')
ON CONFLICT (utilisateur_id, cle)
DO UPDATE SET valeur = EXCLUDED.valeur;

Pagination par curseur :

SELECT * FROM produits WHERE id > $dernier_id ORDER BY id LIMIT 20;
-- O(1) contre OFFSET qui est O(n)

Traitement de file d'attente :

UPDATE travaux SET statut = 'traitement'
WHERE id = (
  SELECT id FROM travaux WHERE statut = 'en_attente'
  ORDER BY cree_le LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

Détection d'anti-patrons

-- Trouver les clés étrangères non indexées
SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

-- Trouver les requêtes lentes
SELECT query, mean_exec_time, appels
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

-- Vérifier le gonflement (bloat) des tables
SELECT relname, n_dead_tup, dernier_vide
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Modèle de configuration

-- Limites de connexion (ajuster selon la RAM)
ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET work_mem = '8MB';

-- Temps d'attente
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET statement_timeout = '30s';

-- Surveillance
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Sécurité par défaut
REVOKE ALL ON SCHEMA public FROM public;

SELECT pg_reload_conf();

Rubriques connexes

• Agent : database-reviewer – Processus complet de révision de base de données
• Skill : clickhouse-io – Modèles d'analyse ClickHouse
• Skill : backend-patterns – Modèles API et backend

---

Basé sur Supabase Agent Skills (crédit : équipe Supabase) (Licence MIT)