Postgres individual inserts, slow performance, delay

[John Mudd]

I'm just trying to insert a couple of rows. I need them committed right away. And there will be a brief delay before the next inserts are ready.

Sample python program:
http://pastebin.com/82NKnH99

I simulate the delay in my sample program by sleeping for 0.2 sec. You can see by the output that sometime the inserts are fast and sometimes close to 0.2 sec.

Output:
Elapsed: 0.0045 sec
Elapsed: 0.0046 sec
Elapsed: 0.1952 sec <-- slow
Elapsed: 0.0021 sec
Elapsed: 0.0012 sec
Elapsed: 0.1955 sec <-- slow
Elapsed: 0.1997 sec <-- slow
Elapsed: 0.0013 sec

If I sleep less than 0.2 sec then all inserts run relatively fast, around 1.5 ms, which is fine for the application.

If I sleep more than 0.2 sec (e.g. 1.0 sec) then all inserts take 0.2 sec.

Is there something I can change so that inserts are fast even with delays between inserts?

John

[John Mudd]

I updated the link to a much shorter sample program. Still reproduces the problem.

John

[John Mudd]

I think the "problem" is not slow INSERTs but "slow" transactions. I use quotes because it's not a true problem with Postgres. The real problem is with my expectations.

I see the same sort of delays when using SQLite. Here's a link to their documentation which probably applies to any db.

http://www.sqlite.org/faq.html#q19

John