Partizionamento PostgreSQL per tabelle eventi nei log di audit

Perché le tabelle di eventi e audit diventano un problema

Le tabelle di eventi e le tabelle di audit sembrano simili, ma esistono per ragioni diverse.

Una tabella di eventi registra cose che accadono: visualizzazioni di pagina, email inviate, chiamate webhook, esecuzioni di job. Una tabella di audit registra chi ha cambiato cosa e quando: un cambio di stato, un aggiornamento delle autorizzazioni, l'approvazione di un pagamento, spesso con dettagli "prima" e "dopo".

Entrambe crescono rapidamente perché sono append-only. Raramente si eliminano righe singole e nuove righe arrivano ogni minuto. Anche un prodotto piccolo può generare milioni di righe di log in poche settimane una volta inclusi job in background e integrazioni.

Il dolore appare nel lavoro quotidiano. I pannelli amministrativi hanno bisogno di filtri rapidi come “errori di ieri” o “azioni di questo utente”. Man mano che la tabella cresce, quelle schermate di base iniziano a rallentare.

Di solito noterai prima alcuni sintomi:

• I filtri impiegano secondi (o scadono) anche con un intervallo di date stretto.
• Gli indici diventano talmente grandi che gli insert rallentano e i costi di storage aumentano.
• VACUUM e autovacuum impiegano più tempo e compaiono attività di manutenzione più visibili.
• La retention diventa rischiosa: cancellare righe vecchie è lento e crea bloat.

La partizione è un modo per affrontare questo. In termini semplici, divide una tabella grande in molte tabelle più piccole (partizioni) che condividono un nome logico. PostgreSQL instrada ogni nuova riga nella partizione corretta in base a una regola, di solito il tempo.

Per questo i team valutano il partizionamento PostgreSQL per tabelle di eventi: può mantenere i dati recenti in blocchi più piccoli, così PostgreSQL può saltare intere partizioni quando una query richiede solo una finestra temporale.

La partizione non è una bacchetta magica. Può aiutare molto per query come “ultimi 7 giorni” e semplifica la retention (droppare partizioni vecchie è veloce). Ma può anche creare nuovi problemi:

• Le query che non usano la chiave di partizione potrebbero dover controllare molte partizioni.
• Più partizioni significano più oggetti da gestire e più modi per configurare male le cose.
• Alcuni vincoli unici e indici diventano più difficili da far rispettare su tutti i dati.

Se il tuo pannello admin si basa molto su filtri temporali e regole di retention prevedibili, la partizione può essere un vero vantaggio. Se la maggior parte delle query è “trova tutte le azioni dell'utente X nella storia”, può creare problemi a meno che non progetti l'UI e gli indici con cura.

Pattern di accesso tipici per log e audit

Le tabelle di eventi e audit crescono in una direzione: verso l'alto. Ricevono un flusso costante di insert e quasi nessun update. La maggior parte delle righe viene scritta una volta sola, poi letta in seguito durante il supporto, le revisioni di incidenti o i controlli di conformità.

Questa forma “append-only” conta. Le prestazioni di scrittura sono una preoccupazione costante perché gli insert avvengono tutto il giorno, mentre le letture contano a ondate (quando support o ops hanno bisogno di risposte rapide).

La maggior parte delle letture sono filtri, non lookup casuali. In un pannello amministrativo, qualcuno inizia di solito in modo ampio (ultime 24 ore) e poi restringe a un utente, un'entità o un'azione.

Filtri comuni includono:

• Un intervallo temporale
• Un attore (ID utente, service account, indirizzo IP)
• Un target (tipo entità + ID entità, come Ordine #1234)
• Un tipo di azione (creato, aggiornato, eliminato, login fallito)
• Uno stato o gravità (successo/errore)

L'intervallo temporale è il “primo taglio” naturale perché è quasi sempre presente. Questa è l'intuizione chiave dietro il partizionamento PostgreSQL per tabelle di eventi: molte query vogliono una fetta di tempo, e tutto il resto è un secondo filtro dentro quella fetta.

La retention è l'altra costante. I log raramente vivono per sempre. I team spesso conservano eventi ad alto dettaglio per 30 o 90 giorni, poi cancellano o archiviano. Gli audit possono avere requisiti più lunghi (365 giorni o più), ma anche allora di solito vuoi un modo prevedibile per rimuovere dati vecchi senza bloccare il database.

L'audit logging porta anche aspettative aggiuntive. Di solito vuoi che la storia sia immutabile, che ogni record sia tracciabile (chi/cosa/quando più contesto di richiesta o sessione) e che l'accesso sia controllato (non tutti devono vedere eventi sensibili).

Questi pattern emergono direttamente nel design dell'UI. I filtri che la gente si aspetta di default — selettori di data, selettori utente, ricerca entità, menu azioni — sono gli stessi filtri che la tua tabella e i tuoi indici devono supportare se vuoi che l'esperienza admin resti veloce con l'aumentare del volume.

Come capire se la partizione conviene

La partizione non è una best practice predefinita per gli audit log. Vale la pena quando una tabella diventa così grande che le query di tutti i giorni e la manutenzione di routine iniziano a ostacolarsi a vicenda.

Un indizio semplice di dimensione: una volta che una tabella di eventi raggiunge decine di milioni di righe, vale la pena misurare. Quando la tabella e i suoi indici crescono fino a decine di gigabyte, anche le ricerche per intervallo di date “semplici” possono diventare lente o imprevedibili perché si leggono più pagine di dati da disco e gli indici diventano costosi da mantenere.

Il segnale più chiaro dalle query è quando chiedi regolarmente una piccola finestra temporale (ultimo giorno, ultima settimana), ma PostgreSQL tocca ancora una grande parte della tabella. Lo vedrai come schermate lente di “attività recente” o audit filtrati per data più utente, tipo azione o ID entità. Se i piani delle query mostrano ampia scansione o le letture di buffer sono costantemente alte, stai pagando per dati che non volevi leggere.

I segnali di manutenzione contano altrettanto:

• VACUUM e autovacuum impiegano molto più tempo.
• Autovacuum resta indietro e i dead tuple (bloat) si accumulano.
• Gli indici crescono più velocemente del previsto, specialmente gli indici multi-colonna.
• La contesa sulle lock diventa più evidente quando la manutenzione si sovrappone al traffico.

I costi operativi sono la goccia lenta che spinge i team verso la partizione. Backup e restore diventano più lenti man mano che una tabella cresce, lo storage aumenta, e i job di retention diventano costosi perché DELETE grandi creano bloat e lavoro extra di vacuum.

Se i tuoi obiettivi principali sono una policy di retention pulita e query più veloci per i periodi recenti, la partizione vale quasi sempre un'attenta considerazione. Se la tabella è moderata e le query sono già veloci con una buona indicizzazione, la partizione può aggiungere complessità senza un ritorno chiaro.

Opzioni di partizionamento adatte a eventi e audit

Per la maggior parte dei dati di audit ed eventi, la scelta più semplice è il range partitioning per tempo. I log arrivano in ordine temporale, le query spesso si concentrano su “ultime 24 ore” o “ultimi 30 giorni”, e la retention è di solito basata sul tempo. Con partizioni temporali, eliminare dati vecchi può essere semplice come rimuovere una partizione vecchia invece di eseguire un grande DELETE che crea bloat.

La partizione per intervalli temporali mantiene anche gli indici più piccoli e mirati. Ogni partizione ha i propri indici, quindi una query per l'ultima settimana non deve scorrere un unico indice enorme che copre anni di storia.

Esistono altri stili di partizionamento, ma si adattano a meno casi di log e audit:

• List (tenant o cliente) può funzionare quando hai pochi tenant molto grandi e le query rimangono spesso all'interno di un tenant. Diventa problematico con centinaia o migliaia di tenant.
• Hash (distribuzione uniforme delle scritture) può aiutare quando non hai query basate sulla finestra temporale e vuoi distribuire gli scritti. Per gli audit è meno comune perché rende più difficile la retention e l'esplorazione temporale.
• Subpartizionamento (tempo più tenant) può essere potente, ma la complessità cresce rapidamente. È principalmente per sistemi ad altissimo volume con esigenze strette di isolamento dei tenant.

Se scegli range temporali, scegli una dimensione di partizione che corrisponda a come navighi e conservi i dati. Partizioni giornaliere hanno senso per tabelle ad altissimo volume o retention molto stringente. Partizioni mensili sono più facili da gestire a volume moderato.

Un esempio pratico: se il team admin controlla i tentativi di login falliti ogni mattina e filtra per gli ultimi 7 giorni, partizioni giornaliere o settimanali significano che la query tocca solo le partizioni più recenti. PostgreSQL può ignorare il resto.

Qualunque approccio tu scelga, pianifica le parti noiose: creare partizioni future, gestire eventi che arrivano in ritardo e definire cosa succede a ogni confine (fine giorno, fine mese). La partizione ripaga quando quelle routine restano semplici.

Come scegliere la giusta chiave di partizione

Una buona chiave di partizione rispecchia come leggi la tabella, non come i dati appaiono in un diagramma.

Per log e audit, parti dal tuo pannello amministrativo: quale filtro usano le persone per primo, quasi sempre? Per la maggior parte dei team è un intervallo temporale (ultime 24 ore, ultimi 7 giorni, date personalizzate). Se è così per te, la partizione basata sul tempo di solito dà il guadagno più grande e più prevedibile perché PostgreSQL può saltare intere partizioni fuori dalla finestra selezionata.

Considera la chiave come una promessa a lungo termine. Stai ottimizzando per le query che continuerai a eseguire per anni.

Parti dal “primo filtro” che le persone usano

La maggior parte delle schermate admin segue un pattern: intervallo temporale più opzionale utente, azione, stato o risorsa. Partiziona per la cosa che restringe i risultati presto e in modo consistente.

Un rapido controllo di realtà:

• Se la vista di default è “eventi recenti”, partiziona per timestamp.
• Se la vista di default è “eventi per un tenant/account”, tenant_id può avere senso, ma solo se i tenant sono abbastanza grandi da giustificarlo.
• Se il primo passo è sempre “seleziona un utente”, user_id può sembrare allettante, ma tipicamente crea troppe partizioni da gestire.

Evita chiavi ad alta cardinalità

La partizione funziona meglio quando ogni partizione è un blocco significativo di dati. Chiavi come user_id, session_id, request_id o device_id possono portare a migliaia o milioni di partizioni. Questo aumenta l'overhead dei metadata, complica la manutenzione e spesso rallenta il planning.

Le partizioni basate sul tempo mantengono il numero di partizioni prevedibile. Scegli giornaliera, settimanale o mensile in base al volume. Troppe poche partizioni (una all'anno) non aiutano molto. Troppe (una all'ora) aggiungono overhead rapidamente.

Scegli il timestamp giusto: created_at vs occurred_at

Sii esplicito su cosa significa tempo:

• occurred_at: quando l'evento è avvenuto nel prodotto.
• created_at: quando il database lo ha registrato.

Per gli audit, “occurred” è spesso ciò che interessa agli admin. Ma la consegna ritardata (client mobile offline, ritentativi, code) significa che occurred_at può arrivare in ritardo. Se gli arrivi tardivi sono comuni, partizionare per created_at e indicizzare occurred_at per il filtraggio può essere più stabile operativamente. L'altra opzione è definire una politica di backfill chiara e accettare che occasionalmente partizioni vecchie riceveranno eventi tardivi.

Decidi anche come memorizzi il tempo. Usa un tipo coerente (spesso timestamptz) e tratta UTC come fonte di verità. Format per il fuso orario del visualizzatore nell'UI. Questo mantiene i confini delle partizioni stabili e evita sorprese con l'ora legale.

Passo dopo passo: pianificare e introdurre la partizione

La partizione è più semplice se la tratti come un piccolo progetto di migrazione, non una modifica rapida. L'obiettivo è scritture semplici, letture prevedibili e una retention che diventa un'operazione di routine.

Un piano di rollout pratico

1. Scegli una dimensione di partizione che corrisponda al tuo volume. Le partizioni mensili vanno bene a qualche centinaio di migliaia di righe al mese. Se inserisci decine di milioni al mese, partizioni settimanali o giornaliere mantengono gli indici più piccoli e il lavoro di vacuum più contenuto.

2. Progetta chiavi e vincoli per le tabelle partizionate. In PostgreSQL, un vincolo unico deve includere la chiave di partizione (o essere applicato in altro modo). Un pattern comune è (created_at, id), dove id è generato e created_at è la chiave di partizione. Questo evita sorprese quando scopri che un vincolo che ti aspettavi non è permesso.

3. Crea le partizioni future prima di averne bisogno. Non aspettare che gli insert falliscano perché manca la partizione corrispondente. Decidi quanto in anticipo crearle (per esempio 2-3 mesi) e rendilo un job di routine.

4. Mantieni gli indici per partizione piccoli e intenzionali. La partizione non rende gli indici gratuiti. La maggior parte delle tabelle eventi ha bisogno della chiave di partizione più uno o due indici che riflettono i filtri reali dell'admin, come actor_id, entity_id o event_type. Evita indici “per ogni evenienza”. Puoi aggiungerli più tardi alle nuove partizioni e backfillare quelle vecchie se necessario.

5. Pianifica la retention intorno al droppare partizioni, non ai DELETE. Se conservi 180 giorni di log, droppare una partizione vecchia è veloce ed evita DELETE massivi che causano bloat. Scrivi la regola di retention, chi la esegue e come verificarla.

Piccolo esempio

Se la tua tabella di audit riceve 5 milioni di righe a settimana, partizioni settimanali su created_at sono un buon punto di partenza. Crea partizioni con 8 settimane di anticipo e tieni due indici per partizione: uno per ricerche comuni per actor_id e uno per entity_id. Quando la finestra di retention scade, droppa la partizione settimanale più vecchia invece di cancellare milioni di righe.

Se stai costruendo strumenti interni in AppMaster, è utile decidere la chiave di partizione e i vincoli presto in modo che il modello dati e il codice generato seguano le stesse assunzioni fin dall'inizio.

Cosa cambia per i filtri del pannello admin

Una volta che parti una tabella di log, i filtri del pannello admin smettono di essere “solo UI”. Diventano il fattore principale che decide se una query tocca poche partizioni o ne scansiona mesi.

Il cambiamento pratico più grande: il tempo non può più essere opzionale. Se gli utenti possono eseguire una ricerca senza limiti di data (nessun intervallo, solo “mostrami tutto per l'utente X”), PostgreSQL potrebbe dover controllare ogni partizione. Anche se ogni controllo è veloce, aprire molte partizioni aggiunge overhead e la pagina sembra lenta.

Una regola che funziona: richiedi un intervallo temporale per ricerche di log e audit e impostalo di default in modo sensato (per esempio ultime 24 ore). Se qualcuno ha davvero bisogno di “tutto il tempo”, falla essere una scelta deliberata e informa che i risultati potrebbero essere più lenti.

Fai corrispondere i filtri al partition pruning

Il partition pruning aiuta solo quando la clausola WHERE include la chiave di partizione in una forma che PostgreSQL può usare. Filtri come created_at BETWEEN X AND Y escludono le partizioni in modo pulito. Pattern che spesso interrompono il pruning includono il casting dei timestamp a date, l'avvolgimento della colonna in funzioni o il filtraggio principalmente su una colonna temporale diversa dalla chiave di partizione.

All'interno di ogni partizione, gli indici dovrebbero corrispondere a come le persone filtrano realmente. Nella pratica, le combinazioni che contano spesso sono tempo più una condizione: tenant/workspace, utente, azione/tipo, ID entità o stato.

Ordinamento e paginazione: mantienile poco profonde

La partizione non risolve da sola la paginazione lenta. Se il pannello ordina per più recenti e gli utenti saltano alla pagina 5000, la paginazione con OFFSET costringe comunque PostgreSQL a scorrere molte righe.

La paginazione con cursore si comporta meglio per i log: “carica eventi prima di questo timestamp/id.” Mantiene il database che usa indici invece di saltare grandi offset.

Preset aiutano anche qui. Poche opzioni sono generalmente sufficienti: ultime 24 ore, ultimi 7 giorni, oggi, ieri, intervallo personalizzato. I preset riducono ricerche “scannerizza tutto” accidentali e rendono l'esperienza admin più prevedibile.

Errori comuni e trappole

La maggior parte dei progetti di partizionamento fallisce per ragioni semplici: la partizione funziona, ma le query e l'UI admin non le corrispondono. Se vuoi che la partizione ripaghi, progetta intorno ai filtri reali e alla retention reale.

1) Partizionare sulla colonna temporale sbagliata

Il partition pruning avviene solo quando la clausola WHERE corrisponde alla chiave di partizione. Un errore comune è partizionare per created_at mentre l'UI filtra per event_time (o viceversa). Se il tuo team di support chiede sempre “cosa è successo tra 10:00 e 10:15”, ma la tabella è partizionata per tempo di ingestione, potresti comunque toccare più dati del previsto.

2) Creare troppe partizioni piccole

