When we design software or databases, we have to make decisions right now. If you work as I have in many environments, this means I'm often picking a tool, platform, version, technology, or something else without a complete set of information. In some cases, I'm actually writing code (SQL or C#/Java/etc.) without completely knowing the business problem I'm solving.what someone described, but as many of us know, those descriptions often prove to be lacking.
Over time, we refine the code with feedback from users, which is why DevOps has become so popular. We write the best code we can, making small changes and improvements, and then quickly alter code if we haven't hit the mark.
However, changing the code is one thing. Changing more fundamental technology choices is harder. There is an interesting post on the disproportionate impact of your early tech decisions from one of the developers at Stripe. One of the examples is of the database platform. which was Mongo. The piece notes that once the database platform is chosen, most groups never switch platforms. I think that's been true in many places I've worked. The same thing for languages and cloud providers as well, as once a choice is made, rarely does a team, much less an organization, switch.
Why is this? Is the cost of switching that high? I think it is. I've seen a few organizations want to migrate from a licensed relational platform to one that is free and OSS. However, the time it takes to rewrite code, the time to build new expertise, learn the tips and tricks for a new database (or other platform technology) is significant. A team can make no shortage of mistakes during this process. Is that worth the cost of licensing? Perhaps. Perhaps not. It's a hard thing to decide.
In many cases, I'd argue that core changes to your technology stack ought to be considered, but understanding that no tech will solve all your problems, and you will make lots of mistakes in the process of changing. Does this mean you need to make a great decision when you start working on a project? In my mind, no. Make the decision to use what your team is most familiar with and then live with it.
And be open to using targeted technology for specific needs. Lots of transient data in a busy workload? Add Redis to your database stack. If you need full text search, consider ElasticSearch. Maybe add a graph database (not SQL Server) if you need queries in this problem domain. Use what works, and learn to use it well.