Quando si implementa un pattern di soft-delete utilizzando un flag booleano, perché l'aggiunta di questo flag a un indice composito a volte degrada le prestazioni delle query active-record rispetto a una strategia di indice parziale, e come differisce la stima di selettività del pianificatore delle query tra questi approcci?

Risposta alla domanda

Storia della domanda

I pattern di soft-delete sono emersi come alternativa alla cancellazione dura per le tracce di audit e il recupero dei dati. Le prime implementazioni utilizzavano semplici flag booleani o colonne timestamp, ma gli sviluppatori si sono presto imbattuti in un degrado delle prestazioni quando questi flag sono stati incorporati negli indici B-tree standard. Il problema è diventato prominente con l'adozione diffusa degli indici parziali di PostgreSQL e degli indici filtrati di SQL Server a metà degli anni 2000, che consentivano l'indicizzazione solo dei record attivi. Comprendere la stima di selettività — come il pianificatore delle query prevede la percentuale di righe che corrispondono a una condizione — è diventato cruciale nel confrontare indici compositi completi e strategie di indicizzazione parziale.

Il problema

Quando un flag di soft-delete (ad es. is_deleted) viene aggiunto a un indice composito come (is_deleted, user_id, created_at), l'ottimizzatore del database potrebbe calcolare male la selettività delle righe per le query che filtrano WHERE is_deleted = false. Se il 90% delle righe è attivo, l'ottimizzatore potrebbe scegliere una scansione sequenziale anziché una scansione dell'indice, o viceversa, se la distribuzione è distorta, potrebbe favorire inappropriatamente l'indice. Gli indici parziali (WHERE is_deleted = false) memorizzano solo righe attive, garantendo alta selettività, ma gli indici compositi standard memorizzano tutte le righe, portando a un'espansione dell'indice e a stime di cardinalità ambigue quando le statistiche non riflettono accuratamente la distribuzione del soft-delete.

La soluzione

Implementare indici parziali (in PostgreSQL) o indici filtrati (in SQL Server) che escludano completamente le righe soft-delete, abbinati a indici separati per i dati eliminati se necessario. Per MySQL o database privi di indicizzazione parziale, utilizzare un indice composito con il flag di soft-delete come colonna principale solo se il dataset attivo è piccolo; in caso contrario, partizionare la tabella per stato di cancellazione. Analizzare esplicitamente le statistiche della tabella dopo cancellazioni di massa per prevenire istogrammi obsoleti. Quando si interrogano record attivi, utilizzare il predicato esatto dalla definizione dell'indice parziale (WHERE is_deleted = false) per garantire che l'ottimizzatore riconosca l'applicabilità dell'indice.

Esempio di codice

-- PostgreSQL: Indice parziale per soli record attivi
CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE is_deleted = false;

-- SQL Server: Indice filtrato equivalente
CREATE INDEX IX_Active_Users_Email 
ON Users(Email) 
WHERE IsDeleted = 0;

-- Query che sfrutta l'indice parziale
SELECT * FROM users 
WHERE email = 'alice@example.com' 
AND is_deleted = false;

Situazione dalla vita reale

Una piattaforma SaaS che gestisce 10 milioni di record utenti ha subito un grave rallentamento nel proprio dashboard di amministrazione quando filtrava gli utenti attivi per data di creazione. Inizialmente, utilizzavano un indice composito (is_deleted, created_at) su PostgreSQL, assumendo che avrebbe accelerato le query WHERE is_deleted = false ORDER BY created_at. Tuttavia, man mano che il dataset cresceva fino all'80% di account storici soft-delete, le query hanno iniziato a richiedere 8-12 secondi perché il pianificatore sottovalutava il costo della scansione dell'indice gonfiato.

Soluzione A: Mantenere l'indice composito e forzare l'uso dell'indice con suggerimenti. Questo approccio utilizzava SET enable_seqscan = off o suggerimenti del piano di query per forzare l'utilizzo dell'indice. Anche se ha migliorato temporaneamente alcune query specifiche, ha creato un debito di manutenzione e spesso ha costretto piani subottimali per altri schemi di accesso quando la distribuzione dei dati è cambiata. La soluzione non ha nemmeno affrontato l'espansione dell'indice sottostante che causava un aumento dello spazio di archiviazione e un sovraccarico di VACUUM.

Soluzione B: Creare indici parziali separati per record attivi ed eliminati. Implementando CREATE INDEX idx_active_created ON users(created_at) WHERE is_deleted = false si è ridotto la dimensione dell'indice dell'80% e si è consentito al pianificatore di stimare accuratamente 2 milioni di righe attive contro 8 milioni eliminate. Le query sono scese a 40 ms, ma hanno richiesto la rifattorizzazione di tutte le query dell'applicazione per garantire che il predicato is_deleted = false rimanesse esplicito e non fosse avvolto in funzioni o astratto dietro viste che offuscavano la condizione.

Il team ha scelto la Soluzione B perché ha fornito prestazioni sostenibili senza necessità di mantenere suggerimenti per le query. Il risultato è stato una riduzione del 95% della latenza delle query e l'eliminazione dei problemi periodici di espansione di VACUUM causati dal precedente indice composito sovradimensionato. Il monitoraggio ha confermato tempi di risposta costantemente inferiori a un secondo per i casi d'uso principali del dashboard.

Cosa spesso mancano i candidati

Come influisce la presenza di valori NULL in una colonna timestamp di soft-delete (utilizzando NULL per attivo, timestamp per eliminato) sull'uso degli indici parziali rispetto agli approcci con flag booleano?

Quando si utilizza un timestamp deleted_at nullable, gli indici parziali come WHERE deleted_at IS NULL affrontano sfide con la gestione degli indici NULL da parte di PostgreSQL. A differenza dei flag booleani in cui = false è esplicito e utilizzabile, le condizioni IS NULL richiedono che il pianificatore riconosca l'applicabilità dell'indice, il che può fallire se la query utilizza istruzioni parametriche in cui il pianificatore non può dimostrare che il parametro sarà NULL. Inoltre, aggiornamenti che impostano deleted_at = CURRENT_TIMESTAMP causano espansione dell'indice nell'indice parziale per i record attivi mentre le righe vengono rimosse da esso, mentre gli aggiornamenti con il flag booleano capovolgono il bit ma rimangono all'interno di un indice composito completo. L'approccio nullable richiede chiamate ANALYZE più frequenti e un'attenta considerazione dei fattori di riempimento degli indici per gestire l'elevato turnover delle modifiche allo stato di cancellazione.

Perché un indice coprente che include colonne di soft-delete potrebbe causare scritture più lente del previsto anche con bassa frequenza di eliminazione?

Gli indici coprenti (utilizzando la clausola INCLUDE in PostgreSQL 11+ o SQL Server) che appendono is_deleted per evitare le ricerche nella tabella in realtà degradano le prestazioni delle scritture perché ogni operazione di soft-delete (una UPDATE) deve modificare più strutture di indice. Quando un utente viene soft-eliminato, il database deve contrassegnare la vecchia voce dell'indice come morta nell'indice parziale attivo, inserire una nuova voce in eventuali indici di record eliminati e aggiornare i puntatori del heap dell'indice coprente. I candidati spesso non notano che gli indici parziali isolano questo turnover: solo gli indici parziali specifici per stati attivi o eliminati vengono modificati, mentre gli indici coprenti sulla tabella principale richiedono l'aggiornamento della struttura principale dell'indice indipendentemente dallo stato di soft-delete, creando un'amplificazione delle scritture che influisce sul throughput delle transazioni.

Quando l'ottimizzatore delle query ignora un indice parziale per dati soft-delete anche quando la query filtra esplicitamente per record eliminati?

Se l'indice parziale è definito come WHERE is_deleted = true per query di audit, ma l'applicazione utilizza un'istruzione preparata con un parametro $1 per query attive ed eliminate, PostgreSQL potrebbe memorizzare nella cache un piano generico che non riconosce l'indice parziale per il caso specifico true. Ciò si verifica perché le istruzioni preparate generano piani prima che i valori dei parametri siano legati, e l'ottimizzatore non può dimostrare che $1 = true corrisponderà sempre al predicato dell'indice. I candidati mancano della consapevolezza che SQL dinamico o suggerimenti di ricompilazione (OPTION (RECOMPILE) in SQL Server, eseguendo con valori letterali in PostgreSQL) sono necessari per garantire che il pianificatore veda il valore concreto e lo abbini al predicato dell'indice parziale, piuttosto che fare affidamento su piani generici che per impostazione predefinita utilizzano scansioni sequenziali a causa dell'incertezza del valore del parametro.