Re: PostgreSQL as a local in-memory cache

FYI I've tweaked this program a bit :

import psycopg2
 from time import time
conn = psycopg2.connect(database='peufeu')
cursor = conn.cursor()
cursor.execute("CREATE TEMPORARY TABLE test (data int not null)")
conn.commit()
cursor.execute("PREPARE ins AS INSERT INTO test VALUES ($1)")
cursor.execute("PREPARE sel AS SELECT 1")
conn.commit()
start = time()
tx = 0
N = 100
d = 0
while d < 10:
    for n in xrange( N ):
        cursor.execute("EXECUTE ins(%s)", (tx,));
        #~ conn.commit()
        #~ cursor.execute("EXECUTE sel" );
    conn.commit()
    d = time() - start
    tx += N
print "result : %d tps" % (tx / d)
cursor.execute("DROP TABLE test");
conn.commit();

Results (Core 2 quad, ubuntu 10.04 64 bits) :

SELECT 1 : 21000 queries/s (I'd say 50 us per query isn't bad !)
INSERT with commit every 100 inserts : 17800 insets/s
INSERT with commit every INSERT : 7650 tps

fsync is on but not synchronous_commit.