Modifiche agli indici senza downtime in PostgreSQL: una playbook sicura

Perché le modifiche agli indici causano downtime (e come evitarlo)

Il lavoro sugli indici sembra innocuo. Stai "solo" aggiungendo una struttura di supporto. In PostgreSQL, però, creare, eliminare o sostituire un indice può richiedere lock che bloccano altre sessioni. Se la tabella è occupata, quegli attesi si accumulano e l'app inizia a sembrar guasta.

Il downtime raramente appare come un banner di disservizio pulito. Spesso si manifesta come pagine che si bloccano, job in background che restano indietro e una coda crescente di richieste in attesa sul database. Qualcuno clicca "Cerca" e ottiene un timeout, mentre gli strumenti di supporto e le schermate di amministrazione improvvisamente sembrano lente perché query semplici non riescono a ottenere il lock necessario.

"Eseguilo di notte" fallisce per due ragioni comuni. Molti sistemi non sono mai veramente silenziosi (utenti globali, job batch, ETL, backup). E le operazioni sugli indici possono durare più del previsto perché leggono molti dati e competono per CPU e disco. Se la finestra si chiude a metà build, sei costretto a scegliere tra aspettare di più o interrompere il lavoro.

Le modifiche agli indici a zero downtime non sono magia. Dipendono dal scegliere l'operazione meno bloccante, impostare limiti (timeout e controlli sul disco) e osservare il database mentre lavora.

Questa playbook si concentra su abitudini pratiche in produzione:

• Preferire build concorrenti degli indici quando letture e scritture devono rimanere attive.
• Monitorare i lock e il progresso della build per poter reagire presto.
• Avere una strada di rollback se la modifica causa regressioni o impiega troppo tempo.

Cosa non copre: teoria profonda sul design degli indici, ottimizzazione ampia delle query o refactor di schema che riscrivono molti dati.

Il modello semplice di lock dietro il lavoro sugli indici

PostgreSQL usa lock per mantenere i dati corretti quando molte sessioni toccano la stessa tabella. Un lock è semplicemente una regola che dice chi può leggere o scrivere un oggetto in quel momento e chi deve aspettare.

La maggior parte del tempo non noti i lock perché PostgreSQL può usare modalità leggere che permettono alle query normali di funzionare. Le DDL sono diverse. Quando crei o elimini un indice, PostgreSQL ha bisogno di abbastanza controllo sulla tabella per mantenere coerente il catalogo e i dati. Più controllo è necessario, più altre sessioni potrebbero essere costrette ad aspettare.

Costruire un indice vs usare un indice

Usare un indice di solito è poco costoso dal punto di vista dei lock. SELECT, UPDATE e DELETE possono leggere o mantenere indici mentre altre sessioni fanno lo stesso.

Costruire un indice è differente. PostgreSQL deve scansionare la tabella, ordinare o hashare le chiavi e scrivere una nuova struttura su disco. Quel lavoro richiede tempo, e il tempo è ciò che trasforma "piccoli lock" in "grandi problemi" in produzione.

Cosa cambia CONCURRENTLY (e cosa non cambia)

Un normale CREATE INDEX prende un lock pesante che blocca le scritture per tutta la durata. CREATE INDEX CONCURRENTLY è progettato per mantenere letture e scritture normali in movimento mentre l'indice viene costruito.

Ma "concurrent" non significa "senza lock". Avrai comunque brevi finestre di lock all'inizio e alla fine, e la build può fallire o attendere se qualcosa tiene lock incompatibili.

Gli esiti più importanti:

• Le build non concorrenti possono bloccare insert, update e delete sulla tabella.
• Le build concorrenti di solito permettono letture e scritture, ma possono rallentare o bloccarsi a causa di transazioni lunghe.
• I passaggi finali richiedono comunque lock brevi, quindi i sistemi molto occupati possono osservare attese brevi.

Scegliere l'approccio giusto: concorrente o normale

Hai due opzioni principali quando cambi gli indici: costruire l'indice normalmente (veloce, ma bloccante) oppure costruirlo con CONCURRENTLY (di solito non bloccante per il traffico applicativo, ma più lento e più sensibile a transazioni lunghe).

