benchmark

package Backend;
use Mojo::Base -base;

has 'pg';
has 'db';

sub bulk_insert {
  my ($self, $table, $data, $options) = @_;
  $options ||= {};
  my $tx = $self->db->begin;
  my $stmt;
  $data->each(sub{
    ($stmt) ||= $self->pg->abstract->insert($table, $_, {on_conflict => undef, %$options});
    my $sth = $self->db->dbh->prepare_cached($stmt, undef, 3);
    $sth->execute(@$_{sort keys %$_});
  });
  $tx->commit;
}

package main;
use 5.010;
use Mojo::Pg;
use Benchmark qw(timeit timestr :hireswallclock);

my $pg = Mojo::Pg->new('postgresql://test@%2fvar%2frun%2fpostgresql/test');
$pg->on(connection => sub { warn $_[1]->{pg_pid} });
my $db = $pg->db;
my $backend = Backend->new(pg => $pg, db => $db);

sub n (&@) { say STDERR timestr timeit($_[1] // 1, $_[0]) };

my $limit = $ARGV[0] || 10000;
my $n = $ARGV[1] || 1;
#say $db->query('select count(*) from b')->array->[0];

n {
  my $results = $db->select('a', [qw/first last birthday age phone/], undef, {limit => $limit, offset => 1});
  my $tx = $db->begin; $results->hashes->each(sub{$db->insert('b', $_)}); $tx->commit;
}, $n;

n {
  my $results = $db->select('a', [qw/first last birthday age phone/], undef, {limit => $limit, offset => 1});
  my $tx = $db->begin;
  while ( $_ = $results->hash ) {
    my ($stmt) = $pg->abstract->insert('b', $_);
    my $sth = $db->dbh->prepare_cached($stmt, undef, 3);
    $sth->execute(@$_{sort keys %$_});
  }
  $tx->commit;
}, $n;

n {
  my $results = $db->select('a', [qw/first last birthday age phone/], undef, {limit => $limit, offset => 1});
  my $tx = $db->begin;
  my $stmt;
  while ( $_ = $results->hash ) {
    ($stmt) ||= $pg->abstract->insert('b', $_);
    my $sth = $db->dbh->prepare_cached($stmt, undef, 3);
    $sth->execute(@$_{sort keys %$_});
  }
  $tx->commit;
}, $n;

n {
  my $results = $db->select('a', [qw/first last birthday age phone/], undef, {limit => $limit, offset => 1});
  $backend->bulk_insert('b', $results->hashes);
}, $n;

benchmark-results

# $limit = 10000, $n = 10
1.76079 wallclock secs ( 1.34 usr +  0.05 sys =  1.39 CPU) @  0.72/s (n=1)
1.30126 wallclock secs ( 1.03 usr +  0.02 sys =  1.05 CPU) @  0.95/s (n=1)
0.403406 wallclock secs ( 0.14 usr +  0.06 sys =  0.20 CPU) @  5.00/s (n=1)
0.341554 wallclock secs ( 0.13 usr +  0.04 sys =  0.17 CPU) @  5.88/s (n=1)