讲师中心 微信公众号

工具推荐

视频效率加速
立刻MV
立刻MV

低成本歌曲 MV、视频生成

AniShort
AniShort

AI短剧协同制作平台

updream

一键生成高质量推广成片

SoundView
SoundView

字幕擦除翻译、配音口型同步

在PostgreSQL 15中如何使用SQL从大表中快速清理历史数据?

大辰姑娘_7670

大辰姑娘_7670

发布时间:2026-11-21 09:59:20

|

220人浏览过

|

来源于php中文网

原创

在postgresql 15中,从大表中快速清理历史数据的最有效方法是使用 drop table 配合分区表(partitioning),或者对非分区表使用 delete 结合 vacuum full。对于拥有数十亿行且按时间范围分区的表,直接删除整个分区(即 drop table)是瞬间完成的,因为它只涉及元数据操作,不产生大量的wal日志和锁竞争。如果表未分区,则应使用带有 limit 的批量 delete 语句,以避免事务膨胀和锁超时,随后执行 vacuum full 以回收磁盘空间。

在PostgreSQL 15中如何使用SQL从大表中快速清理历史数据?

如何设计支持快速删除的分区策略?

要实现秒级删除,核心在于将数据按时间范围进行物理隔离。在PostgreSQL 15中,推荐使用声明式分区(Declarative Partitioning)。通过将主表定义为范围分区,并按 created_at 字段划分区间,旧的数据会被存储在独立的子表中。当需要清理一年前的数据时,只需找到对应的分区并执行 DROP TABLE。这种方法避免了全表扫描和逐行删除的性能开销,同时保持了主表的索引效率。

在PostgreSQL 15中如何使用SQL从大表中快速清理历史数据?

