Christopher Browne wrote:
> On an otherwise-idle system, I took that same table and dumped/loaded in
> both formats.
> 
> Summarizing the timings observed:
> 
>             Text           Binary
>           --------------------------
> Dump time   15:38          04:53
> Load time   07:57          07:14
> 
> It is interesting to note that the binary dump was over 3x faster than
> the text dump.

The difference goes up to factor 10, if the data contains mostly 
non-ascii-representable data so every byte is escaped to the 5-byte form 
(which apparently consumes a lot of time for the conversion). I found 
the text form to be 4.9 times bigger than the binary form for my 
particular case. If all 256 possible bytes were evenly distributed, a 
factor around 3.5 (96/256 + 5* 160/256) should be the result. This 
certainly adds a lot to network costs.


> I don't have anything handy that makes interesting use of TOAST tables. 
> 
> I'd want to see a benchmark showing a *compelling* benefit before
> considering it.  The above certainly isn't compelling.

You could stuff a bytea column with 1MB of random data. There's probably 
no easy and ultimately satisfying way for a portable solution at the 
moment, as long as there's no COPY option that works performant for all 
data patterns.

Regards,
Andreas