如何通过变更让 PostgreSQL 翻车

2024-08-20 16:20
文章标签 postgresql 变更 翻车

本文主要是介绍如何通过变更让 PostgreSQL 翻车,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

在开发应用程序和维护其后台数据库集群的过程中,我们经常会遇到实践与理论、开发环境与生产环境之间的差异。其中一个典型的例子就是变更数据库中的列类型。

对于在 PostgreSQL(及其他符合 SQL 标准的系统)中变更列类型的常规操作,大多数人都认为有一个标准的方法,即:

ALTER TABLE table_name
ALTER COLUMN column_name
[SET DATA] TYPE new_data_type

这种方法在语义上无可挑剔,但在特定情况下,它可能带来一些意想不到的困扰。

问题

让我们来创建一个示例表,并展示一些你可能会遇到的特定行为。从 1000 万条数据开始说起 —— 虽然听起来很多,但在数据世界里,只是沧海一粟。

-- create very simple table
CREATE TABLE sample_table (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,label TEXT,created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()
);-- populate with 10m records
INSERT INTO sample_table (label)
SELECT 'hash: ' || md5(random()::text)
FROM generate_series(1, 7000000);

将 id 的数据类型 从 INT 变更为 BIGINT。

alter_type_demo=# ALTER TABLE sample_table ALTER COLUMN id TYPE bigint;
ALTER TABLE
Time: 21592.190 ms (00:21.592)

然后 …… 21 秒后,数据变更完成。但请注意,这只是一个含有大约 600 MB 数据的小型数据表。如果你要处理的数据量是这个的 100 倍怎么办?让我们来看看背后发生了什么。

PostgreSQL 必须执行的操作

变更数据类型以及其他许多操作都不是简单的任务,PostgreSQL 引擎需要执行以下几项关键任务:

  • 重写整个表是最直接的必要步骤。将一个列从 INT 类型变更为 BIGINT 类型,需要为每个数据行额外分配 4 字节。原有的表结构要求数据以固定的字节大小存储,因此系统会以最有效的方式进行存储。在我们的例子中,这意味着需要读取并重新写入所有的 1000 万数据行,以符合新的数据行大小要求。
  • 锁处理。虽然在我们这个假设的示例中加锁似乎不是问题,但如果你在实际的生产环境中,同时处理数百甚至数千个并发查询时执行 ALTER 命令,你可能需要先等待所有这些查询释放它们的锁。
  • 索引和约束的重建。如果变更的列有索引或约束,这些索引和约束需要被重建或重新验证,这会带来额外的开销。
  • 事务处理和预写日志(WAL,Write-Ahead Log)。为了保证数据的持久性 —— 即 ACID 原则中的「持久性」(ACID 中的 Durability),PostgreSQL 必须在 WAL 文件中记录每次变更。这样,即使数据库发生崩溃,系统也可以通过重放 WAL 文件来恢复自上一次检查点以来的所有修改。

如你所见,在进行看似常规的表格维护时,实际涉及的因素远比想象的多。被修改数据的大小、磁盘输入 / 输出和容量以及系统的总体拥堵状态,都是必须考虑的关键点。

然而,这些还只是问题的一部分。在进行严肃的生产环境部署时,还有更多需要考虑的方面:

  • 物理和逻辑实时复制:这又增加了一层复杂性。对于只读副本来说,系统默认通过同步提交来保持数据库集群的数据一致性。这种配置确保了只有在所有备份副本都确认接收到修改后,事务才会被最终确认。但这也带来了新的挑战,因为系统的表现现在还依赖于网络的传输效率,包括可能的网络拥堵,以及备份节点的延迟和输入 / 输出性能。
  • 恢复和备份是另一个需要考虑的重要方面。虽然常规备份的大小可能不会有太大变化,但在变更前的最后一次备份与下一次备份之间所发生的所有事项都必须被考虑进去,确保能够做到时间点的一致性。
  • 异步复制或为逻辑复制预留插槽(reserved slot)可能不太常见,但也不是没有。产生大量变更(以及 WAL 文件)可能会让性能较差(或不频繁)的复制系统落后相当长的时间。虽然这种情况可以接受,但必须确保源系统有足够的磁盘空间来长时间保存 WAL 文件。

