Edgica logo

The world is full of information. Databases can store all sorts of data – users’ personal info, collections of pictures, common log entries, music playlists. For data to be easily readable, usable, and accessible we use database management systems (DBMS). Those programs do not only help with reading data but also managing it in different ways – change it to another form, link data together, sort it in a way you want, and finally analyze the information stored.

But before we begin the overview, let’s go over two major groups of databases – relational and non-relational. So how do they differ? The answer can be given with two single words – data structure.

Firstly, we have relational databases. These databases have their data structured in terms of rows which are grouped into tables (also called relations), and they use SQL (Structured Query Language) to interact with data, and that’s where the term “SQL databases” came from. The purpose of relational databases is to manage information organized in tables and to provide concepts of how those tables relate to one another.

In opposition to relational databases, we have non-relational databases, which do not shape stored data into tables. Even though the term “NoSQL” is often used to describe these databases, it does not mean that they do not use SQL. Part “No” in “NoSQL” stands for “Not Only” and indicates that they may or may not support SLQ-like languages. NoSQL uses different data structures (key-value pairs, lists, graphs, documents), which sometimes are considered more flexible than tables.

For the last decade number of non-relational databases grown and the gap between NoSQL and SQL databases shortened thanks to popular NoSQL DBMS’ such as MongoDB and Redis. But even considering that, relational databases are still holding strong leadership and are being used in 20% more cases. According to the information of popular internet portals (Stackoverflow, Percona, and DB-engines), the big three of DBMS’ used by developers all over the world are PostgreSQL, MongoDB, and MySQL. So we’ll take a look at those and two other popular DBMS’ Redis and Microsoft SQL Server.

PostgreSQL
PostgreSQL is an open-source DBMS used mostly for web applications. Although it was one of the earliest databases developed, its support for JSON gives it the ability to manage not only structured data but non-tabular data as well. It can be done to work on different OS’, provides easy tools for receiving information from other DBMS’, provides the choice of a procedural language to use, can work with huge chunks of information measured in TBs (although the speed still may suffer while performing large queries), and can be hosted in both physical and virtual environments. It should be noted that the documentation for PostgreSQL is not that straightforward, so it can be tricky to obtain specific information.

Pros

+ Has the big amount of function as for free DBMS.
+ Can work on many OS’ and has different environments.
+ Can work with NoSQL.
+ Can work with big chunks of data.
Cons

– Does not have much documentation.
– It may require a big portion of the CPU to operate.
– It may be hard to update.

MongoDB
MongoDB is a NoSQL database that uses documents with JSON-like structure. It was designed to manage non-relational models, however, can also work with relational ones (although it may cause problems with performance). It does a great job in situations where other DBMS’ have difficulties, and the reason for this is that it was originally purposed to work with non-shaped data. There is an in-depth list of languages it can be used with, that’s why it is really simple to use no matter which ones you are working with. Moreover, users will have no trouble finding information about what they want to do because of meticulous documentation that is actively updating with new releases. The big downside is the fact that MongoDB has problems with transactions. Even though the latest versions added support for transactions, they still do not work well and may potentially lead to data corruption.

Pros

+ Works fast with non-tabular data and grants quick access to it.
+ Has accurate documentation.
+ Operates well with numerous languages.
+ Can work with shaped data.
Cons

– Does not have built-in SQL support.
– Works slowly with shaped data and is weak in joining it.
– Has problems with transactions.

MySQL
MySQL is yet another open-source DBMS commonly used for web and mobile development that can provide a big amount of functions for users. It is deployable on many OS’, can be managed to work with other Oracle databases if needed, and also has different user interfaces to be used with. But there also are some downsides – support is not available for the free version users, some features that other DBMS’ do automatically can’t be solved quickly with MySQL (such as incremental backups and work with OLAP), and it does not support very large database sizes. The greater focus was made on security, reliability, and low resource usage than on a big amount of features.

Pros

+ Is secure and reliable when it comes to resource usage.
+ Can work with many OS’ and has a list of user interfaces.
+ Can work with other databases.
Cons

– Has no free support.
– Misses features that are basic to other DBMS’.
– Has no tools for visualization of data.
– Does not support big database sizes.

Redis
Redis is not only an open-source key-value database, it is a data store, cache, and message broker. The main advantage of Redis database is caching – its data is stored in cash (temporary storage), so it can be referred to faster when needed. It supports almost every popular language and tons of data structures – from strings and sets to hashes and maps of all kinds. This DBMS is easy to set up and work, although it lacks a better UI. But of course, there are some downsides with the first one being the high scaling of resource consumption as your data grows. Not only that but it also does not support multi-threading, so it won’t benefit from multi-core CPUs usage.

Pros

+ Is simple to work with.
+ Is in-memory and uses cashing.
+ Supports a wide range of languages and data types.
Cons

– Has high resource consumption.
– Does not support multi-threading.
– Has not clear visual UI.

MSSQL
Microsoft SQL Server (MSSQL) is one of the most popular relational DBMS’ used, and there are reasons for that. First of all, it is simply easy to use, is extremely reliable, and has multiple data recovery options. Providing data to other applications is the most common usage of MSSQL, and T-SQL query language helps with that. Being one of the most used DBMS’, it has clear documentation and provides great support for users. It also gives a possibility to work with in-memory databases, but there is a downside – once you enable this option, it cannot be turned off easily. Another downside is that MSSQL does not provide data visualization and analysis tools. Although it has good performance, it also has high requirements when it comes to hardware, especially if you use the latest versions of it.

Pros

+ Is simple to use and manage.
+ Provides the ability to tune the performance.
+ Is reliable and has multiple recovery options.
+ Has accurate documentation and good user support.
Cons

– Might need an enterprise edition to get special features.
– Has high hardware requirements.
– Has no data visualization and analysis tools.

Sometimes it is important to not only save the information but also to see how it was changing over time. That kind of information is called time series data which means data with an attached timestamp, and is often used to store measurements of sensors, which is common to IoT. And there are DBMS’ that were specifically developed to work with this kind of information – time series databases. There are multiple databases that grant the ability to manage time series data, with InfluxDB holding the lead, and followed by TimescaleDB and Prometheus.

InfluxDB
InfluxDB is a free database written with GO language, that provides InfluxQL language to work with measurements (shape of data used). It has a unique protocol for data that contains the name of the measurement, sets of specific tags and fields, and, finally, the timestamp that can have different accuracy. What makes it one of the most useful tools to use for managing time series data is that it has no limitations for the number of fields and their types, which makes its usage extremely flexible.

TimescaleDB
TimescaleDB would be an amazing option for you if PostgreSQL is already being used in your project, as it was built based on it. It operates with time series data quicker than its ancestor and is not hard to work with, so it would be an obvious choice.

Here is a good example of using the TimescaleDB in architecture for the Internet of Things projects – SensorOcean.com, an IoT platform that is based on a massive amount of time-series data from IoT devices and sensors.

And if you’re looking for a DBMS that can present time-related information in form of various graphs – Prometheus would be a great choice, as it is closely related to Grafana visualizing application.

There are numerous DBMS’ to choose from, and the list of them is not limited by the ones mentioned above. The modern market provides a huge assortment of databases, each of which has its own set of features. And that is the purpose why developers should take many factors into consideration when choosing the one DBMS that will lead to the success of their project.
Copyright 2021 Edgica LLC, All rights reserved
Subscribe to our Newsletter!

Subscribe to our Newsletter!

Join our mailing list to receive the latest news and updates from Edgica. We keep your contact information confidential, you always can unsubscribe.

Thank you! Please, check your Inbox to confirm the subscription!

Pin It on Pinterest

Shares
Share This