Hacker News Reader

Your many accounts trolling this thread are a serious abuse of Hacker News, and we've banned all of them. Please don't do anything like this here again.

We detached this subthread from https://news.ycombinator.com/item?id=11859027 and marked it off-topic.

I have been a DBA for 20 years, Oracle, SQL Server, Sybase, Informix, Postgres, MySQL/MariaDB... About the only big name I haven't had serious experience with is DB2. We did hot-releases into Prod routinely.

I have absolutely no idea what a "SQL migration" is or why it would be so hard. I assume it's just a bogeyman made up by the MongoDB snake-oil salesmen. It is certainly not something "traditional" database people actually do, or worry about.

> I have absolutely no idea what a "SQL migration"

Transforming data to conform to a new schema. It's a tedious process that could use some automation, but ditching schemas because data migrations are tedious is like ignoring traffic rules because you're in a hurry.

> I assume it's just a bogeyman made up by the MongoDB snake-oil salesmen.

It's just like dynamic type marketing: “If you never define the structure of your data, you never need to worry about it!” The only problem is that it isn't true.

Oh, that doesn't happen much, because serious organizations have dozens of apps connected to a DB and no-one would be crazy enough to big-bang change them all at once to use a different schema. Adding columns and tables tho' is easy, populating new tables from existing, replacing tables with views, no problem.

I'd argue the inverse. Serious organisations shouldn't have lots of different apps reading from each other's databases, for the reason you suggest above. It makes changes difficult and the application landscape fragile. It also encourages not having well-documented interdependencies between your apps (and consequently no way of knowing what the impact of any given change to an app will have).

It's not uncommon however in large organisations with mature applications, without strict IT policies.

Complex applications that are undergoing active changes shouldn't be constrained in this manner, and in that context a SQL migration is more of an in-place upgrade of the data structures that hold the application data between different versions of the application.

Adding columns, adding tables, replacing columns with other columns and managing how the data should look in the new schema, based on the 'old' data structures.

They are not "each others" databases tho'. It is "the database", deliberately chosen as the integration point. The alternative is a horrific tangle of replication, or a vast undiscoverable landscape of tiny APIs.

That sounds horrible. No wonder you've never done a migration - it's been made impossible. Why would you not hide the database behind an API, so that you CAN migrate it, change it, etc? This is like not having a separate data layer in your application, except it's across many applications all at once.

What API do you suggest? Bear in mind it needs to provide the same results and enforce the same constraints for everything from COBOL to Java to Excel to ColdFusion to Python to R and any other future language. I mean you could write your business logic in all of them, and maintain it in all of them, but that would be insane.

These are solved problems, have been for decades.

Yes, and the way you solve it is by having an API that holds your business logic. That's how you avoid duplicating business logic in all those applications. Is your database just chock full of insane triggers and functions?

Business logic doesn't belong in a database. data does.

That API is called ODBC.

Or you can rewrite all the third-party apps your organisation uses now and in the future to use your own.

Doing large incompatible migrations is unavoidable eventually, so avoiding them doesn't solve the problem either it just postpones the problem until the entire enterprise has to move to an entirely "new system" at fantastic cost. For some reason the old system never really dies so after the multimillion dollar migration the old zombie system still supports some business functions, leading to that replication anyway.

Both solutions are unpalatable: "the database is the integration point we can't ever change" is bad and "no apps have a central ground truth database" is also painful.

I'd prefer the latter with small APIs and more duplication, than the common enterprise solution with a massive holy unchangable db directly serving apps.

Its not unchangeable. That's the point. You can easily add things without needing to do a "SQL migration".

Yes. But "adding" is a pretty small subset of changes. If you need to do even a trivial migration like the classic "merge first and last name columns into a single column" is pretty hairy if you have a dozen legacy apps that assume "customers" have "firstname".

In the end it means you give the data an elevated role in the system, and you write the code around the data. When the code would be cheaper to write or maintain with a different data representation - you can't. It's a comfortable situation for a while, but when you eventually grow out of the current system it's a world of hurt.

Like I said - it's two bad (non) solutions but I very much prefer duplication, isolation, inconsistency, a forest of small obscure APIs etc. where I can add/remove/normalize/denormalize/add caching etc - to "the data is the ground truth and the schema we can only change through addition" non-solution.

> In the end it means you give the data an elevated role in the system, and you write the code around the data.

That's how things are meant to be. The structure of a program naturally follows that of the data it manipulates.

