PostgreSQL Partitioning & Database Migration Runbook

Overview

A focused operations toolkit and runbook for migrating the platform’s PostgreSQL database to a partitioned layout, including scheduled maintenance via pg_cron. It packages the SQL, shell scripts, and step-by-step procedure needed to rehearse and execute the change safely.

Why It Exists

As article and match volumes grow, large unpartitioned tables hurt query performance and maintenance. Partitioning is high-risk on a live system, so the work needed a repeatable, rehearsable procedure, clone production locally, apply and verify the migration, then run it for real, rather than improvised one-off SQL.

What We Built

A scripted runbook: a Docker Compose PostgreSQL 15 instance acting as a local clone of production, plus dump.sh, restore.sh, and reset.sh to pull a production snapshot, load it, and reset between rehearsals. commands.sql carries the partitioning and pg_cron setup (enabling cloudsql.enable_pg_cron and configuring the cron database), steps.md documents the ordered procedure, and staging-indexes.csv captures the index inventory to recreate. The result is a self-contained, reproducible migration package targeting Cloud SQL.

Technologies & Approach

PostgreSQL 15 partitioning with pg_cron for scheduled partition maintenance, driven by Bash automation and Docker Compose for an isolated rehearsal environment. Cloning production locally first means the migration is validated against real data and index shapes before touching the live database.

Outcome / Impact

Delivered a safe, documented path to partition a production Cloud SQL database, turning a risky structural change into a rehearsed, reversible operation with explicit steps and recovery scripts.

Capabilities Demonstrated

• Planning and executing PostgreSQL table partitioning on production Cloud SQL
• Building rehearsal environments by cloning production data locally
• Scheduled database maintenance with pg_cron
• Authoring operational runbooks with dump/restore/reset automation