Re: Postgres batch write very slow - what to do

On 3/16/07, Bob Dusek <bob(at)copienttech(dot)com> wrote:
> This may or may not be related to what you're seeing... but, when we
> changed from Postgres 7.4.2 to 7.4.8, our batch processing slowed down
> fairly significantly.
>
> Here's what we were doing:
>
> Step 1) Build a larg file full of SQL insert statements.
> Step 2) Feed the file directly to "psql" using "psql dbname <
> insertfile".
>
> The time of execution for step 2 seemed like it nearly doubled from
> 7.4.2 to 7.4.8, for whatever reason (could have been the way Suse
> compiled the binaries). Perhaps the slowdown was something we could
> have/should have tweaked with config options.

> At any rate, what we did to speed it up was to wrap the entire file in a
> transaction, as such: "BEGIN; ..filecontents.. COMMIT;"
>
> Apparently the autocommit stuff in the version of 7.4.8 we were using
> was just *doggedly* slow.
>
> Perhaps you're already using a transaction for your batch, though. Or,
> maybe the problem isn't with Postgres. Just thought I'd share.

If you are inserting records one by one without transaction (and no
fsync), i/o is going to determine your insertion speed. not really
sure what was happening in your case...it looks like quite a different
type of issue from the OP.

anyways, to the OP some quick googling regarding postgresql jdbc
driver showed that the batch insert case is just not as optimized (in
the driver) as it could be. The driver could do multi statement
inserts or use the libpq copy api, either of which would result in
huge performance gain.

merlin