Module 4: Native Partitioning in PostgreSQL
Module project: partition `events` with 50M rows and a zero-downtime migration
What are you going to build, and why?
You've reached the module's close. The previous capsules taught you the pieces: deciding when to partition (02), applying range (03), list (04), hash (05), verifying pruning (06), and automating with pg_partman (07). This project integrates them into a real case with all of production's constraints.
You're going to take an events table with 50 million rows, 30 GB on disk, unpartitioned, serving a Blog API with live traffic. Your lead asked for:
- Partition it by month so the dashboards drop from 4 seconds to <100 ms.
- Configure
pg_partmanfor 18-month retention and automatic maintenance. - Do the migration with no downtime — the API has to keep responding throughout the whole process.
- Produce before/after benchmarks that demonstrate the improvement with real numbers.
- Document the architectural decisions (
ARCHITECTURE.md) for code review and future maintenance.
The deliverable is a PR against the Blog API repo (from guide #8) with Alembic migrations, updated SQLAlchemy code, measured benchmarks, and documentation. When you finish, you'll have exactly the material an employer will ask to see: "show me a real case where you optimized a large table without taking production down".
This project demonstrates the skill a modern senior backend dev needs: performing surgery on critical infrastructure with controlled operational risk.
Project objective
By completing this project you will:
- Partition the
eventstable (50M rows) bycreated_atmonthly using PostgreSQL 16's declarative partitioning. - Migrate the existing data from the original table to the partitioned one with no downtime using guide #13's technique (new table → backfill → atomic swap → cleanup).
- Configure
pg_partmanwithpremake = 3andretention = '18 months', scheduled withpg_cronevery hour. - Verify with
EXPLAIN ANALYZEthat the dominant queries leverage partition pruning. - Measure and report before/after benchmarks for 4 key operations: last-month query, single insert, bulk insert, dropping an old partition.
- Document the decisions in
ARCHITECTURE.mdso the team understands the why, not just the what.
How it fits with what you learned
This project is the integration of the previous 7 capsules. The table shows which piece comes from where:
| Module concept | Where it's used in the project |
|---|---|
| Capsule 02 — Decision matrix | You justify why events meets the quantitative criteria for partitioning |
| Capsule 03 — Range by date | You apply the PARTITION BY RANGE (created_at) SQL with monthly granularity |
| Capsule 04 — List by tenant | You reference it as a complementary technique if the team needs per-tenant isolation later |
| Capsule 05 — Hash | You mention in ARCHITECTURE.md why hash is NOT the choice for events (it would be counterproductive) |
| Capsule 06 — Pruning | You validate each dominant query with EXPLAIN ANALYZE before and after |
Capsule 07 — pg_partman | You configure the automated lifecycle |
| Guide #13 — Zero-downtime migration | You apply the atomic swap technique (without re-explaining it) |
Think of the project as a surgical operation: the table is the patient, the migration is the surgery, and the benchmarks are the lab results you present to the chief surgeon (your lead).
Technical specifications
Stack
- Language: Python 3.11+
- Framework: FastAPI 0.110+
- ORM: SQLAlchemy 2.0+ async (with the asyncpg driver)
- Migrations: Alembic
- DB: PostgreSQL 16+ (minimum 14 if your deployment doesn't support 16)
- DB extensions:
pg_partman5.0+,pg_cron1.6+ - Testing: pytest + pytest-asyncio
- Benchmarking:
time.perf_counter()for latencies,EXPLAIN (ANALYZE, BUFFERS)for validation
Initial setup
Assume you have the Blog API from guide #8 working. You'll work on that repo in a feature branch.
# In the Blog API repo
git checkout -b feature/partition-events-table
# Install additional dependencies if missing
pip install asyncpg sqlalchemy[asyncio] alembic pytest pytest-asyncio
# Verify pg_partman is available
psql -d blog_dev -c "SELECT * FROM pg_available_extensions WHERE name = 'pg_partman';"
# If it's not, install it per your OS (capsule 07)
# Create the partman schema if it doesn't exist
psql -d blog_dev -c "CREATE SCHEMA IF NOT EXISTS partman; CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;"
psql -d blog_dev -c "CREATE EXTENSION IF NOT EXISTS pg_cron;"Current events table (unpartitioned)
Assume this is the current table in guide #8:
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
post_id BIGINT REFERENCES posts(id),
event_type TEXT NOT NULL,
metadata JSONB DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX events_user_id_idx ON events (user_id);
CREATE INDEX events_post_id_created_at_idx ON events (post_id, created_at DESC);
CREATE INDEX events_created_at_idx ON events (created_at);Volume: 50M rows spread across the last 24 months (~2M per month).
Partitioned events_new table (the target)
After the migration, the table should look like this:
CREATE TABLE events (
id BIGSERIAL,
user_id BIGINT NOT NULL,
post_id BIGINT,
event_type TEXT NOT NULL,
metadata JSONB DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
-- Indexes propagated to the partitions
CREATE INDEX events_user_id_idx ON events (user_id);
CREATE INDEX events_post_id_created_at_idx ON events (post_id, created_at DESC);
-- Foreign keys (PG 12+ supports them on partitioned tables)
ALTER TABLE events ADD CONSTRAINT events_user_id_fkey
FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE events ADD CONSTRAINT events_post_id_fkey
FOREIGN KEY (post_id) REFERENCES posts(id);The partitions (created by pg_partman):
events_p2024_06 (created_at FROM '2024-06-01' TO '2024-07-01')
events_p2024_07 ...
...
events_p2026_05 (current month)
events_p2026_06 (premake)
events_p2026_07 (premake)
events_p2026_08 (premake)
events_default
Required functionality
1. Alembic migration — create the partitioned table in parallel
It must create the new structure without touching the current table. The app keeps writing to the old one during the migration.
# alembic/versions/XXXX_create_partitioned_events.py
"""Create partitioned events table
Revision ID: a1b2c3d4e5f6
Revises: previous_revision
"""
from alembic import op
import sqlalchemy as sa
def upgrade():
# 1. Create the partitioned parent table (with a temporary name)
op.execute("""
CREATE TABLE events_new (
id BIGSERIAL,
user_id BIGINT NOT NULL,
post_id BIGINT,
event_type TEXT NOT NULL,
metadata JSONB DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
""")
# 2. Indexes that will propagate to the partitions
op.execute("""
CREATE INDEX events_new_user_id_idx ON events_new (user_id);
""")
op.execute("""
CREATE INDEX events_new_post_id_created_at_idx
ON events_new (post_id, created_at DESC);
""")
# 3. Foreign keys
op.execute("""
ALTER TABLE events_new ADD CONSTRAINT events_new_user_id_fkey
FOREIGN KEY (user_id) REFERENCES users(id);
""")
op.execute("""
ALTER TABLE events_new ADD CONSTRAINT events_new_post_id_fkey
FOREIGN KEY (post_id) REFERENCES posts(id);
""")
# 4. Register with pg_partman (assuming data since June 2024)
op.execute("""
SELECT partman.create_parent(
p_parent_table => 'public.events_new',
p_control => 'created_at',
p_type => 'range',
p_interval => '1 month',
p_premake => 3,
p_start_partition => '2024-06-01'
);
""")
# 5. Configure 18-month retention
op.execute("""
UPDATE partman.part_config
SET retention = '18 months',
retention_keep_table = false,
automatic_maintenance = 'on'
WHERE parent_table = 'public.events_new';
""")
# 6. Schedule maintenance with pg_cron (every hour)
op.execute("""
SELECT cron.schedule(
'events_new_partman_maintenance',
'0 * * * *',
$$CALL partman.run_maintenance_proc();$$
);
""")
def downgrade():
op.execute("SELECT cron.unschedule('events_new_partman_maintenance');")
op.execute("DELETE FROM partman.part_config WHERE parent_table = 'public.events_new';")
op.execute("DROP TABLE events_new CASCADE;")2. Backfill the data in batches
A Python script that migrates the 50M rows from events (old) to events_new (partitioned) without blocking the app.
# scripts/backfill_events.py
import asyncio
import logging
from datetime import datetime, timedelta
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession
from app.core.db import get_async_session
logging.basicConfig(level=logging.INFO)
log = logging.getLogger(__name__)
BATCH_SIZE = 50000
WAIT_SECONDS = 0.5 # between batches, avoids saturating the DB
async def backfill_events_batch(
session: AsyncSession,
last_id: int,
) -> tuple[int, int]:
"""Migrates one batch from events to events_new.
Returns (new_last_id, rows_scanned). If rows_scanned == 0, the source is
exhausted and the backfill is done.
CAREFUL — the cursor advances by what was SCANNED from the source, not by what
was INSERTED. With dual-write active, many rows in the batch already exist in
events_new and ON CONFLICT discards them. If you measured progress with
RETURNING, a batch of pure conflicts would return zero rows and the script
would think it had finished (see "Mistake 7" below: it's a data-loss bug).
"""
result = await session.execute(
text("""
WITH batch AS (
SELECT id, user_id, post_id, event_type, metadata, created_at
FROM events
WHERE id > :last_id
ORDER BY id
LIMIT :batch_size
), ins AS (
INSERT INTO events_new (id, user_id, post_id, event_type, metadata, created_at)
SELECT id, user_id, post_id, event_type, metadata, created_at
FROM batch
ON CONFLICT (id, created_at) DO NOTHING
)
SELECT max(id) AS max_id, count(*) AS scanned FROM batch
"""),
{"last_id": last_id, "batch_size": BATCH_SIZE},
)
row = result.one()
if row.scanned == 0:
return last_id, 0 # source exhausted: the real end of the backfill
await session.commit()
return row.max_id, row.scanned
async def main():
"""Backfill loop. Resumable: reads the last processed id from a file."""
progress_file = "/tmp/backfill_events_progress.txt"
try:
with open(progress_file) as f:
last_id = int(f.read().strip())
log.info(f"Resuming from id {last_id}")
except FileNotFoundError:
last_id = 0
log.info("Starting fresh from id 0")
total_scanned = 0
start_time = datetime.now()
async for session in get_async_session():
while True:
last_id, scanned = await backfill_events_batch(session, last_id)
if scanned == 0:
log.info("Backfill complete — source exhausted")
break
total_scanned += scanned
# Persist progress so we can resume if it crashes
with open(progress_file, "w") as f:
f.write(str(last_id))
elapsed = (datetime.now() - start_time).total_seconds()
rate = total_scanned / elapsed if elapsed > 0 else 0
log.info(
f"Processed {total_scanned:,} rows so far, "
f"last_id={last_id}, rate={rate:.0f} rows/sec"
)
await asyncio.sleep(WAIT_SECONDS)
# Final validation: source and destination must match
async for session in get_async_session():
missing = await session.scalar(
text("""
SELECT count(*) FROM events e
WHERE NOT EXISTS (
SELECT 1 FROM events_new n
WHERE n.id = e.id AND n.created_at = e.created_at
)
""")
)
if missing:
log.error(f"{missing:,} rows are MISSING from events_new. Do NOT swap.")
raise SystemExit(1)
log.info("Verified: every row in events exists in events_new.")
if __name__ == "__main__":
asyncio.run(main())How to use it:
# Run it in the background with nohup, logging to a file
nohup python -u scripts/backfill_events.py > /var/log/backfill.log 2>&1 &
# Monitor progress
tail -f /var/log/backfill.log
# If it crashes or you kill it, just run it again — it resumes from the last idFor 50M rows with BATCH 50000 and a 0.5s wait: ~1000 batches × (1s per batch + 0.5s wait) = ~25 minutes. Tune BATCH_SIZE and WAIT_SECONDS based on the load your DB can accept.
3. Dual-write during the transition
While the backfill runs, the app must write to both tables (old and new). This guarantees that when you do the atomic swap, no data is missing.
# services/event_service.py
import json
from datetime import datetime, timezone
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession
from app.core.config import settings
from app.models.event import Event
async def record_event(
session: AsyncSession,
user_id: int,
event_type: str,
post_id: int | None = None,
payload: dict | None = None,
) -> Event:
"""Records an event. During the migration (DUAL_WRITE_ENABLED=True),
writes to events AND events_new so both stay in sync."""
now = datetime.now(timezone.utc)
event = Event(
user_id=user_id,
event_type=event_type,
post_id=post_id,
event_metadata=payload or {},
created_at=now,
)
session.add(event)
await session.flush() # assigns the id
if settings.DUAL_WRITE_ENABLED:
# Replicate into events_new
await session.execute(
text("""
INSERT INTO events_new (id, user_id, post_id, event_type, metadata, created_at)
VALUES (:id, :user_id, :post_id, :event_type, :metadata, :created_at)
ON CONFLICT (id, created_at) DO NOTHING
"""),
{
"id": event.id,
"user_id": user_id,
"post_id": post_id,
"event_type": event_type,
"metadata": json.dumps(event.event_metadata),
"created_at": now,
},
)
return event⚠️ Note event_metadata, not metadata. The column in PostgreSQL is indeed called metadata, but the model's attribute cannot be named that: metadata is reserved by SQLAlchemy's Declarative API. If you try, the model won't even import:
InvalidRequestError: Attribute name 'metadata' is reserved when using the Declarative API.
The model maps it explicitly:
# models/event.py
class Event(Base):
__tablename__ = "events"
id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
created_at: Mapped[datetime] = mapped_column(
TIMESTAMP(timezone=True), primary_key=True # part of the PK: it's the partition key
)
user_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
post_id: Mapped[int | None] = mapped_column(BigInteger)
event_type: Mapped[str] = mapped_column(String, nullable=False)
# a different attribute in Python, the "metadata" column in the table
event_metadata: Mapped[dict] = mapped_column("metadata", JSONB, default=dict)Environment variable:
# .env
DUAL_WRITE_ENABLED=true # for the duration of the migration4. Atomic swap
Once events_new has all the data and the app is dual-writing, you do the atomic switch: rename the tables in a single transaction.
# scripts/swap_events_tables.py
import asyncio
from sqlalchemy import text
from app.core.db import get_async_session
async def swap_tables():
"""Performs the atomic rename events -> events_old, events_new -> events.
After this:
- The app keeps working unchanged (it still queries 'events').
- 'events' is now the partitioned one.
- 'events_old' remains as a backup until we confirm everything is fine.
"""
async for session in get_async_session():
# Do NOT use text("BEGIN") / text("COMMIT"): the AsyncSession already opens
# the transaction itself. Mixing hand-written transactional SQL with the ORM's
# handling leaves the session in an inconsistent state. Use session.commit()/rollback().
try:
# 1. Verify that NO row is missing (the real sanity check).
# count(*) == count(*) isn't enough: two tables can have the same
# total and different rows. We check for genuine absences.
missing = await session.scalar(text("""
SELECT count(*) FROM events e
WHERE NOT EXISTS (
SELECT 1 FROM events_new n
WHERE n.id = e.id AND n.created_at = e.created_at
)
"""))
if missing:
raise RuntimeError(
f"{missing} rows from events are NOT in events_new. "
f"Backfill incomplete — aborting the swap."
)
old_count = await session.scalar(text("SELECT count(*) FROM events"))
new_count = await session.scalar(text("SELECT count(*) FROM events_new"))
print(f"Counts: events={old_count}, events_new={new_count}, missing=0")
# 2. Free the old index names BEFORE renaming.
# Renaming events -> events_old KEEPS its indexes' names
# (events_user_id_idx is still called that). If you don't rename them,
# you'll collide when giving the new ones that same name.
await session.execute(text(
"ALTER TABLE events RENAME TO events_old"))
await session.execute(text(
"ALTER INDEX events_user_id_idx RENAME TO events_old_user_id_idx"))
await session.execute(text(
"ALTER INDEX events_post_id_created_at_idx "
"RENAME TO events_old_post_id_created_at_idx"))
# 3. Promote the partitioned one and give it the final names
await session.execute(text(
"ALTER TABLE events_new RENAME TO events"))
await session.execute(text(
"ALTER INDEX events_new_user_id_idx RENAME TO events_user_id_idx"))
await session.execute(text(
"ALTER INDEX events_new_post_id_created_at_idx "
"RENAME TO events_post_id_created_at_idx"))
# 4. Update pg_partman to point at the new name
await session.execute(text("""
UPDATE partman.part_config
SET parent_table = 'public.events'
WHERE parent_table = 'public.events_new'
"""))
# 5. Reschedule pg_cron with the correct name.
# ONE statement per execute(): asyncpg doesn't accept multiple
# statements in a single statement (you'd get a syntax error).
await session.execute(text(
"SELECT cron.unschedule('events_new_partman_maintenance')"))
await session.execute(text("""
SELECT cron.schedule(
'events_partman_maintenance',
'0 * * * *',
$$CALL partman.run_maintenance_proc();$$
)
"""))
await session.commit()
print("Swap completed successfully!")
except Exception as e:
await session.rollback()
print(f"Swap failed (rolled back): {e}")
raise
if __name__ == "__main__":
asyncio.run(swap_tables())After a successful swap:
- Change
DUAL_WRITE_ENABLED=falseand deploy. - Monitor for 24-48 hours that everything works.
- Drop
events_oldwhen you're sure.
5. Cleanup of events_old
# scripts/cleanup_old_events.py
import asyncio
from sqlalchemy import text
from app.core.db import get_async_session
async def cleanup():
"""Drops events_old after confirming the partitioned events works."""
async for session in get_async_session():
# Interactive confirmation (irreversible operation)
confirm = input(
"Do you confirm the partitioned events has worked for >24h? "
"This operation is irreversible. (yes/NO): "
)
if confirm.lower() != "yes":
print("Cancelled.")
return
await session.execute(text("DROP TABLE events_old;"))
await session.commit()
print("events_old dropped successfully.")
if __name__ == "__main__":
asyncio.run(cleanup())6. Before/after benchmarks
A script that measures the 4 key operations on both tables and produces a report.
# scripts/benchmark_partitioning.py
import asyncio
import time
from sqlalchemy import text
from app.core.db import get_async_session
async def benchmark_query(session, sql, params=None, iterations=10):
"""Runs a query N times and returns statistics."""
times = []
for _ in range(iterations):
start = time.perf_counter()
await session.execute(text(sql), params or {})
elapsed = time.perf_counter() - start
times.append(elapsed * 1000) # ms
return {
"min": min(times),
"max": max(times),
"avg": sum(times) / len(times),
"p50": sorted(times)[len(times) // 2],
"p95": sorted(times)[int(len(times) * 0.95)],
}
async def main():
"""Comparative benchmarks. Assumes events_old (unpartitioned) and events
(partitioned) have the same data (after a successful swap)."""
benchmarks = {}
async for session in get_async_session():
# Benchmark 1: last-month query
# CAREFUL: the range is CLOSED at the top (`< NOW()`). With only
# `>= NOW() - 30d` the planner can't discard the FUTURE (premake) partitions
# and leaves them in the plan — you'd measure partitioning as worse than it
# is. See capsule 06.
for table in ["events_old", "events"]:
stats = await benchmark_query(
session,
f"""
SELECT count(*), event_type
FROM {table}
WHERE created_at >= NOW() - INTERVAL '30 days'
AND created_at < NOW()
GROUP BY event_type
"""
)
benchmarks[f"query_last_month_{table}"] = stats
# Benchmark 2: single insert
for table in ["events_old", "events"]:
stats = await benchmark_query(
session,
f"""
INSERT INTO {table} (user_id, post_id, event_type, created_at)
VALUES (1, 1, 'view', NOW())
""",
iterations=50, # more iterations for inserts
)
benchmarks[f"insert_single_{table}"] = stats
# Benchmark 3: query by user_id (does NOT use pruning on the partitioned one)
for table in ["events_old", "events"]:
stats = await benchmark_query(
session,
f"""
SELECT * FROM {table}
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 100
"""
)
benchmarks[f"query_by_user_{table}"] = stats
# Benchmark 4: dropping an old month (only applies to the partitioned one)
# For events_old we simulate it with a DELETE
# NOTE: this benchmark is destructive, run it in staging
# Report
print("\n=== BENCHMARK RESULTS ===\n")
print(f"{'Operation':<40} {'Min (ms)':>10} {'Avg (ms)':>10} {'P95 (ms)':>10}")
print("-" * 75)
for name, stats in benchmarks.items():
print(f"{name:<40} {stats['min']:>10.2f} {stats['avg']:>10.2f} {stats['p95']:>10.2f}")
if __name__ == "__main__":
asyncio.run(main())Expected output:
=== BENCHMARK RESULTS ===
Operation Min (ms) Avg (ms) P95 (ms)
---------------------------------------------------------------------------
query_last_month_events_old 3215.42 3289.81 3402.15
query_last_month_events 38.21 42.15 48.92
insert_single_events_old 0.82 1.21 1.85
insert_single_events 0.91 1.32 1.91
query_by_user_events_old 12.45 14.21 18.92
query_by_user_events 24.89 28.45 35.12
7. ARCHITECTURE.md documenting the decisions
Documentation that explains the why, not just the what.
# Architecture Decision Record: Partitioning the `events` table
## Context
The `events` table records every user action in the Blog API (views, likes,
comments, shares). In May 2026 it reached 50M rows (30 GB on disk) and grows
~2M rows/month. Observed symptoms:
- Analytics dashboards take 4 seconds (the SLO is <500ms).
- Autovacuum on `events` takes 25 minutes.
- The retention DELETE blocked the API for 40 minutes.
## Decision
Partition `events` by `created_at` with monthly granularity using PostgreSQL 16's
declarative partitioning.
## Justification
We applied module 4's decision matrix:
- ✅ Volume > 50M (50M — met)
- ❌ Size > 50GB (**30 GB — NOT met**, but at +2M rows/month it crosses the threshold in ~18 months)
- ✅ Growth > 5M/3 months (2M/month = 6M/3 months)
- ✅ Dominant queries filter by `created_at` (verified with pg_stat_statements)
- ✅ 18-month retention policy (compliance + costs)
- ✅ Slow autovacuum (25 min)
- ✅ Range DELETE blocking production (40 min)
- ✅ Team prepared (module 4 training completed)
**7/8 criteria say "partition".** The only one not met is on-disk size, and it isn't a
blocker: the two operational symptoms that genuinely hurt today — a 25-min autovacuum
and the retention `DELETE` blocking the API for 40 min — are exactly what partitioning
solves, and neither depends on crossing 50 GB.
*(If your project comes out 8/8, double-check you aren't marking green a criterion that
isn't actually met. An honest ADR about what is NOT met is worth more in code review
than one with a perfect score.)*
## Chosen type: RANGE by `created_at`
Reason: the dominant queries filter by time range (dashboards, listings).
RANGE allows optimal partition pruning + instant DROP PARTITION for retention.
Alternatives discarded:
- **HASH by `id`:** the queries aren't equality lookups on id; we'd lose
pruning for the range queries (the main case).
- **LIST by `event_type`:** low cardinality (4 values), uneven distribution,
and the queries don't predominantly filter by event_type.
## Granularity: monthly
- Size per partition: 2M rows (~1.2 GB). Optimal (fits in RAM, fast vacuum).
- Simultaneous partition count: 18 (retention) + 3 (premake) = 21. Manageable.
- Aligned with typical queries (dashboards for "this month", "last 3 months").
Alternatives discarded:
- **Daily:** 540 simultaneous partitions, high planner overhead, excessive
granularity for our query patterns.
- **Yearly:** 50M rows per partition, and last-month queries would still scan
everything (no benefit).
## Migration strategy: zero-downtime with dual-write
We applied guide #13's technique from the Data Layer sub-track:
1. Create the partitioned `events_new` in parallel (Alembic migration).
2. Backfill batch-by-batch (50k rows/batch, 0.5s wait) — 25 min total.
3. Enable dual-write in the app (writes to `events` and `events_new`).
4. Atomic swap (rename `events` → `events_old`, `events_new` → `events`).
5. Disable dual-write, deploy.
6. Monitor 24-48h.
7. Drop `events_old`.
Total time with zero downtime: ~2-3 hours of active work.
## Maintenance: `pg_partman` + `pg_cron`
- `pg_partman` to create future partitions (`premake = 3`) and drop old ones (`retention = 18 months`).
- `pg_cron` to run `partman.run_maintenance_proc()` every hour.
- Configured alerts:
- Default partition with >100 rows (Warning) / >1000 (Critical).
- Maintenance lagging >2 hours (Warning) / >24h (Critical).
- Future partitions missing (Warning).
## Results (benchmarks)
Measured the day after the migration:
| Operation | Before (unpartitioned) | After (partitioned) | Improvement |
|-----------|--------------------------|--------------------------|--------|
| Last-month query | 3289 ms | 42 ms | **78×** |
| Single insert | 1.21 ms | 1.32 ms | -9% (expected, routing overhead) |
| Query by user_id (no pruning) | 14.2 ms | 28.5 ms | -50% (expected, scans N partitions) |
| DROP of an old month | 38 minutes (equivalent DELETE) | 0.05 seconds | **45000×** |
## Accepted trade-offs
1. **Inserts ~10% slower** from the planner's routing overhead. Acceptable
given the massive benefit on queries.
2. **Queries with no date filter are slower** (they scan N partitions). Mitigation:
in code review, require a time filter on queries against `events`. If a query
genuinely needs the full history, consider a materialized view (module 5).
3. **Schema migrations affect 21+ partitions.** Mitigation: stabilize the schema
before major changes, use zero-downtime techniques (#13).
## Connection with guide #13
This project is living proof of guide #13's patterns:
- **Zero-downtime migrations:** applied for the atomic swap.
- **Partitioned audit logs:** the same pattern, transferable to `audit_logs` when the time comes.
- **Multi-tenant with RLS:** not applied here (the table isn't multi-tenant), but
capsule 04 documents how to combine it with partitioning if needed.
## Next steps
- Q3: apply the same pattern to `audit_logs` (180M rows, 5-year retention).
- Q4: evaluate whether `comments` (15M, growing) meets the criteria.
- When it reaches 100M+: consider sub-partitioning by hash of `user_id` within
each month to distribute load even further (capsule 05's case).
## References
- Module 4 of the Advanced PostgreSQL for Backend guide (capsules 02-08).
- Guide #13 (SQL Patterns for Production APIs), the zero-downtime + audit logs modules.
- [pg_partman docs](https://github.com/pgpartman/pg_partman)
- [PostgreSQL 16 partitioning docs](https://www.postgresql.org/docs/16/ddl-partitioning.html)Validations and error handling
What must be validated
- Zero missing rows after the backfill (the check that actually matters): the
NOT EXISTSfrom section 2 returns 0. Comparingcount(*)againstcount(*)is not enough — two tables can have the same total and contain different rows. - Data sample: a spot-check of 10 random rows in
events_newmust matchevents. - EXPLAIN before the swap: verify that the dominant queries on
events_newleverage pruning. -
pg_partmanconfigured correctly:premake = 3,retention = '18 months',automatic_maintenance = 'on'. -
pg_cronjob active:SELECT * FROM cron.job WHERE jobname LIKE '%partman%'returns the job. - Default partition empty post-swap: there should be no rows in
events_default. - Indexes propagated:
\di+ events*shows the indexes on each partition. - Dual-write active during the migration: confirm with
EXPLAINthat both tables receive inserts.
Errors that must be handled
- The backfill crashes halfway: the script must be resumable (reads progress from a file).
- A conflict on
(id, created_at)during the backfill:ON CONFLICT DO NOTHINGavoids duplicates (it can happen if dual-write already wrote them). - A count mismatch at swap time: the swap script must abort if any row is missing.
- The default partition receives unexpected rows: immediate alert, do NOT delete without investigating.
pg_cronstops running: monitor + a "maintenance lagging" alert.- Disk full during the backfill: monitor space and pause if it drops below 20% free.
Evaluation rubric (self-check)
Functionality (40 points)
- (10 pts) The Alembic migration correctly creates the partitioned table with
pg_partmanconfigured. - (10 pts) The backfill script migrates the data in batches without blocking, is resumable, and handles edge cases.
- (5 pts) Dual-write works: new events go to both tables during the migration.
- (10 pts) The atomic swap executes correctly with sanity checks and rolls back on failure.
- (5 pts) The
events_oldcleanup is documented and executed only after validation.
Technical quality (30 points)
- (10 pts)
EXPLAIN ANALYZEbefore and after demonstrates that pruning works on the dominant queries. - (5 pts) The indexes are propagated to every partition (verifiable with
\di+ events*). - (5 pts)
pg_partmanconfigured with premake ≥ 3 and appropriate retention, automatic_maintenance active. - (5 pts) The
pg_cronjob is scheduled and verified working. - (5 pts) The 4 minimum alerts are configured (default partition, maintenance lagging, future partitions, anomalous size).
Measurable benchmarks (20 points)
- (5 pts) Last-month query: improvement ≥ 50× (expected: 3.2s → 40ms or better).
- (5 pts) DROP of an old partition: <100ms (vs the equivalent DELETE taking minutes).
- (5 pts) Single insert: degradation <15% (the expected routing overhead).
- (5 pts) A query without pruning (random access): identified and documented as an accepted trade-off.
Documentation (10 points)
- (5 pts)
ARCHITECTURE.mddocuments the decisions (why range, why monthly, why 18m retention). - (3 pts) Explicit trade-offs documented (what you gain, what you lose).
- (2 pts) The connection with guide #13 is mentioned (zero-downtime + audit logs).
Extra credit (optional, up to +15 pts)
- (+5 pts) Implement S3 archiving before dropping old partitions (compliance).
- (+5 pts) Configure a Grafana/Datadog dashboard showing: count per partition, size, latencies by query type.
- (+3 pts) Automated tests validating the partitioning's behavior (pytest).
- (+2 pts) Document the restore procedure if you need to recover archived data.
Total: 100 points Pass: ≥ 70 points Exemplary: ≥ 90 points
Common mistakes in this project
Mistake 1: a blocking backfill in a single transaction
Symptom: you run INSERT INTO events_new SELECT * FROM events; in a script. The transaction holds a lock for 30+ minutes. The API slows down noticeably.
Why it happens: a single transaction for 50M rows is enormous. A SHARE UPDATE lock on events, lots of WAL generated, autovacuum blocked.
How to fix it: use the batch-by-batch backfill script with frequent commits. Each batch is a short transaction. The app keeps working.
Mistake 2: forgetting dual-write and losing data during the swap
Symptom: you finish the backfill and do the swap directly. The inserts from the backfill's last 25 minutes weren't replicated because they only went to events. You lose them.
Why it happens: the backfill copies the rows that existed when it started. The new ones (inserted during the backfill) only go to events. At swap time, events (the new one, formerly events_new) doesn't have those rows.
How to fix it: enable dual-write before starting the backfill. New rows go to both tables; the backfill may re-copy them but ON CONFLICT DO NOTHING avoids duplicates.
Mistake 3: not testing the swap in staging first
Symptom: you run the swap script straight in production. Something fails mid-transaction. The app is in an inconsistent state.
Why it happens: overconfidence. The swap is a critical operation combining a rename + cron reschedule + pg_partman config update. Any error leaves everything broken.
How to fix it: always run the full procedure in staging with representative data before production. Document the timing, validations, and rollback procedure.
Mistake 4: trusting that pruning works without verifying
Symptom: you finish the migration and declare victory. A week later a dashboard is still slow. You investigate: the query didn't use created_at in the WHERE, so there was no pruning.
Why it happens: you assume every improvement comes from the partitioning. But pruning only applies to queries filtering by the partition key.
How to fix it: a systematic post-migration audit. Take the 10 dominant queries (pg_stat_statements), run EXPLAIN ANALYZE on each, confirm pruning applies where it should. If one doesn't leverage pruning, decide: refactor the query or accept the documented trade-off.
Mistake 5: dropping events_old too quickly
Symptom: on swap day everything looks fine. You drop events_old that night. The next day there's a bug in the app that requires comparing old data: you can't anymore.
Why it happens: rushing to "close the ticket". The drop is irreversible.
How to fix it: wait a minimum of 48 hours. Ideally a week. Validate there were no bugs or need for comparison. Only then drop.
Mistake 7 (the most dangerous): measuring backfill progress with RETURNING
Symptom: the backfill logs "Backfill complete" after a few minutes. The counts don't add up, but since the script said it finished, you do the swap. You permanently lose rows.
Why it happens: it's the interaction between ON CONFLICT DO NOTHING and RETURNING, and it's subtle:
RETURNINGonly returns the rows actually inserted. The onesON CONFLICTdiscarded don't show up.
And you have dual-write active (you enabled it before the backfill, as Mistake 2 requires), so events_new already has rows. If a batch lands entirely on rows dual-write already wrote, RETURNING returns zero rows — and a script that reads "zero rows returned" as "there's nothing left to migrate" cuts the loop off in the middle of the source.
Check it with 10 rows, 5 of which are already in the destination:
INSERT INTO events_new (id, created_at)
SELECT id, created_at FROM events WHERE id > 0 ORDER BY id LIMIT 5
ON CONFLICT (id, created_at) DO NOTHING
RETURNING id; id
----
(0 rows) ← the script concludes "backfill complete"
…and rows 6 through 10 were never migrated. At swap time, they disappear.
How to fix it: have the cursor advance by what was scanned from the source, not by what was inserted into the destination. That's what the script in section 2 does: a batch CTE reading from the source, an ins CTE inserting, and a SELECT max(id), count(*) FROM batch reporting the real progress. The stop condition becomes scanned == 0 (the source is exhausted), which is the right question.
And on top of that: validate at the end. Never do the swap trusting that the script said "complete". Run the NOT EXISTS from section 2 and confirm that zero rows from events are missing in events_new.
Mistake 6: not updating the pg_partman config after the rename
Symptom: you do the swap, renaming events_new → events. But you forget to update partman.part_config. The next run_maintenance looks for events_new (which no longer exists) and fails silently. Future partitions don't get created.
Why it happens: pg_partman keeps its metadata by table name. Renaming the table doesn't update that metadata automatically.
How to fix it: include the UPDATE partman.part_config in the swap's transaction (as the script shows). Verify afterwards with SELECT * FROM partman.part_config.
What to do if you get stuck
- If the Alembic migration fails: verify that
pg_partmanandpg_cronare installed (SELECT * FROM pg_extension). Verify your user has permission to create extensions. - If the backfill is very slow: reduce
BATCH_SIZE(less lock per batch) and increaseWAIT_SECONDS(more breathing room for the DB). If it still doesn't progress, check there aren't heavy concurrent queries saturating the DB. - If the swap fails: the script rolls back. Verify no rows are missing. If the difference is large, dual-write or the backfill had a problem — investigate before retrying.
- If EXPLAIN doesn't show pruning: review capsule 06 (the 6 cases where pruning fails). Your query probably doesn't filter by
created_ator uses a cast/function on the column. - If
pg_partmandoesn't create future partitions: verify thepg_cronjob is scheduled and running (SELECT * FROM cron.job_run_details ORDER BY runid DESC LIMIT 10). If it isn't running, debug the scheduler. - If the default partition accumulates rows: investigate before moving them. Probably: (a)
pg_partmandidn't create the corresponding partition, or (b) there's data with weirdcreated_atvalues (NULL, far future) — a bug in the app.
Resources for the project
- Capsules 02-07 of module 4 — recap concepts whenever you need them.
- Guide #13 — Zero-downtime migrations — the atomic swap technique you apply here without re-explaining it.
- pg_partman documentation — a detailed reference of functions and configuration.
- pg_cron documentation — for the scheduler.
- PostgreSQL 16 — Table Partitioning — section 5.11, the official reference.
- Crunchy Data — Migrating to Partitioned Tables — a real migration case with similar techniques.
- SQLAlchemy 2.0 async docs — for the app code.
- Alembic documentation — for writing the migrations.
What comes next
What you built here is:
- Portfolio-worthy skill: a PR against the Blog API repo with migrations, code, benchmarks, and an ADR. Direct material to show in senior interviews.
- A reusable template: the mechanics you applied to
eventsreplicate identically toaudit_logs(the next module of the final integrative project),metrics,notifications, any time-series table that grows. - Technical argumentation: you can defend in code review why you partitioned, which type you chose, how you migrated with no downtime, and what trade-offs you accepted.
Module 4 closes here. In the next module (Materialized Views) you'll take partitioning as the foundation. Typical analytics dashboards query recent partitions and aggregate data. Computing those aggregates live every time is expensive, even with pruning. Materialized views are the piece that avoids recomputing: a "cached table" living in the DB, refreshed by a job. Capsule 1 of module 5 teaches you CREATE MATERIALIZED VIEW, REFRESH CONCURRENTLY, and how they're built on top of the partitions you just created.
Before moving on to module 5, make sure your project meets the rubric's criteria (at least 70 pts). The final project in module 8 will integrate partitioning with FTS, JSONB, materialized views, and CTEs — a solid foundation in partitioning is a prerequisite for that integration to work.
Module 4 — Advanced PostgreSQL for Backend Guide
Module 4 closes. Next module: Materialized Views — PostgreSQL's native cache for dashboards and analytical reports.