-- 典型的時間范圍分區(qū)設計示例
CREATE TABLE sensor_data (
    device_id BIGINT NOT NULL,
    record_time TIMESTAMPTZ NOT NULL,
    temperature NUMERIC(5,2),
    humidity NUMERIC(5,2)
PARTITION BY RANGE (record_time);
CREATE TABLE sensor_data_2023 PARTITION OF sensor_data
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE sensor_data_2024 PARTITION OF sensor_data
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- 使用復合分區(qū)鍵的示例（PG14+）
CREATE TABLE customer_orders (
    region VARCHAR(20) NOT NULL,
    order_date DATE NOT NULL,
    amount NUMERIC(10,2)
PARTITION BY LIST (region), RANGE (order_date);
-- 創(chuàng)建子分區(qū)
CREATE TABLE orders_asia_2023 PARTITION OF customer_orders
    FOR VALUES IN ('asia') 
    PARTITION BY RANGE (order_date);

-- 分區(qū)維護操作示例
-- 歸檔舊分區(qū)
ALTER TABLE sensor_data DETACH PARTITION sensor_data_2022;
-- 合并分區(qū)（PG12+）
ALTER TABLE sensor_data 
    MERGE PARTITIONS sensor_data_202301, sensor_data_202302 
    INTO sensor_data_2023_q1;

-- 每小時自動分區(qū)創(chuàng)建（使用pg_partman）
SELECT partman.create_parent(
    'public.sensor_logs',
    'log_time',
    'native',
    'hourly',
    p_premake := 24
);

-- 多級列表分區(qū)設計
CREATE TABLE sales (
    region VARCHAR(20),
    country VARCHAR(20),
    sale_date DATE,
    amount NUMERIC
) PARTITION BY LIST (region);
CREATE TABLE sales_europe PARTITION OF sales
    FOR VALUES IN ('western_europe', 'eastern_europe')
    PARTITION BY LIST (country);

-- 哈希分區(qū)示例（PG14+支持自定義模數(shù)）
CREATE TABLE user_sessions (
    user_id BIGINT,
    session_data JSONB
) PARTITION BY HASH (user_id) 
WITH (MODULUS 4, REMAINDER 0); 
CREATE TABLE user_sessions_1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

-- 查看分區(qū)修剪效果（PG16新增partition pruning提示）
EXPLAIN (ANALYZE)
SELECT * FROM sensor_data 
WHERE record_time BETWEEN '2024-03-01' AND '2024-03-02';
-- 輸出結果關鍵片段
Append  (cost=0.00..48.95 rows=12 width=48)
  ->  Seq Scan on sensor_data_20240301  (cost=0.00..24.12 rows=6 width=48)
  ->  Seq Scan on sensor_data_20240302  (cost=0.00..24.12 rows=6 width=48)

-- 設置并行度（PG16支持分區(qū)級并行度控制）
ALTER TABLE sensor_data 
    SET (parallel_workers = 8);
-- 查看并行執(zhí)行計劃
EXPLAIN (ANALYZE)
SELECT AVG(temperature) FROM sensor_data 
WHERE record_time > now() - interval '1 week';

-- 全局索引示例（PG11+自動創(chuàng)建子分區(qū)索引）
CREATE INDEX idx_record_time ON sensor_data (record_time);
-- 分區(qū)本地索引優(yōu)化
CREATE INDEX idx_asia_2024_sales ON sales_asia_2024 (product_id) 
WHERE quantity > 1000;

-- 配置分區(qū)自動清理參數(shù)
ALTER TABLE sensor_data_2024 SET (
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_scale_factor = 0.02
);
-- 手動收集統(tǒng)計信息（PG14+支持子分區(qū)并行分析）
ANALYZE VERBOSE sensor_data;

-- 創(chuàng)建繼承層次（經(jīng)典案例：設備類型繼承）
CREATE TABLE devices (
    id SERIAL PRIMARY KEY,
    name TEXT,
    created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE sensors (
    accuracy DECIMAL(5,2)
) INHERITS (devices);
CREATE TABLE actuators (
    max_force NUMERIC
) INHERITS (devices);

-- 事件日志多態(tài)模型
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    target_type VARCHAR(32),
    target_id BIGINT,
    event_time TIMESTAMPTZ
);
CREATE TABLE temperature_events (
    sensor_id BIGINT REFERENCES sensors(id),
    temperature NUMERIC(5,2)
) INHERITS (events);
-- 查詢所有設備事件（自動包含子表數(shù)據(jù)）
SELECT e.* FROM events e WHERE target_type = 'sensor';

CREATE TABLE contracts_v1 (LIKE contracts);
CREATE TABLE contracts_v2 (payment_terms TEXT) INHERITS (contracts_v1);

CREATE TABLE tenant_a.orders () INHERITS (public.orders);
GRANT SELECT ON tenant_a.orders TO role_a;

CREATE RULE archive_orders AS 
ON INSERT TO orders WHERE order_date < '2020-01-01'
DO INSTEAD INSERT INTO orders_archive VALUES (NEW.*);

SELECT * FROM ONLY devices; -- 不包含子表數(shù)據(jù)

SET constraint_exclusion = on;
EXPLAIN SELECT * FROM devices WHERE id BETWEEN 1000 AND 2000;

-- 啟用異步修剪（新增參數(shù)）
SET enable_async_partition_pruning = on;
-- 監(jiān)控修剪進度
SELECT * FROM pg_stat_async_partition_pruning;

GRANT SELECT ON TABLE sales_2024 TO analyst_role;
REVOKE DELETE ON TABLE sales_archive FROM api_user;

CREATE TABLE genomic_data (
    lab_id INT,
    sample_date DATE,
    dna_data BYTEA
PARTITION BY LIST (lab_id), HASH (sample_date);
CREATE TABLE lab_nyc PARTITION OF genomic_data
    FOR VALUES IN (1)
    PARTITION BY HASH (sample_date);