DEV Community: Syed Muhammad Haris

How We Saved 7.6GB and Fixed 99.95% Database Bloat in Our Django Logistics Platform

Syed Muhammad Haris — Sun, 19 Oct 2025 11:32:11 +0000

A Weekend Crisis That Turned Into a Masterclass in Database Optimization

Keywords: Django Simple History, PostgreSQL optimization, database bloat, Django performance tuning, production debugging, ORM optimization, VACUUM FULL, historical records cleanup, freight management system

The Saturday Morning That Changed Everything

It was a regular Saturday morning when our production freight management platform suddenly crashed. No warnings. No obvious errors. Just... down.

Our DigitalOcean managed PostgreSQL database was struggling, CPU at 99.9%, queries timing out, and our logistics operations grinding to a halt. Shipments worth millions were in limbo, and we had no idea why.

This is the story of how we diagnosed and fixed one of the most insidious database problems in Django applications: 99.95% duplicate historical records causing massive database bloat.

The Initial Symptoms

Database Stats That Made Us Panic:

Database size: 10.2 GB (and growing)
CPU usage: Constant 99.9%
Load average: 7.37 (on a 2-core instance)
Query timeouts: Every few seconds
Application status: Completely down

We knew something was catastrophically wrong, but what?

The Investigation Begins

Step 1: Identifying the Culprit

Running a simple query to check table sizes revealed the smoking gun:

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS size_bytes
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY size_bytes DESC;

The Results Were Shocking:

Table	Total Size	Records
`historical_shipment_containers`	3.7 GB	20.7M
`historical_shipment_events`	2.2 GB	11.2M
`historical_tracking_data`	1.7 GB	6.1M
`historical_customs_records`	763 MB	2.8M

Total: 38 million historical records consuming around 8GB!

Our actual shipment data? about 1,500 shipments. Something was very, very wrong.

The Root Cause: Django Simple History Gone Wild

We use django-simple-history to track changes to shipment data - essential for freight operations where audit trails are critical. But our implementation had a fatal flaw.

What Went Wrong

Our system syncs shipment data from another service every 20 minutes via Celery background tasks. For each container, we were calling:

# The problematic code
container, created = ShipmentContainer.objects.update_or_create(
    shipment=shipment,
    container_number=container_number,
    defaults={
        'container_type': container_type,
        'gate_out_date': gate_out_date,
        'empty_return_date': empty_return_date,
    }
)

The Hidden Problem:

update_or_create() calls .save() EVERY TIME, even when data hasn't changed!

With django-simple-history installed, every .save() creates a new historical record.

The Math:

150 containers being tracked
Synced every 20 minutes
72 syncs per day
10,800 duplicate history records PER DAY
Running for months...

Result: 6.8 million duplicate container history records!

The Failed First Attempt

Django Simple History provides a built-in cleanup command:

python manage.py clean_duplicate_history --auto

We tried it. And waited. And waited.

Removed 2432 historical records for ShipmentContainer
Removed 16741 historical records for ShipmentContainer
Removed 7877 historical records for ShipmentContainer
...
(1 hour 43 minutes later, still running)

Locally, this would take days. In production? Unacceptable.

The command processes records one-by-one, which is fine for thousands of duplicates. For millions? We needed a different approach.

The SQL Solution: PostgreSQL Window Functions to the Rescue

We needed something faster. Much faster. Enter: PostgreSQL window functions with batched deletion.

The Strategy

Instead of processing records one-by-one, we used SQL to identify and delete duplicates in batches:

-- Identify duplicates using window functions
WITH ranked_history AS (
    SELECT
        history_id,
        ROW_NUMBER() OVER (
            PARTITION BY shipment_id, container_number, container_type,
                         gate_out_date, empty_return_date
            ORDER BY history_date DESC
        ) as rn
    FROM historical_shipment_containers
)
DELETE FROM historical_shipment_containers
WHERE history_id IN (
    SELECT history_id FROM ranked_history WHERE rn > 1
);

This query:

Groups identical records by all tracked fields
Numbers them chronologically (newest = 1)
Deletes everything except the newest record

Time to delete 6.8M records: ~2 hours (vs. days with the Django command)

How Window Functions Work

For those unfamiliar, ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) is like saying:

"Group records by these fields, then number each record within its group by date, keeping the newest as #1"

This lets us delete all records numbered 2, 3, 4, etc. (the duplicates) while keeping #1 (the most recent).

The Space Reclamation: VACUUM FULL

After deleting millions of rows, we discovered another gotcha: PostgreSQL doesn't automatically reclaim disk space!

