Database Design Tips | Choosing the Best Database in a System Design Interview

Summary

This video script delves into the critical role of database selection in system design, emphasizing its impact on scalability and performance. It outlines common use cases, such as caching with Redis, file storage with Amazon S3, text search with Elastic Search or Solr, and time series data with InfluxDB. The script guides through choosing between SQL and NoSQL databases based on data structure, query patterns, and scale, highlighting the importance of ACID properties for transactional systems and suggesting combinations of databases to meet diverse requirements.

Takeaways

• 🔍 The choice of database is crucial for system scalability and is influenced by non-functional requirements (NFRs).
• 📚 Databases do not impact functional requirements but can affect performance based on query patterns and data structures.
• 📈 The suitability of a database depends on factors such as data structure, query patterns, and the scale of data to be handled.
• 💾 Caching solutions like Redis, Memcached, etcd, and Hazelcast are essential for reducing database load and improving latency.
• 🖼️ For file storage, particularly for images and videos, Blob Storage solutions like Amazon S3 are commonly used.
• 🌐 Content Delivery Networks (CDNs) complement Blob Storage by distributing content geographically for faster access.
• 🔎 Text Search Engines like Elastic Search and Solr, built on Apache Lucene, are used for implementing search capabilities with support for fuzzy search.
• 📊 Time Series Databases such as InfluxDB and openTSDB are optimized for sequential data writes and time-range queries, ideal for metrics tracking.
• 📊 Data Warehouses like Hadoop are used for offline reporting and analytics by aggregating data from various transactional systems.
• 🔑 The decision between relational and non-relational databases often hinges on the structure of the data and the need for ACID transactions.
• 🔄 For ever-increasing data with a finite number of query types, columnar databases like Cassandra and HBase are recommended.

Q & A

What is the primary factor that determines the choice of database in a system design?

The primary factor that determines the choice of database in a system design is the non-functional requirements (NFRs) such as query patterns, data structure, and scale to handle.

How do databases impact the functional requirements of a system?

Databases generally do not impact the functional requirements of a system. Any database can be used to satisfy the functional requirements.

What are the three main factors that influence the choice of database?

The three main factors that influence the choice of database are the structure of the data, the query pattern, and the amount of scale that needs to be handled.

Why is caching important in system design?

Caching is important in system design to reduce the number of times a database is queried, to improve response times, and to handle high-latency remote calls by storing responses locally.

What are some common caching solutions mentioned in the script?

Some common caching solutions mentioned in the script are Redis, Memcached, etcd, and Hazelcast.

How does caching work in terms of key-value stores?

In caching, the key is typically the query parameter or request parameter, and the value is the response expected from the system. This key-value pair is stored in key-value stores.

What is Blob Storage and why is it used?

Blob Storage is used to store large binary objects like images and videos. It is not a database but a data store where files are served as they are, without querying.

Why is Amazon S3 a popular choice for Blob Storage?

Amazon S3 is a popular choice for Blob Storage because it is cost-effective and widely used by many companies, making it a reliable and efficient solution for storing images and videos.

What is the purpose of a Content Delivery Network (CDN) in the context of Blob Storage?

A CDN is used to distribute the same image or video geographically across various locations, allowing users to access the content faster by querying servers closer to their location.

What is a Text Search Engine and why is it used?

A Text Search Engine is used to provide text searching capabilities on textual data, such as product titles and descriptions. It supports fuzzy search, which helps in handling misspellings and provides a better user experience.

What are some common implementations of Text Search Engines?

Some common implementations of Text Search Engines are Elastic Search and Solr, both of which are built on top of Apache Lucene.

Why are Time Series Databases used and what are some examples?

Time Series Databases are used to store and manage time-stamped data, such as application metrics. They are optimized for sequential updates and bulk read queries. Examples include InfluxDB and openTSDB.

What is the role of a Data Warehouse in system design?

A Data Warehouse is used for storing large amounts of data from various transactional systems and providing querying capabilities for offline reporting and analytics.

How does the choice between a relational and non-relational database depend on the data structure?

If the data is structured and can be easily modeled in tables with rows and columns, a relational database is suitable. For unstructured data or data with complex queries, a non-relational database like a document DB is more appropriate.

What are some scenarios where a combination of databases is used?

In real-world scenarios like an e-commerce platform, a combination of databases is often used. For example, an RDBMS can be used for inventory management, while Cassandra can be used for storing historical order data. Document DBs can be used for complex querying needs.

What are some common providers of relational databases?

Some common providers of relational databases include MySQL, Oracle, SQL Server, and Postgres.

What are the characteristics of ever-increasing data and why is a columnar DB like Cassandra suitable for this?

Ever-increasing data refers to data that grows at a rate that is more than linear, such as location pings from drivers in a service like Uber. Columnar DBs like Cassandra are suitable because they handle large volumes of data and support high write and read throughput.