David Newall <postgresql@xxxxxxxxxxxxxxx> writes: > [ very slow pg_dump of table with large bytea data ] Did you look at "vmstat 1" output to see whether the system was under any large I/O load? Dumping large bytea data is known to be slow for a couple of reasons: 1. The traditional text output format for bytea is a bit poorly chosen. It's not especially cheap to generate and it interacts very badly with COPY processing, since it tends to contain lots of backslashes which then have to be escaped by COPY. 2. Pulling the data from the out-of-line "toast" table can be expensive if it ends up seeking all over the disk to do it. This will show up as a lot of seeking and I/O wait, rather than CPU expense. Since you mention having recently recopied the table into a new table, I would guess that the toast table is reasonably well-ordered and so effect #2 shouldn't be a big issue. But it's a good idea to check. PG 9.0 is changing the default bytea output format to hex, in part to solve problem #1. That doesn't help you in an 8.3 installation of course. If you're desperate you could consider excluding this table from your pg_dumps and backing it up separately via COPY BINARY. The PITA factor of that might be more than you can stand though. Offhand I can't think of any other way to ameliorate the problem in 8.3. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
