将批量操作从 Oracle 迁移到 PostgreSQL 时的关键注意事项

亚马逊云科技 推出了 亚马逊数据库迁移加速器 (Amazon DMA)，以加快您获得 亚马逊云科技 数据库和分析服务的旅程，并实现云采用的好处，例如节省成本和提高性能。Amazon DMA 已帮助全球成千上万的客户将其工作负载（数据库和应用程序）迁移到 亚马逊云科技 数据库和分析服务。在这篇文章中，我们分享了亚马逊 DMA 在将批量数据操作从甲骨文迁移到 适用于 PostgreSQL 或 亚马逊 A urora PostgreSQL 兼容版的亚马逊关系数据库服务 (Ama z on RDS) 时的主要注意事项。

在 Oracle 中实现批量数据操作

SQL 是一种声明式数据库查询语言，而不是编程语言。但是，诸如Oracle的PL/SQL之类的数据库过程语言是编程语言，它们嵌入SQL语句以扩展其功能，这是有代价的。当 Oracle 的过程语言引擎 (PL/SQL) 处理代码块时，它会在自己的引擎中运行原生的过程语句，但是当遇到 SQL 语句时，它会将语句传递给 SQL 引擎。SQL 引擎运行 SQL 语句并将结果返回到过程语言引擎。这两个引擎之间的这种来回控制权转移被称为 上下文切换 ，它会带来一定的性能开销，并且在发生许多上下文切换时可能会导致性能急剧下降。为了缓解这种担忧，Oracle 通过将上下文切换组合在一起的功能来增强其程序语言。PL/SQL 中的 BULK COLLECT 和 FORALL 语句就是这些增强功能的示例。BULK COLLECT SELECT 语句通过一次读取即可检索多行，从而提高了批量数据检索的速度：

SELECT <column_l> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible>;

同样，PL/SQL 提供了一个 FORALL 语句，该语句使用集合批量更改行，而不是一次将更改应用于一行：

FORALL <loop_variable>in<lower range> ... <higher range> 
<DML operations>;

在本文的其余部分中，我们将使用示例用例来解释如何进行批量数据操作。该用例从包含超过 1000 万条记录的订单表中提取订单，如果订单价格大于 10,000 美元，则提供 1,000 美元的折扣，并将折扣订单插入折扣订单目标表。订单表中大约 95% 的记录定价超过 10,000 美元。

我们可以使用 BULK COLLECT、FETCH 和 FORALL 语句在 Oracle 的 PL/SQL 中实现这个用例，如以下代码片段所示：

DECLARE
CURSOR order_cursor IS SELECT * FROM orders;
TYPE order_tbl_type IS TABLE OF orders%ROWTYPE;
order_tbl order_tbl_type;

BEGIN
           OPEN order_cursor;
           FETCH order_cursor BULK COLLECT INTO order_tbl where price > 10000;
           
           FORALL i IN 1 .. order _tbl.COUNT
           INSERT INTO discounted_orders
           VALUES order_tbl(i).id, order_tbl(i).name, order.tbl(i).price - 1000); 
           CLOSE order_cursor;
           COMMIT;
END;

在 PostgreSQL 中实现批量数据操作

PostgreSQL 的 PL/pgSQL 没有等同于甲骨文 PL/SQL 功能的内置功能，例如批量收集、提取或 FORALL。但是，您可以从多种方法中进行选择，以实现相似的功能。在本节中，我们比较了四种这样的方法。这包括将过程数据库平台特定语言和声明式 SQL 语句组合使用为纯 SQL 语言。这些方法之间的权衡因性能影响和处理记录的灵活性而异。

1。循环浏览 SELECT 语句的结果

在这种方法中，我们将所有价格大于10,000美元的订单选择到游标中，循环地从光标中提取每一行并应用1,000美元的折扣，然后将记录插入目标表。在这里，上下文切换发生的次数与价格大于 10,000 美元的订单的次数一样多。参见以下代码：

DO
$$
DECLARE
order_rec record;
BEGIN
           -- Select orders into a cursor with a price > 10000
           FOR order_rec IN SELECT * FROM orders WHERE price > 10000
           -- Loop through records in the cursor to apply the discount, and insert the record into the target table
           LOOP
                    INSERT INTO discounted_orders VALUES (order_rec.id, order_rec.name, order_rec.price -1000);
           END LOOP;
END;
$$

2。使用 PostgreSQL 函数（ARRAY_AGG 和 UNNEST）

