by Open Source Database Software – Data is everything. Databases are also essential. These are some great open-source options for your next kick-ass endeavor. Another part of the equation is the rise of new business models. These businesses offer a community version and a commercial add-on.
In a world that has been dominated for so many years by database specialists like SQL Server and Oracle, there seems to be an inexorable flow of solutions. Open Source is a key factor in this innovation. It’s a great way for talented developers to get their hands dirty and create something they love.
There are more databases than one person can keep up with. Although there is no official statistic, I am confident that we have more than 100 options today if we combine all the stack-specific object databases with not-so-popular university projects.
It frightens and frightens, I know. Too many options, too much documentation — and a short life.
This is why I wrote this article. It contains ten of my favorite databases that you can use to enhance your solutions, regardless if you are building them for yourself or others.
No MySQL
This list does not contain MySQL. It is, however, the most widely used Open Source database solution.
Why?
MySQL is everywhere, it’s what everyone knows first, it’s supported almost every CMS and framework, and it works well for most uses. MySQL does not need to be “discovered”.
The following options are not alternatives to MySQL. They might be an alternative to MySQL in some cases. In other cases they may not. You don’t have to worry; I will also be discussing their use.
Special Note: Compatibility
Before we get started, let me remind you that compatibility is important. Your options are limited if your project supports only one database engine.
This article will not be of any use if your site is WordPress. Similar to static websites on JAMStack, it is not a good idea to look for other options.
You will have to determine the compatibility equation. Here are some suggestions for those who have a blank slate, and can choose the architecture.
PostgreSQL
If you are from PHP land (WordPress or Magento, Drupal, etc. PostgreSQL may sound unfamiliar to you. This relational database software is a popular choice in communities such as Ruby, Python and Go.
Many developers “graduate” to PostgreSQL because of the features or for its stability. Although it’s difficult to convince someone with a brief write-up, PostgreSQL is a well-engineered product that will never let you down.
Many SQL clients are available to connect to PostgreSQL for development and administration.
Unique Features
PostgreSQL offers many interesting features compared to relational databases like MySQL.
- Built-in data types for Array, Range, UUID, Geolocation, etc.
- Native support for document storage (JSON style), XML and key-value storage
- Synchronous or asynchronous replication
- Scriptable in Perl, Python and PL
- Full-text search
My favorite features are the geolocation engine, which takes the pain out of working with location-based applications — you can manually find all the points nearby and you’ll see what I mean), and support for arrays. Many MySQL projects have been canceled because arrays are not available. Instead, many MySQL projects have chosen to use the well-known comma separated strings.
When should you use PostgreSQL
PostgreSQL is better than any other relational databases engine. If you have ever been bitten by MySQL, now is a great time to look into PostgreSQL. Friends of mine have given up on dealing with MySQL’s mysterious transactional lock failures, and are now able to move on. You won’t overreact if you make the same decision.
PostgreSQL has an advantage when you need partial NoSQL capabilities for a hybrid model. Document and key-value storage are supported natively, so you don’t have to search for, install, learn, maintain, or even modify another database.
PostgreSQL is not compatible with data models that aren’t relational or require very specific architectural requirements. Analytical is an example of a system where new reports are created constantly from existing data. These systems can be read-heavy and are susceptible to a rigid schema. PostgreSQL does have a document storage engine. However, things can get messy when dealing with large data sets.
Also, if you don’t know what you are doing, you should never use PostgreSQL!
If you are interested in learning more, check out this PostgreSQL for Beginners course.
MariaDB
MariaDB was developed by the same person who created MySQL.
Confused?
Actually, MySQL was actually taken over in 2010 by Oracle (by purchasing Sun Microsystems which, incidentally is also how Oracle got to control Java), and the original creator of MySQL created a new open-source project called MariaDB.
You may be wondering why all the boring details are important. MariaDB was built from the same code base that MySQL. This is called “forking” an already existing project in the open-source community. MariaDB is therefore a “drop in” replacement for MySQL.
This means that if you are using MySQL and want MariaDB to migrate your data, it is so simple you won’t believe what you’re seeing.
This is not a way to go back from MariaDB to MySQL. It is impossible to go back to MariaDB from MySQL. If you forcefully try to force the issue, permanent database corruption will be yours!
Unique features
MariaDB is not a MySQL clone. Since the introduction of MariaDB, there have been increasing differences between the two databases. Your decision to adopt MariaDB must be well thought out. MariaDB has many new features that can help you make the transition.
- MariaDB is truly open and free from any corporate control. This means that you are free from sudden predatory licensing or other worries.
- There are many storage engines available for special needs. These include the Spider engine for distributed transactions, ColumnStore for massive data warehousesing, and the ColumnStore engine to parallel, distributed storage.
- Performance improvements over MySQL, mainly due to Aria’s storage engine for complex questions.
- Dynamic columns to create different rows on a table
- Improved replication capabilities, such as multi-source replication
- There are many JSON functions
- Virtual columns
. . . There are many more. It can be exhausting to keep up all the MariaDB features.
MariaDB
MariaDB is a great alternative to MySQL. They are open to innovation and won’t be reverting to MySQL. MariaDB’s new storage engines can be used to complement your existing relational data model. This is an excellent use case.
Why not use MariaDB
This is the only issue. It’s becoming less problematic as MariaDB is supported by projects such as WordPress, Joomla and Magento. I would not recommend MariaDB to fool a CMS that doesn’t support it. Many database-specific tricks can easily cause the system to crash.
You can see the differences between MariaDB and MySQL. Also, check out the MariaDB installation guide.
CockroachDB
CockroachDB’s team seems to be made up of masochists. They must want to win despite all odds with a product named like CockroachDB.
Well, not quite.
“Cockroach” refers to an insect that is built for survival. The cockroach will survive any situation, including floods, predators, floods and bombing.
CockroachDB was created by former Google engineers. It is based on the idea that CockroachDB’s team was frustrated at the limitations of traditional SQL solutions for large-scale use. This is because SQL solutions are supposed to be hosted on one machine historically (data was not that large). MongoDB was the first to be able to create a cluster of SQL databases.
It was difficult to use replication and clustering in MySQL, PostgreSQL and MariaDB. CoackroachDB hopes to change this by bringing easy sharding and clustering to the world SQL.
CockroachDB
CockroachDB – This is the ultimate system architect’s dream. CockroachDB is for you if you are a SQL expert and have been pondering the scaling capabilities of MongoDB. You can now quickly create a cluster and run queries on it. Then you can sleep well at night.
Why not use CockroachDB
It’s better to know the devil than the one you don’t. If your current RDBMS works well for you, and you believe you can handle the scaling pains it presents, then keep it. CockroachDB is an innovative product that will be used by all the brilliant people involved. You don’t want it to cause problems later. CockroachDB’s SQL compatibility is another reason. If you do complex SQL stuff or rely on it for critical tasks, CockroachDB may not be the right tool for you.
We will now be looking at non-SQL (or NoSQL) database solutions to highly specialized requirements.
ClickHouse
Are you looking for an open-source, fast OLAP database system that is easy to use?
Get ClickHouse.
To answer each query faster, it uses all hardware to the maximum extent possible. Two terabytes per second is the peak speed at which a query can be processed. Reads are automatically balanced among healthy replicas to avoid latency.
You can use it across multiple data centers, as well as multi-master async replication. You can avoid single failure points because all nodes are kept equal. System availability will not be affected by the downtime of a single node, or of the entire data center.
ClickHouse is simple to use. ClickHouse streamlines data processing and organizes all data in a system. It also makes it easy to create reports. SQL dialect allows you to express the result without having to use any other API than what is available in alternative systems.
This database management system can be used to set up a distributed system on different nodes that are free from failure points. It also has robust security features, including enterprise-grade security, fail-safe mechanisms, and human error detection.
ClickHouse is able to process queries more quickly than row-oriented systems with the same CPU and I/O throughput. The columnar data storage format allows for more data to be stored in RAM, which results in faster response times.
Commodity hardware with rotary disk drives can reduce the total ownership cost without sacrificing latency. It optimizes disk drive access and minimizes data transfer.
Moreover, the SQL database is feature-rich and can quickly process queries, join distributed and co-located data, manage denormalized information, and much more. ClickHouse can scale horizontally or vertically, and it adapts easily to work on one server or clusters of thousands.
ClickHouse is a web and application analytics platform that offers telecommunications and ad network as well as online games, IoT and business Intelligence, finance and eCommerce monitoring.
It can integrate with Hadoop and Postgres.
You don’t have to set up and install a server. Kamatera offers ClickHouse in one click.
Neo4j
Connected data is one of the most important developments of the past decade. Connected data is a huge development in the last decade. The world around us doesn’t have any partitions — it’s one big mess with almost everything connected.
Social networks are an example of this. Building a data model similar to that using SQL or document-based databases can be a nightmare.
This is because graphs are a completely different data structure. You will need a graph database such as Neo4j.
This example was taken from Neo4j’s website. It shows how university students can be connected to their courses and departments. This data model will be difficult to create with SQL because it will make it hard to avoid endless loops and memory overruns.
Unique features
Neo4j is the only way to work with graphs. Graph databases are very unique. It has unique features as a result.
- Support for graph analytics and transactional applications.
- Data transformation abilities for digesting large-scale tabular data into graphs.
- Cypher is a special query language that allows you to query the graph database.
- Visualization and discovery of features
It is not necessary to discuss whether or not to use Neo4j. Neo4j is required if you require graph-based relationships between data.
MongoDB
MongoDB is the first non-relational data base to make waves in the tech world and continues to be a dominant force.
MongoDB, unlike relational databases is a “document-based database” that stores data in chunks with related data clumped together within the same chunk. It is easy to understand this by looking at an example of an aggregation JSON structure.
This object contains the user’s contact information and access levels, which is not possible with a table-based structure. The user object is automatically fetched by fetching the associated data. There’s no notion of a join. This is a more in-depth introduction to MongoDB.
Unique features
MongoDB is full of serious features (I almost want “kick-ass”, but that wouldn’t be appropriate on a public site) that have caused many seasoned architects to leave the relational world.
- A flexible schema for specialized/unpredictable use cases.
- It is so easy to use clustering and sharding. It is as easy as setting up a cluster configuration and forgetting about it.
- It is easy to add or remove a node from an existing cluster.
- Distributed transactional locks. This feature was absent in earlier versions, but it was finally added.
- It’s optimized for fast writes, making this highly suitable for analytics data caching.
MongoDB’s advantages are hard to oversell, so I apologize if I sound like a MongoDB spokesperson. Although NoSQL data modeling can be confusing at first and many people never master it, it is almost always preferred to a table-based scheme for architects.
MongoDB
MongoDB bridges the gap between the structured and strict world of SQL and the chaotic, almost confusing world of NoSQL. Because there is no schema to worry, it excels at creating prototypes. And when you truly need scale, it’s also very easy to use. You can use a cloud SQL server to solve your DB scaling problems, but it’s expensive!
There are some use cases in which SQL-based solutions won’t work. A relational database is best for creating products like Canva where users can create complex designs and then be able edit them later.
Why not use MongoDB
MongoDB’s complete absence of schema can be a pitfall for those who don’t know what to do. All of this, plus data mismatches, dead data and empty fields that shouldn’t be empty, is possible. MongoDB is basically a “dumb data store”, and the application code must take a lot responsibility for data integrity.
This is for developers.
RethinkDB
RethinkDB is a new database that enables real-time applications.
The application can’t know if a database is updated. Apps should fire off a notification when a database is updated. This is done through a complicated bridge. (PHP-> Redis-> Node-> Socket.io is an example).
What if updates could be sent directly from the database to your front-end? !
RethinkDB promises that. If you are serious about creating a real-time app (game, market, analytics, etc.), then RethinkDB is the right tool for you. Rethink DB is well worth a look.
Redis
It’s easy to forget Redis when it comes to databases. Redis, an in-memory databank, is mainly used for support functions such as caching.
It takes ten minutes to learn this database. It’s a key-value store that stores strings and has an expiry time. This expiry can be set to infinite, of course. Redis makes up for what it lacks in features with utility and speed. It runs entirely on RAM so reads and writes are extremely fast (a few hundred thousands operations per second are not uncommon).
Redis also offers a sophisticated Pub-Sub system which makes this “database” twice as appealing.
Redis is the best choice if your project has distributed components or could benefit from caching.
SQLite
Yes, I did promise that we would be done with relational database, but SQLite was too adorable to ignore.
SQLite is a lightweight C-library that provides a relational database storage platform. This database stores all of its data in one file with a.sqlite extension. You can place it anywhere in your filesystem. It’s that simple! There is no need to install any “server” software or connect to any service.
Useful Features
SQLite, although a lightweight database alternative to MySQL, packs a powerful punch. These are some of the most remarkable features:
- Transaction support, including COMMIT, ROLLBACK and BEGIN.
- Support for 32,000 columns per tableau
- Support for JSON
- 64-way JOIN support
- Subqueries, full-text search, etc.
- Maximum database size: 140 Terabytes
- Maximum row size is 1 gigabyte
- 35% faster than file II/O
How to use SQLite
SQLite, a highly specialized database, is focused on a straightforward, get-shit done approach. SQLite is an excellent choice if your app is simple and doesn’t require a complex database. This is especially useful for demo apps and small-to-medium-sized CMSs.
If you don’t use SQLite
Although it is impressive, SQLite does not cover all features of standard SQL and your favorite database engine. SQLite does not support clustering, stored procedures and scripting extensions. There is no client that can connect to the database, query it, or explore it. Performance will decrease as the application grows in size.
Cassandra
Although many Java developers believe Java is at its end, there are times when the community surprises the public and silences those who disagree. Cassandra, is an example.
Cassandra is part of what’s called the “columnar family” of databases. Cassandra’s storage abstraction is a column, not a row. This is where the idea is to keep all data together in a column on the disk. It minimizes seek time.
Unique features
Cassandra was created for a particular use case: handling write-heavy loads with zero tolerance for downtime. These are Cassandra’s unique selling points.
- Very fast write performance. Cassandra has the best performance when it comes handling large write loads.
- Linear scalability. This means that you can add as many nodes as you like to a cluster, but there will be no increase in the complexity or brittleness.
- Tolerance for unmatched partitions. This means that even if multiple Cassandra cluster nodes go down, the database will continue to function without losing its integrity.
- Static typing
Cassandra
Cassandra’s best uses are logging and analytics. However, that’s not all. The sweet spot is when you have to deal with large amounts of data. Apple has a Cassandra installation that handles 400+ petabytes of data and Netflix handles 1 trillion requests per day. There is virtually no downtime. Cassandra’s hallmark is high availability.
Why not use Cassandra
Cassandra’s column storage scheme also has its drawbacks. Cassandra’s data model is quite flat. If you need to aggregate, Cassandra will not work. It achieves high availability without sacrificing consistency (remember CAP theorem, distributed systems). This makes it less suitable to systems that require high read accuracy.
Timeframe
The Internet of Things (IoT), which is a new phenomenon, requires new databases. Timescale is one of the most popular open-source databases.
A type of “time series” database is the timescale. This database is different than a traditional one in that time is the main axis of concern and analytics and visualizations of large data sets are top priorities. Time series databases rarely experience any changes in their data. An example of this is the temperature readings from a greenhouse sensor. New data is constantly accumulating, which can be useful for reporting and analytics.
Then why not just use a traditional database that has a timestamp field? There are two main reasons why this is so:
- It is not possible to use general-purpose databases with time-based data. A general-purpose database will take longer to process the same amount of data.
- As new data continues to flow in, the database must be able to manage large amounts of data. It cannot also remove data or change schema later.
Unique features
Timescale DB is different from other databases in its category because of some unique features.
- It is built on PostgreSQL which is arguably the most popular open-source relational database. Timescale can be used if your project already runs PostgreSQL.
- SQL syntax is used to query, which reduces the learning curve.
- Extremely fast writing speeds — millions upon millions of inserts per seconds are not uncommon.
- Timescale doesn’t care if there are billions of rows or petabytes worth of data.
- You have complete flexibility when it comes to schema. Choose from schema-based or relational schemas as you need them.
It’s not logical to discuss when Timescale DB should be used. Timescale is worth looking at if IoT is your area or you are after similar database characteristics.
CouchDB
CouchDB , a tiny database solution that is quietly located in a corner with a dedicated following, is CouchDB . It was designed to address the issues of network loss and eventual resolution. This is a very messy problem that many developers would rather not deal with.
A CouchDB cluster can be described as a distributed collection large and small of nodes, some of which will remain offline. Once a node is online, it sends data back, which is carefully and slowly digested until eventually being made available to the entire cluster.
Unique features
CouchDB is a rare breed in the world of databases.
- Offline-first data syncing capabilities
- Specialized versions (PouchDB and CouchDB Lite) for web browsers and mobile devices (e.g.
- Crash-resistant, battle-tested reliability
- Simple clustering with redundant data storage
How to use CouchDB
CouchDB was designed for offline tolerance, and it is still unsurpassed in this respect. One example of a typical use case for CouchDB is mobile apps, where some of your data is stored on a CouchDB instance that the user has on their phone (because that’s where it was created). It is important to remember that the database cannot be relied on to connect to the device at all times. This means that the database must be flexible and ready to handle conflicting updates later. The Couch Replication Protocol is used to achieve this.
Why not use CouchDB
CouchDB is not meant to be used for other purposes. CouchDB uses a lot more storage than any other database, simply because it must maintain redundant copies and conflict resolution results. Because of this, CouchDB’s write speeds can be painfully slow. CouchDB cannot be used as a general-purpose schema engine because it isn’t compatible with schema changes.
Conclusion
Riak was one of the many candidates I had to exclude. This list should be used as a guide, not a directive. This article will not only contain a list of database software recommendations, but will also discuss how to use them (or avoid them).
