Pomalá administrácia e-shopu: N+1 dopyty a chýbajúci index

Sklad pracuje v administrácii so zoznamom objednávok: 50 na stránku, filter podľa stavu, najnovšie hore. Pri každej objednávke je meno zákazníka, spôsob dopravy a počet položiek. Tabuľka objednávok má po niekoľkých rokoch stovky tisíc riadkov.

Šablóna prechádza objednávky v cykle a pri každej číta order.customer.name, order.shipping_method.name a order.items.count.

Zoznam sa spomaľoval postupne, takže si to dlho nikto nevšímal. V predvianočnej sezóne sa stránka začala končiť chybou 502: Gunicorn po predvolených 30 sekundách ukončí pracovný proces, ktorý neodpovedá, a Nginx potom vráti 502. Sklad nemohol vybavovať objednávky inak než cez export.

Zároveň rástla záťaž databázy a spomaľoval sa aj samotný obchod, lebo zákazníci a administrácia zdieľajú tú istú databázu.

Prvá príčina je vzor N+1. Dopyt na objednávky načíta iba tabuľku objednávok. Každé order.customer a order.shipping_method v šablóne potom pošle do databázy vlastný dopyt a order.items.count ďalší. Pri 50 objednávkach je to 1 + 50 × 3 = 151 dopytov na jedno načítanie stránky. Každý je rýchly, ale 151 ciest do databázy a späť sa sčíta.

Druhá príčina je chýbajúci index. Filter podľa stavu a zoradenie podľa dátumu vytvorenia nemali vhodný index, takže PostgreSQL pri každom zobrazení prešiel celú tabuľku a výsledok zoradil, aby z neho vrátil prvých 50 riadkov.

Na vývojárskom počítači s pár desiatkami testovacích objednávok nebolo vidieť ani jedno.

Lokálne s kópiou produkčných dát, v ktorej boli osobné údaje nahradené vymyslenými: django-debug-toolbar ukázal 151 dopytov, z toho väčšinu opakujúcich sa s iným ID.

V produkcii rozšírenie pg_stat_statements, ktoré zbiera štatistiky o dopytoch. Treba ho mať v shared_preload_libraries, čo vyžaduje reštart PostgreSQL, preto sa zapínalo v noci. Dopyt zoznamu objednávok bol na prvom mieste podľa celkového času.

EXPLAIN (ANALYZE, BUFFERS) nad týmto dopytom ukázal sekvenčné prechádzanie celej tabuľky (Seq Scan) a triedenie všetkých riadkov so zvoleným stavom.

Súvisiace dáta sa načítajú naraz. select_related pripojí zákazníka a spôsob dopravy do toho istého dopytu cez JOIN, prefetch_related načíta položky pre všetkých 50 objednávok jedným ďalším dopytom.

orders = (
    Order.objects
    .filter(status=status)
    .select_related("customer", "shipping_method")  # joined into the same query
    .prefetch_related("items")                       # one extra query for the whole page
    .order_by("-created_at")
)
page = Paginator(orders, 50).get_page(request.GET.get("page"))

# template: {{ order.customer.name }}, {{ order.items.all|length }}
# both now read what is already loaded instead of asking the database again

Index na stav a dátum vytvorenia v zostupnom poradí. Tabuľka je veľká a obchod beží, preto sa index vytvára s CONCURRENTLY, ktorý neblokuje zápisy. Nesmie však bežať v transakcii, preto má migrácia atomic = False.

from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models


class Migration(migrations.Migration):
    # CREATE INDEX CONCURRENTLY cannot run inside a transaction
    atomic = False

    dependencies = [("shop", "0042_order_note")]

    operations = [
        AddIndexConcurrently(
            "order",
            models.Index(fields=["status", "-created_at"], name="order_status_created_idx"),
        ),
    ]

Test, ktorý ustráži, že počet dopytov nerastie s počtom objednávok. Nekontroluje presné číslo, ktoré sa môže legitímne zmeniť, ale to, či 5 a 50 objednávok stojí rovnako.

from django.db import connection
from django.test import TestCase
from django.test.utils import CaptureQueriesContext
from django.urls import reverse


def count_queries(client, url):
    with CaptureQueriesContext(connection) as ctx:
        client.get(url)
    return len(ctx.captured_queries)


class OrderListTests(TestCase):
    def test_query_count_does_not_grow_with_orders(self):
        self.client.force_login(make_staff())
        url = reverse("admin_orders")

        make_orders(5)
        few = count_queries(self.client, url)
        make_orders(45)
        many = count_queries(self.client, url)

        self.assertEqual(few, many)

Počet dopytov je teraz konštantný bez ohľadu na počet objednávok na stránke. Údaje, ktoré šablóna číta, sú už v pamäti, takže sa nepýta znova.

Index je zoradený presne tak, ako sa pýtame: najprv stav, potom dátum od najnovšieho. PostgreSQL nájde prvých 50 riadkov daného stavu priamo v indexe a ďalej nečíta. Nemusí prechádzať celú tabuľku ani nič triediť.

Test zlyhá pri prvom novom poli v šablóne, ktoré by vzor N+1 vrátilo, teda skôr, než to zistí sklad.

Čo to nerieši: stránkovanie potrebuje celkový počet objednávok v danom stave a ten COUNT(*) stále rastie s ich počtom, hoci s indexom je lacnejší. Ostatné stránky administrácie neboli preverené.