The deleted rows were marked as "dead tuples" but still occupying disk space.

The Solution: VACUUM FULL

VACUUM FULL historical_tracking_data;
VACUUM FULL historical_shipment_containers;
VACUUM FULL historical_shipment_events;

⚠️ Warning: VACUUM FULL locks the table during operation. We put the application in maintenance mode for 15 minutes.

The Results:

Table	Before	After	Space Saved
Milestone	1.7 GB	13 MB	99.94%
Containers	3.7 GB	1.6 MB	99.97%
Events	2.2 GB	13 MB	99.41%

Total Saved: 7.6 GB

Preventing Future Bloat: The Real Fix

Cleaning the database was only half the battle. We needed to prevent this from happening again.

Problem 1: Unnecessary Saves in update_or_create()

Before:

# This saves EVERY TIME, even if nothing changed!
container, created = ShipmentContainer.objects.update_or_create(
    shipment=shipment,
    container_number=container_number,
    defaults={
        'container_type': container_type,
        'gate_out_date': gate_out_date,
    }
)

After:

def smart_update_container(shipment, container_data):
    """
    Only save if fields have actually changed.
    Prevents unnecessary history records.
    """
    try:
        container = ShipmentContainer.objects.get(
            shipment=shipment,
            container_number=container_data['number']
        )

        # Check if ANY field has actually changed
        needs_update = False

        if container.container_type != container_data['type']:
            container.container_type = container_data['type']
            needs_update = True

        # ONLY save if something actually changed
        if needs_update:
            container.save()

        return container

Impact: Reduced saves by 99%! API returns the same data 99% of the time, so only 1% of syncs actually need to save.

Problem 2: Type Mismatches Causing False Changes

We discovered the tracking API returns status codes as integers (50), but our database stores them as strings ("50").

# API returns: {"status_code": 50}
# Database has: "50"

# This comparison ALWAYS fails!
if tracking.status_code != api_data.get("status_code"):  # 50 != "50" → True!
    tracking.status_code = api_data.get("status_code")
    tracking.save()  # Unnecessary save every time!

Fix:

# Always convert to the same type before comparing
new_status = str(api_data.get("status_code")) if api_data.get("status_code") is not None else None

if tracking.status_code != new_status:
    tracking.status_code = new_status
    needs_update = True

Lesson: API integrations often return different types than your database stores. Always normalize types before comparison!

Problem 3: Timezone-Aware vs Timezone-Naive Datetime Comparison

Our date parsing function was returning timezone-naive datetimes, but PostgreSQL stores them as timezone-aware with Django's USE_TZ=True.

# API returns: "2025-08-22"
# Our parser created: datetime(2025, 8, 22, 0, 0, 0)  # Naive!
# Database stores: datetime(2025, 8, 22, 0, 0, 0, tzinfo=UTC)  # Aware!

# These are NEVER equal, triggering saves every sync!

This caused Django to log warnings:

RuntimeWarning: DateTimeField received a naive datetime while time zone support is active.

Fix:

def parse_api_date(date_value):
    """Parse date from API and return timezone-aware datetime."""
    from django.utils import timezone as tz

    if isinstance(date_value, dict) and date_value.get("Date"):
        date_str = date_value["Date"]
    elif isinstance(date_value, str):
        date_str = date_value
    else:
        return None

    try:
        # Try ISO format first
        dt = parser.isoparse(date_str)
        if dt.tzinfo is None:
            dt = tz.make_aware(dt, tz.get_default_timezone())
        return dt
    except ValueError:
        # Fall back to simple date format
        try:
            date_obj = datetime.strptime(date_str, "%Y-%m-%d")
            # Convert to timezone-aware datetime
            return tz.make_aware(date_obj, tz.get_default_timezone())
        except ValueError:
            return None

Impact: Eliminated all timezone-related false changes and warnings!

Problem 4: Latitude/Longitude Causing Perpetual History Bloat

For shipment tracking, we store GPS coordinates showing where vessels are. These coordinates change constantly as ships move across the ocean - sometimes every few minutes!

We were tracking every single position change in history, which was pointless. We don't need an audit trail of "vessel moved 0.5 nautical miles" thousands of times.

Solution: Exclude frequently-changing, non-critical fields from history tracking.

class ShipmentEvent(models.Model):
    # ... fields ...
    latitude = models.DecimalField(max_digits=30, decimal_places=15, ...)
    longitude = models.DecimalField(max_digits=30, decimal_places=15, ...)

    # Exclude lat/long from history - they change too frequently!
    # We still store current position, just don't track every change
    history = HistoricalRecords(excluded_fields=['latitude', 'longitude'])

