Indici concorrenti (CREATE INDEX CONCURRENTLY)
Crei indici senza bloccare le scritture usando CREATE INDEX CONCURRENTLY e recuperi da creazioni interrotte.
Indici concorrenti (CREATE INDEX CONCURRENTLY) è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 2 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.
Il problema di CREATE INDEX
Un normale CREATE INDEX acquisisce un lock SHARE: blocca le scritture (ma non le letture) per l'intera durata della creazione. Su una tabella con un miliardo di righe, significa interrompere le scritture per diversi minuti.
CONCURRENTLY: la soluzione
CREATE INDEX CONCURRENTLY crea l'indice senza bloccare le scritture:
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);Come funziona
Due scansioni della tabella:
- Primo passaggio: crea l'indice a partire dalle righe presenti
- Secondo passaggio: include le righe modificate durante il primo passaggio
Inoltre, attende brevemente le transazioni concorrenti iniziate prima della creazione.
Non può essere eseguito all'interno di una transazione
CONCURRENTLY esegue due commit, quindi non può essere usato all'interno di un blocco esplicito BEGIN/COMMIT:
BEGIN;
CREATE INDEX CONCURRENTLY ...; -- ERROR
COMMIT;Lento, ma online
CONCURRENTLY richiede più tempo di un normale CREATE INDEX (due scansioni e attese dei lock). Il vantaggio è l'assenza di downtime.
Recupero dagli errori
Se CONCURRENTLY non riesce (a causa di un'interruzione o di un errore di sistema), l'indice rimane nello stato INVALID:
SELECT relname FROM pg_class
WHERE relkind = 'i' AND NOT pg_index.indisvalid;
-- Fix: drop and re-create:
DROP INDEX CONCURRENTLY my_idx;
CREATE INDEX CONCURRENTLY my_idx ON ...;REINDEX CONCURRENTLY
Ricrea l'indice senza bloccare, a partire da PG 12:
REINDEX INDEX CONCURRENTLY orders_user_id_idx;
REINDEX TABLE CONCURRENTLY orders;DROP INDEX CONCURRENTLY
Anche questa operazione non blocca:
DROP INDEX CONCURRENTLY old_idx;Indici UNIQUE
Anche gli indici UNIQUE possono essere creati con CONCURRENTLY. È utile per aggiungere vincoli UNIQUE online:
CREATE UNIQUE INDEX CONCURRENTLY users_email_uidx ON users(email);
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE USING INDEX users_email_uidx;Indici per chiavi esterne
Il modello standard per aggiungere una chiave esterna a una tabella di grandi dimensioni:
-- 1. Build the index online:
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders(user_id);
-- 2. Add the FK with NOT VALID (instant):
ALTER TABLE orders ADD CONSTRAINT orders_user_fk
FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
-- 3. Validate in the background:
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;Costo della creazione concorrente degli indici
Due scansioni complete della tabella, più le operazioni di gestione. Consuma CPU e I/O. Quando possibile, esegua l'operazione nelle ore di minor traffico.
Monitorare l'avanzamento
PG 12+ espone l'avanzamento in pg_stat_progress_create_index:
SELECT phase,
blocks_done, blocks_total,
tuples_done, tuples_total
FROM pg_stat_progress_create_index;Riepilogo
CONCURRENTLY è il modo più adatto alla produzione per gestire gli indici.
- CREATE/DROP/REINDEX CONCURRENTLY non bloccano le scritture
- Non possono essere usati all'interno di una transazione
- Gli errori lasciano indici INVALID: è necessario ripulirli
- È possibile monitorare l'avanzamento con pg_stat_progress_create_index
Verifica rapida
Perché CREATE INDEX CONCURRENTLY non può essere eseguito all'interno di un blocco esplicito BEGIN/COMMIT?
Domande Frequenti
La lezione «Indici concorrenti (CREATE INDEX CONCURRENTLY)» è gratuita?
Sì — il testo completo di «Indici concorrenti (CREATE INDEX CONCURRENTLY)» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.
Cosa imparerò in «Indici concorrenti (CREATE INDEX CONCURRENTLY)»?
Crei indici senza bloccare le scritture usando CREATE INDEX CONCURRENTLY e recuperi da creazioni interrotte. Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.
Ho bisogno di esperienza per iniziare SQL Academy?
Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 2 di 4.
Quanto tempo richiede la lezione «Indici concorrenti (CREATE INDEX CONCURRENTLY)»?
La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.
Posso scrivere ed eseguire codice in questa lezione SQL Academy?
Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.
Tutte le lezioni di questo corso
- Migrazioni online: perché ALTER TABLE applica i lock
- Indici concorrenti (CREATE INDEX CONCURRENTLY)
- Rinominare le colonne senza downtime
- Strumenti: Flyway, Liquibase, Sqitch