Ottimizzazione delle funzioni per lavorare con grandi quantità di dati

Quando parliamo di ottimizzazione delle funzioni in PostgreSQL, di solito intendiamo due cose fondamentali: indicizzazione e partizionamento. Queste due tecniche ti aiutano a gestire grandi quantità di dati più velocemente, eliminando calcoli inutili e permettendo di accedere ai dati "al punto giusto". Vediamo tutto nel dettaglio.

Gli indici nel mondo dei database funzionano come gli indici nei libri. Quando cerchi un'informazione in un libro, non leggi tutte le pagine una dopo l'altra. Apri l'indice, trovi l'argomento che ti serve e vai direttamente alla pagina giusta. Più o meno la stessa cosa fanno gli indici in PostgreSQL.

Creazione degli indici

Gli indici si creano con il comando CREATE INDEX. Ecco un esempio semplice:

-- Creiamo un indice sulla colonna id della tabella users per velocizzare la ricerca
CREATE INDEX idx_users_id ON users (id);

Ora, se esegui una query tipo:

SELECT * FROM users WHERE id = 42;

PostgreSQL userà l'indice creato per trovare velocemente la riga che ti serve.

Esempio: Ottimizzazione di una funzione usando gli indici

Supponiamo di avere una funzione che seleziona i dati degli ordini dalla tabella orders per un utente:

CREATE OR REPLACE FUNCTION get_user_orders(user_id INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE user_id = user_id;
END; 
$$ LANGUAGE plpgsql;

Se la tabella orders ha milioni di righe, l'esecuzione della funzione sarà lenta. Soluzione? Creiamo un indice su user_id:

CREATE INDEX idx_orders_user_id ON orders (user_id);

Ora la query dentro la funzione sarà molto più veloce, perché PostgreSQL userà l'indice per trovare le righe.

Tipi di indici

PostgreSQL supporta diversi tipi di indici, ma i più usati sono B-TREE e GIN. Ecco un confronto veloce:

Se vuoi approfondire gli indici, dai un'occhiata alla documentazione ufficiale di PostgreSQL.

Partizionamento dei dati

Se gli indici servono a velocizzare la ricerca, il partizionamento è un metodo che ti aiuta a dividere una tabella in "pezzi" più piccoli (partizioni). È utile quando hai una quantità enorme di dati in una sola tabella.

Immagina di avere una tabella orders che contiene ordini degli ultimi 10 anni. Se fai una query per trovare gli ordini dell'ultimo mese, PostgreSQL comunque scansiona tutta la tabella, che è pesante. Il partizionamento risolve il problema dividendo i dati, per esempio, per anno.

Creazione di una tabella partizionata

Ecco come puoi creare una tabella partizionata:

-- Creiamo la tabella orders come partizione principale
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    user_id INT NOT NULL
) PARTITION BY RANGE (order_date);

-- Creiamo le tabelle figlie per ogni anno
CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2022 PARTITION OF orders FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

Ora, quando esegui una query tipo:

SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2023-02-01';

PostgreSQL capisce subito che deve cercare solo nella tabella orders_2023, invece di controllare tutta la tabella.

Uso del partizionamento nelle funzioni

Immagina di avere una funzione che seleziona gli ordini per un certo anno. Grazie al partizionamento, le query dentro la funzione saranno più veloci, perché PostgreSQL lavorerà solo con la tabella figlia giusta.

CREATE OR REPLACE FUNCTION get_orders_by_year(year INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE order_date >= make_date(year, 1, 1) 
      AND order_date < make_date(year + 1, 1, 1);
END;
$$ LANGUAGE plpgsql;

Casi pratici

1. Casi di indicizzazione

Ricerca per stringhe: se hai una tabella con prodotti e cerchi spesso i prodotti per nome, crea un indice sul campo name:

CREATE INDEX idx_products_name ON products (name);

Velocizzare l'ordinamento: se nelle query usi spesso l'ordinamento per data, crea un indice:

CREATE INDEX idx_orders_date ON orders (order_date);

2. Casi di partizionamento

Dati storici: se la tabella contiene dati con timestamp, il partizionamento per giorni, mesi o anni velocizza molto le query.

Dati geografici: se la tabella contiene dati per paese, crea partizioni per ogni paese.

Errori potenziali e come risolverli

Tanti sviluppatori sbagliano creando troppi indici. Questo rallenta le operazioni di inserimento e aggiornamento, perché PostgreSQL deve aggiornare gli indici ogni volta che la tabella cambia. Consiglio: crea indici solo sui campi che usi spesso nei filtri o nell'ordinamento.

Un altro errore tipico è il partizionamento sbagliato. Se crei troppe partizioni piccole (tipo per giorno invece che per mese), rischi di avere overhead nella gestione di tutte queste tabelle.