PostgreSQL Partitioning for Multi-Tenant Audit Logs: Querying 100M Events Without Table Scans

PostgreSQL Partitioning for Multi-Tenant Audit Logs: Querying 100M Events Without Table Scans I'll be direct: if you're running a SaaS with compliance requirements and your audit_logs table is approaching 50M rows, you're three months away from pain. I've watched audit queries go from 200ms to 8 seconds in production at 2am because someone ran a "give me all logs for tenant X" report. Partitioning isn't optimization theater—it's table-stakes infrastructure. At CitizenApp, we store 9 months of audit logs across 50+ tenants. Without partitioning, a single compliance query would full-table scan 100M+ rows. With it, we hit the same data in <100ms. This post is exactly how we do it. Why Partitioning Matters (The Reality Check) Most developers treat audit_logs like any other table. You add an index on tenant_id and created_at , call it done, and move on. Then your compliance officer runs a query like: SELECT * FROM audit_logs WHERE tenant_id = 'acme-corp' AND created_at >= '2024-01-01' ORDER BY created_at DESC ; At 50M rows, even with a composite index, PostgreSQL has to: Index scan → finds millions of matching rows Random I/O all over the table Spill to disk if sorting is large Hope the OS cache is warm Partitioning solves this by eliminating the data you don't need from day one . Instead of scanning a 100GB table and filtering it down, PostgreSQL can skip entire partitions. A query against January 2024 data simply ignores partitions for February–December. I prefer partitioning because it's native PostgreSQL—no external caching layer, no read replicas, no Redis gymnastics. It's boring infrastructure that works. The Partitioning Strategy: Composite Partitioning (Range + List) I use a two-level partitioning scheme: Range partition by month ( created_at ) — keeps each partition to ~5–10GB List subpartition by tenant — ensures compliance queries are single-partition scans This is deliberately opinionated. You could do range-only, but then a multi-tenant query still scans the