Gestione dei Dati Con Partizionamento PostgreSQL e pg_partman

Tutorial

PostgreSQL

Una gestione efficiente del database è fondamentale per gestire grandi set di dati mantenendo prestazioni ottimali e facilità di manutenzione. La partizionatura delle tabelle in PostgreSQL è un metodo robusto per dividere logicamente una grande tabella in pezzi più piccoli e gestibili chiamati partizioni. Questa tecnica aiuta a migliorare le prestazioni delle query, semplificare le attività di manutenzione e ridurre i costi di archiviazione.

Questo articolo approfondisce la creazione e la gestione della partizionatura delle tabelle in PostgreSQL, concentrandosi sull’estensione pg_partman per la partizionatura basata sul tempo e sul seriale. Vengono discusse in dettaglio i tipi di partizioni supportate in PostgreSQL, insieme a casi d’uso reali ed esempi pratici per illustrarne l’implementazione.

Introduzione

Le moderne applicazioni generano enormi quantità di dati, richiedendo strategie efficienti di gestione del database per gestire questi volumi. La partizionatura delle tabelle è una tecnica in cui una grande tabella è divisa in segmenti più piccoli e logicamente correlati. PostgreSQL offre un framework di partizionatura robusto per gestire in modo efficace tali set di dati.

Perché la Partizionatura?

Miglioramento delle prestazioni delle query. Le query possono saltare rapidamente le partizioni non rilevanti utilizzando l’esclusione dei vincoli o il pruning delle query.
Manutenzione semplificata. Operazioni specifiche della partizione come il vacuuming o il reindexing possono essere eseguite su set di dati più piccoli.
Archiviazione efficiente. Le partizioni più vecchie possono essere eliminate o archiviate senza incidere sul set di dati attivo.
Scalabilità. La suddivisione consente lo scaling orizzontale, in particolare negli ambienti distribuiti.

Partizionamento nativo vs basato su estensioni

Il partizionamento dichiarativo nativo di PostgreSQL semplifica molti aspetti del partizionamento, mentre le estensioni come pg_partman forniscono capacità aggiuntive di automazione e gestione, in particolare per casi d’uso dinamici.

Partizionamento nativo vs pg_partman

Feature	Native Partitioning	pg_partman
Automazione	Limitata	Completa
Tipi di partizioni	Range, Lista, Hash	Temporale, Seriale (avanzato)
Manutenzione	Richiesti script manuali	Automatizzata
Facilità d’uso	Richiede competenze SQL	Semplificata

Tipi di partizionamento delle tabelle in PostgreSQL

PostgreSQL supporta tre strategie di partizionamento primarie: Range, Lista e Hash. Ciascuna presenta caratteristiche uniche adatte a diversi casi d’uso.

Partizionamento per intervallo

Il partizionamento per intervallo divide una tabella in partizioni basate su un intervallo di valori in una colonna specifica, spesso una colonna di data o numerica.

Esempio: Dati di vendite mensili

SQL

CREATE TABLE sales (

    sale_id SERIAL,

    sale_date DATE NOT NULL,

    amount NUMERIC

) PARTITION BY RANGE (sale_date);

​

CREATE TABLE sales_2023_01 PARTITION OF sales

    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

Vantaggi

Efficiente per dati di serie temporali come log o transazioni
Supporta query sequenziali, come il recupero di dati per mesi specifici

Svantaggi

Richiede intervalli predefiniti, il che potrebbe portare a frequenti aggiornamenti dello schema

Partizionamento per elenchi

Il partizionamento per elenchi divide i dati in base a un insieme discreto di valori, come regioni o categorie.

Esempio: Ordini regionali

SQL

CREATE TABLE orders (

    order_id SERIAL,

    region TEXT NOT NULL,

    amount NUMERIC

) PARTITION BY LIST (region);

​

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US');

CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('EU');

Vantaggi

Ideale per set di dati con un numero finito di categorie (ad esempio, regioni, dipartimenti)
Semplice da gestire per un insieme fisso di partizioni

Svantaggi

Non adatto per categorie dinamiche o in espansione

Partizionamento hash

Il partizionamento hash distribuisce righe in un insieme di partizioni utilizzando una funzione hash. Ciò garantisce una distribuzione uniforme dei dati.

Esempio: Account utente

SQL

CREATE TABLE users (

    user_id SERIAL,

    username TEXT NOT NULL

) PARTITION BY HASH (user_id);

​

CREATE TABLE users_partition_0 PARTITION OF users

    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