如你所见,变更列数据类型并不像看起来那么简单。目前的 CI/CD 实践使得软件开发人员很容易就能提交并在生产环境中部署数据库 schema 变更,但这往往导致他们在几分钟后就面临生产环境的突发事件。虽然使用预发布环境可能有所帮助,但由于负载水平或成本限制,这样的环境并不能完全模拟生产环境的情况。

因此,问题的核心(我要再次强调)在于数据修改的规模、系统的整体拥堵状况、输入 / 输出性能以及目标数据库表在应用程序设计中的重要性。

最终,这些因素共同决定了完成 schema 变更所需的总时间,以及你的业务可能面临的独特限制 —— 这些限制你的业务可能承担得起,也可能承担不起。解决这一问题的最简单方法是在流量较低的时段安排计划内的维护工作,并确保其顺利完成。

如何安全地变更 PostgreSQL 列类型

如果你面临需要重写数百 GB 甚至 TB 的数据,并且几乎不能承受任何停机时间的情况,该如何安全地变更列类型呢?以下是一些步骤和策略。

让我们从坏消息开始:您无法避免重写整个表,这将在重写过程中产生大量 WAL 文件。这是必然的,你必须计划好如何管理它。

好消息是,你可以将可能的停机时间分散到一个比数据处理本身更长的时间段。具体的需求和限制会根据各个业务的需要而有所不同,因此需要仔细规划。

完整的 schema 过程可以总结为以下几个步骤:

  1. 在目标表中添加一个新的列,并确保其类型正确。确保该列可为空且没有默认值,以避免全表重写。(更正:自 PostgreSQL 11 起,实际上无需重写整个表即可实现这一功能 )例如,如果需要增加 order_id 的 ID,你应添加一个名为 new_order_id 的新列。
  2. 设置一个触发器,以便在新数据进入时更新这一新列,确保在 schema 变更期间所有新数据都能及时填充到新列中。
  3. 设计一个批处理变更逻辑,从旧列逐步变更到新列。批量大小和执行时间应根据业务或环境的实际运营限制进行调整。
  4. 变更旧值:根据你的数据量、输入 / 输出能力及其他约束,旧数据的变更可能需要几小时到几周不等。对于较短的变更,你可能只需要在终端会话中运行一个 SQL 或 PL/pgSQL 函数(可以考虑使用 tmux);而对于更长时间的变更,则可能需要更复杂的方法。
  5. 变更完成后,为新列创建相应的约束和索引,并注意任何可能的锁定问题,尤其是当新字段作为外键的一部分时。

此时,你已准备好进行实际的切换操作。一旦确认所有数据行都已正确变更到新列,就到了面对最为复杂部分的时候了。如果可能的话,尽量在一次事务处理中完成,或安排在较短的计划停机期间进行。

  • 删除原有的旧列。这个操作通常只会让表短暂地锁定。
  • 在删除旧列之后,将新列重命名。这一步基本上标志着 schema 变更过程的完成。

考虑重新启动依赖于已更改表的所有应用程序是一种很好的做法,因为某些工具(ORM...... 说的就是你)可能会缓存 OID,无法从容应对更改。

然而,问题并未完全解决。仅仅删除列,只是移除了引用,而数据本身还会物理存留在磁盘上。在这种情况下,你可能需要执行一个全表清理(VACUUM FULL),这个操作可能会锁住整个表并完全重写数据,这有可能违背了进行在线 schema 变更的初衷。这时就需要「膨胀克星:pg_repack 与 pg_squeeze」出马了 —— 了解和准备这些工具是非常必要的。

结论

在 PostgreSQL 中变更列类型的操作可能看起来简单 —— 仅需执行一个 ALTER TABLE 命令。然而,对于涉及的每一个人来说,理解这一操作的复杂性至关重要。无论你是提出变更请求的软件开发者、负责审查此变更的人,还是在这类更改部署到生产环境后负责解决相关问题的技术人员,深刻理解这个过程都非常关键。此外,了解这种具体的变更还可以帮助你更容易地对其他可能代价高昂的操作有所预见。

这篇关于如何通过变更让 PostgreSQL 翻车的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1090562

相关文章

PostgreSQL核心功能特性与使用领域及场景分析

