Choosing the right database for IT projects is no easy task — it depends on business requirements, but also on limitations introduced by costs, legal issues, and even the ability to find the right technical support. In this blog post, based on our experiences at Ingenious.BUILD, I’d like to discuss the challenges of using an unconventional database like CockroachDB in a project based on Laravel, among other platforms. These challenges can also arise when our API uses completely different solutions.
Table of Contents
Complex Database System
Let’s start from the beginning: a few years ago, we used MySQL/MariaDB in a multi-tenant system where each tenant had its own separate database. However, we needed the ability to distribute data to different regions of the world, while still maintaining a single logical database on the application side. Sounds unusual? Perhaps, but such distributed database solutions do exist, and one of them is CockroachDB — a database based on PostgreSQL and partially compatible with it. For example, suppose certain data, due to GDPR regulations, must reside only on servers in Europe — this extensive issue can be resolved quite swiftly with such a database.
After exploring all aspects and extensive testing, we decided to adopt it. It wasn’t easy; both the application code and existing migrations had to be adjusted. We had to create our own database and data migrators to comply with the new types. MySQL allowed for a lot, but here everything was much more restrictive, which we considered an advantage that would allow us better control over the database schema in the future. During testing, it also turned out that the database engine completely failed to handle a large number of databases and tables: the total number of objects should be much lower than what we operated with. This necessitated further changes, numerous migrations, and dedicating time to non-functional work. Fortunately, thanks to incredible people and their commitment, we successfully migrated all our environments to CockroachDB by the end of last year.
Change of Plans and Problems
As life often has it, plans can change — shortly after the big migration, we realized we needed a completely new region not supported by Cockroach Labs. Here, I can confidently list the reasons why using unconventional solutions can generate many problems:
- Lack of supported regions: DBaaS is a convenient option, but here it proved to be a huge limitation, and we could not let our clients down, so we considered a self-hosted option.
- Limited performance: Due to its distributed nature, CockroachDB turned out to be significantly slower than MySQL or PostgreSQL, which we currently use (more on this later). Stronger machines were necessary.
- High costs: Due to the above, we had to pay much more for infrastructure, which limited us. A self-hosted option turned out to be impossible due to very high licensing costs (per vCPU).
- Limited support for Laravel: Although there is a CockroachDB driver, it is not an official project and has limitations that become apparent in a large project like ours. Databases like MySQL, PostgreSQL, SQLite, and even MongoDB are much better supported.
- Log management limitations: While Cockroach Labs provides plenty of logs, it does not allow choosing where they are stored. In an application like ours, where client data may be in many regions and legal regulations are important, this is a significant limitation.
- Functional limitations: The database is theoretically compatible with PostgreSQL, but in practice, this is increasingly not the case. With further updates, support for features like pg_dump was removed, and connecting with external services like ETL proved very problematic.
Solution: Leave the Problematic Database
Faced with this situation, we had no choice — we decided to change our approach to databases and undertook another major migration, this time to PostgreSQL. Now we can use another DBaaS with much greater capabilities, the costs are multiple times lower, and support on the Laravel side is much better. Of course, this means additional changes on the application side, but the entire system associated with databases is much simpler to maintain, monitor, and even scale. This journey was at times very difficult and bumpy, but it allowed us to learn a lot and gain tremendous experience.
In conclusion, I want to emphasize that I do not consider CockroachDB a bad solution — it can be an excellent choice in many scenarios. However, it is essential to thoroughly track requirements and ensure that they will not change. If you want to implement this type of distributed database in a system that is already somewhat established and stable, which does not need too many external integrations, then it may prove to be a really interesting solution.