Vantaggi

Garantisce una distribuzione bilanciata tra le partizioni, prevenendo i punti caldi
Adatto per carichi di lavoro uniformemente distribuiti

Svantaggi

Non leggibile dall’utente; le partizioni non possono essere identificate intuitivamente

pg_partman: Una guida completa

pg_partman è un’estensione di PostgreSQL che semplifica la gestione delle partizioni, in particolare per set di dati basati sul tempo e sui valori seriali.

Installazione e configurazione

pg_partman richiede l’installazione come estensione in PostgreSQL. Fornisce una serie di funzioni per creare e gestire tabelle partizionate dinamicamente.

Installare usando il tuo gestore di pacchetti:
Shell
sudo apt-get install postgresql-pg-partman
Crea l’estensione nel tuo database:
SQL
CREA ESTENSIONE pg_partman;

Configurazione della suddivisione

pg_partman supporta la suddivisione basata sul tempo e sulla serie, particolarmente utile per set di dati con dati temporali o identificatori sequenziali.

Esempio di Suddivisione Basata sul Tempo

SQL

CREATE TABLE logs (

    id SERIAL,

    log_time TIMESTAMP NOT NULL,

    message TEXT

);

​

SELECT partman.create_parent(

    p_parent_table := 'public.logs',

    p_control := 'log_time',

    p_type := 'time',

    p_interval := 'daily'

);

Questa configurazione:

Crea automaticamente partizioni giornaliere
Semplifica interrogazioni e manutenzione per i dati di log

Esempio di Suddivisione Basata sulla Serie

SQL

CREATE TABLE transactions (

    transaction_id BIGSERIAL PRIMARY KEY,

    details TEXT NOT NULL

);

​

SELECT partman.create_parent(

    p_parent_table := 'public.transactions',

    p_control := 'transaction_id',

    p_type := 'serial',

    p_interval := 100000

);

Questo crea partizioni ogni 100.000 righe, garantendo che la tabella principale rimanga gestibile.

Funzionalità di Automazione

Manutenzione Automatica

Usa run_maintenance() per garantire che le future partizioni siano pre-create:

SQL

SELECT partman.run_maintenance();

Politiche di Conservazione

Definisci periodi di conservazione per eliminare automaticamente le vecchie partizioni:

SQL

UPDATE partman.part_config

SET retention = '12 months'

WHERE parent_table = 'public.logs';

Vantaggi di pg_partman

Semplifica la creazione dinamica delle partizioni
Automatizza la pulizia e la manutenzione
Riduce la necessità di aggiornamenti manuali dello schema

Casi d’uso pratici per la suddivisione delle tabelle

Gestione dei log. Log ad alta frequenza suddivisi per giorno per un archivio e interrogazione semplificati.
Dati multi-regionali. Sistemi di e-commerce che suddividono gli ordini per regione per una migliore scalabilità.
Dati in serie temporale. Applicazioni IoT con dati telemetrici suddivisi.

Gestione dei Log

Dividi i log per giorno o mese per gestire efficientemente i dati ad alta frequenza.

SQL

SELECT partman.create_parent(

    p_parent_table := 'public.server_logs',

    p_control := 'timestamp',

    p_type := 'time',

    p_interval := 'monthly'

);

Dati Multiregionali

Partiziona i dati di vendita o inventario per regione per una migliore scalabilità.

SQL

CREATE TABLE sales (

    sale_id SERIAL,

    region TEXT NOT NULL

) PARTITION BY LIST (region);

Transazioni ad Alto Volume

Partiziona le transazioni per ID seriale per evitare indici ingombranti.

SQL

SELECT partman.create_parent(

    p_parent_table := 'public.transactions',

    p_control := 'transaction_id',

    p_type := 'serial',

    p_interval := 10000

);

Conclusione

La partizione delle tabelle è una tecnica indispensabile per gestire grandi set di dati. Le funzionalità integrate di PostgreSQL, combinate con l’estensione pg_partman, semplificano l’implementazione di strategie di partizionamento dinamiche e automatizzate. Questi strumenti consentono agli amministratori di database di migliorare le prestazioni, semplificare la manutenzione e scalare in modo efficace.

Il partizionamento è un fondamento per la gestione moderna dei database, specialmente nelle applicazioni ad alto volume. Comprendere e applicare questi concetti garantisce sistemi di database robusti e scalabili.

Source:
https://dzone.com/articles/postgresql-partitioning-pg-partman-data-management