Why I love databases

It's always fun the first time. Good luck.

Some notes from my experience implementing AP:

- Easiest way to support scaling out is pre-sharding an entire database at the beginning, so there will be no need to ever reshard it. Like have 16 shards spread manually across all nodes and put into configs which nodes synchronize with which. Once you add another node simply move some shards to that node and be done with it.

- Synchronization is easy with hash/Merkle trees but they don't need to keep in-memory hashes of every single record or recalculate them on insertion or deletion. Instead every time new record is inserted its hash should be added arithmetically to the one stored in particular leaf. Same on deletion, but subtracted arithmetically. No scanning of other records for no reason or anything like that.

- Jitters are super important. Synchronizing data or doing anything periodically will cause lags if you forget to schedule at random times.

- Also, don't forget that random disk accesses are slow and organize your data in such way, that disks are read sequentially for most of the bulk access patterns, like during synchronization.

I love databases, but I loathe SQL. And no, I don't mean NoSQL is better - that's throwing out the baby with the bathwater.

To me, SQL is the Common Lisp of relational languages - a brilliant invention of its time that has since long-overstayed its welcome and should be replaced by modern considerations of the problem it solves. The difference is that there are a million rethinks and descendents and redesigns of LISP out there that happily threw out the mistakes and made great strides in the language. You could argue that every modern programming language is a descendant of Lisp thanks to the prevalence of great concepts like lexical closures. SQL, on the other hand, has a teeny tiny few spiritual fringe descendents like the various attempts at Date and Darwin's "Tutorial D".

I love the relational model, but who says the only way to manage the relational model is this hoary old thing? It's immensely frustrating that every implementation of SQL bolts on a tacky and half-assed procedural language, but doesn't solve simple underlying frustrations.

Simply accessing related objects is immensely wordy for a "relational" language. In an algol-derived language, I can say Group.Manager.Person.Address.PostalCode to walk the graph. In SQL, I have to deal with a zillion joins.

Yes, some SQL variants let you join by the foreign key name to make the join a little more terse, but it's still hairy compared to every modern functional or procedural language.

And the APIs - maybe the reason so many sites have SQL injection problems is the hideous APIs. Ever tried to build a WHERE IN (id1, id2, id3... idN) statement with a proper parametrized queries? Holy crap what a icky mass of boiler plate. I mean, it's not a hard problem, but how many times have you solved it, and how many times have you found a tedious bug in your solution? Just give me a proper way to concatenate the parametrization inline with the query FFS.

  db.RunQuery("""
    SELECT * 
    FROM MYTABLE 
    WHERE ID IN + " + db.SomeParameterListFunc(a, b, c) + " 
    ORDER BY HOLYCRAP_WAS_THAT_SO_HARD"
  """);

The above syntax would be trivial in any language with operator overloading on the "+" sign, on the off chance that your SQL dialect is so messy it's impossible to safely build a properly-escaped initializer for the list containing a,b and c in text form.

And that's not even getting into real actual first-class language support like ORMs give you.

And speaking of APIs, the fact that a single "SELECT" is the baseline operation... that you work on one resultset at a time. I don't want a single pile of rows. This is not an excel spreadsheet, it's a relational database, and that means I want a graph of data. I don't want to write three queries to get my Customers, their Personnel, and their Addresses, nor do I want a single row of CustomerPersonnelAdddresses. Once and Only Once is good for the data, why the heck isn't it good for result sets?

Where's the code reuse? Why can't I have a pile of SELECTs and a pile of WHEREs and combine them however I see fit? Oh right, I can use a VIEW... but see the previous point, a VIEW is a single glorified Excel spreadsheet, not a proper graph of data. If I want to bundle a bunch of SELECTs together, I have to just write a stored procedure, but then I can't use the proc with a JOIN statement against my VIEW that provides a custom WHERE clause. You could do something monstrous with table-valued parameters, I guess, but those aren't generally well-supported at the API level. This is not a hard problem in every modern language (except Go, of course - yes, you do freaking need map/reduce/filter).

Namespaces. Real, actual, organizational tools for your giant list of 9000 tables and their related objects. No, schemas don't freaking count - you can't nest them and they're overly tied to the security model - using schemas for organization instead of security leads to madness, besides the fact that you can't nest them.

And of course, so many common problems simply aren't nice to work with using the relational model. How do I make a nice audited row where I have the full history of all the row's changes? Well, I can insert it every time, but that's a lot of wasted space. Yes, again, there are ways to do this, but it's something I'd expect to come out-of-the-box since it's such a common problem. Common problems should be solved by the standard library. But SQL can't solve things like this with the standard library, because a SQL standard libraries are limited to crude things like functions and views and procs and not actual large-scale reusable constructs. It's like a programming language where they gave you a bunch of general tools for manipulating unicode points and dynamically sized arrays but no coherent "string" object.

Or trees. Holy crap, you have a "relational" database where a relationship like a "tree" is a nightmare to actually query out! I know that's not what "relational" means, but still - this ins't exactly a rare edge-case, y'know? But it's not in the standard library because the standard library is limited to crude objects like data-types, functions, procedures, etc. that work below the row-level. Any concept of reusable schema concepts is completely left off the table.

/rant

How many programs in a typical Unix installation are complex? Not many. Most of designed around the concept of pipes and filters. They don't need a database; they are transformative maps.

Complex systems often require complex data storage. If you have more than one process storing information into a file or set of files, you have to control that type of information. When faced with the option of writing complex, ACID like code or dropping in a PG or MySQL or SQLite option that takes from 1 MB to maybe 128 MB, most will go with using existing tools at the expense of RAM.

Is Oracle the solution for every project? Sure, if you're Oracle. However, one shouldn't throw out databases for alternatives simply because they are a bit complex. In fact, I think that raw file IO is more complex because it is not the norm.

Seems like there might be an opportunity here. Definitely a lot of hard work, and adoption would be pretty hard, but you have to start somewhere. Is there nothing quite like what you described yet?

Jerry, would love to see your list. Have any links to your database work?