I have the opposite experience: that for almost all projects I have done in the past 20 years, switching data store is almost always nearly trivial. However, i

I have the opposite experience: that for almost all projects I have done in the past 20 years, switching data store is almost always nearly trivial. However, it does come at the cost of giving the design careful thought and sometimes forgoing useful functionality that it is tempting to make use of. One reason for this is that I had a very bad experience with this. I was involved in two projects in the 1990s where we went all in and used a lot of features unique to two different databases. In one of the cases, the database vendor (a small niche player) got acquired by an automation company and their "real-time capable" database was no longer available for licensing as a separate product. This left us stranded. The other project involved loads of PL/SQL in an Oracle and what killed us was the combination of Oracle licensing fees, the scaling limits of the database and that it was really hard to find good PL/SQL developers. Today we use several strategies to keep the data store choice flexible. 1) we use databases as pure data stores. Sure, we use constraints, but only as an (optional) extra safeguard that lets us know when we have screwed up. We aren't dependent on the constraints, but they are immensely useful for rooting out inconsistencies in the code and the model. 2) we try to model the data in ways that can be realized on all databases (or stores) of a certain class. Yes, this means that sometimes you have to use slightly different data types than you'd like, but that isn't the end of the world. In some cases what you store is a reasonably useful representation of a type, and then you create richer types to represent it in the application. 3) we tend to start development using SQL databases, but not all parts of the schema are expressed as a purely relational model. This way we can split the schema and handle parts of it using an entirely different class of database or store. This gives us the convenience of being able to start a project with a single database, exploit the availability of constraint enforcement to show us where we make mistakes, but as we need to scale up, parts of the schema can easily be migrated to, for instance, a timeseries database. This both helps us off the ground quickly and it allows us to postpone actually building a scaled up implementation. (This has proven particularly useful on projects where we have been able to postpone handling a large scale case for years and then having more technology to choose from later). 4) to accomplish 3 we often spend a lot of time understanding what the key data access patterns for the application are going to be and keep this in mind when designing a schema and a scaling plan. It also helps if you can identify where you can decide that data is going to be immutable so that consistency becomes easier later. 5) the application logic never talks directly to the database. There is always a domain specific store service API between the application. We try to make this API as semantically narrow as possible, and try not to make any promises that would be hard to keep if we have to change storage technology. Again, it helps to partition this API into areas that can be realized with different classes of databases, so you can make fewer promises on the parts of the API that will handle the part of the schema that drives most of the operational cost. 6) all tests are written against the store service API, never directly against any database or storage technology. This way you already have a comprehensive test suite if you need to migrate to a new database entirely, or for when you start implementing different parts of the store using different database technologies. In the beginning this approach will require a bit more work, but when you have established practices and it becomes a habit, it actually doesn't represent more work to implement than more direct ways of using databases. Yes, we sometimes forego using functionality offered by the database that would have saved us some work in the short term, but it pays off in the long run because you end up with simpler and more flexible designs. A few years ago I did an experiment with two new hires. Their first task was to add support for a relatively new database to an existing application. They had zero prior experience with the application. I pointed them to the storage service API and the tests and I said "write an implementation of it that uses database X" (which was a NoSQL database). After about two days of work they had an implementation that passed all of the tests and could run all of the benchmarks without any problems. This included adding configuration options that allowed the application to initialize and run with the new store implementation. I think we discovered only 2-3 minor issues in their code over the next 12 months, which isn't bad for a first contribution to an unknown code base. (Since we only had one set of tests which was leveraged across all implementations it also meant that every time we expanded and improved the tests, we'd be able to improve all implementations as the tests got better at finding subtle mistakes). Because it was so cheap and easy to add support for new databases, I think we had support for something like 5 different databases. Including an embedded SQL database so that you could run the application locally for experimentation or integration testing (it is really nice to be able to fire up the application with an in-memory database for integration tests. It wasn't unusual that we ran integration tests that would create and destroy the storage a few hundred times in just a few seconds). After I left the project they added support for another database and migrated terabytes of production data with minimal downtime. The migration tools they wrote for this used the store service API as well, which means they developed one tool that would let them migrate between arbitrary databases. (So if, in the future, they chose to switch databases again, the migration tool already exists) We also had adapters that allowed us to run different permutations of databases. So we could handle parts of the schema suited for relational databases using an SQL database, and handle the bulky bits with somewhat narrower requirements using a NoSQL database. This requires a lot of care when you design the API to have built in "fault lines" where you can split the schema, but with a bit of practice it becomes a habit. (In some cases we've made "conditional" schemas - so for instance if you run the entire thing in one database you may have a bunch of constraints to help you ensure consistency, but you run with mixed stores, you may not apply the constraints that would span across databases. Again, we try not to depend on the constraints, but they are a great help in designing the schema, the store service API and the application).