Utilizzare PostgreSQL JDBC
Prepared Statement

L'accesso ai dati mediante un driver JDBC e' uno dei metodi piu' diffusi per raccogliere e trattare dati con un database relazionale.

Anche con il database Open Source PostgreSQL il driver JDBC e' uno dei metodi di accesso piu' utilizzati. Il driver JDBC di Postgres e' robusto, affidabile e ben performante... se utilizzato correttamente.
In questa paginetta vedremo i Prepared Statements che consentono vantaggi prestazionali se utilizzati correttamente. Come vedremo le possibilita' sono molte e vi e' qualche insidia e' quindi importante conoscerli bene per evitare problemi ed ottenere il massimo delle prestazioni.

Prepared Statements

Con i prepared statement viene definito lo statement una sola volta e quindi vengono passate (bind) variabili diverse per ogni esecuzione.
Utilizzare i prepared statement e' una buona pratica di programmazione che riduce notevolemente i rischi di SQL injection e separa la creazione dello statement dall'impostazione dei valori delle variabili. Questo consente significativi vantaggi prestazionali, ma prima di vedere quali e' necessario chiarire un aspetto.

In realta' in PostgreSQL tecnicamente vi sono due tipi di prepared statement che hanno importanti differenze tra loro: i Client-Side Prepared Statements ed i Server-Side Prepared Statements. Spesso ci si riferisce a loro con il termine generico di Prepared Statements, anche perche' non ci sono differenze dal punto di vista dell'applicazione, ma per comprendere tutti i dettagli del funzionamento con un driver JDBC e' importante distinguere i due casi.

Client-Side Prepared Statements

Il driver PgJDBC standard di PostgreSQL supporta i prepared statement. Quando viene utilizzata la funzione prepareStatement() in Java viene creato un Client-Side Prepared Statements:

In questo modo lo Statement stmt viene allocato una sola volta ma puo' essere richiamato piu' volte assegnando valori diversi alle variabili di bind.

I prepared statement creati in questo modo sono Client-Side ed alla base dati vengono passati normali statement SQL preparati ad ogni esecuzione dal driver JDBC.
Dal punto di vista della base dati non vi e' differenza tra un'istanza Statement e PreparedStatement Java, possono essere qualsiasi comando SQL valido: SELECT, DML, DDL contenere o meno variabili (indicate con ?): vengono ricevuti e trattati nello stesso modo.

Attenzione: e' molto importante che i datatype delle variabili Java utilizzate siano corrispondenti ai datatype presenti nella base dati, in caso contrario possono verificarsi errori o problemi di performance a volte difficili da diagnosticare.

Un comando SQL viene inviato alla base dati dal driver JDBC ed al client viene restituito il risultato ottenuto da PostgreSQL. Per default viene restituito l'intero risultato del comando; se si tratta di una query questa viene eseguita e tutti i dati vengono raccolti nella memoria dal driver.

Attenzione: se le query restituiscono un grande numero di record le impostazioni di default provocano una forte allocazione di memoria sul driver JDBC e questo puo' generare grossi problemi arrivando fino al blocco del processo. Per evitarlo e' necessario impostare sia un fetchSize che impostare l'autoCommit a false.

Il defaultRowFetchSize fa parte dei molti (oltre 70) Connection Parameters disponibili con PgJDBC e puo' essere passato come opzione nell'URL di connessione; il default e' 0 che fa porre in cache l'intero risultato della query. L'autoCommit invece deve essere impostato come proprieta' della connessione e quindi l'impostazione richiede una riga di codice; il default e' on e questa impostazione non consente di mantenere i cursori quindi invalida un'eventuale impostazione del defaultRowFetchSize. Per tale motivo vanno impostati entrambe i parametri.

Molti framework Java prevedono un'impostazione di default differente di entrambe i parametri, ma e' sempre opportuna una verifica [NdA motori di database diversi hanno impostazioni di default JDBC completamente differenti da PostgreSQL]. L'effettuazione del commit al momento corretto ha un'importanza notevole per la gestione dei dati ed un opportuno fetchsize e' molto importante per le prestazioni.

Vediamo ora i parametri relativi ai Prepared Statement che possono essere passati nella stringa di connessione JDBC o definiti come proprieta':

