Federico MeiniSistemi real-time, agenti AI e altro
EN
Lavoriamo insieme(si apre in una nuova scheda)
← Tutti gli articoli

Il partitioning di PostgreSQL in pratica: chiavi di partizione, pruning, index e retention

Come si comporta davvero il partitioning di PostgreSQL: scelta della chiave, pruning verificato con EXPLAIN, index, vincoli, retention e le insidie di Ecto.

Di 8 min di letturaRead in English

Il partitioning di PostgreSQL ripaga quando quasi ogni query e ogni attività di manutenzione ruotano attorno a una colonna, di solito un timestamp. Le query toccano poche partition, gli index dei dati recenti restano piccoli, il VACUUM lavora su pezzi gestibili e la retention diventa eliminare una tabella. Non fa niente per una ricerca già ben indicizzata, e rende più lenta ogni query che ignora la chiave. In Turn.io ho partizionato tabelle di produzione da oltre 1 TB per recuperare performance mentre i dati dei messaggi crescevano; questo articolo raccoglie i fondamentali che controllo prima e dopo averlo fatto.

Tutto quello che trovi qui sotto l’ho verificato su PostgreSQL 18.6 con una tabella messages da 4 milioni di righe divisa in range partition mensili, con note sulle versioni dove il comportamento cambia. Per migrare una tabella grande esistente senza downtime, leggi Come partizionare una tabella PostgreSQL da 1 TB senza downtime.

In breve

  • Partiziona sulla colonna su cui filtra quasi ogni query, e non aggiornarla mai.
  • Il costo di pianificazione cresce con le partition che una query non riesce a escludere: tieni il numero contenuto, a meno che le query facciano pruning bene.
  • Verifica il pruning con EXPLAIN: le partition escluse mancano dal piano, o sono contate in Subplans Removed.
  • Tieni la chiave come colonna nuda confrontata con un parametro. Le funzioni applicate alla colonna, e ago/2 di Ecto su timestamptz, impediscono il pruning in fase di pianificazione.
  • Costruisci gli index con CREATE INDEX ON ONLY, CONCURRENTLY su ogni partition, poi ALTER INDEX ... ATTACH PARTITION.
  • Primary key, vincoli unique e foreign key che puntano alla tabella devono includere la chiave.
  • Elimina i dati vecchi con DETACH PARTITION ... CONCURRENTLY (PostgreSQL 14+), che non ammette una default partition.
  • L’autovacuum non analizza mai la tabella padre: lancia ANALYZE tu.

Quando il partitioning aiuta, e quando no

Aiuta con dati ordinati nel tempo, scritti quasi solo in append, dove la maggior parte delle letture tocca le righe recenti: gli index delle partition recenti stanno in memoria, quelle vecchie smettono di cambiare e vengono congelate una volta sola, e cancellare un mese diventa un DROP TABLE invece di un DELETE enorme seguito da un VACUUM.

Non aiuta se il vero problema è un index mancante o un piano sbagliato, se le query più frequenti cercano righe per qualcosa di diverso dalla chiave, o se la tabella è semplicemente “grande”. Ogni query senza la chiave ora visita ogni partition. Sistema prima i piani: il partitioning aggiunge complessità operativa e deve darti qualcosa di concreto in cambio.

Scegliere la chiave e la dimensione delle partition

Tre regole per la chiave. Quasi ogni query deve filtrare su di essa, perché il pruning la confronta con i valori della query. Non deve cambiare mai: aggiornarla sposta la riga in un’altra partition, con una delete più una insert (ho visto una riga passare da messages_p2026_05 a messages_p2026_10). E deve distribuire le scritture in modo prevedibile. Per messaggi ed eventi significa RANGE (inserted_at), con i limiti in UTC.

Per la dimensione, la documentazione dice che il planner gestisce “up to a few thousand partitions fairly well” quando le query escludono quasi tutte le partition. Con troppo poche, le partition restano troppo grandi per guadagnarci qualcosa. Con troppe, crescono il tempo di pianificazione e la memoria di ogni sessione, perché ogni partition toccata carica i propri metadati in quel backend. Con 1.000 partition giornaliere, sul mio portatile una query con pruning in pianificazione richiedeva 3 ms per essere pianificata, una senza pruning circa 100 ms. Il mese è un default sensato per i messaggi; scendi più in basso solo quando un mese è troppo grande per fare VACUUM o indicizzare comodamente.

