0Pricing
PostgreSQL Performance & Query Optimization · Lezione

Scelta della chiave e della strategia di partizionamento

Valuti il partizionamento per intervallo, lista e hash rispetto ai modelli di accesso reali, per scegliere una chiave che consenta un pruning efficace.

Scelta della chiave e della strategia di partizionamento è una lezione PostgreSQL Performance & Query Optimization gratuita su CoddyKit. Questa è la lezione 1 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento PostgreSQL Performance & Query Optimization, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso PostgreSQL Performance & Query Optimization include 4 lezioni in totale.

Parti di questa lezione non sono ancora state tradotte e vengono mostrate in inglese.

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.

Domande Frequenti

La lezione «Scelta della chiave e della strategia di partizionamento» è gratuita?

Sì — il testo completo di «Scelta della chiave e della strategia di partizionamento» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso PostgreSQL Performance & Query Optimization, passa a CoddyKit PRO. Il corso PostgreSQL Performance & Query Optimization include 4 lezioni in totale.

Cosa imparerò in «Scelta della chiave e della strategia di partizionamento»?

Valuti il partizionamento per intervallo, lista e hash rispetto ai modelli di accesso reali, per scegliere una chiave che consenta un pruning efficace. Eserciti PostgreSQL Performance & Query Optimization con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare PostgreSQL Performance & Query Optimization?

Non è richiesta alcuna esperienza precedente. PostgreSQL Performance & Query Optimization su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 1 di 4.

Quanto tempo richiede la lezione «Scelta della chiave e della strategia di partizionamento»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione PostgreSQL Performance & Query Optimization?

Sì. Ogni lezione PostgreSQL Performance & Query Optimization include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Scelta della chiave e della strategia di partizionamento
  2. Partition pruning in fase di pianificazione ed esecuzione
  3. Automazione della creazione e della conservazione delle partizioni
  4. Migrazione online di una tabella enorme alle partizioni
← Torna a PostgreSQL Performance & Query Optimization