Quando usare CONCURRENTLY

Usa CREATE INDEX CONCURRENTLY quando la tabella serve traffico reale e non puoi mettere in pausa le scritture. È tipicamente la scelta più sicura quando:

• La tabella è abbastanza grande da far sì che una build normale possa richiedere minuti o ore.
• La tabella ha scritture costanti, non solo letture.
• Non puoi programmare una vera finestra di manutenzione.
• Hai bisogno di costruire prima, verificare, e poi eliminare un vecchio indice più tardi.

Quando una build normale è accettabile

Un normale CREATE INDEX può andare bene quando la tabella è piccola, il traffico è basso o hai una finestra controllata. Spesso termina più velocemente ed è più semplice da eseguire.

Considera l'approccio normale se la build è costantemente rapida in staging e puoi fermare temporaneamente le scritture (anche brevemente).

Se ti serve l'unicità, decidi presto. CREATE UNIQUE INDEX CONCURRENTLY funziona, ma fallirà se esistono valori duplicati. In molti sistemi di produzione, trovare e correggere i duplicati è il vero progetto.

Check preflight prima di toccare la produzione

La maggior parte dei problemi avviene prima ancora che il comando parta. Alcuni controlli ti aiutano a evitare due grandi sorprese: blocchi inattesi e una build che dura molto più a lungo (o usa molto più spazio) del previsto.

1. Assicurati di non essere dentro una transazione. CREATE INDEX CONCURRENTLY fallirà se lo esegui dopo un BEGIN, e alcuni strumenti GUI racchiudono silenziosamente le istruzioni in una transazione. Se non sei sicuro, apri una sessione nuova ed esegui lì solo il comando dell'indice.

2. Imposta aspettative per tempo e disco. Le build concorrenti di solito durano più delle normali e hanno bisogno di spazio di lavoro extra mentre girano. Pianifica per il nuovo indice più l'overhead temporaneo e conferma di avere spazio disco libero confortevole.

3. Imposta timeout che corrispondano al tuo obiettivo. Vuoi che la build fallisca rapidamente se non riesce a ottenere un lock, ma non vuoi che la sessione muoia a metà build per uno statement_timeout troppo aggressivo.

4. Cattura un baseline. Vuoi prova che la modifica abbia aiutato e un modo rapido per individuare regressioni. Registra uno snapshot prima: tempi delle query lente, un EXPLAIN (ANALYZE, BUFFERS) rappresentativo e una vista rapida di CPU, IO, connessioni e spazio disco libero.

Impostazioni di sessione sicure che molte squadre usano come punto di partenza (adatta alle tue regole):

-- Run in the same session that will build the index
SET lock_timeout = '2s';
SET statement_timeout = '0';

Passo dopo passo: creare un indice con CONCURRENTLY

Usa CREATE INDEX CONCURRENTLY quando hai bisogno che il traffico applicativo continui a scorrere e puoi permetterti una durata di build più lunga.

Prima, decidi esattamente cosa stai costruendo:

• Sii specifico sull'ordine delle colonne (conta).
• Considera se un indice parziale è sufficiente. Se la maggior parte delle query filtra su righe "active", un indice parziale può essere più piccolo, più veloce e meno costoso da mantenere.

Una esecuzione sicura assomiglia a questa: annota l'obiettivo e il nome dell'indice, esegui la build fuori da qualsiasi transaction block, osserva fino al completamento, poi verifica che il planner lo possa usare prima di rimuovere altro.

-- Example: speed up searches by email for active users
CREATE INDEX CONCURRENTLY idx_users_active_email
ON public.users (email)
WHERE status = 'active';

-- Validate it exists
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Check the plan can use it
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM public.users
WHERE status = 'active' AND email = '[email protected]';

Per note sul progresso (utile per audit), registra ora di inizio, ora di fine e eventuali attese osservate. Mentre gira, puoi interrogare pg_stat_progress_create_index da un'altra sessione.

