Thursday 27 May 2010

Comparing VoltDB to Postgres

I've been asked a few times recently for my opinion on VoltDB, the new database server architected by the 'father' of Postgres, Dr Michael Stonebraker so rather than repeating myself over and over again it seems like a good idea to write it all down.

VoltDB is an in-memory, lockless relational database that maintains ACID compliance, has a SQL interface and claims to offer massive performance increases and scalability over 'traditional' relational databases. If you take the time to visit the website, and download some of the docs or even the product itself, if you're a database geek like me you'll probably be pretty impressed. The technology is interesting - the ability to avoid locking seems like paradise, as does the linear scaling.

There are downsides though. In order to implement the key features of the DBMS, Stonebraker has had to design the system to work with a pretty narrow set of use-cases. Lets consider some of the pros and cons and compare them to Postgres.

In memory database

VoltDB is an in-memory database. This means it can be very fast. It also gives us two potential problems:

  1. The database must fit into the available memory on the system. That means that with a single server with 4GB of RAM, a practical database size limit may be in the order of 3GB or so, once you allow 1GB for the OS and memory required to actually operate the database server.

  2. Durability (the D in ACID) must be provided through replication of data to one or more secondary servers over the network, or through writing periodic snapshots to disk. Because replication is synchronous within the cluster and asynchronous between clusters, it is possible for a cluster-wide power failure to cause the loss of committed transactions.

Of course, in some circumstances these may be non-issues. VoltDB scales horizontally extremely well (near linearly in fact), so if your database is large, you can add more servers to get the storage you need. This won't suit people running multi-terabyte databases of course - RAM is cheap these days, but not that cheap - especially when you multiply by 2 or more for durability and redundancy!

