PostgreSQL教程FG026-PG临时表与分区表：基础创建与使用场景

目录大纲

Part01-基础概念与理论知识
- 1.1 临时表的概念与特点
- 1.2 分区表的概念与类型
Part02-生产环境规划与建议
- 2.1 临时表的使用场景与注意事项
- 2.2 分区表的设计原则与规划
Part03-生产环境项目实施方案
- 3.1 临时表的创建与管理
- 3.2 分区表的创建与配置
Part04-生产案例与实战讲解
- 4.1 临时表实战案例
- 4.2 分区表实战案例
Part05-风哥经验总结与分享
- 5.1 临时表与分区表的性能优化
- 5.2 常见问题与解决方案

Part01-基础概念与理论知识

1.1 临时表的概念与特点

临时表是PostgreSQL中一种特殊的表，它只在当前会话中存在，会话结束后自动删除。临时表的主要特点包括：

会话隔离：只对创建它的会话可见
自动清理：会话结束后自动删除
性能优势：通常存储在内存中，访问速度快
命名空间：与普通表同名时，临时表优先级更高

学习交流加群风哥微信: itpux-com

1.2 分区表的概念与类型

分区表是将一个大表分成多个小的子表，每个子表称为一个分区。PostgreSQL支持以下分区类型：

范围分区（Range Partitioning）：根据指定列的范围值进行分区
列表分区（List Partitioning）：根据指定列的离散值进行分区
哈希分区（Hash Partitioning）：根据指定列的哈希值进行分区
复合分区：多种分区类型的组合

更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 临时表的使用场景与注意事项

临时表适合以下场景：

复杂查询的中间结果存储
会话级别的数据处理
测试和调试
批量数据处理

注意事项：

临时表会占用内存和临时空间
不要在临时表上创建索引（除非必要）
会话结束后数据会丢失
不支持分区

风哥提示：临时表适合处理会话级别的临时数据，不适合存储持久化数据。

2.2 分区表的设计原则与规划

分区表设计原则：

选择合适的分区键：通常是经常用于查询条件的列
合理的分区数量：过多会增加管理复杂度，过少则失去分区意义
考虑数据分布：确保数据均匀分布到各个分区
维护策略：定期清理旧分区，添加新分区

分区表适合以下场景：

大表（超过100GB）
时间序列数据
按特定条件频繁查询的数据
需要快速删除历史数据的场景

更多学习教程公众号风哥教程itpux_com

Part03-生产环境项目实施方案

3.1 临时表的创建与管理

3.1.1 创建临时表

— 创建临时表
CREATE TEMPORARY TABLE fgedu_temp_users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE
);

— 插入数据
INSERT INTO fgedu_temp_users (name, email) VALUES
(‘张三’, ‘zhangsan@fgedu.net.cn’),
(‘李四’, ‘lisi@fgedu.net.cn’),
(‘王五’, ‘wangwu@fgedu.net.cn’);

— 查询临时表
SELECT * FROM fgedu_temp_users;

3.1.2 临时表的特性

— 查看当前会话中的临时表
SELECT relname FROM pg_class WHERE relpersistence = ‘t’;

— 尝试在另一个会话中访问临时表
— 注意：会报错，因为临时表只对创建它的会话可见

from PostgreSQL视频:www.itpux.com

3.2 分区表的创建与配置

3.2.1 创建范围分区表

— 创建主表（分区表）
CREATE TABLE fgedu_sales (
id SERIAL PRIMARY KEY,
sale_date DATE NOT NULL,
amount DECIMAL(10, 2),
customer_id INTEGER
) PARTITION BY RANGE (sale_date);

— 创建分区
CREATE TABLE fgedu_sales_2024 PARTITION OF fgedu_sales
FOR VALUES FROM (‘2024-01-01’) TO (‘2025-01-01’);

CREATE TABLE fgedu_sales_2025 PARTITION OF fgedu_sales
FOR VALUES FROM (‘2025-01-01’) TO (‘2026-01-01’);

CREATE TABLE fgedu_sales_2026 PARTITION OF fgedu_sales
FOR VALUES FROM (‘2026-01-01’) TO (‘2027-01-01’);

— 插入数据
INSERT INTO fgedu_sales (sale_date, amount, customer_id) VALUES
(‘2024-05-15’, 1000.00, 1),
(‘2025-03-20’, 2000.00, 2),
(‘2026-01-10’, 1500.00, 3);

— 查询分区表
SELECT * FROM fgedu_sales;

— 查看分区信息
SELECT partition_name, parent_table
FROM pg_partitions
WHERE parent_table = ‘public.fgedu_sales’;

3.2.2 创建列表分区表

— 创建主表
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
order_date DATE NOT NULL,
status VARCHAR(20) NOT NULL,
total_amount DECIMAL(10, 2)
) PARTITION BY LIST (status);

— 创建分区
CREATE TABLE fgedu_orders_pending PARTITION OF fgedu_orders
FOR VALUES IN (‘pending’, ‘processing’);

CREATE TABLE fgedu_orders_completed PARTITION OF fgedu_orders
FOR VALUES IN (‘completed’, ‘shipped’);

CREATE TABLE fgedu_orders_cancelled PARTITION OF fgedu_orders
FOR VALUES IN (‘cancelled’, ‘refunded’);

