Colonne generate PostgreSQL per filtri admin più veloci

Perché le schermate admin rallentano e diventano presto disordinate

Le schermate admin di solito iniziano semplici: una tabella, un paio di filtri, magari un ordinamento per “più recenti”. Poi arriva il lavoro vero. Il supporto vuole una ricerca che trovi clienti per nome, email e telefono. Sales vuole ordinare per “ultima attività”. Finance vuole il filtro “saldo scaduto”. Ogni richiesta aggiunge condizioni, join e calcoli extra.

La maggior parte delle liste admin rallenta per lo stesso motivo: ogni click cambia la query. Filtri e ordinamenti possono costringere il database a scansionare molte righe, soprattutto quando la query deve calcolare un valore per ogni riga prima di capire cosa corrisponde.

Un punto di svolta comune è quando WHERE e ORDER BY si riempiono di espressioni. Invece di filtrare su una colonna semplice, filtri su lower(email), date_trunc('day', last_seen_at) o su un CASE che mappa più stati in un’unica “categoria”. Quelle espressioni non sono solo più lente. Rendono l'SQL più difficile da leggere, più difficile da indicizzare e più facile da sbagliare.

L’SQL disordinato per le admin deriva spesso da alcuni schemi ripetuti:

• Un input di “search” che controlla diversi campi con regole diverse
• Ordinamento su un valore derivato (nome completo, punteggio di priorità, “ultima attività significativa”)
• Regole di business copiate tra schermate (attivo vs inattivo, pagato vs scaduto)
• Piccoli ritocchi “helper” (trim, lower, coalesce) sparsi ovunque
• Lo stesso valore calcolato usato nella lista, nei filtri e nell’ordinamento

I team spesso cercano di nascondere questo livello nell’app: builder di query dinamiche, join condizionali o precalcoli nel codice. Può funzionare, ma divide la logica tra UI e database, rendendo il debug delle query lente più doloroso.

L’obiettivo è semplice: query veloci che restino leggibili. Quando un valore calcolato riappare più volte nelle schermate admin, le colonne generate di PostgreSQL possono centralizzare la regola lasciando comunque il database libero di ottimizzare.

Colonne generate in parole semplici

Una colonna generata è una colonna normale della tabella il cui valore viene calcolato da altre colonne. Non scrivi il valore tu. PostgreSQL lo riempie usando un’espressione che definisci.

In PostgreSQL le colonne generate sono memorizzate. PostgreSQL calcola il valore all’inserimento o all’aggiornamento di una riga, poi lo salva su disco come qualsiasi altra colonna. È generalmente quello che vuoi per le schermate admin: letture veloci e la possibilità di indicizzare il valore calcolato.

Questo è diverso dal fare lo stesso calcolo dentro ogni query. Se continui a scrivere WHERE lower(email) = lower($1) o a ordinare per last_name || ', ' || first_name, paghi il costo più volte e l’SQL diventa rumoroso. Una colonna generata sposta quel calcolo ripetuto nella definizione della tabella. Le query diventano più semplici e il risultato è coerente ovunque.

Quando i dati di origine cambiano, PostgreSQL aggiorna automaticamente il valore generato per quella riga. L’app non deve ricordarsi di mantenerlo sincronizzato.

Un modello mentale utile:

• Definisci la formula una sola volta.
• PostgreSQL la calcola sulle scritture.
• Le query la leggono come una colonna normale.
• Essendo memorizzata, puoi indicizzarla.

Se poi cambi la formula dovrai fare una modifica di schema. Pianificalo come una migrazione, perché le righe esistenti verranno aggiornate per riflettere la nuova espressione.

Quando conviene usare campi calcolati per filtri e ordinamenti

Le colonne generate brillano quando il valore è sempre derivato da altre colonne e su di esso filtri o ordini spesso. Sono meno utili per report occasionali.

Campi di ricerca utili e usati davvero