La validazione non è solo "l'indice esiste." Conferma che il planner può sceglierlo, poi osserva i tempi reali delle query dopo il deployment. Se il nuovo indice non viene usato, non affrettarti a eliminare quello vecchio. Correggi la query o la definizione dell'indice prima.

Passo dopo passo: sostituire o rimuovere indici senza bloccare

Il pattern più sicuro è aggiungere prima, lasciare che il traffico benefici del nuovo indice, e solo dopo rimuovere il vecchio. Così mantieni un fallback funzionante.

Scambiare un indice vecchio con uno nuovo (ordine sicuro)

1. Crea il nuovo indice con CREATE INDEX CONCURRENTLY.

2. Verifica che venga usato. Controlla EXPLAIN sulle query lente che ti interessano e osserva l'uso dell'indice nel tempo.

3. Solo dopo, elimina il vecchio indice concorrente. Se il rischio è alto, mantieni entrambi gli indici per un intero ciclo di business prima di rimuovere qualcosa.

Eliminare indici: quando CONCURRENTLY funziona (e quando no)

Per un indice normale che hai creato tu, DROP INDEX CONCURRENTLY è di solito la scelta giusta. Due avvertenze: non può essere eseguito dentro una transaction block e richiede comunque brevi lock all'inizio e alla fine, quindi può essere ritardato da transazioni lunghe.

Se l'indice esiste a causa di una PRIMARY KEY o di un vincolo UNIQUE, solitamente non puoi eliminarlo direttamente. Devi modificare il vincolo con ALTER TABLE, che può richiedere lock più forti. Considera questo come un'operazione di manutenzione separata e pianificata.

Rinominare indici per chiarezza

Rinominare (ALTER INDEX ... RENAME TO ...) è di solito veloce, ma evita di farlo se tool o migration fanno riferimento ai nomi degli indici. Un'abitudine più sicura è scegliere un nome chiaro fin dall'inizio.

Se il vecchio indice è ancora necessario

A volte due pattern di query diversi necessitano di due indici differenti. Se query importanti si basano ancora su quello vecchio, mantienilo. Considera di aggiustare il nuovo indice (ordine delle colonne, condizione parziale) invece di forzare la rimozione.

Monitora lock e progresso mentre l'indice si costruisce

Anche con CREATE INDEX CONCURRENTLY dovresti guardare cosa succede in tempo reale. La maggior parte degli incidenti a sorpresa deriva da due cose: una sessione bloccante che non hai notato, o una transazione lunga che tiene la build in attesa.

Individuare sessioni bloccanti (chi blocca chi)

Inizia trovando le sessioni in attesa di lock:

SELECT
  a.pid,
  a.usename,
  a.application_name,
  a.state,
  a.wait_event_type,
  a.wait_event,
  now() - a.xact_start AS xact_age,
  left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
ORDER BY xact_age DESC;

Se ti serve il blocco esatto, segui blocked_pid verso blocking_pid:

SELECT
  blocked.pid  AS blocked_pid,
  blocking.pid AS blocking_pid,
  now() - blocked.xact_start AS blocked_xact_age,
  left(blocked.query, 80)  AS blocked_query,
  left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Osservare il progresso della build e i segnali di "blocco"

PostgreSQL espone il progresso delle build degli indici. Se vedi nessun movimento per molto tempo, cerca una transazione lunga (spesso una sessione idle che tiene uno snapshot vecchio).

SELECT
  pid,
  phase,
  lockers_total,
  lockers_done,
  blocks_total,
  blocks_done,
  tuples_total,
  tuples_done
FROM pg_stat_progress_create_index;

Tieni anche d'occhio la pressione sul sistema: IO disco, ritardo di replica e tempi di query in aumento. Le build concorrenti sono più amichevoli per l'uptime, ma leggono comunque molti dati.

Regole semplici che funzionano bene in produzione:

• Aspetta se il progresso avanza e l'impatto sugli utenti è basso.
• Cancella e riprogramma se la build è bloccata dietro una transazione lunga che non puoi terminare in sicurezza.
• Metti in pausa durante i picchi di traffico se l'IO sta danneggiando le query rivolte ai clienti.
• Termina solo come ultima risorsa, e solo dopo aver confermato cosa stia facendo la sessione.

