Re: SUBSTRING performance for large BYTEA

I'm working on reading large BYTEA fields from PostgreSQL 8.1. (For
legacy reasons, it's unattractive to move them to large objects.) I'm
using JDBC, and as various people have pointed out
<http://archives.postgresql.org/pgsql-jdbc/2005-06/msg00138.php>, the
standard stream-style access method runs out of memory for large BYTEAs.

Karsten Hilbert mentions using SUBSTRING to read these BYTEA fields a
chunk at a time
<http://archives.postgresql.org/pgsql-general/2005-01/msg00032.php>.
I've tried this, and indeed it works. (Once I corrected for the 1-based
indexing ;-))

My question is about performance in the postgres server. When I execute
"SELECT SUBSTRING (my_bytea FROM ? FOR ?) FROM my_table WHERE id = ?",
does it fetch the whole BYTEA into memory? Or does it access only the
pages that contain the requested substring?

Vance

"Vance Maverick" <vmaverick(at)pgp(dot)com> writes:
> My question is about performance in the postgres server. When I execute
> "SELECT SUBSTRING (my_bytea FROM ? FOR ?) FROM my_table WHERE id =3D ?",
> does it fetch the whole BYTEA into memory? Or does it access only the
> pages that contain the requested substring?

Recent releases will do what you want if the column has been marked
SET STORAGE EXTERNAL (before storing anything in it...) See the
ALTER TABLE reference page.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
> "Vance Maverick" <vmaverick(at)pgp(dot)com> writes:
>> My question is about performance in the postgres server. When I execute
>> "SELECT SUBSTRING (my_bytea FROM ? FOR ?) FROM my_table WHERE id =3D ?",
>> does it fetch the whole BYTEA into memory? Or does it access only the
>> pages that contain the requested substring?
>
> Recent releases will do what you want if the column has been marked
> SET STORAGE EXTERNAL (before storing anything in it...) See the
> ALTER TABLE reference page.

Should we consider setting storage external by default for the type?

Joshua D. Drake

>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGxx8CATb/zqfZUUQRAkXkAJ4i6GKe7/v8dHOaj8fjTvc2hZZN4wCgknFQ
VVaj655AAbKFipfFNcAbdos=
=jdgD
-----END PGP SIGNATURE-----

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> Should we consider setting storage external by default for the type?

No. That would be counterproductive for the more typical case of bytea
values in the range of some-small-number-of-kilobytes. Or at least
I think that's more typical than values that are so large you have to go
out of your way to fetch them in chunks.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>> Should we consider setting storage external by default for the type?
>
> No. That would be counterproductive for the more typical case of bytea
> values in the range of some-small-number-of-kilobytes. Or at least
> I think that's more typical than values that are so large you have to go
> out of your way to fetch them in chunks.

The typical case that I run into with Bytea is storing enough
information to where this would be useful. Specifically items such as
pdf and .doc.

Sincerely,

Joshua D. Drake

>
> regards, tom lane
>

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGxyYLATb/zqfZUUQRAq5gAJsGFIHglJGcGjqjNc92G6Wt2U+cwQCghGMV
181pA78JUFIfpepzXLY1eK0=
=GJGH
-----END PGP SIGNATURE-----

On Sat, Aug 18, 2007 at 12:20:42PM -0400, Tom Lane wrote:

> "Vance Maverick" <vmaverick(at)pgp(dot)com> writes:
> > My question is about performance in the postgres server. When I execute
> > "SELECT SUBSTRING (my_bytea FROM ? FOR ?) FROM my_table WHERE id =3D ?",
> > does it fetch the whole BYTEA into memory? Or does it access only the
> > pages that contain the requested substring?
>
> Recent releases will do what you want if the column has been marked
> SET STORAGE EXTERNAL (before storing anything in it...) See the
> ALTER TABLE reference page.
Ah, thanks, good to know !

"Recent releases" seems to mean at least as far back as 8.1
going by the docs.

Now, to convert an existing bytea column I would need to add
a new bytea column with "set storage external", move the
data from the old column to the new column, remove the old
column, and give the new column the original name, correct ?

Or is the an easier way ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Karsten Hilbert wrote:
> On Sat, Aug 18, 2007 at 12:20:42PM -0400, Tom Lane wrote:
>
>> "Vance Maverick" <vmaverick(at)pgp(dot)com> writes:
>>> My question is about performance in the postgres server. When I execute
>>> "SELECT SUBSTRING (my_bytea FROM ? FOR ?) FROM my_table WHERE id =3D ?",
>>> does it fetch the whole BYTEA into memory? Or does it access only the
>>> pages that contain the requested substring?
>> Recent releases will do what you want if the column has been marked
>> SET STORAGE EXTERNAL (before storing anything in it...) See the
>> ALTER TABLE reference page.
> Ah, thanks, good to know !
>
> "Recent releases" seems to mean at least as far back as 8.1
> going by the docs.
>
> Now, to convert an existing bytea column I would need to add
> a new bytea column with "set storage external", move the
> data from the old column to the new column, remove the old
> column, and give the new column the original name, correct ?
>
> Or is the an easier way ?

