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.
In almost 30 years I’ve never seen anyone actually switch databases underneath an existing product. I have worked at one place where generic database APIs were required because it was a product that supportedf multiple databases, but no individual customer was really expected to switch from one database to another, that’s just how the product was written.
I have heard of this happening, but it’s the kind of thing that happens in one of two scenarios:
Very early in a product’s lifetime the developer (probably a startup) realizes the database they chose was a poor choice. Since the product doesn’t even exist yet, the switching cost is low, and generic database use wouldn’t have helped.
A management shakeup in a very mature product causes the team to switch databases. This is, as you observed, usually part of a major rewrite of some kind, so lots of things are going to change at once. Also–critically–this only happens with companies that have more money than sense. Management doesn’t mind if it takes a long time to switch.
It won’t go smoothly, at all, but nobody actually cares, so generic database use wouldn’t have helped.