Impact: Prevents thousands of unnecessary history records as vessels sail!

The Final Results

Before

Database size: 10.2 GB
Historical records: 28 million
Duplicate records: 24.2 million (99.95%)
CPU usage: 99.9% constant
Application status: Down
Sync performance: Timing out
Query response time: 2-5 seconds

After

Database size: 1.6 GB (saved 8.6 GB)
Historical records: 1.8 million
Duplicate records: 0
CPU usage: <10% average
Application status: Stable
Sync performance: Sub-second
Query response time: 50-200ms

10x performance improvement across the board!

Key Lessons Learned

1. Monitor Your Historical Tables

Historical tables can silently grow to dwarf your actual data. Set up monitoring:

-- Weekly table size check
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
    (SELECT COUNT(*) FROM tablename) as row_count
FROM pg_tables
WHERE schemaname = 'public' AND tablename LIKE '%historical%'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Add this to your weekly ops checklist or set up automated alerts when historical tables exceed expected sizes.

2. Never Blindly Use update_or_create() with History Tracking

Always check if fields have actually changed before saving:

# Bad - saves every time
obj, created = Model.objects.update_or_create(pk=1, defaults={'field': value})

# Good - only saves when changed
obj = Model.objects.get(pk=1)
if obj.field != new_value:
    obj.field = new_value
    obj.save()

3. Type Consistency Matters in API Integrations

API integrations often return different types than your database stores. Always normalize:

# Convert types before comparison
api_value = str(api_response.get("field"))  # Ensure string
db_value = str(obj.field)  # Ensure string

if api_value != db_value:
    obj.field = api_value
    obj.save()

4. Timezone Awareness is Critical

With USE_TZ=True in Django settings, always return timezone-aware datetimes from parsers:

from django.utils import timezone as tz

dt = datetime.strptime(date_str, "%Y-%m-%d")
dt = tz.make_aware(dt, tz.get_default_timezone())  # Make aware!

5. Not Everything Needs History

Exclude frequently-changing non-critical fields from history:

# Good candidates for exclusion:
# - updated_at timestamps
# - last_sync timestamps
# - GPS coordinates
# - view counts
# - Any high-frequency changing data

history = HistoricalRecords(
    excluded_fields=['updated_at', 'last_sync', 'latitude', 'longitude']
)

6. PostgreSQL Window Functions Are Powerful

For bulk operations on millions of rows, SQL outperforms ORM by orders of magnitude.

Django ORM is great for application logic, but for data cleanup, bulk updates, and analytics, raw SQL with window functions is often 100x+ faster.

7. VACUUM FULL After Large Deletions

Don't forget to reclaim disk space after deleting millions of rows!

-- Regular VACUUM (non-blocking, safe for production)
VACUUM ANALYZE table_name;

-- VACUUM FULL (blocking, use in maintenance window)
VACUUM FULL table_name;

Regular VACUUM is safe to run anytime. VACUUM FULL requires downtime but reclaims more space.

Tools and Technologies Used

Django 5.0 with django-simple-history
PostgreSQL 15 (DigitalOcean Managed Database)
Celery for background task processing
Redis as Celery broker
pgAdmin 4 for database management
DigitalOcean infrastructure and monitoring
Python 3.12

Performance Impact

Before the Fix:

Database queries: 2-5 seconds average
Background sync tasks: 45-60 seconds
Admin page loads: 8-12 seconds
API response times: 1-3 seconds
CPU: Constant 99.9%
Database connections: Maxed out

After the Fix:

Database queries: 50-200ms average
Background sync tasks: 1-2 seconds
Admin page loads: 500ms-1s
API response times: 100-300ms
CPU: 5-10% average
Database connections: Healthy pool

10x performance improvement across the board!

Monitoring and Maintenance

We now run weekly checks to catch any regression early:

-- Check for duplicate accumulation
WITH ranked AS (
    SELECT
        COUNT(*) as total,
        COUNT(DISTINCT (shipment_id, container_number, container_type, gate_out_date)) as unique_records
    FROM historical_shipment_containers
)
SELECT
    total,
    unique_records,
    (total - unique_records) as duplicates,
    ROUND(100.0 * (total - unique_duplicates) / total, 2) as duplicate_percentage
FROM ranked;

Healthy Result:

total: 13,812
unique_records: 13,802
duplicates: 10
duplicate_percentage: 0.07%

