Partitioned Tables

Halo database supports RANGE, LIST, and HASH partitioning methods, and also supports multi-level composite partitioning.

RANGE Partitioning

Partitions data by value ranges, suitable for organizing data by time or numeric ranges.

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);

-- Default partition
CREATE TABLE test_range_default PARTITION OF test_range DEFAULT;

-- Create monthly partitions
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
CREATE INDEX idx_test_range_1 ON test_range USING btree (logdate);

-- Insert data
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);

-- Partition pruning query
EXPLAIN ANALYZE SELECT * FROM test_range WHERE logdate = '2006-02-07';

HASH Partitioning

Distributes data evenly by hash value, suitable for large datasets with no obvious range characteristics. Supports single-column and multi-column hashing.

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 test data
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 Partitioning

Partitions data by discrete value lists, suitable for organizing data by fixed values such as categories or regions.

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', 'Male');
CREATE TABLE test_list_female PARTITION OF test_list FOR VALUES IN ('WOMAN', 'Female');

INSERT INTO test_list VALUES (1, 'Yaoyao', 'Male');
INSERT INTO test_list VALUES (2, 'Qianqian', 'WOMAN');
INSERT INTO test_list VALUES (3, 'Youyou', 'GOD');

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

Multi-Level Composite Partitioning

Supports multi-level nested partitioning such as LIST + RANGE + HASH.

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

-- First-level LIST partition
CREATE TABLE test_lrh_active PARTITION OF test_lrh
    FOR VALUES IN ('ACTIVE','RECURRING','REACTIVATED') PARTITION BY RANGE (arr);

-- Second-level RANGE partition
CREATE TABLE test_lrh_active_arr_small PARTITION OF test_lrh_active
    FOR VALUES FROM (MINVALUE) TO (101) PARTITION BY HASH (id);

-- Third-level HASH partition
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 sub-partition
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;

Partition Maintenance

-- Detach partition (separates from main table as an independent table)
ALTER TABLE test_lrh DETACH PARTITION test_lrh_other;

-- Attach partition (mounts an independent table back to the main table)
ALTER TABLE test_lrh ATTACH PARTITION test_lrh_other DEFAULT;

-- Rename partition
ALTER TABLE test_lrh_active RENAME TO test_lrh_status_active;