Cool new Free software

Posted Dec 19, 2012 15:58 UTC (Wed) by man_ls (guest, #15091)
In reply to: Cool new Free software by macson_g
Parent article: Status.net service to phase out, replaced by pump.io

Good to know that there are alternatives. I have fond memories of PostgreSQL from some years ago, and MongoDB while nice has some wrinkles here and there. Still, mongo is really fast: you can do hundreds of writes per second without any optimization, while from my recollections PostgreSQL stayed closer to the 100 req/sec mark. I guess that between Moore's Law and relentless progress in PostgreSQL now it will do much better.

There are other NoSQL stores like Redis or Riak which are much faster and more scalable by nature. But their key-value nature makes the programming model much more time-consuming: you have to think about how you are going to exploit the data, not how you want to store it.

Cool new Free software

Posted Dec 19, 2012 16:03 UTC (Wed) by andresfreund (subscriber, #69562) [Link]

The 100 req/sec come from the simple fact that postgres defaults to doing your writes safely and when you do every request in a single transaction without parallelism thats about the speed of a normal disk (when you do them in parallel the synchronous writes needed will be batched together, so you can get significantly higher tps).

You can optionally say that you don't need that (SET synchronous_commit = off in the session or configuration) which will then only sync commits in a background process. Which gives you a data-loss window from < 1s in default settings. Note that *only* the last transactions will be lost, *no* old data can be corrupted by that.

If you need even less guarantees you can say fsync=off.

Cool new Free software

Posted Dec 19, 2012 16:07 UTC (Wed) by macson_g (guest, #12717) [Link] (1 responses)

If you are running PostgreSQL with default configuration and trying to populate your database with INSERTs, then yes, the performance may be less than impressive.

But there is few tricks one can do: 1) use COPY instead of INSERT, 2) loosen reliability guarantees (WAL, sync commits) in configuration, 3) define your table as UNLOGGED.

This will bring your insertion speed close to MongoDB. And probably reliability will still be better than Mongo's.

Cool new Free software

Posted Dec 20, 2012 16:41 UTC (Thu) by pboddie (guest, #50784) [Link]

To get most of the way there, just choose trick #1 in that list. In any book about PostgreSQL performance, that trick should have a chapter by itself consisting of the words "Use COPY instead of INSERT" in very big letters with some smaller print used to clarify stuff like how to do COPY-like operations instead of using the full-privilege COPY, and so on.