Relational Database and SQL

Data storage became quite structured and manageable with the advent of Relational Databases. Every application systems that was built had a natural inclination of looking at the data in the form of rows and columns which will be well defined and that are related to one another.

With the introduction of Structure Query Language or SQL (also called as SEQUEL), it became even more natural for any application designer or developer to think in these terms. The SQL as a language made it simpler to manage, manipulate, and maintain these structured and related database tables. The idea of thinking about data in the form of well-defined columns of pre-defined types was the natural and obvious choice for every application till the early 2000s.

Was there any problem with the approach?

While both in theory and practice, the relational database has worked and is working, however, with new age applications, social media, and content-based systems, the nature and structure of data started transforming totally from being structured to dynamic in nature. Not only did relational database start to seem quite rigid because of the pre-defined structures but the SQL queries also started getting complex making it difficult for the developers to use them.

Advent of NoSQL

As more and more data was collected and stored, it was evident that not every data is structured and not every time a standard schema can be used to store them.

This is where the term NoSQL came into view. The need to create a complex, difficult-to-manage data model to avoid data duplication can be avoided using this concept. It means that not everything has to be structured and defined before storage. This blog explains how the concept of NoSQL has been used to store data in databases which is called as NoSQL database.

How did NoSQL Database solve the problem?

NoSQL databases came into view in the late 2000s. It came with varied properties that were built and designed to solve the exact limitations that applications and systems faced in the RDBMS database. Some of the key design properties that helped in this are:

  • Dynamic nature of schema: NoSQL databases do not have a fixed schema and can accommodate changing data structures without the need for migrations or schema alterations. In such cases, ACID property of data is not the main need but the flexibility of storing any data type is more relevant.
  • Ability to handle large volumes of data: NoSQL databases are designed in a scale-up architecture so that the growth in size of data does not impact the performance of the databases.
  • Easy to scale: Since the inherent nature of the database is schema-less, and additionally to support storage of large volumes of data, the NoSQL database are built with the ability to rapidly scale up for big data demands.
  • Storage in a single data format: Instead of storing data in a pre-defined structure and data type, NoSQL databases store it in document format or key value format or graphical format.

The way RDBMS tables have different types, versions, and providers, similarly the NoSQL database also has multiple types and categories created as different products. Each one of these came into being based on the need and nature of data. Hence, it becomes very important to make the choice of the right database depending upon the application being built.

What are the advantages of NoSQL Databases compared to RDBMS?

SQL databases are also moving into providing a scalable model and they come with a natural advantage of SQL queries that are easy to execute and understand. Then why should one go for a NoSQL databases? Some of the main advantages are:

  • High scalability: Vertical scaling means adding more resources to the existing machine whereas horizontal scaling means adding more machines to handle the data. Vertical scaling is not that easy to implement, however, horizontal scaling is easy to implement. NoSQL can handle a huge amount of data because of scalability. As the data grows, NoSQL scales itself to handle that data in an efficient manner. This makes them a good fit for applications that need to handle substantial amounts of data or traffic.
  • Flexibility: NoSQL databases are designed to handle unstructured or semi-structured data, which means, they can accommodate dynamic changes to the data model. This makes NoSQL databases a good fit for applications that need to handle changing data requirements.
  • High availability: Auto replication feature in NoSQL databases makes it highly available because, in case of any failure, data replicates itself to the previous consistent state.
  • Performance: NoSQL databases are designed to handle copious amounts of data and traffic, which means, they can offer improved performance compared to traditional relational databases.
  • Cost-effectiveness: NoSQL databases are often more cost-effective than traditional relational databases, as they are typically less complex and do not require expensive hardware or software.
  • Agility: Ideal for agile development.

How to choose the correct NoSQL Database?

When it comes to NoSQL database, as stated, the data type or format can be anything and hence, the choice of right database plays an important role. Based on this and the application created along with the requirement of data to be stored, the appropriate database categories should be selected. Some of these categories are listed below:

Document databases:

These databases store data as semi-structured documents, such as JSON or XML, and can be queried using document-oriented query languages. For e.g.: Mondo DB, Couch DB.

Use case:
A document database is a great choice for content management applications such as blogs and video platforms. With a document database, each entity that the application tracks can be stored as a single document. The document database is more intuitive for a developer to update an application as the requirements evolve. Even for storing user profiles, a document database is very handy.

How has document database helped in real life?

  • Forbes gains 58% faster build time, accelerates release cycle by 4x, supports a 28% increase in subscriptions, and reduces TCO by 25% with MongoDB Atlas and Google Cloud.
  • Hong Kong’s home airline Cathay Pacific pioneers’ digital transformation of in-flight operations to drive efficiency, productivity, and sustainability of all its flights.

Below is an example of comparison of an average throughput of a document database MongoDB vs a traditional MS SQL database. We can clearly conclude that overall, the throughput remains high even for smaller batches of query size. Although the data is not in traditional row-column format but a document.

 

Source: https://www.triona.se/globalassets/global/documents/pdf/rekrytering/utforda_exjobb/andre_josefsson_exjobb.pdf

 

Key-value stores

These databases store data as key-value pairs and are optimized for simple and fast read/write operations. For e.g.: Redis, etcd & Memcached

Use case:
Web applications may store user session details and preference in a key-value store. All the information is accessible via user key, and key-value stores lend themselves to fast reads and writes. Real-time recommendations and advertising are often powered by key-value stores because the stores can quickly access and present new recommendations or ads as a web visitor moves throughout a site.

How has Key-value database helped in real life?

  • Capital One uses RediSearch and eliminates the performance bottlenecks by allowing users who index the database to index the data sets and align querying an aggregate in a real-time manner. RediSearch’s ability to execute this query in sub-millisecond latency is truly a gamechanger
  • Kubernetes uses etcd’s “watch” function to monitor this data and to reconfigure itself when changes occur. The “watch” function stores values representing the actual and ideal state of the cluster and can initiate a response when they diverge.
Source: https://i.stack.imgur.com/OyFnO.png

Below is an example of the comparison of a median response time of a key-value database Redis vs a traditional MySQL database. We can clearly conclude that overall, the response time remains low even for larger batches of query size. Although the data is not in traditional row-column format.

Source: https://dkomanov.medium.com/mysql-as-redis-vs-redis-74b788af9c6f

Column-family stores

These databases store data as column families, which are sets of columns that are treated as a single entity. They are optimized for fast and efficient querying of substantial amounts of data. For eg: Cassandra & HBase.

Use case:
Wide-column databases are optimized for data warehousing and business intelligence applications, where large amounts of data need to be analyzed and aggregated. They are often used for analytical queries, such as aggregation and data mining. Wide-column databases can handle a high number of writes and reads and can be used for storing and processing IoT data. Wide-column databases are optimized to handle high write throughput and low latency and are suitable for use cases where there are a high number of writes such as gaming and e-commerce.

How has wide column database helped in real life?

  • Best Buy uses Apache Cassandra to manage massive spikes in holiday traffic — 7x traffic spikes and bursts > 50,000 reads per second — and calls it “flawless”.
  • BlackBerry deployed Apache Cassandra as the NoSQL database solution for its Internet of Things (IoT) platform. The BlackBerry IoT platform powers the BlackBerry Radar IoT solution designed to provide continuous visibility into an organization’s transportation fleet.
  • ignio uses Apache HBase to leverage its wide column high throughput to ingest timeseries data on which it runs custom algorithm and again stores output back in HBase.
  • Facebook adopted HBase for serving Facebook messenger, chat, email, etc. due to its various features. HBase comes with very good scalability and performance for this workload with a simpler consistency model.

Below is an example of comparison of an average throughput of a wide column database Cassandra vs traditional My SQL database. We can clearly conclude that overall, the response time remains almost constant as we increase the throughput. Although the data is not in traditional row-column format.

Source: https://www.semanticscholar.org/paper/PERFORMANCE-EVALUATION-OF-NOSQL-CASSANDRA-OVER-DATA-Gupta-Narsimha/580f9c27778e94de0da6fb7e66c76ffd6946bebc

Graph databases

These databases store data as nodes and edges and are designed to handle complex relationships between data. For e.g.: Neo4j & Memgraph

Real-time analysis of data relationships is essential to uncovering fraud rings and other sophisticated scams before fraudsters and criminals cause lasting damage. Graph databases are inherently more suitable than RDBMS (relational database management system) for making sense of complex interdependencies central to managing networks and IT infrastructure. Graph-powered recommendation engines help companies personalize products, content, and services by leveraging a multitude of connections in real time. Refer to this blog where we discuss graph databases in detail.

Is everything this good?

While the NoSQL database comes with its set of advantages, there are risks associated with it that one cannot ignore. Some of them are:

  • Lack of standardization: There are many different types of NoSQL databases, each with its own unique strengths and weaknesses. The lack of standardization can make it difficult to choose the right database for a specific application.
  • Lack of ACID compliance: NoSQL databases are not fully ACID-compliant, which means that they do not guarantee the consistency, integrity, and durability of data. This can be a drawback for applications that require strong data consistency guarantees.
  • Lack of support for complex queries: NoSQL databases are not designed to handle complex queries, which means that they are not a good fit for applications that require complex data analysis or reporting.
  • Lack of maturity: NoSQL databases are relatively new and lack the maturity of traditional relational databases. This can make them less reliable and secure than traditional databases.
  • Management challenge: The purpose of big data tools is to make the management of a large amount of data as simple as possible. But it is not so easy. Data management in NoSQL is much more complex than in a relational database. NoSQL has a reputation for being challenging to install and even hectic to manage daily.

Cases where NoSQL Databases should not be used

There are strong use-cases and applications where NoSQL do not help because of the risks and challenges associated. Hence, a user should make a cautious choice to avoid using these databases when:

  • The data size is small: If the application is small, and not collecting any huge data, then RDBMS databases still can be considered as the go-to database choice as they provide much better management and query tools.
  • Scalability is not the question: Many a times, the scalability of the processing is maintained at the application level instead of passing it over to the databases. If one’s application is designed in such a way, then NoSQL databases can still be avoided.
  • Data consistency is utmost critical: For example, a transaction that goes through multiple phases and one wants to maintain the consistency of such transactions at all phases, then RDBMS should be the natural choice to ensure this.
  • Data is structured: There are numerous applications and systems that still totally rely on the nature of data being structured. In those cases, RDBMS should be a natural choice.

In many enterprises, while the day-to-day operational data is stored in the RDBMS to support all above cases, however, for backup and warehouse kind of applications, the same data is moved to NoSQL databases. This helps them manage the scale of data.

NoSQL Databases in ignio

ignio as a system continuously collects information from the customer environment for the IT estate, its relationship with business, and the availability and performance metrics of the estate. This means, the data which is not only huge and consists of capturing combination of complex connections between different components and the dynamic nature of the estate. Multiple databases such as graph database, timeseries database, and the column family store provide a perfect solution for storing such combinations of information such that, they can not only be viewed end to end but also analyzed and insightful information are provided to the end user. Also, since ignio has a capability of continuously analyzing data to generate huge insights, even this information is candidate for storing in the NoSQL database. These results generated do not follow a standard structure and vary depending upon the analysis done.

Concluding thoughts

The trick of best application development and storage lies in understanding the right set of data collected, generated, and analyzed. With the nature and size of such applications and data seen in every walk of life, NoSQL databases have become a need of every enterprise and every domain. These databases with or without mixed with traditional databases, provide many opportunities to application development with ease and quickly. At this junction of the industry and business getting restless with more and more IT needs, these databases play an important role for fulfilling these needs.