The Temple of Django Database Performance

by Andrew Michael Brookins, Angela Stewart (Illustrator), Reese Shari (Illustrator)

Overview

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

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!

Product Details

ISBN-13:	9781734303704
Publisher:	Spellbook Press
Publication date:	11/30/2019
Pages:	164
Product dimensions:	7.50(w) x 9.25(h) x 0.43(d)

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

1. The Sacrificial Cliff of Profiling

Application Performance Monitoring

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

The 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

2. The Labyrinth of Indexing

What is an Index?

Origin of the B-tree

Page Size

Checking for Indexes

Adding an Index

Adding Indexes in Production

Performance with an Index

A Successful Index

In General, Indexes Slow Down Writes

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

Summary

Quiz

3. 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

Nested Related Records

Using prefetch_related

Limiting the Data Returned by Queries

Use only() to Return Only the Columns You Want

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()

F() Expressions

Func() Expressions

Moving Work to the Database

The Models

Anti-Pattern: Counting with Python

Annotations

Aggregations

Bounding Queries with Pagination

Enter, Paginator

Problems With Offset Pagination

Keyset Pagination

Summary

Quiz

A. Quiz Answers

Answers from Chapter 1

Answers from Chapter 2

Answers from Chapter 3

Index