Per la comunicazione nel team, mantieni aggiornamenti brevi: ora di inizio, fase corrente, cosa è bloccato (se qualcosa) e quando verrai a ricontrollare.

Piano di rollback: come tornare indietro in sicurezza

Le modifiche agli indici rimangono a basso rischio solo se pianifichi l'uscita prima di iniziare. Il rollback più sicuro spesso non è un annullamento drammatico. È semplicemente fermare il nuovo lavoro e mantenere l'indice vecchio al suo posto.

Modi comuni in cui il lavoro sugli indici fallisce

La maggior parte dei fallimenti in produzione è prevedibile: la build incontra un timeout, qualcuno la cancella durante un incidente, il server resta senza disco o la build compete con il traffico normale tanto da far salire la latenza utente.

Con CREATE INDEX CONCURRENTLY, annullare è di solito sicuro per l'app perché le query continuano a girare. Il compromesso è la pulizia: una build concorrente annullata o fallita può lasciare dietro un indice invalido.

Regole sicure per cancellare e pulire

Annullare una build concorrente non torna indietro come una transazione normale. PostgreSQL può lasciare un indice che esiste ma non è valido per il planner.

-- Cancel the session building the index (use the PID you identified)
SELECT pg_cancel_backend(\u003cpid\u003e);

-- If the index exists but is invalid, remove it without blocking writes
DROP INDEX CONCURRENTLY IF EXISTS your_index_name;

Prima di eliminare, conferma cosa stai guardando:

SELECT
  c.relname AS index_name,
  i.indisvalid,
  i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'your_index_name';

Se indisvalid = false, non viene usato ed è sicuro eliminarlo.

Checklist pratica di rollback quando sostituisci un indice esistente:

• Mantieni l'indice vecchio fino a quando il nuovo non è completamente costruito e valido.
• Se la nuova build fallisce o viene cancellata, elimina l'indice invalido con DROP INDEX CONCURRENTLY.
• Se hai già eliminato il vecchio indice, ricrealo con CREATE INDEX CONCURRENTLY per ripristinare lo stato precedente.
• Se la pressione sul disco ha causato il fallimento, libera spazio prima di ritentare.
• Se i timeout hanno causato il fallimento, programma una finestra più tranquilla invece di forzare l'operazione.

Esempio: inizi una nuova build per una ricerca amministrativa, gira per 20 minuti, poi partono gli alert sul disco. Annulli la build, elimini l'indice invalido concurrentemente e mantieni quello vecchio che serve il traffico. Puoi riprovare dopo aver liberato spazio, senza outage visibili agli utenti.

Errori comuni che creano outage a sorpresa

La maggior parte degli outage attorno agli indici non è causata dal fatto che PostgreSQL sia "lento." Succedono perché un piccolo dettaglio trasforma una modifica sicura in una bloccante.

1) Mettere una build concorrente dentro una transazione

CREATE INDEX CONCURRENTLY non può essere eseguito dentro una transaction block. Molti tool di migrazione racchiudono ogni modifica in una singola transazione per impostazione predefinita. Il risultato è o un errore netto (caso migliore) o un deploy complicato con retry.

Prima di eseguire la migration, conferma che il tuo strumento possa lanciare l'istruzione senza una transazione esterna, o dividi la migration in uno step speciale non-transazionale.

2) Avviarla durante il traffico di picco

Le build concorrenti riducono i blocchi, ma aggiungono comunque carico: letture extra, scritture extra e più pressione su autovacuum. Avviare la build durante una finestra di deploy con traffico in aumento è un modo comune per creare un rallentamento che sembra un outage.

Scegli un periodo tranquillo e trattalo come qualsiasi altra manutenzione di produzione.

3) Ignorare le transazioni lunghe

Una singola transazione lunga può trattenere la fase di cleanup di una build concorrente. L'indice può sembrare avanzare, poi fermarsi vicino alla fine in attesa che spariscano vecchi snapshot.

