pg_repack su tabelle senza indice univoco: istruzioni per l’uso

Quando il bloat diventa vistoso ma la tabella incriminata non ha né PRIMARY KEY né un indice UNIQUE NOT NULL, pg_repack si rifiuta di lavorare:

ERROR: relation "foo" must have a primary key or not-null unique keys
Requirement: “Target table must have a PRIMARY KEY, or at least a UNIQUE total index on a NOT NULL column.”

Di seguito vediamo perché serve quell’unicità, come aggiungerla in modo sicuro (e reversibile), e quali alternative considerare se proprio non puoi toccare lo schema.

Perché pg_repack pretende una chiave

pg_repack ricostruisce la tabella in parallelo mentre applica — tramite trigger — tutte le scritture che arrivano durante la copia. Per garantire che ogni riga sia replicata una sola volta ha bisogno di un identificatore univoco stabile: se manca, non può sapere quali tuple sono già state trattate. Da qui il vincolo su PK/UNIQUE.

Strategia rapida: indice univoco temporaneo

Se la tabella possiede già una colonna con valori distinti e senza NULL (ad esempio un codice esterno), basta:

CREATE UNIQUE INDEX CONCURRENTLY ux_mytable ON public.mytable(unique_column_name);

Poi lancia:

pg_repack --table=public.mytable --no-order myob

e infine (se non ti serve più):

DROP INDEX CONCURRENTLY public.ux_mytable;

Lock? Solo quelli impliciti dell’INDEX CONCURRENTLY (pochi millisecondi) e quelli imposti da pg_repack (normalmente secondi – vedi avvertenze al termine dell’articolo). Spazio disco addizionale: circa la dimensione dell’indice.

Quando non esiste nessuna colonna candidata

Aggiungere una surrogate key (metodo consigliato)

Aggiungi una colonna vuota

ALTER TABLE public.log_big ADD COLUMN repack_id bigint ;

Aggiungi una sequence per assegnare una numerazione automatica

CREATE SEQUENCE log_big_repack_seq OWNED BY public.log_big.repack_id ;

Assegna un valore di default alla nuova colonna usando la sequence

ALTER TABLE public.log_big ALTER COLUMN repack_id SET DEFAULT nextval('log_big_repack_seq') ;

Popola in batch

DO 
$$ 
DECLARE _done boolean := false; 
    BEGIN WHILE NOT _done LOOP 
        WITH cte AS ( 
            UPDATE public.log_big 
            SET repack_id = nextval('log_big_repack_seq') 
      WHERE repack_id IS NULL 
      LIMIT 100000 -- batch da 100k 
      RETURNING 1
        ) 
        SELECT COUNT(*)=0 INTO _done FROM cte; 
        COMMIT; -- rilascia lock e memoria 
    END LOOP; 
END
$$;

Aggiungi un indice univoco

Usa CONCURRENTLY per creare il nuovo indice in parallelo:

CREATE UNIQUE INDEX CONCURRENTLY log_big_repack_uidx ON public.log_big (repack_id);

(Facoltativo) Promuovi la colonna a PK

Richiede un lock più lungo, ma da PostgreSQL 12 puoi evitare la scansione totale con NOT VALID + VALIDATE:

ALTER TABLE public.log_big ADD CONSTRAINT log_big_pk PRIMARY KEY USING INDEX log_big_repack_uidx ;

A questo punto lancia pg_repack. Puoi tenere la colonna come surrogate key (utile per future maintenance) oppure rimuoverla dopo:

ALTER TABLE public.log_big
DROP CONSTRAINT IF EXISTS log_big_pk,
DROP COLUMN repack_id ;

3.2 Impatto e stime

Alternative se non vuoi cambiare lo schema

Checklist prima di premere Invio

1. Controlla di avere spazio libero ≥ 2 × (tabella + indici)
2. Uniformità nelle versioni di pg_repack: versione client = pg_repack = estensione server.
3. Verifica che autovacuum non stia facendo VACUUM FULL sulla stessa tabella.
4. Imposta --wait-timeout adeguato e pianifica fuori dal picco.
5. Monitora con pg_stat_progress_repack (da PostgreSQL 15) o log in modalità verbose.

Ma è davvero innocuo ?

Vista la sua efficacia, pg_repack è un “power tool” che di solito consideriamo quasi innocuo perché effettua la manutenzione delle tabelle online, ma dietro le quinte impone per ben tre volte un Access EXCLUSIVE lock: se durante quella finestra non riesce a bloccare tutti gli altri processi, prima li cancella e poi, superato il doppio del wait-timeout, li termina brutalmente, con il rischio concreto di timeout applicativi o veri e propri outage. La tentazione di schedularlo in automatico su ogni tabella – come fosse un vaccino miracoloso contro il bloat – è quindi potenzialmente pericolosa: oltre al lock, genera un picco di I/O e di WAL e blocca qualsiasi DDL sullo schema. Morale: usatelo solo in finestre di bassa concorrenza, con l’opzione --no-kill-backend sui sistemi critici, e non usatelo come se fosse una cura universale.

Conclusioni

pg_repack è lo strumento-laser per eliminare il bloat online, ma vuole un bersaglio con coordinate precise. Se la tua tabella ne è priva, creare in modo sicuro una chiave surrogata è l’intervento meno invasivo rispetto al costo operativo di un VACUUM FULL.
Investi qualche minuto per preparare indice e colonna, poi lascia che pg_repack faccia il grosso del lavoro — e goditi una heap compatta senza interruzioni.

Fai attenzione ai rischi legati ai blocchi esclusivi che impone e schedulalo solo in finestre a bassa concorrenza.

Riferimenti:

pg_repack (codice)