[development] Database / SQL future thoughts

[Vladimir Zlatanov]

On Tue, 2009-05-05 at 16:57 -0600, Kyle Mathews wrote:
> I'm definitely a database noob so I'm looking for correction -- but
> isn't the main advantage of CouchDB / Tokyo Cabinet etc. that it's
> much simpler to spread them horizontally across multiple machines in a
> similar fashion to memcache? 
no. their difference from the sql model is their advantage, for some use
cases. there is no panacea, if somebody tries to sell you that, remember
the snake oils of old.

> From what I understand, the main problem with MySQL / other
> traditional databases is that the complexity of your architecture
> increases dramatically as you go scale beyond one server as you have
> to deal with all sorts of problems with data replication whereas with
> CouchDB, it's much more straightforward to spread the data around. Is
> that correct?
All database systems, which support updates, have the same difficulties
regarding replication - they stem from the fact that the updates across
replicas are non-deterministic and you need to synchronise, which is a
tough job, each solution has its cons and has pros.

Why people use key/value stores  (plug your own) instead of sql? Well
sql is not a panacea either. SQL is good to use where your data roughly
fits the relational (sorry) model.

Take the example with trees. Parent-child relations. If you store the
tree directly, in some kind of a tree store, traversing it will
obviously be fast. Faster, than simulating it using n SQL queries. You
could roll some stored procedures, the way Celko advocates, but you will
end up with log n queries on the inside, rather than one. Then again. If
you can actually map your tree to a number scheme, and have the required
queries defined on those numbers, then you could probably do it using
only one query, probably quite fast. The key is to make the db do most
of the work. The problem is this doesn't work for most kinds of tree or
graph data :( Some mappings either don't  exist or are yet to be
discovered.