普通表存储表实践
普通表存储表实践
目录
常见的greenplum磁盘空间占用过高的原因
Greenplum 大表占空间 + 有主键/唯一键，常见空间浪费来源主要是：
- MVCC 膨胀（dead tuples）
- 索引膨胀（尤其唯一索引）
- AO / AOCO 表压缩率低或未压缩
- 数据分布不合理（数据倾斜）
- 历史无效数据
- 列类型设计不合理（过宽）
- 小文件过多（append-only segment 文件膨胀）
如果你目标是 尽量释放大量磁盘空间，下面按“见效最快 → 结构性优化”排序。
常见的释放空间的方案
一、最快速释放空间VACUUM FULL / 表重写（最直接释放空间）
Greenplum 普通 VACUUM 不回收文件空间，只标记可复用。
要真正释放磁盘：
VACUUM FULL schema.table_name;
或者
ALTER TABLE schema.table_name SET WITH (reorganize=true);
作用
- 重写表文件
- 清理 dead tuple
- 索引一起重建
- 物理文件变小
空间释放量
👉 经常能释放 30%~80%
注意
- 会锁表
- 需要临时额外空间
- 大表执行时间长
重建索引（唯一索引通常最膨胀）
REINDEX TABLE schema.table_name;
或者
REINDEX INDEX index_name;
为什么唯一索引更容易膨胀？
- 更新频繁
- delete + insert 模式
- btree 分裂
释放量
👉 常见 20%~60%
二、AO / 列存储压缩（释放空间最猛）
如果你表是：
- heap 表
- appendonly 未压缩
- 压缩级别低
👉 这是最大优化空间
压缩率参考
| 数据类型 | 压缩率 |
|---|---|
| 普通业务表 | 3~5倍 |
| 日志类 | 5~10倍 |
| 数值为主 | 2~4倍 |
👉 通常能减少 70% 空间
这是 Greenplum 最强节省手段。
三、删除历史数据（立竿见影）
如果存在：
- 历史归档
- 已完成订单
- 旧日志
- 审计记录
建议：
DELETE FROM t WHERE create_time < '2023-01-01';
VACUUM FULL t;
更好的：
分区表直接 drop 分区
ALTER TABLE t DROP PARTITION p2022;
四、检查数据分布（严重倾斜会浪费磁盘）
五、列类型瘦身（长期收益）
典型浪费：
| 原类型 | 优化 |
|---|---|
| varchar(1000) | varchar(100) |
| bigint 实际很小 | int |
| text 存枚举 | smallint |
| char(n) | varchar |
六、检查是否有无用索引（很多系统都有）
👉 索引可能比表还大
SELECT
schemaname AS 模式,
relname AS 表名,
indexrelname AS 索引名,
idx_scan AS 扫描次数,
pg_size_pretty(pg_relation_size(indexrelid)) AS 索引大小
FROM pg_stat_user_indexes
WHERE idx_scan = 0 -- 从未被使用
ORDER BY pg_relation_size(indexrelid) DESC;
七、分区化（长期空间管理最佳）
大表建议：
- 按时间分区
- 按业务分区
优点：
- 删除历史 = drop partition
- VACUUM 更快
- 扫描更少
八、查询当前空间大户（必须先定位）
表大小：
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
索引大小：
表大小：
普通表转存储表案例
限制
Greenplum append-only 表：
- 不支持行级 update
- 不支持唯一性强校验
- 分布式环境难保证全局唯一
- 插入是并行写 segment 文件
所以官方限制：
不支持唯一索引
| 存储类型 | 是否支持唯一索引 |
| ----------- | -------- |
| heap | ✅ 支持 |
| AO row | ❌ 不支持 |
| AOCO column | ❌ 不支持 |
1.查看表当前的存储类型
SELECT relstorage
FROM pg_class
WHERE relname = 'ads_bom_gross_margin_b_ai_bk';
| 值 | 含义 |
|---|---|
| h | heap（普通表） |
| a | AO 行存 |
| c | AOCO 列存 |
2.创建一个新表（表类型为压缩表）
SELECT conname
FROM pg_constraint
WHERE conrelid = 'ads_bom_gross_margin_b_ai_bk'::regclass;
查看表上的约束，删除唯一索引和主键
ALTER TABLE table DROP CONSTRAINT pk_name;
创建压缩表
CREATE TABLE ads_bom_gross_margin_b_ai_bk_20240318_000_aoco
(LIKE ads_bom_gross_margin_b_ai_bk_20240318_000)
WITH (
appendonly = true,
orientation = column,
compresstype = zstd,
compresslevel = 5
);
3.数据迁移
INSERT INTO ads_bom_gross_margin_b_ai_bk_20240318_000_aoco
SELECT * FROM ads_bom_gross_margin_b_ai_bk_20240318_000;
-- 方式1：全量导入（数据量<1000万，低峰期执行）
INSERT /*+ APPEND */ INTO t_column
SELECT * FROM t_row; -- /*+ APPEND */ 提示：走批量写入，跳过WAL，提升速度
-- 方式2：分批导入（数据量>1000万，避免长事务）
DO $$
DECLARE
batch_size INT := 100000; -- 每批10万行（Greenplum可适当调大）
total_rows INT;
i INT := 0;
BEGIN
SELECT COUNT(*) INTO total_rows FROM t_row;
WHILE i * batch_size < total_rows LOOP
INSERT /*+ APPEND */ INTO t_column
SELECT * FROM t_row
ORDER BY id -- 按分布键排序，避免数据倾斜
LIMIT batch_size OFFSET i * batch_size;
COMMIT; -- 每批提交，释放资源
i := i + 1;
END LOOP;
END $$;
select count(*) from ads_bom_gross_margin_b_ai_bk_20240318_000_aoco;
select count(*) from ads_bom_gross_margin_b_ai_bk_20240318_000;
ALTER TABLE ads_bom_gross_margin_b_ai_bk_20240318_000 RENAME TO ads_bom_gross_margin_b_ai_bk_20240318_000_old;
ALTER TABLE ads_bom_gross_margin_b_ai_bk_20240318_000_aoco RENAME TO ads_bom_gross_margin_b_ai_bk_20240318_000;
4.对比两种模式的表的大小差异
SELECT
sotdoid,
sotdsize / 1024 / 1024 / 1024 AS sotdsizeGB,
sotdtoastsize,
sotdadditionalsize,
sotdschemaname,
sotdtablename
FROM
gp_toolkit.gp_size_of_table_disk
WHERE
sotdtablename IN ( 'ads_bom_gross_margin_b_ai_bk_20240318_000_aoco', 'ads_bom_gross_margin_b_ai_bk_20240318_000' )
ORDER BY
sotdsize DESC;
浙公网安备 33010602011771号