A developer’s life is anything but a sitcom. But if you’re building database functionality into an application or an operating environment, you eventually may run into a host of challenges with single-node open source databases that, if you weren’t tasked with fixing them, might seem comical.
In general, database performance problems relate to data ingestion, scaling, speed and not being able to easily store all the different kinds of data you want. In this blog you’ll get a personal view into what those problems look like, as developers share their experiences with the three signs of outgrowing popular open-source databases like MySQL, PostgreSQL and MariaDB. We’ve also included some tips on what to look for in a new database solution to ease the pain.
Sign 1: Application Performance Hits a Wall
Jack Ellis is co-founder of Fathom, Inc., a SaaS firm that believes website analytics should be simple, fast and privacy focused. Fathom delivers a simple, lightweight, privacy-first alternative to Google Analytics. Jack describes how his application’s performance suffered because he had maxed out MySQL:
"Despite keeping summary tables only (data rolled up by the hour), our [MySQL] database struggled to perform SUM and GROUP BY. And it was even worse with high cardinality data. One example was a customer who had 11,000,000 unique pages viewed on a single day. MySQL would take maybe 7 minutes to process a SUM/GROUP query for them, and our dashboard requests would just time-out. To work around this limitation, I had to build a dedicated cron job that pre-computed their dashboard data."
Josh Blackburn is co-founder and head of technology at IEX Cloud, a data infrastructure and delivery platform for financial and alternative data sets that connects developers and financial data creators. Josh’s team builds high-performance APIs and real time streaming data services used by hundreds of thousands of applications and developers. He had hit a similar wall with MySQL running in Google Cloud:
"We average about 500,000 to 800,000 data ops per second, typically during market hours. These could be really tiny requests, but you can see our ingress and egress rates; we’re consuming a lot of data from multiple resources, but we’re also passing a lot of that out the door… In our case, we’ve got to keep up not just with the stock market, with real-time prices, but also with everyone coming in and needing all that data in real time."
Josh summed up his data ingestion challenge, “We were in a tight spot to find something that would scale and had better performance, especially on the ETL side, because we’re loading hundreds of gigs of data every day.”
Sign 2: An Open-Source Database Doesn’t Support Your Business Needs
Gerry Morgan is lead developer at dailyVest, a fintech company using 401(k) participant data and analytics to improve the health and performance of retirement plans. Each month, over 7 million investors and plan participants can access digestible insights delivered via visual dashboards.
Data volumes are growing at 36% a year, fueled by billions of transactions, and Gerry found that dailyVest’s Azure SQL database couldn’t support business growth. He said:
"[We were] not just increasing resource requirements in our cloud environment, but also increasing costs [of Azure Cloud resources]… We were also seeing some performance degradation in Azure SQL. Not so much that our customers would have noticed, but we noticed there was some drop off in speed in our ingestion of data. We wanted to improve our ETL operation, but at the same time improve the customer experience — all customers will be happy if you make things faster, even if they haven’t noticed if things were particularly slow."
Mohammed Radwan is head of engineering at Foodics, a restaurant management software company serving more than 22,000 establishments in 35 markets. The company processes more than 5 billion orders per year, offering dashboard analytics for business owners and managers. At first, Foodics used a combination of CitusDB for and MySQL to power the business, later swapping out MySQL for a commercial version of PostgreSQL.
Foodics ran into reliability problems with CitusDB, experiencing outages that lasted three hours at a time up to four times per month. Only 200 users could concurrently use the existing system. Foodics had 5,000 customers, but downtime and a lack of fast data were accelerating churn. Although the company had just received $20 million in Series B funding in 2021, the unreliable system limited growth and put future funding at risk. Mohammed said:
"Like many tech companies, we started with MySQL. It was compatible with what we had and was easy to use. It fulfilled its purpose for a while, but when we needed to grow and expand, MySQL couldn’t enable that."
When experiencing scaling issues with MySQL or other open source databases, developers often turn to sharding middleware or NoSQL. These approaches, however, can compromise the performance of ACID-compliant transactions and complex joins, particularly in high-volume production environments.
Sign 3: You’re dealing with database sprawl
Here, the writing on the wall is clear: if you need to incorporate multiple data types into your application or environment – such as time series, JSON, documents and other specialty data types – you are going to need to spin up specialty databases to contain them. These separate databases will need to be connected, maintained and upgraded, creating database sprawl — and exponential complexity.
If your single-node database supports only standard SQL numeric data, you will likely experience significant growing pains if you try to augment it to support multiple data types.
What should you look for in a replacement database?
Most single-node open source growing pains can be solved by a database that offers:
Your list may be much more granular. Jack at Fathom had a lengthy list of non-negotiables for any database he might consider to replace MySQL:
For Mohammed, delivering 24/7 resource availability was paramount. He said:
"We can’t take time off or delay reports. The most important thing for us is concurrency. As we grow, we need to ensure that our customer base grows with us. We needed a database that allows for seamless reporting without worrying about how many customers are using it all at once."
After all the challenges Foodics had weathered, Mohammed needed a database that would offer:
Developers Choose MySQL Wire-Compatible SingleStoreDB
Fathom, IEX Cloud, dailyVest and Foodics all chose SingleStoreDB, a real-time, distributed SQL database, to replace open source database technology. Mohammed’s reasons why are a common theme:
"We are a small team, so we did not want to spend time tuning a database. We wanted something that just worked out of the box. For this reason, we went with SingleStoreDB Cloud running on AWS. With SingleStore, we can just plug and play and do everything we need to empower our customers. It allows us to focus on what we are really here to do: serve our customers."
SingleStoreDB is MySQL wire-compatible, making it incredibly easy to migrate from any flavor of MySQL (including AWS RDS, Google Cloud SQL, Azure MySQL or others). It supports familiar SQL syntax, so developers don’t need to learn a completely new technology to get started.
Most developers can quickly complete their migration and get started with SingleStore in hours or a few days. To learn about migration, check out these resources:
After Migration, a Bigger, Better House
All of the developers experienced major improvements in speed, performance, scalability and flexibility after they migrated to SingleStoreDB. Here’s how Jack tells Fathom’s “after” story:
Josh from IEX Cloud summed up, “SingleStore enables us to do monitoring and analysis in the same system that houses our historical data, and this creates enormous efficiencies for us. We’ve been able to consolidate multiple databases, run our platform faster, and speed the onboarding processes for new data sets.”
If you’ve outgrown your single-node open source database and are ready to move into a bigger, better house, try SingleStore for free today.