Quindi i Client-Side Prepared Statement in realta' vengono promossi (con le impostazioni di default) a Server-Side Prepared Statements dopo 5 richiami! E' possibile impedire la promozione a Server-Side impostando prepareThreshold a 0 oppure renderla immediata impostando prepareThreshold a -1.
Nella maggior parte dei casi la configurazione di default dei prepared statement e' corretta e non richiede modifiche perche' evita la creazione di Server-Side Prepared Statement per comandi eseguiti poche volte.

Un ulteriore vantaggio dei prepared statement, non collegato alle performance ma comunque importante, e' che impediscono le SQL-injection.

Riassumendo i Client-Side Prepared Statement presentano vantaggi prestazionali perche' le allocazioni di memoria avvengono una sola volta per singolo statement e viene mantenuta una cache locale sul client.

Server-Side Prepared Statements

PostgreSQL supporta i Prepared Statement dalla versione 7.3 (2002-11). Si tratta di un semplice costrutto SQL che puo' essere richiamato da qualsiasi client o API con accesso a PostgreSQL. E' la tecnica piu' efficiente quando vengono utilizzati gli stessi statement con variabili (bind) che hanno differenti valori forniti ogni volta. In questo modo i tempi di parsing dello statement vengono ridotti al minimo.

I prepared statement vengono creati con la clausola PREPARE ed eseguiti con la clausola EXECUTE. Un prepared statement e' valido solo per la sessione corrente e non puo' essere acceduto da altre sessioni. La cosa piu' semplice e' vedere un esempio:

La vista di sistema pg_prepared_statements riporta l'elenco di tutti i prepared statement presenti nella sessione corrente.

Dal punto di vista tecnico l'esecuzione di uno statement SQL in PostgreSQL richiede diversi passi come riportato nella figura a destra.
Le fasi sono molto simili a quelli di altri database relazionali e partono dalla parsificazione della stringa del comando. La fase di rewriter in PostgreSQL e' utilizzata per la gestione delle viste che sono implementate con un sistema di regole. Naturalmente la fase finale e' quella esecuzione dello statement.
Quando si utilizzano i prepared statement la PREPARE esegue il parsing, l'analyze ed il rewrite; l'EXECUTE esegue il planning e l'execute.
Naturalmente e' consigliabile utilizzare un prepared statement quando lo stesso comando viene ripetuto piu' volte con parametri diversi. Il vantaggio e' significativo solo per statement eseguiti un grande numero di volte perche' la parte di planning puo' essere ulteriormente ottimizzata. In questo modo i tempi totali di esecuzione risultano piu' veloci ed e' ridotto l'utilizzo di CPU.

Un prepared statement puo' essere eseguito con un generic plan o con un custom plan. Il piano generic e' lo stesso per tutte le esecuzioni mentre quello custom puo' variare a seconda del valore delle variabili di bind.
Con le versioni piu' recenti di PostgreSQL, nella configurazione di default (plan_cache_mode=auto) inizialmente viene utilizzato un custom plan per poi passare ad un generic plan se i costi sono simili. Tale comportamento pero' puo' essere configurato con questo parametro:

Si tratta di un parametro con context user quindi impostabile in qualsiasi momento con il comando di SET.

Se la distribuzione dei dati e' uniforme non c'e' problema: il piano e' gia' quello ottimale e non viene ricalcolato consentendo un risparmio di CPU. Ma se la distribuzione dei dati non e' uniforme la query potrebbe avere tempi di risposta molto alti quando viene scelto un piano d'esecuzione sbagliato. Una prima possibilita' e' quella di non passare le variabili in bind ma inserirli nella stringa dello statement come literal, si tratta di una scelta "robusta" che non presenta problemi ma generalmente si preferisce trattare tutte le variabili come parametri dello statement.

Attenzione: se la distribuzione dei dati per le variabili in bind usate nelle condizioni non e' uniforme e' fortemente consigliabile utilizzare l'impostazione SET plan_cache_mode=force_custom_plan in questo modo viene rieseguito il planning per cercare il piano di esecuzione migliore ad ogni attivazione senza utilizzare un generic plan.

L'analisi degli execution plan in PostgreSQL si effettua con la clausola EXPLAIN ma non e' semplice in PostgreSQL determinare a posteriori i plan utilizzati [NdA e' tuttavia possibile, con tutte le avvertenze del caso, utilizzare l'estensione auto_explain].

