Test 1: 1000 INSERTs

CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');
INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');
... 995 lines omitted
INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');
INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');
INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');
PostgreSQL:   18.255
SQLite 2.7.6:   45.872
SQLite 2.7.6 (nosync):   0.230

Test 2: 25000 INSERTs in a transaction

BEGIN;
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred sixty one');
... 24997 lines omitted
INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight hundred forty seven');
INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hundred thirty');
COMMIT;
PostgreSQL:   4.216
SQLite 2.7.6:   1.383
SQLite 2.7.6 (nosync):   1.238

Test 3: 100 SELECTs without an index

BEGIN;
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<1100;
... 96 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;
SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;
COMMIT;
PostgreSQL:   4.225
SQLite 2.7.6:   5.094
SQLite 2.7.6 (nosync):   5.095

Test 4: 100 SELECTs on a string comparison

BEGIN;
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';
... 96 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';
COMMIT;
PostgreSQL:   6.210
SQLite 2.7.6:   5.670
SQLite 2.7.6 (nosync):   5.671

Test 5: Creating an index

CREATE INDEX i2a ON t2(a);
CREATE INDEX i2b ON t2(b);
PostgreSQL:   0.351
SQLite 2.7.6:   1.013
SQLite 2.7.6 (nosync):   0.842

Test 6: 5000 SELECTs with an index

SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<200;
SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300;
... 4994 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;
SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;
SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;
PostgreSQL:   3.367
SQLite 2.7.6:   1.079
SQLite 2.7.6 (nosync):   1.085

Test 7: 1000 UPDATEs without an index

BEGIN;
UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;
UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20;
... 996 lines omitted
UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;
UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;
COMMIT;
PostgreSQL:   1.689
SQLite 2.7.6:   0.941
SQLite 2.7.6 (nosync):   0.833

Test 8: 25000 UPDATEs with an index

BEGIN;
UPDATE t2 SET b=271822 WHERE a=1;
UPDATE t2 SET b=28304 WHERE a=2;
... 24996 lines omitted
UPDATE t2 SET b=442549 WHERE a=24999;
UPDATE t2 SET b=423958 WHERE a=25000;
COMMIT;
PostgreSQL:   7.881
SQLite 2.7.6:   3.653
SQLite 2.7.6 (nosync):   3.160

Test 9: 25000 text UPDATEs with an index

BEGIN;
UPDATE t2 SET c='four hundred sixty eight thousand twenty six' WHERE a=1;
UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty eight' WHERE a=2;
... 24996 lines omitted
UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;
UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three' WHERE a=25000;
COMMIT;
PostgreSQL:   8.586
SQLite 2.7.6:   2.501
SQLite 2.7.6 (nosync):   2.036

Test 10: INSERTs from a SELECT

BEGIN;
INSERT INTO t1 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t1;
COMMIT;
PostgreSQL:   1.942
SQLite 2.7.6:   2.793
SQLite 2.7.6 (nosync):   1.924

Test 11: DELETE without an index

DELETE FROM t2 WHERE c LIKE '%fifty%';
PostgreSQL:   0.238
SQLite 2.7.6:   2.896
SQLite 2.7.6 (nosync):   0.638

Test 12: DELETE with an index

DELETE FROM t2 WHERE a>10 AND a<20000;
PostgreSQL:   0.426
SQLite 2.7.6:   2.085
SQLite 2.7.6 (nosync):   0.977

Test 13: A big INSERT after a big DELETE

INSERT INTO t2 SELECT * FROM t1;
PostgreSQL:   4.474
SQLite 2.7.6:   2.785
SQLite 2.7.6 (nosync):   1.803

Test 14: A big DELETE followed by many small INSERTs

BEGIN;
DELETE FROM t1;
INSERT INTO t1 VALUES(1,29676,'twenty nine thousand six hundred seventy six');
... 2997 lines omitted
INSERT INTO t1 VALUES(2999,37835,'thirty seven thousand eight hundred thirty five');
INSERT INTO t1 VALUES(3000,97817,'ninety seven thousand eight hundred seventeen');
COMMIT;
PostgreSQL:   0.849
SQLite 2.7.6:   0.444
SQLite 2.7.6 (nosync):   0.214

Test 15: DROP TABLE

DROP TABLE t1;
DROP TABLE t2;
PostgreSQL:   0.624
SQLite 2.7.6:   0.656
SQLite 2.7.6 (nosync):   0.145