PostgreSQL Locks con il Partitioning

Il locking e' fondamentale per garantire la consistenza dei dati. L'utilizzo delle partizioni consente di gestire grandi moli di dati in modo efficiente. In questa paginetta vediamo le problematiche di locking applicate a tabelle partizionate PostgreSQL in modo pratico con avvertenze e suggerimenti.

Per motivi di spazio vengono si considera che il lettore gia' conosca il partitioning e la gestione dei lock in PostgreSQL.

Introduzione

In PostgreSQL non solo le DML (INSERT, UPDATE, DELETE, MERGE) ma anche le DDL (eg. CREATE INDEX, ALTER TABLE) possono far parte di una transazione e quindi venir confermate con un COMMIT o rigettate con un ROLLBACK.
PostgreSQL ha una sofisticata gestione dei lock per garantire la consistenza delle transazioni e per proteggere gli oggetti durante le operazioni di modifica dei dati (DML) e di modifica della struttura dei dati (DDL).
Per comprendere i lock in PostgreSQL bisogna sapere che vi sono lock a livello di tabella e lock a livello di riga. Hanno regole ed implementazioni molto diverse tra loro e quindi vanno analizzati separatamente.

PostgreSQL supporta da sempre il partitioning mediante l'ereditarieta' degli oggetti ma questo ne rendeva l'utilizzo complesso e non sempre efficiente. Il partizionamento nativo, introdotto con la versione 10 e' invece molto piu' efficiente, molto piu' utilizzato ed e' quello che vedremo in questa paginetta. Il partizionamento nativo consente di definire tabelle partizionata con le modalita' Range, List ed Hash. In PostgreSQL la tabella partizionata non contiene nessun dato ma solo le definizioni, le partizioni sono in effetti tabelle a tutti gli effetti che possono essere utilizzate direttamente.

I lock PostgreSQL che ci interessano maggiormente sono i lock a livello di tabella che sono importanti sopratutto per le operazioni di DDL e che, con il partizionamento, presentano diversi casi particolari.

Table-level Locks

Il lock a livello di tabella ha molteplici lock mode a seconda del tipo di operazione. I lock vengono liberati al momento del commit o del rollback della transazione.
Dal punto di vista dell'implementazione PostgreSQL i table-level lock vengono mantenuti in memoria in un area dedicata.

