CTEs are Fracking Magical

We have a cron job. It runs frequently, looking for rows that have changed, or their parent relationships that have changed, or grandparents. There’s millions of rows. These cron jobs were causing tablescans of 200K+ rows each time. Replica latency was horrible.

CTEs and a few well-placed indexes. Down to only scanning rows needed. Weirdly just indexes and joins/subqueries didn’t help.

This post sponsored by me pulling my hair out staring at DB graphs for three days.