This is something I have thought a lot recently since I recently saw a project that absolute didn’t care in the slightest about this and used many vendor specific features of MS SQL all over the place which had many advantages in terms of performance optimizations.
Basically everyone always advises you to write your backend so generically with technologies like ODBC, JDBC, Hibernate, … and never use anything vendor specific like stored procedures, vendor specific datatypes or meta queries with the argument being that you can later switch your DBMS without much hassle.
I really wonder if this actually happens in the real world with production Software or if this is just some advice that makes sense on surface level but in reality never pans out. I personally haven’t seen any large piece of Software switch to a different DBMS, even if there would be long term advantages of doing so, because the risk and work to retest everything would be far too great.
The only examples I know of (like SAP) were really part of a much larger rewrite or update rather than “just” switching DBMS.
I suppose this is a hot take, but I’d never intentionally select a closed source paid database or programming language. Your data is the most valuable thing you have. The idea that you’d lock yourself into a contract with a third party is extremely risky.
For example, I’ve never seen a product on Oracle that didn’t want to migrate off, but every one has tightly coupled everything Oracle so it’s nearly impossible. Why start with Oracle in the first place? Just stay away from paid databases, they are always the wrong decision. It’s a tax on people who think they need something special, when at most they just need to hire experts in an open source database. It’ll be much much cheaper to just hire talent.
Meanwhile I’ve done two major database shifts in my career, and you are correct, keeping to ANSI standard SQL is extremely important. If you’re on a project that isn’t disciplined about that, chances are they are undisciplined about so many other things the whole project is a mess that’ll be gone in ten years anyway. I know so few projects that have survived more than fifteen years without calls for a “rewrite”. Those few projects have been extremely disciplined about 50% of all effort is tech debt repayment, open source everything, and continuous modernization.