零停机迁移：从PostgreSQL到PlanetScale MySQL的技术实践

本文详细介绍了如何将高流量Rails应用从PostgreSQL迁移到PlanetScale MySQL，采用双数据库策略实现零停机和持续开发。涵盖数据类型差异、索引调整、查询重写等关键技术挑战和解决方案。

从PostgreSQL迁移到PlanetScale MySQL：零停机的技术实践

快速增长迫使团队做出大胆决策。我们帮助完成了一个典型案例：将高流量Rails应用从PostgreSQL迁移到PlanetScale MySQL——无需暂停开发——并在此过程中深入了解了两种熟悉但差异巨大的数据库之间的桥梁搭建。

数据库选择是一个特别棘手的决策。早期做出的这个选择会持续尽可能长的时间，因为迁移可能极其痛苦。我们将展示Evil Martians如何帮助一个快速发展的Rails团队实现从PostgreSQL到PlanetScale MySQL的实时迁移，无需停机或开发冻结。

我们成功将750GB的生产数据从Postgres迁移到PlanetScale——零停机。

Whop决定迁移

Whop为数以千计的创作者和社区提供支持。最终，名人流量激增将其Rails应用和主数据库推至边缘。大影响力者（想象数百万订阅者）开始将他们的社区带到Whop。结果，由于这些剧烈的流量增长，Whop开始经历停机。

Whop的决定：从PostgreSQL迁移到PlanetScale MySQL。这不是简单的database.yml交换，因为这两个SQL世界虽然相似，但在关键处存在差异。

双数据库策略：保持开发速度

我们的第一个挑战不是技术性的，而是组织性的。

问题是，在我们迁移数据库期间，Whop不能冻结开发数周。新功能、错误修复和客户请求不会因为基础设施项目而暂停。我们需要一种不停止团队动力的迁移方式。

我们的解决方案：在双数据库模式下运行Rails。

这类似于Rails双启动策略的数据库版本。通过单个环境变量，整个应用程序（和测试套件）可以在PostgreSQL和MySQL之间切换。这允许Whop的开发人员继续在PostgreSQL上发布功能，同时我们并行处理MySQL兼容性。

实现围绕简单的on_mysql?辅助方法：

1
2
3
4
5
6
7

module Kernel
  if ENV["ON_MYSQL"] == "true"
    def on_mysql? = true
  else
    def on_mysql? = false
  end
end

这个布尔检查出现在代码库中数据库特定行为不同的任何地方。与其用环境变量检查污染代码，on_mysql?提供了一种清晰、可读的方式来处理数据库差异：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

default: &default
  pool: ...
  connect_timeout: ...
  adapter: <%= on_mysql? ? "trilogy" : "postgresql" %>
<% if on_mysql? %>
  schema_dump: <%= ENV["DUMP_STRUCTURE"] == "1" ? "structure.mysql.sql" : "schema.rb" %>
<% else %>
  encoding: unicode
  prepared_statements: false
  advisory_locks: false
  ...
<% end %>

在这种双数据库设置中，PostgreSQL模式保留原始structure.sql和适配器设置。

MySQL模式切换到trilogy适配器，默认使用schema.rb（当需要原始SQL时使用structure.mysql.sql）。

这种双数据库方法揭示了这些数据库之间存在多少细微差异。

例如，不区分大小写的搜索需要不同的操作符：

1
2

op = on_mysql? ? "LIKE" : "ILIKE"
scope = scope.where("resources.name #{op} ?", "%#{sanitized_query}%")

MySQL的LIKE默认不区分大小写，而PostgreSQL需要ILIKE。

同时，JSON数组操作需要完全不同的语法：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

if on_mysql?
  scope :with_roles, lambda { |roles|
    sanitized_roles = sanitize_roles(Array(roles))
    where("JSON_OVERLAPS(roles, JSON_ARRAY(#{sanitized_roles.join(',')}))")
  }
else
  scope :with_roles, lambda { |roles|
    where("roles @> ARRAY[?]::varchar[]", Array(roles))
  }
end

这种双数据库设置成为我们的迁移安全网。每个更改都可以在部署前针对两个数据库进行测试。

