>> Huh ??? isn't that point of using bytea or text datatypes.
>>
>> I could have sworn bytea does not use large object interface it uses
>> TOAST or have i gone insane
>>
>>
> You're not insane :)
>
> Put it another way: bytea values are not stored in the pg_largeobject
> catalog.
>
I missed the part that BYTEA was being used since it's generally not a
good way for starting large binary data because you are right that BYTEA
requires escaping across the wire (client to backend) both directions,
which for true binary data (like compressed/encrypted data, images or
other non-text files) makes for a lot of expansion in size and related
memory.
BYTEA and TEXT both can store up to 1GB of data (max field length),
which means even less "file size" supported if you use TEXT with base64
coding. LO supports 2GB of data. In JDBC, typically BYTEA is used with
byte[] or binary stream while LOs with BLOB. I think LOs allow for
streaming with the backend, too, but not sure about that, whereas I'm
pretty sure BYTEA/TEXT move all the data together you it will be in
memory all or nothing.
Of course, to support larger file storage than 1GB or 2GB, you'll have
to create your own "toast" like capability to split them into multiple rows.
David