104: Jonathan Reinink - Pushing More Work to the Database

What It Covers

Jonathan Reinink explains how pushing database queries instead of PHP logic reduced page loads from 30 seconds to milliseconds by using Postgres subqueries, date ranges, and WHERE NOT EXISTS clauses in Laravel Eloquent applications.

Key Questions Answered

• •N+1 vs Memory Problems: Loading 100 users with eager-loaded login records creates 10,000 Eloquent model instances consuming massive memory. Each model initialization adds milliseconds that compound into 200+ millisecond delays, worse than running multiple queries for performance-critical applications.
• •Subquery SELECT Pattern: Use SELECT sub in Laravel to add dynamic columns like last login date directly in the main query. Returns 200 total records for 100 users instead of 10,000, avoiding both N+1 queries and memory bloat while maintaining two-query efficiency.
• •WHERE NOT EXISTS Optimization: Database stops searching immediately after finding one matching record in WHERE NOT EXISTS subqueries. Reverse logic to check if conflicts exist rather than gathering all possibilities, dramatically reducing query execution time for eligibility and availability checks across large datasets.
• •R-Rule Caching Strategy: Cache time-based rules only when triggered by user actions, not time passage. Convert recurring availability rules into 365 daily records per teacher per school year, regenerating only when schedules change or administrators modify school year dates for real-time performance.
• •Database-Specific Features: Postgres offers native date range comparison and overlap detection unavailable in MySQL. Abstracting databases to remain database-agnostic sacrifices performance gains from specialized features like range operators, extensions, and optimized functions built into specific database engines over decades.