Partizioni orarie (o più piccole) sembrano ordinate, ma aggiungono overhead: più oggetti da gestire, più lavoro per il planner e più possibilità di indici mancanti o permessi non allineati.

A meno che tu non abbia un volume di scrittura estremamente alto e retention stringenti, partizioni giornaliere o mensili sono più facili da operare.

3) Assumere che l'unicità globale funzioni ancora

Le tabelle partizionate impongono vincoli: alcuni indici unici devono includere la chiave di partizione, altrimenti PostgreSQL non può applicarli su tutte le partizioni.

Questo sorprende spesso i team che si aspettano che event_id sia unico per sempre. Se hai bisogno di un identificatore unico, usa un UUID e rendilo unico insieme alla chiave temporale, oppure applica l'unicità lato applicazione.

4) Lasciare l'UI eseguire ricerche aperte

I pannelli admin spesso escono con una casella di ricerca amichevole che gira senza filtri. Su una tabella di log partizionata, questo può significare scandagliare ogni partizione.

La ricerca full-text sui payload dei messaggi è particolarmente rischiosa. Metti salvaguardie: richiedi un intervallo temporale, limita il range di default e rendi “tutto il tempo” una scelta deliberata.

5) Nessuna politica di retention (e nessun piano per le partizioni)

La partizione non risolve automaticamente la retention. Senza una policy, ti ritroverai con un mucchio di partizioni vecchie, storage caotico e manutenzione rallentata.

