Storia della domanda
PostgreSQL ha introdotto le dichiarazioni preparate per eliminare l'onere di analisi e pianificazione per le query SQL eseguite ripetutamente. Le versioni iniziali generavano sempre piani di esecuzione personalizzati adattati a valori di parametro specifici, ma ciò comportava costi significativi per la CPU per query complesse. Per ottimizzare tutto ciò, PostgreSQL 9.2 ha implementato la cache dei piani con un meccanismo di piano generico che riutilizza una singola struttura di piano attraverso più esecuzioni. Sebbene questo approccio riduca la latenza di pianificazione, tratta tutti i parametri come segnaposto opachi durante la fase di pianificazione iniziale.
Il problema
La potatura delle partizioni in PostgreSQL opera in due fasi distinte: la potatura al momento del piano, che si verifica durante la pianificazione della query quando il pianificatore esamina i vincoli di partizione rispetto a valori letterali, e la potatura al momento dell'esecuzione, che filtra le partizioni durante l'esecuzione utilizzando join a livello di partizione o filtraggio dei nodi di append. I piani generici creati per le dichiarazioni preparate mancano di valori di parametro concreti al momento della pianificazione, rendendo impossibile la potatura al momento del piano. Di conseguenza, il pianificatore genera una scansione su tutte le partizioni indipendentemente dai veri valori di parametro forniti durante l'esecuzione, portando a un catastrofico degrado delle prestazioni su tabelle partizionate di grandi dimensioni.
La soluzione
La risoluzione richiede di costringere PostgreSQL a generare piani personalizzati che incorporino i valori di parametro effettivi durante la fase di pianificazione. Questo si ottiene impostando il parametro di configurazione plan_cache_mode su force_custom_plan per la sessione o la query specifica, bypassando completamente la cache del piano generico. In alternativa, le soluzioni a livello di protocollo includono l'utilizzo del protocollo di query esteso con il messaggio Bind contenente valori letterali anziché parametri, o l'impiego di costruttori di query lato client che inondano valori letterali per le chiavi di partizione mantenendo altri parametri vincolati per prevenire l'iniezione SQL.
-- Forza piano personalizzato per questa sessione SET plan_cache_mode = force_custom_plan; -- Oppure usa SQL dinamico con format() per inondare i letterali in modo sicuro EXECUTE format('SELECT * FROM logs WHERE log_date >= %L', '2024-01-01');
Una piattaforma di analisi del trading ad alta frequenza ha subito gravi picchi di latenza ogni mattina durante le query sui movimenti di prezzo intraday. Il database memorizzava dati di tick in una tabella partizionata per data di sessione di trading, contenente oltre 2.000 partizioni su un arco di cinque anni. L'applicazione utilizzava dichiarazioni preparate JDBC con segnaposto ? per il parametro della data per prevenire l'iniezione SQL e ridurre l'onere di analisi.
Il team di sviluppo ha inizialmente osservato che le query che filtravano i dati "di oggi" stavano scandagliando partizioni storiche, consumando 45 secondi invece dei previsti 300 millisecondi. Questo degrado delle prestazioni si è verificato perché il piano generico non riusciva a eliminare le partizioni irrilevanti durante la fase di pianificazione.
Un approccio ha comportato la creazione di una tabella non registrata separata per i dati attivi e la migrazione dei record la notte. Questa strategia avrebbe completamente bypassato la tabella partizionata per le query recenti, ma ha introdotto una logica ETL complessa e ha rischiato di perdere dati durante i crash di sistema.
Una proposta alternativa suggeriva di disabilitare globalmente le dichiarazioni preparate nel pool di connessioni JDBC. Sebbene ciò avrebbe ripristinato la potatura al momento del piano esponendo valori letterali al pianificatore, i benchmark hanno rivelato un aumento del 40% dell'utilizzo della CPU sul server di database a causa della ripetuta analisi e pianificazione.
Il team ha anche valutato l'uso delle capacità di potatura delle partizioni a livello di esecuzione di PostgreSQL introdotte nella versione 11. Tuttavia, la potatura al momento dell'esecuzione elimina solo le partizioni dopo che l'esecutore inizia a scandire, il che significa che il pianificatore ha comunque allocato risorse per tutte le partizioni e ha prodotto ordini di join subottimali che ignoravano i confini delle partizioni.
Alla fine, il team ha scelto di implementare un cambio di configurazione a livello di connessione. Hanno configurato il pool di connessioni per rilevare le query mirate a tabelle partizionate ed eseguire SET plan_cache_mode = force_custom_plan prima di inviare quelle dichiarazioni specifiche. Questo ha preservato i benefici di sicurezza delle query parametrizzate per i filtri di input degli utenti, assicurando che i valori delle chiavi di partizione fossero visibili al pianificatore.
Il risultato ha ridotto la latenza delle query a 280 millisecondi e ha diminuito l'utilizzo complessivo della CPU del database del 15%, poiché il pianificatore ora poteva utilizzare l'esclusione dei vincoli per eliminare 1.999 partizioni prima dell'esecuzione. Questa ottimizzazione ha permesso alla piattaforma di trading di soddisfare i severi requisiti di latenza mattutina senza compromettere l'integrità o la sicurezza dei dati.
Come decide PostgreSQL tra piani generici e personalizzati quando plan_cache_mode è impostato su auto?
In modalità auto, PostgreSQL pianifica ed esegue la query utilizzando un piano personalizzato per le prime cinque esecuzioni, accumulando il costo di pianificazione. Dopo la quinta esecuzione, confronta il tempo medio di esecuzione del piano generico (stimato durante la prima esecuzione) con il tempo medio di esecuzione dei piani personalizzati più il loro sovraccarico di pianificazione. Se il costo stimato del piano generico è inferiore al costo medio del piano personalizzato, il sistema passa permanentemente al piano generico per quella dichiarazione preparata. I candidati spesso perdono di vista che questo confronto include il sovraccarico di pianificazione risparmiato riutilizzando il piano generico e che la decisione è permanente per la vita della dichiarazione preparata a meno che non venga esplicitamente ripianificata.
Qual è la distinzione tra potatura delle partizioni al momento del piano e al momento dell'esecuzione nel contesto delle dichiarazioni preparate?
La potatura al momento del piano si verifica durante la fase di pianificazione quando il pianificatore può dimostrare che alcune partizioni non possono contenere righe rilevanti in base ai vincoli di partizione e ai valori letterali nella query. La potatura al momento dell'esecuzione si verifica durante l'esecuzione quando l'esecutore controlla i vincoli di partizione rispetto ai valori di parametro effettivi utilizzando il meccanismo di filtro dell'esecutore. I piani preparati generici supportano la potatura al momento dell'esecuzione a partire da PostgreSQL 11, ma non possono supportare la potatura al momento del piano perché i valori dei parametri sono sconosciuti. I candidati spesso confondono questi meccanismi, credendo che la potatura al momento dell'esecuzione risolva tutti i problemi di partizionamento delle dichiarazioni preparate, senza rendersi conto che la potatura al momento del piano è fondamentale per una pianificazione efficiente dei join e per la selezione degli indici.
Perché force_custom_plan potrebbe non risolvere i problemi di potatura delle partizioni nelle versioni di PostgreSQL precedenti alla 10?
Prima della versione 10, PostgreSQL mancava di un supporto significativo per la potatura delle partizioni al momento dell'esecuzione e le dichiarazioni preparate non potevano beneficiare dell'esclusione dei vincoli nemmeno con piani personalizzati se i parametri venivano passati attraverso il protocollo di query esteso utilizzando il messaggio Bind. Il pianificatore trattava tutti i parametri vincolati come esterni al processo di pianificazione, richiedendo valori letterali espliciti nella stringa della query per attivare l'esclusione dei vincoli. Questa limitazione storica significa che nei sistemi legacy, anche i piani personalizzati avrebbero scandito tutte le partizioni, necessitando la generazione dinamica di SQL con EXECUTE ... USING con letterali o concatenazione di stringhe lato client con un'adeguata escaping, piuttosto che vincolando i parametri a livello di protocollo moderno.