Basic concepts of Data Store and factors to consider for selecting data stores in applications

In today’s data-driven world, selecting the appropriate data store is crucial for effectively analyzing, managing data, and building applications. With hundreds of data store options available, making the right choice can be challenging. This article aims to provide a comprehensive overview of data stores and offer guidance on selecting the best type for your specific needs.

1. Basic concepts of Data Store

1.1. Data store

A data store is a place to store and manage data. Besides data storage like databases, data stores are also used to store other types of data such as raw files (text, audio, image, video, etc.) and emails. This article will focus on three popular types of data stores: RDBMS, NoSQL database, and Object Store.

1.2.  Database

A database is a collection of data managed by a database management system (DBMS). There are two popular types of databases: relational database management systems (RDBMS) and NoSQL databases.

1.3.  Distributed database

A distributed database stores data across multiple computers, known as clusters. Each computer in the cluster is called a node.

1.4.  RDBMS (Relational Database Management System)

An RDBMS is a database based on relational models, typically containing tables with one-to-one, one-to-many, and many-to-many relationships. It uses Structured Query Language (SQL) to query and update data. While SQL dialects may vary depending on the vendor, they generally comply with the SQL-92 standard.

1.5.  NoSQL

The term NoSQL has been a source of confusion since its inception, with some mistakenly believing it to be a replacement for SQL. However, NoSQL actually stands for “Not Only SQL,” referring to a database design that focuses on storage and retrieval rather than tabular design like RDBMS.

1.6.  Transaction

A transaction is a collection of operations that accomplish a single purpose, appearing as a single operation to external observers. For example, in an online money transfer transaction, the system must perform a series of actions, such as verifying the sender’s balance, checking the recipient’s account existence, debiting the sender’s account, and crediting the recipient’s account.

1.7. OLTP versus OLAP:

–        OLTP (Online Transaction Processing): enables multiple users to perform multiple transactions simultaneously. For example, e-commerce websites that support functions like product viewing and purchasing are OLTP systems.

–        OLAP (Online Analytical Processing):  enables data analysis, typically using data generated from OLTP systems. For example, in an e-commerce system, calculating sales by month, quarter, or year, and identifying best-selling products are examples of OLAP activities.

1.8.  Data Warehouse

A data warehouse is a type of database management system designed to execute queries and analyze historical data. It is often integrated with Business Intelligence (BI) tools to generate reports.

1.9.  ACID

ACID stands for Atomicity, Consistency, Isolation and Durability.

–        Atomicity: All operations in a transaction must be successful for the transaction to be executed. If any operation fails, the entire transaction is rolled back.

–        Consistency: Once a transaction is successfully committed, all future queries to the data affected by the transaction will return consistent results. For example, if a transaction updates the ‘salary’ column in the employee table to 10M, all subsequent queries on that record will consistently return 10M without discrepancies.

–        Isolation: Multiple transactions can occur simultaneously without causing data inconsistency in the database.

–        Durability: Once a transaction is successful, the changes it made to the data are permanently stored in the database.

1.10. CAP Theorem

The CAP Theorem states that in the presence of a network partition, a distributed database cannot simultaneously guarantee both Consistency and Availability. Only one of these two properties can be chosen.

• Choosing Availability over Consistency:

Suppose there are two services, A and B, with data stored in a key/value store consisting of three nodes. When service A updates the counter value from 6 to 8, the value is stored in the key/value store. However, due to a network partition, only two nodes update the data to 8, while one node still has the value 6. If availability is prioritized over consistency, service B can read the counter value as 6.

• Choosing Consistency over Availability:

Using the same example, if consistency is prioritized over availability, then when service B attempts to read the counter, the system will not respond.

2. Comparison of RDBMS and NoSQL

2.1. Overview of RDBMS

The following table lists today’s most popular types of relational databases.

2.2. Overview of NoSQL database

NoSQL databases have existed for a long time but gained significant popularity in the 2000s due to the sharp decline in hard drive prices, the exponential increase in data volume, and the necessity to handle not only structured but also semi-structured and unstructured data. There are five main types of NoSQL databases: Key-Value, Document, Columnar, Graph, and Search. We will delve deeper into each type.

2.2.1. Key-Value store

Key-Value stores data in key – value pairs, where the value can encompass various data types such as integers, strings, or arrays.

Common types of Key-Value stores:

Applications suitable for Key-Value stores:

• Applications with high-frequency inserts, such as web session logs. User login sessions are stored into application.
• Applications requiring very fast data writes.
• Applications utilizing data caching to enable quick data query.
• Applications unsuitable for Key-Value stores:
• Applications requiring complex queries or SQL-type data retrieval.

2.2.2. Document store

• Each data line (document) in a document store contains sufficient information to define itself without the need for additional metadata. Data formats can include HTML, XML, or JSON.
• Each document has a primary key.
• ACID properties are supported at the document level, not across the entire database.
• Data retrieval is performed through APIs.
• To enhance performance, the document store utilizes data sharding across multiple nodes in a cluster.
• JSON files, being voluminous, are compressed into Binary JSON (BSON) in document stores.

Applications suitable for Document stores:

• Applications requiring a flexible schema (schemaless), such as e-commerce platforms where each product has different attributes (e.g., books vs. electronics).
• Applications requiring quick data retrieval, especially when migrating from legacy applications from RDBMS systems.

