Bytea, ASCII-only encoding and pg_dumpall (Was: Bytea vs. BLOB)

On Thu, Mar 28, 2002 at 07:00:30AM -0800, Joe Conway wrote:
> Frank Joerdens wrote:
[ . . . ]
> 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

Ah. I hadn't seen that. Doesn't that fact that you can encode the entire
binary string to ASCII-only mean then that you don't have to rely on the
-b option with pg_dump, i.e. that you can dump to text files? This would
mean that we'd have a very clear advantage over LO in that you can use
pg_dumpall again to backup your entire server (pg_dumpall can't deal with
blobs because it needs to create text output which is then
concatenated). What I don't understand yet is if that also applies if
you don't use ASCII-only encoding (how is data representet internally
without it?) and when you'd decide to encode or not, or if the fact that
you can dump to text would be sufficient grounds to decide to encode
everything bytea to ASCII-only.

>
> Each type (bytea and LO) has its own advantages. For instance, bytea
> manipulates the entire string in memory

This probably means that it's faster for data that fits into physical
memory at least because the OS, and Postgres, can do they their caching
properly (I would presume that any sort of caching mechanism is voided
if the app requires data to be written to disk straight away while it's
fed into it, as it appears to be the case with LOs).

The approach sounds more friendly on the whole (provided the data
doesn't get too large, as you mention), so I'll give it a spin.

Regards, Frank