当问题出现时（它们总是会出现），我们可以在不影响生产的情况下调试它们。因此，开发团队继续发布功能，而我们系统性地解决兼容性问题。

模式探索：数据类型差异

随着双数据库基础设施到位，我们开始发现这些差异有多深。虽然LIKE和ILIKE足够简单易记，但真正的挑战出现在模式层。

如果你认为没有精度的DECIMAL在任何地方都有效，或者DISTINCT ON是通用SQL，你即将发现PostgreSQL一直宠坏了你。迁移到MySQL意味着面对根本性差异——从NULL的排序方式到部分索引突然不存在时会发生什么。

现在，继续前进，这是我们希望拥有的现场指南：具体的陷阱、经过测试的解决方法和保持Whop开发速度的迁移模式。

无论你是计划类似的迁移还是围绕数据库约束进行架构设计，这里都是实际会破坏的内容以及如何修复它。

数值精度：十进制觉醒

说实话：PostgreSQL宠坏了我们。在Rails迁移中，我们可以随意编写t.decimal "price"或t.decimal "balance"而不指定精度和小数位数，包括用于不同货币的货币值（其中子单位的顺序在不同货币之间可能有很大差异）。PostgreSQL只是处理它，具有慷慨的限制，与大多数现实世界需求相比感觉像无限存储空间。

然后，MySQL很快把我们带回了现实。那些随意的t.decimal声明默认为精度10，小数位数0（是的，零！），意味着你在PostgreSQL中愉快存储1234.56的balance列现在将截断为1235，让你想知道为什么财务计算突然出错了。

PostgreSQL的限制确实是天文数字：“小数点前最多131,072位数字；小数点后最多16,383位数字。“MySQL总共限制在65位数字。对大多数应用程序来说仍然慷慨，但需要你实际考虑数据。

修复方法是始终预先指定精度需求：

1

t.decimal "balance", precision: 10, scale: 2

迁移生存提示：你正处于数据库迁移中，所以不要为自己制造额外问题。

对精度和小数位数要慷慨：为大多数货币字段设置精度15和小数位数4，如果不确定甚至更高。成功完成迁移，稍后优化。拥有一个具有稍大十进制字段的工作系统比因为试图聪明地处理存储而导致迁移失败更好。

时间戳：精度再次来袭

如果你认为精度差异只是十进制问题，时间戳有自己的惊喜等待。两个数据库都支持时间戳精度，但它们有非常不同的默认值。MySQL默认为0小数秒，而PostgreSQL给你（最多）6个。

如果小数秒对你的应用程序很重要（对于审计跟踪或事件排序通常很重要），你需要明确：

1

ALTER TABLE users ADD COLUMN created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6);

JSON和数组迁移

我们的JSON故事开始足够简单：MySQL有JSON，但没有JSONB。你的JSONB列无戏剧性地迁移到普通JSON。但如果你在索引单个JSON属性，你会很快遇到MySQL的函数索引限制。

尝试在JSON函数上创建索引，MySQL会说：“不能在返回JSON或GEOMETRY值的函数上创建函数索引。”

解决方法是生成列，将JSON键提取到可索引字段中：

1
2

ALTER TABLE users ADD COLUMN data_key INT GENERATED ALWAYS AS (data->'$.key') STORED;
CREATE INDEX index_users_on_data_key ON users (data_key);

数组提出了更大的迁移难题。PostgreSQL的本机数组类型在MySQL中根本不存在——你需要将它们序列化为JSON并相应调整查询。

迁移生存提示：当准备迁移数据时，生成列似乎是完美的桥梁。两个数据库都支持它们，那么为什么不在PostgreSQL中添加生成列，将其迁移到MySQL，然后删除原始数组列？问题是：PostgreSQL只有STORED生成列，从来没有虚拟的。向大表添加存储生成列会触发全表重写——正是你在迁移期间想要避免的。相反，添加常规列并在你认为合适时计算其值。

部分索引：条件挑战

PostgreSQL的部分索引是那些优雅的WHERE子句，让你只索引关心的记录。它们在MySQL中不存在。所以，如果你一直将它们用作数据子集上的唯一约束，你将迎来创造性的问题解决会话。

