Time Based Partitioning

CREATE TABLE payments (
    payment_id   bigint GENERATED ALWAYS AS IDENTITY,
    customer_id  int NOT NULL,
    amount       numeric(10,2) NOT NULL,
    payment_date date NOT NULL,
    CONSTRAINT payments_pk PRIMARY KEY (payment_id, payment_date)
) PARTITION BY RANGE (payment_date);

-- Create yearly partitions
CREATE TABLE payments_2023 PARTITION OF payments
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE payments_2024 PARTITION OF payments
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE payments_2025 PARTITION OF payments
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Catch anything that doesn't match a defined range
CREATE TABLE payments_default PARTITION OF payments DEFAULT;

List Partitioning

CREATE TABLE orders (
    order_id    bigint GENERATED ALWAYS AS IDENTITY,
    region      text NOT NULL,
    product_id  int NOT NULL,
    quantity    int NOT NULL,
    order_date  timestamp NOT NULL,
    CONSTRAINT orders_pk PRIMARY KEY (order_id, region)
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders
    FOR VALUES IN ('us-east', 'us-west', 'us-central');

CREATE TABLE orders_eu PARTITION OF orders
    FOR VALUES IN ('eu-west', 'eu-central', 'eu-north');

CREATE TABLE orders_apac PARTITION OF orders
    FOR VALUES IN ('apac-east', 'apac-south');

CREATE TABLE orders_default PARTITION OF orders DEFAULT;

Hash Partitioning

CREATE TABLE user_sessions (
    session_id  uuid NOT NULL,
    user_id     bigint NOT NULL,
    started_at  timestamp NOT NULL,
    data        jsonb,
    CONSTRAINT user_sessions_pk PRIMARY KEY (session_id, user_id)
) PARTITION BY HASH (user_id);

CREATE TABLE user_sessions_p0 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE user_sessions_p1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE user_sessions_p2 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE user_sessions_p3 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Quick Comparison

Changing Partition Boundaries

Partition Pruning and Query Performance

Pruning in Action

EXPLAIN (COSTS OFF)
SELECT * FROM payments
WHERE payment_date BETWEEN '2024-06-01' AND '2024-06-30';

Append
  ->  Seq Scan on payments_2024
        Filter: (payment_date >= '2024-06-01' AND payment_date <= '2024-06-30')

Without Pruning

EXPLAIN (COSTS OFF)
SELECT * FROM payments
WHERE customer_id = 42;

Append
  ->  Seq Scan on payments_2023
        Filter: (customer_id = 42)
  ->  Seq Scan on payments_2024
        Filter: (customer_id = 42)
  ->  Seq Scan on payments_2025
        Filter: (customer_id = 42)
  ->  Seq Scan on payments_default
        Filter: (customer_id = 42)

Verifying Pruning Settings

SHOW enable_partition_pruning;  -- should be 'on'

PREPARE payment_lookup (date, date) AS
SELECT * FROM payments WHERE payment_date BETWEEN $1 AND $2;

EXPLAIN (COSTS OFF) EXECUTE payment_lookup('2024-03-01', '2024-03-31');

Append
  ->  Seq Scan on payments_2024
        Filter: (payment_date >= '2024-03-01' AND payment_date <= '2024-03-31')

Tips for Effective Pruning

Partition Maintenance with pg_partman

Initial Setup

CREATE EXTENSION pg_partman;

Configuring Automated Partitioning

SELECT partman.create_parent(
    p_parent_table   => 'public.payments',
    p_control        => 'payment_date',
    p_interval       => '1 month',
    p_premake        => 3
);

Setting Up Retention

UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = false
WHERE parent_table = 'public.payments';

Running Maintenance

SELECT partman.run_maintenance();

SELECT cron.schedule(
    'partition-maintenance',
    '0 * * * *',  -- every hour
    $$SELECT partman.run_maintenance()$$
);

Complete IoT Example

-- Create the parent table
CREATE TABLE sensor_readings (
    reading_id   bigint GENERATED ALWAYS AS IDENTITY,
    sensor_id    int NOT NULL,
    reading_time timestamptz NOT NULL,
    temperature  numeric(5,2),
    humidity     numeric(5,2),
    CONSTRAINT sensor_readings_pk PRIMARY KEY (reading_id, reading_time)
) PARTITION BY RANGE (reading_time);

-- Let pg_partman manage it
SELECT partman.create_parent(
    p_parent_table   => 'public.sensor_readings',
    p_control        => 'reading_time',
    p_interval       => '1 day',
    p_premake        => 7
);

-- Configure 90-day retention
UPDATE partman.part_config
SET retention = '90 days',
    retention_keep_table = false
WHERE parent_table = 'public.sensor_readings';

-- Schedule maintenance
SELECT cron.schedule(
    'sensor-partition-maint',
    '15 * * * *',
    $$SELECT partman.run_maintenance()$$
);

Automatic Archiving with Partition Detach

-- Create an archive schema
CREATE SCHEMA IF NOT EXISTS archive;

-- Configure pg_partman to detach (not drop) expired partitions
UPDATE partman.part_config
SET retention = '6 months',
    retention_keep_table = true,    -- detach, don't drop
    retention_schema = 'archive'    -- move detached partitions here
WHERE parent_table = 'public.payments';

-- Query archived data directly
SELECT * FROM archive.payments_p2024_01
WHERE customer_id = 42;

-- Example: export archived partitions after detach
CREATE OR REPLACE FUNCTION archive_and_compress()
RETURNS void LANGUAGE plpgsql AS $$
DECLARE
    tbl text;
BEGIN
    -- Find tables in archive schema that haven't been compressed
    FOR tbl IN
        SELECT tablename FROM pg_tables
        WHERE schemaname = 'archive'
        AND tablename LIKE 'payments_p%'
        AND tablename NOT IN (
            SELECT tablename FROM pg_tables
            WHERE schemaname = 'archive_compressed'
        )
    LOOP
        -- Export via COPY
        EXECUTE format(
            'COPY (SELECT * FROM archive.%I) TO ''/archive/%s.csv'' WITH (FORMAT csv, HEADER)',
            tbl, tbl
        );
        -- Drop after successful export
        EXECUTE format('DROP TABLE archive.%I', tbl);
    END LOOP;
END;
$$;

-- Schedule weekly archival
SELECT cron.schedule(
    'archive-compress',
    '0 2 * * 0',  -- Sundays at 2am
    $$SELECT archive_and_compress()$$
);

Archiving to Iceberg with pg_lake

-- Create an Iceberg table for payment history
CREATE TABLE payments_history (
    payment_id   bigint,
    customer_id  int,
    amount       numeric(10,2),
    payment_date date
) USING iceberg;

-- Copy an old partition into Iceberg before dropping it
INSERT INTO payments_history
SELECT payment_id, customer_id, amount, payment_date
FROM payments_2023;

-- In Snowflake: query historical data written by pg_lake
SELECT customer_id, SUM(amount) as total_payments
FROM payments_history
WHERE payment_date BETWEEN '2020-01-01' AND '2023-12-31'
GROUP BY customer_id;

Snowflake Postgres supports native partitioning and pg_partman. For time-series data, pg_lake can move older partitions into Iceberg so Postgres stays lean and the history stays queryable from Snowflake.

When to Partition (and When Not To)

Partition When

Don't Partition When

Common Mistakes

Decision Framework

Conclusion

Related Resources

---

---

---