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 whole partition. You could do list-only, but then you can't efficiently age out old data.
Here's the schema:
-- Main partitioned table
CREATE TABLE audit_logs (
id BIGSERIAL,
tenant_id UUID NOT NULL,
user_id UUID,
action VARCHAR(50) NOT NULL,
resource_type VARCHAR(100),
resource_id VARCHAR(255),
changes JSONB,
ip_address INET,
user_agent TEXT,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
PRIMARY KEY (id, tenant_id, created_at)
) PARTITION BY RANGE (created_at);
-- January 2024 range partition
CREATE TABLE audit_logs_2024_01 PARTITION OF audit_logs
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')
PARTITION BY LIST (tenant_id);
-- Subpartitions for each tenant in January
CREATE TABLE audit_logs_2024_01_acme PARTITION OF audit_logs_2024_01
FOR VALUES IN ('acme-corp-id-here');
CREATE TABLE audit_logs_2024_01_widget PARTITION OF audit_logs_2024_01
FOR VALUES IN ('widget-inc-id-here');
-- Repeat for Feb, Mar, etc.
CREATE TABLE audit_logs_2024_02 PARTITION OF audit_logs
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01')
PARTITION BY LIST (tenant_id);
Why this structure? Because a query like:
SELECT * FROM audit_logs
WHERE tenant_id = 'acme-corp-id-here'
AND created_at >= '2024-01-15'
AND created_at < '2024-02-01';
...only touches audit_logs_2024_01_acme. PostgreSQL's constraint exclusion prunes all other partitions at planning time. No scanning, no guessing.
Automating Partition Creation
Creating partitions manually is insane. I use a stored procedure that runs monthly via a cron job (or GitHub Actions):
CREATE OR REPLACE FUNCTION create_monthly_audit_partitions(
p_year INT,
p_month INT,
p_tenants UUID[]
)
RETURNS void AS $$
DECLARE
v_table_name TEXT;
v_start_date DATE;
v_end_date DATE;
v_tenant_id UUID;
BEGIN
v_start_date := MAKE_DATE(p_year, p_month, 1);
v_end_date := MAKE_DATE(p_year, p_month, 1) + INTERVAL '1 month';
v_table_name := FORMAT('audit_logs_%s_%s', p_year, LPAD(p_month::TEXT, 2, '0'));
-- Create range partition
EXECUTE FORMAT(
'CREATE TABLE IF NOT EXISTS %I PARTITION OF audit_logs
FOR VALUES FROM (%L) TO (%L)
PARTITION BY LIST (tenant_id)',
v_table_name,
v_start_date,
v_end_date
);
-- Create subpartitions for each tenant
FOREACH v_tenant_id IN ARRAY p_tenants
LOOP
EXECUTE FORMAT(
'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
FOR VALUES IN (%L)',
FORMAT('%s_%s', v_table_name, SUBSTR(v_tenant_id::TEXT, 1, 8)),
v_table_name,
v_tenant_id
);
END LOOP;
END;
$$ LANGUAGE plpgsql;
Run it from your FastAPI worker or cron:
from sqlalchemy import text
from app.db import SessionLocal
def create_partitions_for_next_month():
from datetime import datetime, timedelta
db = SessionLocal()
today = datetime.now()
next_month = today + timedelta(days=32)
# Get all active tenants
tenants = db.execute(
text("SELECT id FROM tenants WHERE active = true")
).scalars().all()
db.execute(
text("""
SELECT create_monthly_audit_partitions(:year, :month, :tenants)
"""),
{
"year": next_month.year,
"month": next_month.month,
"tenants": [str(t) for t in tenants]
}
)
db.commit()
db.close()
# Schedule this monthly
# (via APScheduler, Celery, or GitHub Actions)
Query Example: Compliance Report (Still Fast at 100M Rows)
# FastAPI endpoint: export audit logs for compliance
@app.get("/api/v1/tenants/{tenant_id}/audit-export")
async def export_audit_logs(
tenant_id: str,
start_date: datetime,
end_date: datetime,
db: Session = Depends(get_db)
):
# This query touches only the relevant partitions
logs = db.query(AuditLog).filter(
AuditLog.tenant_id == tenant_id,
AuditLog.created_at >= start_date,
AuditLog.created_at < end_date
).order_by(AuditLog.created_at.desc()).all()
return [log.to_dict() for log in logs]
With partitioning, this runs in ~80ms even for a year of data. Without it, 8–12 seconds.
Gotcha: The Dreaded Default Partition
This burned me hard: if you don't explicitly list every tenant in your subpartitions, inserts fail silently or slow crawl into a DEFAULT partition.
-- DON'T do this:
CREATE TABLE audit_logs_2024_01 PARTITION OF audit_logs
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')
PARTITION BY LIST (tenant_id)
DEFAULT; -- ← This is a trap
New tenants will insert into the DEFAULT partition, which defeats the entire purpose. Instead, always create explicit subpartitions when onboarding a tenant:
async def create_tenant(tenant_name: str, db: Session):
tenant = Tenant(name=tenant_name)
db.add(tenant)
db.flush()
# Create subpartitions for this tenant in all existing month partitions
from datetime import datetime
today = datetime.now()
for month_offset in range(-9, 1): # Last 9 months + current
dt = today.replace(day=1) - relativedelta(months=-month_offset)
partition_name = f"audit_logs_{dt.year}_{dt.month:02d}"
db.execute(text(f"""
CREATE TABLE IF NOT EXISTS {partition_name}_{tenant.id.hex[:8]}
PARTITION OF {partition_name}
FOR VALUES IN ('{tenant.id}')
"""))
db.commit()
Archival and Retention
Partitioning also makes data lifecycle management trivial. Drop old partitions instead of DELETE statements:
-- Drop all audit logs from January 2023 instantly
DROP TABLE audit_logs_2023_01;
This is O(1)—no table scan, no transaction log bloat. Compare to DELETE FROM audit_logs WHERE created_at < '2023-02-01', which locks the table and generates gigabytes of WAL.
Final Take
Partitioning isn't "optimization" in the sense of indexes or caching. It's correctness. A well-partitioned audit table is queryable, maintainable, and compliant. An unpartitioned