Database as a Backend Web Service

The database is always the bottleneck. This is what all the admins of massive services tell in talks about their scaling efforts.

In short:

• Database used to mean SQL
• It is difficult to scale SQL CPU
• It is simple to scale Web-Frontend CPU
• The SQL philosophy puts the burden on Read by enabling very complex SELECTs and JOINs while Write is usually simple with short INSERT. Just the wrong concept in a massive world. We need quick and simple read operations, not complex reporting features.

Therefore many people step back from SQL and use other databases. Read more about the NoSQL movement. You have the choice: CouchDB, MongoDB, Tokyo Tyrant, Voldemort, Cassandra, Ringo, Scalaris, Kai, Dynomite, MemcacheDB, ThruDB, Cassandra, HBase, Hypertable, AWS SimpleDB, or just use Amazon S3 as stupid document store. Also SQL can be 'misused' as quick document/key-value oriented storage. It still has some key benefits.

Basically all you need is a key-value collection store with some indexing, alias document store. Whatever you decide: you are bound to it and this sucks. So, why not decouple the application logic from the database? Decoupling can be done in different ways. Traditionally you had a thin database code layer that tried to abstract from different (SQL) databases. Now, I need more abstraction, because there might well be a non-SQL database in the mix.

I decided to put a web service style frontend-backend separation between application code and database. This makes the DB a web service. In other words: There is HTTP between application and DB which allows for massive scaling. Eventually, my DBs can be scaled using web based load balancing tools. This is great. I can also swap out the DB on a per table basis for another database technology. Also great, because I do not have to decide about the database technology now and this is what this article really is about, right?

So, now I design the DB web service interface. I know what I need from the database interface. This are the requirements:

1. Database items (think: rows) are Key-Value collections
2. Sparse population: not all possible keys (think: column names) exist for all items
3. One quick primary key to access the collection or a subset of key-values per item
4. Results are max one item per request. I will emulate complex searches and multi-item results in the application (disputed by Ingo, see Update 1)
5. Required operations: SET, GET, DELETE on single items
6. Support auto-generated primary keys
7. Only data access operations, no DB management.

This is the interface as code:

1. interface IStorageDriver
2. {
3. // Arguments:
4. // sType: Item type (think: table).
5. // properties: The data. Everything is a string.
6. // names: Column names.
7. // condition: A simple query based on property matching inside the table. No joins. Think: tags or WHERE a=b AND c=d
8. 
   
9. // Add an item and return an auto created ID
10. string Add(string sType, Dictionary<string, string> properties);
11. // returns Created ID
12. 
    
13. // Set item properties, may create an item with a specified ID
14. void Set(string sType, string sId, Dictionary<string, string> properties);
15. 
    
16. // Fetch item properties by ID or condition, may return only selected properties
17. Dictionary<string, string> Get(string sType, string sId, List<string> names);
18. List<Dictionary<string, string>> Get(string sType, Dictionary<string, string> condition, List<string> names);
19. // returns The data. Everything is a string
20. 
    
21. // Delete an item by ID
22. bool Delete(string sType, string sId);
23. // returns True = I did it or False = I did not do it, because not exist, result is the same
24. }

I added the "Add" method to support auto-generated primary keys. Basically, "Set" would be enough, but there are databases or DB schemes which generate IDs on insert, remember?

All this wrapped up into a SRPC interface. Could be SOAP, but I do not want the XML parsing hassle (not so much the overhead). WSDLs suck. Strong typing of web services is good, but can be replaced by integration tests under adult supervision.

On the network this looks like:

Request:

1. POST /srpc HTTP/1.1
2. Content-length: 106
3. 
   
4. Method=Data.Add
5. _Type=TestTable
6. User=Planta
7. Age=3
8. Identity=http://ydentiti.org/test/Planta/identity.xml

Response:

1. HTTP/1.1 200 OK
2. Content-length: 19
3. 
   
4. Status=1
5. _Id=57646

Everything is a string. This is the dark side for SQL people. The application knows each type and asserts type safety with integration tests. On the network all bytes are created equal. They are strings anyway. The real storage drivers on the data web service side will convert to the database types. The application builds cached objects from data sets and maps data to internal types. There are no database types as data model in the application. Business objects are aggregates, not table mappings (LINQ is incredibly great, but not for data on a massive scale).

