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:
