How to Сhoose a DBMS to Solve Your Tasks
Hi, my name is Yevgeny Samoilenko, and I am the Head of RnD. I work for a European Fintech company. I have over 15 years of experience in Fintech, in the software development for banks, central banks, insurance organizations, funds, money market, payment providers е.t.с. We developed almost every software using a database, and from time to time I saw developers confused when they were choosing the right database. Sometimes it is not obvious what type of DBMS would be the best choice.
This is the first article in a series about database management systems, in which I will try to tell you in simple words what solutions exist on the DBMS market. Also, I’ll explain what products to choose from and how to make the right choice according to your tasks.
My professional activities include job interviews of new hires. I have noticed that when asked “What types of DBMS exist?” many recall NoSQL and relational databases. But people often forget about DBMS types or can’t convey the idea of the difference between them. Here is the list of the most commonly used DBMS:
● Relational
● Graph
● Document
● Column-oriented
● Key-value
Check out the table at the end of the text where I sum up the article, namely, in which cases a particular type of DBMS is suitable and what popular solutions you might find applicable. If you’d like to skip a long explanation, you’re welcome to move on straight to the table. I should point out that some major vendors are equipped with several types of DBMS, both as standalone products and as internal implementations. As for Oracle, there isn’t a thing they haven’t got: a classic relational database; a separate Oracle NoSQL Database product that can be used as a column, document, and key-value; Autonomous Data Warehouse, specialized for data warehouses; Oracle Graph Server for graphs, and many other products. One can write a series of articles on it — I guess I’ll do this. For now, I’ll go on and tell you more about each type of DBMS.
Types of Database Management Systems. How to Choose the Right Option
Relational DBMS
These are classic systems used most often to build OLTP solutions (Online Transaction Processing). With these solutions, the DBMS works with many small transactions. The system requires a short response time and the ability to undo any changes made during the transaction under certain conditions. If you are building a system where you’d like to store a significant number of entities (tables) with different types of relations between them (one-to-one, one-to-many, many-to-many), then most likely you need a relational DBMS.
Most well-known relational DBMS
● Oracle
● Microsoft SQL
● PostgreSQL
● MySQL
● IBM DB2
When to choose a relational database
The main sign that indicates you need a relational DBMS is high data normalization. An additional hint is the need to process many short transactions with a large percentage of insertion transactions.
When you shouldn’t choose a relational DBMS
If you’d like to store unstructured data, it’s better to consider a document DBMS. If you need to store very simple key-value structures, choose specialized key-value DBMS.
Another sign that it makes sense not to opt for relational DBMS is the need to update values in the same rows frequently. This might be costly to relational DBMS — you have to use “advanced magic” to do everything correctly. In cases when there is an expert who wields this very magic, ignore this recommendation. Or if you know exactly how to do it.
Graph DBMS
It is a specific type of DBMS designed to deal with graphs, their nodes and properties, and arbitrary relationships between nodes. A simple example is building applications like social networks. There you need to store connections between users (nodes) according to different criteria: common interests, colleagues, relatives.
Most well-known graph DBMS
● Neo4j
● Amazon Neptune
● InfiniteGraph
● InfoGrid
When to choose a graph database
It’s worth paying attention to graph DBMS if you’re building some kind of a social network or implementing a recommendation and rating system. Also use it, when you have a deep understanding of what graphs are and what they are for.
When you shouldn’t choose a graph DBMS
In almost all other cases except those mentioned above.
Document DBMS
Document or document-oriented DBMS is one of the most popular NoSQL DBMS varieties, where the basic unit of the logical data model is a structured text with a specific syntax (document).
It is believed that the data model in document DBs is similar to the data model in object-oriented DBs. There is some truth to this, but the difference between them is that document databases only store state, but not objects’ behavior. Document DBMS are actively developing, and some of them even support schema validation.
Most well-known document DBMS
● CouchDB
● MongoDB
● Amazon DocumentDB
When to choose a document database
The application scope of document DBMS is wide. It can be used as a compact DB for a single microservice or large-scale solutions as a state store for something. It is best if you want to store objects in the same entity but with different structures. It is also of great use when you need to store structures (including objects, lists, and dictionaries), especially in a format similar to JSON.
When you shouldn’t choose a document DBMS
A document system is not the right solution to implement a transactional model, and certainly not the best option to render accounts.
Column-oriented DBMS
They are very unlike relational DBMS. Yes, they also consist of rows that have attributes and are grouped in tables. The differences in logical models are minor. But at the physical storage level, the differences are significant.
Relational DBMS stores data row-by-row. This means that to read the value of a particular column, you will have to read almost the entire row, at least from the first column to the desired column. In a column-oriented DBMS, data is stored column-by-column. That is, a column appears as a separate table. Reading will be performed right from a particular column. In practice, it works very fast — I’ve tested it on several implemented data warehouses.
Advantages of column-oriented DBMS:
● efficiently execute complex analytical queries for a large amount of data
● simple and almost instantaneous restructure of data tables
● considerable compression that saves space significantly
Most well-known column-oriented DBMS
● Sybase IQ (SAP IQ)
● Vertica
● ClickHouse
● Google BigQuery
● InfoBright
● Apache Druid
When to choose a column-oriented database
The main reason to use a column-based DBMS is that you want to build a data warehouse and plan to make selections with sophisticated analytical calculations. An indirect indicator is if the number of rows to be queried exceeds hundreds of millions.
When you shouldn’t choose a column-oriented DBMS
If the number of rows in the table from which you are querying is less than hundreds of millions of rows, a column-oriented DBMS will probably not have many advantages over a relational one.
Given the specifics of the system, a column-oriented DBMS will be inefficient if your queries are simple enough and the parameters of each query are static.
Keep in mind that column-oriented DBMS may have other limitations. For example, the query language may be different from classic SQL, or there may be no transactional support.
Key-value DBMS
This is probably one of the simplest types of DBMS. It’s a kind of a table with a unique key and a bound value that can have anything in it. Such DBMS are most often used for caching because they are fast — it is because there is a unique key and a query returns only one value.
Some key-value DBMS can completely run in memory and some have an option to set a time to live (TTL) for a record after which the record is automatically deleted.
Most well-known key-value DBMS
● Redis
● Memcached
When to choose a key-value database
This system is ideal if you need a DBMS for caching data or message brokers. It’s also great for databases where you need to keep fairly simple structures and have quick access to them.
When you shouldn’t choose a key-value DBMS
Don’t choose this one if you store a lot of entities (tables) in the database, and the entities have complex structures with different data types. Also, consider another option if you expect to make complex queries that return lots of rows.
Conclusion
There are a lot of different types of DBMS and choosing the right one could be a headache sometimes. I believe I made this matter a little bit clearer and now you can easily choose the right DBMS in a minute if not a second.
If you are planning to build a complex solution and feel that you need many types of DBMS — go for it, that’s fine. Also, don’t try to immediately decide on a DBMS vendor, usually, this is a secondary topic.
Pick a DBMS type based on these three things:
● type of tasks you need to solve
● types of data to be processed
● growth prospects and scaling
Pay attention to the popularity of DBMS — it guarantees a wide range of developers and development tools to help you find a quick answer to your question.
In the table below, I concisely compiled everything I’ve covered in this article.
I hope I was helpful. In the next articles of the DBMS series, you’ll learn how to choose between cloud and on-premise solutions, paid and free options.
I love helping organizations to empower their businesses with data, so I’d love to hear your thoughts on the above in the comments below, and feel free to connect with me on LinkedIn.
