Re: bytea columns and large values

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.

merlin