A small number of duplicates (< 1%) is normal due to legitimate updates. If this crosses 5%, we investigate.

The Human Element

This incident happened on a Saturday morning. Our team:

Immediately put the platform in maintenance mode
Communicated transparently with stakeholders
Debugged collaboratively (huge props to the team!)
Implemented fixes incrementally
Tested thoroughly before reopening
Documented everything for future reference

Time to resolution: 14 hours (Saturday 7 AM → Saturday 9 PM) Total downtime: 2 hours (maintenance mode during VACUUM operations)

We follow agile practices, which helped us:

Prioritize fixes (clean data first, prevent recurrence second)
Test in small increments
Roll back quickly if needed
Collaborate effectively under pressure

Conclusion

What started as a catastrophic Saturday morning crash turned into a masterclass in:

Database optimization
Django ORM performance tuning
PostgreSQL internals
Production debugging under pressure
Collaborative problem-solving

Key Takeaway: The most dangerous bugs are the ones that accumulate silently over months. Regular monitoring, performance testing, and database audits aren't optional - they're essential.

Our freight management platform is now faster, leaner, and ready to scale. And we learned valuable lessons that will inform our engineering practices for years to come.

If you're using django-simple-history (or any audit logging system), review your update patterns today. You might be sitting on a ticking time bomb!

Additional Resources

About the Author: Software Engineer at Maalbardaar, building scalable freight management solutions. Passionate about database optimization, Django performance, and solving real-world logistics challenges.

Tech Stack: Django, PostgreSQL, Celery, Docker, DigitalOcean, React

Follow for more: Production war stories, Django tips, and database optimization techniques.

Have you faced similar database bloat issues? How did you solve them? Share your story in the comments!

Django #PostgreSQL #DatabaseOptimization #SoftwareEngineering #ProductionDebugging #PerformanceTuning #FreightTech #LogisticsTechnology #WebDevelopment #BackendEngineering #Python #DevOps

What is google's nano banana and how it solved the biggest problem in ai image editing

Syed Muhammad Haris — Thu, 28 Aug 2025 22:00:02 +0000

so google just dropped nano banana (officially called gemini 2.5 flash image) and it actually fixes the thing that makes most ai photo editing tools useless - character consistency.
here's the problem: you upload a photo, ask the ai to "change my shirt color," and boom - suddenly you don't look like yourself anymore. face is different, lighting's off, whole vibe is wrong. makes the tools feel like party tricks instead of actual useful software.

nano banana solved this shit, and if you're building anything with visual content, this matters.

what actually makes it different

nano banana (technically gemini 2.5 flash image) has unrivaled consistency across edits - it can actually remember the details instead of rolling the dice every time you make a change.
while other tools spin for 10–15 seconds per image, nano banana often responds in 1–2 seconds. sometimes even faster. it feels like working in real time, not batch mode.

people have been going bananas over it already in early previews - it's the top-rated image editing model in the world.

a fun way to use it

i have this original image below of me smiling.

i will tell google nano banana to edit something like this, prompt: Make a fun post as google nano banana is launched and edit my pic so that Im holding a big big banana in my hand above my head, banana should have google nano banana written on it. also In the img my hands are closed you can closely fix them, only 1 hand should hold the banana on top of my head

here is what i get:

AI GeneratedPretty cool isn't

stuff you can actually do with it

consistent character placement: put the same person/product in different backgrounds, outfits, scenarios without losing their identity. perfect for social content, marketing campaigns, product shots.

multi-image fusion: combine different images into one seamless new visual. you can use multiple reference images to create a single, unified image for use cases such as marketing, training, or advertising.

precision editing: targeted transformation and precise local edits with natural language. for example, the model can blur the background of an image, remove a stain in a t-shirt, remove an entire person from a photo, alter a subject's pose, add color to a black and white photo.

multi-turn editing: keep making changes and it maintains consistency throughout. paint a room, add furniture, change lighting - all while keeping everything coherent.

why this matters if you're building stuff

if you're working on:

social apps: users can place themselves consistently across different posts/scenarios
e-commerce: customers visualize products in their spaces without quality loss
marketing tools: maintain brand character consistency across campaigns
content creation: generate consistent visual stories, comics, tutorials

with gemini 2.5 flash image, the model benefits from gemini's world knowledge, which unlocks new use cases. it's not just prettier pictures - it actually understands context and handles complex instructions.

how to start using it

test it first: try it in the gemini app (free 100 edits/day) to see if it handles your use cases
for developers: hit the gemini api or google ai studio to integrate
enterprise: vertex ai has the enterprise-grade access

