Whether you’re keeping track of corporate accounting records, storing huge amounts of data from a network of IoT devices, tracking your company's inventory systems, or building a web application, a database allows multiple users to update, edit, and maintain a store of information quickly, securely, and efficiently.
That being said, there are many database options available—and each comes with distinct advantages and disadvantages. If you're currently reviewing your database options, this article will give you a bird's eye view of the most popular modern database systems for your needs.
Table of Contents
Relational Database Management Systems (SQL-based) vs. Non-Relational Database Systems (NoSQL-based)
Relational Database Management Systems (SQL-based) vs. Non-Relational Database Systems (NoSQL-based)
Before diving into the most popular modern database options, it's important to understand the difference between a relational database management system (RDBMS, i.e. SQL databases) and a non-relational database management system (NoSQL databases).
For most of the last 40 years, businesses relied on relational database management systems (RDBMSs)—that used the programming language SQL. In the image from ScaleGrid below, you can see that the SQL-based model continues to dominate. ScaleGrid reports that 60.5% of databases in 2019 are SQL-based relational database management systems:
* Image source scalegrid.io.
Still, each year, NoSQL-based non-relational database management systems are becoming more popular—particularly because data scientists want to expose their machine learning business analytics tools to more, unstructured data—so let's look at how these database styles are different.
Relational Database Management Systems (SQL-Based)
Relational database management systems (RDBMSs) use SQL, a database management language that offers a highly organized and structured approach to information management. Similar to the way a phone book has different categories of information (name, number, address, etc.) for each line of data, relational databases apply strict, categorical parameters that allow database users to easily organize, access, and maintain information within those parameters.
The primary reasons why SQL-based RDBMSs continue to dominate are because (1) they are highly stable and reliable; (2) they adhere to a standard that integrates seamlessly with popular software stacks like LAMP; and (3) we've been using them for more than 40 years!
Here are the advantages of RDBMS platforms:
- ACID compliance: If a database system is "ACID compliant," it satisfies a set of priorities that measure the atomicity, consistency, isolation, and durability of database systems. The more ACID-compliant a database is, the more it serves to guarantee the validity of database transactions, reduce anomalies, safeguard data integrity, and create stable database systems. Generally, SQL-based RDBMSs achieve a high level of ACID compliance, but NoSQL databases give up this distinction to gain speed and flexibility when dealing with unstructured data.
- Ideal for consistent data systems: With a SQL-based RDBMS, your information will remain in the structure you originally create. If you don't need a dynamic information system for massive amounts of data—and you're not dealing with numerous data types—an RDBMS offers great speed and stability.
- Better support options: Because RDBMS databases have been around for over 40 years, it's easier to get support, add-on products, and integrate data from other systems.
Here are some of the disadvantages of RDBMS solutions:
- Scalability challenges and difficulties with sharding: RDBMSs have a more difficult time scaling up in response to massive growth compared to NoSQL databases. These databases also present challenges when it comes to sharding. CitusData explains: "When your database is small (10s of GB), it’s easy to throw more hardware at the problem and scale up. As these tables grow, however, you need to think about other ways to scale your database [...] Sharding enables you to linearly scale your database’s CPU, memory, and disk resources by separating your database into smaller parts." If you're dealing with a conservative database that you don't expect to change a lot in the years ahead, the sharding and scaling challenges related to RDBMS solutions may never apply to you. On the other hand, if you plan to scale up and grow in the years ahead, a non-relational database system (NoSQL-based) could be a better match for your needs.
- Less efficient with NoSQL formats: Most RDBMSs are now compatible with NoSQL data formats, but they don't work with them as efficiently as non-relational databases.
Here are three of the most popular RDBMS/SQL database engines (which we'll talk about in more detail below):
Non-Relational Database Systems (NoSQL-based)
Imagine you're tasked with managing large amounts of unstructured data—like text from emails and customer surveys, data collected by a network of mobile apps, or random social media information. The information is disorganized. There isn't a clearly-defined schema like you would find an RDBMS. You can't store it in an RDBMS. But you can with a non-relational (or NoSQL) database system.
Non-relational databases let you organize information in a looser fashion—kind of like dropping the information in different file folders. This is important for two reasons: (1) you can store unstructured information and expose it to powerful business intelligence systems that will analyze it with AI algorithms; and (2) you can store unstructured data that you plan to structure later.
Another reason why non-relational databases are important is that they work with NoSQL formats like JSON, which has become essential for web-based applications that let websites update "live" without needing to refresh the page. Here, it's worth taking a moment to understand why JSON matters with an excerpt from the CopterLabs blog:
"With the rise of AJAX-powered sites, it’s becoming more and more important for sites to be able to load data quickly and asynchronously, or in the background without delaying page rendering. Switching up the contents of a certain element within our layouts without requiring a page refresh adds a “wow” factor to our applications, not to mention the added convenience for our users. Because of the popularity and ease of social media, many sites rely on the content provided by sites such as Twitter, Flickr, and others. These sites provide RSS feeds, which are easy to import and use on the server-side, but if we try to load them with AJAX, we run into a wall: we can only load an RSS feed if we’re requesting it from the same domain it’s hosted on.
[Exception... "Access to restricted URI denied" code: "1012" nsresult: "0x805303f4 (NS_ERROR_DOM_BAD_URI)" location: "http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js Line: 19"]
JSON allows us to overcome the cross-domain issue because we can use a method called JSONP that uses a callback function to send the JSON data back to our domain. It’s this capability that makes JSON so incredibly useful, as it opens up a lot of doors that were previously difficult to work around."
At the end of the day, by applying a looser structure to data and working with NoSQL data structures, non-relational database users receive some noteworthy benefits:
- Excellent for handling "big data" analytics: The main reason why NoSQL databases are becoming more popular is that they remove the bottleneck of needing to categorize and apply strict structures to massive amounts of information. NoSQL databases like HBase, Cassandra, and CouchDB support the speed and efficiency of server operations while offering the capacity to work with large amounts of data.
- No limits on types of data you can store: NoSQL databases give you unlimited freedom to store diverse types of data in the same place. This offers the flexibility to add new and different types of data to your database at any time.
- Easier to scale: NoSQL databases are easier to scale. They're designed to be fragmented across multiple data centers without much difficulty.
- No data preparation required: When there isn't time to design a complex model, and you need to get a database running fast, non-relational databases save a lot of time.
Here are some disadvantages related to non-relational database designs:
- More difficult to find support: Because the NoSQL community doesn't have 40 years of history and development behind it, it could be more difficult to find experienced users when you in need support.
- Lack of tools: Another disadvantage relating to "newness" is that—compared to SQL-based RDBMS solutions—there aren't as many tools to assist with performance testing and analysis.
- Compatibility and standardization challenges: Newer NoSQL database systems also lack the high degree of compatibility and standardization offered by SQL-based alternatives. Therefore, you may find that the data in your non-relational database management system doesn't readily integrate with other products and services.
Here are two of the most popular non-relational database systems (which we talk about in more detail below):
Interestingly, most RDBMSs—like Oracle, MySQL, and Microsoft SQL Server—now offer NoSQL functionality. Nevertheless, non-relational DBMSs like PostgreSQL usually work better with these NoSQL formats. A Stack Overflow user offers one example of why that's the case:
"Mysql isn't as good at subqueries as PostgreSQL, but the JSON functionality limits your ability to perform joins against other tables. In contrast, PostgreSQL has a whole heap of functions that can return rows."
Overview of the Most Popular Modern Database Systems
If you're shopping for a DBMS, you will probably choose one of the five popular database engines below. In all cases, these databases have support for both the SQL and NoSQL data formats. However, the non-relational databases—like PostgreSQL and MongoDB—tend to work better with NoSQL formats. The relational databases—like Oracle, Microsoft SQL Server, and MySQL—work better with purely SQL formats.
Here are the most popular databases that companies are using in 2019:
(1) Oracle Database
Oracle has provided high-quality database solutions since the 1970s. The most recent version of Oracle Database was designed to integrate with cloud-based systems, and it allows you to manage massive databases with billions of records. Moreover, Oracle lets you organize data via a “grid framework” and it uses dynamic data masking for an additional layer of security. Traditionally, Oracle has offered RDMBS solutions, but now you can find SQL and NoSQL database solutions too.
Here are some of the main advantages of using Oracle:
- The most advanced technology: Oracle is known for being on the leading edge of database technology. They have a long-standing reputation for bringing quality – along with the latest features and innovations – to their customer base.
- A wide range of solutions: Oracle offers a massive suite of tools and solutions that can address virtually any information challenges you encounter.
Here are some of the main disadvantages of using Oracle:
- An expensive solution: Oracle tends to be a high-cost solution that smaller, non-enterprise-level organizations might not be able to afford.
- System upgrades might be required: Your current system specifications might not be enough to implement Oracle. Many businesses have to upgrade their hardware before using Oracle solutions.
If you’re a large organization that needs to manage a massive amount of data, Oracle could a match for your needs.
MySQL is a free, open-source RDBMS solution that Oracle owns and manages. Even though it’s freeware, MySQL benefits from frequent security and features updates. Commercial and enterprise can upgrade to paid versions of MySQL to benefit from additional features and user support. Although MySQL didn't support NoSQL in the past, since Version 8, it provides NoSQL support to compete with other solutions like PostgreSQL. Read more about the differences between MySQL and PostgreSQL here.
The free version of MySQL focuses on speed and reliability instead of all of the bells and whistles found in many modern database designs. For example, MySQL:
- Only uses simple SQL commands.
- Doesn't support XML.
- Only allows two character types: CHAR and VARCHAR.
- Doesn't allow incremental backups.
- Is missing other features offered by its competitors.
Still, MySQL gives you the ability to use a lot of different types of storage engines, which brings the flexibility to deal with a wide variety of data types. Plus, a user-friendly interface and batch commands offer the ability to efficiently manage, edit, and process large amounts of information.
The advantages of using MySQL include:
- It’s free! As an open-source RDBMS solution, MySQL is free to use in any way you want.
- Excellent for any size organization: MySQL is an excellent solution for enterprise-level businesses and small startup companies alike.
- Different user interfaces available:
- Highly compatible with other systems: MySQL has a reputation for being compatible with many other database systems.
The disadvantages of using MySLQ include:
- Missing features common in other RDBMSs: Because MySQL prioritizes speed and agility over features, you might find that it’s missing some of the standard features found in other solutions, like the ability to create incremental backups, for example.
- Challenges getting quality support: Because it’s a free solution, you won’t have a team a support team at your disposal to help you through challenges, unless you purchase an upgraded MySQL package from Oracle. That being said, MySQL does have an active volunteer community, useful forums, and lot of documentation that you may find useful to answer most of your questions.
MySQL is a particularly valuable RDBMS solution for businesses that need a solution with enterprise-level capabilities, but are operating under strict budget constraints. As a free, but extremely powerful and reliable modern RDBMS, you can't go wrong with MySQL.
(3) Microsoft SQL Server
Microsoft SQL server is a database engine that is compatible with both on-site and cloud-based servers. Plus, not long after releasing Microsoft SQL Server 2016, Microsoft unveiled a Linux version of the database engine in addition to the Windows version.
Microsoft SQL Server has a variety of versions available to choose from. This article details the differences in each, though in most developers prefer the latest release (2016). An interesting addition in the 2016 release is temporal data support. This allows you to keep track of changes to information in the database over time. According to SQL with Manoj:
"A Temporal Data is tightly related to a Time period and used to handle the time-varying data. It provides the ability to look at data trends, types of data changes, and the overall data evolution within your database [...] With a Temporal Table, the value of each record at any point in time can be determined, rather than just the current value of each record."
Also, Microsoft SQL Server currently supports dynamic data masking, which boosts security by masking sensitive information from non-privileged users. SQL with Manoj explains:
"This feature helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer."
Advantages of using Microsoft SQL Server include:
- It's mobile: This database engine allows you to access dashboard graphics and visuals via mobile devices.
- Integrates with Microsoft products: Companies that rely heavily on Microsoft products will enjoy the way SQL Server integrates easily with these applications.
- It's fast: Microsoft SQL Server has built a reputation around being fast and stable. Here are some posts from Microsoft that try to explain why this DBMS is fast and reliable (warning: they're very technical).
Disadvantages of using Microsoft SQL Server:
- It's expensive: Considering that there are plenty of free database engines available, the cost of Microsoft SQL Server is steep. It's over $14,000 for one enterprise-level license per core. There are scaled down licensing options for approximately $3,700 and $900, and a free version you can use to to test the platform.
- Requires a lot of resources: This resource-heavy RDBMS may require you to purchase better hardware. A reviewer on G2Crowdsays, "Despite having a lot of tools it is problematic to use this software because it is very heavy and it consumes a lot of computer resources, so we have to invest in good hardware that guarantees us to work efficiently."
If you're an enterprise-level corporation that relies heavily on Microsoft products, the speed, agility, and reliability of Microsoft SQL Server could be an excellent match for your needs.
PostgreSQL is an open-source, free database engines with unlimited scaling capabilities. If you look to the section above regarding NoSQL or "non-relational database systems," PostgreSQL is an excellent option that supports both relational and non-relational formats.
As a highly trusted DBMS that has been around since the early 1990s, PostgreSQL as a devoted user base and won the Database of the Year Award for the last two years in the row (as a result of being the fastest-growing database engine).
An interesting feature of PostgreSQL is its history of working with both structured (SQL) and unstructured (NoSQL) data. PostgreSQL is highly extensible due to having a catalog-driven operation. It doesn’t simply store information to identify tables and columns; it allows you to define data types, index types, and functional languages. It's also compatible with most operating systems, including Linux platforms, and it integrates well with data from a wide variety of databases. Lastly, PostgreSQL works with on-site servers and cloud-based servers.
Even though it's a non-profit, free database system, a large network of devoted followers and volunteers offer free support to users and regularly update the system. For example, a recent upgrade (PostgreSQL 9.5) expanded the size of data volumes and how many concurrent users the system supports.
Here are some of the most important benefits of PostgreSQL:
- More features: PostgreSQL has a lot more features than others DBMSs, as these users attest. These extra features include table inheritance, a rich set of data types (including native support for JSON), ability to define a column as an "array" of column types, and other features.
- Highly ACID Compliant: PostgreSQL consistently ranks as the most ACID compliant DBMS, so this database engine could be an excellent choice if you're concerned about the integrity of data.
- Massive Scalability: PostgreSQL can work with massive database tables. According to this writer, "the maximum size of tables in PostgreSQL is (1) 32 Terabytes (32TB) in PostgreSQL 9.6 or earlier; (2) 2 Exabytes (2EB) in PostgreSQL 10 – 2048PB (Petabytes) or 2 million TB; (3) 0.137 Lottabytes in PostgreSQL 11.
Here are a few potential downsides of using PostgreSQL:
- Lacking in Documentation: PostgreSQL doesn't have the best documentation compared to alternative database engines. Therefore, if you run into difficulty, you may need to seek assistance from a private PostgreSQL support firm, or try your luck with the community support forums.
- Speed issues during read-only operations: PostgreSQL excels with read-write operations for data that needs validation, but slowdowns could happen when working with read-only operations.
Since PostgreSQL is completely free and scalable, this is an excellent solution for companies of any size, those with budget constraints don't need to worry about costs. Also, if your use case can benefit from a DBMS with native JSON support PostgreSQL is probably for you. Read more about whether you should choose PostgreSQL or MySQL here.
MongoDB is a free, open-source database engine built especially for applications that use unstructured data. Because most DBMSs were built for structured data—even if add-ons allow them to handle non-relational data now—MongoDB often excels where other DBMSs fail. MongoDB works with structured data too, but since this database engine wasn't designed for relational data, performance slowdowns are likely.
MongoDB connects non-relational databases with applications by using a wide variety of drivers (based on the programming language of the application). The most recent versions of MongoDB include pluggable storage engines. Upgraded text search features are also available, along with partial indexing features which can help with performance.
The benefits of using MongoDB:
- NoSQL support: This DBMS was specifically made to support JSON and NoSQL data.
- Highly flexible: Since MongoDB stores and manages any kind of information, developers face fewer restrictions when incorporating data into a MongoDB database.
- Great for applications including web-based apps: MongoDB has become a popular DBMS for web-based applications.
The disadvantages of Using MongoDB:
- No SQL querying: MongoDB will not accept SQL queries. You can use additional tools to translate your SQL queries to work with this database engine, but this will be inconvenient.
- Difficult to set up: MongoDB takes time and more experience to set up properly than other solutions.
- Lack of security: The native settings on MongoDB don't tend to be very secure. Additional steps will be required to secure this database.
According to MongoDB, If you're building an application on top of an operational database "where you need a millisecond response time" this DBMS could be the right choice for you. If you're building a data warehouse for analytics purposes, you might want to use a different platform.
Xplenty: Advanced Data Integration Solutions
This overview of modern database engines should give you a better understanding of your DBMS options. Nevertheless, choosing your database is just the first step. Later, you might need to integrate data into your database that's not readily compatible. Or, you might need to integrate your database with a larger data warehousing solution. This is where Xplenty's advanced data integration tools can help.
Xplenty's ETL solutions help you extract and transform all kinds of data to work with your data database and warehouse. If you'd like to learn more about our technology, contact our team and tell us about your data integration needs!