# 连接数据库
$ psql -d fgedudb -U fgedu
psql (9.4.26)
Type “help” for help.

fgedudb=>

# 创建按日期范围分区的表
fgedudb=> CREATE TABLE fgedu.fgedu_sales_partition (
fgedudb(> sale_id BIGINT,
fgedudb(> sale_date DATE,
fgedudb(> customer_id INT,
fgedudb(> product_id INT,
fgedudb(> quantity INT,
fgedudb(> amount NUMERIC(18,2)
fgedudb(> ) DISTRIBUTED BY (sale_id)
fgedudb-> PARTITION BY RANGE (sale_date)
fgedudb-> (
fgedudb(> PARTITION p202601 START (‘2026-01-01’) END (‘2026-02-01’),
fgedudb(> PARTITION p202602 START (‘2026-02-01’) END (‘2026-03-01’),
fgedudb(> PARTITION p202603 START (‘2026-03-01’) END (‘2026-04-01’),
fgedudb(> PARTITION p202604 START (‘2026-04-01’) END (‘2026-05-01’),
fgedudb(> DEFAULT PARTITION other
fgedudb(> );
CREATE TABLE

# 查看分区信息
fgedudb=> SELECT
fgedudb-> tablename,
fgedudb-> partitionname,
fgedudb-> partitiontype,
fgedudb-> partitionlevel
fgedudb-> FROM pg_partitions
fgedudb-> WHERE tablename = ‘fgedu_sales_partition’;
tablename | partitionname | partitiontype | partitionlevel
——————–+—————+—————+—————-
fgedu_sales_partition | p202601 | range | 0
fgedu_sales_partition | p202602 | range | 0
fgedu_sales_partition | p202603 | range | 0
fgedu_sales_partition | p202604 | range | 0
fgedu_sales_partition | other | range | 0
(5 rows)

学习交流加群风哥QQ113257174

# 添加新分区
fgedudb=> ALTER TABLE fgedu.fgedu_sales_partition
fgedudb-> ADD PARTITION p202605 START (‘2026-05-01’) END (‘2026-06-01’);
ALTER TABLE

# 删除分区
fgedudb=> ALTER TABLE fgedu.fgedu_sales_partition
fgedudb-> DROP PARTITION p202601;
ALTER TABLE

# 清空分区数据
fgedudb=> TRUNCATE TABLE fgedu.fgedu_sales_partition_1_prt_p202602;
TRUNCATE TABLE

# 分区裁剪测试
fgedudb=> EXPLAIN SELECT * FROM fgedu.fgedu_sales_partition
fgedudb-> WHERE sale_date BETWEEN ‘2026-03-01’ AND ‘2026-03-31’;
QUERY PLAN
———————————————————————————–
Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..100.00 rows=1000 width=100)
-> Seq Scan on fgedu_sales_partition_1_prt_p202603 (cost=0.00..100.00 rows=1000 width=100)
Filter: ((sale_date >= ‘2026-03-01’::date) AND (sale_date <= '2026-03-31'::date)) (3 rows) # 可以看到只扫描了p202603分区 更多学习教程公众号风哥教程itpux_com

# 创建按地区列表分区的表
fgedudb=> CREATE TABLE fgedu.fgedu_order_region (
fgedudb(> order_id BIGINT,
fgedudb(> order_date DATE,
fgedudb(> region VARCHAR(50),
fgedudb(> customer_id INT,
fgedudb(> amount NUMERIC(18,2)
fgedudb(> ) DISTRIBUTED BY (order_id)
fgedudb-> PARTITION BY LIST (region)
fgedudb-> (
fgedudb(> PARTITION p_beijing VALUES (‘北京’),
fgedudb(> PARTITION p_shanghai VALUES (‘上海’),
fgedudb(> PARTITION p_guangzhou VALUES (‘广州’),
fgedudb(> PARTITION p_shenzhen VALUES (‘深圳’),
fgedudb(> DEFAULT PARTITION p_other
fgedudb(> );
CREATE TABLE

# 插入测试数据
fgedudb=> INSERT INTO fgedu.fgedu_order_region VALUES
fgedudb-> (1, ‘2026-04-01’, ‘北京’, 1001, 1000.00),
fgedudb-> (2, ‘2026-04-01’, ‘上海’, 1002, 2000.00),
fgedudb-> (3, ‘2026-04-01’, ‘广州’, 1003, 3000.00),
fgedudb-> (4, ‘2026-04-01’, ‘深圳’, 1004, 4000.00);
INSERT 0 4

# 查询特定分区
fgedudb=> SELECT * FROM fgedu.fgedu_order_region WHERE region = ‘北京’;
order_id | order_date | region | customer_id | amount
———-+————+——–+————-+———
1 | 2026-04-01 | 北京 | 1001 | 1000.00
(1 row)

from GreenPlum视频:www.itpux.com

# 日志表分区案例

# 1. 创建按天分区的日志表
CREATE TABLE fgedu.fgedu_log_partition (
log_id BIGSERIAL,
log_time TIMESTAMP,
log_level VARCHAR(20),
log_module VARCHAR(100),
log_message TEXT,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) DISTRIBUTED BY (log_id)
PARTITION BY RANGE (log_time)
(
PARTITION p20260401 START (‘2026-04-01 00:00:00’) END (‘2026-04-02 00:00:00’),
PARTITION p20260402 START (‘2026-04-02 00:00:00’) END (‘2026-04-03 00:00:00’),
PARTITION p20260403 START (‘2026-04-03 00:00:00’) END (‘2026-04-04 00:00:00’),
DEFAULT PARTITION p_other
);

# 2. 自动添加分区脚本
$ cat > /GreenPlum/scripts/add_partition.sh << 'EOF' #!/bin/bash # add_partition.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn NEXT_DATE=$(date -d "+1 day" +%Y-%m-%d) NEXT_DATE_END=$(date -d "+2 day" +%Y-%m-%d) PARTITION_NAME="p$(date -d "+1 day" +%Y%m%d)" psql -d fgedudb -U fgedu << SQL ALTER TABLE fgedu.fgedu_log_partition ADD PARTITION ${PARTITION_NAME} START ('${NEXT_DATE} 00:00:00') END ('${NEXT_DATE_END} 00:00:00'); SQL EOF # 3. 配置定时任务（每天执行） $ crontab -e 0 0 * * * /GreenPlum/scripts/add_partition.sh # 4. 删除旧分区（保留90天） $ cat > /GreenPlum/scripts/drop_old_partition.sh << 'EOF' #!/bin/bash OLD_DATE=$(date -d "-90 day" +%Y%m%d) PARTITION_NAME="p${OLD_DATE}" psql -d fgedudb -U fgedu << SQL ALTER TABLE fgedu.fgedu_log_partition DROP PARTITION ${PARTITION_NAME}; SQL EOF # 5. 配置定时任务（每天执行） $ crontab -e 0 1 * * * /GreenPlum/scripts/drop_old_partition.sh

大表分区技巧：

1. 分区键选择
– 选择查询常用字段
– 选择数据分布均匀的字段
– 选择有明确范围的字段
– 避免选择更新频繁的字段

2. 分区粒度
– 根据数据量确定
– 日志表：按天分区
– 订单表：按月分区
– 历史表：按年分区

3. 分区维护
– 定期添加新分区
– 定期删除旧分区
– 监控分区数据量
– 优化分区性能

4. 性能优化
– 利用分区裁剪
– 建立合适的索引
– 更新统计信息
– 监控查询性能

5. 最佳实践
– 建立分区管理流程
– 自动化分区维护
– 监控分区性能
– 定期优化调整

本文档介绍了GreenPlum大表分区设计的核心内容，包括范围分区、列表分区、大表分区案例等，希望对大家有所帮助。