Background

Building a POS system that handles inventory, sales, and real-time reporting presented multiple performance bottlenecks. With a PostgreSQL database and a Django backend, early performance issues arose due to inefficient query patterns, redundant updates, and excessive database hits.

Initial Problems

1. Slow Bulk Updates

Updating multiple records individually in Django was inefficient. Consider this naïve approach:

for product in products:
    product.stock -= 1
    product.save()

This triggered a separate UPDATE query for each product, significantly slowing down batch operations.

2. Signal Overhead

Django signals were initially used to track stock changes, but they fired on every save, leading to unnecessary computations.

@receiver(post_save, sender=Sale)
def update_inventory(sender, instance, **kwargs):
    product = instance.product
    product.stock -= instance.quantity
    product.save()

With high transaction volumes, this became a major bottleneck.

3. Redundant Queries

Certain parts of the system, such as product conversion rates (e.g., unit-based conversions), recalculated values every time instead of caching them.

def get_conversion_rate(product):
    return ConversionRate.objects.get(product=product).rate

Optimizations Implemented

1. Using bulk_update for Efficiency

Instead of saving each instance separately, Django’s bulk_update was used to update multiple rows efficiently.

Product.objects.bulk_update(products, ['stock'])

This reduced the number of queries from N to 1.

2. Replacing Signals with Direct Updates

Instead of relying on Django signals, updates were performed explicitly in views or services:

Sale.objects.create(product=product, quantity=5)
Product.objects.filter(id=product.id).update(stock=F('stock') - 5)

By using F expressions, updates were performed in a single SQL statement, improving efficiency.

3. Caching Frequently Accessed Data

For product conversion rates, a caching mechanism using Django’s built-in cache framework was introduced:

from django.core.cache import cache

def get_conversion_rate(product):
    cache_key = f'conversion_rate_{product.id}'
    rate = cache.get(cache_key)
    if rate is None:
        rate = ConversionRate.objects.get(product=product).rate
        cache.set(cache_key, rate, timeout=3600)  # Cache for 1 hour
    return rate

This reduced redundant database hits and significantly improved response times.

Results

  • Bulk updates reduced update time from seconds to milliseconds.
  • Eliminating unnecessary signals lowered database write load.
  • Caching reduced redundant queries, leading to faster response times in product-related calculations.

Final Thoughts

Performance tuning in Django requires a mix of bulk operations, query optimization, and caching. Understanding when to use Django’s ORM features efficiently can prevent unnecessary database load and keep the application responsive. This approach significantly improved the speed of a POS system handling thousands of transactions daily.