VoltDB first steps - schema

The first, and most critical, step for working with VoltDB is figuring out what table(s) will be partitioned, and how. So, the schema...

The case I'm looking at is for a hypothetical ticket booking agency, for very large events (rock bands playing large stadiums, etc.). There's three basic tables:

1. seat_info
2. booking_info
3. booking_seats

seat_info is a list of all of the seats, and whatever metadata is required.
booking_info is a list of the bookings, so far, and whatever metadata is required.
booking_seats relates seats to bookings, and events.

booking_seats is the table that's interesting; it will be the largest, and hottest, by far. The schema elements for booking_seats is below:

create table booking_seats (
  booking_id varchar(10) not null,
  seat_number smallint not null,
  seat_row int not null,
  event_id int not null,
  constraint booking_seats_unique_hash unique (seat_number, seat_row, event_id)
);

partition table booking_seats on column seat_row;

create index event_booking_hash_idx on booking_seats (event_id);

booking_id is a foreign key for booking_info.
seat_number and seat_row form a composite key into the seat_info table.
event_id will be a foreign key relationship "in the future".

seat_row is used for partitioning the booking_seats table.

The VoltDB user manual has an example of a flight booking schema that's been partitioned by flight_id, analogous to event_id in my example.  The use case here is different, and so the partitioning scheme needs to be different.

• event_id would cause all of the traffic for the ticket booking system would be directed to a single partition when tickets for that event were put on sale - which sort of defeats the purpose of partitioning the data in the first place.
• booking_id could be used for partitioning, but as it's not a part of the unique constraint, the constraint would be expensive to enforce.
• seat_number isn't a good choice for partitioning because buying multiple tickets is normal, and each transaction should be kept within a single partition, if possible.
• seat_row is my choice for partitioning, because most bookings tend to be in the same row, and because it's a part of the unique constraint that prevents seats being double-booked.

In theory booking_id, or even event_id, may make a sound choice for partitioning. For this example I really would like to have aggressive partitioning of the data to see what breaks stress VoltDB's capabilities.

This is a "not bad" schema; but the query for finding available seats is a bit ugly. I added the index on event_id, to optimise the query for available seats for a given event, but testing is really essential to see if it helps or hinders performance overall.

Google Spanner reflections

Google Spanner (warning: PDF) has made some noise recently, and I got around to giving their paper a quick read. I was happy to discover that it describes a solution to a problem I've been thinking about for awhile. I was happy to read that my musing were heading in the right direction. The key (as the Googlers are at pains to point out, and I was also considering) is how to handle distributed co-ordination of commits (in a scalable manner).

Spanner introduces the concept of tracking time by a combination of instants, intervals, and assertions about the two - on the understanding that there's uncertainty of the "current" time. As I read through the paper, I tend to think that there's no particular need for atomic and GPS clocks (although they're definitely desirable); so long as the confidence in the current time can be established. The effect of rising uncertainty in the current time would be a reduction in performance, I imagine...

It turns out that the venerable NTP protocol is able to provide measures of uncertainty; so one could re-implement Spanner without atomic clocks (at least, initially), and could then assault the various questions of electing "leaders" and manging reads and writes in a scalable manner.

Apache Zookeeper is the first thought for the leader election, but they talk about clusters of 4 machines being a large deployment, and about "within this data centre", etc. Let's reserve Zookeeper for a use case that fits better; something without Spanner's global ambitions. Spanner's architecture means that this is a key real concern. A closer reading of the paper is necessary to figure out what's involved here; and how it intimately it's related to uncertain timing.

The distributed reads and writes problem does look a bit like Ceph...

Spanner may not be so intractable, after all.