La figura seguente (che e' una versione leggermente semplificata rispetto alla tabella completa) indica quale lock richiedono i vari comandi SQL e quali sono gli eventuali conflitti. Analizzando la figura e' evidente che una CREATE INDEX blocca le UPDATE, mentre una CREATE INDEX CONCURRENTLY consente le UPDATE; e' possibile lanciare una CREATE INDEX anche se ve ne e' una gia' in corso, mentre e' possibile lanciare solo una CREATE INDEX CONCURRENTLY alla volta.

Table-Level Locks

Requested Lock Mode		Existing Lock Mode
		ACCESS SHARE	ROW EXCLUSIVE	SHARE UPDATE EXCLUSIVE	SHARE	SHARE ROW EXCLUSIVE	ACCESS EXCLUSIVE
	SQL	SELECT	INSERT, UPDATE, DELETE, MERGE	VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, ALTER TABLE (some), ALTER INDEX (some), CREATE STATISTICS	CREATE INDEX	CREATE TRIGGER, ALTER TABLE (some)	DROP TABLE, TRUNCATE, VACUUM FULL, REINDEX, CLUSTER, ALTER TABLE (most), ALTER INDEX (most), LOCK TABLE, DROP INDEX, ...
ACCESS SHARE	SELECT						X
ROW EXCLUSIVE	INSERT, UPDATE, DELETE, MERGE				X	X	X
SHARE UPDATE EXCLUSIVE	VACUUM, ...			X	X	X	X
SHARE	CREATE INDEX		X	X		X	X
SHARE ROW EXCLUSIVE	CREATE TRIGGER, ...		X	X	X	X	X
ACCESS EXCLUSIVE	DROP TABLE, ...	X	X	X	X	X	X

Ma come si comporta PostgreSQL nel caso di tabelle partizionate?
Le partizioni in PostgreSQL sono tabelle a tutti gli effetti e si comportano come tali. Ogni partizione foglia e' una tabella separata e quindi indipendente dal punto di vista dei lock. Ma vi sono molti casi un cui viene coinvolta la tabella partizionata e non solo le singole partizioni individualmente come vedremo nei prossimi paragrafi che riportano le piu' tipiche operative: Create / Attach / Drop / Truncate / Detach Partition, Create / Drop Index on a Partitioned Table, Sub Partitioning, Default Partition.

Create partition

Se le attivita' sulla tabella partizionata sono sempre di breve durata non c'e' problema: il lock verra' ottenuto dopo poco tempo e la creazione della partizione avverra' velocemente. Ma se vi sono query di lunga durata l'attesa del lock per la CREATE PARTITION e per le richieste successive sara' elevata, per questo motivo e' possibile utilizzare una modalita' alternativa effettuando l'ATTACH di una partizione.

Attach partition

Se la partizione da connettere e' vuota l'operazione e' di ATTACH e' molto veloce e richiede lock che consentono DML sulla tabella partizionata senza attese.
Se la futura partizione contiene dati PostgreSQL deve effettuare uno scan per controllare che tutti i record presenti soddisfino le condizioni di partizionamento.

Suggerimento: per evitare rallentamenti in fase di ATTACH di una partizione che gia' contiene dati e' possibile creare preventivamente tutti gli indici necessari ed utilizzare un CHECK constraint che puo' essere successivamente elimitato. In questo modo l'attach sara' veloce anche se la partizione da aggiungere contiene molti record.

Vi sono alcuni comandi ulteriori da utilizzare ma l'operativa non e' complessa:

Drop partition

Il drop di una partizione e' terribilmente piu' efficiente di una DELETE e delle successivi attivita' di VACUUM, l'unica avvertenza e' sul locking...

Attenzione: la drop di una partizione e' un'operazione tipicamente di breve durata ma utilizza un lock ACCESS EXCLUSIVE anche sulla tabella partizionata. Questo significa che la richiesta di lock viene accodata fino al termine agli accessi correnti e le richieste successive sulla tabella partizionata vengono poste a loro volta in attesa.
Se le attivita' sulla tabella partizionata sono sempre di breve durata non c'e' problema: il lock verra' ottenuto dopo poco tempo e la drop della partizione avverra' velocemente. Ma se vi sono query di lunga durata l'attesa del lock sara' elevata, per questo motivo e' possibile utilizzare modalita' alternative per eliminare i dati da una partizione.

Truncate partition

Il truncate di una partizione e' terribilmente piu' efficiente di una DELETE e delle successivi attivita' di VACUUM e non coinvolge la tabella partizionata ma solo la partizione interessata. Tutto lo spazio viene immediatamente liberato e le statistiche aggiornate e' quindi un'alternativa valida al drop di una partizione. E' comunque opportuno effettuare il drop attendendo un momento in cui non vi sono problemi di lock (il drop e' comunque di breve durata.

Detach partition

Suggerimento: dalla versione PG14 e' disponibile ALTER TABLE... DETACH PARTITION... CONCURRENTLY che utilizza un locking meno pesante.

Con la modalita' CONCURRENTLY internamente vengono effettuate due transazioni; nella prima transazione viene avviata la fase di detach utilizzando un lock SHARE UPDATE EXCLUSIVE sulla tabella e sulla partizione, questo lock non blocca le DML. Dopo il primo commit si attende la conclusione di tutte le transazioni attive sulla partizione; quindi viene richiesto un lock SHARE UPDATE EXCLUSIVE sulla tabella partizionata ed un lock ACCESS EXCLUSIVE sulla partizione. In pratica le DML rivolte alla tabella partizionata non vengono mai bloccate.
La modalita' CONCURRENTLY non puo' essere utilizzata in una transazione e non puo' essere utilizzata se e' presente una DEFAULT partition. Nel caso in cui l'operazione sia stata interrotta a meta' puo' essere completata con DETACH PARTITION... FINALIZE.

Create index

La creazione di un indice richiede un lock SHARE e, se la dimensione degli oggetti e' elevata, tale lock deve essere mantenuto a lungo perche' la costruzione di un indice richiede tempo [NdA O(n log n)]. Come e' noto un lock SHARE non blocca le SELECT ma blocca tutte le modifiche sulla tabella con INSERT, UPDATE, DELETE, MERGE. Inoltre la creazione di un indice, se la tabella sottostante ha molti record, puo' richiedere molto tempo, quindi il lock viene mantenuto a lungo.
Con le normali tabelle, per evitare il blocco sulle modifiche dei dati, per la creazione di un indice on-line si utilizza la clausola CONCURRENTLY, ma tale modalita' non e' consentita per le tabelle partizionate.

E' possibile creare un nuovo indice su una tabella partizionata senza rallentare le operazioni di modifica dei dati, cosi' come avviene sulle tabelle singole, creando l'indice con la clausola ONLY sulla tabella partizionata e sfruttando la clausola CONCURRENTLY nella creazione degli indici su tutte le partizioni. La creazione di un indice con la clausola ONLY modifica solo i metadati della tabella partizionata ed e' molto veloce. La creazione di un indice con la clausola CONCURRENTLY richiede un lock SHARE UPDATE EXCLUSIVE che e' compatibile con le DML.

Per controllare la presenza di indici INVALID in PostgreSQL si utilizza la seguente query:

SELECT n.nspname as schema, c2.relname as table, c1.relname as index
  FROM pg_class c1, pg_index i, pg_namespace n, pg_class c2
 WHERE c1.relnamespace = n.oid
   AND i.indexrelid = c1.oid
   AND c2.oid = i.indrelid
   AND i.indisvalid = false;

Come gia' riportato, possono essere creati indici anche sulle singole partizioni; come per le normali tabelle anche questi indici possono essere creati con la clausola CONCURRENTLY per non bloccare INSERT/UPDATE/DELETE.

Drop index

Per la DROP il comportamento nella gestione dei lock e' diverso: anche con la clausola CONCURRENTLY viene usato un lock ACCESS EXCLUSIVE, ma l'istruzione attende che non vi siano altri accessi e quindi non crea un lock in attesa. Quando la tabella e' libera da altri accessi il lock viene richiesto ed ottenuto immediatamente e l'operazione di cancellazione di un indice e' di breve durata.
Vi sono pero' molti limiti nell'uso della clausola CONCURRENTLY nella DROP (eg. transazioni, cascade) e non e' possibile utilizzarla neanche per gli indici di tabelle partizionate.

VACUUM ed ANALYZE

Subpartitioning

Ad esempio se si esegue un ATTACH di una partizione che a sua volta e' partizionata il lock ACCESS EXCLUSIVE sulla partizione da aggiungere viene richesto anche su tutte le sotto partizioni.

Default partition

Attenzione: l'utilizzo della partizione di DEFAULT impedisce l'uso di alcune delle tecniche descritte (eg. DETACH... CONCURRENTLY) per evitare lock sospensivi e rallenta altre operative.
In generale e' opportuno evitare l'utilizzo della DEFAULT partition in PostgreSQL.

Lock timeout

Lock Sizing

PostgreSQL utilizza un area di memoria per mantenere i Table-Level lock. L'area e' dimensionata basandosi sui parametri max_connections (default: 100), max_locks_per_transaction (default: 64) e max_pred_locks_per_transaction (default: 0) che porta ad un totale massimo di 6.400 lock. Il limite non e' per transazione ma e' totale per tutte le sessioni attive.
Nella pratica e' difficile superare il limite dei lock in shared memory, ma nel caso in cui avvenga l'errore e' il seguente:

Quando si opera su tabelle con un gran numero di partizioni e non si effettua il pruning per PostgreSQL ciascuna partizione e' una tabella a tutti gli effetti ed i Table-Level lock vengono richiesti per tutte le partizioni: in questi casi e' possibile superare il limite dei lock e ricevere l'errore.

Evitare l'overpartitioning ed effettuare il pruning risolve il problema.
Se non e' possibile agire altrimenti per evitare l'errore non si puo' fare altro che aumentare il valore del parametro max_locks_per_transaction (o il max_connections):

Row-level Locks

I lock a livello di riga riguardano solo le attivita' di scrittura e non le letture: due transazioni diverse non possono mantenere un lock sulla stessa riga contemporaneamente. Al contrario le attivita' in sola lettura non vengono mai messe in attesa da un lock di riga, anche se e' possibile che vedano righe diverse grazie all'MVCC (MultiVersion Concurrency Control).
Anche i lock a livello di riga vengono liberati al momento del commit o del rollback della transazione.

Cio' premesso quali sono le differenze sui lock row-level tra le normali tabelle e le partizioni?
Nessuna differenza! Le normali tabelle e le partizioni sono trattate nello stesso identico modo. Le tabelle partizionate o sottopartizionate non contengono nessun dato, le partizioni foglia sono normali tabelle ed i lock a livello di riga vengono mantenuti sulla heap nella tupla interessata.

In Postgres il lock di riga non ha alcun limite, semplicemente richiede una scrittura sulla heap per indicare che e' presente un lock. Se non si utilizzano particolari estensioni e query i lock di riga non sono visibili, diventano evidenti solo quando vi e' un conflitto ed una transazione resta in attesa: solo in questo momento diventano visibili sulla pg_locks. Ma non vi e' nessuna differenza nel trattamento dei lock a livello di riga quando queste fanno parte di partizioni.

Varie ed eventuali

Possono essere utili le pagine che descrivono il partizionamento nativo, il locking e gli indici in PostgreSQL.

Come sempre la documentazione ufficiale (eg. lock, ALTER TABLE) e' la fonte piu' completa ed affidabile delle informazioni.

Titolo: PostgreSQL Locks con il Partitioning
Livello: Avanzato (3/5)
Data: 1 Aprile 2025
Versione: 1.0.1 - 1 Aprile 2025
Autore: mail [AT] meo.bogliolo.name