PostgreSQL有什么优点? 开源和免费 PostgreSQL是一个开源的数据库管理系统,可以免费使用和修改。这降低了企业的成本,并为开发者提供了一个活跃的社区和丰富的资源。 高度兼容 PostgreSQL支持多种操作系统(如Linux、Windows、macOS等)和编程语言(如C、C++、Java、Python、Ruby等),并提供了多种接口(如JDBC、ODBC、ADO.NET等

PostgreSQL中的多版本并发控制(MVCC)深入解析

引言 PostgreSQL作为一款强大的开源关系数据库管理系统,以其高性能、高可靠性和丰富的功能特性而广受欢迎。在并发控制方面,PostgreSQL采用了多版本并发控制(MVCC)机制,该机制为数据库提供了高效的数据访问和更新能力,同时保证了数据的一致性和隔离性。本文将深入解析PostgreSQL中的MVCC功能,探讨其工作原理、使用场景,并通过具体SQL示例来展示其在实际应用中的表现。 一、

PostgreSQL入门介绍

一、PostgreSQL 背景及主要功能介绍 1、背景 PG数据库,全称为PostgreSQL数据库,是一款开源的关系型数据库管理系统(RDBMS)。其起源可以追溯到20世纪80年代末和90年代初,由加拿大的计算机科学家Michael Stonebraker及其团队在加州大学伯克利分校启动。该项目旨在创建一个强大的、开源的关系型数据库管理系统,作为早期关系型数据库系统Ingres的继承者。Mi

PostgreSQL索引介绍

梦中彩虹   博客园首页新随笔联系管理 随笔 - 131  文章 - 1  评论 - 14 PostgreSQL索引介绍 INDEX 索引是增强数据库性能的常用方法。索引使得数据库在查找和检索数据库的特定行的时候比没有索引快的多。但索引也增加了整个数据库系统的开销,所以应该合理使用。 介绍 假设我们有一个类似这样的表: CREATE TABLE test1 (id integ

PostgreSQL分区表(partitioning)应用实例详解

https://www.jb51.net/article/97937.htm   PostgreSQL分区表(partitioning)应用实例详解  更新时间:2016年11月22日 10:25:58   作者:小灯光环    我要评论   这篇文章主要为大家详细介绍了PostgreSQL分区表(partitioning)应用实例,具有一定的参考价值,感兴趣的小伙伴们可以参考一下

PostgreSql中WITH语句的使用

https://blog.csdn.net/chuan_day/article/details/44809125 PostgreSql中WITH语句的使用 With语句是为庞大的查询语句提供了辅助的功能。这些语句通常是引用了表表达式或者CTEs(一种临时数据的存储方式),可以看做是一个查询语句的临时表。在With语句中可以使用select,insert,update,delete语句。当然wit

PostgreSQL 17即将发布,新功能Top 3

按照计划,PostgreSQL 17 即将在 2024 年 9 月 26 日发布,目前已经发布了第一个 RC 版本,新版本的功能增强可以参考 Release Notes。 本文给大家分享其中 3 个重大的新增功能。 MERGE 语句增强 MERGE 语句是 PostgreSQL 15 增加的一个新功能,它可以在单个语句中实现 INSERT、UPDATE 以及 DELETE 操作,非常适合数据

Peewee+Postgresql+PooledPostgresqlDatabase重连机制

需求: Postgresql数据库服务重启后,需要业务代码正常读写数据库 方案: 通过继承playhouse.shortcuts.ReconnectMixin和playhouse.pool.PooledPostgresqlDatabase来创建一个新的ReconnectPooledPostgresqlDatabase类修改reconnect_errors属性来适配Postgresql

Mysql在线安全变更工具 gh-ost

gh-ost(GitHub Online Schema Translater)是一个用于安全地进行 MySQL 数据库表结构变更的工具,专门为在线数据库迁移而设计。它的主要优点是可以在不中断业务的情况下,不影响数据库的可用性和性能,进行表结构的修改。 主要特点 在线迁移:gh-ost 允许在高负载的生产环境中进行表结构的更改,而不需要锁定表,确保数据库的可用性。 渐进式复制:通过逐步复制行

【PostgreSQL教程】PostgreSQL 高级篇之 LOCK(锁)

博主介绍:✌全网粉丝20W+,CSDN博客专家、Java领域优质创作者,掘金/华为云/阿里云/InfoQ等平台优质作者、专注于Java技术领域✌ 技术范围:SpringBoot、SpringCloud、Vue、SSM、HTML、Nodejs、Python、MySQL、PostgreSQL、大数据、物联网、机器学习等设计与开发。 感兴趣的可以先关注收藏起来,在工作中、生活上等遇到相关问题都可以给我