General-Practice QA… and Why It Leaves the Database Behind
For most tiers of a system, QA is well understood and practiced almost religiously. It's a cornerstone of a product's delivery process, and all responsible teams automatically include it in their workflows. There are sets of steps to follow for good application development QA, and there's a wide variety of tools and processes that support those steps:- Unit testing/TDD and continuous integration.
- Benchmarking and application profiling.
- End-to-end and system tests, and pre-production environments.
- Production monitoring and automated health checks.
- How changes affect existing loads.
- Fluctuations in concurrency and contention.
- Real data sets.
Methods for QA Testing a Database
What are the actual methods for doing QA on a database? The simple, tl;dr answer is that queries are the solution to everything. With insightful and detailed query analysis you can find out what's new in your database, measure change, and spot and fix problems early. Similarly, if you can understand how your queries are behaving and compare that behavior to how they should be behaving, you'll have a basis of quality to run QA against. But let's dig deeper. To do the most thorough QA testing of your database, you'll want to perform exercises like running against a test-dataset in a container for unit-testing/CI. This doesn't need to be excessively complicated — a small dataset that you can leverage to exercise new code paths can help provide initial validation that your databases are responding as expected. The simplest thing to look for here is desired output. You can also be more verbose and check query behavior, like running SHOW WARNINGS for MySQL, to ensure there's no unexpected query behavior — the query might return successfully, but a warning might indicate that it'll fail in a subtle way in production. These tests can also validate that the data contained in your database is what you expect it to be.The smaller your dataset, the stricter you can make your tests, but, as a trade off, the tests become more brittle and more likely to fail. In unit testing, developers strive for their testing to strike a balance between flexibility and strong data validation. Additionally, end-to-end testing and pre-production/production monitoring will give you a full picture of how the database behaves and changes in varying contexts. End-to-end testing in particular can confirm that your application and data tier work together as expected. And comparing the results from pre-production/production monitoring lets you identify new queries, detect any regressions in query performance, and quickly identify any errors that you missed in your original end-to-end testing. Finally, be sure to also incorporate regression testing, so you can track how modifications to the database have affected its behavior. Some potential regressions include:- An increase in query latency or lock contention.
- An increase in the number of executions of a query — it's possible to accidentally put a query in a loop and not realize it until you see the query executing more frequently than expected.
- A change in the execution pattern of a query, which has, for example, caused more clients to issue the same query simultaneously, rather than spreading the query out over time. This might cause a change from a "smooth" execution to a pattern that's "spikier."
A Few More Things to Keep in Mind
Databases change all the time. In some ways, each execution of a query in production represents a unique test of the database's behavior. From that point of view, database QA has to be a continual live production process. System changes and database versions impact how you expect your database to react in ways. Data sets can grow or shrink; read / write ratios change over time; different bottlenecks may trigger when your system hits various milestones in performance; and, ultimately, your database may look and behave very differently from one month to the next. Additional change comes in many forms. These are a few examples:- Code commits can change SQL, though such changes can also come indirectly from ORMs.
- UI changes can trickle down to database utilization pattern changes.
- An increase or decrease in the demand for work placed on the database, as related to site/application traffic.
- Data no longer fitting into available memory.
