When preparing to transfer blob data from one database to another (8.0.5
to 8.1.4), I found some interesting numbers that made me suspect that
bytea dumping is more ineffective than expectable.
I have a test dataset of 2000 rows, each row containing a bytea column.
Total disk usage of the table (no indexes) is 138MB, total data size is
1.4GB (sum(length(bytea_col)). Data is stored on a RAID5 (Areca 128MB,
SATA, 4 disks), and was dumped to a RAID1 on the same controller.
When dumping the table with psql \copy (non-binary), the resulting file
would be 6.6GB of size, taking about 5.5 minutes. Using psql \copy WITH
BINARY (modified psql as posted to -patches), the time was cut down to
21-22 seconds (filesize 1.4GB as expected), which is near the physical
throughput of the target disk. If server based COPY to file is used, The
same factor 12 can be observed, CPU is up to 100 % (single P4 3GHz 2MB
Cache HT disabled, 1GB main mem).
What's happening here?
Regards,
Andreas