Well this is a guess, but:

Set existing column to storage external
update existing column with existing data:

UPDATE foo SET bar = bar;

Now the down side to this is you are going to create a dead row for
every update which means a vacuum (probably full) afterward, but the way
you describe above will do the same thing as well.

Sincerely,

Joshua D. Drake

>
> Karsten

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGxyseATb/zqfZUUQRAn7AAJ9jzhsOb8xoy9QWoI6yfNV4cO9Z3gCeJG6W
n3Z0uaYp5d6QGoFP3O8QJUI=
=fIqx
-----END PGP SIGNATURE-----

On Sat, Aug 18, 2007 at 12:49:09PM -0400, Tom Lane wrote:

> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > Should we consider setting storage external by default for the type?
>
> No. That would be counterproductive for the more typical case of bytea
> values in the range of some-small-number-of-kilobytes. Or at least
> I think that's more typical than values that are so large you have to go
> out of your way to fetch them in chunks.

Would it be feasible to add an ALTER TABLE mode

... set storage externally-extended cutoff <size> ...

where <size> is the user configurable size of the column
data at which PostgreSQL switches from extended to external
storage strategy ?

Such that large bytea values would be chunkable while
smaller ones wouldn't at the discretion of the DBA.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> Would it be feasible to add an ALTER TABLE mode
> ... set storage externally-extended cutoff <size> ...
> where <size> is the user configurable size of the column
> data at which PostgreSQL switches from extended to external
> storage strategy ?

