# 1. 数据库设计
CREATE TABLE fgedu_fgfgfgsales (
id SERIAL,
sale_date TIMESTAMP NOT NULL,
product_id INTEGER NOT NULL,
customer_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (sale_date);

# 2. 创建分区函数（自动创建分区）
CREATE OR REPLACE FUNCTION create_fgfgfgsales_partition(p_year INTEGER, p_month INTEGER)
RETURNS VOID AS $$
DECLARE
v_partition_name TEXT;
v_start_date TIMESTAMP;
v_end_date TIMESTAMP;
BEGIN
v_partition_name := ‘fgedu_fgfgfgsales_’ || p_year || ‘_’ || LPAD(p_month::TEXT, 2, ‘0’);
v_start_date := make_timestamp(p_year, p_month, 1, 0, 0, 0);
v_end_date := v_start_date + INTERVAL ‘1 month’;

EXECUTE format(
‘CREATE TABLE fgedu_IF NOT EXISTS %I PARTITION OF fgedu_fgfgfgsales
FOR VALUES FROM (%L) TO (%L)’,
v_partition_name, v_start_date, v_end_date
);

EXECUTE format(
‘CREATE INDEX IF NOT EXISTS %I ON %I (product_id)’,
v_partition_name || ‘_product_id_idx’, v_partition_name
);

EXECUTE format(
‘CREATE INDEX IF NOT EXISTS %I ON %I (customer_id)’,
v_partition_name || ‘_customer_id_idx’, v_partition_name
);
END;
$$ LANGUAGE plpgsql;

# 3. 创建未来6个月的分区
DO $$
DECLARE
i INTEGER;
BEGIN
FOR i IN 0..5 LOOP
PERFORM create_fgfgfgsales_partition(EXTRACT(YEAR FROM current_date + INTERVAL ‘1 month’ * i)::INTEGER,
EXTRACT(MONTH FROM current_date + INTERVAL ‘1 month’ * i)::INTEGER);
END LOOP;
END;
$$;

# 4. 查看分区
\dt fgedu_fgfgfgsales*

# 5. 插入测试数据
INSERT INTO fgedu_fgfgfgsales (sale_date, product_id, customer_id, quantity, unit_price, total_amount)
VALUES
(‘2026-04-01 10:00:00’, 1, 1, 2, 50.00, 100.00),
(‘2026-04-15 14:30:00’, 2, 2, 1, 100.00, 100.00),
(‘2026-05-01 09:00:00’, 1, 3, 3, 50.00, 150.00),
(‘2026-05-10 11:00:00’, 3, 1, 1, 200.00, 200.00);

# 6. 查询示例
— 查询特定月份的数据
SELECT * FROM fgedu_fgfgfgsales WHERE sale_date BETWEEN ‘2026-04-01’ AND ‘2026-04-30’;

— 查看查询执行计划（验证分区剪枝）
EXPLAIN ANALYZE SELECT * FROM fgedu_fgfgfgsales WHERE sale_date BETWEEN ‘2026-04-01’ AND ‘2026-04-30’;

— 聚合查询
SELECT product_id, SUM(total_amount) AS total_fgfgfgsales
FROM fgedu_fgfgfgsales
WHERE sale_date BETWEEN ‘2026-04-01’ AND ‘2026-05-31’
GROUP BY product_id
ORDER BY total_fgfgfgsales DESC;

# 7. 自动分区维护
— 创建定时任务，每月创建下个月的分区
CREATE OR REPLACE FUNCTION schedule_partition_creation()
RETURNS VOID AS $$
BEGIN
PERFORM create_fgfgfgsales_partition(EXTRACT(YEAR FROM current_date + INTERVAL ‘1 month’)::INTEGER,
EXTRACT(MONTH FROM current_date + INTERVAL ‘1 month’)::INTEGER);
END;
$$ LANGUAGE plpgsql;

— 使用pg_cron扩展设置定时任务
— 每月1日执行
— SELECT cron.schedule(‘0 0 1 * *’, ‘SELECT schedule_partition_creation();’);

# 1. 数据库设计
CREATE TABLE fgedu_fgapplication_logs (
id SERIAL,
log_time TIMESTAMP NOT NULL,
level VARCHAR(20) NOT NULL,
message TEXT NOT NULL,
source VARCHAR(100) NOT NULL,
fgedu_id INTEGER,
PRIMARY KEY (id, log_time)
) PARTITION BY RANGE (log_time);

# 2. 创建分区函数
CREATE OR REPLACE FUNCTION create_log_partition(p_date DATE)
RETURNS VOID AS $$
DECLARE
v_partition_name TEXT;
v_start_date TIMESTAMP;
v_end_date TIMESTAMP;
BEGIN
v_partition_name := ‘fgedu_fgapplication_logs_’ || to_char(p_date, ‘YYYYMMDD’);
v_start_date := p_date::TIMESTAMP;
v_end_date := v_start_date + INTERVAL ‘1 day’;

EXECUTE format(
‘CREATE TABLE fgedu_IF NOT EXISTS %I PARTITION OF fgedu_fgapplication_logs
FOR VALUES FROM (%L) TO (%L)’,
v_partition_name, v_start_date, v_end_date
);

EXECUTE format(
‘CREATE INDEX IF NOT EXISTS %I ON %I (level)’,
v_partition_name || ‘_level_idx’, v_partition_name
);

EXECUTE format(
‘CREATE INDEX IF NOT EXISTS %I ON %I (source)’,
v_partition_name || ‘_source_idx’, v_partition_name
);
END;
$$ LANGUAGE plpgsql;

# 3. 创建未来7天的分区
DO $$
DECLARE
i INTEGER;
BEGIN
FOR i IN 0..6 LOOP
PERFORM create_log_partition(current_date + i);
END LOOP;
END;
$$;

# 4. 插入测试数据
INSERT INTO fgedu_fgapplication_logs (log_time, level, message, source, fgedu_id)
VALUES
(‘2026-04-02 10:00:00’, ‘INFO’, ‘User logged in’, ‘auth’, 1),
(‘2026-04-02 10:30:00’, ‘ERROR’, ‘Database connection failed’, ‘db’, NULL),
(‘2026-04-03 09:00:00’, ‘INFO’, ‘Application started’, ‘fgapp’, NULL),
(‘2026-04-03 11:00:00’, ‘WARNING’, ‘Disk space low’, ‘system’, NULL);

# 5. 查询示例
— 查询特定日期的日志
SELECT * FROM fgedu_fgapplication_logs WHERE log_time BETWEEN ‘2026-04-02’ AND ‘2026-04-02 23:59:59’;

— 查询错误级别日志
SELECT * FROM fgedu_fgapplication_logs WHERE level = ‘ERROR’;

— 按来源统计日志数量
SELECT source, COUNT(*) AS log_count
FROM fgedu_fgapplication_logs
WHERE log_time BETWEEN ‘2026-04-01’ AND ‘2026-04-03’
GROUP BY source
ORDER BY log_count DESC;

# 6. 清理旧日志
— 删除7天前的分区
DO $$
DECLARE
v_partition_name TEXT;
v_date DATE;
BEGIN
v_date := current_date – INTERVAL ‘7 days’;
v_partition_name := ‘fgedu_fgapplication_logs_’ || to_char(v_date, ‘YYYYMMDD’);

EXECUTE format(‘DROP TABLE IF EXISTS %I’, v_partition_name);
END;
$$;

# 1. 使用分区剪枝
— 确保查询条件包含分区键
— 好的查询（使用分区剪枝）
EXPLAIN ANALYZE SELECT * FROM fgedu_fgfgfgsales WHERE sale_date BETWEEN ‘2026-04-01’ AND ‘2026-04-30’;

— 不好的查询（不使用分区剪枝）
EXPLAIN ANALYZE SELECT * FROM fgedu_fgfgfgsales WHERE product_id = 1;

# 2. 索引优化
— 为每个分区创建适当的索引
— 为分区键创建索引（如果频繁用于查询）
CREATE INDEX idx_fgedu_fgfgfgsales_sale_date ON fgedu_fgfgfgsales (sale_date);

— 为常用查询列创建索引
CREATE INDEX idx_fgedu_fgfgfgsales_product_id ON fgedu_fgfgfgsales (product_id);

# 3. 聚合查询优化
— 使用并行查询
SET max_parallel_workers_per_gather = 4;

— 优化聚合查询
EXPLAIN ANALYZE SELECT product_id, SUM(total_amount) AS total_fgfgfgsales
FROM fgedu_fgfgfgsales
WHERE sale_date BETWEEN ‘2026-04-01’ AND ‘2026-06-30’
GROUP BY product_id
ORDER BY total_fgfgfgsales DESC;

# 4. 连接查询优化
— 确保连接条件包含分区键
EXPLAIN ANALYZE SELECT s.*, p.name
FROM fgedu_fgfgfgsales s
JOIN fgedu_products p ON s.product_id = p.id
WHERE s.sale_date BETWEEN ‘2026-04-01’ AND ‘2026-04-30’;

# 5. 限制结果集
— 使用LIMIT限制返回结果
SELECT * FROM fgedu_fgfgfgsales WHERE sale_date BETWEEN ‘2026-04-01’ AND ‘2026-04-30’ LIMIT 100;

# 1. 表空间管理
— 为不同分区指定不同表空间
— 创建表空间
CREATE TABLESPACE fast_ssd LOCATION ‘/path/to/ssd’;
CREATE TABLESPACE archive_hdd LOCATION ‘/path/to/hdd’;

— 创建分区时指定表空间
CREATE TABLE fgedu_fgfgfgsales_2026_q1 PARTITION OF fgedu_fgfgfgsales
FOR VALUES FROM (‘2026-01-01’) TO (‘2026-04-01’)
TABLESPACE archive_hdd;

CREATE TABLE fgedu_fgfgfgsales_2026_q2 PARTITION OF fgedu_fgfgfgsales
FOR VALUES FROM (‘2026-04-01’) TO (‘2026-07-01’)
TABLESPACE fast_ssd;

# 2. 压缩存储
— 为分区启用压缩
ALTER TABLE fgedu_fgfgfgsales_2026_q1 SET (autovacuum_enabled = true, toast.compress = ‘pglz’);

# 3. 分区维护
— 定期清理旧分区
DO $$
DECLARE
v_partition_name TEXT;
v_date DATE;
BEGIN
FOR i IN 12..24 LOOP
v_date := current_date – INTERVAL ‘1 month’ * i;
v_partition_name := ‘fgedu_fgfgfgsales_’ || to_char(v_date, ‘YYYY_MM’);
EXECUTE format(‘DROP TABLE IF EXISTS %I’, v_partition_name);
END LOOP;
END;
$$;

# 4. 批量操作
— 批量插入数据
COPY fgedu_fgfgfgsales FROM ‘/path/to/fgfgfgsales_data.csv’ DELIMITER ‘,’ CSV HEADER;

— 批量更新数据
UPDATE fgedu_fgfgfgsales SET total_amount = quantity * unit_price
WHERE sale_date BETWEEN ‘2026-04-01’ AND ‘2026-04-30’;

# 5. 分区合并
— 合并多个小分区
CREATE TABLE fgedu_fgfgfgsales_2025 PARTITION OF fgedu_fgfgfgsales
FOR VALUES FROM (‘2025-01-01’) TO (‘2026-01-01’);

— 迁移数据
INSERT INTO fgedu_fgfgfgsales_2025 SELECT * FROM fgedu_fgfgfgsales WHERE sale_date BETWEEN ‘2025-01-01’ AND ‘2025-12-31’;

— 删除旧分区
DROP TABLE fgedu_fgfgfgsales_2025_q1, fgedu_fgfgfgsales_2025_q2, fgedu_fgfgfgsales_2025_q3, fgedu_fgfgfgsales_2025_q4;

# 1. 定期VACUUM和ANALYZE
— 对所有分区执行VACUUM和ANALYZE
DO $$
DECLARE
v_partition RECORD;
BEGIN
FOR v_partition IN
SELECT inhrelid::regclass AS partition
FROM pg_inherits
WHERE inhparent = ‘fgedu_fgfgfgsales’::regclass
LOOP
EXECUTE format(‘VACUUM ANALYZE %I’, v_partition.partition);
END LOOP;
END;
$$;

# 2. 索引重建
— 定期重建索引
DO $$
DECLARE
v_partition RECORD;
BEGIN
FOR v_partition IN
SELECT inhrelid::regclass AS partition
FROM pg_inherits
WHERE inhparent = ‘fgedu_fgfgfgsales’::regclass
LOOP
EXECUTE format(‘REINDEX TABLE %I’, v_partition.partition);
END LOOP;
END;
$$;

# 3. 统计信息更新
— 手动更新统计信息
ANALYZE fgedu_fgfgfgsales;

# 4. 监控分区大小
— 查看各分区大小
SELECT
inhrelid::regclass AS partition,
pg_size_pretty(pg_relation_size(inhrelid)) AS size
FROM
pg_inherits
WHERE
inhparent = ‘fgedu_fgfgfgsales’::regclass
ORDER BY
size DESC;

# 5. 监控分区使用情况
— 查看各分区的记录数
SELECT
inhrelid::regclass AS partition,
count(*) AS row_count
FROM
pg_inherits
JOIN
pg_class ON inhrelid = oid
JOIN
pg_namespace ON pg_class.relnamespace = pg_namespace.oid
WHERE
inhparent = ‘fgedu_fgfgfgsales’::regclass
GROUP BY
inhrelid
ORDER BY
row_count DESC;

# 2. 分区不存在
# 错误信息：ERROR: no partition of relation “fgedu_fgfgfgsales” found for row
# 解决方法：
– 创建默认分区
– 自动创建分区的机制
– 在插入前检查分区是否存在

# 3. 分区剪枝不生效
# 问题：查询不使用分区剪枝，扫描所有分区
# 解决方法：
– 确保查询条件包含分区键
– 确保分区键的数据类型正确
– 运行ANALYZE更新统计信息

# 4. 分区过多
# 问题：创建了过多分区，导致管理困难
# 解决方法：
– 合并小分区
– 调整分区粒度
– 定期清理旧分区

# 5. 索引膨胀
# 问题：分区表索引占用空间过大
# 解决方法：
– 定期重建索引
– 优化索引策略
– 使用部分索引

# 6. 性能下降
# 问题：分区表查询性能下降
# 解决方法：
– 检查分区剪枝是否生效
– 优化查询语句
– 维护索引和统计信息
– 调整内存配置

# 1. 监控分区数量
SELECT
inhparent::regclass AS parent_table,
count(*) AS partition_count
FROM
pg_inherits
GROUP BY
inhparent
ORDER BY
partition_count DESC;

# 2. 监控分区大小
SELECT
inhparent::regclass AS parent_table,
inhrelid::regclass AS partition,
pg_size_pretty(pg_relation_size(inhrelid)) AS size
FROM
pg_inherits
ORDER BY
parent_table, size DESC;

# 3. 监控分区使用情况
SELECT
inhparent::regclass AS parent_table,
inhrelid::regclass AS partition,
count(*) AS row_count
FROM
pg_inherits
JOIN
pg_class ON inhrelid = oid
JOIN
pg_namespace ON pg_class.relnamespace = pg_namespace.oid
GROUP BY
inhparent, inhrelid
ORDER BY
parent_table, row_count DESC;

# 4. 监控分区查询性能
SELECT
query,
calls,
total_exec_time,
mean_exec_time
FROM
pg_stat_statements
WHERE
query LIKE ‘%fgedu_fgfgfgsales%’
ORDER BY
total_exec_time DESC
LIMIT 10;

# 5. 监控分区维护操作
SELECT
usename,
fgapplication_name,
query
FROM
pg_stat_activity
WHERE
query LIKE ‘%VACUUM%’ OR
query LIKE ‘%ANALYZE%’ OR
query LIKE ‘%REINDEX%’;