How To Choose The Right Database For Your Project

Key-value databases

Key-value database is known as the simplest variation of NoSQL databases. In contrast to relational SQL DBs, they can contain data sets that have no predefined structure. This makes them more flexible in terms of data formats they operate – you simply assign a unique key to each data value, and these data values shouldn’t necessarily be of the same format and structure. Calling for a certain key triggers the associated data value.

Such simplicity of key-value databases makes them highly resilient to failure, easy to scale, fast, and high-load. Most often, their structure is used for in-memory databases, which run on a computer’s main memory (RAM), giving them low latency and extremely high throughput at the same time due to the much faster access offered by RAM.

All this makes the key-value in-memory database a good fit for systems requiring quick data access and simple querying. Some good examples of these could be e-commerce solutions (shopping carts, in particular), caching, logging, session management, real-time interactions, personalization functions, and alike. Popular key-value database options include Amazon Dynamo DB, Redis, Aerospike, Memcached.

Document-oriented databases

In a document-oriented database, data is stored in the form of documents, typically in JSON format. Each document can have different structure and types of fields, is self-describing and independent of other documents in the DB. This makes document-oriented databases dynamic: its schema shouldn’t be predefined and, moreover, can be easily modified as new data comes in.

Software engineers favor document-oriented databases for their flexibility and scalability. They can be easily adapted to changeable business needs and can store both structured and unstructured data. Thus, document DBs work best for content management solutions, processing big data, mobile gaming, and even IoT applications. Among the top document-oriented DBs, you can find MongoDB, Couch DB, DocumentDB.

Column-oriented databases

Also known as columnar or wide-column store, this type of database is focused on columns of data, as opposed to rows. In general, the data structure looks similar to a row-oriented database, where each cell of a table row contains different kinds of data about one object. There, when we need to retrieve all the data about this object, for example, an employee information in an HRM system, we can simply call for a single row from the table.

But the situation gets more complicated when we need to filter all the data we have by a certain criteria, for example, find all the employees with the role of a manager. In this case, we need to sort it based on a single column. For a row-oriented database this operation would entail scanning each row for information about the employee role, which is really time and effort-consuming. A column-oriented database will only need to read a single column.

This way, by reading only the needed information in a store, columnar databases require much shorter access time, provide higher retrieval efficiency and more storage size optimization opportunities. Such characteristics make them a great fit for processing big data and analytical workloads. Some examples of columnar databases include Cassandra, HBase, or Azure Tables.

Graph databases

Graph database is a network of nodes connected by edges, the former representing data entities (such as people, accounts, objects) and the latter representing the multitude of relationships between them. Each node can have a number of various relationships with other nodes. This kind of structure makes graph databases a very special type, with relationships between pieces of data equally important to the data itself.

But there are relational databases, what’s so special – you might say. Indeed, relational dbs do store the relationships between pieces of data. But they do it in such a strictly organized and rigid way that querying for complex data relationships may turn into an ordeal.

Just imagine trying to look up the verb “put” and all its relations to other words and linguistic categories in the English language. How much indexing you’d need to include in your query in a relational database in order to retrieve all the possible connections? Meanwhile, in a graph database all these connections would be already reflected with no need for multiple indexing and referencing. Even though this type of database is NoSQL, there’ve been developed several query languages to deal with graph databases specifically, including Cypher, Gremlin, and GraphQL.

Due to the data relationships-oriented approach of the graph databases, they are becoming the core of almost everything related to innovation and data analytics – data intelligence, recommendation engines, fraud detection software, NLP algorithms, machine learning and artificial intelligence, pattern recognition, network management and loads of other emerging applications. Neo4j, ArangoDB, Dgaph are some of the popular examples of graph databases.

Partition tolerance

Any distributed system consists of several nodes. If a node cannot receive any messages from another node in the system, there occurs a partition between the two nodes. In an application, partition can be the result of a network failure or a server crash, for example. Partition tolerance is the ability of the system to continue its operation despite the failure.

Availability

System availability means that every request from the user should elicit a response from the system. Whether the user wants to read or write, they should get a response even if the operation was unsuccessful. This way, every operation is bound to terminate.

Consistency

Data consistency means that the user should be able to see the same data no matter which node they connect to on the system. This data is the most recent data written to the system. So if a write operation has occurred on a node, it should be replicated to all its copies. As a result, users can see that same information when they connect to the system.

Provided there are no partitions – network or server failures – our system is both available and consistent. But in case of a partition we can either keep it available or consistent. In the first case, the system will stay responsive but without guarantee that all the data across the nodes is up-to-date and synchronized. In the second case, it will return an error or won’t respond but ensure that data is the same for every node.