在这种方法中，我们使用了 PostgreSQL 函数 ARRAY _AGG 和 UNNEST。 ARRAY_AGG 函数用于将所需的订单记录批量提取到数组中，然后使用 UNNEST 函数批量插入获取的记录。在这种方法中，当将记录读取到数组并将更新的记录插入到目标表中时，就会发生上下文切换。参见以下代码：

DO
$$
DECLARE
tmp_orders orders[]; — orders is both a table name and a type
BEGIN
            — Fetch the records for orders with a price > 10000 into an array.
            SELECT ARRAY_AGG(orders)
            INTO tmp_orders
            FROM orders
            WHERE price > 10000;

            -- Unnest the array and insert each record into the target table.
            INSERT INTO discounted_orders
            SELECT t.id, t.name, t.price - 1000
            FROM UNNEST(tmp_orders) t;
END;
$$

与以前的方法相比，这种方法极大地减少了上下文切换的次数。这种方法的缺点是，ARRAY_AGG 和 UNNEST 操作可能无法为超大型数据集提供最佳性能。

3。仅使用 SQL 语句

在这种方法中，我们使用纯 SQL 语句编写整个代码，并完全绕过过程语言引擎。这消除了在过程语言引擎和 SQL 引擎运行之间进行上下文切换的需要，如以下代码片段所示：

-- Insert into the target table the order records whose price is > 10000
INSERT INTO discounted_orders
SELECT id, name, price - 1000
FROM orders
WHERE price > 10000;

4。将 SQL 语句用于分块的大批处理

在某些情况下，由于资源限制（例如，处理内存限制或结果集分页）等原因，纯 SQL 语句不适用于大批量记录。在这些情况下，我们可以使用 LIMIT 子句来计算以较小的增量对大批记录进行分块所需的偏移量，如以下代码片段所示：

DO
$$
DECLARE
_lmt INTEGER :=100000;
_maxID INTEGER := 0;
BEGIN
          LOOP
              INSERT INTO discounted_orders SELECT id, name, price - 1000 FROM orders WHERE
              id >= _maxID ORDER BY id LIMIT _lmt;
              EXIT WHEN NOT FOUND;
              SELECT MAX(id) + 1 INTO _maxID FROM discounted_orders;
          END LOOP;
END;
$$

比较技巧

我们可以按照从最少使用的上下文切换到大多数的顺序对这些批量操作技术进行排序：

• 使用纯 SQL 语句（如果可行）
• 将 SQL 语句用于分块的大批处理
• 如果上述方法不可行，请使用 AGG_ARRAY 和 UNNEST
• 将 SQL 语句的结果缓存在游标中并逐行处理结果

尽管减少上下文切换的数量可以帮助提高查询性能，但在评估所选方法的整体性能时，您需要考虑其他标准，例如资源限制、并行化和系统配置。为了证明这一点，Amazon DMA 使用包含 1000 万条记录的订单表对上述技术进行了性能基准分析，并观察到第四个选项的运行时间最短，并且比最慢的第一个选项快 300% 以上。下表列出了详细信息。

根据性能基准分析，我们按以下顺序对该方法进行了排名：

• 使用纯 SQL 语句（如果可行）
• 对分块的大批次使用 SQL 语句（如果我们需要将大批次分块成较小的增量）
• 使用 PostgreSQL 函数（如果纯 SQL 不可行，则使用 ARRAY_AGG 和 UNNEST）
• 循环浏览 SELECT 语句的结果（作为最后的手段，在游标中缓存 SQL 语句的结果并逐行处理结果）。

结论

在这篇文章中，我们分享了亚马逊 DMA 对适用于 PostgreSQL 的亚马逊 RDS 或亚马逊 Aurora PostgreSQL 兼容版的批量操作的关键注意事项。

Amazon DMA 提供补充咨询服务以制定迁移策略和实施计划，并使您的内部迁移团队（或亚马逊专业服务或 APN 合作伙伴，如果有）能够进行迁移实施。如果您计划将工作负载迁移到 亚马逊云科技 数据库和分析服务， 请发送电子邮件 至 DMA-sales@amazon.com 联系亚马逊 DMA 团队。

在接下来的几周内，我们将发布更多关于如何处理 PostgreSQL 中的大型对象、如何在 PostgreSQL 分区之间强制实现唯一性等的文章。敬请期待！

作者简介

Ezat Karimi 是亚马逊网络服务 (亚马逊云科技) 的高级解决方案架构师。她是数据库迁移加速器 (DMA) 团队的成员，负责帮助客户完成数据库应用程序现代化之旅。

Sharath Gopalappa 是亚马逊网络服务 (亚马逊云科技) 的高级产品经理，致力于通过亚马逊云科技数据库和分析服务帮助组织实现技术投资的现代化。