Wrong. SQL databases (except for SQLite) are almost completely interchangeable because they are all based on the same relational model and they all implement the ANSI SQL standard with only minor deviations. If you have a lot of stored procedures and triggers -- executable code embedded in the database -- you will have to rewrite that.
Oracle is in a world of its own in a lot of ways, but if you are moving to or from Oracle you should have the required resources and expertise in your budget.
If you are referring to the well-known cost of COUNT(*) in PostgreSQL (or MySQL with InnoDB, for that matter), or different ways to handle full-text searching, I agree that there are differences you have to deal with, but they are not usually a big deal.
Contrast the fairly easy and routine process of migrating between MySQL, PostgreSQL, or SQL Server with the huge amount of work involved changing your application code from MongoDB to anything else.
SQL databases (except for SQLite) are almost completely interchangeable because they are all based on the same relational model and they all implement the ANSI SQL standard with only minor deviations.
They only all implement the ANSI SQL standard for certain values of "ANSI SQL standard." Those values being SQL-89 or perhaps SQL-99.
Beyond that it's a mess. SQL Server didn't get around to doing a really good job with window functions until this year. Last I checked, MySQL still doesn't do any number of useful things, such as WITH or FULL JOIN. Upserts and bulk inserting are different for every DBMS on which I've learned how to do them. And so on and so on. There are any number of ways you can get yourself coupled to a particular DBMS if you go beyond the core set of basic SQL features.
In other words, SQL databases are only trivially interchangeable for trivial cases.
I agree that there are numerous inconsistencies. I just have a different definition of "trivial cases" than you do. The kinds of things you mention -- real issues that come up in real applications -- fall outside of what most databases are doing in the kinds of web apps that I work on. I haven't had to deal with Oracle vs. SQL Server bulk load problems or Oracle's unique implementation of NULLs in years. Not waving them away, just saying that these kinds of issues are not all that common because most real-world databases are trivial cases.
>"Not waving them away, just saying that these kinds of issues are not all that common because most real-world databases are trivial cases."
If this is the case, then, it's only because database application developers go out of their way to write compatible code. Things like upserts are not trivially compatible between vendors, and so are avoided by people trying to write portable applications.
Similarly, let's look at SQL 2003 structure data type inheritance and how this is implemented in Informix, Oracle, DB2, and PostgreSQL. Ok, so it isn't in PostgreSQL, and Informix allows tables to be inherited as well as types, and will even happily return result sets with different numbers of columns per row (google "Informix jagged rows")... And Oracle and DB2 look pretty close in this way until you get under the hood.
Not wrong. As a DBA with 15+ year experience, I can tell at a glance what an application was first developed against (Oracle, SQL Server, etc) or even where a dev started their career. You might as well say any OS is interchangeable as they're all just a kernel running processes.
I didn't claim RDBMSs are interchangeable or that there are no differences. I disputed the statement that "databases are only trivially interchangeable for trivial cases." That has not been my experience: changing out one RDBMS for another is not trivial, but it's easier and more controllable and predictable than changing out application code.
I also disputed the notion that reading a book first would enable someone to choose the right tool the first time. There's no substitute for experience and real expertise, and you only get that by trying things and making mistakes, or at least learning from other people's mistakes.
. . ."databases are only trivially interchangeable for trivial cases." That has not been my experience: changing out one RDBMS for another is not trivial, but it's easier and more controllable and predictable than changing out application code.
Keeping the punctuation but replacing the words with symbols:
"X." Not X: X, but tangential comparison.
Sounds like you actually agree with me, but don't see that as a reason not to make much hay about disagreeing with me. :)
> Wrong. SQL databases (except for SQLite) are almost completely interchangeable because they are all based on the same relational model and they all implement the ANSI SQL standard with only minor deviations.
Wrong again. Here's a very trivial example. To index or not to index? On MySQL InnoDB you usually get a major performance benefit out of indexing just about everything you want to search on later. On PostgreSQL you usually get a major performance benefit out of indexing columns only as needed later. This is because of internal design differences, sequential scans through a table are much cheaper on PostgreSQL (in MySQL it has to scan a table in key order, not physical order which means no OS prefetch). Also non-PK index scans are faster on PostgreSQL though PK index scans are slightly faster on InnoDB (basically the table is contained in the Primary Key index there).
Contrast the fairly easy and routine process of migrating between MySQL, PostgreSQL, or SQL Server with the huge amount of work involved changing your application code from MongoDB to anything else.
So suppose I want to move my database which uses PostGIS and pgcrypto to MySQL or SQL Server. How would I do that?
Just in case you weren't already aware, the count(*) issue should be significantly improved for some queries in postgres 9.2, thanks to the addition of covering indexes.
There was discussion on pgsql-general a bit before the release. It appears that physically sequential scan of a table is about as fast as key-order sequential scan of a covering index.
To speed up a lot, I think you'd need to be able to scan an index in physical order which is not currently supported.
Now if you are using SSD's yeah, you could tweak the planner settings enough to cause it to make an index scan perhaps, and maybe you'd see a modest performance increase, but even there it appears the operating system's prefetch logic really does come to the rescue of a sequential scan of a table in physical order.
Oracle is in a world of its own in a lot of ways, but if you are moving to or from Oracle you should have the required resources and expertise in your budget.
If you are referring to the well-known cost of COUNT(*) in PostgreSQL (or MySQL with InnoDB, for that matter), or different ways to handle full-text searching, I agree that there are differences you have to deal with, but they are not usually a big deal.
Contrast the fairly easy and routine process of migrating between MySQL, PostgreSQL, or SQL Server with the huge amount of work involved changing your application code from MongoDB to anything else.