PostgreSQL ha introdotto il partizionamento dichiarativo nella versione 10 per sostituire il modello di partizionamento basato su ereditarietà, che era complesso. Il pianificatore di query esegue la potatura statica delle partizioni durante la fase di pianificazione confrontando i predicati delle query con i confini delle partizioni, ma richiede che le espressioni siano valutabili a costanti al momento della pianificazione per determinare quali partizioni possono essere eliminate.
Il vincolo architettonico principale è che le funzioni STABLE, come now() o current_timestamp, non vengono valutate durante la pianificazione poiché i loro risultati possono differire tra pianificazione ed esecuzione o addirittura durante l'esecuzione della query. Di conseguenza, il pianificatore tratta i predicati che coinvolgono queste funzioni come scatole nere, incapaci di dimostrare che alcune partizioni non possano contenere righe corrispondenti, il che costringe a una scansione di tutte le partizioni.
La soluzione implica riscrivere il predicato per utilizzare funzioni IMMUTABLE o costanti letterali, o fare affidamento sulla potatura delle partizioni a tempo di esecuzione disponibile in PostgreSQL 11 e versioni successive. Impostando enable_partition_pruning su on, l'esecutore valuta i risultati delle funzioni STABLE rispetto ai confini delle partizioni al momento dell'esecuzione, saltando dinamicamente le partizioni irrilevanti dopo la fase iniziale di pianificazione.
Un'azienda di analisi finanziaria ha partizionato una tabella di transazioni per TIMESTAMPTZ sulla colonna execution_time utilizzando intervalli giornalieri per gestire terabyte di dati. Gli analisti interrogavano frequentemente attività recenti con WHERE execution_time >= now() - interval '1 hour', ma hanno osservato che queste query subivano un cattivo degrado delle prestazioni, scansionando tutte le 365 partizioni giornaliere invece di solo quella più recente.
Il primo approccio considerato è stato far calcolare il confine del timestamp al livello dell'applicazione e iniettarlo come costante letterale. Questo ha consentito una potatura statica immediata e ha ridotto il tempo di query da 45 secondi a 80 millisecondi. Tuttavia, ha rotto le query SQL esistenti incorporate in strumenti BI di terze parti che non potevano essere facilmente modificate.
Il secondo approccio prevedeva la creazione di una funzione immutabile personalizzata che restituiva un timestamp fisso basato sulla data corrente. Questo è stato respinto perché avrebbe prodotto risultati errati se la transazione del database fosse rimasta aperta oltre il confine della mezzanotte, violando il contratto STABLE che now() fornisce all'interno di una transazione. Tale violazione potrebbe portare a risultati erronei delle query se il pianificatore memorizzava in cache un intervallo di partizione obsoleto.
La soluzione scelta ha sfruttato le capacità di potatura delle partizioni a tempo di esecuzione di PostgreSQL 12. Gli amministratori del database si sono assicurati che enable_partition_pruning fosse attivato e hanno rifattorizzato l'applicazione per utilizzare istruzioni preparate con il confine temporale passato come parametro anziché concatenato nella stringa SQL. Questo ha consentito all'esecutore di potare dinamicamente le partizioni utilizzando il valore del parametro effettivo al momento dell'esecuzione, raggiungendo prestazioni quasi ottimali senza richiedere modifiche alla generazione del testo SQL negli strumenti di reporting legacy.
Qual è la differenza tra il meccanismo di esclusione dei vincoli di PostgreSQL per le tabelle ereditarie e la potatura nativa delle partizioni, e perché il primo richiede una configurazione GUC esplicita mentre il secondo no?
L'esclusione dei vincoli era il metodo di partizionamento utilizzato prima del partizionamento dichiarativo, basato su vincoli CHECK sulle tabelle figlie per dimostrare che le tabelle non potessero contenere righe rilevanti. Poiché valutare questi vincoli contro ogni tabella ereditata durante la pianificazione è costoso quando esistono centinaia di tabelle, è controllato dal parametro constraint_exclusion, che per impostazione predefinita è partition (controllando solo quando si interroga tramite ereditarietà). La potatura nativa delle partizioni utilizza strutture dati specializzate nel pianificatore che comprendono direttamente la gerarchia delle partizioni, rendendola più veloce e sempre abilitata, senza richiedere regolazioni GUC per il corretto funzionamento.
Quando si aggiorna una riga per spostarla tra le partizioni modificando la chiave di partizione, perché PostgreSQL esegue internamente un DELETE e un INSERT invece di un aggiornamento in loco, e quali implicazioni di trigger ha questo?
Poiché ogni partizione è una relazione heap distinta con uno stoccaggio fisico separato, modificare la chiave di partizione richiede di spostare la tupla da un file all'altro. PostgreSQL implementa questa transizione eliminando la riga dalla partizione di origine e inserendola nella partizione di destinazione. Ciò significa che i trigger a livello di riga BEFORE DELETE, AFTER DELETE, BEFORE INSERT e AFTER INSERT si attivano tutti durante quello che sembra essere una singola operazione di UPDATE. Inoltre, la replicazione logica streamma questo come due voci WAL separate (elimina e inserisci), il che può causare conflitti sui sottoscrittori se l'identità del replica non è configurata correttamente.
Quali specifici sovraccarichi di blocco e convalida si verificano quando si allega una nuova partizione a una tabella che ha una partizione DEFAULT, e come si può evitare la scansione completa della partizione predefinita?
Quando si allega una nuova partizione a una tabella partizionata per intervallo o elenco contenente una partizione DEFAULT, PostgreSQL deve scansionare l'intera partizione DEFAULT per verificare che non esistano righe che dovrebbero appartenere alla nuova partizione più specifica. Questa scansione di convalida acquisisce un blocco ACCESS EXCLUSIVE sulla tabella partizionata e può richiedere ore per partizioni predefinite di grandi dimensioni. Per evitare ciò, è opportuno disattivare la partizione DEFAULT prima di allegare la nuova partizione, quindi riattaccare la partizione DEFAULT solo dopo aver assicurato che non esistano righe in conflitto, oppure, in alternativa, utilizzare CREATE TABLE ... PARTITION OF per creare una nuova partizione vuota e migrare i dati utilizzando INSERT ... SELECT con una clausola WHERE che filtra per l'intervallo di partizione specifico, bypassando la scansione di convalida del contenuto della partizione predefinita.