Come funziona il partitioning delle tabelle in PostgreSQL?

Question

Accepted Answer

**Il partitioning** suddivide una tabella grande in pezzi fisici più piccoli (**partizioni**) in base al valore di una colonna, mentre rimane una singola tabella logica. Il **partitioning dichiarativo** di PostgreSQL (Postgres 10+) lo rende pulito. Migliora le prestazioni e la gestibilità per tabelle molto grandi.

## Partitioning dichiarativo (il modo moderno)

```sql
-- create a partitioned table, partitioned by RANGE on a date
CREATE TABLE orders (id INT, order_date DATE, amount NUMERIC)
PARTITION BY RANGE (order_date);

-- create partitions for each range
CREATE TABLE orders_2023 PARTITION OF orders
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
```

`orders` è una singola tabella logica, fisicamente memorizzata in partizioni per anno. Gli inserimenti e le query vengono indirizzati automaticamente alla partizione corretta.

## Partition pruning — il principale beneficio di prestazione

```sql
-- a query filtered on the partition key scans ONLY relevant partitions
SELECT * FROM orders WHERE order_date >= '2024-06-01';
-- → Postgres skips orders_2023 entirely ("partition pruning") — scans only orders_2024
```

**Partition pruning** è il vantaggio principale: le query che filtrano sulla partition key toccano solo le partizioni rilevanti, riducendo drasticamente i dati scansionati su tabelle enormi.

## Strategie di partitioning

```text
PARTITION BY RANGE → value ranges (dates, numbers) — common for time-series
PARTITION BY LIST  → discrete values (region, status)
PARTITION BY HASH  → hash of the key (even distribution across N partitions)
```

## Benefici di manutenzione

```sql
-- drop old data instantly by dropping a whole partition (vs slow DELETE)
DROP TABLE orders_2020;              -- removes all 2020 data instantly
-- vs DELETE FROM orders WHERE year = 2020 → slow, creates bloat

-- detach/attach partitions; automate with pg_partman extension
```

L'eliminazione di un'intera partizione per archiviare/rimuovere dati vecchi è istantanea — un grande vantaggio operativo rispetto a `DELETE` per dataset di grandi dimensioni.

## Perché è importante

Il partitioning delle tabelle è una tecnica importante in PostgreSQL per gestire e interrogare **tabelle molto grandi**, e comprenderla è prezioso per lavorare con Postgres su larga scala.

Man mano che le tabelle crescono a centinaia di milioni o miliardi di righe, il partitioning affronta i problemi di prestazione e gestibilità suddividendo la tabella in pezzi fisici più piccoli.

Il **partitioning dichiarativo** di Postgres (Postgres 10+) lo rende pulito ed è lo standard moderno da conoscere.

Il principale beneficio di prestazione è **partition pruning** — le query che filtrano sulla partition key scansionano solo le partizioni rilevanti (saltando le altre), riducendo drasticamente i dati scansionati per tabelle enormi (particolarmente prezioso per i dati time-series filtrati per data, un caso molto comune).

Oltre alle prestazioni, il partitioning fornisce importanti **benefici di manutenzione**: l'archiviazione o l'eliminazione di dati vecchi diventa istantanea eliminando un'intera partizione (rispetto a un lento e bloat-creato `DELETE` di milioni di righe — un significativo vantaggio operativo per dati con policy di retention, come log ed eventi).

Comprendere le **strategie** (RANGE per intervalli di data/numerici — comune per time-series; LIST per valori discreti; HASH per distribuzione uniforme) e **quando utilizzare il partitioning** (tabelle molto grandi con una partition key naturale, specialmente time-series con archiviazione regolare — non piccole tabelle dove l'indexing è sufficiente) riflette un buon giudizio.

Poiché la gestione di tabelle molto grandi è una vera sfida nei sistemi PostgreSQL scalabili, e poiché il partitioning dichiarativo (con partition pruning e efficiente manutenzione tramite partition-drop) è lo strumento standard — complementare all'indexing — comprendere il partitioning delle tabelle PostgreSQL è una conoscenza preziosa e praticamente rilevante per la scalabilità del database, particolarmente importante per applicazioni large-scale, ad alto volume o time-series e un argomento comune che dimostra la capacità di gestire Postgres su larga scala.