0Pricing
PostgreSQL Performance & Query Optimization · Leçon

Choisir une clé et une stratégie de partitionnement

Évaluez le partitionnement par intervalle, par liste et par hachage selon les modes d’accès réels afin de choisir une clé permettant un élagage efficace.

Choisir une clé et une stratégie de partitionnement est une leçon PostgreSQL Performance & Query Optimization gratuite sur CoddyKit. Ceci est la leçon 1 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage PostgreSQL Performance & Query Optimization, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours PostgreSQL Performance & Query Optimization comprend 4 leçons au total.

Certaines parties de cette leçon n'ont pas encore été traduites et s'affichent en anglais.

Why the Partition Key Decides Everything

Partitioning splits one logical table into many physical child tables. The single most important choice is the partition key — the column (or expression) PostgreSQL uses to route each row to a partition.

A good key lets the planner do partition pruning: it skips partitions that cannot match your WHERE clause, so a query touches a few partitions instead of all of them. A bad key forces scans across every partition and you lose most of the benefit.

  • Range partitioning — buckets by ordered ranges (dates, IDs).
  • List partitioning — buckets by a discrete set of values (region, tenant).
  • Hash partitioning — buckets by a hash of the key for even spread.

The right strategy is the one that matches how your queries actually filter the data.

Start From the Access Pattern, Not the Schema

Never pick a partition key by looking at the table alone. Look at the queries. Ask three questions:

  • Which columns appear in WHERE clauses on almost every read?
  • Do queries filter by ranges (last 7 days) or exact values (tenant_id = 42)?
  • How do you delete or archive old data — by time, by customer, or never?

If 90% of your queries filter created_at >= now() - interval '7 days', then created_at is your candidate key. The partition key must appear in the query's filter for pruning to fire.

-- Inspect real filters before deciding: which columns are filtered most?
SELECT query, calls, total_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%events%'
ORDER BY total_exec_time DESC
LIMIT 10;

Range Partitioning: The Time-Series Default

Range partitioning assigns each row to a partition based on whether the key falls inside a half-open interval [from, to). It is the natural fit for time-series and append-mostly data, where new rows have increasing timestamps and old data is queried less.

Each partition covers one period (a month here). Inserts land in the newest partition; queries that filter by date prune to just the months they need.