Common types of Document Stores

2.2.3. Columnar store

Columnar store, also known as column-oriented database, stores data on disk in a column format instead of the traditional rows. It accelerates data query performance in data analytics.

For example, consider an Employee table with four columns: ID Number, Last Name, First Name, and Bonus (additional amount received). Traditional databases store data in row-oriented manner, meaning data rows are stored vertically. The row with ID = 513001 is placed above 502333, and each row containing data for all four columns.  Now, if we want to analyze data, such as calculating the average bonus for employees, the engine must scan all the rows, sum the bonuses, and divide by the number of rows. This query would be very costly, however, with millions of rows. With a columnar store, table’s data is organized by columns, meaning the columns of the Employee table are flattened and stored horizontally in a row. For instance, all IDs are stored in one row, and all bonus values are stored in another row. The number of rows in a column-oriented store equals the number of columns in the table. To calculate the average bonus, the engine only needs to sum the values in the row containing the bonus data.

• Applications suitable for Columnar stores:
  • Analytics applications that require filtering and aggregating data by columns, such as log data analysis.
  • Applications storing and analyzing time-series data.
• Applications unsuitable for Columnar stores:
  • OLTP applications.
  • Applications requiring full ACID properties.
  • Applications with a small amount of data.
• Common types of Columnar Stores:

2.2.4. Graph data store

Hierarchical data is quite common in modern applications. For example, in a company’s employee list, some employees are “bosses” of other employees. RDBMS models this type of data using foreign keys. In the example above, the Employee table might have columns like ID (primary key – PK), Name, Date Of Birth, Gender, ManagerID (foreign key – FK to ID). To find a list of employees for a certain manager, you need to join the Employee table with itself. For an extremely large number of records and a deep hierarchy, this join becomes very costly.  Graph stores were created to store complex hierarchical data, making querying much more efficient. In a graph store, data is represented as a graph with nodes, and the relationships between these nodes are edges. This structure allows for queries between nodes by traversing the connecting edges.

Applications suitable for Graph stores:

–        Graph stores are ideal for storing hierarchical data with interconnected relationships, such as social networks.

Applications unsuitable for Graph stores:

–        Applications that rely heavily on SQL JOIN operations.

Common types of Graph Stores:

2.2.5. Search data store

A search data store enables efficient searching using wildcard, data grouping, and ranking. Two typical algorithms used in search stores are inverted index and TF-IDF. Inverted index will “hash” words in a document into indexes, while TF-IDF will determine the importance of those keywords.

Applications suitable for Search stores:

–        Applications requiring effective search capabilities, such as keyword searches in e-commerce platforms or any application needing to search large datasets.

Applications unsuitable for Search stores:

–        Applications requiring ACID properties.

Common types of Search stores:

Some other types of Data Stores

If raw files storage is needed, there are two very popular types of data stores, namely Hadoop Distributed File System (HDFS) and Object Store.

1. Object store

Object stores efficiently store raw data, simplifying both storage and retrieval processes.

 When to use Object store:

–        When requiring storage of raw data at low cost.

–        When utilizing big data tools for data retrieval. Most big data SQL-On-Hadoop technologies, such as Apache Drill, Hive, Amazon Athena, Impala, and Presto, support querying data on Object Store.

Common types of Object stores

2. HDFS

HDFS is the core of Hadoop big data technology. Data on HDFS is replicated by default into three copies, providing high availability.  It can be processed by various analytical tools such as MapReduce, Apache Spark, Apache Hive, Apache Pig, and Apache Impala.

3. Factors to consider for selecting data stores in applications

3.1. Operational workload or analytic workload

Operational workload typically involves OLTP applications that require the database to be optimized for inserts, updates, and transactions with strong ACID properties needed. RDBMS is well-suited for this type.

Analytic workload involves OLAP applications that require the database to be optimized for data reading and performing quantitative operations such as sums, averages, minimums, and maximums. Columnar data stores are ideal for this type of workload.

Additionally, there is a hybrid type called “Hybrid Transactional and Analytical Processing” (HTAP). This is useful for real-time applications like fraud detection. In-memory data stores such as key-value stores are recommended for HTAP scenarios.

3.2. Data storage and query method

Another critical factor influencing the choice of a data store is how data is stored and queried.

Graph stores are suitable for hierarchical data or complex relationships between data points.

Key-Value stores are used when data retrieval is primarily based on keys, regardless of data storage format and data type.

Relational databases (RDBMS) are appropriate for data that is structured and requires quantitative retrieval operations like sum, average, min, and max.

Document stores are optimal when the data has many attributes in the schemas, or contains tags such as JSON or XML.

Object stores are recommended for storing raw files at low cost.

3.3. Costs and experience

Once the type of data store is decided, the choice of a specific database involves considering cost and expertise factors. For instance, opting for an RDBMS like Oracle or Microsoft SQL Server may be feasible with a larger budget, while startups with limited resources might prefer open-source options like PostgreSQL. Additionally, the practical experience of the team also plays a crucial role in the final decision-making process.

In today’s landscape, many applications are deployed using the microservices model, where each service performs distinct functions and utilizes its own data store. This approach often results in applications employing multiple types of data stores simultaneously. Hopefully, this article has provided an overview of various data store types to aid in making informed decisions.