---
title: "Il partitioning di PostgreSQL in pratica: chiavi di partizione, pruning, index e retention"
description: "Come si comporta davvero il partitioning di PostgreSQL: scelta della chiave, pruning verificato con EXPLAIN, index, vincoli, retention e le insidie di Ecto."
author: Federico Meini
date: 2026-08-12
tags: [postgresql, partitioning, performance, elixir]
language: it
url: https://fedme.dev/it/blog/postgresql-partitioning-in-practice
---

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

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](https://fedme.dev/it/blog/partitioning-a-1tb-postgres-table-without-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](https://www.postgresql.org/docs/current/ddl-partitioning.html) 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

```sql
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:

```sql
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';
```

```text
 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:

```sql
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';
```

```text
 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](https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITION-PRUNING).

`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:

```sql
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:

```sql
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](https://www.postgresql.org/docs/current/ddl-partitioning.html) richiede tre passi; `\gexec` di psql esegue separatamente ogni istruzione generata, quindi funziona anche con `CONCURRENTLY`:

```sql
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:

```sql
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

```sql
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](https://www.postgresql.org/docs/current/sql-altertable.html) 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](https://www.postgresql.org/docs/current/routine-vacuuming.html) 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.

```elixir
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)`:

```elixir
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](https://github.com/fly-apps/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](https://fedme.dev/it/services/postgres-elasticsearch).