Re: bytea columns and large values

On 29/09/11 14:55, Merlin Moncure wrote:
> On Thu, Sep 29, 2011 at 6:12 AM, Radosław Smogura
> <rsmogura(at)softperience(dot)eu> wrote:
>> On Wed, 28 Sep 2011 10:18:27 -0500, Merlin Moncure wrote:
>>> sending ~1GB bytea values is borderline crazy, and is completely crazy
>>> if you are not absolutely sure the transmission is not 100% binary. I
>>> don't know if the JDBC sends/receives bytea as binary, but it may not.
>>> If not, you might have better luck with the large object interface.
> hm, I had one extra 'not' in there of course I meant to say you should
> be sure data is transferred in binary (I think everyone knew that
> though).
>
>> You have exposed such interface, and you firm it. Does this crazynies is
>> measured by fixed amount above 500MB or it's fuzzy measured with standard
>> deviation near 1GB? If I use bytea to store such values, looks I'm crazy
>> too. Sorry, for thinking that documentation is trust worthy.
>>
>> Thanks for mobilization - I reserved moderngres domains. I think it's about
>> 1-2 week for making some infrastructure for this.
> The interface (well, libpq) and the protocol are in fact part of the
> problem. To truly support large bytea means streaming features, new
> extensions to libpq, maybe some protocol modifications. There have
> been a couple of semi-serious attempts at dealing with this problem
> (see archives), but none so far have gains serious traction.
>
> The lo interface sucks but it's slightly better on resources for
> really huge bytea and tends to be more consistently implemented in
> database drivers. If I was doing this, I would of course be crafting
> a carefully generated client in C, using libpqtypes, which is the gold
> standard for sending bytea against which all others should be judged.

Thanks everyone for your replies.

For my use-case, I'm going to break down the data into smaller lumps and
store one per row (it's actually a bunch of serialized Java objects, so
putting them all in one field of one row was rather lazy in the first
place).

I'll also apply GZip to keep the amount of data in any given bytea/lob
as small as possible.

David

--
David North, Software Developer, CoreFiling Limited
http://www.corefiling.com
Phone: +44-1865-203192