分区表

Halo 数据库支持 RANGE、LIST、HASH 三种分区方式，并支持多级组合分区。

RANGE 范围分区

按值的范围进行分区，适合按时间或数值范围组织数据。

CREATE TABLE test_range (
    city_id NUMERIC(38,0) NOT NULL,
    logdate DATE NOT NULL,
    peaktemp NUMERIC(38,0),
    unitsales NUMERIC(38,0)
) PARTITION BY RANGE (logdate);

-- 默认分区
CREATE TABLE test_range_default PARTITION OF test_range DEFAULT;

-- 按月创建分区
CREATE TABLE test_range_y2006m01 PARTITION OF test_range
    FOR VALUES FROM ('2006-01-01') TO ('2006-02-01');
CREATE TABLE test_range_y2006m02 PARTITION OF test_range
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE test_range_y2007m11 PARTITION OF test_range
    FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
CREATE TABLE test_range_y2007m12 PARTITION OF test_range
    FOR VALUES FROM ('2007-12-01') TO ('2008-01-01');

-- 创建索引
CREATE INDEX idx_test_range_1 ON test_range USING btree (logdate);

-- 插入数据
INSERT INTO test_range VALUES (1, '2006-02-07', 20, 30);
INSERT INTO test_range VALUES (2, '2006-03-07', 30, 40);
INSERT INTO test_range VALUES (3, '2007-11-07', 40, 50);

-- 分区裁剪查询
EXPLAIN ANALYZE SELECT * FROM test_range WHERE logdate = '2006-02-07';

HASH 分区

按哈希值均匀分布数据，适合数据量大且无明显范围特征的场景。支持单列和多列哈希。

CREATE TABLE test_hash (
    order_id NUMERIC(38,0) NOT NULL,
    cust_id NUMERIC(38,0) NOT NULL,
    status TEXT
) PARTITION BY HASH (order_id);

CREATE TABLE test_hash_01 PARTITION OF test_hash FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE test_hash_02 PARTITION OF test_hash FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE test_hash_03 PARTITION OF test_hash FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE test_hash_04 PARTITION OF test_hash FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 插入测试数据
INSERT INTO test_hash SELECT id, id, md5(id::text) FROM generate_series(1,200000) AS id;

EXPLAIN ANALYZE SELECT * FROM test_hash WHERE order_id = 12345;

LIST 列表分区

按离散值列表进行分区，适合按类别、地区等固定值组织数据。

CREATE TABLE test_list (
    id NUMERIC(38,0) NOT NULL,
    pname VARCHAR(30),
    sex VARCHAR(10) NOT NULL
) PARTITION BY LIST (sex);

CREATE TABLE test_list_default PARTITION OF test_list DEFAULT;
CREATE TABLE test_list_male PARTITION OF test_list FOR VALUES IN ('MAN', '男');
CREATE TABLE test_list_female PARTITION OF test_list FOR VALUES IN ('WOMAN', '女');

INSERT INTO test_list VALUES (1, '瑶瑶', '男');
INSERT INTO test_list VALUES (2, '倩倩', 'WOMAN');
INSERT INTO test_list VALUES (3, '优优', 'GOD');

EXPLAIN ANALYZE SELECT * FROM test_list WHERE sex = 'GOD';

多级组合分区

支持 LIST + RANGE + HASH 等多级嵌套分区。

CREATE TABLE test_lrh (
    id INTEGER,
    status TEXT,
    arr NUMERIC
) PARTITION BY LIST (status);

-- 第一级 LIST 分区
CREATE TABLE test_lrh_active PARTITION OF test_lrh
    FOR VALUES IN ('ACTIVE','RECURRING','REACTIVATED') PARTITION BY RANGE (arr);

-- 第二级 RANGE 分区
CREATE TABLE test_lrh_active_arr_small PARTITION OF test_lrh_active
    FOR VALUES FROM (MINVALUE) TO (101) PARTITION BY HASH (id);

-- 第三级 HASH 分区
CREATE TABLE test_lrh_active_arr_small01 PARTITION OF test_lrh_active_arr_small
    FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE test_lrh_active_arr_small02 PARTITION OF test_lrh_active_arr_small
    FOR VALUES WITH (MODULUS 2, REMAINDER 1);

-- DEFAULT 子分区
CREATE TABLE test_lrh_other PARTITION OF test_lrh DEFAULT PARTITION BY RANGE (arr);
CREATE TABLE test_lrh_other_arr_large PARTITION OF test_lrh_other
    FOR VALUES FROM (101) TO (MAXVALUE) PARTITION BY HASH (id);

INSERT INTO test_lrh VALUES
    (1,'ACTIVE',100), (2,'RECURRING',20), (3,'REACTIVATED',38),
    (4,'EXPIRED',144), (5,'EXPIRED',150);

EXPLAIN ANALYZE SELECT * FROM test_lrh WHERE id = 1 AND arr = 100;

分区维护

-- 分区下线（从主表分离为独立表）
ALTER TABLE test_lrh DETACH PARTITION test_lrh_other;

-- 分区上线（将独立表挂载回主表）
ALTER TABLE test_lrh ATTACH PARTITION test_lrh_other DEFAULT;

-- 分区改名
ALTER TABLE test_lrh_active RENAME TO test_lrh_status_active;