Variations on 1M rows insert(2): commit write – PostgreSQL

Franck was quite fast with his second post Variations on 1M rows insert(2): commit write. Lets see what we can do on the PostgreSQL side.

I’ll take the same test table as Franck again:

create table DEMO ("id" number , "text" varchar2(15), "number" number) ;

The pgplsql block which corresponds to Frank’s plsql block looks like this:

\timing on
truncate table DEMO;
DO $$DECLARE
  l_people_array varchar(15)[12] := '{"Marc", "Bill", "George", "Eliot", "Matt", "Trey", "Tracy","Greg", "Steve", "Kristina", "Katie", "Jeff"}';
  n int;
BEGIN
  for i in 0..1e6 loop
     n:=trunc(random()*1000+1);
     begin
       insert into DEMO values( i , l_people_array[floor((random()*11))+1::int] , n );
     exception when others then raise;
     end;
  end loop;
END$$;

Some explanations: In pgplsql we can not do a commit inside a loop. Either everything is committed or nothing. But we can force the same behavior by using a begin/exception/end block. Lets see the results:

Time: 5.920 ms
TRUNCATE TABLE
Time: 10.527 ms
LOG:  checkpoints are occurring too frequently (5 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".
DO
Time: 9271.443 ms

Compare that to the test case with the transaction only committing once:

postgres=# \i 1mio_rows_no_pk.sql
Timing is on.
 autocommit 
------------
 on
(1 row)

Time: 0.275 ms
TRUNCATE TABLE
Time: 16.355 ms
DO
Time: 6992.205 ms

It makes a difference of around 2,5 seconds. But 2,5 seconds for 1’000’001 commits is again not that bad. In his second post Franck continues with various variations of commits. In PostgreSQL a commit is not as much fine grained (at least as far as I know, comments welcome). The most similar behavior is synchronous_commit. So lets give it a try.
Now the test case looks like this:

\timing on
alter system set synchronous_commit='off';
select pg_reload_conf();
truncate table DEMO;
DO $$DECLARE
  l_people_array varchar(15)[12] := '{"Marc", "Bill", "George", "Eliot", "Matt", "Trey", "Tracy","Greg", "Steve", "Kristina", "Katie", "Jeff"}';
  n int;
BEGIN
  for i in 0..1e6 loop
     n:=trunc(random()*1000+1);
     begin
       insert into DEMO values( i , l_people_array[floor((random()*11))+1::int] , n );
     exception when others then raise;
     end;
  end loop;
END$$;

Execute it:

postgres=# \i 1mio_rows_no_pk_commit_by_row.sql
Timing is on.
ALTER SYSTEM
Time: 12.870 ms
LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "synchronous_commit" changed to "off"
 pg_reload_conf 
----------------
 t
(1 row)

Time: 5.521 ms
TRUNCATE TABLE
Time: 7.898 ms
DO
Time: 8861.194 ms

Compared to 9,2 seconds from the previous test it makes not that much difference. But this might be dependent on the actual use case and needs to be tested case by case.
Lets see what comes next …