Table partitioning in PostgreSQL
Published: 2023-10-03 14:23:24

---
title: Table partitioning in PostgreSQL
published: true
date: 2023-06-08 00:00:00 UTC
tags: 
canonical_url: 
---

Se il concetto di _[ereditarietà](https://theredcode.it/database/table-inheritance-postgresql)_ permette di creare una relazione tra una o più tabelle, il partizionamento ne consente una suddivisione logica che si adatta meglio alle esigenze di business.

Partizionare la tabella, chiamato **table partitioning** , significa dividere una tabella in pezzi più piccoli.

Il partizionamento delle tabelle offre molti vantaggi in termini di prestazioni per le tabelle che contengono grandi quantità di dati.

E, indovina un po’? PostgreSQL consente il partizionamento delle tabelle tramite l’_ereditarietà_ delle tabelle.

<ins>Ogni partizione viene creata come tabella figlia di una singola tabella genitore.</ins>

Un esempio potrebbe essere quello delle prenotazioni effettuate da un’agenzia di viaggi internazionale: tenendo in una tabella unica tutte le prenotazioni eseguite nel corso della sua attività, la lista rischia di allungarsi di molto nel giro di poco tempo.

Per questo, creare delle tabelle partizionate per anno/mese, permette alle persone che vi lavorano di recuperare le informazioni più velocemente, e quindi migliorare anche le prestazioni del database.

# Esempio

Riprendendo l’esempio fatto in precedenza, immaginiamo di voler memorizzare le informazioni di tutti i voli prenotati nel corso dell’attività dell’agenzia, con il relativo numero di volo, compagnia, stato del volo e data di prenotazione: creiamo la tabella _bookings_ con queste informazioni.

```
CREATE TABLE bookings(id serial, flightno int, flightcompany varchar(100), status varchar(100), booking_date timestamp)

```

Creiamo anche una serie di tabelle che rappresentino i voli per anno e mese, e che ci permettano di suddividere i dati attraverso più “elenchi”, di dimensione ridotta: nella creazione delle tabelle, specifichiamo di creare la tabella esattamente come quella originale, ma con un controllo sulle date inserite:

```
CREATE TABLE bookings_2023_jan(check(booking_date >= '2023-01-01' AND booking_date <= '2023-01-31')) inherits(bookings);
CREATE TABLE bookings_2023_feb(check(booking_date >= '2023-02-01' AND booking_date <= '2023-02-28')) inherits(bookings);
CREATE TABLE bookings_2023_mar(check(booking_date >= '2023-03-01' AND booking_date <= '2023-03-31')) inherits(bookings);
...

```

Se usi [pgAdmin](https://dev.to/scottw/pgadmin---postgresql-tools-5ejk) come client per accedere al tuo database, questo è quello che vedrai nel menù di sinistra: due frecce accanto alle tabelle, dove le tabelle derivate riportano una freccia in entrata per indicare che sono create a partire da quella principale, ossia _bookings_:

![](https://theredcode.it/img/postgres-table-partitioning-1.png)

A questo punto, applichiamo un _barbatrucco_ per poter partizionare i nostri dati all’inserimento: creiamo una_procedura_ che, verificati i dati che stanno per essere inseriti all’interno della tabella _bookings_, procediamo ad inserire i dati nella tabella corretta, secondo la data specificata nel campo _booking\_date_:

```
create or replace function on_insert() returns trigger as $$
begin
if (new.booking_date >= timestamp '2023-01-01' and new.booking_date <= timestamp '2023-01-31') then
insert into bookings_2023_jan values(new.*) ;
elsif (new.booking_date >= timestamp '2023-02-01' and new.booking_date <= timestamp '2023-02-28') then
insert into bookings_2023_feb values(new.*) ;
elsif (new.booking_date >= timestamp '2023-03-01' and new.booking_date <= timestamp '2023-03-31') then
insert into bookings_2023_mar values(new.*) ;
else
raise 'Enter valid booking date' ;
end if;
return null;
end;
$$ LANGUAGE plpgsql;

```

![](https://theredcode.it/img/postgres-table-partitioning-2.png)

All’interno di questa funzione, andiamo a specificare quali sono i controlli da eseguire sul campo _booking\_date_all’interno dell’oggetto denominato _new_: questo contiene i dati inseriti dall’utente che richiede l’aggiunta di un nuovo record nella tabella, e ne riporta le informazioni principali. Per ogni mese, andiamo a creare un ramo _if_ e terminiamo, eventualmente, con un messaggio di errore, che riporta di creare una prenotazione che segua una data valida tra quelle che si vogliono memorizzare.

Questa funzione è una funzione “speciale”, perché va a restituire un **trigger** : si tratta di un oggetto che si attiva e _scatena_ delle operazioni non appena vengono rilevate delle azioni particolari. In questo caso, andiamo infatti a creare un _trigger_ che, all’inserimento di un nuovo record nella tabella _bookings_, vada a richiamare la funzione appena creata, così che i dati vengano correttamente inseriti nella tabella partizione:

```
create trigger booking_entry before insert on bookings for each row execute procedure on_insert();

```

# Inserimento

Proviamo a inserire dei dati, e vediamo cosa succede:

```
INSERT INTO bookings(flightno, flightcompany, status, booking_date)
 VALUES (12345, 'AirCanada', 'Ontime','2023-01-07');

INSERT INTObookings(flightno, flightcompany, status, booking_date)
 VALUES (67890, 'Lufhtansa', 'Ontime','2023-02-16');

INSERT INTO bookings(flightno, flightcompany, status, booking_date)
 VALUES (13579, 'Vueling', 'Delayed','2023-03-09');

```

Riportando l’elenco delle singole tabelle, vediamo che i record sono stati inseriti correttamente:

```
SELECT * FROM bookings_2023_feb;
>>>
2 67890 "Lufhtansa" "Ontime" "2023-02-16 00:00:00"

```

Notiamo l’ID del record del mese di febbraio: è pari a 2, e non 1, nonostante sia l’unico della tabella. Questo perché in PostgreSQL, quando si eredita una colonna che ha come tipo di dato _serial_ dalla tabella genitore, la sequenza è condivisa dalla tabella genitore e figlia.

Questo vuol dire che se riprendiamo il concetto di ereditarietà, ci aspettiamo che tutti i record inseriti finora siano 3:

```
SELECT * FROM bookings;
>>>

1 12345 "AirCanada" "Ontime" "2023-01-07 00:00:00"
3 67890 "Lufhtansa" "Ontime" "2023-02-16 00:00:00"
4 13579 "Vueling" "Delayed" "2023-03-09 00:00:00"

```

Allo stesso modo, utilizzando l’operatore ONLY, visto nell’articolo dedicato all’_[ereditarietà](https://theredcode.it/database/table-inheritance-postgresql)_, noteremo che la tabella è vuota:

```
SELECT * FROM ONLY bookings;
>>>

```

Questo perché la funzione creata in precedenza interviene _prima_ che il record sia inserito in _bookings_ e lo smista nella tabella corretta, portando due vantaggi: i dati sono correttamente partizionati all’origine dell’inserimento, senza la necessità di avere informazioni duplicate, e di conseguenza non si va a riempire la tabella principale di dati che la renderebbero un elefante a 6 gambe.

# Risorse utili

- [Libro sulle Basi di Dati](https://amzn.to/3ABUix3) (edito da [Apogeo](https://www.apogeonline.com/))
- [Documentazione ufficiale](https://www.postgresql.org/docs/current/ddl-partitioning.html)
- [If-else in Postgresql](https://www.commandprompt.com/education/postgresql-else-if-statement-with-examples/)