On Thu, Feb 23, 2012 at 1:37 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
It's possible that you might get a nice boost by wrapping the inserts into a transaction:
begin;
insert into...;
insert into...;
insert into...;
...
commit;
This only requires all that disk-intensive stuff that protects your data once at the end instead of 1000 times for you batch of 1000.
I think that is essentially what I am doing. I'm using psycopg2 in a python script that runs continuously on a queue. It opens a connection and creates a cursor when it begins. It then passes that cursor into a function along with the data (read off the queue) that needs to be inserted. I run cur.execute("SAVEPOINT insert_savepoint;") followed by cur.execute(q) (where q is the insert statement). If there's an error I run cur.execute("ROLLBACK TO SAVEPOINT insert_savepoint;") otherwise I increment a counter. Once the counter exceeds 999, I run conn.commit() and reset the counter. I believe that psycopg2 is essentially doing what you are suggesting. The fact that the data does not appear in the database until conn.commit() tells me that it's not committing anything until then.
COPY is even better. I just ran a quick test by restoring a table on my desktop hacking db (untuned, few years old PC, single SATA disk, modest RAM and lots of resource competition). The 22+ million rows restored in 282 seconds which is a rate somewhat north of 78,000 records/second or about 0.13ms/record.
I'll try that. Of course, the fact that the database is stored in AWS complicates matters. Regardless, it sounds like COPY should be considerably faster.