What are the most common performance bugs developers encounter when using databases? I like this paper because it carefully studies what sorts of database performance problems real developers encounter in the real world. The authors analyze several popular open-source web applications (including OpenStreetMap and Gitlab) to see where database performance falters and how to fix it. Here’s what they found: - ORM-related inefficiencies are everywhere. This won’t be surprising to most experienced developers, but by hiding the underlying SQL, ORMs make it easy to write very slow code. Frequently, ORM-generated code performs unnecessary sorts or even full-table scans, or takes multiple queries to do the job of one. Lesson: Don’t blindly trust your ORM–for important queries, check if the SQL it generates makes sense. - Many queries are completely unnecessary. For example, many programs run the exact same database query in every iteration of a loop. Other programs load far too much data that they don’t need. These issues are exacerbated by ORMs, which don’t make it obvious that your code contains expensive database queries. Lesson: Look at where your queries are coming from, and see if everything they’re doing is necessary. - Figuring out whether data should be eagerly or lazily loaded is tricky. One common problem is loading data too lazily–loading 50 rows from A then for each loading 1 row from B (51 queries total) instead of loading 50 rows from A join B (one query total). But an equally common problem is loading data too eagerly–loading all of A, and also everything you can join A with, when in reality all the user wanted was the first 50 rows of A. Lesson: When designing a feature that retrieves a lot of data, retrieve critical data as efficiently as possible, but defer retrieving other data until needed. - Database schema design is critical for performance. The single most common and impactful performance problem identified is missing database indexes. Without an index, queries often have to do full table scans, which are ruinously slow. Another common problem is missing fields, where an application expensively recomputes a dependent value that could have just been stored as a database column. Lesson: Check that you have the right indexes. Then double-check. Interestingly, although these issues could cause massive performance degradation, they’re not too hard to fix–many can be fixed in just 1-5 lines of code, and few require rewriting more than a single function. The hard part is understanding what problems you have in the first place. If you know what your database is really doing, you can make it fast!