CREATE TABLE measurements (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

-- 创建当前分区
CREATE TABLE measurements_y2023m04 PARTITION OF measurements
    FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');

-- 创建历史分区
CREATE TABLE measurements_y2022m04 PARTITION OF measurements
    FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');

-- 模拟插入数据
INSERT INTO measurements SELECT random()::int, '2022-04-01'::date + i, random()::int, random()::int
FROM generate_series(1, 1000000) AS s(i); -- 假设插入了大量数据

-- 快速清理:直接删除整个分区
DROP TABLE IF EXISTS measurements_y2022m04;

上述代码展示了如何创建一个按日期范围分区的表。首先定义主表结构并指定分区键。接着,为不同的月份创建具体的分区。在实际操作中,你可以编写一个脚本定期创建未来的分区,并删除过期的分区。当执行 DROP TABLE measurements_y2022m04 时,PostgreSQL 会立即释放该分区占用的所有磁盘块,无需等待垃圾回收机制。

非分区表的大规模数据删除方案是什么?

如果由于架构限制无法使用分区表,必须处理单一的大表,那么直接使用 DELETE FROM table WHERE condition 可能会导致严重的性能问题,包括长事务、锁持有时间过长以及事务日志(WAL)爆满。推荐的替代方案是采用“分批删除”策略。通过在一个循环中使用 DELETE ... LIMIT n,每次只删除少量数据并提交事务。这样可以减少单次事务的负载,允许其他查询并发执行。删除完成后,必须运行 VACUUM FULL 来真正回收空间,因为普通的 VACUUM 只是标记空间为可用,不会缩小表文件。

在PostgreSQL 15中如何使用SQL从大表中快速清理历史数据?

-- 假设我们要删除 created_at 早于 '2022-01-01' 的记录
-- 使用 PL/pgSQL 进行分批删除
DO $$
DECLARE
    rows_deleted INTEGER;
BEGIN
    LOOP
        DELETE FROM measurements
        WHERE created_at < '2022-01-01'
        LIMIT 10000; -- 每批删除1万条

        GET DIAGNOSTICS rows_deleted = ROW_COUNT;
        EXIT WHEN rows_deleted = 0;
        
        -- 可选:短暂休眠以避免CPU过载
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

-- 最后回收空间
VACUUM FULL measurements;

这段代码演示了如何在非分区场景下安全地清理数据。关键在于 LIMIT 10000 的使用,它确保了每次 DELETE 操作都很快完成并立即提交。虽然这比一次性删除慢得多,但它将对生产环境的影响降到了最低。需要注意的是,VACUUM FULL 会获取排他锁,阻塞所有对该表的读写操作,因此建议在业务低峰期执行。此外,如果表上有外键约束或触发器,分批删除的逻辑可能需要调整以处理级联效应。

删除操作后为什么需要执行VACUUM FULL?

在PostgreSQL中,删除操作并不会立即从磁盘上移除数据页,而是将这些页面标记为“死元组”(dead tuples)。这是为了支持多版本并发控制(MVCC),确保正在读取旧数据的查询不会看到不一致的状态。然而,这些死元组会占用磁盘空间并导致索引膨胀。普通的 VACUUM 命令可以清理这些死元组并回收空间给表内部重用,但不会收缩表文件的大小。如果你希望彻底释放磁盘空间,使表文件变小,就必须使用 VACUUM FULL。它会重新构建整个表,将活数据紧凑排列,并将未使用的空间归还给操作系统。这也是为什么在大批量删除后,VACUUM FULL 是恢复磁盘容量的必要步骤。

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热门AI工具

更多
咔片AIPPT

一款在线AI演示文稿制作工具,可根据主题和内容需求辅助生成PPT结构与页面,提高演示材料制作效率。

墨刀AI
墨刀AI Hot

一款AI图像与设计工具,主要用于产品经理的专属智能体,适合需要提升相关任务效率的用户。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

UpDream
UpDream Hot

一款AI视频创作工具,主要用于哔哩哔哩推出的自研AI视频创作工具,适合需要提升相关任务效率的用户。

WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

超级麦吉

一款通过数字员工协作完成调研、数据分析与文档制作的AI办公平台。

Atoms
Atoms Hot

Atoms是一款AI智能体工具,第一支自动构建真实业务的 AI 团队。

函子Zion

一款支持AI搭建、可视化编辑和多端发布的全栈无代码开发平台。

豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

相关专题

更多
数据分析工具有哪些
数据分析工具有哪些

数据分析工具有Excel、SQL、Python、R、Tableau、Power BI、SAS、SPSS和MATLAB等。详细介绍:1、Excel,具有强大的计算和数据处理功能;2、SQL,可以进行数据查询、过滤、排序、聚合等操作;3、Python,拥有丰富的数据分析库;4、R,拥有丰富的统计分析库和图形库;5、Tableau,提供了直观易用的用户界面等等。

5163

2023.10.12

SQL中distinct的用法
SQL中distinct的用法

SQL中distinct的语法是“SELECT DISTINCT column1, column2,...,FROM table_name;”。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

1051

2023.10.27

SQL中months_between使用方法
SQL中months_between使用方法

在SQL中,MONTHS_BETWEEN 是一个常见的函数,用于计算两个日期之间的月份差。想了解更多SQL的相关内容,可以阅读本专题下面的文章。

1349

2024.02.23

SQL出现5120错误解决方法
SQL出现5120错误解决方法

SQL Server错误5120是由于没有足够的权限来访问或操作指定的数据库或文件引起的。想了解更多sql错误的相关内容,可以阅读本专题下面的文章。

7281

2024.03.06

sql procedure语法错误解决方法
sql procedure语法错误解决方法

sql procedure语法错误解决办法:1、仔细检查错误消息;2、检查语法规则;3、检查括号和引号;4、检查变量和参数;5、检查关键字和函数;6、逐步调试;7、参考文档和示例。想了解更多语法错误的相关内容,可以阅读本专题下面的文章。

3883

2024.03.06

oracle数据库运行sql方法
oracle数据库运行sql方法

运行sql步骤包括:打开sql plus工具并连接到数据库。在提示符下输入sql语句。按enter键运行该语句。查看结果,错误消息或退出sql plus。想了解更多oracle数据库的相关内容,可以阅读本专题下面的文章。

7260

2024.04.07

sql中where的含义
sql中where的含义

sql中where子句用于从表中过滤数据,它基于指定条件选择特定的行。想了解更多where的相关内容,可以阅读本专题下面的文章。

10201

2024.04.29

sql中删除表的语句是什么
sql中删除表的语句是什么

sql中用于删除表的语句是drop table。语法为drop table table_name;该语句将永久删除指定表的表和数据。想了解更多sql的相关内容,可以阅读本专题下面的文章。

1390

2024.04.29

Nginx CVE-2026-123007排查与防护指南
Nginx CVE-2026-123007排查与防护指南

Nginx HTTP/2漏洞该怎么修复?本专题围绕CVE-2026-123007,介绍安全版本升级、模块兼容检查和配置恢复方法,以及日志排查、权限设置等后续加固操作,方便你按实际部署情况查找对应教程。

0

2026.11.20

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
热门推荐
/
最新课程
关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn