This email thread discusses the advantages (ease of management) and disadvantages (uneven performance, poor reliability) of running Postgres on Amazon’s Elastic Compute Cloud (EC2) using Elastic Block Stores (EBS). It includes the idea of using multiple EBS volumes in RAID10, and using multiple EC2 database clusters for redundancy. One quote is telling, “It’s possible to run a larger database on EC2, but it takes a lot of work, careful planning and a thick skin.” A similar thread from a year ago had a similar cost/benefit analysis.

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.

I arrived home from PGCon 2010 yesterday. As always the conference was held in Ottawa, Canada to which many of the PostgreSQL developers now make an annual pilgrimage.

We started on Wednesday with the third annual developer meeting, this year held at Arc The Hotel for the second time. Many thanks to Nadine for ensuring everything went without a hitch. There were 29 developers present, working their way through a packed agenda in an extremely productive manner. There were some great ideas for 9.1, with lots of people claiming items to work on. If we get only half of that done, 9.1 will be an incredible release.

There are some photos from the meeting on my SmugMug page.

Thursday was the start of the conference proper, beginning with an excellent presentation from Gavin M. Roy comparing PostgreSQL with NoSQL databases. The talk can be heard here. I ducked in and out of a few more talks over the day, but spent much of my time working with Magnus and Stefan on the new infrastructure hosting platform that we’re polishing off for the project’s hosting.

On Thursday night we had the annual EnterpriseDB party, held at the Velvet Room. Good food, plenty of drink, and people discussing Postgres well into the early hours of Friday. Unfortunately I don’t have any photos as I was too busy running around giving out beer tokens to remember to take any.

Friday was the second day of the main conference, starting at 10AM (thankfully) with David Fetter and Jonathan Leto kicking off my day talking about PL/Parrot. Dan hosted the closing session which consisted largely of an auction, the main purpose of which was to show how the PostgreSQL community can raise more money than the BSD community for charity. And we did. By quite some margin. The biggest earner being a PostgreSQL 9.0 sweatshirt signed by all of the developers at the developer meeting, which amid a circus of bidding between teams led by Jim Nasby and Gavin Roy ended up going for $1517, donated by both camps. Photos, are here.

The week was finished off with a short stop in Montreal with a number of other community members, before taking advantage of cheap Sunday flights to get back home for Monday morning!

Magnus posted the results of the PostgreSQL Europe board elections yesterday, and I’m happy to have be one of the successful candidates, along with Guillaume Lelarge and Andreas Scherbaum. Everyone standing has made important contributions to PostgreSQL however and now that the election is over I can say that any of them would have been good choices in my opinion.

I’m looking forward to working on new initiatives to promote PostgreSQL within Europe, as well as the ongoing work to organise our 2010 conference in Amsterdam, so a big “thank you” goes to all those who voted for me.

And we have been hard at work since my last post. Demand for Postgres has been overwhelming. I said in my first blog, we will only be limited by capacity, not opportunity. Upon reflection, that seems like an incredible grasp of the obvious. We were very well received at the MySQL User’s Group meeting in [...]

Well, in theory I love it. It’s just become a nightmare over the last few trips.

In March there was PG East in Philadelphia. And the BA cabin crew decided to go on strike. Cue purchase of a (refundable) backup ticket at vast expense, and then days of nail biting until it became apparent that my original (on-refundable, but much cheaper) flights would go ahead just with reduced on-board service. So the US airways ticket got refunded, and I enjoyed a couple of salads for lunch. Not to my usual taste, but probably more healthy than what I normally favour!

In April I planned to take a trip to the EnterpriseDB Massachusetts office in Westford. Then Eyjafjallaj?kull erupted, and the ash came.  Four more days of nail biting, before it’s confirmed that my flight is canceled. Excellent service from BA though, and within 30 minutes I’m rebooked for the beginning of May. The office will just have to wait.

So in May we have attempt number two to get to Massachusetts. Which went without a hitch. Amazing. Well, except that the ash returned briefly whilst I was away causing another minor scare. Oh, and a mixup with my ride from the airport which quit waiting for me 22 hours before I arrived.

Second trip in May is PGCon. And late Saturday afternoon I hear on the radio that the ash is back. More nail biting. Constant refreshing of websites like the Met Office and NATS. On Sunday night, the Air Canada flight from Ottawa to London is delayed by hours due to overnight ash in the early hours of Monday morning, and by the time I wake up its been cancelled entirely which means that so is my flight as the plane is now in the wrong place. Luckily, I woke up at 5:30AM, by which time no-one else had noticed the flight cancellation and I managed to get a seat on the Tuesday flight (tomorrow). The ash is now gone, the CAA have relaxed the flying rules again and things are looking rosy.

So what’s next? Well, in June I’m visiting our New Jersey office just after the planned second batch of BA cabin crew strikes are due to finish. Well, were due to finish. Now, BA have managed to get an injunction to prevent the strike going ahead as planned because the union screwed up the ballot (again)! I can already see what’s going to happen; they’ll take two weeks to re-run the ballot, then give BA a weeks notice of upcoming strikes, the second of which will coincide perfectly with my trip. D’oh!

Oh well. I still love being paid to work on Postgres and pgAdmin; it would just be nice if the travel were a little less stressful

While conventional economics theorizes that increased rewards always leads to better output, this
video suggests that many activities, particularly creative ones, produce worse output with increased rewards. The video shows various cases where this can happen, and even interviews open source contributors as a tangible example of people working without financial reward.

This video answers a question I have been asked many times, “Why do people contribute to open source?”, and it answers it better than I ever have. Basically, once people have adequate compensation, people are often motivated more by creative challenges than by money. A corollary of this is that financial reward can distort creative output. This might explain why the Postgres user and developer experience feels different from proprietary software — because there are few financial distortions to interfere with the creative work of designing and using Postgres.

After community discussion, pg_migrator has been renamed to “pg_upgrade” and will be an additional supplied module (/contrib) in the main server distribution of Postgres 9.0 (documentation). Pg_Upgrade supports upgrades from Postgres 8.3.X and 8.4.X to Postgres 9.0. As you can see from the documentation, many of the restrictions of upgrading from 8.3 to 8.4 are gone, even for upgrades from Postgres 8.3 directly to 9.0.

This signals a more serious committment from the community to try to provide non-dump/restore major upgrades. The pg_migrator pgFoundry project will continue, but will not be enhanced to support new major versions of
Postgres — it will remain only to support upgrades to Postgres 8.4.

A colleague of mine (thanks Jimbo!) pointed me towards a benchmark comparison of Postgres 8.3.8 on RHEL 5.4 versus SQL Server 2008 R2 on Windows Server 2008 R2, performed and written up by Red Hat. I hadn’t seen it before, so figured that maybe others hadn’t either:

http://www.redhat.com/pdf/rhel/bmsql-postgres-sqlsrvr-v1.0-1.pdf

Can’t wait for the result? The elephant wins