Patch to speed up pg_dump

From: "Vincze, Tamas" <vincze(at)neb(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Patch to speed up pg_dump
Date: 2009-04-01 14:47:51
Message-ID: 49D37E97.8050504@neb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

We have a database with tens of millions of large objects, none
of them with any comments. When running pg_dump it spends several
hours looking for BLOB comments, finding none at the end but taxing
the server so much that the simplest query takes seconds to complete.

The attached patch fixes this by fetching the description only
of those BLOBs that may have it.

For those interested, some more info:

This query takes about 2 hours to execute:
sw2=# select count(*) from pg_largeobject;
count
-----------
135807552
(1 row)

I'm throttling the transfer rate on pg_dump's stdout so that it
doesn't affect server performance a lot, but obviously it didn't
help the function saving (looking for) BLOB comments.

Regards,
Tamas

Attachment Content-Type Size
pg_dump_blob_comment_fix.patch text/plain 1.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2009-04-01 15:17:42 Re: [HACKERS] string_to_array with empty input
Previous Message Sam Mason 2009-04-01 14:38:50 Re: [GENERAL] string_to_array with empty input