Deep EVM #25 : Partitionnement de tables PostgreSQL — Quand votre table dépasse 10M+ lignes

Quand partitionner

Vous avez une table de transactions qui a commencé petite et atteint maintenant 34 millions de lignes. Les requêtes qui prenaient 50 ms prennent maintenant 5 secondes. VACUUM tourne pendant des heures. Le partitionnement de tables divise une table logique en plusieurs tables physiques, et le planificateur de requêtes de PostgreSQL route automatiquement les requêtes vers les bonnes partitions.

Types de partitionnement

Partitionnement par plage (Range)

Idéal pour les données temporelles :

CREATE TABLE transactions (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    created_at TIMESTAMPTZ NOT NULL,
    amount NUMERIC(18,2),
    account_id BIGINT
) PARTITION BY RANGE (created_at);

CREATE TABLE transactions_2024_01
    PARTITION OF transactions
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE transactions_2024_02
    PARTITION OF transactions
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

Partitionnement par liste (List)

Idéal pour les données catégorielles :

CREATE TABLE events (
    id BIGINT,
    event_type TEXT NOT NULL,
    data JSONB
) PARTITION BY LIST (event_type);

CREATE TABLE events_transfer
    PARTITION OF events
    FOR VALUES IN ('transfer', 'approval');

CREATE TABLE events_swap
    PARTITION OF events
    FOR VALUES IN ('swap', 'flash_loan');

Partitionnement par hash

Idéal pour la distribution uniforme :

CREATE TABLE accounts (
    id BIGINT,
    address TEXT NOT NULL,
    balance NUMERIC
) PARTITION BY HASH (id);

CREATE TABLE accounts_0
    PARTITION OF accounts
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- ... et 3 autres partitions

Migration d’une table existante

Migrer 34 millions de lignes vers une table partitionnée :

-- 1. Créer la nouvelle table partitionnée
CREATE TABLE transactions_new (
    LIKE transactions INCLUDING ALL
) PARTITION BY RANGE (created_at);

-- 2. Créer les partitions
-- (une par mois pour les 3 dernières années)

-- 3. Copier les données par lots
INSERT INTO transactions_new
SELECT * FROM transactions
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';
-- Répéter pour chaque mois

-- 4. Échange atomique
BEGIN;
ALTER TABLE transactions RENAME TO transactions_old;
ALTER TABLE transactions_new RENAME TO transactions;
COMMIT;

-- 5. Supprimer l'ancienne table après vérification
DROP TABLE transactions_old;

Élagage de partitions

Le planificateur de requêtes exclut automatiquement les partitions non pertinentes :

EXPLAIN SELECT * FROM transactions
WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01';

-- Scan uniquement sur transactions_2024_06
-- Les 35 autres partitions sont ignorées

Index sur les tables partitionnées

Chaque partition a ses propres index :

-- Index global (créé sur la table parent, répliqué sur chaque partition)
CREATE INDEX idx_transactions_account
    ON transactions (account_id);

-- Index plus petit par partition = maintenance plus rapide

VACUUM et maintenance

Le principal avantage du partitionnement pour la maintenance : VACUUM peut traiter chaque partition indépendamment. Au lieu de verrouiller 34M lignes, il traite des partitions de 1-2M lignes.

Résultats

Métrique	Avant	Après	Amélioration
Requête par plage de dates	5.2s	45ms	115x
VACUUM	4h	12min	20x
INSERT (débit)	8K/s	25K/s	3x
Taille des index	12 GB	400 MB/partition	Fragmenté

Conclusion

Le partitionnement de tables PostgreSQL est essentiel quand vos tables dépassent les dizaines de millions de lignes. Le partitionnement par plage pour les données temporelles, par liste pour les catégories et par hash pour la distribution uniforme couvrent la majorité des cas d’usage.