Re: Bytea vs. BLOB (what's the motivation behind the former?)

Frank Joerdens wrote:
> Leading up to 7.2 there was quite some noise on both pgsql-general and
> hackers regarding the new bytea type. It *appears* that this is now the
> recommended choice for binary data over BLOBs. I didn't manage to dig up
> an explanation though why bytea would be better than BLOB - besides that
> the interface to deal with large objects is somewhat more convoluted.
>
> Is this all about the cleaner interface? I also saw that bytea is a
> proprietary Postgres type, a replacement(?) for the SQL99 BLOB type.
> Does this mean that bytea will eventually supersede the BLOB type in
> Postgres? Is bytea faster?
>
> This probably has all been explained before somewhere but I dug through the
> archives till about mid-2001 and couldn't find a clear explanation.
>

Bytea is not actually new, but it was poorly documented and lacked a
good bit of functionality before 7.2. My take is that bytea is much
closer to SQL99 BLOB than PostgreSQL large objects. See table 3.9 at:
http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/datatype-binary.html

Also see function and operator support at:
http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/functions-binarystring.html

Each type (bytea and LO) has its own advantages. For instance, bytea
manipulates the entire string in memory (up to 4 copies from what I
understand), which may be a problem if your data is very large (I've
only tried to store 10s of MB in bytea fields myself). But I do find the
LO interface a bit more convoluted and prefer the bytea approach myself.

I haven't done any testing wrt speed, but would be very interested in
hearing from others if they have.

Hope this helps,

Joe