SQL vs NoSQL Database

#database

Relational DB

Pros

  1. Data Consistency: SQL databases ensure data consistency across multiple tables through the use of transactions, which ensure that changes made to one table are reflected in all related tables[1].
  2. Backup and Recovery: SQL databases have built-in backup and recovery tools that help recover data in case of system crashes or other disasters[1:1].
  3. High-Speed Query Processing: SQL databases are known for their high-speed concurrent query processing capabilities[2].
  4. Data Integrity: SQL databases abide by ACID principles, ensuring data integrity[3].
  5. Support: All SQL databases receive great support from their vendors. Also, many independent consultants can help you with SQL databases on a large scale [4].
  6. Reduced redundancy: Data related to an entity is organized in one table, while relevant information is stored in other tables linked through foreign keys. This is called normalization and helps eliminate inconsistent dependencies.

Cons

  1. Lack of Real-Time Analytics: SQL databases are designed for batch processing and do not support real-time analytics, which can disadvantage applications requiring real-time data processing[1:2].
  2. Limited Query Performance: SQL databases may have limited query performance when dealing with large datasets, as queries may take longer to process than in-memory databases[1:3].
  3. Complexity: SQL databases can be complex to set up and manage, requiring skilled database administrators to ensure optimal performance and maintain data integrity[1:4].
  4. Limited Flexibility: SQL databases are less flexible than NoSQL databases when handling unstructured or semi-structured data[1:5]. However, popular SQL DBs like MySQL and PostgreSQL support JSON datatype columns, which can store unstructured data up to a certain extent.
  5. Resource-Intensive Scaling: In SQL databases, vertical scaling involves investing in more powerful hardware, a costly and time-intensive process[2:1].

NoSQL DB

There are various types of NoSQL databases. These databases are used in applications that require a large volume of semi-structured and unstructured data, low latency, and flexible data models. Relaxing some of the data consistency restrictions of other databases can achieve this. Types of NoSQL DBs:

  1. Key-Value store -> DynamoDB, Redis, Memcache
    1. Key-value databases are efficient for session-oriented web applications that store users' data during a memory or database session.
    2. User data is stored with a unique ID in a key-value database for easy access and storage.
    3. Key-value databases use key-value methods like hash tables to store data in key-value pairs. Here, the key serves as a unique or primary key, and the values can be anything ranging from simple scalar values to complex objects. These databases allow easy partitioning and horizontal scaling of the data. Some popular key-value databases include Amazon DynamoDB, Redis, and Memcached DB.
    4. Use case: Key-value databases are efficient for session-oriented applications. Session-oriented applications, such as web applications, store users’ data in the main memory or a database during a session. This data may include user profile information, recommendations, targeted promotions, and discounts. A unique ID (a key) is assigned to each user’s session for easy access and storage. Therefore, a better choice to store such data is the key-value database.
  2. Document store -> MongoDB, DocumentDB
    1. Document databases are ideal for storing unstructured catalog data, such as JSON files or other complex hierarchical data structures.
    2. In e-commerce, a product has too many attributes to store in a relational database, impacting the reading performance.
    3. A document database efficiently stores attributes in a single file for easy management and faster reading. It's ideal for content management apps like blogs and video platforms.
    4. A document database is designed to store and retrieve documents in formats like XML, JSON, BSON, and so on. These documents comprise a hierarchical tree data structure, including maps, collections, and scalar values. Documents in this type of database may have varying structures and data. MongoDB and Google Cloud Firestore are examples of document databases.
    5. Use case: Document databases are suitable for unstructured catalog data, like JSON files or complex structured hierarchical data. For example, in e-commerce applications, a product has thousands of attributes, which is unfeasible to store in a relational database due to its impact on reading performance. Here comes the role of a document database, which can efficiently store each attribute in a single file for easy management and faster reading speed. Moreover, it’s also a good option for content management applications, such as blogs and video platforms. An entity required for the application is stored as a single document in such applications.
  3. Columnar store -> Cassandra, HBase, Redshift
    1. Columnar databases are efficient for analytics queries, reducing disk I/O and data load requirements.
    2. Financial applications often require summing transactions over time. Columnar databases speed up the process by reading only the monetary column, ignoring customer attributes.
    3. Columnar databases store data in columns instead of rows. They enable quick and efficient access to all entries in the database column. Popular columnar databases include Cassandra, HBase, Hypertable, and Amazon Redshift.
    4. Use case: Columnar databases are efficient for many aggregation and data analytics queries. It drastically reduces the disk I/O requirements and the data required to load from the disk. For example, in applications related to financial institutions, there’s a need to sum the financial transaction over some time. Columnar databases make this operation quicker by just reading the column for the amount of money, ignoring other attributes of customers.
  4. Graph store -> Neo4j, OrientDB
    1. Graph databases aid social apps by providing user activity insights.
    2. Graph databases are a type of database that stores data in a way that enables analyses and decisions based on the relationships between different entities.
    3. Graph databases are versatile and can be used for various applications, such as data regulation, machine learning research, and financial services.
    4. Graph databases use the graph data structure to store data, where nodes represent entities, and edges show relationships between entities. The organization of nodes based on relationships leads to exciting patterns between the nodes. This database allows us to store the data once and interpret it differently based on relationships. Popular graph databases include Neo4J, OrientDB, and InfiniteGraph. Graph data is kept in store files for persistent storage. Each file contains data for a specific graph part, such as nodes, links, properties, etc.
    5. Use case: Graph databases can be used in social applications to provide interesting facts and figures about different kinds of users and their activities. The focus of graph databases is to store data and pave the way to drive analyses and decisions based on relationships between entities. The nature of graph databases makes them suitable for various applications, such as data regulation and privacy, machine learning research, financial services-based applications, and many more.

