fgedudb=> CREATE TABLE fgedu.fgedu_test_ao (
id INT,
col1 VARCHAR(100),
col2 NUMERIC(18,2),
col3 TIMESTAMP,
col4 TEXT
) WITH (
appendonly = true,
compresstype = zlib,
compresslevel = 5
) DISTRIBUTED BY (id);
CREATE TABLE

fgedudb=> CREATE TABLE fgedu.fgedu_test_aoco (
id INT,
col1 VARCHAR(100),
col2 NUMERIC(18,2),
col3 TIMESTAMP,
col4 TEXT
) WITH (
appendonly = true,
orientation = column,
compresstype = zstd,
compresslevel = 6
) DISTRIBUTED BY (id);
CREATE TABLE

# 插入测试数据（100万条）
fgedudb=> INSERT INTO fgedu.fgedu_test_heap
SELECT
generate_series(1, 1000000),
‘value_’ || generate_series(1, 1000000),
(random() * 10000)::NUMERIC(18,2),
NOW() – (random() * 365 || ‘ days’)::interval,
repeat(‘test_text_’, 10);
INSERT 0 1000000

# 复制数据到其他表
fgedudb=> INSERT INTO fgedu.fgedu_test_ao SELECT * FROM fgedu.fgedu_test_heap;
INSERT 0 1000000

fgedudb=> INSERT INTO fgedu.fgedu_test_aoco SELECT * FROM fgedu.fgedu_test_heap;
INSERT 0 1000000

table_type | size
————+———–
Heap | 185 MB
AO | 85 MB
AOCO | 58 MB
(3 rows)

# 插入性能测试
fgedudb=> \timing on
Timing is on.

# 堆表插入
fgedudb=> INSERT INTO fgedu.fgedu_test_heap
SELECT
generate_series(1000001, 1100000),
‘value_’ || generate_series(1000001, 1100000),
(random() * 10000)::NUMERIC(18,2),
NOW() – (random() * 365 || ‘ days’)::interval,
repeat(‘test_text_’, 10);
INSERT 0 100000
Time: 2345.678 ms

# AO表插入
fgedudb=> INSERT INTO fgedu.fgedu_test_ao
SELECT
generate_series(1000001, 1100000),
‘value_’ || generate_series(1000001, 1100000),
(random() * 10000)::NUMERIC(18,2),
NOW() – (random() * 365 || ‘ days’)::interval,
repeat(‘test_text_’, 10);
INSERT 0 100000
Time: 1567.890 ms

# AOCO表插入
fgedudb=> INSERT INTO fgedu.fgedu_test_aoco
SELECT
generate_series(1000001, 1100000),
‘value_’ || generate_series(1000001, 1100000),
(random() * 10000)::NUMERIC(18,2),
NOW() – (random() * 365 || ‘ days’)::interval,
repeat(‘test_text_’, 10);
INSERT 0 100000
Time: 1890.123 ms

# 全列查询性能测试
fgedudb=> SELECT * FROM fgedu.fgedu_test_heap LIMIT 10000;
Time: 123.456 ms

fgedudb=> SELECT * FROM fgedu.fgedu_test_ao LIMIT 10000;
Time: 89.012 ms

fgedudb=> SELECT * FROM fgedu.fgedu_test_aoco LIMIT 10000;
Time: 145.678 ms

# 单列查询性能测试
fgedudb=> SELECT COUNT(DISTINCT col1) FROM fgedu.fgedu_test_heap;
Time: 456.789 ms

fgedudb=> SELECT COUNT(DISTINCT col1) FROM fgedu.fgedu_test_ao;
Time: 345.678 ms

fgedudb=> SELECT COUNT(DISTINCT col1) FROM fgedu.fgedu_test_aoco;
Time: 156.789 ms

# 聚合查询性能测试
fgedudb=> SELECT
date_trunc(‘month’, col3) AS month,
COUNT(*),
SUM(col2)
FROM fgedu.fgedu_test_heap
GROUP BY month
ORDER BY month;
Time: 234.567 ms

fgedudb=> SELECT
date_trunc(‘month’, col3) AS month,
COUNT(*),
SUM(col2)
FROM fgedu.fgedu_test_ao
GROUP BY month
ORDER BY month;
Time: 178.901 ms

fgedudb=> SELECT
date_trunc(‘month’, col3) AS month,
COUNT(*),
SUM(col2)
FROM fgedu.fgedu_test_aoco
GROUP BY month
ORDER BY month;
Time: 89.012 ms

存储效率：
– AOCO表：最高（58MB）
– AO表：中等（85MB）
– 堆表：最低（185MB）

插入性能：
– AO表：最快（1567ms）
– AOCO表：中等（1890ms）
– 堆表：最慢（2345ms）

