The Temple of Django Database Performance

Description

Most performance problems in web applications come down to one thing: the database. With Django, the situation is no different -- in fact, in some ways it's even worse than that of our ORM-hating colleagues. Database performance tuning in Django involves a dizzying number of ORM API methods (defer, prefetch_related, iterator, and many more), mysterious alphabet letters (Q, F?!), and all of this rests on top of the already complex machinery of SQL queries and indexes. I'm going to make a bold claim here: The only way to master Django is to master database performance. The difference between junior, mid-level, and senior Django engineers is usually a function of this mastery. And to fully master database performance with Django, reading through API documentation is not enough. You need to become an expert. Here are some examples of what an expert knows that this book will teach you: How to use profiling and measurement tools like New Relic, Django Debug Toolbar, and database query plans to see exactly how badly a query performs in production How to eyeball a query and know exactly the right index to speed it up (a covering index with INCLUDE? a partial index? a GIN index? You're going to tell me by the end of the book because YOU will be the expert) How to use advanced techniques like server-side cursors with iterator(), custom Func() expressions, and keyset pagination with row comparisons to churn through millions of rows of data with ease How to move slow queries into Materialized Views and query them as Django models You will learn all of this and more through over 160 pages, 60 examples, and 30 quiz questions. By the end, you won't ever think of a Django ORM query the same again: it will exist simultaneously in your mind as an operational metric, ORM syntax, generated SQL, and a database execution plan. Because you will be an expert. I can teach this because I have over ten years of experience working with Django and relational databases, both as an application developer and a platform engineer building databases as a service for Compose.com and IBM Cloud. And did I mention that you're going to have fun? Yes, you read that right. This book is chock full of awesome fantasy art including a map of each chapter's core concepts. Why fantasy art? Because I play tabletop role-playing games and I require books to be fun. So gaze upon the Sacrificial Cliff of Profiling. Get lost in the Labyrinth of Indexing. Ransack the Crypt of Querying. Then get back to saving the world from unbounded queries! The Temple of Django Database Performance Table of Contents Preface Audience Django and Python Experience Relational Databases and SQL Structure of the Book Learning Tools The Fantastic The Example Application and Database Terminology Database vs. Database Management System vs. Relational Database Management System Code Formatting The Sacrificial Cliff of Profiling Application Performance Monitoring Software Setting Up New Relic Apdex Key Transactions Alerts Transaction Traces Finding the ORM Code APM Alternative: the Slow Query Log PostgreSQL MySQL Prefer APM Services Reproducing the Problem Locally Django Debug Toolbar Problem: What About API Views? Moving Forward The Query Plan What is a Query Plan? Studying An Example Query Plan Summary Quiz The Labyrinth of Indexing What is an Index? A Database Index is Like a Book Index But a Database Index is Not Like a Book Index …​ Arriving at the Point The Ancient B-tree Index Page Size The Many Flavors of Indexing Indexes and Django Checking for Indexes Adding an Index Adding Indexes in Production DDL vs. DML Operations Performance with an Index A Successful Index In General, Indexes Slow Down Writes LSM Trees and SSTables Other Index Types Indexing JSON Data JSON Data Example #1: Account Data JSON Example #2: Denormalization A Closer Look at the GIN Index When the Database Ignores an Index Cost Prediction Too Many Results Query Doesn’t Match the Index It Still Doesn’t Work Covering Indexes Partial Indexes Clustering Clustered indexes in MySQL WITHOUT ROWID in SQLite Summary Quiz The Crypt of Querying The N+1 Problem and select_related Identifying an N+1 Problem Why the N+1 Problem Happens Using select_related Adding select_related to a Query What SQL Does select_related Generate? Nested Related Records Profiling without select_related Profiling with select_related The SQL Behind select_related with Nested Related Records Outer Join vs. Inner Join Using prefetch_related Limiting the Data Returned by Queries Use only() to Return Only the Columns You Want Using only() with Relations Use defer() to Specify the Columns You Do Not Want Reducing ORM Memory Overhead Avoiding Model Instantiation with values() Iterating Over Large Querysets with iterator() Faster Updates The Basics: update() and bulk_update() update() bulk_update() F() Expressions Func() Expressions Moving Work to the Database The Models Anti-Pattern: Counting with Python When You Really Do Need to Calculate in Python Annotations Aggregations Materialized Views What is a Materialized View? Materialized Views and Django: The Model Materialized Views and Django: The Migration Materialized Views and Django: The Management Command Materialized Views and Django: The View Materialized Views Summary Bounding Queries with Pagination Enter, Paginator Problems With Offset Pagination Keyset Pagination Choosing the Keyset Keyset Pagination with Row Comparison Keyset Pagination with Boolean Logic Downsides of Keyset Pagination Keyset Pagination Implementations for Django Summary Quiz Appendix A. Quiz Answers Answers from Chapter 1 Answers from Chapter 2 Answers from Chapter 3 Index