Re: Postgres insert performance and storage requirement compared to Oracle

On Tue, Oct 26, 2010 at 4:02 PM, Mladen Gogala
<mladen.gogala@vmsinfo.com> wrote:
> On 10/26/2010 11:41 AM, Merlin Moncure wrote:
>>
>> yup, that's exactly what I mean -- this will give you more uniform
>> insert performance (your temp table doesn't even need indexes).  Every
>> N records (say 10000) you send to permanent and truncate the temp
>> table.  Obviously, this is more fragile approach so weigh the
>> pros/cons carefully.
>>
>> merlin
>
> Truncate temporary table? What a horrible advice! All that you need is the
> temporary table to delete rows on commit.

I believe Merlin was suggesting that, after doing 10000 inserts into
the temporary table, that something like this might work better:

start loop:
  populate rows in temporary table
  insert from temporary table into permanent table
  truncate temporary table
  loop

I do something similar, where I COPY data to a temporary table, do
lots of manipulations, and then perform a series of INSERTS from the
temporary table into a permanent table.

--
Jon