all images created or edited with gemini 2.5 flash image will include an invisible synthid digital watermark, so they can be identified as ai-generated or edited.
real talk

this isn't just another ai image tool - it's the first one that actually works reliably for real use cases. nano banana, if it really is from google, marks a shift. it's not about just generating pretty images. it's about replacing the entire workflow of editing.

if you've been waiting for ai image editing to be good enough for production, this might be it.

building something that needs reliable image editing? i help founders implement ai tools and scale technical products from idea to launch. if you're considering integrating visual ai or need someone who's navigated these technical challenges,
let's talk.

smharisofficial@gmail.com

how infinite scrolling on social media actually works (and why most devs get it wrong) -2025

Syed Muhammad Haris — Mon, 25 Aug 2025 15:54:40 +0000

I was scrolling twitter at 2am last night (as one does when you should be sleeping), and i started wondering... how the fk does this thing just keep going forever?

like seriously, i've been scrolling for what feels like hours, millions of people are posting every second, and somehow i never see the same tweet twice, never get a "loading error", never hit some weird gap where posts are missing. it just... works?

turns out there's some actually clever engineering behind this that most people (including me until recently) have no clue about, and it's one of those things that separates real production systems from the mvp prototypes most of us start with.

the obvious approach (that doesn't work)

when you're building your first app with infinite scrolling, you probably do what i did - offset pagination. it's the most obvious thing in the world:

SQL
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 0;  -- page 1
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 20; -- page 2  
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40; -- page 3

works great when you have 1000 users and a few thousand posts. feels like magic when you first implement it and see that smooth scrolling action.

img by Dan Hollick

but this approach doesn't work at scale, and here's why:

performance gets worse with every page - when you're on page 1000, the database has to count through 20,000 rows just to find your starting point. it's like counting to 20,000 every time someone scrolls down.

inconsistent results - if someone posts a new tweet while you're scrolling, your offset gets messed up. you might see the same post twice or miss posts entirely. super confusing for users.

memory usage - databases hate large offsets, they literally have to load and skip all those rows in memory.

how the big players actually do it (cursor pagination)

platforms like twitter, reddit, instagram, tiktok all use something called cursor pagination instead, and it's honestly way more elegant once you understand it.

instead of saying "give me page 5", you say "give me 20 posts after this exact post id/timestamp". the api looks something like:

GET /posts?after=1692123456789&limit=20

where that after parameter is called a cursor - it's like leaving a bookmark instead of counting pages.

img by Dan Hollick

on the backend, your query becomes:

sql
SELECT * FROM posts 
WHERE created_at < '2023-08-15 12:34:56' 
ORDER BY created_at DESC 
LIMIT 20;

this is lightning fast because:

constant time lookups - databases can jump directly to your cursor position using indexes
stable results - new posts don't affect your current scroll position
scales infinitely - doesn't matter if you have 1000 posts or 1 billion
the frontend just stores the last post's timestamp/id and uses that for the next request. boom, perfect infinite scrolling that works exactly like the big platforms.

the devil's in the implementation details
but here's where it gets interesting - each platform has their own flavor of cursors depending on their needs:

twitter uses tweet ids (which are chronologically ordered snowflake ids), reddit combines score and timestamp for their ranking algorithm, instagram has to handle both chronological and algorithmic feeds.

some use opaque cursor tokens (encrypted strings that hide the implementation), others expose the actual values. some encode multiple values into a single cursor for complex sorting.

there's also edge cases like handling deleted posts, dealing with ties in timestamps, making sure cursors work across different timezones, pagination limits to prevent abuse... lot of little details that make the difference between a prototype and production-ready infinite scrolling.

what's next in this series

this is the first post in my "how shit actually works" series because i keep using these tools every day and realizing i have no idea what's happening under the hood, and apparently neither do a lot of other people.

next post i'm gonna dive deeper into cursor token implementation - how to handle complex sorting, deal with edge cases, and build cursors that actually work in production. might even show some real code examples from projects i've worked on.

building your own infinite scroll?

if you're working on something that needs this kind of functionality, honestly the jump from prototype pagination to production-ready cursor systems can be tricky. there's a lot of little details that aren't obvious until you hit scale or real user patterns.

i help founders and indie hackers turn their ideas into actual working products all the time - from mvp to scale, full-stack development, team building, all that stuff. if you're building something cool and want someone who's been through these scaling challenges before, hit me up.

anyway, that's how your favorite apps keep you scrolling forever without breaking. pretty neat engineering hidden behind something that just feels like magic.