0Pricing
SQL Academy · Lezione

Migrazioni online: perché ALTER TABLE applica i lock

Comprenda quali forme di ALTER TABLE acquisiscono un lock ACCESS EXCLUSIVE e riscrivono la tabella e quali operano solo sui metadati.

Migrazioni online: perché ALTER TABLE applica i lock è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 1 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 delle migrazioni in produzione

Su un database piccolo, ALTER TABLE è istantaneo. Su una tabella live da 500 GB, lo stesso comando può bloccare le scritture per 20 minuti. È essenziale sapere quali ALTER sono sicuri e quali no.

Livelli di lock

I lock di PostgreSQL sono organizzati in livelli:

  • ACCESS SHARE — select
  • ROW EXCLUSIVE — scritture
  • SHARE / SHARE ROW EXCLUSIVE — DDL compatibile con le letture
  • EXCLUSIVE — blocca le select
  • ACCESS EXCLUSIVE — blocca TUTTO

Quale lock richiede ALTER TABLE

La maggior parte delle varianti di ALTER acquisisce ACCESS EXCLUSIVE: blocca lettori e scrittori fino al completamento.

ALTER rapidi (solo metadati)

Alcuni ALTER modificano solo il catalogo e vengono completati in pochi millisecondi anche su tabelle enormi:

ALTER TABLE t RENAME COLUMN a TO b;
ALTER TABLE t ALTER COLUMN a SET DEFAULT ...;
ALTER TABLE t ADD COLUMN x INT;             -- nullable, no default: metadata only (PG 11+)
ALTER TABLE t ADD COLUMN x INT NOT NULL DEFAULT 0;  -- metadata only PG 11+ if default is constant

ALTER lenti (con riscrittura)

Questi comandi riscrivono l'intera tabella:

ALTER TABLE t ALTER COLUMN x TYPE BIGINT;     -- when types not binary-compatible
ALTER TABLE t SET LOGGED;
CLUSTER t USING idx;                          -- physically reorders rows
VACUUM FULL t;                                -- rewrites whole table

Aggiungere NOT NULL in sicurezza

Su una tabella di grandi dimensioni:

-- BAD: full table scan + ACCESS EXCLUSIVE lock
ALTER TABLE t ALTER COLUMN x SET NOT NULL;

-- BETTER:
ALTER TABLE t ADD CONSTRAINT x_not_null CHECK (x IS NOT NULL) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT x_not_null;     -- scans without exclusive lock
-- then drop the CHECK and add NOT NULL (still cheap because already validated):
ALTER TABLE t ALTER COLUMN x SET NOT NULL;
ALTER TABLE t DROP CONSTRAINT x_not_null;

Aggiungere chiavi esterne online

Si usa lo stesso approccio con NOT VALID:

ALTER TABLE orders
  ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

Problemi di attesa dei lock

Un ALTER in attesa di un lock ACCESS EXCLUSIVE si accoderà a ogni transazione di lunga durata. Anche le transazioni più recenti si accodano dietro l'ALTER: si crea una catena di query bloccate.

lock_timeout

Non lasci che le migrazioni rimangano bloccate per sempre:

SET lock_timeout = '5s';
ALTER TABLE t ...;
-- ERROR if it can't get the lock in 5s — retry.

Cicli di tentativi

Le migrazioni dovrebbero riprovare quando si verifica un lock_timeout:

for (let i = 0; i < 20; i++) {
  try {
    await client.query('SET lock_timeout = 5000');
    await client.query('ALTER TABLE ...');
    break;
  } catch (e) {
    if (e.code === '55P03') continue;     // lock_not_available
    throw e;
  }
}

statement_timeout per la sicurezza

Limiti il tempo massimo di esecuzione di ogni singola istruzione all'interno di una migrazione:

SET statement_timeout = '30s';

Strumenti utili

  • strong_migrations (Rails)
  • django-migrate-zero-downtime
  • pg-osc (Postgres Online Schema Change)
  • pgRoll

Riepilogo

Le migrazioni online richiedono di sapere:

  • Quali ALTER modificano solo i metadati e quali riscrivono la tabella
  • Come usare NOT VALID + VALIDATE per i vincoli
  • Come impostare lock_timeout e riprovare
  • Come evitare transazioni di lunga durata che bloccano le migrazioni

Verifica rapida

Aggiunge un vincolo NOT NULL a una tabella da 500 GB con un unico ALTER TABLE: cosa succede alle scritture?

Domande Frequenti

La lezione «Migrazioni online: perché ALTER TABLE applica i lock» è gratuita?

Sì — il testo completo di «Migrazioni online: perché ALTER TABLE applica i lock» è 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 «Migrazioni online: perché ALTER TABLE applica i lock»?

Comprenda quali forme di ALTER TABLE acquisiscono un lock ACCESS EXCLUSIVE e riscrivono la tabella e quali operano solo sui metadati. 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 1 di 4.

Quanto tempo richiede la lezione «Migrazioni online: perché ALTER TABLE applica i lock»?

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

  1. Migrazioni online: perché ALTER TABLE applica i lock
  2. Indici concorrenti (CREATE INDEX CONCURRENTLY)
  3. Rinominare le colonne senza downtime
  4. Strumenti: Flyway, Liquibase, Sqitch
← Torna a SQL Academy