Types of Databases | Criteria to choose the best database in the System Design Interview

Summary

This video script offers a comprehensive guide on selecting the right database for distributed services. It emphasizes the importance of considering various criteria such as ease of learning, product maturity, data model, scalability, and cost. The script discusses different types of databases, including relational, NoSQL, document, column family, graph, time series, and NewSQL databases, highlighting their pros and cons. It also provides scenarios to illustrate when each type is most suitable, ultimately guiding viewers on making informed decisions based on their service's specific requirements.

Takeaways

• 📚 Choosing the right database for a distributed service is crucial as it involves trade-offs and depends on specific service requirements.
• 🔍 The decision is not as simple as choosing a relational database for structured data or a NoSQL database for semi-structured or unstructured data.
• 📈 Multiple criteria must be considered when selecting a database, including learning curve, product maturity, technical support, data model/schema, scalability, and cost.
• 🤔 Sometimes a choice must be made between two databases, neither of which is fully appropriate, and the more suitable option must be selected based on the given requirements.
• 🛠️ Different database types have their pros and cons, and the script discusses relational, key-value, document, column family, graph, time series, and NewSQL databases.
• 🔑 Key-value stores are simple NoSQL databases suitable for scenarios with primary key access and where scalability and flexible schema are needed.
• 📄 Document databases are a superset of key-value stores, allowing for a flexible schema and the ability to create secondary indexes on document attributes.
• 🗂️ Column family databases are optimized for accessing data based on columns and are suitable for scenarios requiring aggregation queries on large datasets.
• 🌐 Graph databases excel in scenarios involving complex relationships and multi-hop queries, making them ideal for fraud detection and recommendation systems.
• ⏱️ Time series databases are specialized for storing and retrieving timestamped data, which is useful for IoT and financial systems.
• 🔄 NewSQL or distributed SQL databases combine the ACID properties of relational databases with the scalability and sharding support needed for large-scale systems.

Q & A

What is the primary consideration when selecting a database for a distributed service?

The primary consideration is choosing the most appropriate database based on the requirements of the service, as migrating databases later can be costly and risky.

Why is it not as simple as choosing a relational database for structured data or a NoSQL database for semi-structured or unstructured data?

Because the decision is not binary and depends on multiple criteria that need to be weighed in parallel, such as the specific needs and trade-offs of the service.

What are some of the criteria used to select a database?

Criteria include ease of learning curve, database product maturity and technical support, data model or schema, scalability, QD and data access pattern, cost, and ACID or BASE requirements.

What does ACID stand for in the context of database transactions?

ACID stands for Atomicity, Consistency, Isolation, and Durability, which are properties of database transactions intended to guarantee data validity.

What is the difference between ACID and BASE systems?

ACID systems prioritize consistency and guarantee data validity through a set of transaction properties, while BASE systems prioritize availability and are eventually consistent, often used in distributed databases.

Why are relational databases considered intuitive and straightforward for data representation?

Relational databases represent data in the form of tables with rows as records and columns as attributes, making it easy to establish relationships between different data points.

What are some limitations of relational databases?

Limitations include difficulty in scaling horizontally, manual sharding, rigid schema that is hard to change, and not optimized for queries that require graph traversal.

What scenarios are key-value data stores a good fit for?

Key-value data stores are suitable for scenarios where all data access is through a primary key, scalability is important, and there is no need for complex queries like joins.

What are some examples of NoSQL databases?

Examples of NoSQL databases include MongoDB, CouchDB, Azure Cosmos DB, Apache Cassandra, and Amazon DynamoDB.

Why are document databases considered a superset of key-value databases?

Document databases are considered a superset of key-value databases because they allow the database to examine the internal data of the value, unlike key-value stores where the value is opaque.

What scenarios are column family databases or column-oriented databases suitable for?

Column family databases are suitable for scenarios where you only need to query a subset of columns, and column-oriented databases are efficient for analytic scenarios due to better compression and faster access to a subset of columns.

What are some use cases for graph databases?

Use cases for graph databases include fraud detection in payment systems, recommendation systems in social networks, and any scenario where complex multi-hop queries are required.

Why are time series databases optimized for storing and serving time series data?

Time series databases are optimized for storing and serving time series data because they allow fast insertion and retrieval of large amounts of timestamped data, supporting complex analysis on this type of data.

What is the difference between NewSQL or distributed SQL databases and traditional relational databases?

NewSQL or distributed SQL databases are relational databases with implicit support for sharding and scalability, making them suitable for systems that require ACID compliance and handle large volumes of data and high throughput.

How should one decide whether to use a relational database for semi-structured data?

One should decide based on whether the data can be accessed using an ID and stored in a BLOB or CLOB field within a relational database table, considering factors like performance and the need for complex queries.

What are the implications of updating a count or similar attribute in a database system?

Updating a count or similar attribute can change the system from read-heavy to write-heavy, affecting the choice of database and its ability to handle the increased write load efficiently.