Partitioning Patterns — Time-Series, Multi-Tenant, and Archival

Partitioning is not one-size-fits-all. Different workloads call for different partition strategies, granularities, and maintenance patterns. This lesson covers the three most common real-world partitioning scenarios.

Pattern 1: Time-series with automated partition creation

For growing time-series tables, you need to create new partitions before data arrives. The standard approach is a function called by a cron job or application before each period:

-- Function to create the next month's partition automatically
CREATE OR REPLACE FUNCTION create_monthly_partition(
    parent_table TEXT,
    partition_date DATE
) RETURNS TEXT AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    partition_name := parent_table || '_' || TO_CHAR(partition_date, 'YYYY_MM');
    start_date := DATE_TRUNC('month', partition_date);
    end_date := start_date + INTERVAL '1 month';

    -- Create the partition if it doesn't exist
    IF NOT EXISTS (
        SELECT 1 FROM pg_class WHERE relname = partition_name
    ) THEN
        EXECUTE FORMAT(
            'CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
            partition_name, parent_table, start_date, end_date
        );
        -- Create GIN index on JSONB payload if the table has one
        EXECUTE FORMAT(
            'CREATE INDEX ON %I USING GIN (payload)',
            partition_name
        );
        RETURN 'Created: ' || partition_name;
    END IF;

    RETURN 'Already exists: ' || partition_name;
END;
$$ LANGUAGE plpgsql;

-- Create partitions for the next 3 months:
SELECT create_monthly_partition('events_partitioned', (CURRENT_DATE + (n || ' months')::INTERVAL)::DATE)
FROM generate_series(0, 2) n;

Pattern 2: Multi-tenant partitioning by LIST

For SaaS applications with many tenants (clients), LIST partitioning by tenant_id or region ensures each tenant's data is physically isolated:

CREATE TABLE tenant_events (
    event_id    BIGSERIAL,
    tenant_id   INT NOT NULL,
    event_type  TEXT NOT NULL,
    occurred_at TIMESTAMPTZ NOT NULL,
    payload     JSONB
) PARTITION BY LIST (tenant_id);

-- Enterprise tier: dedicated partitions per tenant
CREATE TABLE tenant_events_t1 PARTITION OF tenant_events FOR VALUES IN (1);
CREATE TABLE tenant_events_t2 PARTITION OF tenant_events FOR VALUES IN (2);
CREATE TABLE tenant_events_t3 PARTITION OF tenant_events FOR VALUES IN (3);

-- Standard tier: shared partition for smaller tenants
CREATE TABLE tenant_events_standard PARTITION OF tenant_events
    FOR VALUES IN (4, 5, 6, 7, 8, 9, 10);

-- Default: catch new tenants until explicitly partitioned
CREATE TABLE tenant_events_default PARTITION OF tenant_events DEFAULT;

-- Every query with WHERE tenant_id = N only scans one partition
-- This is both a performance and a data isolation guarantee

Pattern 3: Archival with DETACH and ATTACH

The most operationally powerful aspect of partitioning is instant data archival:

-- ❌
-- Step 1: Create an archive schema
CREATE SCHEMA archive;

-- Step 2: Detach old partition (makes it a regular table)
ALTER TABLE orders_partitioned DETACH PARTITION orders_2022;

-- Step 3: Move to archive schema
ALTER TABLE orders_2022 SET SCHEMA archive;

-- Step 4: Optionally compress (if using pg_compress or tablespace)
-- ALTER TABLE archive.orders_2022 SET TABLESPACE cold_storage;

-- Step 5: Make it read-only
REVOKE INSERT, UPDATE, DELETE ON archive.orders_2022 FROM PUBLIC;

-- The partitioned table now has one less partition (faster scans)
-- archive.orders_2022 still contains all data (accessible for historical queries)

-- Union view for combined access
CREATE VIEW all_orders_including_archive AS
    SELECT *, 'active' AS archive_status FROM orders_partitioned
    UNION ALL
    SELECT *, 'archived' FROM archive.orders_2022;

Pattern 4: Querying partition metadata

-- List all partitions of a table with their ranges and sizes
SELECT
    c.relname AS partition,
    pg_size_pretty(pg_relation_size(c.oid)) AS size,
    s.n_live_tup AS live_rows,
    s.n_dead_tup AS dead_rows,
    pg_get_expr(c.relpartbound, c.oid) AS partition_range
FROM pg_class p
JOIN pg_inherits i ON i.inhparent = p.oid
JOIN pg_class c ON c.oid = i.inhrelid
LEFT JOIN pg_stat_user_tables s ON s.relname = c.relname
WHERE p.relname = 'orders_partitioned'
ORDER BY c.relname;

-- Which partition would a specific value go to?
SELECT tableoid::regclass AS partition
FROM orders_partitioned
WHERE ctid = (
    SELECT min(ctid) FROM orders_partitioned
    WHERE created_at = '2025-06-15'
);

Key Takeaway

Time-series tables: RANGE partition by month, automate partition creation. Multi-tenant tables: LIST partition by tenant_id to isolate data and enable per-tenant maintenance. Archival: DETACH gives you instant data isolation and a standalone table you can move to cold storage. The consistent theme: partitioning is most valuable when your access patterns have a clear dominant dimension (time, tenant) that lets the planner prune 90%+ of partitions on typical queries.