Programming

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.

Oh yes! Yes yes yes. I worked at a company where the DB admins were tasked with moving away from OracleDB to MariaDB and are now moving to PostgreSQL. They have thus been migrating for about 20 years now. It is a huge expense with multiple teams across the business dedicated to this sole task.
One would think the devs learned from the first migration not to write DB specific queries, but alas... To be fair though, managers also pushed for quickly written code and hired a lot of junior engineers straight out of uni. The turnover rate was amazing back then (1-2 years max) and probably stayed about the same or got worse.

At another company, the client used some old Microsoft database on-prem, wanted to migrate to PostgreSQL, but had trouble with their datacenter (couldn't scale IIRC) and had to migrate to the cloud first. Of course the only vendor of the old version of that Microsoft DB is Azure and by the time their migration to the cloud was complete, the DB was not officially supported anymore. So now, they are paying out of their ass for premium support as, IINM, they are among the last clients still running that version of the DB.
The entire code base is handwritten SQL in the flavor of microsoft's DB which is of course incompatible with anything else.
Their migration started close to a decade ago and by the time I left the end was nowhere in sight.

So yeah, staying DB agnostic is not a joke.

CC BY-NC-SA 4.0

Yes, I have seen a product being swapped from oracle, to oracle + PostgreSQL and then only PostgreSQL

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:

1. 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.

2. 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.

30 years experience running enterprise development teams here. Switching databases has happened once for me, Sql Server to Postgres. We were busy with a huge rewrite of something existing, approx $100 million project for a major company.

The instruction to switch dbs came midway through the project, basically on the whim of the CIO. Luckily we were only lightly impacted by db specific features on a couple procs, but code base was abstracted away - which made it achievable.

Sure - for example we migrated all our stuff from MySQL to MariaDB.

It was completely painless, because all of the source code and many of the people who wrote that code migrated to MariaDB at the same time. They made sure the transition was effortless. We spent a months second guessing ourselves, weighing all of our options, checking and triple checking our backups, verifying everything worked smoothly afterwards... but the actual transition itself was a very short shell script that ran in a few seconds.

I will never use a proprietary database unless it's one I wrote myself and I'd be extremely reluctant to do that. You'd need a damned good reason to convince me not to pick a good open source option.

My one exception to that rule is Backblaze B2. I do use their proprietary backup system, because it's so cheap. But it's only a backup and it's not my only backup, so I could easily switch.

I'm currently mid transition from MariaDB to SQLite. That one is more complex, but not because we did anything MariaDB specific. It's more that SQLite is so different we have a completely different database design (for one thing, we have hundreds of databases instead of just one database... some of those databases are less than 100KB - the server just reads the whole thing into RAM and slow queries on our old monolithic database are less than 1 millisecond with this new system).

never use anything vendor specific like stored procedures, vendor specific datatypes or meta queries

Yeah we don't do anything like that. All the data in our database is in a JSON type (string, number, boolean, null) with the exception of binary data (primarily images). It doesn't even distinguish between float/int - though our code obviously does. All of the queries we run are simple "get this row by primary key" or "find all rows matching these simple where clauses. I don't even use joins.

Stored procedures/etc are done in the application layer. For example we don't do an insert query anywhere. We have a "storage" object with simple read/write functions, and on top of that there's an object for each model. That model does all kinds of things, such as writing the same data in different places (with different indexes) and catching "row not found" failures with an "ok, lets check if it's in this other place". That's also the layer we do constraints which includes complex business rules, such as "even if this data is invalid — we will record it anyway, and flag it for a human to follow up on".

My line of business is entirely a Microsoft shop so everything we've ever written has been for MSSQL.

That being said, I can understand the benefits of having a choice in backend. For example, for our Zabbix deployment some engineer just installed mariadb+zabbix on a server and called it a day. This has caused us no end of troubles (ibdata misconfigured, undo files too small, etc). After the last time I had to rebuild it due to undo file corruption I swore that if it broke again I was switching to postgres. So far knocks on wood we haven't had any major issues. We're still looking into and planning for a postgres migration but we're hoping to hold out for a little longer prep time.

Maybe I should contribute a MSSQL engine for Zabbix so I can move it to a platform I'm more comfortable with. ;)