Digging Deeper on Partitioning Part 1: Time-Based Partitions

Last week we covered how partitioning avoids bottlenecks. This week we'll look at best practices for time-based partitioning in Postgres and MySQL.

• Pick the right interval: Use daily partitions for high-volume logs/events and monthly for moderate workloads like invoices. Aim to keep each partition under ~100M rows.
• Match query patterns: If queries target the last 7 days, daily partitions prune well. For monthly reports, monthly partitions align better.
• Control partition count: A few dozen to a few hundred partitions is manageable; thousands drag down planning time.
• Automate lifecycle: Pre-create upcoming partitions and drop expired ones. For example: ALTER TABLE events DROP PARTITION p2023_01; removes January's data instantly.
• Index locally: Add local indexes like (timestamp, user_id) or (timestamp, tenant_id) to accelerate scans within partitions.
• Handle cross-partition queries: For multi-year analytics, use summary tables or caching instead of scanning every partition.

Time-based partitioning is not a silver bullet, but it stabilizes performance as data grows. Readyset complements this approach by caching hot-path queries so the database does less work in the first place. Together, teams get predictable performance at scale without constant firefighting.

🔍 What We're Watching in the Database Ecosystem

VLDB 2025 Keynote: Databases for the AI Era

In a VLDB keynote, Databricks' founder argued that AI agents are reshaping database workloads, creating new demands on scalability, adaptability, and trust. The future will require systems that merge core database principles with designs built for agent-driven queries.

Supporting our AI overlords: Redesigning data systems to be Agent-first

A Berkeley paper introduces "agentic speculation," the redundant exploratory query patterns of LLM agents, and argues databases must adapt. Proposed solutions include approximate answers, shared computation, and agentic memory caches, though open questions remain around multi-tenancy and whether agents should instead learn to query more efficiently.

SQL performance improvements: finding the right queries to fix

Mattias Geniar outlines practical ways to spot problematic MySQL queries before tuning them. Tools like debug bars, slow query logs, and process lists help surface missing indexes or high-frequency queries, where even a 5ms improvement can compound into major performance gains. Part two will cover the fixes.

In Case You Missed It

Readyset August Product update

This month Readyset QueryPilot (beta for MySQL) showed strong results on WordPress tests, with throughput up ~60% and latency down ~38%. August Core updates added support for window functions, LATERAL subqueries, heartbeat-based MySQL replication, dialect-aware ordering, and dynamic log controls. Plus, on our blog we shared deep dives on straddled join optimization, scaling WordPress with QueryPilot, and our streaming dataflow engine.