Ottimizzazione delle funzioni analitiche per grandi volumi di dati: indicizzazione e partizionamento

Quando i dati diventano tanti (tipo i messaggi sulle deadline nelle chat aziendali), le query di selezione e di elaborazione iniziano a rallentare. Ecco i motivi principali:

1. Assenza di indici. Quando PostgreSQL per eseguire una query deve scansionare tutta la tua tabella (si chiama "Seq Scan" — scansione sequenziale), la query può metterci molto di più.
2. Query SQL inefficienti. Se le query sono scritte senza pensare all’ottimizzazione, anche con gli indici potresti trovarti nei guai in produzione. Tipo, ti sei dimenticato di usare le condizioni chiave in WHERE? Preparati ad aspettare a lungo.
3. Grandi volumi di dati in una sola tabella. Per esempio, se provi ad analizzare tutte le vendite di tutti gli anni insieme, nemmeno gli indici ti salvano.

Ma tranquillo, abbiamo due metodi collaudati per gestire la cosa: Indicizzazione e Partizionamento.

Uso degli indici per velocizzare le query

Ecco un esempio semplice di creazione di un indice:

CREATE INDEX idx_sales_date ON sales(transaction_date);

• Qui idx_sales_date è il nome dell’indice (puoi chiamarlo come vuoi, ma meglio usare qualcosa di sensato).
• ON sales(transaction_date) — indica per quale tabella e colonna viene creato l’indice.

Questo indice è super utile se filtri spesso le query sul campo transaction_date.

Esempio di query che va più veloce grazie a questo indice:

SELECT *
FROM sales
WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31';

Indicizzazione di chiavi composte

Se le tue query usano spesso una combinazione di più campi, tipo region e product_id, pensa a creare un indice composto:

CREATE INDEX idx_sales_region_product ON sales(region, product_id);

Ora query come questa vanno molto più veloci:

SELECT *
FROM sales
WHERE region = 'Nord America' AND product_id = 42;

Uso di indici unici

Gli indici unici non solo velocizzano la ricerca, ma garantiscono anche l’unicità dei valori nella colonna. Per esempio:

CREATE UNIQUE INDEX idx_unique_customer_email ON customers(email);

Così non potrai mai inserire per sbaglio due clienti con la stessa email.

Indicizzazione per funzioni analitiche

Alcune funzioni di analisi dati, come SUM, COUNT o AVG, possono usare l’indice per calcolare i valori più in fretta. Ecco un esempio:

CREATE INDEX idx_sales_amount ON sales(amount);

Query:

SELECT SUM(amount)
FROM sales 
WHERE transaction_date >= '2023-01-01';

andrà più veloce grazie all’indice.

Partizionamento delle tabelle per lavorare con grandi volumi di dati

Il partizionamento delle tabelle è il processo di suddivisione di una grande tabella in parti logiche più piccole, chiamate partizioni. Per esempio, puoi dividere la tabella sales in partizioni per anno: sales_2021, sales_2022 ecc.

Pensi sia difficile? In realtà PostgreSQL lo rende più facile di quanto sembri.

Tipi di partizionamento

1. Partizionamento per intervallo (Range Partitioning). I dati vengono divisi in base a un intervallo, tipo per data.
2. Partizionamento per lista (List Partitioning). I dati vengono divisi in base a valori precisi, tipo per regione.
3. Partizionamento per hash (Hash Partitioning). Usa una funzione hash per dividere i dati (si usa raramente a mano).

Creazione di una tabella partizionata

Creiamo una tabella delle vendite partizionata per anno.

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    transaction_date DATE NOT NULL,
    amount NUMERIC,
    region TEXT
) PARTITION BY RANGE (transaction_date);

Ora creiamo le partizioni per gli anni diversi:

CREATE TABLE sales_2021 PARTITION OF sales
FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE sales_2022 PARTITION OF sales
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

Le query che filtrano per data lavoreranno automaticamente solo con la partizione giusta. Puoi verificarlo facilmente con il comando EXPLAIN.

Esempio con partizionamento

Ecco come sarebbe una query per sommare le vendite solo del 2021:

SELECT SUM(amount)
FROM sales
WHERE transaction_date BETWEEN '2021-01-01' AND '2021-12-31';

Come vedi, PostgreSQL lavora solo con la partizione necessaria sales_2021, senza scansionare tutta la tabella.

Esempio: ottimizzazione del calcolo delle metriche per regione

Supponiamo che tu voglia calcolare il totale delle vendite per regione. Senza indici e partizioni ci metteresti una vita. Prima creiamo un indice sulla colonna region:

CREATE INDEX idx_sales_region ON sales(region);

La tua query:

SELECT region, SUM(amount)
FROM sales
GROUP BY region;

Ora l’elaborazione è più veloce grazie all’indice.

Esempio: partizionamento dei dati temporali

Per dati temporali, come transazioni o log, crea partizioni per mese. Per esempio:

CREATE TABLE sales_monthly PARTITION BY RANGE (transaction_date);

CREATE TABLE sales_jan_2023 PARTITION OF sales_monthly
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

Query:

SELECT SUM(amount)
FROM sales_monthly
WHERE transaction_date >= '2023-01-01' AND transaction_date < '2023-02-01';

andrà più veloce perché PostgreSQL legge solo la partizione sales_jan_2023.

Esempio: combinare indicizzazione e partizionamento

Indicizzazione e partizionamento si possono combinare per ottenere il massimo delle performance. Per esempio, puoi creare indici dentro ogni partizione. Ecco come:

CREATE INDEX idx_sales_amount_jan_2023 ON sales_jan_2023(amount);

Come evitare gli errori più comuni

Tanti errori di performance sono dovuti a un uso sbagliato di indici e partizionamento. Per esempio:

• Avere troppi indici può rallentare le operazioni di inserimento.
• Le partizioni vanno progettate in modo che siano riempite in modo uniforme; partizioni troppo piccole o troppo grandi peggiorano le performance.
• Dimenticarsi di analizzare le performance (EXPLAIN ANALYZE) prima di ottimizzare — è come provare a riparare la macchina senza guardare sotto il cofano.

Controlla sempre se le tue ottimizzazioni portano davvero un aumento di velocità e non aver paura di sperimentare.