database performance issue due to rating calculations

[jmankoff]

I was trying to debug a freeze in a local version of the backend, and the AI came up with this. Didn't help my original problem, but probably something that should be looked into.

There are multiple trigger-related issues that are likely causing your database to freeze:

Primary Issue: Trigger Performance Bottleneck

Your computed_rating trigger system creates a performance bottleneck:

The Problem Chain:

1. Every rating INSERT/UPDATE/DELETE fires trg_rating_update_computed_rating (AFTER trigger on ratings table)
2. Each execution performs an UPDATE on the products table:

   UPDATE products
   SET computed_rating = compute_product_rating(NEW.product_id)
   WHERE id = NEW.product_id;

3. The compute_product_rating() function runs TWO queries:
   • SELECT AVG(rating) FROM ratings WHERE product_id = ...
   • SELECT source_rating FROM products WHERE id = ...

Why This Freezes:

• Bulk operations become extremely slow: If a scraper inserts 100 products with ratings, or users add multiple ratings, you get 100+ sequential product table updates
• Lock contention: Multiple ratings for the same product cause row-level locks to queue up
• Query overhead: Each trigger execution runs 2-3 SQL queries

Additional Issues Found:

1. BEFORE INSERT trigger anomaly (20260104_add_product_computed_rating_trigger.sql):

   -- This queries a product that doesn't exist yet!
   NEW.computed_rating := compute_product_rating(NEW.id);

   During INSERT, NEW.id doesn't exist in the products table yet, so the function's query returns NULL or scans unnecessarily.

2. Recent fix was incomplete (20260127_fix_computed_rating_triggers.sql):

   • Fixed the field reference but didn't address the performance issue

Solutions:

Option 1: Disable Automatic Updates (Quick Fix)

Disable the problematic trigger, then update ratings manually when needed or on a schedule.

Option 2: Batch Update Strategy (Better)

Replace the row-level AFTER trigger with a statement-level trigger that batches updates:

Option 3: Async/Scheduled Update (Best for Scale)

Remove real-time triggers entirely and update computed_rating via:

• Background job every 5-15 minutes
• Manual API endpoint for immediate updates
• Only when displaying sorted results

Would you like me to create a migration file implementing one of these solutions?