全列查询性能：
– AO表：最快（89ms）
– 堆表：中等（123ms）
– AOCO表：最慢（145ms）

单列查询性能：
– AOCO表：最快（156ms）
– AO表：中等（345ms）
– 堆表：最慢（456ms）

聚合查询性能：
– AOCO表：最快（89ms）
– AO表：中等（178ms）
– 堆表：最慢（234ms）

结论：
– OLAP场景推荐使用AOCO表
– 需要更新的表使用堆表
– 维度表使用复制表
– 根据业务场景选择合适的存储模型

fgedudb=> CREATE TABLE fgedu.fgedu_compress_zlib3 (
id INT,
data VARCHAR(1000)
) WITH (appendonly = true, compresstype = zlib, compresslevel = 3) DISTRIBUTED BY (id);
CREATE TABLE

fgedudb=> CREATE TABLE fgedu.fgedu_compress_zlib6 (
id INT,
data VARCHAR(1000)
) WITH (appendonly = true, compresstype = zlib, compresslevel = 6) DISTRIBUTED BY (id);
CREATE TABLE

fgedudb=> CREATE TABLE fgedu.fgedu_compress_zlib9 (
id INT,
data VARCHAR(1000)
) WITH (appendonly = true, compresstype = zlib, compresslevel = 9) DISTRIBUTED BY (id);
CREATE TABLE

fgedudb=> CREATE TABLE fgedu.fgedu_compress_zstd3 (
id INT,
data VARCHAR(1000)
) WITH (appendonly = true, compresstype = zstd, compresslevel = 3) DISTRIBUTED BY (id);
CREATE TABLE

fgedudb=> CREATE TABLE fgedu.fgedu_compress_zstd6 (
id INT,
data VARCHAR(1000)
) WITH (appendonly = true, compresstype = zstd, compresslevel = 6) DISTRIBUTED BY (id);
CREATE TABLE

fgedudb=> CREATE TABLE fgedu.fgedu_compress_zstd9 (
id INT,
data VARCHAR(1000)
) WITH (appendonly = true, compresstype = zstd, compresslevel = 9) DISTRIBUTED BY (id);
CREATE TABLE

# 插入测试数据（包含重复数据）
fgedudb=> INSERT INTO fgedu.fgedu_compress_none
SELECT
generate_series(1, 100000),
CASE
WHEN generate_series(1, 100000) % 1000 = 0 THEN ‘pattern_’ || (generate_series(1, 100000) / 1000)
ELSE ‘repeated_pattern’
END;
INSERT 0 100000

# 复制数据到各压缩表
fgedudb=> INSERT INTO fgedu.fgedu_compress_zlib3 SELECT * FROM fgedu.fgedu_compress_none;
INSERT 0 100000

fgedudb=> INSERT INTO fgedu.fgedu_compress_zlib6 SELECT * FROM fgedu.fgedu_compress_none;
INSERT 0 100000

fgedudb=> INSERT INTO fgedu.fgedu_compress_zlib9 SELECT * FROM fgedu.fgedu_compress_none;
INSERT 0 100000

fgedudb=> INSERT INTO fgedu.fgedu_compress_zstd3 SELECT * FROM fgedu.fgedu_compress_none;
INSERT 0 100000

fgedudb=> INSERT INTO fgedu.fgedu_compress_zstd6 SELECT * FROM fgedu.fgedu_compress_none;
INSERT 0 100000

fgedudb=> INSERT INTO fgedu.fgedu_compress_zstd9 SELECT * FROM fgedu.fgedu_compress_none;
INSERT 0 100000

# 查看压缩效果
fgedudb=> SELECT
‘None’ AS compress_type,
pg_size_pretty(pg_total_relation_size(‘fgedu.fgedu_compress_none’)) AS size
UNION ALL
SELECT ‘ZLIB-3’, pg_size_pretty(pg_total_relation_size(‘fgedu.fgedu_compress_zlib3’))
UNION ALL
SELECT ‘ZLIB-6’, pg_size_pretty(pg_total_relation_size(‘fgedu.fgedu_compress_zlib6’))
UNION ALL
SELECT ‘ZLIB-9’, pg_size_pretty(pg_total_relation_size(‘fgedu_compress_zlib9’))
UNION ALL
SELECT ‘ZSTD-3’, pg_size_pretty(pg_total_relation_size(‘fgedu.fgedu_compress_zstd3’))
UNION ALL
SELECT ‘ZSTD-6’, pg_size_pretty(pg_total_relation_size(‘fgedu.fgedu_compress_zstd6’))
UNION ALL
SELECT ‘ZSTD-9’, pg_size_pretty(pg_total_relation_size(‘fgedu.fgedu_compress_zstd9’))
ORDER BY
CASE
WHEN compress_type = ‘None’ THEN 1
WHEN compress_type LIKE ‘ZLIB%’ THEN 2
WHEN compress_type LIKE ‘ZSTD%’ THEN 3
END,
compress_type;

