{"id":472,"date":"2021-06-11T12:11:25","date_gmt":"2021-06-11T19:11:25","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/startups\/?p=472"},"modified":"2021-06-11T12:11:25","modified_gmt":"2021-06-11T19:11:25","slug":"cto-choices-what-happens-when-you-need-to-choose-your-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/startups\/cto-choices-what-happens-when-you-need-to-choose-your-database\/","title":{"rendered":"CTO Choices \u2013 What happens when you need to choose your database?"},"content":{"rendered":"
\n

Building a robust technology stack from scratch is a challenging task. CTO Choices is a series of articles aimed at CTOs, Tech founders, and project creators. They help you ask yourself the right question to pick up your tools. We try hard to give you honest, enduring, and agnostic advice. That is why \u2013 to let our true self speak \u2013 the last section of each article will present a heavily opinionated choice. And you will have the right questions to assess if this choice is good for you.\n After an exploration of the criteria to pick a Frontend framework<\/a>, let’s look at another important component of your architecture: the database.<\/p>\n<\/blockquote>\n

Almost any application needs to keep track of some sort of state. For persistent state, you will probably have a database. Now, you have one of the most complicated choice of your architecture to make: which database to pick?<\/p>\n

You may not see it at first glance, but choosing a database is a complex task for two main reasons. First, there is dozen dimensions to think about \u2013 we will see them in a minute. Then, is one of the choices that is the most complicated to change afterwards. Changing your main database while having a product in production is an extremely complex task. You better have made the right choice the first time \ud83d\ude0a.<\/p>\n

\"A<\/p>\n

What are your constrains?<\/h2>\n

Before going into a list of criteria, you need to understand what you\u2019re building. Do you have performance constrains? Do you have the slightest idea of the volume of data and the volume growth over the next 3 to 5 years (in your biggest dreams)? Applications are born with quite different data requirements and\/or constrains. While I will give you my personal choice later in this article, some projects may need special attention.<\/p>\n

That\u2019s why I recommend doing a \u201cballpark\u201d exercise of estimating the amount of data per user\/customer and their growth over time. You may also want to understand if you need specific features within your database \u2013 like manipulating geospatial data or doing AI right within the DB \u2013 or later in the architecture \u2013 like multi-region deployments, or if you have specific requirements \u2013 like performance constrains. We will dig in more constrains considerations later, however, this first exercise should give you some hints.<\/p>\n

A lot of criteria to consider.<\/h2>\n

So, how to make your own choice while picking a database? It may be not your first criteria, but as a developer, I am looking at developer experience<\/strong>: Can I execute the database in a container or locally? Can I query the DB both from CLI and from an UI? I\u2019m also looking at the DevOps side of the database: can we automate all the deployment \u2013 from creating a database, to updating a schema or creating new users -? Can we use the database in our automated test pipeline? There is a stark difference between databases on these topics.<\/p>\n

There is also a related question to ask: is there a client for the programming language you will be using? You may have a great DB choice, but if it\u2019s only working with few languages, it may be complicated to pick it. You can still create a service to expose DB features through an API, or wrap a native library, but this overhead may be used for other purposes.<\/p>\n

Read and write patterns<\/h3>\n

Most of the data stores in use have a read\/write ratio largely in favor of reads. When the numbers of reads is consequent, we tend to add a cache layer in front of our database \u2013 like Redis. However, some constraints like consistency or heavy analytical queries on the transactional database may refrain this addition. You the need to evaluate the maximum capacity of your database: is there a maximum number of concurrent connections? Do you have read-only replication easily available?<\/p>\n

Write scalability is an interesting and complex problem area. From sequential IDs to sharding, there is a lot of techniques that you can leverage in both your database engine choice and your data architecture. Most of the (relational) databases are deployed on a single-node, but you need to think what you\u2019ll need in 1, 2 or 3 years from now. Does your hosting provider will be able to grow this server up to what you need in 3 years?<\/p>\n

The answer here may be pragmatic: choose something now and accept a redesign or a redeployment later. But ensure you have the proper analytics in your DB in place, so you don\u2019t hit the ceiling too late for this redesign.<\/p>\n

\n

Few years ago, my team had to move a huge database from one datacenter to another (over 200 Tb). The fastest solution was to buy hundred of hard drives, copy data to them, ship them to the new location and plug them one by one in a \u201cload server\u201d. Even with shipping and handling times, the transfer rate was better than transferring the data over Internet.<\/p>\n<\/blockquote>\n

Scalability<\/h3>\n

I\u2019ve already touched a bit the scalability criteria in the previous section. Scalability is also about deploying your database across regions \u2013 for High-availability or regional performance reasons. For example, some opensource databases are great in a single node deployment, but requires paid add-ons when you need to scale to several nodes.<\/p>\n

Hosting<\/h3>\n

When you pickup a database, you may not know where you\u2019ll host it now or in 2 years. Hence, having multiple hosting capabilities is probably a clever idea. We can evaluate a database hosting solution with several categories:<\/p>\n