Some numbers that indicate PostgreSQL performance using JDBC to query and insert data. All of the tests done on the same quiescent machine.

...

Switching off fsync and full_page_writes has no effect on performance.

Similar checks on Oracle

Writing the same in Oracle is quite a lot faster. Without indexes and PK, but with full transaction logging the performance is:

...

Inserting from the database itself, using pgsql

The following PGSQL method:

CREATE or replace function test() returns void AS $$
DECLARE
    count integer;
BEGIN
    for count in 1..1000000 loop
        insert into datavault.h_test(id_h,source_system_id,organisation_id,load_dts,boekingdetailid)
        values(count, 1, 12, now(), 'boe' || count || 'king' || count);
    end loop;
END;
$$ LANGUAGE plpgsql;

can be executed as:

select test();
commit;

and returns in 4 seconds, which means 250.000 rows per second. This seems to be an upper limit, and is less than half the speed Oracle manages (see below).

Similar checks on Oracle

Writing the same in Oracle is quite a lot faster. Without indexes and PK, but with full transaction logging the performance is:

• Inserted 1000000 rows in 1072 milliseconds, 1000000.0 rows per second
• Inserted 24000000 rows in 47155 milliseconds, 510638.2978723404 rows per second

Links

• http://postgres.cz/wiki/PostgreSQL_SQL_Tricks
• http://rostislav-matl.blogspot.nl/2011/08/fast-inserts-to-postgresql-with-jdbc.html