Skip to content
PostreSQL and Perl bulk inserts & updates speed testing
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
lib
sql
t
tmp
.gitignore
Makefile
README.pod

README.pod

SYNOPSIS

with 23k rows, Postgres server 9.6.12 on localhost:

pg-n-pl-bulking$ prove -l t/
t/01_test-clean-db.t ................................... ok
t/02_generate-import-data.t ............................ ok
t/03.1_insert-data_standard.t .......................... 5/? # 23000 rows total, speed 0.642k/s
t/03.1_insert-data_standard.t .......................... ok
t/03.2_insert-data_standard_delete.t ................... 5/? # 23000 rows total, speed 0.644k/s
t/03.2_insert-data_standard_delete.t ................... ok
t/03.3_insert-data_on_conflict.t ....................... 5/? # 23000 rows total, speed 0.708k/s
t/03.3_insert-data_on_conflict.t ....................... ok
t/04.1_insert-data_standard_pre_select_chunks.t ........ 5/? # 23000 rows total, speed 0.711k/s
t/04.1_insert-data_standard_pre_select_chunks.t ........ ok
t/04.2_insert-data_standard_pre_select_chunks_async.t .. 5/? # 23000 rows total, speed 0.740k/s
t/04.2_insert-data_standard_pre_select_chunks_async.t .. ok
t/04.3_insert-data_standard_pre_select_chunks_multi.t .. 5/? # 23000 rows total, speed 8.565k/s
t/04.3_insert-data_standard_pre_select_chunks_multi.t .. ok
t/04.4_insert-data_standard_pre_select_chunks_copy.t ... 5/? # 23000 rows total, speed 10.476k/s
t/04.4_insert-data_standard_pre_select_chunks_copy.t ... ok
t/05.1_insert-data_standard_multi_on_conflict.t ........ 4/? # 23000 rows total, speed 12.111k/s
t/05.1_insert-data_standard_multi_on_conflict.t ........ ok
t/05.2_insert-data_standard_multi_on_conflict_async.t .. 5/? # 23000 rows total, speed 12.418k/s
t/05.2_insert-data_standard_multi_on_conflict_async.t .. ok     All tests successful.
Files=11, Tests=69, 181 wallclock secs ( 0.08 usr  0.00 sys + 19.30 cusr  9.34 csys = 28.72 CPU)
Result: PASS

with 23k rows, Postgres server 9.6.8 over local network

pg-n-pl-bulking[master]$ prove -l t/
t/01_test-clean-db.t ................................... ok
t/02_generate-import-data.t ............................ ok
t/03.1_insert-data_standard.t .......................... 5/? # 23000 rows total, speed 0.540k/s
t/03.1_insert-data_standard.t .......................... ok
t/03.2_insert-data_standard_delete.t ................... 5/? # 23000 rows total, speed 0.599k/s
t/03.2_insert-data_standard_delete.t ................... ok
t/03.3_insert-data_on_conflict.t ....................... 5/? # 23000 rows total, speed 1.099k/s
t/03.3_insert-data_on_conflict.t ....................... ok
t/04.1_insert-data_standard_pre_select_chunks.t ........ 5/? # 23000 rows total, speed 1.129k/s
t/04.1_insert-data_standard_pre_select_chunks.t ........ ok
t/04.2_insert-data_standard_pre_select_chunks_async.t .. 5/? # 23000 rows total, speed 1.222k/s
t/04.2_insert-data_standard_pre_select_chunks_async.t .. ok
t/04.3_insert-data_standard_pre_select_chunks_multi.t .. 5/? # 23000 rows total, speed 7.884k/s
t/04.3_insert-data_standard_pre_select_chunks_multi.t .. ok
t/04.4_insert-data_standard_pre_select_chunks_copy.t ... 5/? # 23000 rows total, speed 9.120k/s
t/04.4_insert-data_standard_pre_select_chunks_copy.t ... ok
t/05.1_insert-data_standard_multi_on_conflict.t ........ 5/? # 23000 rows total, speed 10.454k/s
t/05.1_insert-data_standard_multi_on_conflict.t ........ ok
t/05.2_insert-data_standard_multi_on_conflict_async.t .. 5/? # 23000 rows total, speed 10.325k/s
t/05.2_insert-data_standard_multi_on_conflict_async.t .. ok
All tests successful.
Files=11, Tests=69, 166 wallclock secs ( 0.06 usr  0.00 sys + 22.14 cusr  8.18 csys = 30.38 CPU)
Result: PASS

DATABASE SETUP

as root

pg_hba.conf, datable and role:

host    pg_n_pl_bulking_db      nobody  127.0.0.1/32            trust

/etc/init.d/postgresql reload
su - postgres
psql -c 'CREATE ROLE nobody LOGIN;'
createdb --owner=nobody pg_n_pl_bulking_db
exit

as user:

cat >> ~/.pg_service.conf << __SERVICE__

[pg_n_pl_bulking_db]
host=localhost
port=5432
user=nobody
dbname=pg_n_pl_bulking_db
__SERVICE__

# to test
PGSERVICE=pg_n_pl_bulking_db psql

# to create test tables
prove -l t/01_test-clean-db.t -v

RUN

once database config is in place, simply execute:

make

or for a single test

prove -l t/…

pleae note that the 03.1_insert-data_standard.t will generate database dump tmp/test_dump-pg_n_pl_bulking.tsv to verify if the other methods are correctly inserting/updating the data.

TODO/FIXME

* try txn_do { delete + insert }

SEE ALSO

More text/description and commenting possibility on my blog: https://blog.kutej.net/2019/06/pg-n-pl-bulking.

Feel free to create an issue or make a pull-request on GitHub https://github.com/jozef/pg-n-pl-bulking.

You can’t perform that action at this time.