┌─────────────────────────────────────────────────────────────┐
│                      分区表示例                               │
├─────────────────────────────────────────────────────────────┤
│                      orders (父表)                          │
│                          │                                  │
│        ┌─────────────────┼─────────────────┐                │
│        ▼                 ▼                 ▼                │
│   orders_2024_q1    orders_2024_q2    orders_2024_q3        │
│    (1-3月)            (4-6月)           (7-9月)             │
└─────────────────────────────────────────────────────────────┘

-- 创建分区表
CREATE TABLE orders (
    id SERIAL,
    user_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10, 2),
    status VARCHAR(20)
) PARTITION BY RANGE (order_date);

-- 创建按月分区的子表
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_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

-- 按地区分区
CREATE TABLE sales (
    id SERIAL,
    region VARCHAR(50),
    amount DECIMAL(10, 2),
    sale_date DATE
) PARTITION BY LIST (region);

CREATE TABLE sales_east PARTITION OF sales
    FOR VALUES IN ('北京', '天津', '河北');

CREATE TABLE sales_west PARTITION OF sales
    FOR VALUES IN ('新疆', '西藏', '青海', '甘肃');

CREATE TABLE sales_other PARTITION OF sales
    FOR VALUES IN (DEFAULT);

-- 按用户 ID 哈希分区
CREATE TABLE user_events (
    id SERIAL,
    user_id INTEGER,
    event_type VARCHAR(50),
    event_data JSONB,
    created_at TIMESTAMPTZ
) PARTITION BY HASH (user_id);

CREATE TABLE user_events_p0 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE user_events_p1 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE user_events_p2 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE user_events_p3 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 创建分区管理函数
CREATE OR REPLACE FUNCTION create_monthly_partition(
    table_name TEXT,
    partition_date DATE
)
RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    start_date := DATE_TRUNC('month', partition_date);
    end_date := start_date + INTERVAL '1 month';
    partition_name := table_name || '_' || TO_CHAR(start_date, 'YYYY_MM');
    
    -- 检查分区是否存在
    IF NOT EXISTS (
        SELECT 1 FROM pg_tables 
        WHERE schemaname = 'public' 
        AND tablename = partition_name
    ) THEN
        EXECUTE format(
            'CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
            partition_name, table_name, start_date, end_date
        );
        RAISE NOTICE '创建分区: %', partition_name;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 创建未来 3 个月的分区
SELECT create_monthly_partition('orders', CURRENT_DATE);
SELECT create_monthly_partition('orders', CURRENT_DATE + INTERVAL '1 month');
SELECT create_monthly_partition('orders', CURRENT_DATE + INTERVAL '2 months');

-- 安装 pg_partman 扩展
CREATE EXTENSION pg_partman;

-- 配置自动分区
SELECT partman.create_parent(
    p_parent_table => 'public.orders',
    p_control => 'order_date',
    p_interval => 'monthly',
    p_premake => 3
);

-- 配置自动维护
UPDATE partman.part_config 
SET infinite_time_partitions = true 
WHERE parent_table = 'public.orders';

-- 直接插入，PostgreSQL 自动路由到正确分区
INSERT INTO orders (user_id, order_date, total, status)
VALUES (1, '2024-01-15', 199.99, 'completed');

INSERT INTO orders (user_id, order_date, total, status)
VALUES 
    (1, '2024-02-20', 299.99, 'pending'),
    (2, '2024-03-10', 149.99, 'completed');

-- 从分区表插入
INSERT INTO orders 
SELECT * FROM orders_archive 
WHERE order_date >= '2024-01-01';

-- 查询自动利用分区裁剪
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

-- 查看查询计划（验证分区裁剪）
EXPLAIN SELECT * FROM orders WHERE order_date = '2024-01-15';

-- 查看分区信息
SELECT 
    parent.relname AS parent_table,
    child.relname AS partition,
    pg_get_expr(child.relpartbound, child.oid) AS partition_range
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid;

-- 分离分区
ALTER TABLE orders DETACH PARTITION orders_2024_q1;

-- 将分区转为独立表
ALTER TABLE orders_2024_q1 SET WITHOUT OIDS;
ALTER TABLE orders_2024_q1 SET TABLESPACE fast_storage;

-- 附加分区
ALTER TABLE orders ATTACH PARTITION orders_archive_2023
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- 删除旧分区
DROP TABLE orders_2022_q1;

-- 重命名分区
ALTER TABLE orders_2024_01 RENAME TO orders_jan_2024;

-- 在分区表上创建索引（自动应用到所有分区）
CREATE INDEX idx_orders_date ON orders (order_date);
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

-- 在特定分区上创建索引
CREATE INDEX idx_orders_2024_user ON orders_2024_01 (user_id);

-- 部分索引（分区特定）
CREATE INDEX idx_orders_pending ON orders_2024_01 (created_at) 
WHERE status = 'pending';

-- 分区表上的唯一约束必须包含分区键
CREATE TABLE orders (
    id SERIAL,
    order_no VARCHAR(50),
    user_id INTEGER,
    order_date DATE
) PARTITION BY RANGE (order_date);

-- 正确：包含分区键
CREATE UNIQUE INDEX idx_orders_no_date ON orders (order_no, order_date);

-- 错误：缺少分区键
-- CREATE UNIQUE INDEX idx_orders_no ON orders (order_no); -- 会报错

-- 使用分区键过滤
EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2024-02-01';

-- 跨分区查询
EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2024-07-01';

-- 聚合查询（利用分区并行）
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- 启用分区裁剪
SET enable_partition_pruning = on;

-- 查看裁剪信息
EXPLAIN SELECT * FROM orders WHERE order_date = '2024-03-15';

-- Output 应包含:
-- -> Seq Scan on orders_2024_03 orders  (cost=...)

-- 1. 重命名原表
ALTER TABLE orders RENAME TO orders_old;

-- 2. 创建分区表
CREATE TABLE orders (
    id SERIAL,
    user_id INTEGER,
    order_date DATE,
    total DECIMAL(10, 2)
) PARTITION BY RANGE (order_date);

-- 3. 创建分区
CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- 4. 迁移数据
INSERT INTO orders SELECT * FROM orders_old;

-- 5. 删除旧表
DROP TABLE orders_old;

-- 将历史分区分离并归档
ALTER TABLE orders DETACH PARTITION orders_2023;

-- 可选：将分区移到归档表空间
ALTER TABLE orders_2023 SET TABLESPACE archive_tablespace;

-- 如果需要，重新附加
ALTER TABLE orders ATTACH PARTITION orders_2023
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');