Pros

  1. Scalability: NoSQL databases are highly scalable and can be modified to meet your applications' unique scaling needs. They can be scaled horizontally instead of vertically, which provides a clear advantage over SQL databases[5].
  2. Simplicity: NoSQL databases require less structuring than SQL, so every item is self-contained and independent[5:1].
  3. Flexible Data Model: NoSQL databases easily store and combine any data, both structured and unstructured[6].
  4. Elastic Scalability: NoSQL databases scale on low-cost, commodity hardware, allowing for almost unlimited growth[6:1].
  5. High Performance: NoSQL databases are built for outstanding performance, measured in terms of both throughput and latency[6:2].

Cons

  1. Less Mature: NoSQL is less mature than SQL, disadvantaging projects requiring experience[7].
  2. Requires Multiple Databases: If you’re using NoSQL, you’ll probably end up using multiple types of databases and data models to fill all the niches and needs of your application[7:1].
  3. Lack of Complex Query Functionality: NoSQL databases are more flexible when storing various data structures, but they need more complex query functionality found in SQL[5:2].
  4. Lack of standardization: NoSQL doesn’t follow any specific standard, like how relational databases follow relational algebra. Porting applications from one type of NoSQL database to another might be challenging.
  5. Consistency: NoSQL databases provide different products based on the specific trade-offs between consistency and availability when failures can happen. We cannot guarantee strong data integrity, such as primary and referential integrities, within a relational database. Instead, data may converge slowly using a weak model, such as eventual consistency.
Summary

In conclusion, the choice between SQL and NoSQL depends on the specific requirements of your application. SQL is generally a good choice for applications that require complex queries, transaction reliability, and structured data.
At the same time, NoSQL is better suited for applications that require scalability, flexibility, and high performance.

  1. https://www.geeksforgeeks.org/advantages-and-disadvantages-of-sql/ ↩︎ ↩︎ ↩︎ ↩︎ ↩︎ ↩︎

  2. https://www.skillvertex.com/blog/advantages-and-disadvantages-of-sql/ ↩︎ ↩︎

  3. https://www.almabetter.com/bytes/articles/advantages-and-disadvantages-of-sql ↩︎

  4. https://www.geeksforgeeks.org/difference-between-sql-and-nosql/ ↩︎

  5. https://www.adservio.fr/post/what-are-the-pros-and-cons-of-nosql ↩︎ ↩︎ ↩︎

  6. https://www.mongodb.com/scale/nosql-databases-pros-and-cons ↩︎ ↩︎ ↩︎

  7. https://www.altexsoft.com/blog/nosql-pros-cons/ ↩︎ ↩︎

Thoughts 🤔 by Soumendra Kumar Sahoo is licensed under CC BY 4.0