When choosing a database management system, one of the questions to ask is “what is more critical for your application – data consistency or availability?” This will depend on how often your application makes data updates. Databases with the relational data model at their core, such as SQL or NewSQL databases, are designed with the ACID properties in mind and usually provide consistency over availability. Meanwhile, most NoSQL databases, that follow the eventual consistency philosophy, will cater for data availability.

Scalability

At some point of your project, you might realize that your database needs scaling, i.e. extending its capacity to store more data or handle a larger amount if requests, for example. There are two ways databases can scale: vertical scaling and horizontal scaling.

Vertical scaling implies adding more capacity to the system hosting the database by means of configurations, multiprocessors, etc. This kind of scaling is typical of SQL databases which require all data to be stored on one node (server). Meanwhile, horizontal scaling implies adding more servers to the system to increase its capacity. This kind of scaling can be done with NoSQL databases, which store data on distributed servers and you can add as many new servers as you need.

Another important thing is the approach we take when creating an index for a row and how complicated our queries can be. This will affect our database’s scaling capabilities. At the initial development stage, the choice of the database might look quite simple – just choose a database taking into account what type of data you will work with and how you are going to structure it.

But as you move on with your app development trying to add new features, new kinds of data, new user roles, and trying to scale, you might witness as your development has suddenly turned into a headache. For example, our choice is Cassandra, which is a NoSQL columnar database type. If, according to new product requirements, we need to make a complex query to collect all data, we will face a bottleneck as this database is not fully suitable for complex queries with various additional conditions.

Or let’s take MongoDB and start building links between its different collections in the code. We can take a relational database and rest on the fact that our object has 15 options for details, and in order to have a normalized database, we will have to create 16 tables and decide which one to write the details from the code. You can argue that in the new version of relational databases there is a JSON column type. But here you can’t make an index on the field inside it, and writing a request for filtering on it is still a pain in the neck (however not impossible). Another option is to consider the CQRS approach when we have 2 repositories. But, again, this will solve some of our problems – and also add new ones.

So how to choose a database in this case? To solve this dilemma, it’s worth considering the following aspects:

1. Are we dealing with big data?
   If yes – go for Cassanda or HBase which are both non-relational and columnar, and allow you to make column-based queries efficient for working with big data. They are a great fit for OLAP-based systems, thus being applicable for analytic purposes.
2. Read or write operations?
   In complex applications, different types of databases are not equally efficient in carrying out read and write operations. Also, each application will require a different ratio of read and write operations to be performed, as well as their complexity. Thus, we rely on the CQRS pattern – Command and Query Responsibility Segregation. This means we should have separate database query and command models in a system. For example, the NoSQL document-oriented MongoDB will be more convenient for writing data while the relational SQL database Postgres will be a good choice for querying for data.
3. How are your business objects structured?
   A relational database is very suitable for working with structured data. However, if we deal with unstructured or semi-structured data, this also does not mean that we can only consider NoSQL databases. After all, don’t forget about the JSON cell type in Postgres. What can influence our choice here is the performance of your database considering the size, complexity, and schema of your data.

Performance

When we speak of database performance, we can mean several interrelated characteristics of databases. First of all, performance is about the time it takes for a database to pass a query – latency. The lower the latency – the faster a read or write operation goes through. The influencing factors here are network connection and query complexity.

A way to impact network connection to reduce latency is by hosting our application closer to our database, in the same region, for example. As for the query complexity – it’s closely related to the data types and schemas we are using and how “compatible” they are. For example, we can expect low latency from a relational database when handling two-dimensional data that fits well into a row-column order. But once we need to process more data dimensions, our data transactions may get too hard for an RDBMS to operate fast. In such a case, we can shift our attention to NoSQL.

Another aspect affecting database performance is transaction rate, also known as throughput. The higher the throughput a database can handle, the more users can simultaneously query it without hurdling the speed of processing. Apart from software and hardware features which directly impact a database throughput, we can take into consideration the workloads to be dealt with, for example:

• Are these frequent but small transactions as in an oline banking app or less frequent but bulky ones, as in a recommendation engine? – OLTP-based databases are designed to handle such workloads well. Go for a highly-structured SQL or a key-value database that runs in-memory.
• Does a transaction include any data sorting, filtering, search by keywords, or other modifications? – Wide-column databases are designed in a way that allows less resource consumption and better data compression.
• Will the workload depend on a region, time of the day, season of the year? – Database distribution across more clusters can help spread the unexpected workload bounces evenly and save performance.

Are you more certain now about which kind of database could be more suitable for your web or mobile app? We hope you are. But this is not it!