Re: Postgres insert performance and storage requirement compared to Oracle

On Wed, Oct 27, 2010 at 2:06 PM, Mladen Gogala
<mladen(dot)gogala(at)vmsinfo(dot)com> wrote:
> Scott, I find this very hard to believe. If you are inserting into a
> temporary table and then into the target table, you will do 2 inserts
> instead of just one. What you are telling me is that it is faster for me to
> drive from NYC to Washington DC by driving first to Miami and then from
> Miami to DC.

The reason why in one transaction per insert environment staging to
temp table first is very simple...non temp table inserts have to be
wal logged and fsync'd. When you batch them into the main table, you
get more efficient use of WAL and ONE sync operation. This is
especially advantageous if the inserts are coming fast and furious and
there are other things going on in the database at the time, or there
are multiple inserters.

If you have luxury of batching data in a transaction, you don't have
to worry about it.

merlin