La ricerca admin raramente è “pura”. Gli utenti si aspettano che la casella gestisca testo disordinato, casse inconsistenti e spazi extra. Se memorizzi una "chiave di ricerca" generata già normalizzata, il tuo WHERE resta leggibile e si comporta allo stesso modo su tutte le schermate.

Buoni candidati includono un nome completo combinato, testo minuscolo e trimmato per ricerche case-insensitive, una versione pulita che collassa spazi, o un'etichetta di stato derivata da più campi.

Esempio: invece di ripetere lower(trim(first_name || ' ' || last_name)) in ogni query, genera full_name_key una sola volta e filtra su quello.

Chiavi di ordinamento che corrispondono a come ordinano gli umani

L’ordinamento è spesso dove i campi calcolati ripagano più in fretta, perché l’ordinamento può costringere PostgreSQL a valutare espressioni su molte righe.

Chiavi comuni di ordinamento includono un rank numerico (piano mappato a 1, 2, 3), un singolo timestamp di “ultima attività” (il max di due timestamp), o un codice riempito che ordina correttamente come testo.

Quando la chiave di ordinamento è una colonna semplice indicizzata, ORDER BY diventa molto più economico.

Flag derivati per filtri rapidi

Gli utenti admin amano checkbox come “Scaduto” o “Valore alto”. Funzionano bene come colonne generate quando la logica è stabile e basata solo sui dati della riga.

Per esempio, se la lista clienti ha bisogno di “Ha messaggi non letti” e “È in ritardo”, una booleana generata has_unread (da unread_count > 0) e is_overdue (da due_date < now() e paid_at is null) consente ai filtri UI di mappare condizioni semplici.

Scegliere tra colonne generate, indici ed altre opzioni

Le schermate admin richiedono tre cose: filtri veloci, ordinamenti veloci e SQL che si possa ancora leggere dopo mesi. La vera decisione è dove mettere il calcolo: nella tabella, in un indice, in una view o nel codice dell’app.

Colonne generate sono una buona scelta quando vuoi che il valore si comporti come una colonna reale: facile da referenziare, visibile nelle select e difficile da dimenticare quando si aggiungono nuovi filtri. Si sposano naturalmente con gli indici normali.

Gli indici su espressione possono essere più veloci da aggiungere perché non cambi la tabella. Se ti interessa soprattutto la velocità e non ti dispiace un SQL più brutto, un indice su espressione spesso basta. Il lato negativo è la leggibilità e dipendi dal planner che riconosca esattamente la tua espressione.

Le view aiutano quando vuoi una “forma” condivisa di dati, specialmente se la lista admin fa molti join. Ma view complesse possono nascondere lavoro costoso e aggiungere un secondo posto da debug.

I trigger possono mantenere sincronizzata una colonna normale, ma aggiungono parti mobili. Possono rallentare bulk update e sono facili da trascurare durante il troubleshooting.

A volte la migliore opzione è una colonna normale riempita dall’app. Se gli utenti possono modificarla, o se la formula cambia spesso per decisioni di business, tenerla esplicita è più chiaro.

Una regola pratica veloce:

• Vuoi query leggibili e una formula stabile basata solo sui dati della riga? Usa una colonna generata.
• Vuoi velocità per un filtro specifico e non ti dispiace un SQL rumoroso? Usa un indice su espressione.
• Hai bisogno di una forma report-like con join usata in molti posti? Considera una view.
• Hai logica cross-table o side effect? Preferisci prima la logica in app, poi i trigger come ultima risorsa.

Passo dopo passo: aggiungere una colonna generata e usarla in una query

Inizia con una singola query lenta della lista admin che senti nell’UI. Annota i filtri e l’ordinamento che la schermata usa di più. Migliora prima quella query.

Scegli un campo calcolato che rimuove lavoro ripetuto e nominalo chiaramente in snake_case così gli altri capiscono cosa contiene senza rileggere l’espressione.

1) Aggiungi la colonna generata (STORED)