非唯一部分索引很简单：删除条件并索引整个列。但唯一部分索引需要巧妙的解决方法，利用NULL在唯一约束中的行为方式。

由于所有NULL被认为是不同的，你可以通过使条件逻辑为排除的记录返回NULL来强制执行特定记录子集的唯一性：

1
2

t.index "`name`, (nullif((`status` = 'active'),false))",
        name: "index_active_users_on_name_uniq", unique: true

在MySQL中，这转化为：

1

CREATE UNIQUE INDEX index_users_on_name_uniq ON users (name, (nullif((`status` = 'active'),false)));

这是它在实践中的工作方式：

1
2
3
4
5
6
7
8

> INSERT INTO users(name, status) VALUES('John Doe', 'active');
Query OK, 1 row affected (0.002 sec)

> INSERT INTO users(name, status) VALUES('John Doe', 'active');
ERROR 1062 (23000): Duplicate entry 'John Doe-1' for key 'users.index_users_on_name_uniq'

> INSERT INTO users(name, status) VALUES('John Doe', 'inactive');
Query OK, 1 row affected (0.002 sec)

nullif()函数当两个表达式相等时返回NULL，否则返回第一个表达式。这意味着活跃用户获得非NULL值（强制执行唯一性），而非活跃用户获得NULL（允许重复）。

替代方法：使用生成列将条件逻辑移动到单独的索引字段中。对于复杂条件，这可能更清晰，但需要更多模式更改。

物化视图幻象

如果你一直依赖PostgreSQL的物化视图进行复杂聚合或报告查询，MySQL有一些令人失望的消息。MySQL FAQ直截了当地指出：

MySQL有物化视图吗？没有。你的物化视图需要新家。你有两个主要前进路径：

选项1：使用单独表自己构建。为物化数据创建专用表，并通过触发器或计划作业刷新它们。

计划方法更简单实现，但完全刷新创建了一个危险窗口，你的表在TRUNCATE和INSERT之间为空。如果你的应用程序尝试在此窗口期间读取（或更糟，缓存空结果），你将面临比数据库迁移更大的问题。

你可以通过创建另一个表，用数据填充它，然后用新表替换旧表，最后删除旧表来减少这种情况的风险。这种原子交换消除了可能破坏应用程序的空表窗口。

选项2：切换到常规视图。如果你的基础查询没有物化足够快，常规视图完全消除了刷新复杂性。彻底测试这一点。在开发数据库上运行快速的东西可能无法扩展到生产。

查询记录：语法冒险

NULL排序惊喜

就在你认为已经映射了所有差异时，NULL排序行为悄悄接近你。这不仅仅是关于缺失语法。相反，MySQL和PostgreSQL对NULL在排序结果中的位置有根本不同的看法。

缺失语法：MySQL不支持PostgreSQL的NULLS FIRST和NULLS LAST子句。

更大的问题：即使没有显式NULL定位，数据库默认对它们进行不同的排序：

PostgreSQL：NULL排序好像比任何非空值都大（DESC为NULLS FIRST，ASC为NULLS LAST）

1
2
3
4
5
6

> SELECT id, name, last_activity_on FROM users ORDER BY last_activity_on DESC;
 id |    name     | last_activity_on
----+-------------+------------------
  3 | Jane Doe Jr |
  1 | John Doe    | 2025-08-01
  2 | John Doe Jr | 2025-08-01

MySQL：NULL在ASC中排在前面，在DESC中排在最后

1
2
3
4
5
6
7
8

> SELECT id, name, last_activity_on FROM users ORDER BY last_activity_on DESC;
+----+-------------+------------------+
| id | name        | last_activity_on |
+----+-------------+------------------+
| 14 | John Doe    | 2025-08-08       |
| 15 | John Doe Jr | 2025-08-08       |
| 16 | Jane Doe    | NULL             |
+----+-------------+------------------+

迁移安全解决方案：如果NULL定位对你的应用程序很重要，你应该已经在代码中有显式NULLS FIRST/LAST。

如果没有，但你需要它们，现在不要添加它们。在两种情况下，使用Arel生成可移植SQL：

