如何通过变更让 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 过程可以总结为以下几个步骤:
- 在目标表中添加一个新的列,并确保其类型正确。确保该列可为空且没有默认值,以避免全表重写。(更正:自 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 命令。然而,对于涉及的每一个人来说,理解这一操作的复杂性至关重要。无论你是提出变更请求的软件开发者、负责审查此变更的人,还是在这类更改部署到生产环境后负责解决相关问题的技术人员,深刻理解这个过程都非常关键。此外,了解这种具体的变更还可以帮助你更容易地对其他可能代价高昂的操作有所预见。
相关文章:
如何通过变更让 PostgreSQL 翻车
在开发应用程序和维护其后台数据库集群的过程中,我们经常会遇到实践与理论、开发环境与生产环境之间的差异。其中一个典型的例子就是变更数据库中的列类型。 对于在 PostgreSQL(及其他符合 SQL 标准的系统)中变更列类型的常规操作࿰…...

MySQL表涉及规范
MySQL表设计规范是为了确保数据库表结构的合理性、可读性和可维护性。以下是一些建议和规范: 1.使用InnoDB存储引擎:InnoDB存储引擎提供了事务支持、行级锁定和外键约束,有助于提高数据的完整性和性能。 2.表名和字段名命名规范:…...

服务器Ubuntu22.04系统 使用dcocker部署安装ollama和搭配open_webui使用
服务器Ubuntu22.04系统 使用dcocker部署安装ollama和搭配open_webui使用 一、ubuntu和docker基本环境配置 1.更新包列表: 打开终端,输入以下命令: sudo apt-get updatesudo apt upgrade更新时间较长,请耐心等待 2. 安装docke…...

代理模式Proxy
一、代理模式(Proxy) 1.代理模式的定义 代理模式给某一个对象提供一个代理对象,并由代理对象控制对真实对象的访问,起到对代理对象已有功能的增强 通俗的来讲代理模式就是我们生活中常见的中介。 2.作用 中介隔离作用&#x…...

C++ 设计模式——抽象工厂模式
抽象工厂模式 抽象工厂模式 抽象工厂模式主要组成部分代码实现抽象工厂模式模式的 UML 图抽象工厂模式 UML 图解析优点和缺点适用场景 抽象工厂模式提供一个接口,用于创建一系列相关或相互依赖的对象,而无需指定它们的具体类。它通常用于需要创建多个产品…...

《亿级流量系统架构设计与实战》第十一章 Timeline Feed服务
Timeline Feed服务 一、概述1、分类2、功能 二、设计原理1、拉模式与用户发件箱2、推模式与用户收件箱3、推拉模式结合 三、关键技术1、内容与用户收件箱的交互(推模式)2、推送拆分子任务3、收件箱模型设计 内容总结自《亿级流量系统架构设计与实战》 一…...

氙灯老化试验箱试验机
氙灯老化试验箱,采用6.5KW大功率的精密水冷式氙灯,曝晒面积达到了6500cm2 功能强大,测试结果可靠 ◆ 满足国内外所有氙灯测试标准要求。 ◆ 采用氙灯灯管及滤光器组件,保证试验数据的可比性和重现性。 ◆ 自动旋转式三层鼓型样板架…...

【Qt】常用控件QRadioButton
常用控件QRadioButton QRadioButton是单选按钮,可以在多个选项中选择一个。 作为QAbstractButton和QWidget的子类,其属性和用法,对于QRadioButton同样适用。 属性说明 checkable 是否能选中 checked 是否已经被选中. checkable 是 checked…...

Mysql 离线版下载安装-(详细版)
Mysql 离线版下载安装-(详细版) 文章目录 Mysql 离线版下载安装-(详细版)1.0 下载地址2.0 解压到本地2.0.1 配置环境变量2.0.2 新建mysql配置文件ini2.0.3使用管理员启动 cmd 3.0 初始化密码忘记了4.0 修改初始化密码5.0 使用可视化工具登录Mysql 1.0 下载地址 地址࿱…...

Spring Boot和OCR构建车牌识别系统
博客主页: 南来_北往 系列专栏:Spring Boot实战 OCR介绍 OCR(Optical Character Recognition)是光学字符识别技术的缩写,它能够将图像中的文本转换为机器可读和编辑的数字文本格式。这种技术广泛应用于数据输入、文档管理…...

Java-自定义注解中成员变量是Class<?>
在Java中,自定义注解可以包含各种类型的成员变量,包括 Class<?> 类型。这种类型的成员变量 通常用于表示某个类的类型信息。下面我将详细介绍如何定义一个包含 Class<?> 类型成员变量的 自定义注解,并给出一些示例代码。 1. 定义自定义注解 定义一个自定义…...
SX_UNIX套接字通信_15
UNIX套接字通信的优势: UNIX套接字通信常用于一个项目中的进程之间通信,UNIX提供了与网络套接字相似的特性,但是避免了网络延迟,提高了性能,但是它只能在同一台机器上使用,无法跨越网络的进程间通信 实例&…...

JS模块化总结 | CommonJS、ES6
BV13W42197jR 个人笔记 目录 JS模块化基础知识1. 概述1.1 什么是模块化1.2 为什么需要模块化? 2 模块化规范3 导入&导出4 CommonJS规范4.1 初步体验4.2 导出数据4.3 导入数据4.4 扩展理解4.5 浏览器端运行 5 ES6模块化规范5.1 初步体验5.2 Node中运行ES65.3 导出数据①分别…...
25考研计算机组成原理复习·3.5高速缓冲存储器
高速缓冲存储器Cache 工作原理:将某些主存块复制到Cache中,缓和CPU与主存之间的速度矛盾局部性原理 时间局部性:现在访问的地址,不久之后也很可能被再次访问空间局部性:现在访问的地址,其附近的地址也很可…...

餐厅管理系统
目录 一、 系统简介 1.1需求分析 1.2 编程环境与工具 二、 系统总体设计 2.1 系统的功能模块图。 2.2 各功能模块简介。 三、 主要业务流程 (1)用户及管理员登录流程图 (2)信息添加流程 (3…...
杭州百腾教育科技 TiDB 6.5 to 7.5 升级记录
作者: reAsOn2010 原文来源: https://tidb.net/blog/612103f3 背景 使用 TiDB 作为我们的全量数据库已经有六七年了,当时还是 2.0 版本。早期TiDB的迭代和新特性的发布对于实际使用的影响还是很大的,所以从那个时候开始就有每…...
Redis的缓存穿透、击穿、雪崩
目录 缓存穿透 定义: 解决方法: 缓存击穿 定义: 解决方案: 缓存雪崩 定义: 解决方案: 缓存穿透、缓存击穿和缓存雪崩的区别 缓存穿透 定义: 查询一个不存在的数据,数据库未…...

【Django开发】前后端分离django美多商城项目第1篇:欢迎来到美多 项目主要页面介绍【附代码文档】
本教程的知识点为: 项目准备 项目准备 配置 1. 修改settings/dev.py 文件中的路径信息 2. INSTALLED_APPS 3. 数据库 用户部分 图片 1. 后端接口设计: 视图原型 2. 具体视图实现 用户部分 使用Celery完成发送 判断帐号是否存在 1. 判断用户名是否存在 后…...
【软件造价咨询】信息化项目预算评审看什么?
在信息化项目预算评审中,各方往往只重视预算金额部分,而忽视了项目建设的全局性和整体性把关,导致信息系统的重复建设、分散建设、业务和系统两张皮、重功能轻数据、重投资轻方案等问题频出,从而大幅降低财政投资效益。 例如&…...

第37讲:Cephfs文件系统的正确使用姿势
文章目录 1.Cephfs文件系统简介2.Cephfs文件系统细节介绍2.1.Cephfs文件系统多客户端隔离挂载2.2.Ceph集群中多个Cephfs如何单独使用 3.挂载多个Cephfs文件系统4.Cephfs文件系统多客户端隔离挂载实战4.1.创建一个Cephfs文件系统4.2.将Cephfs文件系统挂载到本地路径4.3.在Cephfs…...

手游刚开服就被攻击怎么办?如何防御DDoS?
开服初期是手游最脆弱的阶段,极易成为DDoS攻击的目标。一旦遭遇攻击,可能导致服务器瘫痪、玩家流失,甚至造成巨大经济损失。本文为开发者提供一套简洁有效的应急与防御方案,帮助快速应对并构建长期防护体系。 一、遭遇攻击的紧急应…...
【位运算】消失的两个数字(hard)
消失的两个数字(hard) 题⽬描述:解法(位运算):Java 算法代码:更简便代码 题⽬链接:⾯试题 17.19. 消失的两个数字 题⽬描述: 给定⼀个数组,包含从 1 到 N 所有…...
【解密LSTM、GRU如何解决传统RNN梯度消失问题】
解密LSTM与GRU:如何让RNN变得更聪明? 在深度学习的世界里,循环神经网络(RNN)以其卓越的序列数据处理能力广泛应用于自然语言处理、时间序列预测等领域。然而,传统RNN存在的一个严重问题——梯度消失&#…...
什么是EULA和DPA
文章目录 EULA(End User License Agreement)DPA(Data Protection Agreement)一、定义与背景二、核心内容三、法律效力与责任四、实际应用与意义 EULA(End User License Agreement) 定义: EULA即…...

PL0语法,分析器实现!
简介 PL/0 是一种简单的编程语言,通常用于教学编译原理。它的语法结构清晰,功能包括常量定义、变量声明、过程(子程序)定义以及基本的控制结构(如条件语句和循环语句)。 PL/0 语法规范 PL/0 是一种教学用的小型编程语言,由 Niklaus Wirth 设计,用于展示编译原理的核…...
C# SqlSugar:依赖注入与仓储模式实践
C# SqlSugar:依赖注入与仓储模式实践 在 C# 的应用开发中,数据库操作是必不可少的环节。为了让数据访问层更加简洁、高效且易于维护,许多开发者会选择成熟的 ORM(对象关系映射)框架,SqlSugar 就是其中备受…...
JVM暂停(Stop-The-World,STW)的原因分类及对应排查方案
JVM暂停(Stop-The-World,STW)的完整原因分类及对应排查方案,结合JVM运行机制和常见故障场景整理而成: 一、GC相关暂停 1. 安全点(Safepoint)阻塞 现象:JVM暂停但无GC日志,日志显示No GCs detected。原因:JVM等待所有线程进入安全点(如…...

让回归模型不再被异常值“带跑偏“,MSE和Cauchy损失函数在噪声数据环境下的实战对比
在机器学习的回归分析中,损失函数的选择对模型性能具有决定性影响。均方误差(MSE)作为经典的损失函数,在处理干净数据时表现优异,但在面对包含异常值的噪声数据时,其对大误差的二次惩罚机制往往导致模型参数…...
Python+ZeroMQ实战:智能车辆状态监控与模拟模式自动切换
目录 关键点 技术实现1 技术实现2 摘要: 本文将介绍如何利用Python和ZeroMQ消息队列构建一个智能车辆状态监控系统。系统能够根据时间策略自动切换驾驶模式(自动驾驶、人工驾驶、远程驾驶、主动安全),并通过实时消息推送更新车…...
WebRTC从入门到实践 - 零基础教程
WebRTC从入门到实践 - 零基础教程 目录 WebRTC简介 基础概念 工作原理 开发环境搭建 基础实践 三个实战案例 常见问题解答 1. WebRTC简介 1.1 什么是WebRTC? WebRTC(Web Real-Time Communication)是一个支持网页浏览器进行实时语音…...