[GENERAL] 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 , the standard stream-style access method ru

Re: [GENERAL] SUBSTRING performance for large BYTEA

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 p

Re: [GENERAL] SUBSTRING performance for large BYTEA

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 f

Re: [GENERAL] SUBSTRING performance for large BYTEA

"Karsten Hilbert" <[EMAIL PROTECTED]> 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 e

Re: [GENERAL] SUBSTRING performance for large BYTEA

On Sat, Aug 18, 2007 at 01:51:18PM -0400, Tom Lane wrote: > Karsten Hilbert <[EMAIL PROTECTED]> writes: > > Would it be feasible to add an ALTER TABLE mode > > ... set storage externally-extended cutoff ... > > where is the user configurable size of the column > > data at which PostgreSQL sw

Re: [GENERAL] SUBSTRING performance for large BYTEA

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

Re: [GENERAL] SUBSTRING performance for large BYTEA

"Joshua D. Drake" <[EMAIL PROTECTED]> 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 cl

Re: [GENERAL] SUBSTRING performance for large BYTEA

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 > >

Re: [GENERAL] SUBSTRING performance for large BYTEA

Karsten Hilbert <[EMAIL PROTECTED]> writes: > Would it be feasible to add an ALTER TABLE mode > ... set storage externally-extended cutoff ... > where is the user configurable size of the column > data at which PostgreSQL switches from extended to external > storage strategy ? Actually, it

Re: [GENERAL] SUBSTRING performance for large BYTEA

On Sat, Aug 18, 2007 at 12:49:09PM -0400, Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> 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

Re: [GENERAL] SUBSTRING performance for large BYTEA

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karsten Hilbert wrote: > On Sat, Aug 18, 2007 at 12:20:42PM -0400, Tom Lane wrote: > >> "Vance Maverick" <[EMAIL PROTECTED]> writes: >>> My question is about performance in the postgres server. When I execute >>> "SELECT SUBSTRING (my_bytea FROM ? FO

Re: [GENERAL] SUBSTRING performance for large BYTEA

On Sat, Aug 18, 2007 at 12:20:42PM -0400, Tom Lane wrote: > "Vance Maverick" <[EMAIL PROTECTED]> 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

Re: [GENERAL] SUBSTRING performance for large BYTEA

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> 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-num

Re: [GENERAL] SUBSTRING performance for large BYTEA

"Joshua D. Drake" <[EMAIL PROTECTED]> 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 t

Re: [GENERAL] SUBSTRING performance for large BYTEA

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: > "Vance Maverick" <[EMAIL PROTECTED]> 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

Re: [GENERAL] SUBSTRING performance for large BYTEA

"Vance Maverick" <[EMAIL PROTECTED]> 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 reques

[GENERAL] 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 , the standard stream-style access method ru