The app I’ve been working on for the past 24 years is currently on its third DBMS, and I would be unwilling to bet on whether it’s on its last DBMS.

There is zero SQL code in our app above a very low level. All high-level code calls through a DB API, and all SQL code is behind that abstraction layer.

That said, each one of these transitions was a “pull up stakes and move” kind of thing. We never tried to support multiple back-ends at once. So on those grounds, I think this is probably a good move for GitLab.

If you're using basic ANSI/92 SQL syntax then it'll work pretty much anywhere, especially with a decent ORM, but as soon as you start using any of the interesting features of a particular database, then you're locked in.

Supporting multiple databases at this point is usually not economically feasible.

That's the thing: it's super tempting to use nonstandard features. On our last SQL transition, we were most bitten by use of data types the new DBMS didn't support and by changes in the way "standard" text data types behaved between the two. (Just as GitLab was, on that last one.)

That's why I don't regret putting all of our SQL behind that DB layer: to port to the new DBMS, we just had to rewrite parts of that layer, rather than go on a hunt through the whole application for one-off SQL calls, as would probably occur if we didn't enforce this discipline.

But why prioritize portability over better functionality at all? Why did you move databases so often?

> But why prioritize portability over better functionality at all?

It's not just about portability, or even primarily. It's simply good application layering to put the DB code behind a layer. Intermingling SQL code in your application logic is just as bad as intermingling GUI code in your core app logic.

But even that aside, we haven't felt the lack from putting such code a single function call layer away. I suppose it may give us a slight amount of pause when we consider whether it's worth creating another function in that helper library, but that's probably a good thing. If we decide we can get our work done by calling one of the existing functions, then why write yet another DB access function?

Example: if a table is known to have at most 5 rows, and the query that would allow the DB to return just the one we actually want is complicated, and it's almost never called, why not call the existing "SELECT all" one and iterate over the rows? The performance hit of being inefficient will be immeasurable.

> Why did you move databases so often?

That's once every 8 years on average. A lot changes in the computing world over that span.

The first change did coincidentally happen about 8 years after the company was founded, and it was because much better DBMS tech became available to us, within the constraints we had on choosing.

That DBMS lasted us for the next 14 years. By that time, enough had changed in the computing world that it was worth swapping again. The new one runs about twice as fast, takes far less code space, and is simpler to use all around than the prior one.

We'd have to switch again this year in order to maintain the mean of once every 8 years. My sense is that this is more likely a nonlinearly increasing growth curve, so that the next switch will be closer to 20 years than 14, if it happens at all.

We might be talking about different things. Of course using an ORMs and Query constructors to programmatically generate SQL is the best option instead of handwritten code everywhere, but that's a different issue.

What we're discussing isn't the SQL code, it's SQL syntax and features of a particular database over another. JSONB, partial indexing, text search, extensions, etc. in Postgres are not available in MySQL, and not using those features just to have the ability to switch or run on multiple databases is almost never worth the effort.

Simple CRUD apps with an ORM can probably do so, but otherwise most business apps shouldn't avoid using functionality for some "what-if" scenario. Having to switch databases after a decade is just a normal development item if it needs to happen, and usually coincides with heavy rewrites anyway, so I don't see much value in pre-planning for that at the architectural level.

Not OP, but over 24 years some major db upgrades may as well be an entirely new db. In addition database vendors go out of business and when their assets are bought, the buyer is often buying customer base and not so much the obligation to keep developing a separate product forever. E.g. IBM and Oracle have each bought lots of their prior competition, and sometimes they only offer customers an upgrade path to a different product. Each time something like that happens you are going to re write your db support anyway.

I have migrated apps from one database to another several times. The decoupling was very helpful.

I have also seen instances where the decoupling helped us abstract out differences between different versions of the same database product during upgrades.

As a long time PostgreSQL user, I'd say that decoupling has been very useful for most of the last two decades, but it's quickly becoming irrelevant for reasons that should be obvious here.

I've more often seen the app change from on top of the DB, than the DB change from under the app. Actually I'm not sure I've ever seen the latter happen IRL. App rewrite/replacement but DB remaining? Several times. Network message format/tech change? Several times. DB even going from one SQL server or one NoSQL server to another of the same general sort? Never. I know it does happen but I've stopped bothering to give it so much as a thought when designing a system. DB techs are more likely to be added on and maybe, maybe some earlier one(s) replaced over time alongside rewrites, than to be swapped out from under an app directly.