1

MyModel.order(User.arel_table[:my_column].desc.nulls_last)

这种方法确保你的代码迁移到MySQL而不破坏现有排序行为：

1
2
3
4
5
6
7

-- 强制PostgreSQL匹配MySQL的NULL放置
# SELECT id, name, last_activity_on FROM users ORDER BY last_activity_on DESC NULLS LAST;
 id |    name     | last_activity_on
----+-------------+------------------
  1 | John Doe    | 2025-08-01
  2 | John Doe Jr | 2025-08-01
  3 | Jane Doe Jr |

当你需要MySQL覆盖其默认NULL定位时，可以使用条件排序实现与PostgreSQL显式NULLS FIRST/LAST子句相同的效果：

1
2
3
4
5
6
7
8
9

-- 强制MySQL将NULL放在前面（与其默认相反）
> SELECT id, name, last_activity_on FROM users ORDER BY (last_activity_on IS NULL) DESC, last_activity_on ASC;
+----+-------------+------------------+
| id | name        | last_activity_on |
+----+-------------+------------------+
| 16 | Jane Doe    | NULL             |
| 14 | John Doe    | 2025-08-08       |
| 15 | John Doe Jr | 2025-08-08       |
+----+-------------+------------------+

DISTINCT ON：PostgreSQL派对技巧

PostgreSQL的DISTINCT ON是那些宠坏你的功能之一。它优雅地解决了Rails应用中不断出现的"每组第一条记录"问题。想要每个客户的最新订单？每个类别的最高分帖子？DISTINCT ON干净地处理它。

但MySQL根本没有DISTINCT ON。

所以，如果你一直在Rails应用中依赖这样的查询：

1
2
3

SELECT DISTINCT ON (category_id) *
FROM items
ORDER BY category_id, qty DESC;

你需要使用子查询和窗口函数重写它们。MySQL等效需要更多仪式但实现相同结果：

1
2
3
4
5
6

SELECT *
FROM (
   SELECT id, qty, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY qty DESC) as rn
   FROM items
) subquery
WHERE rn = 1;

迁移后分析

Whop团队从PostgreSQL到MySQL的迁移不仅仅是简单的数据库交换。它代表了不同数据库如何塑造应用程序架构的大师班；PostgreSQL免费提供给我们的许多功能在MySQL中需要深思熟虑的工程决策。

现在，这是真正重要的迁移清单：

  • 首先审计你的DISTINCT ON查询。这些将需要最多的重写工作。
  • 映射每个PostgreSQL数组列。JSON迁移需要规划，而不是最后一刻的发现。
  • 清点你的部分索引。子集上的唯一约束需要创造性解决方案。
  • 测试你的十进制精度假设。财务计算会静默中断。
  • 记录你的NULL排序依赖关系。不同的默认值创建细微错误。

使这次迁移成功的原因：

简而言之，团队没有对抗MySQL的设计理念。他们没有尝试复制PostgreSQL的功能集，而是调整应用程序模式以适应MySQL的优势。生成列取代了复杂的部分索引。JSON数组取代了本机数组。窗口函数取代了DISTINCT ON。

这里的真正教训是：当你接受目标数据库的世界观而不是重新创建源数据库的行为时，数据库迁移就会成功。

PostgreSQL和MySQL不仅仅是不同的语法；它们代表了解决数据问题的不同方法。双数据库模式在这里被证明是必不可少的。当你负担不起停机时间且不能停止开发时，运行并行数据库支持不仅仅是帮助，而是必要的。

你的迁移应该从一个诚实的清单开始。计算你的JSONB列，列出你的数组字段，并审计你的部分索引。技术翻译工作是可预测的；惊喜来自你忘记正在使用的功能。

结束…？

迁移代码和开发过程实际上只是通往PlanetScale道路上的第一步。接下来是数据迁移和复制的阶段，并准备在没有任何中断的情况下移动生产——但这是另一个故事，我们希望Whop有一天会讲述它！

comments powered by Disqus
© 2020 - 2025 qife
使用 Hugo 构建
主题 Stack 由 Jimmy 设计
本站总访问量次 本站访客数人次