Un semplice set di regole operative previene questo: definisci quanto a lungo restano gli eventi raw, automatizza la creazione di partizioni future e la rimozione di quelle vecchie, applica gli indici in modo coerente, monitora il numero di partizioni e le date di confine e testa i filtri admin più lenti contro volumi realistici.

Checklist rapida prima di impegnarti

La partizione può essere un grande vantaggio per gli audit log, ma aggiunge lavoro di routine. Prima di cambiare lo schema, verifica come le persone usano davvero la tabella.

Se il tuo problema principale è che le pagine admin scadono quando qualcuno apre “Ultime 24 ore” o “Questa settimana”, sei vicino a un buon caso d'uso. Se la maggior parte delle query è “user ID su tutta la storia”, la partizione può aiutare meno a meno che non cambi anche come l'UI guida le ricerche.

Una checklist breve che mantiene i team onesti:

• Intervallo temporale come filtro di default. La maggior parte delle query admin include una finestra chiara (from/to). Se le ricerche senza limiti sono comuni, il partition pruning aiuta meno.
• Retention gestita droppando partizioni, non cancellando righe. Sei a tuo agio nel droppare partizioni vecchie e hai una regola chiara su quanto i dati devono essere conservati.
• Numero di partizioni ragionevole. Stima il numero di partizioni per anno (giornaliero, settimanale, mensile). Troppe piccole aumentano l'overhead. Troppe grandi riducono il beneficio.
• Gli indici corrispondono ai filtri reali. Oltre alla chiave di partizione, servono ancora i giusti indici per partizione per i filtri comuni e l'ordine.
• Partizioni create automaticamente e monitorate. Un job crea partizioni future e sai quando fallisce.

