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 …