— 插入数据
INSERT INTO fgedu_orders (order_date, status, total_amount) VALUES
(‘2026-04-01’, ‘pending’, 500.00),
(‘2026-04-02’, ‘completed’, 1000.00),
(‘2026-04-03’, ‘cancelled’, 200.00);

— 查询特定状态的订单
SELECT * FROM fgedu_orders WHERE status = ‘completed’;

学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 临时表实战案例

4.1.1 复杂查询的中间结果处理

— 步骤1：创建临时表存储中间结果
CREATE TEMPORARY TABLE fgedu_temp_sales_summary AS
SELECT
EXTRACT(YEAR FROM sale_date) AS year,
EXTRACT(MONTH FROM sale_date) AS month,
SUM(amount) AS monthly_total
FROM fgedu_sales
GROUP BY year, month
ORDER BY year, month;

— 步骤2：查询临时表获取年度总销售额
SELECT
year,
SUM(monthly_total) AS annual_total
FROM fgedu_temp_sales_summary
GROUP BY year
ORDER BY year;

— 步骤3：查询月度销售趋势
SELECT
year,
month,
monthly_total,
LAG(monthly_total) OVER (ORDER BY year, month) AS previous_month,
(monthly_total – LAG(monthly_total) OVER (ORDER BY year, month)) / LAG(monthly_total) OVER (ORDER BY year,
month) * 100 AS growth_rate
FROM fgedu_temp_sales_summary;

4.1.2 批量数据处理

— 步骤1：创建临时表存储待处理数据
CREATE TEMPORARY TABLE fgedu_temp_batch_update AS
SELECT
id,
amount,
amount * 1.1 AS new_amount — 增加10%
FROM fgedu_sales
WHERE sale_date >= ‘2026-01-01’;

— 步骤2：验证更新数据
SELECT * FROM fgedu_temp_batch_update LIMIT 10;

— 步骤3：执行批量更新
UPDATE fgedu_sales s
SET amount = t.new_amount
FROM fgedu_temp_batch_update t
WHERE s.id = t.id;

— 步骤4：验证更新结果
SELECT * FROM fgedu_sales WHERE sale_date >= ‘2026-01-01’ LIMIT 10;

风哥提示：使用临时表进行批量数据处理时，建议先在临时表中验证数据，再执行实际更新操作。

4.2 分区表实战案例

4.2.1 时间序列数据管理

— 步骤1：创建按天分区的表
CREATE TABLE fgedu_server_logs (
id SERIAL PRIMARY KEY,
log_time TIMESTAMP NOT NULL,
server_id INTEGER,
log_level VARCHAR(20),
message TEXT
) PARTITION BY RANGE (log_time);

— 步骤2：创建分区函数和触发器（自动创建分区）
CREATE OR REPLACE FUNCTION fgedu_create_log_partition()
RETURNS TRIGGER AS $$
DECLARE
partition_name TEXT;
start_date DATE;
end_date DATE;
BEGIN
start_date := DATE_TRUNC(‘day’, NEW.log_time);
end_date := start_date + INTERVAL ‘1 day’;
partition_name := ‘fgedu_server_logs_’ || TO_CHAR(start_date, ‘YYYYMMDD’);

IF NOT EXISTS (
SELECT 1 FROM pg_class
WHERE relname = partition_name
) THEN
EXECUTE format(
‘CREATE TABLE IF NOT EXISTS %I PARTITION OF fgedu_server_logs
FOR VALUES FROM (%L) TO (%L)’,
partition_name, start_date, end_date
);
END IF;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fgedu_log_partition_trigger
BEFORE INSERT ON fgedu_server_logs
FOR EACH ROW
EXECUTE FUNCTION fgedu_create_log_partition();

— 步骤3：插入测试数据
INSERT INTO fgedu_server_logs (log_time, server_id, log_level, message) VALUES
(NOW(), 1, ‘INFO’, ‘Server started’),
(NOW() + INTERVAL ‘1 hour’, 1, ‘WARNING’, ‘High memory usage’),
(NOW() + INTERVAL ‘2 hours’, 2, ‘ERROR’, ‘Database connection failed’);

— 步骤4：查看创建的分区
SELECT partition_name, parent_table, partition_boundaries
FROM pg_partitions
WHERE parent_table = ‘public.fgedu_server_logs’;

— 步骤5：查询特定日期的日志
SELECT * FROM fgedu_server_logs
WHERE log_time >= ‘2026-04-07’ AND log_time < '2026-04-08' ;

4.2.2 分区表的维护

— 步骤1：查看分区大小
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(‘”‘ || schemaname || ‘”.”‘ || tablename || ‘”‘)) AS size
FROM pg_tables
WHERE tablename LIKE ‘fgedu_sales_%’;

— 步骤2：删除旧分区
DROP TABLE IF EXISTS fgedu_sales_2024;

— 步骤3：添加新分区
CREATE TABLE fgedu_sales_2027 PARTITION OF fgedu_sales
FOR VALUES FROM (‘2027-01-01’) TO (‘2028-01-01’);

— 步骤4：重建分区索引
REINDEX TABLE fgedu_sales_2025;
REINDEX TABLE fgedu_sales_2026;