Actually, it just occurred to me that this ties into the recent
discussion of compression parameters
http://archives.postgresql.org/pgsql-hackers/2007-08/msg00082.php
(which hasn't gone further than discussion yet). Perhaps we need
an additional parameter which is a maximum input size to attempt
compression at all. IOW, the current force_input_size is not
only useless but exactly backwards ...

There was some discussion in that thread (or maybe the earlier
one on -patches) of exposing the lzcompress parameters directly
to users, perhaps as an extended form of the current SET STORAGE
command. That won't happen for 8.3 but it might later. In the
meantime, if the defaults included not attempting to compress
multi-megabyte values, I think it'd Just Work for cases like
yours.

regards, tom lane

On Sat, Aug 18, 2007 at 10:23:42AM -0700, Joshua D. Drake wrote:

> >> SET STORAGE EXTERNAL (before storing anything in it...) See the
> >> ALTER TABLE reference page.

> > Now, to convert an existing bytea column I would need to add
> > a new bytea column with "set storage external", move the
> > data from the old column to the new column, remove the old
> > column, and give the new column the original name, correct ?
>
> Set existing column to storage external
> update existing column with existing data:
>
> UPDATE foo SET bar = bar;
>
> Now the down side to this is you are going to create a dead row for
> every update which means a vacuum (probably full) afterward, but the way
> you describe above will do the same thing as well.
Sure.

I was a bit uneasy about the docs saying

"set storage doesn't affect existing data but only sets the
strategy on new inserts/updates"

and hence thought using a wholy new column would somehow be
safer. But maybe this can be nefariously interpreted such
that I could sort-of implement cutoff-based
extended/external switching by prepending "alter table ...
set storage external/extended ..." to INSERTs/UPDATEs based
on bytea parameter size. Or even writing a trigger issuing
ALTER TABLE depending on size of insert ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:

> Well this is a guess, but:
>
> Set existing column to storage external
> update existing column with existing data:
>
> UPDATE foo SET bar = bar;

Well, not quite. That would actually reuse the toast pointer without
decompressing it. We try to be clever about not decompressing and duplicating
toast pointers unnecessarily on updates -- in this case too clever.

You could do this:

postgres=# ALTER TABLE foo ALTER bar TYPE bytea, ALTER bar SET STORAGE external;
ALTER TABLE

(Note that you have to include the 'ALTER bar SET STORAGE external' in the
same command or the storage will get reset to the default 'extended' for bytea
even if it was previously set to 'external'.)

When I tested this though I noticed it did *not* decompress compressed data
which was small enough to store internally. This may actually be desirable for
your case since anything small enough to be stored internally is probably not
worth bothering decompressing so it can be streamed out. It will still not be
compressed next time you update it so it's not really helpful for the long
term.

If you want to decompress everything you have to do something like:

postgres=# ALTER TABLE foo ALTER bar TYPE bytea USING t||'', ALTER bar SET STORAGE external;
ALTER TABLE

However note that this will require extra memory for both the decompressed
original value and the new value after "appending" the empty string.

Another option would be to update only the records which need to be
decompressed with something like

UPDATE foo SET bar=bar||'' WHERE length(bar) > pg_column_size(bar)

This at least gives you the option of doing them in small groups or even one
by one. I would suggest vacuuming between each update.

I do have to wonder how you're getting the data *in* though. If it's large
enough to have to stream out like this then how do you initially load the
data?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

On Sat, Aug 18, 2007 at 09:32:33PM +0100, Gregory Stark wrote:

> I do have to wonder how you're getting the data *in* though. If it's large
> enough to have to stream out like this then how do you initially load the
> data?
Well, in my particular case it isn't so much that I *want*
to access bytea in chunks but rather that under certain
not-yet-pinned-down circumstances windows clients tend to go
out-or-memory on the socket during *retrieval* (insertion is
fine, as is put/get access from Linux clients). Doing
chunked retrieval works on those boxen, too, so it's an
option in our application (the user defines a chunk size
that works, a size of 0 is treated as no-chunking).

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On Sat, Aug 18, 2007 at 01:51:18PM -0400, Tom Lane wrote:

> Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> > Would it be feasible to add an ALTER TABLE mode
> > ... set storage externally-extended cutoff <size> ...
> > where <size> is the user configurable size of the column
> > data at which PostgreSQL switches from extended to external
> > storage strategy ?
>
> Actually, it just occurred to me that this ties into the recent
> discussion of compression parameters
> http://archives.postgresql.org/pgsql-hackers/2007-08/msg00082.php
> (which hasn't gone further than discussion yet). Perhaps we need
> an additional parameter which is a maximum input size to attempt
> compression at all. IOW, the current force_input_size is not
> only useless but exactly backwards ...

I can see that a maximum size can be relevant for the
decision as to whether to *attempt* compression since large
things compress slowly and may unduly slow down queries.

As well as a minimum size to use compression on, quite
obviously.

OTOH, I'd like to be able to tell PostgreSQL to be so kind
and refrain from attempting to compress values above a
certain size even if it thought it'd make sense.

> There was some discussion in that thread (or maybe the earlier
> one on -patches) of exposing the lzcompress parameters directly
> to users, perhaps as an extended form of the current SET STORAGE
> command. That won't happen for 8.3 but it might later. In the
Sounds good.

> meantime, if the defaults included not attempting to compress
> multi-megabyte values, I think it'd Just Work for cases like
> yours.
Not as tweakable as I'd eventually want it but, yes, that
would sort of Just Work.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

"Karsten Hilbert" <Karsten(dot)Hilbert(at)gmx(dot)net> writes:

> But maybe this can be nefariously interpreted such that I could sort-of
> implement cutoff-based extended/external switching by prepending "alter
> table ... set storage external/extended ..." to INSERTs/UPDATEs based on
> bytea parameter size. Or even writing a trigger issuing ALTER TABLE
> depending on size of insert ?

I wouldn't suggest doing that. It will bloat the pg_attribute catalog table
and require a lot of extra vacuums. I think it would also create some lock
contention issues.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Karsten Hilbert writes:
> Well, in my particular case it isn't so much that I *want*
> to access bytea in chunks but rather that under certain
> not-yet-pinned-down circumstances windows clients tend to go
> out-or-memory on the socket during *retrieval* (insertion is
> fine, as is put/get access from Linux clients). Doing
> chunked retrieval works on those boxen, too, so it's an
> option in our application (the user defines a chunk size
> that works, a size of 0 is treated as no-chunking).

This is my experience with a Java client too. Writing the data with
PreparedStatement.setBinaryStream works great for long strings, but
reading it with the complementary method ResultSet.getBinaryStream runs
into the memory problem, killing the Java VM.

Thanks to all for the useful feedback. I'm going to post a note to the
JDBC list as well to make this easier to find in the future.

Vance

On Sat, Aug 18, 2007 at 10:54:11PM -0700, Vance Maverick wrote:

> This is my experience with a Java client too. Writing the data with
> PreparedStatement.setBinaryStream works great for long strings, but
> reading it with the complementary method ResultSet.getBinaryStream runs
> into the memory problem, killing the Java VM.
Again, I am observing this under Python with a libpq-based driver
running on Windows and during retrieval only.

Are we seeing a pattern ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346