CREATE TABLE events (
    id          bigint        NOT NULL,
    created_at  timestamptz   NOT NULL,
    user_id     bigint        NOT NULL,
    payload     jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_05 PARTITION OF events
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE events_2026_06 PARTITION OF events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

Watch Pruning Work on a Range Key

The payoff is visible in EXPLAIN. When your filter overlaps only one partition's range, the plan scans just that partition. The others never appear in the plan — they were pruned at planning time.

Because the filter on created_at matches the partition key, PostgreSQL knows June rows can only live in events_2026_06. The May partition is skipped entirely.

EXPLAIN (COSTS OFF)
SELECT count(*)
FROM events
WHERE created_at >= '2026-06-01'
  AND created_at <  '2026-06-15';

-- Plan shows only:
--   Seq Scan on events_2026_06
-- The May partition is pruned and never scanned.

List Partitioning: Discrete, Bounded Categories

List partitioning routes rows by matching the key against an explicit set of values. Reach for it when the key is a small, stable set of discrete categories that queries filter on exactly — region, country, status, or a fixed tenant tier.

It shines when each category has different retention, locality, or compliance needs (for example, keeping EU data physically separate). Use a DEFAULT partition to catch values you did not enumerate.

CREATE TABLE customers (
    id      bigint NOT NULL,
    region  text   NOT NULL,
    name    text
) PARTITION BY LIST (region);

CREATE TABLE customers_eu PARTITION OF customers
    FOR VALUES IN ('DE', 'FR', 'NL', 'ES');

CREATE TABLE customers_us PARTITION OF customers
    FOR VALUES IN ('US', 'CA');

CREATE TABLE customers_other PARTITION OF customers DEFAULT;

When List Partitioning Backfires

List partitioning assumes the value set is small and slow-changing. It breaks down when:

  • The category has thousands of values (one partition per user_id) — too many partitions hurt planning time and memory.
  • Values are highly skewed — one giant partition holds 80% of rows, so pruning gives little benefit.
  • Queries rarely filter on the key — then no partition is ever pruned.

If the values are many but you still want even distribution and pruning on equality, that is the signal to consider hash partitioning instead.

Hash Partitioning: Even Spread for Point Lookups

Hash partitioning computes a hash of the key and assigns rows by hash MOD modulus. Its purpose is even data distribution when there is no natural range or list, and when queries look up rows by exact equality on a high-cardinality key like user_id.

You define a fixed number of partitions (the modulus). Each gets a remainder. Distribution is uniform, which spreads write load and keeps every partition a similar size.

CREATE TABLE sessions (
    id       bigint NOT NULL,
    user_id  bigint NOT NULL,
    token    text
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_p0 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Hash Prunes on Equality, Not Ranges

The critical limitation: hash partitioning can only prune for equality (and IN) on the key. A range filter is useless because the hash scatters consecutive values across all partitions.

  • WHERE user_id = 1001 → prunes to exactly one partition.
  • WHERE user_id BETWEEN 1000 AND 2000 → no pruning, scans all partitions.

So hash fits point lookups and even write spread, but it is the wrong choice if your dominant queries scan ranges of the key or need time-based retention.

-- Prunes to ONE partition (equality matches the hash key):
EXPLAIN (COSTS OFF)
SELECT * FROM sessions WHERE user_id = 1001;

-- Does NOT prune (range over a hashed key scans every partition):
EXPLAIN (COSTS OFF)
SELECT * FROM sessions WHERE user_id BETWEEN 1000 AND 2000;

Match Retention and Deletes to the Key

Deletion strategy often decides the key more than reads do. Dropping a whole partition is instantaneous and reclaims space immediately; deleting millions of rows with DELETE is slow and bloats the table.

If you must purge data older than 90 days, a range key on the date lets you DROP or DETACH an old partition in milliseconds. Hash and list keys cannot do this for time-based retention because age is not aligned to any single partition.

-- Range-by-date makes retention a metadata operation:
ALTER TABLE events DETACH PARTITION events_2026_05;
DROP TABLE events_2026_05;

-- vs. the slow, bloating alternative on a non-range layout:
-- DELETE FROM events WHERE created_at < '2026-06-01';

Composite Keys and Sub-Partitioning

Sometimes one dimension is not enough. You can partition by multiple columns, or sub-partition: range by month at the top level, then hash by user_id inside each month. This combines time-based retention with even spread for point lookups.

Keep it disciplined — every extra level multiplies the partition count. Too many partitions slow down planning and consume locks. Only sub-partition when a single key genuinely cannot serve both your retention and your lookup patterns.

CREATE TABLE events_2026_06 PARTITION OF events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01')
    PARTITION BY HASH (user_id);

CREATE TABLE events_2026_06_h0 PARTITION OF events_2026_06
    FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE events_2026_06_h1 PARTITION OF events_2026_06
    FOR VALUES WITH (MODULUS 2, REMAINDER 1);

A Decision Checklist

Put the strategies side by side against your real workload:

  • Range — key is ordered (time/sequence), queries filter by ranges, you archive or drop old data by period. The default for logs, events, metrics.
  • List — key is a small fixed set of categories, queries filter by exact category, categories need separate handling (locality, retention).
  • Hash — high-cardinality key, queries are equality point lookups, you want uniform size and write spread, and you do NOT need range pruning or time-based drops.

Pick the strategy whose pruning model matches the filter your queries actually use. If no key is filtered on most queries, partitioning will not help reads — reconsider before adding the complexity.

Quick Check

Apply the decision rules to a concrete workload.

Recap

The partition key and strategy must be driven by how queries filter and how data is retired:

  • Range for ordered keys, range filters, and period-based archival — the time-series default that makes purges a metadata operation.
  • List for a small, stable set of discrete categories filtered by equality, especially when categories need distinct handling.
  • Hash for high-cardinality keys with equality point lookups and a need for even distribution — but it cannot prune ranges or drop by age.

Always verify pruning with EXPLAIN: the right key shows only the partitions your filter can match. If most queries do not filter the key at all, partitioning adds cost without speeding up reads.

Questions Fréquemment Posées

La leçon « Choisir une clé et une stratégie de partitionnement » est-elle gratuite ?

Oui — le texte complet de « Choisir une clé et une stratégie de partitionnement » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours PostgreSQL Performance & Query Optimization, passe à CoddyKit PRO. Le cours PostgreSQL Performance & Query Optimization comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Choisir une clé et une stratégie de partitionnement » ?

Évaluez le partitionnement par intervalle, par liste et par hachage selon les modes d’accès réels afin de choisir une clé permettant un élagage efficace. Tu pratiques PostgreSQL Performance & Query Optimization avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer PostgreSQL Performance & Query Optimization ?

Aucune expérience préalable n'est requise. PostgreSQL Performance & Query Optimization sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 1 sur 4.

Combien de temps prend la leçon « Choisir une clé et une stratégie de partitionnement » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon PostgreSQL Performance & Query Optimization ?

Oui. Chaque leçon PostgreSQL Performance & Query Optimization inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. Choisir une clé et une stratégie de partitionnement
  2. Élagage des partitions lors de la planification et de l’exécution
  3. Automatiser la création et la conservation des partitions
  4. Migrer une table volumineuse vers des partitions en ligne
← Retour à PostgreSQL Performance & Query Optimization