However, re: document store, I've been bouncing Postgres around in my head for that: all required columns (ie, columns that always exist, or will be queried on) are normal SQL columns, everything else is stashed in one or more hstore columns.
This has the same problem as most NoSQL solutions: You don't get the benefit of a schema, and schemas are damn useful things.
The alternative I've also been bouncing around in my head is multiple tables: objects (rows in the "primary" table) have traits (rows in "secondary" tables with identical primary keys) that break out columns according to what they do.
Having 9000 columns in a single table is a bit unwieldy.
Everything does. Data without schema are just noise.
The real question is whether you'll enforce it at the DB level (and suffer from inflexibility) or enforce it at the code level (that, obviously, should know what field is what, and which fields are available, e.g. enforcing a schema at runtime) and get potentially corrupted data, management overhead, etc.
Love that concise adage. Will probably adopt it when explaining databases to novices.
Any good examples?
You may know some invariants, but much can change without notice. So you want to be able to work with the structure you have without preventing non-conforming data from entering your system.
In some cases schema conformance is just delayed, in other cases it is never achieved completely or not even a goal.
Can you give a more specific example?
Similarly, events.
The source entity, more often than not, is also complemented with other data that needs to remain a part of the persistence layer.
A similar thing happens when you retrieve data on securities and companies from various exchanges, from the SEC, from national registries all over the world or you try to include XBRL from different countries.
And then you often have documents (like quarterly reports) that contain structured fields and tables but not in a formally specified syntax. You don't know exactly what fields will be in those documents before you parse them. So you parse the documents, store key/value pairs, and then you clean them up gradually.
There are tons of situations like this in data integration. It's a never ending cleanup and merge process. You can use RDBMS for all of that but they're not always the best tool for the job (but they are still my preferred tool most of the time).
We need to store the key/value pairs and explore them in a reasonably productive fashion (i.e using queries) in order to come up with machine learning algorithms. And any new algorithms we write need access to all historical data.
Imagine you want to store random metadata from a digital camera picture, or perhaps even XML/HTML attributes. You can create another table and add each new attribute – join on query – but if you don't plan to search for that data directly, it's easier to skip normalisation and dump the original set into a JSON(B) or HStore field. You don't have to add every possible attribute to your data model or schema, you can carry data along and not analyse it if it's not relevant to you.