Gestione delle partizioni PostgreSQL con l'estensione pg_partman

Il partizionamento delle tabelle PostgreSQL fornisce un framework per la gestione ad alte prestazioni di input e reporting dei dati. Utilizzare il partizionamento per database che richiedono un input molto veloce di grandi quantità di dati. Il partizionamento fornisce anche query di tabelle di grandi dimensioni più veloci. Il partizionamento consente di conservare i dati senza influire sull'istanza del database perché richiede meno risorse I/O.

Utilizzando il partizionamento, è possibile suddividere i dati in blocchi di dimensioni personalizzate per l'elaborazione. Ad esempio, è possibile partizionare i dati delle serie temporali per intervalli quali orario, giornaliero, settimanale, mensile, trimestrale, annuale, personalizzato o qualsiasi combinazione di questi. Per un esempio di dati di serie temporali, se la tabella è stata partizionata per ora, ogni partizione conterrà un'ora di dati. Se si partiziona la tabella delle serie temporali per giorno, le partizioni conterranno i dati di un giorno e così via. La chiave di partizione controlla le dimensioni di una partizione.

Quando si utilizza un comando SQL INSERT o UPDATE in una tabella partizionata, il motore database indirizza i dati alla partizione appropriata. Le partizioni di tabella PostgreSQL che memorizzano i dati sono tabelle figlio della tabella principale.

Durante le letture delle query di database, l'ottimizzatore PostgreSQL esamina la clausola WHERE della query e, se possibile, indirizza la scansione del database solo alle partizioni pertinenti.

A partire dalla versione 10, PostgreSQL utilizza il partizionamento dichiarativo per implementare il partizionamento delle tabelle. Questo è noto anche come partizionamento PostgreSQL nativo. Prima di PostgreSQL versione 10, per implementare le partizioni venivano utilizzati i trigger.

Il partizionamento delle tabelle PostgreSQL fornisce le seguenti funzionalità:

• Creazione di nuove partizioni in qualsiasi momento.

• Intervalli di partizione variabili.

• Partizioni scollegabili e ricollegabili utilizzando istruzioni DDL (Data Definition Language).

  Ad esempio, le partizioni scollegabili sono utili per rimuovere i dati storici dalla partizione principale, conservando i dati storici per l'analisi.

• Le nuove partizioni ereditano le proprietà della tabella di database padre, tra cui:

  • Indici

  • Chiavi primarie, che devono includere la colonna delle chiavi di partizione

  • Chiavi esterne

  • Vincoli check

  • Riferimenti

• Creazione di indici per la tabella completa o per ogni partizione specifica.

Non è possibile modificare lo schema per una singola partizione. Tuttavia, è possibile modificare la tabella padre (ad esempio, aggiungendo una nuova colonna), che si propaga alle partizioni.

Panoramica dell'estensione PostgreSQL pg_partman

È possibile utilizzare l'estensione pg_partman PostgreSQL per automatizzare la creazione e la manutenzione delle partizioni di tabella. Per informazioni più generali, consulta PG Partition Manager nella documentazione di pg_partman.

Nota

L'estensione pg_partman è supportata sul motore Amazon RDS for PostgreSQL versioni 12.5 e successive.

Invece di dover creare manualmente ogni partizione, è possibile configurare pg_partman con le seguenti impostazioni:

• Tabella da partizionare

• Tipo di partizione

• Chiave di partizione

• Granularità delle partizioni

• Opzioni di pre-creazione e gestione delle partizioni

Dopo aver creato una tabella con partizioni PostgreSQL, la si registra con pg_partman chiamando la funzione create_parent. In questo modo vengono create le partizioni necessarie in base ai parametri passati alla funzione.

L'estensione pg_partman fornisce anche la funzione run_maintenance_proc, che è possibile chiamare su base pianificata per gestire automaticamente le partizioni. Per pianificare la creazione delle partizioni appropriate in base alle esigenze, puoi pianificare questa funzione in modo che venga eseguita periodicamente (ad esempio, ogni ora). È inoltre possibile assicurarsi che le partizioni vengano eliminate automaticamente.

Abilitazione dell'estensione pg_partman

Se disponi di più database all'interno della stessa istanza database per cui desideri gestire le partizioni, è necessario abilitare l'estensione pg_partman separatamente per ogni database. Per abilitare l'estensione pg_partman per un database specifico, crea lo schema di manutenzione delle partizioni, quindi crea l'estensione pg_partman nel modo seguente:

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

Nota

Per creare l'estensione pg_partman, assicurati di disporre dei privilegi rds_superuser.

Se viene restituito un errore come il seguente, concedi i privilegi rds_superuser all'account o utilizza l'account utente avanzato.

ERROR: permission denied to create extension "pg_partman"
HINT: Must be superuser to create this extension.

Per concedere i privilegi rds_superuser, collegati con l'account utente avanzato ed emetti il seguente comando:

GRANT rds_superuser TO user-or-role;

Per gli esempi che mostrano l’uso dell’estensione pg_partman, si utilizza la tabella di database e la partizione di esempio seguenti. Questo database utilizza una tabella partizionata basata su un timestamp. Uno schema data_mart contiene una tabella denominata events con una colonna denominata created_at. Nella tabella events sono incluse le seguenti impostazioni:

• Chiavi primarie event_id e created_at, che devono avere la colonna utilizzata per guidare la partizione.

• Un vincolo di controllo ck_valid_operation per applicare i valori per una colonna della tabella operation .

• Due chiavi esterne, dove una (fk_orga_membership)) punta alla tabella esterna organization e l'altra (fk_parent_event_id) è una chiave esterna autoreferenziata.