Client-Side + Server-Side Prepared Statements

Abbiamo visto nei paragrafi precedenti i Client-Side ed i Server-Side Prepared statement. Quando da Java i Client-Side Prepared Statement attivano i Server-Side Prepared Statement entrambe vengono sfruttati contemporaneamente con ulteriori vantaggi prestazionali:

• Ad ogni EXECUTE viene tramesso solo lo statement handle anziche' l'intera stringa dello statement SQL
• Viene abilitato il binary transfer del contenuto delle variabili (e.g. binary int4, binary timestamps)
• Puo' essere riutilizzato lo stesso esecution plan
• La definizione delle colonne viene mantenuta e quindi non e' necessario ricevere e parsificare i metadata ad ogni esecuzione

I vantaggi prestazionali nell'uso dei prepared statement in PostgreSQL con JDBC sono significativi. Per utilizzarli al meglio e' importante conoscere ed evitare i possibili problemi che abbiamo visto nei paragrafi precedenti.

Esempio

Si sa che la gente da' buoni consigli... se non puo' piu' dare il cattivo esempio.

Ma vediamo comunque un semplice esempio di utilizzo del driver JDBC di Postgres. Vogliamo leggere i dati da una tabella di grosse dimensioni [NdA la base dati e' stata creata con pgbench utilizzando scale=100 e contiene 10.000.000 di clienti] eseguendo alcune migliaia di accessi puntuali usando codici casuali. Per il caso d'uso scelto alcune delle impostazioni suggerite in precedenza non sono utili ma le lasciamo indicate nei commenti in modo che sia chiaro come possono essere impostate.

import java.sql.*;
import java.util.Random;

public class DemoPS {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:postgresql://localhost:5432/pgbench";
        String username = "pgbench";
        String password = "xxx";
        Integer loop = 10000;
        Integer total = 0;
        Integer i = 0;
        Random rnd = new Random();
        try {
            System.out.println("Timestamp " + new java.sql.Timestamp(System.currentTimeMillis()));
            Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
            conn.setAutoCommit(false);
            PreparedStatement stmt = conn.prepareStatement("SELECT abalance FROM pgbench_accounts WHERE aid = ?");
            // stmt.setFetchSize(100);
            while (i < loop) {
                stmt.setInt(1, rnd.nextInt(10000000) + 1);
                ResultSet rs = stmt.executeQuery();
                while (rs.next()) {
                    total += rs.getInt("abalance");
                }
                rs.close();
                i++;
            }
            System.out.println("Total balance: " + total);
            System.out.println("Timestamp " + new java.sql.Timestamp(System.currentTimeMillis()));
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

I tempi? Con questo esempio molto semplice le latenze si misurano in nanosecondi...

Per valutare i risparmi in termini di analisi degli execution plan va modificata la configurazione di default di pg_stat_statements per effettuare il track_planning [NdA ma generalmente non e' consigliabile perche' puo' essere pesante]. Ecco la differenza utilizzando o meno i generic plan:

I tempi sono comunque molto bassi e variabili quindi vanno valutati se necessario con gli statement SQL reali dell'applicazione.

In conclusione... dal punto di vista prestazionale e' sicuramente importante evitare errori (eg. datatype, OOM, wrong plan) ma e' possibile ottimizzare al massimo gli accessi alla base dati con i Prepared Statement di PostgreSQL via JDBC.

Varie ed eventuali

Altre paginette sull'utilizzo del JDBC con PostreSQL sono: Utilizzare PostgreSQL JDBC senza OOM (Out Of Memory), Utilizzare PostgreSQL JDBC - slow?!, ...
Mentre per qualche indicazione sull'SQL puo' essere utile Ottimizzazione SQL in PostgreSQL.

Le fonti definitive per le informazioni sui prepared statement sono i siti ufficiali: PostgreSQL, PgJDBC e Java.

Titolo: Raccolta dati con PostgreSQL JDBC - Prepared Statement
Livello: Avanzato (3/5)
Data: 14 Febbraio 2024 ❤️
Versione: 1.0.1 - 31 Ottobre 2024 🎃
Autore: mail [AT] meo.bogliolo.name