Best database choice for a highly scalable web ticketing system?

Question

Essentially, we are developing using Ruby on Rails and want to make a highly scalable ticketing system. Essentially we will have a small database (containing event details and tickets sold) that will spike with large numbers of reads (users visiting the site, up to 1-2 million per day) and a low-ish level of writes (write when a user requests a ticket, and when they buy it).

The writes need to persisted to disk, but we are keen on keeping the whole database in memory (perhaps using memcached). Right now we are going to build this on Mysql (activerecord on RoR).

Question is, will this setup be scalable for our future needs where we may need to organize dozens of events with 10s of millions of hits.

Answer 1

On a personal note, in the case of a ticketing system, I'd have my eye on full text search functionality and bitmap index scan (i.e. the ability to single out the one or two disk pages that might contain rows that fit for idx1 and idx2 and idx3) functionality much more than raw performance. But that's just me -- ticket searches in trac, lighthouse, redmine etc is just dismal in my experience.

By this criteria, as well as performance, Postgres fits the bill quite nicely. (And it's available on heroku.)

Answer 2

Scalability is factor for every business that expects large growth. Ultimately only you and your team can answer the question with whatever is right for your pocketbook and time considerations. The best we can do is offer some options.

You could use a master MySQL database for the writes and then implement replication to create multiple, read-only slaves.

Or MySQL for the write database and then create a custom ETL application to convert the transactional data into read-only and place it into a NoSQL database like Cassandra. I've found Cassandra to be optimal for tons and tons of reads (which is great if people are performing searches on your content), but it will require you to be pretty savvy in the database realm.

Answer 3

This sounds like the kind of thing some of the NoSQL databases excel at. The key here would also be to ensure the application architecture that sits in front of the database is designed well too - sounds like a classic CQRS (Command & Query Responsibility Segregation) usage pattern. This will at least help performance irrespective of the DB selected.