Speeding up Postgres INSERTs

punkish has asked for the wisdom of the Perl Monks concerning the following question:

I have an array of arrays that I am INSERTing into a Pg table (pseudo-code ahead) —

my $sth = $dbh->prepare("INSERT INTO table (a, b, c) VALUES (?, ?, ?)"
+);
for my $row (@table) {
    $sth->execute($row->[0], $row->[1], $row->[2]);
}
$dbh->commit;
[download]

I'd like to speed up the INSERTs by possibly using the COPY table FROM STDIN WITH DELIMITER AS ',' command. Is there a way I can do that? Is there some other way I can speed up the INSERTs?

Update: I should have added, right now the INSERTs are rather slow; several seconds for every 100K INSERTs.

Update2: I finally got a result back from one set of INSERTs. I am getting ~3300 insertions per second. I would consider that fairly slow.

--

when small people start casting long shadows, it is time to go to bed

Comment on Speeding up Postgres INSERTs Select or Download Code

Replies are listed 'Best First'.
Re: Speeding up Postgres INSERTs by jau (Hermit) on Jun 18, 2010 at 07:25 UTC
In case you missed it: The PostgreSQL documentation has a section with performance tips for populating a database.	[reply]
Re: Speeding up Postgres INSERTs by happy.barney (Friar) on Jun 18, 2010 at 10:51 UTC
Do you have AutoCommit turned Off ? Snippet that use COPY: `my $table = 'table'; my @cols = (qw( a b c)); $" = ", "; $dbh->do ("COPY $table (@cols) FROM STDIN"); while (...) { $dbh->pg_putline (join ("\t", @values) . "\n"); } $dbh->pg_endcopy; $dbh->commit;` [download]	[reply] [d/l]
Re^2: Speeding up Postgres INSERTs by punkish (Priest) on Jun 18, 2010 at 11:41 UTC
`my $table = 'table'; my @cols = (qw( a b c)); $" = ", "; $dbh->do ("COPY $table (@cols) FROM STDIN"); while (...) { $dbh->pg_putline (join ("\t", @values) . "\n"); } $dbh->pg_endcopy; $dbh->commit;` [download] Ahhh... that was what I was looking for. Many thanks. In answer to other queries -- AutoCommit is off (hence, the explicit COMMIT every 100_000 rows). I am not reading nor wanting to read from a CSV file, but directly from an array in memory. I don't have any INDEXes other than a primary key -- when small people start casting long shadows, it is time to go to bed	[reply] [d/l]
Re^2: Speeding up Postgres INSERTs by punkish (Priest) on Jun 19, 2010 at 01:11 UTC
Update: Using the COPY command, I am now getting around 15,000 INSERTs per second. Sooooo much better. -- when small people start casting long shadows, it is time to go to bed	[reply]
Re: Speeding up Postgres INSERTs by Khen1950fx (Canon) on Jun 18, 2010 at 06:13 UTC
As I understand it, COPY can read csv files, and the comma delimiter is default when you use CSV mode. Have you tried that?	[reply]
Re: Speeding up Postgres INSERTs by JavaFan (Canon) on Jun 18, 2010 at 09:02 UTC
For most databases (I don't know Pg, so it may be different), it's much faster to have a few large `INSERT INTO table (a, b, c) VALUES (z, y, x), (w, v, u), (t, s, r), ...` [download] queries, than to do a round trip for each row inserted. And sometimes, it's faster to first drop all the indices, and recreate them after the inserts are done. But that requires some benchmarking for each case. several seconds for every 100K INSERTs That doesn't sound that slow to me. Perhaps you need to hire a Pg specialist to tweak your database.	[reply] [d/l]
Re^2: Speeding up Postgres INSERTs by punkish (Priest) on Jun 18, 2010 at 14:10 UTC
I finally got a result back from one set of INSERTs. I am getting ~3300 insertions per second. I would consider that fairly slow. -- when small people start casting long shadows, it is time to go to bed	[reply]
Re^3: Speeding up Postgres INSERTs by JavaFan (Canon) on Jun 18, 2010 at 14:59 UTC
3300 insertions/second comes to 30 seconds for 100K. I find 30 fairly large for "several". But is this number for 3300 separate queries, or a single query with 3300 rows? Of course, there are many other factors to consider, not of them mentioned in the original post, nor in any way perl related: How many indices are they? What kind of indices? Do they allow for new rows written at the end of the table, or must the table be rewritten? Are there any constraints? Foreign keys? Unique indices? Triggers? Auditting? How many rows are there already? How many other processes are using the table while you're doing inserts? What's the hardware the database is running on? How much memory does said hardware have? How "hot" is the table? Is the table being replicated? What's the physical layout of where the data is written to? What's the physical layout of where your logs are written to?	[reply]
Re^4: Speeding up Postgres INSERTs by punkish (Priest) on Jun 18, 2010 at 15:18 UTC
Re^5: Speeding up Postgres INSERTs by JavaFan (Canon) on Jun 18, 2010 at 16:58 UTC
Re: Speeding up Postgres INSERTs by Krambambuli (Curate) on Jun 18, 2010 at 06:49 UTC
Do you have any indexes defined for these columns? Have you maybe tried/thought about disabling indexes during the INSERTs?	[reply]