Un test pratico: guarda i tre filtri che il tuo team di support o ops usa di più. Se due su tre sono soddisfatti da “intervallo temporale + una condizione”, il partizionamento PostgreSQL per tabelle di eventi merita seria considerazione.

Un esempio realistico e passi pratici successivi

Un team di support tiene due schermate aperte tutto il giorno: “Eventi di login” (accessi riusciti e falliti) e “Audit di sicurezza” (reset password, cambi ruoli, aggiornamenti API key). Quando un cliente segnala attività sospette, il team filtra per utente, controlla le ultime ore ed esporta un breve report.

Prima della partizione, tutto è in un'unica tabella events. Cresce rapidamente e anche ricerche semplici iniziano a rallentare perché il database attraversa molte righe vecchie. La retention è dolorosa: un job notturno cancella righe vecchie, ma DELETE massivi impiegano tempo, creano bloat e competono con il traffico normale.

Dopo la partizione per mese (usando il timestamp dell'evento), il flusso migliora. Il pannello admin richiede un filtro temporale, quindi la maggior parte delle query tocca solo una o due partizioni. Le pagine si caricano più velocemente perché PostgreSQL può ignorare le partizioni fuori dalla finestra selezionata. La retention diventa di routine: invece di cancellare milioni di righe, droppi le partizioni vecchie.

Una cosa rimane difficile: la ricerca full-text su “tutto il tempo”. Se qualcuno cerca un indirizzo IP o una frase vaga senza limite di data, la partizione non può renderla economica. La soluzione è spesso nel comportamento del prodotto: impostare le ricerche di default su una finestra temporale e rendere “ultime 24 ore / 7 giorni / 30 giorni” il percorso ovvio.

Passi pratici che funzionano bene:

• Mappa prima i filtri del pannello admin. Annota quali campi le persone usano e quali devono essere obbligatori.
• Scegli partizioni che corrispondono al modo in cui navighi. Le partizioni mensili sono spesso un buon inizio; passa a settimanali solo quando il volume lo richiede.
• Rendi il range temporale un filtro di prima classe. Se l'UI permette “nessuna data”, aspettati pagine lente.
• Allinea gli indici ai filtri reali. Quando il tempo è sempre presente, una strategia di indici che mette il tempo per primo è spesso una base giusta.
• Imposta regole di retention che corrispondono ai confini delle partizioni (per esempio conserva 13 mesi e rimuovi tutto ciò che è più vecchio).

Se stai costruendo un pannello admin interno con AppMaster, vale la pena modellare queste assunzioni presto: tratta i filtri limitati nel tempo come parte del modello dati, non solo come scelta dell'UI. Questa piccola decisione protegge le prestazioni delle query man mano che il volume dei log cresce.