compress_type | size
—————+———
None | 105 MB
ZLIB-3 | 35 MB
ZLIB-6 | 28 MB
ZLIB-9 | 25 MB
ZSTD-3 | 30 MB
ZSTD-6 | 22 MB
ZSTD-9 | 18 MB
(7 rows)

压缩比计算：
– ZLIB-3: 3.0:1
– ZLIB-6: 3.8:1
– ZLIB-9: 4.2:1
– ZSTD-3: 3.5:1
– ZSTD-6: 4.8:1
– ZSTD-9: 5.8:1

# ODS层订单表（AO表）
fgedudb=> CREATE TABLE ods.fgedu_orders (
order_id VARCHAR(50),
customer_id VARCHAR(50),
product_id VARCHAR(50),
order_amount DECIMAL(18,2),
order_status VARCHAR(20),
order_time TIMESTAMP,
create_time TIMESTAMP DEFAULT NOW(),
etl_time TIMESTAMP DEFAULT NOW()
) WITH (
appendonly = true,
compresstype = quicklz,
compresslevel = 1
) DISTRIBUTED BY (order_id);
CREATE TABLE

# DWD层订单明细表（AO表）
fgedudb=> CREATE TABLE dwd.fgedu_order_detail (
order_id VARCHAR(50),
customer_id VARCHAR(50),
customer_name VARCHAR(100),
product_id VARCHAR(50),
product_name VARCHAR(100),
category_id VARCHAR(50),
category_name VARCHAR(50),
order_amount DECIMAL(18,2),
order_status VARCHAR(20),
order_time TIMESTAMP,
province VARCHAR(50),
city VARCHAR(50),
create_time TIMESTAMP,
etl_time TIMESTAMP DEFAULT NOW()
) WITH (
appendonly = true,
compresstype = zlib,
compresslevel = 5
) DISTRIBUTED BY (order_id);
CREATE TABLE

# DWS层销售汇总表（AOCO表）
fgedudb=> CREATE TABLE dws.fgedu_sales_summary (
date_id INT,
province VARCHAR(50),
city VARCHAR(50),
category_id VARCHAR(50),
category_name VARCHAR(50),
order_count BIGINT,
order_amount DECIMAL(18,2),
customer_count BIGINT,
create_time TIMESTAMP DEFAULT NOW()
) WITH (
appendonly = true,
orientation = column,
compresstype = zstd,
compresslevel = 6
) DISTRIBUTED BY (date_id, province);
CREATE TABLE

# 维度表（复制表）
fgedudb=> CREATE TABLE dim.fgedu_dim_date (
date_id INT,
date_date DATE,
year INT,
quarter INT,
month INT,
day INT,
week INT,
day_of_week INT,
is_weekend BOOLEAN,
is_holiday BOOLEAN,
holiday_name VARCHAR(50)
) DISTRIBUTED REPLICATED;
CREATE TABLE

fgedudb=> CREATE TABLE dim.fgedu_dim_product (
product_id VARCHAR(50),
product_name VARCHAR(100),
category_id VARCHAR(50),
category_name VARCHAR(50),
brand VARCHAR(50),
price DECIMAL(10,2),
status VARCHAR(20),
create_time TIMESTAMP
) DISTRIBUTED REPLICATED;
CREATE TABLE

fgedudb=> CREATE TABLE dim.fgedu_dim_customer (
customer_id VARCHAR(50),
customer_name VARCHAR(100),
gender VARCHAR(10),
age INT,
province VARCHAR(50),
city VARCHAR(50),
level VARCHAR(20),
register_date DATE,
status VARCHAR(20)
) DISTRIBUTED REPLICATED;
CREATE TABLE

1. 存储模型选择
– 频繁更新的表 → 堆表
– 只追加的表 → AO表
– 分析查询表 → AOCO表
– 小的维度表 → 复制表

2. 压缩策略
– 热数据 → 低压缩级别（1-3）
– 温数据 → 中等压缩级别（4-6）
– 冷数据 → 高压缩级别（7-9）
– 重复数据 → RLE压缩
– 文本数据 → ZSTD压缩

3. 数据类型选择
– 选择最小的适合的数据类型
– 避免使用过大的数据类型
– 使用合适的日期时间类型
– 避免使用TEXT类型存储短字符串

4. 表设计规范
– 所有表都要有分布键
– 避免数据倾斜
– 合理使用分区
– 避免过多索引
– 定期更新统计信息