The Abuse of Databases: Reflections from a Developer with 20 Years of Experience
The Beginning: dBase III and Microsoft Access
I have been developing software for over 20 years, and the first database (DB) I recall using was dBase III. That was ugly and complicated to handle. It was basically used as a standalone application and not as a service that a backend would connect to.
At that time, Microsoft Access also started to emerge, which, without a doubt, democratized database systems. It was much more accessible to the average user and facilitated the creation and management of simple databases.
The Era of MySQL and PHP
My experience connecting databases with other services started with MySQL and PHP (I have never seen a pair with more chemistry in my life). I remember the world of possibilities that opened up to me. Its ACID properties made writing PHP applications much simpler, but it also generated vices and abuses of Database Management Systems (DBMS).
Common Abuses Nowadays and Their Consequences
20 years later, we continue to abuse DBs. If we have concurrency problems, we often hand it over to the DB to take care of it. If we have to do calculations, it’s better to pull from SQL to do the math for us. I even remember a project from a major information processing company, where they abused the DB so much that they used it even to perform intermediate calculations. The expense in input/output operations (IOP) was brutal. Thousands of QPS (Queries Per Second) that could be done without any problem in the process without having to access the DB.
The proposed solution was clear: read at the beginning, process the information, and only write at the end. Anyway, I don’t blame them; although in most cases these abuses are justified, the problem is that when a need for persistence or information processing arises, we usually assign it to the DB without blinking an eye or thinking about other possible solutions with their pros and cons.
Common Examples of Abuse and Alternative Solutions
1. Using databases for cache
Cache systems like Redis or Memcached are specifically designed to store data in memory and access it extremely quickly, avoiding the performance hit that always accessing the database implies.
2. Calculations in the database
While it’s possible to perform complex calculations with SQL, it’s often more efficient to carry them out in the backend using programming languages that are more suited to handling complex structures and algorithms.
3. Too many relationships in a single query
Excessive denormalization can lead to very complex queries that are hard to optimize. Instead, consider breaking down queries into several smaller ones and managing relationships in your application’s logic.
4. Event logging in the main database
Using the main database to store logs or event records can make it extremely slow. A solution is to use specialized logging tools like Elasticsearch, which are optimized for fast searches and efficient storage of large volumes of data.
5. Validations and business logic in SQL
Instead of transferring all validations and business logic to SQL queries, it’s preferable to maintain these controls in the application code so that they are more manageable and understandable.
Best Practices and Final Reflection
As Uncle Bob says, sometimes a file is enough to add persistence. From all this abuse, there are clear winners; if not, just ask AWS RDS, where the bill for IOPs often hurts quite a bit due to abusing the DB and making a poor design.
It is crucial that, as developers, we understand the impact of our decisions and explore alternatives before resorting to databases as the solution to all our problems. With proper planning and the use of good practices, we can avoid many common issues and make a more efficient and economical use of our resources.
What techniques do you use to avoid abusing databases in your projects? Share your experiences in the comments.