Abitua il team a controllare transazioni lunghe prima di iniziare e di nuovo se il progresso si blocca.

4) Cancellare la cosa sbagliata (o rompere un vincolo)

I team a volte eliminano un indice a memoria, o rimuovono un indice che supporta una regola di unicità. Se elimini l'oggetto sbagliato, puoi perdere un vincolo (unique) o peggiorare immediatamente le performance delle query.

Checklist rapida di sicurezza: verifica il nome dell'indice nel catalogo, conferma se supporta un vincolo, ricontrolla schema e tabella e tieni separati "crea nuovo" e "elimina vecchio". Tieni pronto il comando di rollback prima di iniziare.

Esempio realistico: velocizzare una ricerca amministrativa

Un punto dolente comune è una ricerca amministrativa che sembra istantanea in staging ma è lenta in produzione. Supponiamo di avere una grande tabella tickets (decine di milioni di righe) dietro un pannello admin interno, e gli agenti spesso cercano "ticket aperti per un cliente, più recenti prima."

La query è così:

SELECT id, customer_id, subject, created_at
FROM tickets
WHERE customer_id = $1
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

Un indice completo su (customer_id, status, created_at) aiuta, ma aggiunge overhead sulle scritture per ogni aggiornamento del ticket, inclusi quelli chiusi. Se la maggior parte delle righe non è open, un indice parziale è spesso una vittoria più semplice:

CREATE INDEX CONCURRENTLY tickets_open_by_customer_created_idx
ON tickets (customer_id, created_at DESC)
WHERE status = 'open';

Una timeline sicura in produzione:

• Preflight: conferma che la forma della query sia stabile e che la tabella abbia spazio disco libero sufficiente per una nuova build.
• Build: esegui CREATE INDEX CONCURRENTLY in una sessione separata con impostazioni di timeout chiare.
• Validate: esegui ANALYZE tickets; e conferma che il planner usi il nuovo indice.
• Cleanup: quando sei sicuro, elimina eventuali indici ridondanti con DROP INDEX CONCURRENTLY.

Cosa significa successo:

• La ricerca amministrativa passa da secondi a decine di millisecondi per i clienti tipici.
• Letture e scritture regolari continuano a funzionare durante la build.
• CPU e IO disco aumentano durante la build ma restano nei limiti di sicurezza.
• Puoi mostrare numeri chiari prima/dopo: tempo query, righe lette e storico dei lock.

Checklist rapida e passi successivi

Il lavoro sugli indici è più sicuro quando lo tratti come una piccola release di produzione: prepara, osserva mentre gira e verifica il risultato prima di pulire.

Prima di iniziare:

• Imposta timeout così un lock inatteso non rimanga appeso per sempre.
• Conferma spazio disco libero sufficiente per la nuova build.
• Cerca transazioni lunghe che possano rallentare la build.
• Scegli una finestra a basso traffico e definisci cosa significa "finito".
• Scrivi ora il tuo piano di rollback.

Mentre gira:

• Controlla blocchi e catene di attesa sui lock.
• Monitora il progresso con pg_stat_progress_create_index.
• Tieni d'occhio i sintomi dell'app: tassi di errore, timeouts e endpoint lenti legati alla tabella.
• Sii pronto a cancellare se le attese dei lock aumentano o se i timeouts lato utente salgono.
• Registra cosa è successo: ora di inizio, ora di fine e eventuali alert.

Dopo il completamento, conferma che l'indice sia valido, esegui una o due query chiave per verificare piano e miglioramento dei tempi, e solo allora rimuovi gli indici vecchi in modo non bloccante.

Se lo fai più di una volta, trasformalo in uno step di consegna ripetibile: un piccolo runbook, una prova in staging con dati simili alla produzione e un responsabile chiaro che osservi la build.

Se stai creando strumenti interni o pannelli admin con AppMaster (appmaster.io), aiuta trattare le modifiche al database come le build degli indici: parte della stessa checklist di rilascio del backend — misurata, monitorata e con rollback che puoi eseguire rapidamente.