ALTER TABLE customers
ADD COLUMN full_name_key text
GENERATED ALWAYS AS (
  lower(concat_ws(' ', last_name, first_name))
) STORED;

Valida su righe reali prima di aggiungere indici:

SELECT id, first_name, last_name, full_name_key
FROM customers
ORDER BY id DESC
LIMIT 5;

Se l’output è sbagliato, correggi l’espressione ora. STORED significa che PostgreSQL la manterrà aggiornata su ogni insert e update.

2) Aggiungi l’indice che corrisponde alla schermata admin

Se la tua schermata filtra per status e ordina per nome, indicizza quel pattern:

CREATE INDEX customers_status_full_name_key_idx
ON customers (status, full_name_key);

3) Aggiorna la query admin per usare la nuova colonna

Prima potevi avere un ORDER BY confuso. Dopo, è ovvio:

SELECT id, status, first_name, last_name
FROM customers
WHERE status = 'active'
ORDER BY full_name_key ASC
LIMIT 50 OFFSET 0;

Usa colonne generate per le parti su cui le persone filtrano e ordinano ogni giorno, non per schermate rare.

Schemi di indicizzazione che corrispondono a schermate admin reali

Le schermate admin ripetono alcuni comportamenti: filtrare su poche colonne, ordinare su una colonna e paginare. La soluzione migliore raramente è “indicizzare tutto”. È “indicizzare la forma esatta delle query più comuni”.

Una regola pratica: metti prima le colonne filtro più comuni e l’ultima la colonna di ordinamento più usata. Se sei multi-tenant, spesso workspace_id (o simile) viene prima: (workspace_id, status, created_at).

La ricerca testuale è un problema a parte. Molte caselle finiscono con ILIKE '%term%', difficile da velocizzare con indici btree base. Un pattern utile è cercare una colonna helper normalizzata invece del testo grezzo (minuscolo, trimmato, magari concatenato). Se la UI può usare ricerca per prefisso (term%), un indice btree su quella colonna normalizzata può aiutare. Se serve contains (%term%), considera di restringere la UI per tabelle grandi (per esempio, “email inizia con”), o limita la ricerca a un sottoinsieme più piccolo.

Controlla anche la selectività prima di aggiungere indici. Se il 95% delle righe condivide lo stesso valore (es. status = 'active'), indicizzare solo quella colonna non aiuta molto. Abbinala a una colonna più selettiva o usa un indice parziale per il caso di minoranza.

Esempio realistico: una lista clienti admin che resta veloce

Immagina una tipica pagina clienti admin: una casella di ricerca, qualche filtro (inattivo, intervallo di saldo) e una colonna ordinabile “Ultima visita”. Col tempo diventa SQL difficile da leggere: LOWER(), TRIM(), COALESCE(), operazioni su date e blocchi CASE ripetuti.

Un modo per mantenerla veloce e leggibile è spostare quelle espressioni ripetute in colonne generate.

Tabella e colonne generate

Supponiamo una tabella customers con name, email, last_seen e balance. Aggiungi tre campi calcolati:

• search_key: un testo normalizzato per ricerche semplici
• is_inactive: booleano su cui filtrare senza ripetere la logica temporale
• balance_bucket: etichetta per segmentazione rapida

ALTER TABLE customers
  ADD COLUMN search_key text
    GENERATED ALWAYS AS (
      lower(trim(coalesce(name, ''))) || ' ' || lower(trim(coalesce(email, '')))
    ) STORED,
  ADD COLUMN is_inactive boolean
    GENERATED ALWAYS AS (
      last_seen IS NULL OR last_seen < (now() - interval '90 days')
    ) STORED,
  ADD COLUMN balance_bucket text
    GENERATED ALWAYS AS (
      CASE
        WHEN balance < 0 THEN 'negative'
        WHEN balance < 100 THEN '0-99'
        WHEN balance < 500 THEN '100-499'
        ELSE '500+'
      END
    ) STORED;

