Drizzle: a lighter MySQL

I believe by 'secure' he was referring to the point that it isn't running as a daemon that
could potentially be exposed to other systems on the network (sane defaults by the distros
could mitigate the risk some).

Also, if theres one central PostgreSQL/MySQL server running on the system for all the desktop
users, an exploit in the server would expose other user's data which wouldn't be possible if
each application simply used an SQLite database stored under ~.

In my OP I was more so thinking about something a little higher than SQLite, without being as
high as the traditional DBMSs. If the 'server' was per-user, and connected to the user's other
applications by something that is already supposed to be trusted (like DBus's session bus?),
it could help ensure the SQLite db isn't lost as easily (say, keeping a copy around from the
end of the last session as well as a transaction log?).

Then again, what do I know? I (obviously :P) have little experience with these database
systems, and none from a development PoV.

Indeed, multi-value databases in this sense predate the relational model.  You might as well
talk of 'pre-relational' database systems as 'post-relational'.

> Post-relational and MultiValue are synonymous. But no, it doesn't mean
> "multiple values per row", it means "multiple values per CELL".

My bad. I was talking about this thing, with postgres's Array types.

http://www.postgresql.org/docs/8.0/interactive/arrays.html

So, now we have established postgres as either pre- or post-relational, or possibly both...
;-)

Your talk about the query optimizer's unworthiness is unconvincing to me. Not all projections
are about fetching a single entity. Some queries are about fetching aggregates such as
statistics of all the rows in the database. It is not useless to have these queries run well,
too.

It would seem advantegous to be able to restrict the amount of data fetched about an entity to
the absolute minimum. For instance, in many databases you can build an internally maintained
"clone" of a table by indexing a set of its columns. When a query concerning that table
requesting only those columns is executed, chances are that the optimizer chooses to read the
data from the index instead of the table proper.

Multiple values per cell sounds like Amazon's "cloud" database, SimpleDB.
Actually the rest of your description sounds like SimpleDB too.

It strikes me as a good idea: easy to understand, and a good fit for a lot of web app
requirements.

But not all of the common ones.  Things like "show me all items matching criteria X and a
summary of their details", or the same but "50 per page sorted by Foo" - and show me how many
pages that comes to - are quite common requirements.

If that comes to 10000 items, it's going to be really slow doing it by simply scanning a
key->multi-value database and filtering/sorting in the app, and especially if you have to do
it every time they click "next page".  Whereas a SQL using window functions, and a backend
table/index which stores cumulative values as well as direct values, can do the same queries
rather fast.  (Not that all SQL databases do such things.)

OTOH for something like storing cookie state, user preferences etc., key->value type of
database is fine and nice and simple.

> Once I know the primary key, I can retrieve everything in one hit. Oh -
> and that information will include the primary key(s) of the owner(s) so
> I can then do a direct retrieval on them - and so on down the chain.

Round trip latency for each of retrieval in the chain?

I've done something similar with a web page built out of data stored in a very simple
name->value database.  The database round trip was about 1ms (over a network).

But the latencies added up, in our case to about 30s per page (yes, thousands of round trips),
due to chasing dependencies like this in the app: "fetch all items in range A..B (to make a
table of results in a web page).  [get answer].  for each item, using it's "current mode"
property just retried, fetch info about current mode C."

With SQL you typically fold dependency chains into the single query so the DB engine chases
them while it reads the file, merging and reordering block reads to minimise seeks, and all
the answers come in bulk with a single network round trip.  There are other methods, some of
them better, and different programming methods, but none of them really simple.

> And the filing system is (usually) a self-balancing hash so (even on an
> unmaintained system!)  a direct retrieval from a known primary key
> takes, on average, about 1.05 table accesses to get the data.

If you just do a direct access to a single primary key with a SQL database, if it has a
half-decent query implementation, it will access the data in similar steps and won't spend any
time optimising such a simple query.

A hash isn't always best, even though it takes only 1.05 table accesses.  If you want to
retrieve 1000 answers per second, and your table is bigger than RAM, that will not work with
hash and a magnetic disk...  You'll be lucky to get 100 answers per second.

But if the 1000 answers per second have some locality properties, then it can work with other
structures which maintain locality, like trees.

Locality is noticably important in Google's BigTable documentation as one way to improve
performance.

Even in a RAM based database, this can make a performance difference, due to difference in
speed between L1 cache, L2 cache and main RAM.

And decent SQL databases have balanced hashes as an option anyway :-)

> all the information about my car is stored in one "row" (or RECORD as
> it's called in the MV world), what is there to optimise?

The reason column-oriented databases have a niche in which they perform better than
row-oriented databases, is that sometimes it's a performance _downgrade_ to keep all the data
related to "my car" in the same place on a disk :-)

This is even true when you have big disks - scanning many records to calculate aggregate
values is still faster if you can read less.  And for single-value lookups, you can cache
more.

Summary: I agree with you that relational isn't always a good model.  I think XML-style is a
better fit for a lot of data.  But don't throw out the SQL model of giving the database
information about the structure of your queries, and it batching and sorting them together.
Round trip times and not parallelising queries adds up to be a big deal with queries which
involve a lot of data, even if it's just intermediate data (as in the "50 items per page"
example).