Rethinking the limits on relational databases
craigkerstiens.com
craigkerstiens.com
Data structures are meant to last much longer than application code. Anyone that has worked on a long running system can attest to that; well defined data structures and table layouts will outlive any application code.
When I'm designing a system, I think I spend orders of magnitude more time thinking about data structures then actually implementing the CREATE/ALTER TABLE code for them. Planned properly, you can even do the ALTERs/CREATEs necessary to add columns in advance of any actual app usage (ie. "two stage" app deployment).
There is a place for "flex fields" or storing generic "documents" but legit use cases are pretty rare. When they are necessary, a single JSON column is usually enough. The example I generally use is an audit trail: The who (FK to user), what (event enum), and when (timestamp) are all strongly typed but you may want a JSON field for event specific data.
Oh and if anybody has every tried to do a data migration with a schema-less database ... well have fun with that. Either you bite the bullet and convert everything or you end up with a lot if/then/else logic littered through your app that will bite you down the road.
On data migration, I don't think the if/then/else version is even feasible. If there have been n versions of your apps, there are 2^n possible states any particular record could be in depending on which versions of your apps did and did not update it. I've seen Notes documents after a few years of this that are in such weird states that not even the dev team could say just what the hell happened to that doc or what correct (well, least bad) behavior of the apps would be, much less what the current versions of the apps would probably do. You can kind of get partway there with apps that have existed and been actively maintained for as long as any of your data has been there, but trying to write anything like new analytics over old non-migrated data is hopeless.
eg. WILL THIS VARCHAR COLUMN EVER BE BIGGER THAN N? How should I size it? I don't want to size it too small and then have to rewrite the column... But bytes are precious... Oh, dear!
Now I just make it a TEXT in Postgres and don't worry about it. Obviously you might have good reasons to limit the size of a text column for other reasons (security, interoperability... YMMV depending upon lots of factors), but it is nice to not have to think about it if you otherwise have no reason to think about it.
The actual relational bits of the relational database never bothered me and in fact I quite liked the abstraction they provided, but I really hated how rigid the systems were at the column level, which is now pretty much solved.
But for most real-world work... I find it hard to imagine preferring a document database.
That's easy. Renaming, splitting, changing data types, or altering character encodings... on in-use production data? That's harder, and you may need to take downtime.
On the other hand, document-oriented databases allow in-stream data model changes. More code, of course, but with care you can spread the cost of the upgrade over time.
I'm not a huge advocate for document-oriented databases, but there are trade-offs.
How did your project end up selecting MongoDB? That seems like exactly the sort of thing that it's not good for.
(I'm not just being rhetorical; were there other attributes of MongoDB that made it seem preferable to a relational database earlier in the project?)
EDIT: It's nice and quick for prototypes. Ironically, I'm building one with it now.
1. A persistent cache, or a compute-ahead read-only data store. 2. A write-only store for high volume but non-critical data, such as comments, log events, etc.
MongoDB should never be the primary source of truth data store. Nothing can replace a relational DB for that purpose, yet.
Even then, you're probably better off with Redis or memcached.
> 2. A write-only store for high volume but non-critical data, such as comments, log events, etc.
Just write to disk. It's very hard to beat flat file text for this use case and most of the time it's not worth it.
Definition? As in pre-aggregated data, i.e. materialized views or summary tables?
> ...this is a manual painful process today, but theres no reason this can’t be fully handled by PostgreSQL or directly within an ORM .
You mean like EF already does [1]?
> Add-Migration will scaffold the next migration based on changes you have made to your model since the last migration was created
For what it's worth, I don't use that feature. I prefer to use SQL Server Data Tools [2] to maintain a model of the database and use its schema and data diff tools to generate upgrade scripts. This is more due to the database pre-dating EF migrations but as well the schema is fairly complex so having SSDT (with its knowledge of nearly all SQL Server object types) do diffs against the actual database model is better than EF diffing its own abstract model.
[1] http://msdn.microsoft.com/en-us/data/jj591621.aspx
[2] http://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).a...
It will only do that for non-destructive changes, so if you remove a column (which would lose data), or make an optional field non-optional (which is ill-defined if you had NULLs), it bails out with a message suggesting the SQL you should run to migrate manually.
[1] I can't really call it an ORM, since it maps to Haskell record types rather than _O_bjects, and it works with NoSQL databases as well as _R_elational databases.
My approach is to maintain a stack of diffs to the schema in DDL that are hand-crafted and checked into source control, so that when it inevitably fails during QA there isn't a 2MB SQL diff to hunt through to figure out why.
When it comes to limits of relational databases some of the real limits are actually sharding, replication and high availability, which are all relatively more difficult to do on the popular YesSQL databases.
In any case, its pretty tiring to see NoSQL only refer to MongoDB and other document stores. Redis, Cassandra, HBase and Hive are all NoSQL engines ranked before the next document store, and IMO have a lot harsher performance penalties for incorrect usage than Postgres & friends. Given that Cassandra is a the second highest ranked NoSQL store which also (sort-of) enforces a schema, implying that the limits on relational databases are schemas is pretty bizarre.
Most devs don't need the power of sharding, which is why that benefit can never be felt. But the reality is this is probably the #1 characteristics (huge benefit) of NoSQL databases. Google and Amazon definitely paved the way for this movement, primarily b/c they dealt with tons of data. Its simply cheaper to scale out (distributed) than scale up.
You can't aggregate data with a relational database. But you can aggregate with (most) NoSQL databases (exception is graph dbs). Instead of building relationships, with NoSQL you're building composites. The huge benefit here is enabling sharding while, having your data all in one place.
Lastly, a relationship db is made up of tuples and sets of tuples. With a NoSQL DB you can have complex data structures. I think this is the point you we're trying to make re: Documents.
I still love relational databases. It's cool to have options though. Before, relational was the only way.
The question is how do we determine which db to use (or use more than one)? Ah, the beauty of polygot persistance...
Hibernate can do this (IIRC, it's been awhile), which is great for development mode, but I would never trust a framework/ORM to "auto-migrate" my production database.
if so... Please try RethinkDB, I think it's fantastic. If offers some interesting promises, and is by far the best noSQL DB that I have worked with (they actually fix problems with it, too, constantly).
Comparison from RethinkDB website (RethinkDB vs. Mongo/others): http://rethinkdb.com/docs/comparison-tables/
I think they're really up front with what they promise, instead of people just saying it's "web scale", and I think of them as an iteration after Mongo. Then again, I haven't done a super large amount of work in Mongo, so...
The biggest hangup other than the UX issues the author talks about is the fear of having to manually shard your data. There are a lot of promises about automatically sharding your data from NoSQL vendors but I have no idea if they actually are able to deliver on this. If you are just doing a K-V store, then of course sharding is easy, regardless of the database you are using. I don't think there is really a silver bullet here, just maybe better tooling and removing redundant manual steps. You still need to think carefully about data access paths and queries and so on. Any additional sharding-centric features of NoSQL databases missing in mainstream databases could presumably be added, if they don't exist already with poor UX.
For example PostgreSQL replication, wal log archiving, etc works great but it's still a bit tougher to set up than the more magical cluster auto discovery/configuration/sync stuff you see in things like elasticsearch. It would be interesting if the PostgreSQL people made a real push for a release to tighten up the operational UX of the product to be more human friendly instead of adding on more core features. (Not that I'm complaining, it's great!)
Original author here. Yes this is definitely another real hangup, though I semi-intentionally left this one out as magical sharding is still a bit unclear of how well it works depending on who you talk to.
> For example PostgreSQL replication, wal log archiving, etc works great but it's still a bit tougher to set up than the more magical cluster auto discovery/configuration/sync stuff you see in things like elasticsearch. It would be interesting if the PostgreSQL people made a real push for a release to tighten up the operational UX of the product to be more human friendly instead of adding on more core features. (Not that I'm complaining, it's great!)
A big plus one on all this as well, and we're doing what we can to push it forward.
It is a bad language.
EDIT: It is completely preposterous that SQL injection is even a thing.
def insert_person(first_name, last_name)
sql.query("insert into persons values(#{first_name}, #{last_name})")
end
This is what binding variables is for, but to use them you're either writing for specific platforms (PSQL, Oracle SQL, etc), or you're using middleware that hides the raw SQL from you.