Share Tweet Share





What is an in-memory database, exactly? We’re used to thinking of using a nonrelational database as a cache for PostgreSQL. Yet just before pgCon this year I brushed elbows with three different companies who were using PostgreSQL as a cache for Hadoop/HBase (or similar): Mozilla, Factual.com, and one other. They have large, non-relational document stores which they use for data processing, and need a highly relational database like PostgreSQL in order to be able to present data to the user. In addition to turning the conventional scalable application design on its head, this presents some interesting configuration requirements. For one thing, these users do not require PostgreSQL to be durable. At all. It’s faster for them to replace the entire database from the document store than to wait for recovery from disk. Then Gavin Roy did his keynote for pgCon, in which he presented his performance tests from storing key-value data in a variety of databases. The case he tested was exactly the one you’d expect key-value databases to shine at: EAV data in a single-threaded application. Yet the fascinating thing was that PostgreSQL with all durability turned off had higher throughput than many non-relational databases, even for extremely denormalized data. This kind of invalidates the conventional wisdom that simpler databases are “faster”. Gavin called it “running with scissors mode”, a name I like a lot (look at the slides, there’s a great picture for this). With the advent of binary replication coming in 9.0, this opens some intriguing possibilities for a non-durable version of PostgreSQL. Replication could be used to populate throwaway copies, or to make enough copies to prevent more than a few seconds of data loss. So I started some discussion on the pgsql-performance mailing list: what would it take to remove all durability from PostgreSQL? If we’re that fast just with fsync off, could we be faster if we ripped out everything related to disk persistence? I’m talking not only about disk access, but the possibly large amount of CPU associated with durability work. WAL segment writing, background writer, checkpoint page flushes all burn CPU, and if you’ve settled on recreating the database from scratch in any crash, why bother? We’d need some WAL for replication, but that’s it. Also, of course, we would want to program PostgreSQL not to even try restarting after a crash; init scripts might be sufficient for this. It’s quite possible that removing these tasks won’t produce a measurable performance impact. Only one real way to find out. Robert Haas is taking a step towards this by trying to implement unlogged tables (“global temporary tables”), which will solve the caching problem for a lot of people who have it in a more limited way (user session tables, anyone?) which might end up being sufficient for most purposes, although they don’t really solve the PostgreSQL-as-cache use-case. In the meantime, Bruce Momjian has added to the main docs for 9.0 how to turn off all durability in PostgreSQL if you don’t want it. And I’ve registered the domain scissorsdb.org. Maybe I’ll have time to do something with it.