PostgreSQL: PostgreSQL分区表与表继承

-- Parent table: partition key only
CREATE TABLE orders (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id     BIGINT NOT NULL,
    order_date  DATE NOT NULL,
    total_amount NUMERIC(12,2),
    status      TEXT DEFAULT 'pending'
) PARTITION BY RANGE (order_date);

-- Monthly partitions for 2024
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE orders_2024_03 PARTITION OF orders
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- Default partition catches out-of-range rows
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

CREATE TABLE

-- Generate monthly partitions for a given year
CREATE OR REPLACE FUNCTION create_monthly_partitions(
    p_parent  REGCLASS,
    p_year    INT
) RETURNS VOID AS $$
DECLARE
    m      INT;
    p_name TEXT;
    s_date TEXT;
    e_date TEXT;
BEGIN
    FOR m IN 1..12 LOOP
        p_name := format('%s_%s_%02s', p_parent::text, p_year, m);
        s_date := format('%s-%02s-01', p_year, m);
        e_date := format('%s-%02s-01', p_year, m + 1);
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %I PARTITION OF %s
             FOR VALUES FROM (%L) TO (%L)',
            p_name, p_parent::text, s_date, e_date
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT create_monthly_partitions('orders', 2025);

CREATE TABLE

INSERT INTO orders (user_id, order_date, total_amount, status)
VALUES
    (1001, '2024-01-15', 299.99, 'completed'),
    (1002, '2024-02-20', 159.50, 'shipped'),
    (1003, '2024-03-10', 89.00,  'pending');

-- Query with partition pruning
SELECT * FROM orders
WHERE order_date BETWEEN '2024-02-01' AND '2024-02-29';

  id  | user_id | order_date | total_amount | status
------+---------+------------+--------------+--------
 1002 |    1002 | 2024-02-20 |       159.50 | shipped
(1 row)

CREATE TABLE users_by_region (
    id       BIGINT GENERATED ALWAYS AS IDENTITY,
    name     TEXT NOT NULL,
    region   TEXT NOT NULL,
    email    TEXT
) PARTITION BY LIST (region);

CREATE TABLE users_north_america PARTITION OF users_by_region
    FOR VALUES IN ('US', 'CA', 'MX');

CREATE TABLE users_europe PARTITION OF users_by_region
    FOR VALUES IN ('UK', 'DE', 'FR', 'ES');

CREATE TABLE users_asia PARTITION OF users_by_region
    FOR VALUES IN ('CN', 'JP', 'KR', 'SG');

CREATE TABLE users_other PARTITION OF users_by_region DEFAULT;

CREATE TABLE

CREATE TABLE access_logs (
    id         BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id    BIGINT,
    action     TEXT,
    log_time   TIMESTAMPTZ DEFAULT now()
) PARTITION BY HASH (user_id);

-- Create 8 hash partitions
CREATE TABLE access_logs_p0 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE access_logs_p1 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 1);
CREATE TABLE access_logs_p2 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 2);
CREATE TABLE access_logs_p3 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 3);
CREATE TABLE access_logs_p4 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 4);
CREATE TABLE access_logs_p5 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 5);
CREATE TABLE access_logs_p6 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 6);
CREATE TABLE access_logs_p7 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 7);

CREATE TABLE

CREATE TABLE order_details (
    id          BIGINT,
    order_id    BIGINT,
    order_date  DATE NOT NULL,
    region      TEXT NOT NULL,
    product_id  INT,
    quantity    INT
) PARTITION BY RANGE (order_date);

-- First level: by month
CREATE TABLE order_details_2024q1 PARTITION OF order_details
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01')
    PARTITION BY LIST (region);

-- Second level: by region within each month range
CREATE TABLE order_details_2024q1_na PARTITION OF order_details_2024q1
    FOR VALUES IN ('US', 'CA', 'MX');

CREATE TABLE order_details_2024q1_eu PARTITION OF order_details_2024q1
    FOR VALUES IN ('UK', 'DE', 'FR');

CREATE TABLE

-- Enable partition pruning (default ON)
SET enable_partition_pruning = on;

-- Check which partitions are scanned
EXPLAIN (COSTS OFF) SELECT * FROM orders
WHERE order_date = '2024-02-15';

Append
  -> Seq Scan on orders_2024_02
       Filter: (order_date = '2024-02-15'::date)

-- Detach January 2023 partition (no data loss)
ALTER TABLE orders DETACH PARTITION orders_2023_01;

-- Now it's a standalone table, can be moved to cheaper storage
ALTER TABLE orders_2023_01 SET TABLESPACE archive_tbs;

-- Or export and drop
COPY orders_2023_01 TO '/archive/orders_2023_01.csv';
DROP TABLE orders_2023_01;

