Choosing the right Databases is critical when any Data Science project is started. With the advent of big data, there are simply many more options for data storage and management needs.
Broadly classifying, databases can be either Relational or Non-Relational. Relational Databases like MySQL and PostgreSQL include simplicity, ease of data retrieval, data integrity, and flexibility. They are optimized for writes but scalability is one of the issues.
Non-relational Databases like Redis, MongoDB are optimized for reads and are scalable efficiently. These are mostly NoSQL (Not Only SQL) based and hence are much more flexible. They further have types such as Key Value-based like Redis, Document-based like MongoDB and Graph-based like Neo4j.
Many important questions need to be considered before choosing the database, like, the complexity of data, relationship between data, latency, application criticality, etc.
The trade-off between 3 major requirements have to be optimized - CAP: Consistency (of the read/write requests), Availability (of non-responding nodes) and Partition Tolerance (ability to operate despite node failures).