Denormalized Priority Column Staleness
Problem
When ordering records by denormalized aggregate columns (like total_loops, vine_count,
order_count), the query returns items in the wrong priority order because the
denormalized values are stale, unpopulated, or incorrect.
Context / Trigger Conditions
- A batch job processes items in unexpected order
- Top items (by some aggregate metric) are processed last or skipped
ORDER BY aggregate_column DESC doesn't return expected results- Aggregate columns show 0 or NULL for records that should have high values
- Only a subset of records have the aggregate column populated
- Backfill scripts may have run for some records but not others
Root Cause
Denormalized columns (copies of aggregated data stored for query performance) can become
stale when:
- Initial data migration didn't populate them
- Backfill scripts only ran for some records
- New source records were added without updating the denormalized column
- The aggregation logic changed but the column wasn't recalculated
Solution
Option 1: Compute at Query Time (Immediate Fix)
Join with the source table to compute actual aggregates:
-- BEFORE (broken): Uses potentially stale denormalized column
SELECT user_id, username
FROM users
WHERE status = 'pending'
ORDER BY total_loops DESC NULLS LAST;
-- AFTER (fixed): Computes actual aggregate from source
SELECT u.user_id, u.username,
COALESCE(SUM(vm.loops), 0) as actual_total_loops
FROM users u
LEFT JOIN vine_metadata vm ON u.user_id = vm.user_id
WHERE u.status = 'pending'
GROUP BY u.user_id, u.username
ORDER BY actual_total_loops DESC;
Option 2: Backfill the Denormalized Column (Permanent Fix)
Update the denormalized column from the source data:
UPDATE users u
SET total_loops = subq.actual_loops
FROM (
SELECT user_id, COALESCE(SUM(loops), 0) as actual_loops
FROM vine_metadata
GROUP BY user_id
) subq
WHERE u.user_id = subq.user_id;
Option 3: Use Materialized Views (Best of Both)
Create a materialized view for the aggregates:
CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id,
COUNT(*) as item_count,
SUM(loops) as total_loops
FROM vine_metadata
GROUP BY user_id;
-- Refresh periodically
REFRESH MATERIALIZED VIEW user_stats;
Verification
After applying the fix, verify the query returns expected results:
-- Check that top items are actually top items
SELECT user_id, username, actual_total_loops
FROM (your_fixed_query)
LIMIT 10;
-- Compare against direct aggregate
SELECT user_id, SUM(loops) as loops
FROM source_table
GROUP BY user_id
ORDER BY loops DESC
LIMIT 10;
Example
Scenario: Avatar fetcher should process top Viners first (by total loops), but
instead processes users with ID prefix "10" (effectively random order).
Investigation:
-- Check if denormalized column is populated
SELECT
COUNT(*) as total,
SUM(CASE WHEN loop_count > 0 THEN 1 ELSE 0 END) as has_loop_count
FROM users;
-- Result: Only 29,878 of 119,785 users have loop_count populated
-- Check top users by denormalized vs actual
SELECT u.user_id, u.username, u.loop_count as denormalized,
SUM(vm.loops) as actual
FROM users u
JOIN vine_metadata vm ON u.user_id = vm.user_id
GROUP BY u.user_id, u.username, u.loop_count
ORDER BY SUM(vm.loops) DESC
LIMIT 5;
-- Result: Top creators show loop_count=0 but actual=1,281,730,353
Fix: Changed the query to JOIN with vine_metadata and ORDER BY the computed sum.
Notes
- This is a classic denormalization trade-off: faster reads vs. stale data
- When denormalizing, always implement triggers or application-level updates to keep in sync
- Consider whether the aggregate query is fast enough to compute at runtime
- LEFT JOIN ensures records without source data still appear (with 0 values)
- Use
COALESCE(SUM(...), 0) to handle NULL aggregates properly
NULLS LAST in ORDER BY prevents NULL values from sorting first in DESC order
Related Patterns
- Event sourcing: Keep source events, compute aggregates as needed
- CQRS: Separate read models that are explicitly updated
- Triggers: Automatically update denormalized columns on source changes