in reply to Speeding up Postgres INSERTs

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.

Replies are listed 'Best First'.
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]
      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:
      1. 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?
      2. Are there any constraints? Foreign keys? Unique indices? Triggers? Auditting?
      3. How many rows are there already?
      4. How many other processes are using the table while you're doing inserts?
      5. What's the hardware the database is running on?
      6. How much memory does said hardware have?
      7. How "hot" is the table?
      8. Is the table being replicated?
      9. 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]
        JavaFan> 3300 insertions/second comes to 30 seconds for 100K. 
JavaFan> I find 30 fairly large for "several".
        Indeed. When I asked the original question, I didn't have the correct number. Now I do, for one instance of the job. 
        JavaFan> But is this number for 3300 separate queries, or a single 
JavaFan> query with 3300 rows?
        I am not sure what you mean by "a single query with 3300 rows." I mean $sth->execute() is called for each row, and a $dbh->commit() is called once every 100_000 rows. In the end, I get i rows inserted in j wallclock seconds (via Benchmark), and i / j is equal to approx. 3300.

        Other answers --

        • 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?
          • No indices except for a primary key. I don't allow or disallow anything. I am simply inserting the rows. I have no idea if the db is writing them at the end of table, but I am assuming it is. If it is rewriting the entire table everytime then it is a very silly db
        • Are there any constraints? Foreign keys? Unique indices? Triggers? Auditting?
          • none whatsoever for all of the above
        • How many rows are there already?
          • several hundred million
        • How many other processes are using the table while you're doing inserts?
          • No other processes. Except, now I have started experimenting with doing parallel INSERTs by starting new perl processes inserting different set of rows. See next question on hardware.
        • What's the hardware the database is running on?
          • It is a dual 3GHz Xeon Xserve with two cores each. Hence, my experimentation with four concurrent perl processes doing row insertions.
        • How much memory does said hardware have?
          • 12 GB
        Re. the rest of your questions:
        • How "hot" is the table? Don't know what that means.
        • Is the table being replicated? No.
        • What's the physical layout of where the data is written to? Don't know what that means.
        • What's the physical layout of where your logs are written to? Don't know.
        --

        when small people start casting long shadows, it is time to go to bed
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom