DEV Community: Rich Edwards

SQL vs. NoSQL: Pros & Cons

Rich Edwards — Tue, 07 Jun 2022 14:51:33 +0000

What’s the best way to store, protect and access your data? This is a fundamental, yet critical decision. After all, data is the cornerstone of success for just about every modern organization. For most companies, the choice comes down to SQL and NoSQL databases. Each has unique strengths and weaknesses.

SQL databases have been a proven option since the 1970s. They are made up of highly structured tables, consisting of rows and columns, related to one other through common attributes. Every column is required to have a value for its corresponding row. NoSQL (“not only SQL” or “non-SQL”) databases came along later to break the relational table straitjacket, with the ability to store and access all data types, structured and unstructured, together. They’re extremely flexible and easy for developers to work with and modify. Learn more about SQL and NoSQL databases and their basic differences.

The best choice? Well, it depends on a whole slew of factors, including your querying, availability, and compliance needs, along with your variety of data types and expected growth.

SQL vs. NoSQL comparison chart

Let’s take a close look at the pros and cons of SQL vs. NoSQL to help you make the right choice.

SQL pros

Standardized schema

While the standardized schema of SQL databases makes them rigid and difficult to modify, it does come with some advantages. All data added to the database must comply with the well-known schema of linked tables made up of rows and columns. Some may find this limiting or confining, but it is helpful when data consistency, integrity, security, and compliance are at a premium.

Large user community

At almost 50 years old, the SQL programming language is extremely mature and still widely used. It has a strong community, with countless experts willing to share tips and well-established best practices. There are many opportunities to sharpen skills and collaborate. If necessary, consultants and SQL vendors can provide additional support. With SQL, your developers will be able to find the answers they need.

No code required

SQL is a user-friendly language. Managing and querying the database can be accomplished using simple keywords with little to no coding required. Most developers are taught SQL in college.

ACID compliance

The extremely structured nature of relational database tables enables SQL databases to be Atomicity, Consistency, Isolation, and Durability (ACID) compliant. This level of compliance keeps tables in-sync and guarantees the validity of transactions. It is likely the right choice when you run applications that have no room for error and need the highest level of data integrity.

Here are the ACID properties:

Atomicity: All changes to data and transactions are executed completely and as a single operation. If that isn’t possible, none of the changes are performed. It’s all or nothing.
Consistency: The data must be valid and consistent at the start and end of a transaction.
Isolation: Transactions run concurrently, without competing with each other. Instead, they behave as though they are occurring successively.
Durability: When a transaction is completed, its associated data is permanent and cannot be changed.

SQL cons

Hardware

The norm for SQL databases is to scale-up vertically, where capacity can only be expanded by increasing capabilities, such as RAM, CPU, and SSD, on the existing server or by migrating to a larger, more expensive one. You’ll need to continually increase hard drive space as your data grows and you’ll need faster machines to run evolving and more sophisticated technologies. The database vendor you use will likely require you to periodically level up your hardware just to run their latest releases. In this environment, hardware can quickly become outdated. Each upgrade is sure to be expensive and resource intensive. SQL’s hardware needs also include ongoing, everyday maintenance and operating costs. It’s a never-ending hamster wheel.

Data normalization

Developed at a time when the cost of data storage was high, relational databases attempt to negate data duplication. Each table has different information and they can be connected and queried using common values. However, as SQL databases get large, the lookups and joins required between numerous tables can slow things down.

Rigidity

A SQL database’s schema must be defined before use. Once in place, they are inflexible, and modifications are typically difficult and resource-intensive. For that reason, substantial time needs to be invested in upfront planning, before the database is ever put into production. So it follows that they’re only appropriate when all of your data is also structured and you don’t expect much change, either in volume or data types.

Resource-intensive scaling

As mentioned earlier, SQL databases normally scale vertically by expanding hardware investment. This is expensive and time-consuming. In some cases, an organization may attempt to horizontally scale a SQL database through partitioning. This added complexity magnifies the time and resources expended. The effort will likely include coding, requiring highly skilled, highly paid developers. As data volume grows, scaling your SQL database is like playing a never-ending game of tag, where the perfect setup is always just out of reach. On the other hand, NoSQL databases scale-out horizontally, making it easier and more cost-effective to expand capacity. They’re a good fit for cloud computing and handling extremely large and quickly growing datasets.

NoSQL pros

Continuous availability

With NoSQL, data is distributed across multiple servers and regions, so there is no single point of failure. As a result, NoSQL databases are more stable and resilient, with continuous availability and zero downtime.

Query speed

Since NoSQL databases are denormalized, with no worry of data duplication, all the information needed for a particular query will often already be stored together — no joins required. This can make lookups easier, especially when working with large data volumes. It also means NoSQL can be very fast for simple queries. Make no mistake, SQL databases can also return very speedy queries. They also support highly complex queries for structured data. However, query speed can quickly taper off as SQL databases grow and complex join requirements increase.

Agility

NoSQL databases were developed as data storage costs were beginning to drastically drop and developer costs were rising. Data duplication was no longer a concern. Instead, they were designed to give developers as much flexibility as possible to boost creativity and productivity. Not constrained by rows and columns, NoSQL database schemas don’t have to be predefined. Instead, they are dynamic with the ability to handle all types of data, including structured, semi-structured, unstructured, and polymorphic.

You can launch NoSQL databases without spending time defining their structure and easily add data types and fields, without downtime, on the fly. All of this makes NoSQL a great fit for modern, Agile development teams. Developers can jump in and start building a database without spending time and effort on upfront planning. As requirements change and new data types are added, it allows them to quickly make modifications. The flexibility and adaptive nature of NoSQL databases make them a great fit for organizations that have a variety of data types and expect to continuously add new features and functionality.

NoSQL databases are not one-size-fits-all. Unlike the SQL databases, they aren’t constrained to a rigid, centralized data model, likely housed on a single server. Instead, NoSQL has the flexibility to connect disparate database model types that can be distributed across many servers. NoSQL includes several database types, allowing developers to find the mix that is the best fit for their data and use cases. The main types of NoSQL databases are key/value, document, tabular (or wide column), graph or multi-model.

Low cost

NoSQL databases scale-out horizontally, making it cost-effective to expand capacity. Rather than upgrading expensive hardware, they can cheaply expand by simply adding commodity servers or cloud instances. And open-source NoSQL databases provide affordable options for many organizations. They’re a good fit for cloud computing and handling extremely large and quickly growing datasets.

NoSQL cons

No standardized language

There isn’t a standard language to conduct NoSQL queries. The syntax used to query data varies for the different types of NoSQL databases. Unlike SQL, where there is just one, easy-to-learn language to master, NoSQL has a steeper learning curve. For example, it might be difficult for a developer to quickly get up-to-speed working on a wide-column database if all their prior experience consists of building and managing graph databases.

Smaller user community

Developers have been using NoSQL databases for more than a decade and the community is growing quickly. However, it is less mature than the SQL community. So, it could be harder to solve undocumented issues. There are also fewer consultants and experts on the NoSQL side.

Inefficiency with complex queries

Flexibility comes with a price. With the variety of data structures found in NoSQL databases, querying isn’t as efficient. Unlike SQL databases, there isn’t a standard interface to conduct complex queries. Even simple NoSQL queries will likely require programming experience. This means more technical and costly staff, like developers or data scientists, will need to perform the queries.

Data retrieval inconsistency

The distributed nature of NoSQL databases enables data to be available faster. However, it can also make it more difficult to ensure the data is always consistent. Queries might not always return updated data and it’s possible to receive inaccurate information. With its distributed approach, the database could return different values, at the same time, depending on which server happens to be queried. This is one of the reasons NoSQL doesn’t achieve ACID-level compliance.

Consistency is the “C” in ACID which states that data must be valid and consistent at the start and end of a transaction. Instead, most NoSQL databases adhere to the Basically Available, Soft state, Eventual consistency (BASE) consistency model, where the E promises consistency at some later point. In the real world, this is often a small delay of only a few milliseconds. For many applications, that likely won’t matter, such as social media posts going live, or an online shopping cart being updated. In those situations, faster availability for most of the network outweighs the value of providing the exact same data at the same time to all users. However, it certainly could matter in some cases, such as when you make an online stock purchase. NoSQL values speed and availability over consistency. Each organization must decide if that aligns with their goals.

Weighing your options

Both SQL and NoSQL databases serve specific needs and use cases extremely well. Depending on your organization’s data environment and goals, specific pros and cons of each could be amplified. You may find the best solution is to use both, letting each type of database play to its strengths. Many organizations use both SQL and NoSQL databases in their cloud architecture, sometimes even within the same application. Then again, the best option could be finding a solution, like DataStax Astra DB, that takes advantage of NoSQL’s inherent benefits, such as flexibility, continuous availability, and scalability, while minimizing its drawbacks.

Astra is a multi-cloud database as a service (DBaaS), built on Apache Cassandra® and Kubernetes, with a microservices architecture. You can be up and running in a few clicks on the cloud of your choice — Azure, Google Cloud Platform or AWS. Once there, it drastically simplifies application development.

Astra has Stargate built-in, providing an open source, data API layer that removes drivers from the equation while allowing you to query your data or create tables and schema without having to learn Cassandra Query Language (CQL). Stargate enables you to interact with data using modern APIs, including schemaless JSON, REST, and GraphQL. And with Astra’s Storage Attached Indexing (SAI), you can query any column in the table, without being restricted to the primary key.

If you’re ready to explore an easier way to build an application with a NoSQL database, take a closer look at Astra DB.

Follow the DataStax Tech Blog for more developer stories. Check out our YouTube channel for tutorials and here for DataStax Developers on Twitter for the latest news about our developer community.

References

NoSQL Use Cases: When to Use a Non-Relational Database

Rich Edwards — Wed, 09 Mar 2022 15:18:04 +0000

For decades, many companies have relied on relational databases to store, protect, and access their data. SQL databases, in particular, worked well for a long time and still do for many use cases. But, today, there is a wide range of situations where SQL databases can no longer satisfy the needs of modern enterprises, especially those that have made the move to the cloud. Increasingly, these companies are turning to NoSQL databases to meet their goals.

NoSQL databases are likely the better choice when:

You have a large volume and variety of data
Scalability is a top priority
You need continuous availability
Working with big data or performing real-time analytics

While this will often make a NoSQL database the right choice, there are many things to consider before making the move. In this post, we’ll explore when NoSQL use cases make sense. First, let’s take a closer look at NoSQL.

What is NoSQL?

NoSQL is short for “not only SQL,” or “non-SQL.” It’s a term used to describe databases that are not relational. To better understand NoSQL databases, let’s first take a look at their alternative, SQL databases.

Developed in the early 1970s, a time when data storage was extremely expensive, SQL databases attempt to minimize data duplication between tables. While extremely organized, this also makes them extremely inflexible and difficult to modify. Since then, the cost of storage has plummeted, while the cost of developer time has dramatically increased. With NoSQL databases, developers are no longer limited to the rigid, tabular approach of relational databases and have far more flexibility to do their best work.

NoSQL comes with many benefits, including:

The choice of several database types — key-value, document, tabular (or wide column), graph, and multi-model — so you can find the best fit for your data.
The flexibility to easily store and access a wide variety of data types together, without upfront planning. The data types can include structured, semi-structured, unstructured, and polymorphic.
The ability to add new data types and fields to the database without having to redefine the data model.
Built-in, horizontal scalability that can handle rapid growth and is much less costly than attempting to scale-out a SQL database.
Continuous availability and strong resilience, due to its horizontal scaling approach.
Ease-of-use for developers that fits well with modern, agile teams.

Learn more about NoSQL.

Comparing NoSQL to SQL

While NoSQL databases have many advantages, they’re not the right choice for every situation. Sometimes sticking with a tried-and-true SQL database is the way to go. Let’s compare SQL and NoSQL databases across several factors. Think about how each would apply to your data profile and use cases.

NoSQL use cases

As you can see, making the choice between a SQL and NoSQL database is not always a straightforward decision. Each has its advantages and disadvantages. Making the right choice depends on your organization’s specific data environment, along with your current needs and future goals. Many development teams actually use both within their cloud data architecture, sometimes even within the same application — deploying each to cover the areas they handle best.

So, what are the non-relational use cases? Here are several where NoSQL has been proven to make sense:

Fraud detection and identity authentication
Inventory and catalog management
Personalization, recommendations and customer experience
Internet of things (IoT) and sensor data
Financial services and payments
Messaging
Logistics and asset management
Content management systems
Digital and media management

Let’s look at the first three NoSQL use cases more closely.

Fraud detection and identity authentication

Protecting sensitive personal data and ensuring only real customers have access to applications is understandably a top priority. Of course, this is only heightened in areas such as financial services, banking, payments, and insurance.

It’s a never-ending battle. Fraudsters are creative and nimble. They tirelessly look for new ways to break the seal and their attacks continue to rise at an alarming rate. Whether you’re trying to prevent illegitimate users from gaining access, or authenticating the identity of your customers, you have to lean heavily on your data.

It’s possible to identify patterns and anomalies to pinpoint fraud in real-time or, in some cases, even before it occurs. To do so, real-time analysis of a large volume of both historic and live data of all types is required, including but not limited to user profile, environment, geographic data, and perhaps even biometric data. And context matters. For example, a $500 withdrawal may not typically be a big deal for a particular customer, but it might raise a red flag if the attempt originates at 3 a.m. in a foreign country.

The stakes to your company’s reputation are higher than ever. One breach or mistake can be quickly amplified with the social media megaphone. It’s a balancing act because setting restrictions too narrowly could result in a false positive rate that can adversely impact the customer experience. You want to make it as easy as possible for customers to use your application or website, while ensuring they actually are who they say they are. It’s quite a tightrope to walk.

This combination of needs, including real-time analysis, large and growing datasets, numerous data types, along with the ability to continuously analyze and conduct machine learning and AI, makes the decision to use a NoSQL database a no-brainer for fraud detection and identity authentication.

Take the case of ACI Worldwide, a company that provides real-time payment capabilities to 19 of the top 20 banks in the world. Their transaction volume is astronomically high, processing trillions of dollars in payments every day, and their data analysis needs are complex.

While payment intelligence systems have used relational databases in the past, that approach struggles to handle growing, large-scale use cases that require complex data analysis. At some point, it becomes impractical and cost-prohibitive to build a relational database big enough to do the job. To have any chance at handling these needs, a SQL database would have to be partitioned. In addition to being extremely resource intensive and expensive, partitioning would have another drawback. For the fraud use case, all information across all dimensions is needed to make each transaction decision. To handle the ever-growing volume, inevitably, a partitioned relational database would have to decrease the window of time of past transactions evaluated. As that time window shrinks, so does the ability to detect fraud.

For effective fraud detection and identity authentication, the data types analyzed extend far beyond transactional information. They could include anything from demographic data, help desk information from the CRM system, website interactions, historical shopping data, and much, much more. It would be impossible to develop a schema upfront that would define everything customers might want to do in the future. This environment requires the flexibility of a NoSQL database where any type of data element can be quickly added to the mix.

Using DataStax Enterprise (DSE), ACI has improved its fraud detection rate and false positive rate, while saving their customers millions of dollars. And ACI’s call center is saving money as fewer false positive cases are routed there.

Inventory and catalog management

NoSQL databases are known for their high availability and predictable, cost-effective, horizontal scalability. This makes them a great match for e-commerce companies with massive, growing online catalogs and loads of inventory to manage.

These organizations need the flexibility to quickly update their product mix, without volume limits. And the worst thing imaginable for them would be to have their site or application go down on Black Friday or during the Christmas holiday season.

For these reasons, Macy’s has made the journey from relational databases to NoSQL. One of the most prominent department stores in the world, Macy’s also has one of the largest e-commerce sites, with billions in annual sales. Like ACI, Macy’s handles a massive volume of data that is diverse and growing. Before the move to NoSQL, the company had a heavily normalized database that limited their ability to scale their catalog and online inventory. Now that DSE and a NoSQL database are in place, this is no longer a source of concern for the Macy’s team.

With their NoSQL database setup, Macy’s can now:

Handle traffic growth and massive volumes of data
Easily and cost-effectively scale
Provide faster catalog refreshes
Grow its online catalog and number of products
Analyze its catalog and inventory in real time

Learn more about Macy’s move to NoSQL.

Personalization, recommendations, and customer experience

Providing a fast, personalized experience is no longer a differentiator. Today, it’s table stakes. Customers expect a consistent, high-quality, tailored experience from your brand, 24/7, across all devices.

They take it for granted. They demand near real-time interactions and relevant recommendations. While it’s still possible to carve out unique, memorable experiences, the first priority is to make sure you have these bases covered. If you don’t, that’s what they’ll remember. And, if that happens, you run the risk of them turning to Twitter or Facebook and amplifying your shortcomings. NoSQL databases are the answer to power the individualized experiences that will keep your customers happy.

That’s because NoSQL databases:

Have fast response times with extremely low latency, even as a customer base expands
Can handle all types of data, structured and unstructured, from a variety of sources
Are built to cost-effectively scale, with the ability to store, manage, query, and modify extremely large volumes of data and concurrently deliver personalized experiences to millions of customers
Are extremely flexible, so you can continuously innovate and improve the customer experience
Can seamlessly capture, integrate, and analyze new data that is continuously flowing in
Are adept at being the backbone for the machine learning and AI engine algorithms that provide recommendations and power personalization

By focusing on providing intuitive, superior online customer experiences from the start, Macquarie Bank, an Australian financial services company, was able to move from no retail banking presence to a top contender in the digital banking space in less than two years. Their focus on truly understanding customer behavior and prioritizing personalization has been a key to their success. So, it’s no surprise they use a NoSQL database (Apache Cassandra with DataStax Enterprise) to provide their customers with near real-time recommendations, interactions, and insights.

Do you have a NoSQL use case?

Hopefully, this post and the non-relational database examples above have provided some guidance about when using a NoSQL database would be the smart move. So, what’s the next step if you determine your company does indeed have NoSQL use cases?

A great place to start is to schedule a demo for DataStax Astra DB, a scale-out NoSQL database built on Apache Cassandra.

Or, if you want to jump right in, you can get started with Astra DB for free.