柳州网站建设排行榜,做网站公司找意向客户,wordpress嵌入qq群,西安网站到首页排名在开发应用程序和维护其后台数据库集群的过程中#xff0c;我们经常会遇到实践与理论、开发环境与生产环境之间的差异。其中一个典型的例子就是变更数据库中的列类型。
对于在 PostgreSQL#xff08;及其他符合 SQL 标准的系统#xff09;中变更列类型的常规操作#xff0…在开发应用程序和维护其后台数据库集群的过程中我们经常会遇到实践与理论、开发环境与生产环境之间的差异。其中一个典型的例子就是变更数据库中的列类型。
对于在 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 命令你可能需要先等待所有这些查询释放它们的锁。索引和约束的重建。如果变更的列有索引或约束这些索引和约束需要被重建或重新验证这会带来额外的开销。事务处理和预写日志WALWrite-Ahead Log。为了保证数据的持久性 —— 即 ACID 原则中的「持久性」ACID 中的 DurabilityPostgreSQL 必须在 WAL 文件中记录每次变更。这样即使数据库发生崩溃系统也可以通过重放 WAL 文件来恢复自上一次检查点以来的所有修改。
如你所见在进行看似常规的表格维护时实际涉及的因素远比想象的多。被修改数据的大小、磁盘输入 / 输出和容量以及系统的总体拥堵状态都是必须考虑的关键点。
然而这些还只是问题的一部分。在进行严肃的生产环境部署时还有更多需要考虑的方面
物理和逻辑实时复制这又增加了一层复杂性。对于只读副本来说系统默认通过同步提交来保持数据库集群的数据一致性。这种配置确保了只有在所有备份副本都确认接收到修改后事务才会被最终确认。但这也带来了新的挑战因为系统的表现现在还依赖于网络的传输效率包括可能的网络拥堵以及备份节点的延迟和输入 / 输出性能。恢复和备份是另一个需要考虑的重要方面。虽然常规备份的大小可能不会有太大变化但在变更前的最后一次备份与下一次备份之间所发生的所有事项都必须被考虑进去确保能够做到时间点的一致性。异步复制或为逻辑复制预留插槽reserved slot可能不太常见但也不是没有。产生大量变更以及 WAL 文件可能会让性能较差或不频繁的复制系统落后相当长的时间。虽然这种情况可以接受但必须确保源系统有足够的磁盘空间来长时间保存 WAL 文件。
如你所见变更列数据类型并不像看起来那么简单。目前的 CI/CD 实践使得软件开发人员很容易就能提交并在生产环境中部署数据库 schema 变更但这往往导致他们在几分钟后就面临生产环境的突发事件。虽然使用预发布环境可能有所帮助但由于负载水平或成本限制这样的环境并不能完全模拟生产环境的情况。
因此问题的核心我要再次强调在于数据修改的规模、系统的整体拥堵状况、输入 / 输出性能以及目标数据库表在应用程序设计中的重要性。
最终这些因素共同决定了完成 schema 变更所需的总时间以及你的业务可能面临的独特限制 —— 这些限制你的业务可能承担得起也可能承担不起。解决这一问题的最简单方法是在流量较低的时段安排计划内的维护工作并确保其顺利完成。
如何安全地变更 PostgreSQL 列类型
如果你面临需要重写数百 GB 甚至 TB 的数据并且几乎不能承受任何停机时间的情况该如何安全地变更列类型呢以下是一些步骤和策略。
让我们从坏消息开始您无法避免重写整个表这将在重写过程中产生大量 WAL 文件。这是必然的你必须计划好如何管理它。
好消息是你可以将可能的停机时间分散到一个比数据处理本身更长的时间段。具体的需求和限制会根据各个业务的需要而有所不同因此需要仔细规划。
完整的 schema 过程可以总结为以下几个步骤
在目标表中添加一个新的列并确保其类型正确。确保该列可为空且没有默认值以避免全表重写。更正自 PostgreSQL 11 起实际上无需重写整个表即可实现这一功能 例如如果需要增加 order_id 的 ID你应添加一个名为 new_order_id 的新列。设置一个触发器以便在新数据进入时更新这一新列确保在 schema 变更期间所有新数据都能及时填充到新列中。设计一个批处理变更逻辑从旧列逐步变更到新列。批量大小和执行时间应根据业务或环境的实际运营限制进行调整。变更旧值根据你的数据量、输入 / 输出能力及其他约束旧数据的变更可能需要几小时到几周不等。对于较短的变更你可能只需要在终端会话中运行一个 SQL 或 PL/pgSQL 函数可以考虑使用 tmux而对于更长时间的变更则可能需要更复杂的方法。变更完成后为新列创建相应的约束和索引并注意任何可能的锁定问题尤其是当新字段作为外键的一部分时。
此时你已准备好进行实际的切换操作。一旦确认所有数据行都已正确变更到新列就到了面对最为复杂部分的时候了。如果可能的话尽量在一次事务处理中完成或安排在较短的计划停机期间进行。
删除原有的旧列。这个操作通常只会让表短暂地锁定。在删除旧列之后将新列重命名。这一步基本上标志着 schema 变更过程的完成。
考虑重新启动依赖于已更改表的所有应用程序是一种很好的做法因为某些工具ORM...... 说的就是你可能会缓存 OID无法从容应对更改。
然而问题并未完全解决。仅仅删除列只是移除了引用而数据本身还会物理存留在磁盘上。在这种情况下你可能需要执行一个全表清理VACUUM FULL这个操作可能会锁住整个表并完全重写数据这有可能违背了进行在线 schema 变更的初衷。这时就需要「膨胀克星pg_repack 与 pg_squeeze」出马了 —— 了解和准备这些工具是非常必要的。
结论
在 PostgreSQL 中变更列类型的操作可能看起来简单 —— 仅需执行一个 ALTER TABLE 命令。然而对于涉及的每一个人来说理解这一操作的复杂性至关重要。无论你是提出变更请求的软件开发者、负责审查此变更的人还是在这类更改部署到生产环境后负责解决相关问题的技术人员深刻理解这个过程都非常关键。此外了解这种具体的变更还可以帮助你更容易地对其他可能代价高昂的操作有所预见。