> When the code would be cheaper to write or maintain with a different data representation - you can't.

Who says that? The real problem is that most programming languages aren't capable of integrating database schemas into the type-checking process, so when you change a schema, it's difficult to make sure applications follow through in a consistent manner. That's what makes change unnecessarily risky and painful. But it's possible to do better than that: http://impredicative.com/ur/

In such a scenario, none of the apps "own" the data - the DB is a primary source of truth and consistency is enforced there instead of relying that the code in the many apps will match.

Application logic can undergo active changes and refactoring without changes to the data permanent storage, indeed, a 1-to-1 correspondence between DB and application data structures isn't expected. It's just as with backwards compatibility with file formats, changing the application version shouldn't require incompatible in-place changes to the backing data persistence layer - you can need to add some new features, but the old version of the app should work fine with the updated DB.

> None of you have any idea what you are talking about even though you are supposed to be hackers.

You heard it here first folks. None of us know anything.

> You heard it here first folks. None of us know anything.

You've just taken the first step toward's web-scale enlightenment.

There is no spoon?

There can be only cloud.

Also, the cloud is Turing complete and has infinite tape.

The Eternal Mainframe in the sky. With diamonds.

http://www.winestockwebdesign.com/Essays/Eternal_Mainframe.h...

I'm not going to comment on the content of your posts - others have done that. But you might want to check your tone. You sound like a petulant teenager offended by the mere notion that someone might think you wrong.

It honestly makes it really hard not to dismiss whatever you say out of hand. If you don't really care about people taking you seriously then by all means keep it up, but if you're trying to participate in a grown up conversation, you might want to stop throwing tantrums. You know, the basic rule of human interactions, show people the respect you expect them to show you?

> Unless you have never written migrations in SQL before you would know that they are even scary and a big bunch of sql

I've written a ton of SQL migrations this year, none of them are scary or even really count as "big bunch of sql". If this describes your code, you should probably stop and rethink what you're doing, because that's bad practice.

The difference between, eg, merging your "first_name" and "last_name" fields into a single "name" field with Mongo or a SQL DB is basically that the SQL DB has better tooling so it'll be cleaner, faster, and more concise.

> Now in your migration script you have to hack SQL and language-based ORM commands

Also false. Nothing requires you to use the ORM commands if they don't make your life easier; literally last week I wrote a migration for some ORM based code using pure vanilla SQL because it was more appropriate. If you know what your ORM is doing, it's trivial. And if you don't, well, you have bigger problems.

> Actually they can be just as explicit and documented.

With a relational DB: Check the table schema.

