怎么看别人的网站有没有做301,wordpress 回复下载插件,无代码开发平台什么意思,西宁企业网站开发定制开发应用程序并在其背后操作数据库集群时#xff0c;会遇到一个意想不到的问题是实践与理论、开发环境与生产之间的差异。这种不匹配的一个完美例子就是更改列类型。 #PG考试#postgresql培训#postgresql考试#postgresql认证 关于如何在 PostgreSQL#xff08;以及其他符合 SQ…开发应用程序并在其背后操作数据库集群时会遇到一个意想不到的问题是实践与理论、开发环境与生产之间的差异。这种不匹配的一个完美例子就是更改列类型。 #PG考试#postgresql培训#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 命令则您必须等待所有查询释放锁。索引和约束- 如果要更改的列已编入索引或有约束则需要重建/重新验证它们。这是额外的开销。事务和预写日志是问题的另一个重要部分。为了保证持久性ACID 中的“D”PostgreSQL 必须在 WAL 文件中记录每个更改。这样如果数据库崩溃系统可以重放 WAL 文件以重建自上次检查点以来丢失的修改。
如您所见执行一些可能被理解为常规表维护的操作涉及很多内容。修改的数据的大小、磁盘 I/O 和容量以及一般系统拥塞都会发挥作用。
但真正的问题并没有就此结束。如果我们谈论的是任何类型的严肃生产部署你必须考虑更多的事情
实时复制包括物理和逻辑复制这增加了额外的复杂性。对于只读副本默认行为可确保维护同步提交以实现整个数据库集群的一致性。此设置可保证只有在所有备用副本都确认收到更改后事务才会完成。然而这带来了新的挑战因为性能现在还取决于网络吞吐量包括潜在的拥塞以及备用节点的延迟和 I/O 性能。恢复和备份是另一个需要考虑的重要领域。虽然常规备份的大小可能不会受到太大影响但您必须考虑更改前的最后一次备份与下一次备份之间发生的所有事情并确保时间点一致性。不太常见但并非闻所未闻的可能是异步副本或逻辑复制的保留槽。生成大量更改以及 WAL文件可能会使性能较差或不频繁的复制系统落后相当长的时间。虽然这可能是可以接受的但您需要确保源系统有足够的磁盘空间来保存 WAL文件足够长的时间
如您所见更改列数据类型并不像看起来那么简单。当前的 CI/CD 实践通常使软件开发人员能够非常轻松地提交数据库迁移并将其推广到生产环境但几分钟后他们就会发现自己处于生产事件之中。虽然暂存部署可能会有所帮助但它不能保证与生产具有相同的特征无论是由于负载水平还是资金限制。
因此问题在于我会重复一遍修改的数据量的规模、系统的整体拥塞程度、 I/O 容量以及目标表在应用程序设计中的重要性。
归根结底这转化为完成迁移所需的总时间以及您的企业可以或可能无法承受的独特限制。解决该问题最简单的方法是将计划维护安排在流量较低的时段并完成它。
如何安全地更改 PostgreSQL 列类型
如果您需要重写数百 GB 甚至 TB 的数据并且无法承受超过最低限度的停机时间该怎么办让我们探索如何正确更改列类型。
让我们先从坏消息开始——你无法避免重写整个表这将在此过程中生成大量 WAL 文件。这是必然的你必须计划如何管理它。
好消息您可以将潜在的停机时间分散到比处理数据所需的更长的时间段。具体要求和限制将根据各个业务需求而有所不同因此仔细规划至关重要。
完整的迁移可以概括为以下一系列步骤
向目标表添加具有正确类型的新列。确保该列可以为 NULL 且没有默认值以避免强制重写整个表1。例如如果您需要增加ID则order_id最终会得到新列new_order_id。设置一个触发器在有新数据进入时更新新列。这可确保迁移期间的所有新数据都将填充新列。实现一个函数或逻辑以便随着时间的推移批量将值从旧列迁移到新列。批次的大小和时间应与您的业务/环境的运营约束相一致。迁移旧值根据您的约束、数据大小和 I/O 功能此过程可能需要数小时到数周甚至更长时间。虽然在终端会话中运行的 SQL 或PL/pgSQL 函数考虑使用tmux可能足以完成较短的迁移但更长的迁移可能需要更复杂的方法。仅此主题就可以成为单独的博客文章或指南的好主题。迁移完成后创建反映新列的约束和索引。注意潜在的锁定问题尤其是当该字段是任何外键的一部分时。
此时您已准备好执行切换本身。如果您可以验证所有行都已正确填充新列那么是时候接受最困难的部分了。如果可能的话在一个事务中或更短的计划停机时间内完成
删除旧列。此操作通常只会短暂锁定表。删除旧列后重命名新列。此步骤完成了大部分迁移过程。
考虑重新启动所有依赖于更改的表的应用程序是一种很好的做法因为某些工具ORM…我正在看你可能会缓存 OID 并且不能很好地处理更改。
就是这样 - 但事实并非如此。删除列只会删除引用数据本身将物理保留在磁盘上。这是您可能需要执行的场景VACUUM FULL- 这可能会锁定表并完全重写它 - 可能会破坏并发迁移的目的。这让我们回到了促使我撰写本指南的原始文章 - [[The Bloat Busterspg_repack vs pg_squeeze]] 是必经之路。强烈建议提前准备并熟悉这些工具。
结论
虽然更改 PostgreSQL 中的列类型可能像发出 ALTER TABLE 命令一样简单但对于所有参与其中的人来说了解与之相关的复杂性非常重要。无论您是请求更改的软件开发人员、审核人员还是在没有仔细规划的情况下将此类更改部署到生产环境时负责解决事件的个人深入了解此过程都至关重要。此外掌握这一特定变化使您能够轻松地将洞察力投射到其他可能代价高昂的操作上。
