Choosing the right database service often depends on what you need it to do. There are many free and open source database solutions that you can use, but a few have risen to the top in adoption and performance.
For free, general-purpose relational databases, you are likely considering MySQL and PostgreSQL. As their names clearly state, both systems support the Structured Query Language (SQL). Although the details of each are a little different, the popularity of SQL as a standard query language means you’ll find plenty of resources, documentation, related tooling, and even other developers if needed to support your app.
Relational database systems like MySQL and PostgreSQL are heavily structured, meaning the data is stored in a highly-organized form, making it easy to query later.
MySQL is a foundational component of the LAMP and LEMP stacks, used in content management systems and other applications. MySQL is optimized for database reads, meaning it works best when data gets queried frequently and new data gets added less often. This is common behavior for databases supporting general-purpose websites, ecommerce sites, and directory applications.
PostgreSQL is considered more versatile and sophisticated than MySQL, with a wider range of data types and more advanced queries. This can also make PostgreSQL more challenging to implement, depending on your application. Regardless, if your application is write-heavy, meaning it’s highly scalable, is high-volume, or is an “enterprise” application, PostgreSQL is a good choice.
There are also NoSQL systems that store data in a largely unstructured format. Although it takes a little longer to get a query response, a less-structured format allows for much faster write processes to a NoSQL database. NoSQL databases are ideal for situations where you want to add or move data to the database quickly and aren’t concerned about a standardized query language to retrieve your data in the future.
MongoDB is the most popular NoSQL database and features graph search, geoSearch, map-reduce queries, and text search. MongoDB is ideal for gaming, IoT applications, personalization engines, and other applications where the data doesn’t easily translate to a highly structured or schema-based model.
Two other database options, Redis and Cassandra, target more specialized workloads or applications.
Redis is an in-memory database with a key-value structure. Because data gets stored in memory, it is accessible for high-speed searches and behaves more like a cache than a conventional database. Redis is often used with high-demand inventory systems and other applications that require a lightning-fast response time. Although data is accessed from memory, Redis invisibly keeps a record of the data on disk for restarts and to prevent data loss with system failure.
Cassandra is a NoSQL database system that uses its own query language, the Cassandra Query Language (CQL), which is similar to SQL. Cassandra is designed for distributed environments and is highly resilient to outages and data loss. In terms of scalability and performance, Cassandra has been tested on clusters of up to 1,000 nodes, and comes with other tools to support larger installations, including auditing, replication, and support for elastic clouds.
Whether you are setting up a simple website, building a distributed data store, or creating a custom business application, we can help with documentation to support open source database options to deploy today, or managed solutions to help make the process a little easier.