Django Optimization: from 30s to 142ms

Optimizing a Django Monolith: From 30 Seconds to 142 ms on Reports

In a legacy Django project, branch reports were taking 30 seconds to load due to a full sequential scan on the orders table with 280k records. EXPLAIN ANALYZE revealed:

Seq Scan on orders (cost=0.00..18420.00 rows=2841 width=847)
Filter: (branch_id = 42)
Rows Removed by Filter: 284100
Execution Time: 28340 ms

The ORM was generating 2800+ queries due to N+1 on related objects. Business logic placed in controllers compounded the load.

Original query:

SELECT * FROM orders
JOIN order_items ON orders.id = order_items.order_id
JOIN products ON order_items.product_id = products.id
WHERE orders.branch_id = 42;

Eliminating N+1

N+1 caused exponential load growth. Before:

orders = Order.objects.filter(branch_id=branch_id)
for order in orders:
    items = order.order_items.all()  # N+1
    for item in items:
        product = item.product  # N+1

After with prefetch:

orders = Order.objects.filter(
    branch_id=branch_id
).select_related('customer').prefetch_related('order_items__product')

Query count dropped to 3. select_related uses JOIN for ForeignKey, prefetch_related uses IN queries for ManyToMany and reverse relations.

Indexing Tables

After N+1, the sequential scan remained. Indexes were added without downtime:

CREATE INDEX CONCURRENTLY idx_orders_branch_created ON orders(branch_id, created_at DESC);
CREATE INDEX CONCURRENTLY idx_products_search ON products USING GIN(to_tsvector('english', name));

New plan:

Index Scan using idx_orders_branch_created on orders
Index Cond: (branch_id = 42)
Execution Time: 142 ms

Execution time dropped from 28 seconds to 142 ms. CONCURRENTLY prevents table locks in production.

Key indexing recommendations:

• Composite on (filter + sort)
• GIN for full-text search
• Always check EXPLAIN ANALYZE before adding

Refactoring to DDD

Business logic in controllers created technical debt. Domain and Use Cases were extracted independently of the framework.

Example Use Case:

class GetBranchReportUseCase:
    def __init__(self, repo: OrderRepository):
        self._repo = repo

    def execute(self, branch_id, period) -> BranchReport:
        orders = self._repo.get_by_branch_and_period(branch_id, period)
        return BranchReport.from_orders(orders)

View became thin:

class BranchReportView(APIView):
    def get(self, request, branch_id):
        use_case = GetBranchReportUseCase(DjangoOrderRepository())
        report = use_case.execute(branch_id, DateRange.from_request(request))
        return Response(BranchReportSerializer(report).data)

Use Cases are tested with mock repositories without Django and the database. Time-to-market for new features was cut in half.

Code Quality Control

Added:

• Mypy strict mode:

[mypy]
strict = true
disallow_untyped_defs = true
warn_return_any = true

• Pytest with coverage ≥87%, blocking quality gate in GitLab CI.

• MTTD decreased by 40% due to early error detection.

Improvement Metrics

| Metric | Before | After |

|--------|--------|-------|

| Report Time | 30 s | 1.5 s |

| DB CPU | 80% | 32% |

| Queries/Page | 2800+ | 3 |

| Feature TTM | X | X/2 |

| MTTD | - | -40% |

Key Takeaways

• EXPLAIN ANALYZE is the first step for any performance degradation
• N+1 kills under load, prefetch_related/select_related are mandatory
• Composite indexes on (branch_id, created_at) give 200x speedup
• DDD isolates business logic, accelerating development and testing
• Mypy + pytest + CI gate reduce MTTD without overhead

— Editorial Team