I'm working on reading large BYTEA fields from PostgreSQL 8.1. As
various people have pointed out
<http://archives.postgresql.org/pgsql-jdbc/2005-06/msg00138.php>,
ResultSet.getBinaryStream runs out of memory for large BYTEAs.
(PreparedStatement.setBinaryStream works fine.)
But there's a workaround: use the SUBSTRING function to read only a
chunk at a time, e.g.
SELECT SUBSTRING(my_field FROM 1 FOR 10000) FROM my_table WHERE ...
This was suggested by Karsten Hilbert
<http://archives.postgresql.org/pgsql-general/2005-01/msg00032.php>.
I've had no trouble getting this to work -- as it happens, the chunkwise
reading strategy is consistent with what I was doing in the surrounding
code anyway.
In discussion today
<http://archives.postgresql.org/pgsql-general/2007-08/msg01127.php>, Tom
Lane pointed out that
Recent releases will [perform better] if the column has been marked
SET STORAGE EXTERNAL (before storing anything in it...) See the
ALTER TABLE reference page.
I tried this, and with the test sizes I was running (15 Mb data, 250K
chunk), the difference was not significant. Reading was a bit faster,
but writing was considerably slower, adding up to a wash. Your mileage
may vary.
Vance