In contrast, Postgres stores it's data on non-volatile storage - a direct attached hard disk, or SAN for example. The issues here are:

  1. Since Postgres uses both a shared buffer cache and the kernel cache (and potentially Infinite Cache in EnterpriseDB's Postgres Plus Advanced Server), in a well-configured system - like VoltDB - it will read most if not all of its data from memory. Unlike VoltDB, Postgres will still have locking and buffer management overhead however and of course, any disk reads will be much slower.

  2. Durability is achieved through the immediate logging of all transactions to a sequential transaction log, and later writing of updated pages to the heap. This is much slower than VoltDB's in-memory operations as we write everything to disk twice.

Summary: VoltDB is fast, because it's in-memory. This creates serious practical limits on database size though - how big is your budget? You'll also need at least two servers, with independent power supplies for any real durability. Postgres is slower, most noticeably when the working set doesn't fit into a cache, but you can store multi-terabyte databases on very cheap hard disks with full durability.

Lockless database

Traditional DBMSs allow for concurrent access by using granular locking and other techniques such as MVCC (Multi Version Concurrency Control). By locking only the smallest part of the database required to perform a specific operation, they ensure that other users can still access the rest of the database concurrently. Locks are held for as short a period as possible.

In contrast, VoltDB is a lockless database and therefore doesn't suffer from any of the complexities involved in lock management or MVCC snapshot management that Postgres does. This is especially important when scaling horizontally, as lock management and snapshot management are two of the most complex problems to solve when building clustered database systems based on Postgres.

VoltDB's solution isn't a panacea for these issues though. To achieve lockless operation, every request is serialised through each partition of the database. This means that if you have one partition (i.e. a single server), each database request will be run sequentially, with no parallelism at all. If you have partitioned data, then requests that affect different individual partitions may run in parallel, however any multi-partition requests must be run against the entire cluster on their own.

So what does this mean? Primarily, the effect is that performance is likely to tank if you try to run any complex queries on the system. Any long running, multi-partition query will block all other users of the database until it completes, which means that the system is only suitable for simple OLTP applications, with well thought out data structures. Don't be tempted to sneak any reports into your apps!

In contrast, Postgres can easily handle both OLTP and DW (data warehouse) workloads. MVCC ensures that 'readers never block writers', so you can run complex reports at the same time as tens or hundreds of OLTP operations are running concurrently. The downside is, that it is significantly harder to build multi-master clusters with Postgres.

Summary: VoltDB's lockless architecture makes it easy to scale horizontally, but limits concurrency. Postgres is harder to scale out, but offers excellent concurrency through fine-grained locking and MVCC.

Evolution and growth

The schema in a VoltDB database is defined in a runtime 'catalog'. This includes not just the schema, but also the details of the different nodes in the cluster, and the size of the database. Any changes that are required to the schema, to the configuration of the cluster, or to the size of the database currently require:

  • Dump of the data to non-volatile storage and shutdown of the VoltDB server on each node.
  • Reconfiguration of the runtime catalog.
  • Restart of the VoltDB server on each node, and reload of the data from storage.

In Postgres, database objects can be modified entirely 'on the fly', without shutting down the server or dumping or reloading data. The database size can grow to theoretically unlimited sizes, constrained only by the amount of storage available without the need to restart. Most of the clustering solutions for Postgres don't require a shutdown to install or reconfigure them.

Summary: VoltDB cannot offer high availability if the database grows beyond the predicted size, nor can changes to the schema (such as those that may be required by a software upgrade) be made without shutting down the system. Postgres allows your system to grow and evolve without requiring downtime.

Ecosystem, drivers and tools

VoltDB is a new project. At present, it doesn't have any real community, the only tools available are those shipping with the server, and the API is based on calling Java stored procedures. Actually, that's a bit awkward really. You essentially have to write a data access layer on the server in Java, which encapsulates all of the SQL queries you need to run. Your client code then calls those procedures directly.

Postgres has a long history. There is a large and vibrant community, with hundreds of tools, add-ons, utilities and so-on available. APIs to access the database are available for a host of languages including C, C++, .NET, Perl, Python, TCL, Ruby, PHP, Java and more. Stored procedures (functions) can be written in common languages including C, Perl, TCL, Ruby, Python PHP and Java.

Summary: The long Postgres history means that there are far more tools, utilities and interfaces available for it. VoltDB can catch up, but it will likely take many years. Further, the Java API limits how you can access the database - there is no way to connect via a standard ODBC or JDBC driver to allow you to use generic query tools for example.

Conclusion

VoltDB is an interesting product, but one with limited use-cases. If your database can fit in the memory of your server cluster, and you can architect your application to avoid any kind of complex query then it can offer vast performance advantages over traditional DBMSs like Postgres, and has potential to scale extremely well.

For most users though, concurrency and the ability to run complex queries are real issues, as is the ability to scale the database beyond sizes that are economical to keep in RAM, without having to dump and reload the data and restart the server to accommodate expansion and evolution of the system.

I can see some interesting use cases for VoltDB, and I'll be keeping an eye on its evolution and time permitting, trying it out for size. It's by no means a universal replacement for Postgres or any other similar DBMS (nor does it claim to be), but it could prove to be a very useful tool in the right situation.

8 comments:

  1. Great analysis. Very accurate.

    VoltDB is still under development and some of the limitations (blocking multi-partition transactions, lack of online schema change and adding/removing nodes) are not architectural and will be improved in future releases.

    See http://community.voltdb.com/roadmap

    Currently VoltDB has some analytic utility where the number of queries is small but the number of updates is large. The naive blocking approach currently implemented is good for 10s of read only multi-partition queries per second while still sustaining a hefty single partition workload.

    There is also support for ELTing a subset of your data to a dedicated analytic system for historical and analytic purposes. This can be used to ease the load (both queries and storage) on the OLTP system.

    ReplyDelete
  2. Hi Dave. Thanks for the post.

    I think Ariel, beat me to a comment, but I'll chime in too.

    As you point out, there's always trade offs between general systems like Postgres and a set of specialized systems. VoltDB is designed to solve OLTP at internet scale. If you don't need the scale of VoltDB, then of course you're going to be much happier with a general system like Postgres that offers so many features and is compatible with so much.

    -John Hugg
    VoltDB Engineering
    http://voltdb.com

    ReplyDelete
  3. I think that it might be possible to use VoltDB and a DBMS like PostgreSQL together - if it was possible to use both VoltDB and a different DBMS from commonly used web languages, it could serve well as a local cache. I'm not sure how well it would perform compared to e. g. Memcached (I do realise they are quite different), but for some, it could be an interesting solution.

    Another use with combination with a more general DBMS would be to use them in two layers - VoltDB would handle the most recent/active data, the rest would be moved into the more traditional DBMS. The code to do that might be done in VoltDB, if one can use JDBC in its stored procedures. It might make things more difficult for the application unless VoltDB would query data in the other DMBS or if there wouldn't be a layer which would do that.

    ReplyDelete
  4. Ariel, John - thanks for the comments. It's always good to hear positive feedback from 'the other side of the fence' :-)

    slapo; as you say, memcached as you might use it with stock Postgres is quite different - essentially it's a KV store that you can use from functions and in SQL queries. We do use it at EDB in our commercial Advanced Server product as the basis for Infinite Cache, which is an additional distributed caching layer which can offer huge performance boosts by allowing you to get more of a most-read working set into memory.

    Using a traditional DBMS as a backend store for VoltDB is an interesting idea, but I suspect you would need deeper (or more complex) integration than having the stored procedures push data to the DBMS over JDBC. This is because of the serialisation issue I described, which would mean that VoltDB would slow down due to the additional work in the SPs which would be relatively slow. One solution might be to push the changes to a queue (which can be very fast), and then have them loaded from there in a lazy fashion.

    ReplyDelete
  5. Dave, VoltDB has been designed to push information to companion systems with minimal impact to the performance of VoltDB. First we allow you to mark any table as an "export" table. Changes to the table do get put in an asynchronous queue that isn't cleared until the results have been acked by the companion system. We also support "append-only" tables, where the table itself becomes the asynchronous queue. Doing an insert in a stored procedure will effectively inset a tuple into the companion system.

    One thing to be careful about is the volume of the export stream. VoltDB can change it's state at a staggering rate. If you're using VoltDB with a system that can't insert fast enough (most), then you'll want to be careful about how you filter, aggregate or transform the data before putting it in the export stream.

    If anyone is interested, please check out our publicly available manual at http://community.voltdb.com. Export is new feature and integration is very important to VoltDB. So we're actively looking for feedback on whether it meets our customers needs and how we can improve it.

    ReplyDelete
  6. Just would like to clarify my last comment a bit. When I say companion system, we at VoltDB usually mean an OLAP system for reporting. This is the specialized systems for specialized purposes model we're pushing for those with scaling pain.

    We didn't build VoltDB to be a cache for a second OLTP system and we think it stands well on its own. That said, we're putting VoltDB out there for anyone to try. If people can solve problems with it, we don't care how it fits in with other systems. If you find a use case for VoltDB that works for you, we'd love to hear about it. Having users discover unexpected uses is one of the most exciting things about the open source business model.

    Thanks again Dave for the discussion.

    ReplyDelete
  7. Despite the last comment about the intentions of VoltDB, I'm intrigued by the use case of VoltDB as cache/queue to a more easily-made-durable backing store. Basically, how about a VoltDB vs. Redis discussion? (probably warrants another article rather than continued conversation here, though).

    @Dave: great article; answers a few of my questions regarding VoltDB's current utility (not making assumptions on how future iterations will play out).

    ReplyDelete
  8. Dave, it's been almost two years since you wrote the original post comparing VoltDB and Postgres. Thanks for allowing me to offer some VoltDB updates. I'll attempt to paraphrase your original point and follow up with my response.

    Regards,
    Fred Holahan, VoltDB


    Dave: The [VoltDB] database must fit into available memory.

    Fred: Although this is still true, the costs of main memory are dropping sharply. Commodity servers with 256GB RAM are now quite common. Several VoltDB customers are running multi-TB databases – quite large for OLTP workloads.

    Dave: Durability (the D in ACID) must be provided through ... writing periodic snapshots to disk.

    Fred: In Sept 2011 we released VoltDB 2.0, which included Command Logging. This feature logs all transactions between snapshots, and allows users to choose between synchronous and asynchronous logging. Synchronous logging guarantees full durability at increased transaction latency (estimated 15%, but your mileage may vary); asynchronous reduces the hit on latency in exchange for some possible transaction loss. If you choose asynchronous, you can tune the fsync window to balance the tradeoff between performance and durability.

    Version 2.5 of VoltDB, released in April of 2012, also offers full database replication for applications requiring hot standby, disaster recovery and workload (i.e., read vs, write) optimization.

    In summary, VoltDB provides a very rich feature set for HA, durability and DR: synchronous multi-master replication for HA, snapshots and tunable command logging for durability, and database replication for DR.

    Dave: RAM is cheap these days, but not THAT cheap ...

    Fred: Fair point. If you need extremely high throughput and scale, the hardware costs (i.e., memory vs. disk or SSD) will be a lot higher. However, on a cost-per-transaction basis, the scales tip in the other direction when you’re running high-throughput OLTP apps. Said another way, for typical OLTP-sized databases, you will likely have to build a really large Postgres infrastructure to achieve throughput of 1 million TPS; VoltDB can handle that level of throughput quite economically.

    Dave: Any long running, multi-partition query will block all other users of the database until it completes ...

    Fred: Although what you say is technically correct, the devil is in the details. In the same way that good database design mitigates many potential performance issues in single-node RDBMSs, the same is true for distributed RDBMSs like VoltDB. It’s not too difficult to design a VoltDB schema that minimizes the need for multi-partition requests.

    Your point about long-running queries is valid. VoltDB is aimed at high velocity OLTP problems; it’s not a general purpose RDBMS. So, for long-running queries, we recommend exporting data to a data warehouse. VoltDB has a published export interface that’s designed specifically for this purpose. You can even do a variety of common enrichment operations on the data prior to export (de-duplication, grouping, aggregation, etc.)

    Dave: There is no way to connect [to VoltDB] via a standard ODBC or JDBC driver ...

    Fred: VoltDB now does provide a JDBC interface. Although Java stored procedures do continue to provide the highest performing database API, the JDBC interface is a fine way to go if you’re integrating 3rd party ecosystem tools with VoltDB. Like Postgres, VoltDB also provides client libraries for C, C++, .NET, PHP, Python, Node.js and, of course, Java.

    Dave: [VoltDB is] by no means a universal replacement for Postgres or any other similar DBMS (nor does it claim to be), but it could prove to be a very useful tool in the right situation.

    Fred: I’d agree that VoltDB should not be viewed as a universal replacement for any general purpose RDBMS, Postgres included. VoltDB is designed for applications that require ultra-fast transaction throughput at low (typically single millisecond) latencies. VoltDB is commonly used in concert with a DW (or Hadoop) to optimize OLTP throughput and analytic queries/reports.

    ReplyDelete