Range Partitioning Growth Tables

---
name: range-partitioning-growth-tables
description: "Range Partitioning for Growth Tables"
metadata:
  short-description: "Range Partitioning for Growth Tables"
  original-index: 34
---

# Range Partitioning for Growth Tables

> Source: [PostgreSQL Best Practices](../DOCUMENTS/PostgreSQL_BestPractices_Azure.md)
> -- Backlog #4, 

## What

Splitting large, continuously-growing tables (audit logs, error logs,
appointment history) into **range-partitioned children** by date. This uses
PostgreSQL's native declarative partitioning (`PARTITION BY RANGE`) to keep
query performance stable as the table grows.

## Why

Tables like `AuditLog`, `ErrorLog`, and `AppointmentInfo` grow without
bound. Without partitioning:
- Sequential scans grow linearly with table size
- Index B-tree depth increases, slowing lookups
- VACUUM must process the entire table (hours on large tables)
- Archival requires scanning and deleting millions of rows

With range partitioning:
- Queries with a date filter benefit from **partition pruning** (only
  relevant partitions are scanned)
- VACUUM runs per-partition (smaller, faster)
- Archival = `DROP TABLE` on old partitions (instant, no bloat)
- New partitions can be created in advance (no schema change needed)

## How

### Create a partitioned parent table

```sql
-- New table (greenfield)
CREATE TABLE public."AuditLog" (
    "AuditLogId"    bigint GENERATED BY DEFAULT AS IDENTITY,
    "Action"        text NOT NULL,
    "CreatedAt"     timestamptz NOT NULL DEFAULT NOW(),
    "Payload"       jsonb
) PARTITION BY RANGE ("CreatedAt");
```

### Create partition children (monthly)

```sql
-- January 2025
CREATE TABLE public."AuditLog_2025_01" PARTITION OF public."AuditLog"
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- February 2025
CREATE TABLE public."AuditLog_2025_02" PARTITION OF public."AuditLog"
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- etc.
```

### Partition naming convention

```text
<parent_table>_<YYYY>_<MM>     -- monthly
<parent_table>_<YYYY>_Q<n>     -- quarterly
<parent_table>_<YYYY>          -- yearly

Examples:
  AuditLog_2025_01
  AuditLog_2025_Q1
  ErrorLog_2024
```

### Automate future partition creation with pg_cron

```sql
-- Create next month's partition on the 25th of each month
SELECT cron.schedule(
    'create_auditlog_partition',
    '0 3 25 * *',   -- 3:00 AM on the 25th
    $$
    DO $$
    DECLARE
        v_start date := date_trunc('month', NOW() + INTERVAL '1 month');
        v_end   date := v_start + INTERVAL '1 month';
        v_name  text := 'AuditLog_' || to_char(v_start, 'YYYY_MM');
    BEGIN
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS public.%I PARTITION OF public."AuditLog"
             FOR VALUES FROM (%L) TO (%L)',
            v_name, v_start, v_end
        );
        RAISE NOTICE 'Created partition %', v_name;
    END $$;
    $$
);
```

### Retroactively partition an existing table

This is a multi-step migration:

```text
1. Create new partitioned parent (e.g., AuditLog_new)
2. Create partition children for existing date ranges
3. INSERT INTO AuditLog_new SELECT * FROM AuditLog (batch by date)
4. Rename: AuditLog -> AuditLog_legacy, AuditLog_new -> AuditLog
5. Recreate indexes, FKs, and dependent views
6. Drop AuditLog_legacy after validation
```

This is an **offline migration** that requires downtime or a shadow-write
strategy. See Skill #04 (Idempotent Migrations) for safe scripting patterns.

### Query with partition pruning

```sql
-- This query only scans AuditLog_2025_01 (pruning in action)
SELECT *
FROM public."AuditLog"
WHERE "CreatedAt" >= '2025-01-01'
  AND "CreatedAt" <  '2025-02-01';

-- Verify pruning with EXPLAIN
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM public."AuditLog"
WHERE "CreatedAt" >= '2025-01-01'
  AND "CreatedAt" <  '2025-02-01';
-- Look for: "Partitions removed: N" or only one child in the plan
```

### Archive old partitions

```sql
-- Detach (keeps the data, removes from parent routing)
ALTER TABLE public."AuditLog"
    DETACH PARTITION public."AuditLog_2023_01";

-- Optionally move to archive schema
ALTER TABLE public."AuditLog_2023_01"
    SET SCHEMA archive;

-- Or drop entirely
DROP TABLE public."AuditLog_2023_01";
```

## Decision Matrix: When to Partition

| Signal | Partition? | Notes |
|--------|-----------|-------|
| Table > 10M rows and growing | Yes | Clear benefit |
| Table > 1M rows, date-filtered queries | Yes | Pruning helps |
| Table < 1M rows | No | Overhead not justified |
| No date column in WHERE clauses | No | Pruning cannot help |
| Need to archive/purge old data | Yes | DROP partition = instant |
| Heavily updated (not append-only) | Maybe | Updates across partitions are complex |

## When to Use

- Audit log tables that grow daily
- Error/event log tables
- Appointment history tables with date-range queries
- Any append-mostly table exceeding 10M rows

## Where We Used It

- ****: Partitioning analysis for AppointmentInfo, AuditLog, ErrorLog
  in the scheduling DB

## Related Skills

- **Skill #04** (Idempotent Migrations) -- scripting the multi-step migration
- **Skill #10** (Index CONCURRENTLY) -- indexes on partitioned children
- **Skill #16** (pg_cron Scheduling) -- automated partition creation
- **Skill #26** (timestamptz) -- partition key should be timestamptz
- **Skill #30** (Autovacuum & Bloat) -- per-partition VACUUM is faster

## References

- [PostgreSQL Best Practices](../DOCUMENTS/PostgreSQL_BestPractices_Azure.md)
  -- Backlog #4
- [scheduling Audit TDD](../DOCUMENTS/scheduling_DB_Audit_TDD.md)
  -- Growth table analysis

## Gotchas

- **Primary keys** on partitioned tables must include the partition key
  column -- `(AuditLogId, CreatedAt)` not just `(AuditLogId)`
- **Foreign keys** pointing TO a partitioned table are supported only in
  PostgreSQL 12+ and require the partition key in the referenced columns
- **FK NOT VALID limitation**: Foreign key constraints on partitioned
  tables may NOT be declared `NOT VALID` (PG 16 docs: sql-createtable.html).
  This means the standard NOT VALID + VALIDATE pattern (Skill #09) does
  not apply -- FK creation on a partitioned table scans ALL partitions.
- **Unique indexes** must include the partition key column
- If no partition exists for an inserted row's date, the INSERT **fails** --
  always create partitions in advance
- `CREATE INDEX` on the parent creates indexes on ALL children (PG 11+) --
  but `CONCURRENTLY` is not supported on partitioned parents.
  **Workaround** (PG 16 docs: ddl-partitioning.html):
  1. `CREATE INDEX ON ONLY parent_table (col)` -- marks parent index as invalid
  2. `CREATE INDEX CONCURRENTLY ON each_partition (col)` -- non-blocking per child
  3. `ALTER INDEX parent_idx ATTACH PARTITION child_idx` -- links them
  4. Once all children are attached, parent index becomes valid automatically
- Partition pruning requires `enable_partition_pruning = on` (default) and
  the WHERE clause must use the partition key directly (not wrapped in a
  function)
- Retroactive partitioning of an existing table requires data migration --
  there is no `ALTER TABLE ... PARTITION BY` command
  (PG 16 docs: ddl-partitioning.html)

---

*Category: DDL | Origin: *