The problem

As my POS system grew, bill generation got slow and checkout dragged. Customers complained. When I profiled the Django APIs, most of the time was going to inefficient ORM queries: related objects loaded one at a time, writes issued in loops and more database round trips than the work needed.

This post covers the fixes I made to the refund API and the bill generation path: select_related, prefetch_related, bulk operations and the profiling tools that showed me where to look.

The N+1 query problem

The biggest issue was the N+1 pattern. You query a list, then touch a related object inside the loop, and Django issues a new query for every row.

The refund API did exactly this:

for item_data in refund_items_data:
    bill_item = BillItem.objects.get(id=item_data["bill_item_id"])
    product = bill_item.product  # separate query on every iteration

Each iteration ran one query for the BillItem and another for its product. On a large refund that adds up fast.

Fix: select_related for foreign keys

select_related follows a foreign key with a SQL join, so the product comes back in the same query as the bill item:

for item_data in refund_items_data:
    bill_item = BillItem.objects.select_related("product").get(id=item_data["bill_item_id"])
    product = bill_item.product  # already loaded by the join

That halves the queries, but it still runs one query per item. The cleaner version loads every bill item up front in a single query and looks them up in memory:

ids = [item_data["bill_item_id"] for item_data in refund_items_data]
bill_items = BillItem.objects.select_related("product").in_bulk(ids)

for item_data in refund_items_data:
    bill_item = bill_items[item_data["bill_item_id"]]
    product = bill_item.product

prefetch_related for many-to-many and reverse relations

select_related only works across foreign keys and one-to-one fields, because it relies on a join. For many-to-many and reverse relations, prefetch_related is the right tool. It runs one extra query per relation and stitches the results together in Python.

When I fetched refund items along with their bill items and products, I used:

refund_items = RefundItem.objects.prefetch_related("bill_item__product").filter(refund=refund)

Every related object is loaded before the loop starts, so iterating over refund_items no longer hits the database per row.

When to use raw SQL

The ORM covers most cases. For some aggregations, a direct SQL query is simpler and faster than several ORM calls. Summing refunded amounts was one example:

from django.db import connection


def get_total_refunded_amount(refund_id):
    with connection.cursor() as cursor:
        cursor.execute(
            "SELECT SUM(refunded_amount) FROM refund_item WHERE refund_id = %s",
            [refund_id],
        )
        return cursor.fetchone()[0]

In certain cases this beat filtering and aggregating through the ORM. A simple sum like this one can also be written as RefundItem.objects.filter(refund_id=refund_id).aggregate(Sum("refunded_amount")), which is also a single query, so check what SQL the ORM actually generates before dropping down to raw SQL. Always pass parameters as a list, as above, and never format them into the string.

Bulk inserts and updates

Processing a refund updates several products at once. My first version saved each product inside a loop, one write per product:

for product in products_to_update:
    product.save()

bulk_update sends them as a batch:

Product.objects.bulk_update(products_to_update, ["quantity_units", "quantity_subunits"])

bulk_create did the same for inserting refund items:

RefundItem.objects.bulk_create(refund_items)

One caveat: bulk operations skip the model's save() method and the pre_save and post_save signals. If you rely on either, move that logic somewhere the bulk path also runs.

Profiling with Django Debug Toolbar and Silk

I found these problems with two tools.

Django Debug Toolbar shows the query count and the time of each query for a request:

pip install django-debug-toolbar

Add it to INSTALLED_APPS and the middleware, and each page shows its queries in a side panel. Duplicate queries stand out immediately.

Silk logs requests and their queries, which helps for API endpoints that do not render a page:

pip install django-silk

With query logging on, I could see which operations were slow and which were repeated.

Results

After these changes, query execution time in the refund API dropped by 70%, and bill generation got faster with it. Customers noticed the smoother checkout, and the complaints about slow processing stopped.

What I took from it

Most of the slowdown came from a few patterns: related objects loaded inside loops, writes issued one row at a time and queries nobody had measured. select_related, prefetch_related and the bulk methods fixed the first two. The profilers fixed the third. Some of my customers were genuinely annoyed before this, so the fix mattered to the business as much as to the code.