-- SQL 语句执行成功

-- Create new table first
CREATE TABLE orders_2025_01 (LIKE orders INCLUDING DEFAULTS);

-- Add check constraint for validation (speeds up ATTACH)
ALTER TABLE orders_2025_01
    ADD CONSTRAINT orders_2025_01_check
    CHECK (order_date >= '2025-01-01' AND order_date < '2025-02-01');

-- Attach to parent (takes exclusive lock briefly)
ALTER TABLE orders ATTACH PARTITION orders_2025_01
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE

-- DETACH without blocking concurrent reads
ALTER TABLE orders DETACH PARTITION orders_2023_01 CONCURRENTLY;

-- SQL 语句执行成功

-- Index on parent propagates to all partitions
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Each partition gets its own index
\d orders_2024_01

Indexes:
    "orders_2024_01_user_id_idx" btree (user_id)

-- This FAILS: unique without partition key
CREATE UNIQUE INDEX idx_orders_id ON orders (id);
-- ERROR: unique constraint must contain partition key

-- This WORKS: unique with partition key
CREATE UNIQUE INDEX idx_orders_id_date ON orders (id, order_date);

CREATE TABLE

-- Parent table
CREATE TABLE people (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT
);

-- Child table inherits all columns + adds extras
CREATE TABLE employees (
    salary    NUMERIC(10,2),
    dept      TEXT,
    hire_date DATE
) INHERITS (people);

-- Add primary key to child
ALTER TABLE employees ADD PRIMARY KEY (id);

-- Query parent + all children
SELECT * FROM people;

-- Query parent ONLY (no children)
SELECT * FROM ONLY people;

-- Check which table each row comes from
SELECT tableoid::regclass, * FROM people;

-- Analyze specific partition
ANALYZE orders_2024_02;

-- Analyze all partitions via parent
ANALYZE orders;

-- Check partition stats
SELECT relname, n_live_tup, last_analyze
FROM pg_stat_user_tables
WHERE relname LIKE 'orders_%'
ORDER BY relname;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Partition-wise join (PG 12+)
SET enable_partitionwise_join = on;

SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Base audit table
CREATE TABLE audit_log (
    id         BIGINT GENERATED ALWAYS AS IDENTITY,
    table_name TEXT NOT NULL,
    action     TEXT NOT NULL,
    changed_at TIMESTAMPTZ DEFAULT now()
) PARTITION BY RANGE (changed_at);

-- Monthly partitions, each inherited by type-specific tables
CREATE TABLE audit_log_2024_01 PARTITION OF audit_log
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Additional columns for specific audit type via inheritance
CREATE TABLE audit_log_user_changes (
    old_email TEXT,
    new_email TEXT
) INHERITS (audit_log_2024_01);

CREATE TABLE

-- Complete monthly partitioning setup for e-commerce orders
CREATE TABLE orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id      BIGINT NOT NULL,
    order_date   DATE NOT NULL,
    total_amount NUMERIC(12,2) DEFAULT 0,
    status       TEXT DEFAULT 'pending',
    created_at   TIMESTAMPTZ DEFAULT now()
) PARTITION BY RANGE (order_date);

-- Create partitions for 2024 Q1
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE orders_2024_03 PARTITION OF orders
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- Unique index must include partition key
CREATE UNIQUE INDEX idx_orders_id_date ON orders (id, order_date);
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

-- Insert test data
INSERT INTO orders (user_id, order_date, total_amount, status) VALUES
    (1001, '2024-01-05', 299.99, 'completed'),
    (1001, '2024-02-14', 159.50, 'shipped'),
    (1002, '2024-01-20', 450.00, 'completed'),
    (1002, '2024-03-01', 89.00,  'pending'),
    (1003, '2024-02-28', 1200.00,'completed');

-- Verify partition pruning
EXPLAIN (COSTS OFF) SELECT * FROM orders
    WHERE order_date BETWEEN '2024-02-01' AND '2024-02-28';

-- Detach old partition for archiving
ALTER TABLE orders DETACH PARTITION orders_2024_01;
COPY orders_2024_01 TO '/archive/orders_2024_01.csv';

-- Attach new partition for next quarter
CREATE TABLE orders_2024_04 (LIKE orders INCLUDING DEFAULTS);
ALTER TABLE orders_2024_04
    ADD CONSTRAINT chk_2024_04
    CHECK (order_date >= '2024-04-01' AND order_date < '2024-05-01');
ALTER TABLE orders ATTACH PARTITION orders_2024_04
    FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

-- Monitor partition sizes
SELECT relname,
       pg_size_pretty(pg_total_relation_size(oid)) AS size,
       reltuples::bigint AS row_estimate
FROM pg_class
WHERE relname LIKE 'orders_2024%' ORDER BY relname;