Re: Bytea/Large Objects/external files best practices

David Helgason <david(at)uti(dot)is> writes:
> *) I seem to remember reading in a discussion in the [hackers] list
> that the TOAST column substring operation had been optimized so that
> partial fetches might be viable.

I believe that works now for both text and bytea columns. However the
cost is that you have to store the data uncompressed (by disabling the
normal automatic compression, see ALTER COLUMN SET STORAGE). Pick
your poison...

> open questions: I wonder what timeframe for this to be viable. It
> seems that two things are being worked on that would fix this. The
> wire-protocol to avoid having to escape all communications, and methods
> for fast incremental update.

Binary wire protocol is done in 7.4 (at least if you are using libpq;
not sure about the state of binary protocol support in other client
libraries). Incremental update does remain an unsolved problem.

> 2) Bytea column with many rows per file.
> open questions: What would the chunk-size be? I would imagine 5-20Kb
> per row.

You'd want to keep it under 2Kb per row to avoid triggering TOASTing of
any single row. What you're doing here amounts to hand TOASTing, and
you don't want to pay the penalty of another layer of the same mechanism
underneath. (Alternatively you might be able to set the storage option
so that compression is allowed but out-of-line storage is not, even for
rows up to 8K. I forget what the options are exactly.)

> 4) External files. I really don't like the idea of this.
> open questions: What tricks to use to keep this ACID-ish?

AFAICS, you can't guarantee much of anything if you do this.

regards, tom lane