Database Comparisons

We have many options for databases. Sadly, none seems to be perfect for our purposes. Here are some of the tradeoffs. (Note that this only applies to the main application data, which is largely non-transactional. For key transactional data such as memberships, we are likely to go with a conventional RDBMS, likely MySQL.)

MySQL

Pros

• Built into CloudBees.
• Built into CloudFoundry.
• Well-understood and highly mature.

Cons

• Doesn't "think" like Querki, so almost certainly opaque to our data. (Because we're too unstructured.)
• Relatively slow access. (Although how slow is hotly debated.)

MongoDB

The MongoDB site has an illuminating (and surprisingly fair) comparison of MongoDB and CouchDB

Pros

• Built into CloudFoundry.
• Available via a CloudBees partner.
• Reportedly very high-performance.
• JSON-oriented and designed for heterogeneous data.
• Would potentially allow us to do queries into our data.
• Has a reasonably strong list of deployments, which sort of underscores what it is good at.

Cons

• Architecture is not inherently reliable as CouchDB is. However, more recent versions seem to have journaling, which likely improves reliability by a lot.

CouchDB

Pros

• Cloudant offers a Couch-compatible CloudBees plugin.
• JSON-oriented, so fits our data.
• Inherently versioned, which fulfills a key use case right off the bat.
• Decent list of production applications.

Cons

• Scalability is iffy unless we go with the fancier (and expensive) CouchBase.

Cassandra

Pros

• Reportedly high-performance and high-reliability

Cons

• No apparent CloudBees integration
• Structure just doesn't match our usage, so we would use it very crudely. (Aimed at columnar apps.) Not as rigid as SQL, but not crazy-flexible as Querki is supposed to be.

Postgres

It turns out that PostgresDB has a newish data type called "hstore", which is an unstructured key-value set. You can apparently mix and match this with conventional columns.

Converting MySQL to PostgreSQL

Pros

• Native to Heroku, with hstore support.
• The hstore format is a potentially lovely compromise, letting me mix conventional indexed columns for the universal fields (Space, OID, Name), while using hstore for user-defined Properties.

Cons

• Don't know it as well as MySQL.
• Maybe a bit slower than MySQL?