It is quite a difficult task to choose the right database for your application. It is so because the criteria for choosing the same are different for different people. Besides, migrating to another database on selecting the wrong one is very costly and sometimes more complex to do with zero downtime. Each database technology has advantages and disadvantages. These are the crucial points that Database administrators care about:

• A reliable monitoring and alerting system
• Support for backup and restore
• Reasonable upgrade and migration costs
• An active support community
• Ease of performing tuning
• Ease of troubleshooting

Understanding the Tradeoff:

The reason for the availability of a wide range of database options is the CAP Theorem. CAP stands for Consistency, Availability, and Partition tolerance.

• Consistency indicates any read request that will return the most recent write.
• Availability means all (non-failing) nodes are available for queries and must respond in a reasonable amount of time.
• Partition tolerance means that the system will continue to work despite node failures.

Structure of Data:

If you want your data to be structured, then you should go for a relational database. While dealing with payment and transactions, your database needs to be ACID compliant, along with the SQL-based databases. It is so because the counterpart database (NoSQL) offers weak consistency.

However, if your data requirements aren’t clear or you are designing unstructured data, then NoSQL may be your best choice. The data stored in a NoSQL database doesn’t need a predefined schema due to which you can enjoy much more flexibility and less upfront planning when managing your database

Query Patterns:

The next important factor to consider is how to query your data. If you desire o fetch data by key, then it is advisable to use “Non-Relational” databases and you will need a key-value store, such as DynamoDB. But, if you want to query many different fields at the same time, then you can choose both “Relational” and “Document” DB. For fuzzy search query capabilities (free text search), then search engines like “Elasticsearch” are applicable.

Performance and Scaling:

The performance of the database tends to degrade whenever the amount of read/write traffic increases. During this time, optimizations such as indexing your data and scaling your database come into the picture. Performance is mainly dependent on various factors but the overall performance depends to a very large degree on choosing the right implementation for your use case. SQL database provides great benefits for transactional data that are consistent and where data integrity is paramount. NoSQL database provides more flexibility and scalability, making them useful for rapid or iteration development.