Ottimizzazione del caricamento massivo dei dati

Immagina di dover caricare un milione di righe di dati. Se lo fai lentamente, il tuo server sarà occupato per un sacco di tempo, gli utenti potrebbero notare rallentamenti nel database, e peggio ancora — il tuo caffè potrebbe raffreddarsi prima che il processo finisca. Ottimizzare ti permette di evitare di sovraccaricare il server, ridurre i tempi di attesa e minimizzare la probabilità di errori durante il caricamento.

Partiamo dai passi semplici, poi passiamo a trucchetti più avanzati e furbi.

Disattivare indici e trigger

Indici e trigger sono cose fantastiche che rendono i nostri database intelligenti e reattivi. Ma durante il caricamento massivo possono rallentare parecchio il processo, perché il server cercherà di aggiornare gli indici ed eseguire i trigger per ogni riga che carichi.

Per liberare temporaneamente il sistema da questo peso, puoi disattivarli.

Esempio di disattivazione di indici e trigger:

-- Disattiviamo i trigger per la tabella
ALTER TABLE students DISABLE TRIGGER ALL;

-- Carichiamo i dati
COPY students FROM '/path/to/students.csv' DELIMITER ',' CSV HEADER;

-- Riattiviamo i trigger
ALTER TABLE students ENABLE TRIGGER ALL;

Come funziona?

1. Disattiviamo temporaneamente tutti i trigger con il comando DISABLE TRIGGER ALL.
2. Dopo il caricamento dei dati, riattiviamo i trigger con ENABLE TRIGGER ALL.

Errore tipico: se ti dimentichi di riattivare i trigger, alcuni processi automatici (tipo l’aggiornamento dei campi di default) potrebbero non funzionare correttamente. Quindi ricordati sempre di rimettere tutto a posto — è come uscire dalla modalità "aereo" sul telefono.

Uso delle transazioni

Le transazioni ti permettono di caricare tutti i dati come se fosse un’unica grande operazione. Se qualcosa va storto, puoi fare il rollback e il tuo database non diventa una zuppa di dati a metà.

Esempio di uso della transazione:

-- Iniziamo la transazione
BEGIN;

-- Carichiamo i dati
COPY courses FROM '/path/to/courses.csv' DELIMITER ',' CSV HEADER;

-- Confermiamo le modifiche
COMMIT;

Perché è più veloce?

Se carichi i dati senza transazione, il server conferma le modifiche dopo ogni riga. Con la transazione, lo fa solo una volta alla fine, risparmiando un sacco di tempo.

Disattivare i controlli di integrità

Se non ti serve controllare la foreign key o l’unicità durante il caricamento, disattivali. Altrimenti il database controllerà ogni riga, rallentando tutto.

Esempio di disattivazione dei controlli di integrità:

SET session_replication_role = 'replica';

-- Carichiamo i dati
COPY enrollments FROM '/path/to/enrollments.csv' DELIMITER ',' CSV HEADER;

SET session_replication_role = 'origin';

session_replication_role = 'replica' disattiva i controlli di integrità dei dati (tipo unicità e vincoli FOREIGN KEY).

Aumentare la memoria di esecuzione

Configurare la memoria di PostgreSQL può migliorare le performance del caricamento. I parametri chiave sono work_mem e maintenance_work_mem.

Esempio di aumento della memoria:

-- Aumentiamo la memoria
SET work_mem = '64MB';
SET maintenance_work_mem = '256MB';

-- Carichiamo i dati
COPY teachers FROM '/path/to/teachers.csv' DELIMITER ',' CSV HEADER;

A cosa serve?

• work_mem viene usato per operazioni intermedie, tipo sort o hash.
• maintenance_work_mem influisce sulle operazioni sugli indici, tipo la loro ricostruzione.

Consiglio: Occhio ad aumentare la memoria, soprattutto su sistemi con risorse limitate.

Preparare i dati prima del caricamento

Preparare i dati può ridurre di molto il tempo di caricamento. Per esempio, se hai righe duplicate, filtrale prima così PostgreSQL non perde tempo su dati inutili.

Esempio di pulizia dei dati:

Se hai un file con righe duplicate, puoi usare Python per eliminarle.

import pandas as pd

# Carichiamo il file CSV
data = pd.read_csv('students.csv')

# Rimuoviamo i duplicati
data = data.drop_duplicates()

# Salviamo il CSV pulito
data.to_csv('students_clean.csv', index=False)

Partizionamento dei dati

Se hai un file enorme, dividilo in file più piccoli. Così PostgreSQL può gestire i dati in modo più efficiente.

Esempio:

Dividi il file large_data.csv in pezzi da 1000 righe usando Linux:

split -l 1000 large_data.csv chunk_

Poi caricali uno alla volta:

COPY students FROM 'chunk_aa' DELIMITER ',' CSV HEADER;
COPY students FROM 'chunk_ab' DELIMITER ',' CSV HEADER;
-- E così via

Caricamento in background

Se puoi, usa processi in background per caricare i dati, così non sovraccarichi il database principale.

Strumenti come pg_cron ti aiutano a lanciare job schedulati.

Esempio: configurare un caricamento in background con pg_cron:

CREATE EXTENSION pg_cron;

SELECT cron.schedule('*/5 * * * *', $$COPY students FROM '/path/to/data.csv' DELIMITER ',' CSV HEADER$$);

Ogni 5 minuti i dati dal file verranno caricati nella tabella.

Questo è solo un esempio, in realtà non farlo così! Volevo solo farti vedere che PostgreSQL è super flessibile e puoi gestire il caricamento dati direttamente dagli script SQL.

Trappole nascoste

Alcune cose a cui fare attenzione:

• Se disattivi indici e trigger, ricordati di riattivarli! Se te ne dimentichi, dovrai sistemare errori dopo il caricamento.
• Quando aumenti la memoria, tieni d’occhio le risorse del server: una query troppo affamata può bruciarsi tutta la RAM.
• Se usi le transazioni, assicurati che il file dati non abbia errori gravi. Basta un errore per annullare tutto il caricamento.

Consigli per il futuro

Ora sai come ottimizzare il caricamento massivo dei dati — dalla disattivazione degli indici all’uso delle transazioni. Queste skill ti aiuteranno non solo a caricare i dati più in fretta, ma anche a risparmiare risorse del server, nervi, caffè e a lasciare gli utenti contenti.

La prossima volta che dovrai lavorare con file da gigabyte, sarai pronto!