Range, list o hash

CREATE TABLE messages_p2026_03 PARTITION OF messages
  FOR VALUES FROM ('2026-03-01 00:00:00+00') TO ('2026-04-01 00:00:00+00');

CREATE TABLE by_region (region text NOT NULL, x int) PARTITION BY LIST (region);
CREATE TABLE by_region_eu PARTITION OF by_region FOR VALUES IN ('eu', 'uk');
CREATE TABLE by_region_other PARTITION OF by_region DEFAULT;

CREATE TABLE tenant_events (tenant_id int NOT NULL, payload text) PARTITION BY HASH (tenant_id);
CREATE TABLE tenant_events_0 PARTITION OF tenant_events FOR VALUES WITH (MODULUS 4, REMAINDER 0);

Range è adatto al tempo, con il limite inferiore incluso e quello superiore escluso. List è adatto a un piccolo insieme di valori noti. Hash distribuisce le scritture in modo uniforme ma fa pruning solo sull’uguaglianza, non aiuta la retention e non può avere una default (“a hash-partitioned table may not have a default partition”).

Partition pruning in pianificazione e in esecuzione

Con valori letterali il planner fa pruning, e le altre partition semplicemente non compaiono nel piano:

EXPLAIN (COSTS OFF)
SELECT count(*) FROM messages
WHERE contact_id = 42
  AND inserted_at >= '2026-03-01 00:00:00+00' AND inserted_at < '2026-04-01 00:00:00+00';
 Aggregate
   ->  Index Only Scan using messages_p2026_03_contact_id_inserted_at_index on messages_p2026_03 messages
         Index Cond: ((contact_id = 42) AND (inserted_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND ...

Con now(), o con un parametro in un piano generico, il valore non è noto in pianificazione: Postgres fa pruning all’avvio dell’esecuzione e riporta quante partition ha escluso:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS OFF, SUMMARY OFF)
SELECT count(*) FROM messages
WHERE contact_id = 42 AND inserted_at >= now() - interval '7 days';
 Aggregate (actual rows=1.00 loops=1)
   ->  Append (actual rows=2.00 loops=1)
         Subplans Removed: 15
         ->  Index Only Scan using messages_p2026_09_contact_id_inserted_at_index on messages_p2026_09 messages_1 ...
         ->  Index Only Scan using messages_p2026_10_contact_id_inserted_at_idx on messages_p2026_10 messages_2 ...
         ->  Seq Scan on messages_p2026_11 messages_3 (actual rows=0.00 loops=1)
         ->  Seq Scan on messages_p2026_12 messages_4 (actual rows=0.00 loops=1)

Un prepared statement forzato a piano generico (SET plan_cache_mode = force_generic_plan) ha dato lo stesso Subplans Removed: 15. La terza fase avviene durante l’esecuzione, per valori che cambiano dentro la query, come il lato interno di un nested loop. Lì le partition escluse compaiono come (never executed), come descrive la documentazione.

enable_partition_pruning è attivo di default; disattivarlo serve solo a confrontare i piani. Una trappola in cui sono caduto: disattivandolo, le partition che avevano ancora vecchi vincoli CHECK venivano escluse lo stesso, per via di constraint_exclusion = partition. Solo dopo aver eliminato quei CHECK ridondanti sono comparse tutte e 19 le partition.

Query senza la chiave di partizione

Funzionano lo stesso, ma visitano ogni partition. SELECT * FROM messages WHERE id = 123456 ha prodotto 19 index scan, uno per partition. Ogni tanto va bene, ma un percorso caldo dovrebbe portarsi dietro il timestamp.

Le funzioni applicate alla colonna la nascondono al pruning. Entrambe queste query hanno scansionato ogni partition:

SELECT count(*) FROM messages WHERE inserted_at::date = '2026-03-05';
SELECT count(*) FROM messages WHERE date_trunc('month', inserted_at) = '2026-03-01 00:00:00+00';

Scrivi degli intervalli: inserted_at >= '2026-03-05' AND inserted_at < '2026-03-06'.

Costo di pianificazione e join per partition

La pianificazione cresce con le partition che restano dopo il pruning in fase di pianificazione. Sulla mia tabella di test da 1.000 partition:

Forma della query Tempo di pianificazione
Intervallo letterale o timestamptz passato come parametro da 3 a 4 ms
Nessun filtro sulla chiave circa 100 ms
inserted_at >= now() - interval '1 day' da circa 95 a 130 ms

Le release notes di PostgreSQL 18 riportano una pianificazione più efficiente per le query che toccano molte partition, ma la natura del problema resta: fai pruning presto, o paghi per ogni partition.

enable_partitionwise_join e enable_partitionwise_aggregate sono disattivati di default perché rendono la pianificazione più costosa. Possono aiutare quando fai join tra due tabelle partizionate allo stesso modo sulla chiave, o aggreghi per la chiave, ma il planner decide comunque in base al costo: sulla mia tabella di test, attivare l’impostazione per le aggregazioni non ha cambiato il piano né per GROUP BY inserted_at né per GROUP BY contact_id, inserted_at. Attivali per sessione sulle query che ne hanno bisogno, e controlla:

SET enable_partitionwise_join = on;
SET enable_partitionwise_aggregate = on;
EXPLAIN (COSTS OFF)
SELECT contact_id, inserted_at, count(*) FROM messages GROUP BY 1, 2;

Index sulle tabelle partizionate

CREATE INDEX sul padre costruisce l’index su ogni partition bloccando le scritture, e CONCURRENTLY viene rifiutato: “cannot create index on partitioned table “messages” concurrently”. La soluzione della documentazione richiede tre passi; \gexec di psql esegue separatamente ogni istruzione generata, quindi funziona anche con CONCURRENTLY:

CREATE INDEX messages_direction_inserted_at_index ON ONLY messages (direction, inserted_at);

SELECT format('CREATE INDEX CONCURRENTLY %I ON %I (direction, inserted_at)',
              c.relname || '_direction_inserted_at_index', c.relname)
FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'messages'::regclass \gexec

SELECT format('ALTER INDEX messages_direction_inserted_at_index ATTACH PARTITION %I',
              c.relname || '_direction_inserted_at_index')
FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'messages'::regclass \gexec

SELECT indisvalid FROM pg_index
WHERE indexrelid = 'messages_direction_inserted_at_index'::regclass;

L’index del padre nasce non valido (f) ed è diventato valido (t) appena agganciato l’index dell’ultima partition. Le partition nuove lo ricevono in automatico.

Primary key e vincoli unique

Ogni partition garantisce l’unicità solo al proprio interno, quindi Postgres pretende che ogni vincolo unique includa la chiave: “unique constraint on partitioned table must include all partitioning columns”. La primary key diventa (id, inserted_at). Gli upsert seguono: ON CONFLICT (id) fallisce con “there is no unique or exclusion constraint matching the ON CONFLICT specification”, e il target deve essere (id, inserted_at). Per una chiave di deduplicazione globale, come l’id del messaggio di un provider, tieni una piccola tabella separata con il suo unique index.

Foreign key

Le foreign key da una tabella partizionata funzionano come sempre (PostgreSQL 11+). Quelle verso una tabella partizionata funzionano da PostgreSQL 12, ma devono riferirsi a una chiave unique, che ora include il timestamp:

CREATE TABLE message_reactions (
  id bigserial PRIMARY KEY,
  message_id bigint NOT NULL,
  message_inserted_at timestamptz NOT NULL,
  emoji text NOT NULL,
  FOREIGN KEY (message_id, message_inserted_at) REFERENCES messages (id, inserted_at)
);

Riferirsi solo a messages (id) fallisce con “there is no unique constraint matching given keys”. Altri due comportamenti: staccare una partition che ha ancora righe referenziate fallisce (“removing partition “messages_p2025_06” violates foreign key constraint …”), e PostgreSQL 18 introduce le foreign key NOT VALID sulle tabelle partizionate, così puoi validarle più tardi senza bloccare le scritture.

Retention con DETACH PARTITION CONCURRENTLY

ALTER TABLE messages DETACH PARTITION messages_p2025_06 CONCURRENTLY;
DROP TABLE messages_p2025_06;

Senza CONCURRENTLY, il detach prende un ACCESS EXCLUSIVE sul padre. Con CONCURRENTLY (PostgreSQL 14+), ALTER TABLE usa due transazioni: SHARE UPDATE EXCLUSIVE su padre e partition, poi un’attesa di tutte le transazioni che usano la tabella, poi il passo finale. Le restrizioni (i primi due errori vengono dal mio test):

  • Dentro un blocco di transazione: “ALTER TABLE … DETACH CONCURRENTLY cannot run inside a transaction block”.
  • Con una default partition: “cannot detach partitions concurrently when a default partition exists”.
  • Per ogni tabella può esserci una sola partition in attesa di detach. Se viene interrotto, completalo con DETACH PARTITION ... FINALIZE.

Aggiunge anche alla tabella staccata un CHECK che duplica il vecchio limite (messages_p2025_06_inserted_at_check nel mio test), comodo se un giorno la riagganci. Rispetto a cancellare milioni di righe e poi fare VACUUM, è banale.

Autovacuum e ANALYZE per partition

L’autovacuum tratta ogni partition come una tabella e non elabora mai il padre. La documentazione ne spiega la conseguenza: nessuno esegue ANALYZE su una tabella partizionata, quindi lancialo tu dopo aver caricato dati o quando la distribuzione cambia. Da PostgreSQL 18, ANALYZE ONLY messages aggiorna le statistiche del padre senza rianalizzare ogni partition.

Le impostazioni dell’autovacuum vivono sulle partition. ALTER TABLE messages SET (autovacuum_vacuum_scale_factor = 0.01) fallisce con “cannot specify storage parameters for a partitioned table”, e LIKE ... INCLUDING ALL non ha copiato le impostazioni di autovacuum di una partition: impostale in ciò che crea le nuove partition.

Note su Ecto ed Elixir

A Ecto non interessa la primary key del database, quindi lo schema può tenere id come primary key. Però Repo.get(Message, id) interroga l’index di ogni partition. Quando hai il timestamp, usalo: Repo.get_by(Message, id: id, inserted_at: inserted_at). Gli upsert richiedono conflict_target: [:id, :inserted_at].

Fai attenzione a come costruisci i filtri temporali. Su una colonna timestamptz, ago/2 diventa $2::timestamp + (-7::decimal::numeric * interval '1 day'), e quel cast verso timestamptz non è immutable, quindi il pruning aspetta l’esecuzione: sulla mia tabella da 1.000 partition la pianificazione richiedeva da 95 a 145 ms invece di 3,7 ms. Passare un DateTime già calcolato risolve. Con le colonne timestamp predefinite di Ecto, ago/2 faceva pruning in pianificazione.

cutoff = DateTime.add(DateTime.utc_now(), -7, :day)

from m in Message,
  where: m.contact_id == ^contact_id and m.inserted_at >= ^cutoff,
  select: count()

Le migration possono dichiarare direttamente una tabella partizionata. Questa ha generato PRIMARY KEY ("id","inserted_at") ... PARTITION BY RANGE (inserted_at):

create table(:events, primary_key: false, options: "PARTITION BY RANGE (inserted_at)") do
  add :id, :bigserial, primary_key: true
  add :contact_id, :bigint, null: false
  add :payload, :map
  add :inserted_at, :utc_datetime_usec, primary_key: true
end

DETACH ... CONCURRENTLY e CREATE INDEX CONCURRENTLY devono girare fuori da una transazione: in una migration imposta @disable_ddl_transaction true e @disable_migration_lock true (la guida Safe Ecto Migrations ne spiega i compromessi). Dal codice applicativo, per esempio da un job Oban, chiama Repo.query!/3 fuori da Repo.transaction/1; nel mio test ha funzionato. Per creare le partition in anticipo, l’articolo sulla migrazione contiene il worker Oban che uso.

Se stai partizionando una tabella

La maggior parte dei problemi di partitioning si vede in EXPLAIN molto prima che nella latenza di produzione, e sistemarli presto costa poco. Se il tuo cluster Postgres (o Elasticsearch) rallenta man mano che i dati crescono e vuoi aiuto per scegliere le chiavi, controllare i piani o pianificare una migrazione, è quello che faccio.

Continua a leggere