Except perhaps for the tiniest static services, it’s almost impossible to create an initial plan both detailed and realistic enough to bear a close similarity to the eventual working, such as it may be, product.
Most of us have probably heard the maxim “Fast, Cheap, or Good? Pick two.” (Actually, you’re pretty lucky sometimes if you get two.) This maxim doesn’t quite map as well for production services which require ongoing availability as it does for more shrink-wrap software.
Database engines in particular end up being make-it-or-break-it components of complex, stateful services. A lot can go wrong along the way, including performance that does not scale proportionally to data size or traffic, instability caused by schema or design patterns that work against the database’s strengths and amplify its weaknesses, or simply poor discipline around schema migrations and index creation which make it difficult to monitor changes or prune tables or indices which no longer get used but still cost money and performance in terms of resource overhead.
Care and feeding of databases needs to involve multiple teams, not just whichever team “owns” the database infrastructure. Developers who write queries against the database need to learn efficient patterns for that engine and understand that those patterns may be completely different from other DBs they have been near. Developers designing a database schema need to have even deeper understanding of best practices for that engine.
However, the single most important point of time in the lifecycle of a service’s backend database may very well be the selection time. I’ve personally seen the results when developers pick whatever datastore happened to be up and running and shoved their new use case into it, with disastrous results.
If you have fixed data that receives regular in-place updates and which you need to read much more frequently than you write to it, you don’t shove it into a NoSQL time-series store like Cassandra, which is optimized for writes and which takes a massive performance hit when forced to update existing rows. If you have a lot of large blob-y data, shoving it directly into a more traditional relational database may save the developer work in the first 5 minutes of that use case, and then cause pain in perpetuity. Hint: if you have to change the setting for maximum BLOB field size from the default in MariaDB so you can shove a jar file in there, you’re probably on the wrong path. Turn back now.
Picking the “perfect” database will never be easy. In fact, like the path to that elusive ending in my Choose Your Own Adventure book, the perfect database for a backend SaaS datastore almost never exists. Not only do you need to fit the type and size of the data you need to handle, you have to consider scale, performance, cost, and what may be the most important factor in some teams: whether your engineering team has the skill and bandwidth to manage that database in production or the organizational support to learn on the fly, through uptime and inevitable downtime.
You will almost always lose hard if you try to force a database to behave in a way that makes your code or application design “easier.” Databases are a lot like cats. They’re going to do what they want to do. If you take the time to understand how they see the world, you will have a lot more success and fewer scratches. And the bigger the cat, the more time and care you need to take. You will not have any more success in a battle of wills against a database with terabytes of data and a misaligned use case than you would trying to force a Bengal tiger to do what you want by treating it like a trained dog.
Start smart, keep learning, and if you find that you either somehow picked a database engine that you will always have to fight against or you have simply grown apart, be prepared to invest in researching and finding a better solution, because if you go to war against your database engine, you will always lose.