DBRaven
Start Review →
Architecture Evolution Path

PostgreSQL → Partitioned PostgreSQL

High

Introducing horizontal table partitioning within PostgreSQL to distribute write load, reduce per-partition index size, enable partition pruning on range queries, and push single-node storage limits without leaving the PostgreSQL operational model.

Topology Changes

Architecture Diff
+2 added1 modified
Single-Node PostgreSQL1 changes
→
Partitioned PostgreSQL3 changes
Unchanged
Added
Removed
Modified

From

Single-Node PostgreSQL

→
4 mutations

To

Partitioned PostgreSQL

Topology Mutations

Data Partitionedevents table

events table partitioned by created_at (monthly ranges): each month becomes a separate physical table

Operational Impact

Partition pruning eliminates month-scoped queries from scanning all data; VACUUM runs per-partition

Routing Layer AddedPartition Router (PostgreSQL declarative partitioning)

PostgreSQL partition constraint exclusion routes INSERTs and SELECTs to correct partition

Operational Impact

Queries without partition key in WHERE clause now scan all partitions: query review required

Component AddedPartition Management Job

Automated job that creates next month's partition in advance and detaches expired partitions

Operational Impact

Missing partition for current time range causes all inserts to fail: partition creation must run ahead of schedule

Dependency AddedPartition-Aware Query Planner

All application queries must be reviewed for partition key inclusion to ensure pruning

Operational Impact

Queries that previously used seqscan on 100GB now use seqscan on 8GB monthly partition

Migration Stages

1
Partition Strategy Design1-2 weeks

Identify partition key (most commonly created_at or tenant_id). Decide range vs hash partitioning. Determine partition granularity (monthly, weekly, quarterly). Document all queries that will and will not benefit from partition pruning.

Low risk·Rollback possible
2
Shadow Table Creation1 week

Create new partitioned table structure alongside existing table. Do not migrate data yet. Validate that all indexes, constraints, and foreign keys can be replicated per partition.

Low risk·Rollback possible
3
Dual Write1-2 weeks

Application writes to both old table and new partitioned table simultaneously. Validate partition routing is correct for recent data. Monitor insert latency on both paths.

Medium risk·Rollback possible
4
Historical Data Migration1-4 weeks

Backfill historical data from old table into partitioned table in batches. Use pg_partman or custom migration scripts. Validate row counts per partition. This is the longest and riskiest step: target 10k-50k rows per batch with sleep between batches.

High risk·Rollback possible
5
Read Traffic Cutover1-2 weeks

Switch application reads to partitioned table. Monitor partition pruning effectiveness via EXPLAIN ANALYZE. Confirm query plans use partition exclusion.

Medium risk·Rollback possible
6
Old Table Archival1 week

Rename old table to _archived. Keep for 30 days before dropping. Remove dual-write path from application code.

Low risk·No rollback after this stage

Migration Risks

operationalCritical

Missing partition for current time window causes all INSERTs to fail with 'no partition of relation found'

Mitigation

Create partitions 7-30 days in advance; alert when next partition does not exist before its time window opens

data_lossCritical

Historical data migration batch failures can leave partial data in partitioned table

Mitigation

Validate row counts and checksums per partition before dropping old table; keep old table for 30+ days after cutover

couplingWarning

Application queries that omit the partition key perform full-partition scans: worse than before partitioning

Mitigation

Run EXPLAIN ANALYZE on all high-frequency queries; enforce partition key in WHERE clause via query review

operationalWarning

pg_partman or custom partition management requires its own monitoring and failure alerting

Mitigation

Alert on partition creation job failures; test partition creation in staging monthly

Coupling Changes

schema couplingIncreases

Application must include partition key in queries or pay full-partition scan cost

Consequence

ORM abstraction layers may hide missing partition key inclusion: query review becomes mandatory

operational couplingIncreases

Partition lifecycle management (create, detach, drop) becomes a recurring operational dependency

Consequence

A missing partition prevents all writes: partition creation job must be monitored as a critical process

Consistency Model Changes

  • ·Intra-partition ACID guarantees are unchanged: transactions within a partition are fully consistent
  • ·Cross-partition uniqueness constraints are not enforced by PostgreSQL declarative partitioning
  • ·Unique indexes must include the partition key: global uniqueness enforcement requires application-level logic

Rollback Risks

  • ·Historical data migration to partitioned table is not easily reversed once old table is dropped
  • ·Application code changes for partition-key-aware queries cannot be cleanly rolled back after cutover
  • ·Partitioned table foreign key references from other tables require schema changes that may not be reversible