3 ms·

Well, on a PG 9.1.9 instance, which has fsync=on and an older Intel i7 on an Intel SSD, my 8-process program inserting a 120-byte TEXT value into a (SERIAL, TE

by Erwin 13y ago

Well, on a PG 9.1.9 instance, which has fsync=on and an older Intel i7 on an Intel SSD, my 8-process program inserting a 120-byte TEXT value into a (SERIAL, TEXT) table, with transactions each doing 10 inserts, manages to finish inserting 100,000 groups of 10 "documents" in 37 seconds. So that's roughly 27,000 documents inserted per second (not sure if they are measuring individual inserts documents or groups of 10). According to iostat, this does 5000 write transactions per second to the disk. If you are logging throw-away data, you can use an UNLOGGED table. This brings it to 49,000 inserted rows per second. If you don't care about your data integrity, and set fsync=off, it goes up to 65,000 and seems to be limited by CPU.

coffeemug 13y ago
Very cool, thanks for the numbers! A few bits of info on the benchmark in the post. It uses a single process/thread/connection. Adding more concurrent clients would improve performance, though there is still some scalability work to be done. Ops/second in the graphs measures number of documents, not groups. One fundamental aspect of the benchmark is that it uses random keys, which is significantly slower (in any database) than using sequential inserts. I'm not sure how you insert into Postgres wrt to keys. I suspect postgres has better latency, and since the benchmark in the post is very much latency bound, this would account for the factor of four performance difference. It's something we still need to address. EDIT: 1 million documents / 37 seconds / 8 connections = 3378 ops/second, which is about a factor of four off from 800 ops/second that RethinkDB does on this. (This is obviously back of the napkin math.) There's lots of work to be done, I'm very much looking forward to publishing authoritative, scientific comparison benchmarks.
- malkia 13y ago
  For postgres I've been using several threads, each with its own connection, and all going through the same transaction (using the number exported by pg_export_snapshot()) - with this the speedup is quite big (c++ project using libpq). I expect that if I move to binary format for inserting it might be even faster. I'm basing this since my reader is binary and that made x3-x4 improvements for my data set/computer/etc.

by Erwin 13y ago

coffeemug 13y ago

Very cool, thanks for the numbers! A few bits of info on the benchmark in the post. It uses a single process/thread/connection. Adding more concurrent clients would improve performance, though there is still some scalability work to be done. Ops/second in the graphs measures number of documents, not groups. One fundamental aspect of the benchmark is that it uses random keys, which is significantly slower (in any database) than using sequential inserts. I'm not sure how you insert into Postgres wrt to keys. I suspect postgres has better latency, and since the benchmark in the post is very much latency bound, this would account for the factor of four performance difference. It's something we still need to address. EDIT: 1 million documents / 37 seconds / 8 connections = 3378 ops/second, which is about a factor of four off from 800 ops/second that RethinkDB does on this. (This is obviously back of the napkin math.) There's lots of work to be done, I'm very much looking forward to publishing authoritative, scientific comparison benchmarks.

malkia 13y ago
For postgres I've been using several threads, each with its own connection, and all going through the same transaction (using the number exported by pg_export_snapshot()) - with this the speedup is quite big (c++ project using libpq). I expect that if I move to binary format for inserting it might be even faster. I'm basing this since my reader is binary and that made x3-x4 improvements for my data set/computer/etc.