Sviluppo web

Migrazioni sicure in produzione

Su 200.000 righe SQLite aggiunge una colonna in 0,17 ms e ricostruisce la tabella in 243,4 ms: fra i due numeri c'è il lock che l'utente aspetta.

In sintesi

  • Su 200.000 righe SQLite 3.51.3 aggiunge una colonna in 0,17–0,64 ms e ricostruisce la tabella in 243,4 ms.
  • Il conteggio delle pagine resta 3.847 prima e dopo un ADD COLUMN: il database non riscrive nulla.
  • Un DEFAULT non costante è rifiutato da SQLite: quel caso richiede la ricetta in dodici passi.
  • Ogni ricostruzione tiene il lock di scrittura per tutta la copia: è la durata che conta, non il comando.

Una migrazione è sicura quando sai per quanto tempo tiene il lock, non quando il comando è breve. Su 200.000 righe, SQLite 3.51.3 aggiunge una colonna in 0,64 ms e ricostruisce la tabella in 243,4 ms: fra i due numeri c'è la differenza fra un metadata update e una copia completa.

Cosa fa davvero un ALTER TABLE

Aggiungere una colonna nullable non tocca le righe: il database scrive una riga di catalogo e lascia i valori assenti. PostgreSQL 11, pubblicato il 18 ottobre 2018, ha esteso la stessa idea al caso con NOT NULL DEFAULT costante; prima di quella versione la tabella veniva riscritta. SQLite si comporta allo stesso modo e il conteggio delle pagine lo dimostra: 3.847 prima, 3.847 dopo.

Tre classi, tre rischi

La prima classe è metadata: aggiungere una colonna, rinominare un oggetto, creare un indice su una tabella vuota. La seconda è riscrittura: cambiare il tipo di una colonna, aggiungere un CHECK, togliere il NULL. La terza è backfill: un UPDATE su milioni di righe, che scrive tanto e genera WAL. Le prime due si misurano in laboratorio, la terza si misura in produzione e va spezzata in lotti.

Il comando che dice se la tabella viene riscritta

Il conteggio delle pagine è il segnale più semplice e non richiede un ambiente di prova: se cresce, il database ha copiato i dati. Esegui il confronto su una copia del database, non su quello vero.

node -e "const {DatabaseSync}=require('node:sqlite');const db=new DatabaseSync('app.db');const before=db.prepare('PRAGMA page_count').get().page_count;const t=process.hrtime.bigint();db.exec('ALTER TABLE orders ADD COLUMN archived INTEGER NOT NULL DEFAULT 0');console.log({ms:Number(process.hrtime.bigint()-t)/1e6,before,after:db.prepare('PRAGMA page_count').get().page_count})"

La ricetta expand, backfill, contract

Per cambiare una colonna senza fermare il servizio si lavora in tre release. Nella prima aggiungi la colonna nuova, nullable, e il codice scrive su entrambe. Nella seconda copi i dati a lotti, con un ordine stabile e una pausa fra i lotti. Nella terza il codice legge solo la colonna nuova, aggiungi il vincolo e rimuovi la vecchia. Ogni passo è reversibile e nessuno tiene un lock lungo.

Il limite: la copia non è gratuita

I 243,4 ms misurati valgono per 200.000 righe con la cache calda: su una tabella da cinquanta milioni di righe la stessa ricetta dura minuti e SQLite non ha DDL online, quindi il lock di scrittura resta chiuso per tutta la copia. PostgreSQL permette CREATE INDEX CONCURRENTLY, ma un ALTER TABLE che cambia il tipo richiede comunque un lock esclusivo, breve solo se la tabella è piccola. Nessuno strumento di migrazione cambia questa aritmetica.

Da dove partire

    Domande frequenti

    Aggiungere una colonna con DEFAULT riscrive la tabella?

    No, se il valore è una costante e il database è aggiornato. PostgreSQL lo fa dal 11, SQLite da sempre: il conteggio delle pagine resta identico. Prima di PostgreSQL 11 la tabella veniva riscritta per intero.

    Posso eliminare una colonna senza ricostruire la tabella?

    Dipende dalla versione. SQLite supporta DROP COLUMN dalla 3.35 e PostgreSQL da sempre, con un lock esclusivo breve. Se la colonna è coinvolta in un indice o in un vincolo, serve comunque la ricetta con tabella nuova.

    Come faccio un backfill senza bloccare le scritture?

    A lotti, con un ordine stabile e una pausa fra un lotto e l'altro. Aggiorna qualche migliaio di righe per volta, controlla il ritardo di replica e interrompi se il lock di scrittura cresce.

    Meglio scrivere le migrazioni a mano o generarle?

    Generale con lo strumento e rileggile prima di applicarle. Prisma e drizzle-kit producono SQL corretto ma non conoscono il volume della tua tabella, e il volume decide se la migrazione è metadata o riscrittura.

    Cosa faccio se una migrazione va male?

    Torna al backup e riparti dal passo precedente della sequenza expand, backfill, contract. Il rollback di un ALTER TABLE non ripristina i dati già copiati, quindi ogni passo deve restare compatibile con il codice in esecuzione.