BUT: I could easily (and backward compatible) add type safety by adding type codes to the protocol, e.g. a subset of XQuery types or like here:

1. User=Planta
2. User/Type=text
3. Age=3
4. Age/Type=int32
5. Identity=http://ydentiti.org/test/Planta/identity.xml
6. Identity/Type=url

The additional HTTP is overhead. But SQL connection setup is bigger and the application is INSERT/UPDATE bound anyway, because memcache will be used massively. Remember the coding rule: the database never notices a browser reload.

Now, I can even use AWS S3, which is the easiest massively scalable stupid database, or Simple DB with my data web service on multiple load balanced EC2 instances. I don't have to change anything in the application. I just implement a simple 4-method storage driver in a single page. For the application it is only 1 line configuration to swap the DB technology.

I can proxy the request easily and do interesting stuff:

• Partitioning. User IDs up to 1.000.000 go to http://zero.domain.tld. The next million goes to go to http://one.domain.tld.
• Replication: All the data may be stored twice for long distance speed reasons. The US-cluster may resolve the web service host name differently than the EU cluster. Data is always fetched from the local data service. But changes are replicated to the other continent using the same protocol. No binary logs across continents.
• Backup: I can duplicate changes as backup into another DB, even into another DB technology. I don't know yet how to backup SimpleDB. But if I need indexing and want to use SimpleDB, then I can put the same data into S3 for backup.
• Eventual persistence:The data service can collect changes in memory and batch-insert them into the real database.

All done with Web technologies and one-pagers of code and the app won't notice.

Update 1:

Supporting result sets (multi-item) as 'Get' response might be worth the effort. I propose to have 2 different 'Get' operations. The first with the primary key and no condition. This will always return at most 1 item. A second 'Get' without pimary key but with condition might return multiple items. (Having both, a primary key and a condition in the 'Get' makes no sense anyway). The multi-item response will use the SRPC Array Response.

On the network:

Request:

1. POST /srpc HTTP/1.1
2. Content-length: ...
3. 
   
4. Method=Data.Get
5. _Type=TestTable
6. _Condition=Age=3\nGender=male
7. _Names=Nickname Identity

Comment: _Condition is a key-value list. This is encoded like an 'embedded' SRPC. A key=value\n format with \n escaping to get it on a single line. _Names is a value list. Tokens of a value lists are separated by a blank (0x20) and blanks inside tokens are escaped by a '\ '. Sounds complicated, but easy to parse and read.

Response:

1. HTTP/1.1 200 OK
2. Content-length: ...
3. 
   
4. Status=1
5. 0:Planta
6. 0:Identity=http://ydentiti.org/test/Planta/identity.xml
7. 1:Wolfspelz
8. 1:Identity=http://wolfspelz.de/identity.xml

I am not yet decided about queries with multiple primary keys. They could be implemented as

1. SRPC Batch with multiple queries in a single HTTP request, or
2. with a specific multi-primary-key syntax, similar to SQL: "WHERE id IN (1,2,3)".

The response would be almost identical, because a SRPC Batch response is very much like SRPC Array Response. Solution 2 adds a bit of complexity to the interface with a new multi-key request field. Solution 1 does not need an interface extension, but puts the burden on the data webservice, which must re-create multi-key semantics from a batch of single-key queries for optimal database access.

Update 2:

I agree with Ingo, that solution 1 (SRPC Batch) makes all operations batchable and has a simple interface at the same time. The trade off, that the webservice must detect multi-key semantics from a batch is probably not too severe. Clients will usually batch ony similar requests together. For the beginning the webservice can just execute multiple database transactions. Later the webservice can improve performance with a bit of code that aggregates the batch into a single multi-key database request.


Update 3:

In order to allow for later addition of type safety and other yet unknown features, I define here, now and forever, that SRPC keys with "/" (forward slash) be treated as meta-data for the corresponding keys without "/". Specifically, that they should not be treated as database (column) names. That's no surprise from the SRPC point of view, but I just wanted to make that clear. I have no idea why someone would use "/" in key names anyway. I find even "_" and "-" disturbing. By the way: ":" (colon) is also forbidden in keys for the benefit of SRPC Batch. In other words: please use letters and numbers, the heck.



Update 4:

I removed the "Database". "Type" is enough for the data service to figure out where to look for the data. "Type" is a string. It can contain "Database/Table".



_happy_decoupling()