With mongo: Dig through the app code and try and reverse engineer what fields the code is expecting. (Or pull up a few documents and see what fields actually exist, then ponder how you know whether you're looking at document that uses the latest schema, or maybe an outdated or dead document using an outdated schema. Also, how do you know what optional fields might be missing? Or what possible values the currently null fields might have? Good luck with that.)

> Mongo even has schemas now I think.

No. Mongo still lists flexible schemas as a feature, ie, they do not enforce any specific schema.

> SQL constraints are very limited and literally every application has additional constrains

Yeah, but what relational DBs are very good at is enforcing foreign key constraints. If your data is relational at all (and let's be real, very, very little world data isn't at least somewhat relational), you'll need it, and Mongo doesn't have it. (Well, Mongo doesn't even have foreign keys, but it lacks the equivalent feature for dealing with denormalized data too.) Managing to set some non-numeric characters in your phone number field when some of your app code contains a hard assumption it will be numeric is bad, yeah. But when you start screwing around with relations, foreign keys, failing to properly propagate changes to every copy of a denormalized data structure...oh man, you can spend days trying to untangle that mess. And it's a class of error that relational DBs don't have unless you misuse them badly.

In short, you seem to be suggesting that as long as you're blindly using an ORM, Mongo is almost as good and flexible as Postgres. Which...sure, okay? I guess?

what relational DBs are very good at is enforcing foreign key constraints. If your data is relational at all [..]

Nitpick: the "relational" in "relational database" is not a reference to the concept of foreign keys, but to "relational algebra", the mathematical model for regular data structures that SQL is based on.

https://en.wikipedia.org/wiki/Relational_algebra

> SQL constraints are very limited

Oh really? What exactly cannot be expressed in PostgreSQL check constraint, given the fact you can write it in any programming language you prefer?

Arbitrary constraints which look at multiple rows require serializable isolation to work under concurrency, and most people do not run their databases at serializable. But you get pretty far with single row check constraints and exclusion constraints (exclusion constraints can be used for checking against range overlap).

There's no need of serializable level of isolation in common case. It depends on particular data model, particular access pattern, particular database implementation (MVCC or blocking, and how exactly transaction isolation levels are implemented - e.g. Oracle's "serializable" is closer to PostgreSQL's "repeatable read").

Anyway, any sufficient modern RDBMS implementation provides pretty good level of performance even with serializable isolation level, thanks to decades of tuning and research in the field.

Not in the common case, but it is necessary in the general case. One of the main motivations I have heard from the few people actually using serializable in their systems is the ability to enforce arbitrary constraints under parallelism.

And, yes, performance should in general still be good, but there is less knowledge out there about how to solve the performance issues specific to serializable since there are few people who use it (at least in the PostgreSQL world).

Well, not so many people use serializable in their systems (in the whole system, for every transaction) and that is common and reasonable approach. They use the minimal isolation level for particular transaction that keeps their data consistent. Most widely used safe default is repeatable read, in PostgreSQL terms.

Broadly speaking, of course there are some quirks in the field, let's start from "A critique of ANSI SQL isolation levels"[1] by Jim Gray et al., an author of highly respected fundamental book about transactions [2]. But the very kind of problems discussed in RDBMS world, is a rather contrasting with an "ACID? why do we need it?" attitude that is so often in the world of "web scale NoSQL".

[1] http://dl.acm.org/citation.cfm?id=223785

[2] https://www.amazon.com/Transaction-Processing-Concepts-Techn...?

> "using serializable in their systems is the ability to enforce arbitrary constraints under parallelism"

That's a funny way to say that. :-) Because what it really means is, "wanting to enforce a total lack of parallelism when under parallelism." I mean I get what you're saying, it was just funny to read.

Can you give a use-case for such a constraint that can not be avoided by a better data organization? I'm not sure why you would deliberately design your data with constraints based on other row contents.

Also: how do you enforce such constraints with MongoDB exactly? If the answer is "do it in the application", then your answer applies to relational databases too.

A common example is making sure that every event in a bookkeeping system balances to zero (e.g. three rows: -125 EUR bank, +100 EUR office materials, +25 EUR tax). To enforce this at the database level you either need to run at SERIALIZABLE, take a table lock, or do something horrible with your database structure (like putting all rows of the event in a JSON blob).

When I have solved this case I have done it in the application by making sure we are only using a small set of carefully audited stored procedures to modify the event table.

MongoDB does not even try to solve this kind of problem, and I am the wrong guy to ask if you want someone to try to make a case for using it anywhere.

A common example is making sure that every event in a bookkeeping system balances to zero (e.g. three rows: -125 EUR bank, +100 EUR office materials, +25 EUR tax). To enforce this at the database level you either need to run at SERIALIZABLE, take a table lock, or do something horrible with your database structure (like putting all rows of the event in a JSON blob).

Or you deny direct table inserts, and provide a stored procedure for inserting multiple rows in one transaction. But I see how it could be useful in the generic case.

Which is exactly what I did when I had to solve the problem. :) And you are right in mentioning that the fact that you can restrict modification of the database to only certain stored procedures is a great help here.

With on commit triggers or deferrable check constraints it would be easier, but in Oracle you could use a materialized view with "on commit refresh" and add triggers to this MV.

You don't have to avoid such constraints if they are natural to data model actually. It's much faster (and safer, thanks to transactions) to check new data against some old data when the checking code is as close to the data as possible, instead of make a couple additional requests between DBMS and the business logic layer.

I've just commented with pretty much the same as you're saying.

Some people sound like they haven't even tried Mongo.

It's great that PG has JSON support now, but a few years ago people were having together stuff on hstore, storing their geo objects as binary blobs that you can't read without extensions etc.

SQL databases have been playing catch up with JSON, MSSQL being the worst. Even with JSON support, it's still a pain looking at some SQL queries that have to be written.

If postgres does it for you, don't go bashing everything else that tries to be an alternative I feel.

> If postgres does it for you, don't go bashing everything else that tries to be an alternative I feel.

People bash on MongoDB because they care. People just want to raise awareness that there are two types of MongoDB users: Those who understand it is a deeply broken system, and those who haven't used it thoroughly. The truly careless and anti-social approach would be for people to leave no negative comments about MongoDB and let others fall into the same trap.