Definition
- Lifecycle as metadata – Time-based partitioning converts bulk delete and archival from heavy row operations into cheap, instantaneous metadata operations.
- Drop vs DELETE – Dropping a partition removes a whole physical table instantly, while a mass DELETE scans millions of rows, generates WAL, and leaves bloat.
- Archive workflow – Detach an old partition, copy it to cold storage (S3), then drop it. Hot data stays fast.
- Retention automation – A scheduled job pre-creates future partitions and retires old ones, managed by tools like pg_partman.
The National Bank keeps one transaction book per month. When records older than seven years must be removed, the clerk simply takes the old monthly books off the shelf and moves them to the archive room or shreds them: one clean action per book. Without partitioning, the clerk goes through a single giant ledger line by line crossing out millions of old entries, which is slow, messy, and leaves the book full of holes.
Understanding the Concepts
Why is mass DELETE so expensive at scale?
The naive way to retire old data is DELETE FROM transactions WHERE created_at < '2018-01-01'. On a billion-row table, this is one of the most damaging operations you can run.
-- DANGEROUS: mass DELETE on a billion-row table
DELETE FROM transactions WHERE created_at < '2018-01-01';
-- What happens:
-- 1. Scans and deletes millions of rows (hours of execution)
-- 2. Writes an enormous amount of WAL (fills disk)
-- 3. Holds row-level locks (blocks concurrent operations)
-- 4. Leaves dead tuples that bloat the table and indexes
-- 5. Requires expensive VACUUM to reclaim space
-- 6. Hurts live traffic the entire time it runs
package com.bank.maintenance;
public class MassDeleteProblem {
// With 1 billion rows and 6 years of old data:
// DELETE removes ~800 million rows
// Estimated time: 4-8 hours
// WAL generated: ~500 GB
// Dead tuples: 800 million (need VACUUM)
// VACUUM time: 2-4 hours
// Total disruption: 6-12 hours
// During this time:
// - Live balance checks are slow (locks, I/O contention)
// - Disk may fill up from WAL
// - Replication lag spikes on replicas
}
How does partitioning change the economics?
Range partitioning by time makes each partition a separate physical table holding exactly one time window. Retiring old data becomes a partition-level operation.
-- Partitioned table: one partition per month
CREATE TABLE transactions (
txn_id BIGSERIAL, account_id VARCHAR(20),
amount NUMERIC(15,2), created_at TIMESTAMP
) PARTITION BY RANGE (created_at);
CREATE TABLE txn_2018_01 PARTITION OF transactions
FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');
CREATE TABLE txn_2018_02 PARTITION OF transactions
FOR VALUES FROM ('2018-02-01') TO ('2018-03-01');
-- ... one per month ...
How do you drop old data instantly?
Dropping a partition removes a whole physical object instantly. There is no row-by-row scan, almost no WAL, and no bloat.
-- Option 1: Detach and drop
ALTER TABLE transactions DETACH PARTITION txn_2018_01;
DROP TABLE txn_2018_01;
-- Instant! No scan, no WAL, no dead tuples, no VACUUM needed
-- Option 2: Drop directly (if no archival needed)
DROP TABLE txn_2018_01;
-- Also instant
-- Compare:
-- Mass DELETE: 4-8 hours, 500 GB WAL, blocks live traffic
-- DROP PARTITION: < 1 second, minimal WAL, no impact on live traffic
package com.bank.maintenance;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
public class PartitionRetentionService {
private final DataSource dataSource;
public PartitionRetentionService(DataSource dataSource) {
this.dataSource = dataSource;
}
// Drop a monthly partition: instant, no disruption
public void dropOldPartition(String partitionName) throws SQLException {
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement()) {
stmt.execute("ALTER TABLE transactions DETACH PARTITION " + partitionName);
stmt.execute("DROP TABLE " + partitionName);
System.out.println("Dropped partition: " + partitionName);
}
}
}
How do you archive old partitions?
Detach the old partition, copy it to cheaper cold storage, then drop it. The hot table stays small and fast.
-- Step 1: Detach (no longer part of the live table)
ALTER TABLE transactions DETACH PARTITION txn_2018_06;
-- Step 2: Dump to S3 for long-term archive
-- pg_dump -t txn_2018_06 nbi_ledger | gzip | aws s3 cp - s3://nbi-archive/txn_2018_06.sql.gz
-- Step 3: Verify archive integrity
-- aws s3 ls s3://nbi-archive/txn_2018_06.sql.gz
-- Step 4: Drop the local table (archive is on S3)
DROP TABLE txn_2018_06;
package com.bank.maintenance;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
public class PartitionArchiveService {
private final DataSource dataSource;
public PartitionArchiveService(DataSource dataSource) {
this.dataSource = dataSource;
}
public void archiveAndDrop(String partitionName) throws SQLException {
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement()) {
// Detach from live table
stmt.execute("ALTER TABLE transactions DETACH PARTITION " + partitionName);
System.out.println("Detached: " + partitionName);
// Archive to S3 (would be a shell command in practice)
System.out.println("Archiving " + partitionName + " to S3...");
// pg_dump + aws s3 cp
// Verify archive before dropping
System.out.println("Verifying archive integrity...");
// Drop local table
stmt.execute("DROP TABLE " + partitionName);
System.out.println("Dropped: " + partitionName);
}
}
}
How does this keep hot data fast?
Because only recent partitions receive writes and most reads, the primary store holds a small working set. Indexes on the hot partitions stay small and cache-resident.
National Bank of India retention: 7 years
Table: 500 million total rows across 84 monthly partitions
Hot partitions (last 3 months): 20 million rows
- Receive all writes and 90% of reads
- Indexes fit entirely in shared_buffers
- Balance checks and transfers are fast
Warm partitions (3 months - 1 year): 80 million rows
- Read occasionally for annual statements
- On primary storage but rarely cached
Cold partitions (1 year - 7 years): 400 million rows
- Archived to S3 or compressed tablespace
- Detached from live table
- Accessible for audit by re-attaching or restoring
Without partitioning:
All 500 million rows in one table
Indexes are 25x larger than needed for hot data
Every VACUUM touches the entire table
How do you automate the lifecycle?
A scheduled job pre-creates future partitions and retires old ones. Tools like pg_partman handle the mechanics.
-- Using pg_partman for automated partition management
SELECT partman.create_parent(
p_parent_table := 'public.transactions',
p_control := 'created_at',
p_type := 'range',
p_interval := '1 month',
p_premake := 3 -- Pre-create 3 months ahead
);
-- pg_partman automatically:
-- 1. Creates next month's partition before it is needed
-- 2. Detaches partitions older than the retention period
-- 3. Optionally drops detached partitions after a grace period
package com.bank.maintenance;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import javax.sql.DataSource;
public class PartitionLifecycleManager {
private final DataSource dataSource;
private final int retentionMonths;
public PartitionLifecycleManager(DataSource dataSource, int retentionMonths) {
this.dataSource = dataSource;
this.retentionMonths = retentionMonths;
}
// Run monthly via cron or Spring @Scheduled
public void runMonthlyLifecycle() throws SQLException {
preCreateNextMonth();
retireOldPartitions();
}
private void preCreateNextMonth() throws SQLException {
LocalDate nextMonth = LocalDate.now().plusMonths(2).withDayOfMonth(1);
LocalDate monthAfter = nextMonth.plusMonths(1);
String partName = "txn_" + nextMonth.format(DateTimeFormatter.ofPattern("yyyy_MM"));
String sql = String.format(
"CREATE TABLE IF NOT EXISTS %s PARTITION OF transactions "
+ "FOR VALUES FROM ('%s') TO ('%s')",
partName, nextMonth, monthAfter);
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement()) {
stmt.execute(sql);
System.out.println("Pre-created partition: " + partName);
}
}
private void retireOldPartitions() throws SQLException {
LocalDate cutoff = LocalDate.now().minusMonths(retentionMonths).withDayOfMonth(1);
String partName = "txn_" + cutoff.format(DateTimeFormatter.ofPattern("yyyy_MM"));
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement()) {
stmt.execute("ALTER TABLE transactions DETACH PARTITION " + partName);
// Archive to S3 first, then drop
stmt.execute("DROP TABLE " + partName);
System.out.println("Retired partition: " + partName);
}
}
}
What are the rules for safe partition lifecycle?
Always verify the archive is durable before dropping. An untested backup is a hope, not a strategy.
Partition lifecycle rules:
1. Pre-create partitions BEFORE they are needed
2. Detach before archive (partition is no longer live)
3. Archive to durable storage (S3 with versioning)
4. Verify archive integrity BEFORE dropping local table
5. Monitor for failed archival or missing partitions
6. Keep a grace period between detach and drop
7. Test restore from archive periodically
8. Automate with pg_partman or equivalent tooling
What should a senior engineer know?
A senior engineer knows that partitioning converts the most expensive data-management operations into cheap metadata operations. The single biggest lifecycle win is dropping a whole partition instead of mass-deleting rows. This is what makes multi-year retention feasible at scale.
Comparison: DELETE vs DROP PARTITION
Operation | Mass DELETE | DROP PARTITION
Time | 4-8 hours | < 1 second
WAL generated | 500 GB | Minimal
Locks | Row-level, blocks ops | None
Dead tuples | 800 million | None
VACUUM needed | Yes (2-4 hours) | No
Live traffic | Severely impacted | Not impacted
Reversible | No (without backup) | No (archive first!)
Visual Overview
┌────────────────────────────────────────────────────────────┐ │ Partition Lifecycle Management │ ├────────────────────────────────────────────────────────────┤ │ │ │ Monthly partitions: │ │ ┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐ │ │ │2024-01 │ │2024-02 │ │2024-03 │ │2024-04 │ │2024-05 │ │ │ │ warm │ │ warm │ │ HOT │ │ HOT │ │(future)│ │ │ └────────┘ └────────┘ └────────┘ └────────┘ └────────┘ │ │ │ │ Lifecycle operations: │ │ │ │ Pre-create: 2024-05 created before April ends │ │ │ │ Archive: 2017-06 detached → pg_dump → S3 │ │ │ │ Drop: 2017-06 dropped after archive verified │ │ < 1 second, no WAL, no VACUUM │ │ │ │ vs Mass DELETE: │ │ ┌────────────────────────────────────────────────┐ │ │ │ DELETE FROM transactions WHERE date < cutoff │ │ │ │ 4-8 hours │ 500 GB WAL │ Blocks live traffic │ │ │ └────────────────────────────────────────────────┘ │ └────────────────────────────────────────────────────────────┘
Key Takeaway
Partitioning helps data lifecycle management by turning the most expensive data operations into cheap metadata operations. At scale, data has a lifecycle: hot when recent, then warm, then cold, and eventually it must be archived or deleted. The naive approach is a bulk DELETE of old rows, which on a billion-row table generates hundreds of gigabytes of WAL, holds locks for hours, leaves millions of dead tuples requiring VACUUM, and severely impacts live traffic.
Range partitioning by time changes the economics entirely. Each partition is a separate physical table holding one time window. Dropping a month is a DROP TABLE or DETACH PARTITION that removes a whole object instantly, with no row scan, no WAL, and no bloat. Archiving is detaching the old partition and dumping it to S3 so the hot table stays small while the cold data remains accessible where it is cheap. Because only recent partitions receive writes and most reads, the primary store holds a small working set with cache-resident indexes, so inserts and recent queries stay fast regardless of how much history exists.
Lifecycle automation runs as a scheduled job: pre-creating next month's partition before it is needed, and detaching, archiving, and dropping partitions older than the retention window. Tools like pg_partman handle the mechanics.
At the National Bank of India, the team had a seven-year retention requirement on a transactions table with billions of rows. Instead of a monthly bulk DELETE that would run for hours, Pankaj range-partitioned by month and ran a scheduler that each month pre-created the next partition and archived the one that had crossed seven years. Retention became an instant shelf operation, the hot store stayed small, and inserts and recent queries stayed fast.
Remember one sentence: with time-based partitions, archiving or deleting old data is dropping a whole partition: a metadata operation, not a costly mass DELETE.
---