Utilizzare PostgreSQL JDBC
-evitando OOM-

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' molto robusto ed affidabile, pero' spesso vengono lamentati problemi di utilizzo eccessivo di memoria accedendo a tabelle di grandi dimensioni...
In realta' il driver funziona perfettamente ed il suo comportamento e' esattamente quello documentato ma c'e' un'impostazione che se non viene effettuata correttamente puo' causare problemi di uso eccessivo di memoria come l'OOM (Out Of Memory).

In questa paginetta cercheremo di vedere un esempio di corretto utilizzo del driver JDBC di PostgreSQL per raccogliere dati da tabelle di grandi dimensioni.

Esempio

Vediamo un semplice esempio di utilizzo del driver JDBC di Postgres. Vogliamo leggere i dati da una tabella di grosse dimensioni:

import java.sql.*; public class Dimmelo3 { public static void main(String[] args) { String jdbcUrl = "jdbc:postgresql://localhost:5432/postgres"; String username = "postgres"; String password = "xxx"; try { Connection connection = DriverManager.getConnection(jdbcUrl, username, password); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT generate_series(1,10000000) as val"); System.out.println("KB: " + (double)(Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory()) / 1024); while (resultSet.next()) { int columnValue = resultSet.getInt("val"); System.out.println("Column Value: " + columnValue); if (columnValue == 3) break; } connection.close(); } catch (SQLException e) { e.printStackTrace(); } } }

L'esempio presentato sopra funziona correttamente ma...

$ java Dimmelo3 Memory KB: 929770.0 Column Value: 1 Column Value: 2 Column Value: 3

ma... e' sbagliato!

Il problema e' che l'intero contenuto della tabella viene caricato in memoria quando viene effettata l'executeQuery. Anche se con 10M di record riporta i dati corretti la sua esecuzione non e' efficiente. La memoria utilizzata viene visualizza con il println ed e' oggettivamente troppo elevata.
Aumentando il numero di record della tabella [NdA ad esempio con 1G come valore per la generate_series] si otterra' un errore come: 

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2390)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:356)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:341)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:317)
	at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:266)
	at Dimmelo3.main(Dimmelo3.java:40)

Il comportamento del driver e' esattamente quello riportato nella documentazione ufficiale [NdE in evidenza la parte piu' significativa]:

By default the driver collects all the results for the query at once. This can be inconvenient for large data sets so the JDBC driver provides a means of basing a ResultSet on a database cursor and only fetching a small number of rows.

Il modo corretto con i driver JDBC di PostgreSQL per raccogliere i dati con un cursore da una tabella di grandi dimensioni e' quello di definire una fetchSize opportuna (eg. setFetchSize(1000)) e disattivare l'autoCommit (setAutoCommit(false)). Servono entrambe le impostazioni perche' per default il fetchSize e' a 0, ovvero nessun limite; mentre per default l'autoCommit e' abilitato, ovvero vengono chiuse le transazioni ed cursori ad ogni statement, invalidando l'eventuale impostazione del fetchSize.
Le impostazioni possono essere effettuate in modi diversi, sia nel codice Java che nelle proprieta' della connessione.

import java.sql.*; public class Dimmelo3 { public static void main(String[] args) { String jdbcUrl = "jdbc:postgresql://localhost:5432/postgres"; String username = "postgres"; String password = "xxx"; try { Connection connection = DriverManager.getConnection(jdbcUrl, username, password); connection.setAutoCommit(false); Statement statement = connection.createStatement(); statement.setFetchSize(100); ResultSet resultSet = statement.executeQuery("SELECT generate_series(1,10000000) as val"); System.out.println("KB: " + (double)(Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory()) / 1024); while (resultSet.next()) { int columnValue = resultSet.getInt("val"); System.out.println("Column Value: " + columnValue); if (columnValue == 3) break; } connection.close(); } catch (SQLException e) { e.printStackTrace(); } } }

Con queste impostazioni l'esempio funziona correttamente e, soprattuto, non alloca l'intero risultato della query in memoria ma solo quanto definito nella fetchSize.
Entrambe le impostazioni sono necessarie perche' se l'autoCommit e' a true (che e' il default) il fetchSize viene reimpostato a 0. Naturalmente e' possibile modificare l'esempio commentando l'impostazione del setAutoCommit(false) o del setFetchSize(), utilizzando una query fittizia con piu' colonne o una query reale, ... e verificando ogni volta l'allocazione effettiva di memoria.

Per effettuare caricamenti massivi tipicamente si imposta un fetchSize di grandi dimensioni; naturalmente questo richiede piu' memoria ed e' opportuno effettuare qualche prova per valutare l'effettivo vantaggio.

E' possibile impostare il fetchSize con la proprieta' JDBC defaultRowFetchSize ma non e' disponibile in pgJDBC una proprieta' per impostare ad off l'autoCommit nell'URI JDBC. Per impostare il fetchsize e' anche possibile utilizzare il piu' recente e flessibile adaptiveFetch che tuttavia per default e' disabilitato.

Performance

Per tabelle di piccole dimensioni le impostazioni di default sono quelle che danno le migliori prestazioni.
Per tabelle di grandi dimensioni l'allocazione della memoria nella JVM puo' diventare molto pesante portando a rallentamenti, ad errori, all'attivazione dell'OOM Killer, ... Se il richiamo avviene in un framework puo' essere difficile individuare la causa del problema e comprendere dove effettuare le impostazioni corrette.

Ovviamente il problema dell'occupazione della memoria puo' essere aggirato utilizzando le clausole SQL standard LIMIT ed OFFSET. Ma si tratta di un workaround: in Postgres l'utilizzo della clausola OFFSET generalmente non e' efficiente dal punto di vista prestazionale. La soluzione corretta e' quella descritta che prevede l'impostazione di entrambe autoCommit e fetchSize. Il fetchSize puo' essere dimensionato anche elevato per esecuzioni batch: si ha maggior consumo di memoria ma un numero inferiore di fetch eseguite.

Varie ed eventuali

Tecnicamente si tratta di componenti differenti dal JDBC ma anche il client psql raccoglie tutti i record risultanti da una query, per default. Anche in questo caso l'impostazione per evitarlo e' semplice: impostare il parametro psql FETCH_COUNT. Non ci credete? Provate! 

\timing
select * from generate_series(1,10000000); 

\set FETCH_COUNT 100
select * from generate_series(1,10000000);

Altre paginette sull'utilizzo del JDBC con PostreSQL sono: Utilizzare PostgreSQL JDBC - Prepared Statements, Utilizzare PostgreSQL JDBC - Slow ?!

Le fonti definitive di tutte le informazioni sono il sito ufficiale JDBC PostgreSQL e GitHub.

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