Transactions provide insane bulk insert performance: *It is of note here that each insert is a transaction. What this means is Postgres is doing some extra coordination to make sure the transaction is completed before returning. On every single write this takes some overhead. Instead of single row transactions, if we wrap all of our inserts in a transaction, we’ll see some nice performance gains. -- CitusData Blog <https://www.citusdata.com/blog/2017/11/08/faster-bulk-loading-in-postgresql-with-copy/>*
Using transactions, my benchmark <https://gist.github.com/s1037989/2caa12379b733c7467c634e83f3331a7> improves performance by reducing the insert time of 150,000 records from 70s to 1s. # while ->hash; insert *100.194* wallclock secs (38.81 usr + 2.96 sys = 41.77 CPU) @ 0.02/s (n=1) # prepare; while ->array; execute *71.047* wallclock secs ( 4.56 usr + 1.63 sys = 6.19 CPU) @ 0.16/s (n=1) # begin tx; prepare; while ->array; execute; commit *1.37854* wallclock secs ( 0.26 usr + 0.18 sys = 0.44 CPU) @ 2.27/s (n=1) I'm not sure if or how Mojo::Pg could provide this behavior, or if, as Sebastian said at the beginning, it's really all that useful for Mojo::Pg. Here's one way to accomplish a high-performing transaction-based bulk insert: my $results = $pg->db->select('a', [qw/first last birthday age phone/], undef, {limit => $limit, offset => 1}); $pg->db->bulk_insert('b', $results->hashes->to_array); And in Mojo::Pg::Database, add a new bulk_insert method: # $records is an array of hashes sub bulk_insert { my ($self, $table, $records, $options) = @_; my ($stmt) = $self->pg->abstract->insert($table, $records->[0], $options); eval { my $tx = $self->pg->db->begin; my $i = $self->pg->db->dbh->prepare($stmt); while ( my $next = shift @$records ) { $i->execute(@$next{sort keys %$next}); } $tx->commit; }; return $@ if $@; } On Sat, Feb 3, 2018 at 7:24 AM, sri <kra...@gmail.com> wrote: > For example, we can buffer minion jobs via enqueue and insert them to >> minion_jobs all at once in the end of request. >> >> That feature also is very valuable for highload project if you want to >> store some kind of logs in the database. >> >> So bulk inserts is definitely a good thing and I use them a lot in my >> daily job. >> > > > Seems like reasonable use cases, if someone finds a good way to implement > bulk inserts i'll add the feature. > > -- > sebastian > > -- > You received this message because you are subscribed to the Google Groups > "Mojolicious" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to mojolicious+unsubscr...@googlegroups.com. > To post to this group, send email to mojolicious@googlegroups.com. > Visit this group at https://groups.google.com/group/mojolicious. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "Mojolicious" group. To unsubscribe from this group and stop receiving emails from it, send an email to mojolicious+unsubscr...@googlegroups.com. To post to this group, send email to mojolicious@googlegroups.com. Visit this group at https://groups.google.com/group/mojolicious. For more options, visit https://groups.google.com/d/optout.