Ora la query admin legge come la UI.

Filtro leggibile + ordinamento

“I clienti inattivi, prima la più recente attività” diventa:

SELECT id, name, email, last_seen, balance
FROM customers
WHERE is_inactive = true
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

E una ricerca base diventa:

SELECT id, name, email, last_seen, balance
FROM customers
WHERE search_key LIKE '%' || lower(trim($1)) || '%'
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

Il vero vantaggio è la coerenza. Gli stessi campi alimentano più schermate senza riscrivere la logica:

• La casella di ricerca client usa search_key
• La scheda “Clienti inattivi” usa is_inactive
• I filtri per saldo usano balance_bucket

Errori comuni e trappole

Le colonne generate possono sembrare una vittoria semplice: metti la matematica nella tabella e tieni le query pulite. Aiutano solo quando corrispondono a come la schermata filtra e ordina, e quando aggiungi l’indice giusto.

Gli errori più comuni:

• Pensare che velocizzino senza indicizzazione. Un valore calcolato ha comunque bisogno di un indice per filtrare o ordinare velocemente su larga scala.
• Mettere troppa logica in un campo. Se una colonna generata diventa un mini-programma, le persone smettono di fidarsi. Mantienila breve e nominala chiaramente.
• Usare funzioni non immutabili. PostgreSQL richiede che l’espressione per una colonna generata memorizzata sia immutabile. Funzioni come now() e random() infrangono le aspettative e spesso non sono ammesse.
• Ignorare il costo sulle scritture. Insert e update devono mantenere il valore calcolato. Le letture più veloci non valgono se import e integrazioni rallentano troppo.
• Creare quasi-duplicati. Standardizza uno o due pattern (es. una singola chiave normalizzata) invece di accumulare cinque colonne simili.

Se la tua lista admin usa ricerche contains (come ILIKE '%ann%'), una sola colonna generata non risolleva la situazione. Potresti aver bisogno di un approccio di ricerca diverso. Ma per le query workhorse di tutti i giorni “filtra e ordina”, colonne generate più l’indice giusto rendono spesso le prestazioni molto più prevedibili.

Checklist rapida prima del deploy

Prima di distribuire cambiamenti su una lista admin, verifica che il valore calcolato, la query e l’indice siano allineati.

• La formula è stabile e spiegabile in una frase.
• La tua query usa effettivamente la colonna generata in WHERE e/o ORDER BY.
• L’indice corrisponde all’uso reale, non a un test una tantum.
• Hai confrontato i risultati con la logica precedente sui casi limite (NULL, stringhe vuote, spazi strani, casse miste).
• Hai testato le prestazioni in scrittura se la tabella è molto attiva (import, aggiornamenti in background, integrazioni).

Prossimi passi: applicalo alle tue schermate admin

Scegli un punto di partenza piccolo ma ad alto impatto: le 2–3 schermate admin aperte tutto il giorno (ordini, clienti, ticket). Nota cosa sembra lento (un filtro per intervallo di date, ordinamento per “ultima attività”, ricerca per nome combinato, filtro per etichetta di stato). Poi standardizza un set breve di campi calcolati riutilizzabili tra le schermate.

Un piano di rollout semplice da misurare e revertibile:

• Aggiungi la/e colonna/e generata/e con nomi chiari.
• Esegui vecchio e nuovo fianco a fianco se sostituisci logica esistente.
• Aggiungi l’indice che corrisponde al filtro/ordinamento principale.
• Aggiorna la query della schermata per usare la nuova colonna.
• Misura prima e dopo (tempo di query e righe scansionate), poi rimuovi la soluzione temporanea.

Se costruisci strumenti admin interni con AppMaster (appmaster.io), questi campi calcolati si integrano bene in un modello dati condiviso: il database porta la regola e le UI possono puntare a un nome campo semplice invece di ripetere espressioni tra le schermate.