• Due indici, dove uno (idx_org_id) è per la chiave esterna e l'altro (idx_event_type) è per il tipo di evento.

Le seguenti istruzioni DDL creano questi oggetti, che verranno inclusi automaticamente in ogni partizione.

CREATE SCHEMA data_mart;
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
        org_name TEXT,
        CONSTRAINT pk_organization PRIMARY KEY (org_id)  
    );

CREATE TABLE data_mart.events(
        event_id        BIGSERIAL, 
        operation       CHAR(1), 
        value           FLOAT(24), 
        parent_event_id BIGINT, 
        event_type      VARCHAR(25), 
        org_id          BIGSERIAL, 
        created_at      timestamp, 
        CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at), 
        CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'), 
        CONSTRAINT fk_orga_membership 
            FOREIGN KEY(org_id) 
            REFERENCES data_mart.organization (org_id),
        CONSTRAINT fk_parent_event_id 
            FOREIGN KEY(parent_event_id, created_at) 
            REFERENCES data_mart.events (event_id,created_at)
    ) PARTITION BY RANGE (created_at);

CREATE INDEX idx_org_id     ON  data_mart.events(org_id);
CREATE INDEX idx_event_type ON  data_mart.events(event_type);

Configurazione delle partizioni utilizzando la funzione create_parent

Dopo aver abilitato l'estensione pg_partman, utilizza la funzione create_parent per configurare le partizioni all'interno dello schema di manutenzione delle partizioni. In questo esempio viene utilizzato l’esempio della tabella events creato in Abilitazione dell'estensione pg_partman. Richiama la funzione create_parent come segue:

SELECT partman.create_parent( p_parent_table => 'data_mart.events',
 p_control => 'created_at',
 p_type => 'native',
 p_interval=> 'daily',
 p_premake => 30);

I parametri sono i seguenti:

• p_parent_table – La tabella partizionata padre. Questa tabella deve già esistere ed essere completa, deve ovvero includere lo schema.

• p_control – Colonna su cui basare il partizionamento. Il tipo di dati deve essere intero o basato sul tempo.

• p_type: il tipo è 'native' o 'partman'. In genere, è consigliabile utilizzare il tipo native per migliorare le prestazioni e la flessibilità. Il tipo partman si basa sull'ereditarietà.

• p_interval – Intervallo di tempo o intervallo intero per ogni partizione. I valori di esempio includono: daily, orario e così via.

• p_premake – Il numero di partizioni da creare in anticipo per supportare nuovi inserimenti.

Per una descrizione completa della funzione create_parent, consulta Funzioni di creazione nella documentazione pg_partman.

Configurazione della manutenzione delle partizioni utilizzando la funzione run_maintenance ance_proc

È possibile eseguire operazioni di manutenzione delle partizioni per creare automaticamente nuove partizioni, scollegare partizioni o rimuovere partizioni obsolete. La manutenzione delle partizioni si basa sulla funzione run_maintenance_proc dell'estensione pg_partman e dell'estensione pg_cron, che avvia un pianificatore interno. Lo scheduler pg_cron esegue automaticamente le istruzioni SQL, le funzioni e le procedure definite nei database.

Nell'esempio seguente viene utilizzato l'esempio della tabella events creata in Abilitazione dell'estensione pg_partman per impostare l'esecuzione automatica delle operazioni di manutenzione delle partizioni. Come prerequisito, aggiungere pg_cron al parametro shared_preload_libraries nel gruppo di parametri dell'istanza database.

CREATE EXTENSION pg_cron;

UPDATE partman.part_config 
SET infinite_time_partitions = true,
    retention = '3 months', 
    retention_keep_table=true 
WHERE parent_table = 'data_mart.events';
SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);

Di seguito, è riportata una spiegazione dettagliata dell'esempio precedente:

1. Modifica il gruppo di parametri associato all'istanza database e aggiungi pg_cron al valore del parametro shared_preload_libraries. Perché questa modifica abbia effetto, è necessario riavviare l'istanza database. Per ulteriori informazioni, consulta Modifica di parametri in un gruppo di parametri del database.

2. Emettere il comando CREATE EXTENSION pg_cron; utilizzando un account con le autorizzazioni rds_superuser. In questo modo, viene abilitata l’estensione pg_cron. Per ulteriori informazioni, consulta Pianificazione della manutenzione con l'estensione PostgreSQL pg_cron.

3. Emettere il comando UPDATE partman.part_config per regolare le impostazioni pg_partman per la tabella data_mart.events.

4. Eseguire il comando SET . . . per configurare la tabella data_mart.events, con le seguenti clausole:

   1. infinite_time_partitions = true, – Configura la tabella in modo da poter creare automaticamente nuove partizioni senza limiti.

   2. retention = '3 months', – Configura la tabella in modo che venga conservata per un massimo di tre mesi.

   3. retention_keep_table=true – Configura la tabella in modo che quando il periodo di conservazione è scaduto, la tabella non venga eliminata automaticamente. Le partizioni precedenti al periodo di conservazione vengono invece scollegate dalla tabella padre.

5. Eseguire il comando SELECT cron.schedule . . . per creare una chiamata di funzione pg_cron. Questa chiamata definisce la frequenza con cui lo scheduler esegue la procedura di manutenzione pg_partman, partman.run_maintenance_proc. Per questo esempio, la procedura viene eseguita ogni ora.

Per una descrizione completa della funzione run_maintenance_proc, consulta Funzioni di manutenzione nella documentazione di pg_partman.