在适用于 PostgreSQL 的亚马逊 RDS 和亚马逊 Aurora PostgreSQL 中使用原生命令对现有表进行分区

作者： Dee p Dey | 2022 日

客户使用 适用于 PostgreSQL 的 亚马逊关系数据库服务 (Amazon RDS) 和 兼容 Amazon Aurora PostgreSQL 的版本 来托管他们的在线事务处理 (OLTP) 数据库 工作负载。考虑到当今应用程序的运行规模，数据库可以在很短的时间内增长到数百太字节。数据库的大小之所以增加，是因为数据库存储的大部分份额被几个高度活跃的大型表所占据。这些大型表在操作上变得难以管理并导致性能问题。最终，对这些表进行分区以提高可管理性并减少运营和性能开销变得势在必行。

对表进行分区可以在很多方面提供帮助，例如使用 DDL DETACH 命令 更轻松地归档旧数据、使用 分区修剪 提高查询性能 、通过在选择性活动分区 上运行表来缩短 VACUUM 时间等等。但是，当您想要减少停机时间时，对现有表进行分区需要特殊的方法。有几种方法可以对非分区表进行分区，缩短停机时间： 用于对现有表进行分区的 亚马逊云科技 数据库迁移服务 (亚马逊云科技 DMS) ，或者 PostgreSQL 原 生 SQL 命令。

在这篇文章中，我将向您展示如何使用 PostgreSQL 原生 SQL 命令将现有的非分区表转换为分区表。此方法适用于适用于 PostgreSQL 的亚马逊 RDS、Aurora PostgreSQL 和自行管理的 PostgreSQL。

概述

要对现有的大表进行分区，我们使用以下高级步骤：

  1. 在现有表上创建并验证检查约束。
  2. 重命名现有表及其相应的索引，并使用原始表的名称创建一个新的分区表。
  3. 将旧表附加为新创建的分区表的分区。
  4. 删除检查约束。
  5. 将数据从作为分区附加的旧表移到新表中的多个较小分区。

先决条件

在本演练中，您应该具备以下先决条件：

  • 一个 亚马逊云科技 账户 。
  • 适用于 PostgreSQL 的亚马逊 RDS（版本 13.8）数据库实例或 Aurora PostgreSQL（版本 13.8）集群 — 创建 RDS for PostgreSQL 数据库实例或创建 Aurora PostgreSQL 集群。
  • 亚马逊 Simple Storage Servic e (Amazon S3) 存储桶。
  • 了解 PostgreSQL 分区以及相应的 SQL 命令和 PostgreSQL 锁。

完成以下步骤以准备您的环境。对于这种情况，我创建了一个非分区的示例表 car_dat a。现在，让我们使用本地命令将此表转换为同名的分区表。

  1. 连接到 RDS for PostgreSQL 数据库实例 或 连接到在先决条件步骤中 创建的 Aurora PostgreSQL 集群 。
  2. 使用以下命令创建非分区表和索引： 
CREATE TABLE car_data (email VARCHAR(100),
name VARCHAR(60),
car_make VARCHAR(40),
car_model VARCHAR(40),
date_of_purchase DATE,
car_model_year INT,
car_price DECIMAL(11,2),
city_of_purchase VARCHAR(30),
country_of_purchase VARCHAR(40),
car_color VARCHAR(20),
car_vin VARCHAR(40));

CREATE INDEX ix_email ON car_data(email);
CREATE INDEX ix_city_of_purchase ON car_data(city_of_purchase);

在现有表上创建检查约束

定义一个检查约束，当分区作为分区附加到新表时，该分区边界应与旧表的分区边界相匹配。必须在用作新表分区键的列上定义检查约束。检查约束的目的是确保在将旧表作为分区附加时，避免进行表扫描，这是验证分区边界所必需的。

因为我们使用 date_of_purch ase 作为分区键，所以我们会确定它的最小值和最大值以相应地定义检查约束。在这篇文章中，我们得到以下输出： 

SELECT MIN(date_of_purchase) FROM car_data;
min
------------
2000-06-25

SELECT MAX(date_of_purchase) FROM car_data;
max
------------
2022-06-23

使用以下代码使用在 date_of_purchase 的输出中找到的日期 范围创建检查约束： 

ALTER TABLE car_data ADD CONSTRAINT car_data_check_constraint CHECK (date_of_purchase >= DATE ‘2000-06-01’ AND date_of_purchase < DATE ‘2022-07-01’) NOT VALID;

我们使用 NOT VALID 选项来避免为约束验证而进行昂贵的表扫描。因为我们已经有了分区键的最小值和最大值，所以不需要任何额外的强制器来执行检查约束。

验证现有表的检查约束

使用 NOT VALID 运算符创建约束 条件后，运行 VALID ATE CONSTRAINT 语句，以便在将表附加为分区时接受该约束。 

ALTER TABLE car_data VALIDATE CONSTRAINT car_data_check_constraint;

此命令获得 共享更新专属 锁（你可以在 PostgreSQL 文档中找到更多细节—— 验证约束 和 共享更新专属 锁），它允许你运行常规操作。

在运行 V ALIDATE CONSTRAINT 命令时，您仍然可以使用 UPDATE、INSERT、DELETE 和 SELECT 语句运行常规工作负载，如以下示例所示。 

mytestdb=> select a.locktype, a.relation,a.mode, b.relname, b.oid from pg_locks a, pg_class b where  a.relation=b.oid and b.oid=24657;
 locktype | relation |           mode           |   relname    |  oid  
----------+----------+--------------------------+--------------+-------
 relation |    24657 | ShareUpdateExclusiveLock | car_data     | 24657
(1 row)

mytestdb=> select sr_no, model, make, model_year from car_data limit 5;
 sr_no | model  |   make   | model_year 
-------+--------+----------+------------
     2 | BMW    | M5       |       2001
     3 | Toyota | Prius    |       2007
     4 | Ford   | F350     |       2002
     5 | Buick  | LaCrosse |       2005
     6 | Subaru | Outback  |       2008

mytestdb=> update car_data set model_year=2004 where sr_no=2 and model='BMW' and make='M5';
UPDATE 1

mytestdb=> insert into car_data (sr_no, model, make, model_year) values (2004,'Safari','Tata',2004);
INSERT 0 1

mytestdb=> select sr_no, model, make, model_year from car_data where sr_no=2 and model='BMW';
 sr_no | model | make | model_year 
-------+-------+------+------------
     2 | BMW   | M5   |       2004

mytestdb=> select sr_no, model, make, model_year from car_data where sr_no=2004 and model='Safari';
 sr_no | model  | make | model_year 
-------+--------+------+------------
  2004 | Safari | Tata |       2004

mytestdb=> delete from car_data where sr_no=2004 and model='Safari';
DELETE 1

mytestdb=> select sr_no, model, make, model_year from car_data where sr_no=2004 and model='Safari';
 sr_no | model | make | model_year 
-------+-------+------+------------
(0 rows)

重命名现有表和索引并创建新的分区表

第一步是，重命名现有表及其相应的索引，使用可以接受新数据的分区创建同名的新分区表，并根据需要创建索引。此外，您还可以在分区列上添加索引。而且，您在单个事务中执行所有这些操作，因为这些步骤之间存在相互依赖性。

在此操作期间，该表在整个事务时间内都不可访问，因为 ALTER TABLE 会使用访问专属锁，这会导致一些停机。请参阅以下 SQL 语句： 

BEGIN TRANSACTION;

ALTER TABLE car_data RENAME TO car_data_old;

ALTER TABLE ix_email RENAME TO ix_email_old;

ALTER TABLE ix_city_of_purchase RENAME TO ix_city_of_purchase_old;

CREATE TABLE car_data (email VARCHAR(100),
name VARCHAR(60),
car_make VARCHAR(40),
car_model VARCHAR(40),
date_of_purchase DATE,
car_model_year INT,
car_price DECIMAL(11,2),
city_of_purchase VARCHAR(30),
country_of_purchase VARCHAR(40),
car_color VARCHAR(20),
car_vin VARCHAR(40))
PARTITION BY RANGE (date_of_purchase);

CREATE INDEX ix_email ON car_data(email);

CREATE INDEX ix_city_of_purchase ON car_data(city_of_purchase);

CREATE INDEX ix_date_of_purchase ON car_data(date_of_purchase);

CREATE TABLE car_data_p202207 PARTITION OF car_data FOR VALUES FROM (‘2022-07-01’) TO (‘2022-08-01’);

COMMIT;

将旧表作为分区附加到新表中

将重命名的表 car_data_old 附加 为 car_dat a 的分区。 此操作在主表 car_data 上使用共享更新独占锁，在所连接的表 (car_ data_old ) 上使用访问独占锁，从而使其不可用于读取 和写入。参见以下代码： 

ALTER TABLE car_data ATTACH PARTITION car_data_old FOR VALUES FROM (‘2000-06-01’) TO (‘2022-07-01’);

如果没有检查约束，此步骤可能需要更长的时间，因为它会扫描整个 c ar_data_old 表以验证分区边界。

删除检查约束

因为你不需要它，所以删除你创建的检查约束： 

ALTER TABLE car_data_old DROP CONSTRAINT car_data_check_constraint;

将数据从大分区移动到较小的分区

因为我们将一个大表作为单个分区附加到新表中，所以应该将其分成较小的分区。您可以按照自己的节奏逐步将数据从这个大分区移动到较小的分区。这应该在表上没有业务活动或更少的业务活动时运行，这样数据移动、高 I/O 和锁定对活动查询的影响就会降低。您必须使用事务分步完成此过程，如以下步骤所述，以及相应的 SQL 代码：

  1. 开始交易。 
    
开始交易；
  2. 分离步骤中附加的旧表分区将 旧表作为分区 附加到新表 中 。 
    
更改表 car_data 分离分区 car_data_old；
  3. 为将从上面分离的旧表中移出的数据创建一个新分区。例如，为值介于 2022-06-01 到 2022-07-01 之间的记录创建分区。 
    
为从 ('2022-06-01') 到 ('2022-07-01') 之间的值创建表 car_data_p202206 car_data_p202206 分区；
  4. 使用 INSERT INTO SELECT 语句从旧表中填充上面在步骤 3 中创建的分区。 
    
在 car_data_p202206 中插入从 car_data_old 中选择* 其中 date_of_purchase >= '2022-06-01' 和 date_of_purchase < '2022-07-01'；
  5. 删除已从旧表移至新分区的记录。 
    
从 car_data_old 中删除，其中 date_of_purchase >= '2022-06-01' 和 date_of_purchase < '2022-07-01'；
  6. 添加检查约束条件以避免在再次连接分区时进行表扫描。 
    
更改表 car_data_old 添加约束条件 car_data_check_constraint CHECK（date_of_purchase >= 日期 '2000-06-01' 和 date_of_purchase < 日期 '2022-06-01'）无效；
  7. 验证在上一步中创建的 CHECK 约束。 
    
更改表 car_data_old 验证约束 car_data_check_constraint；
  8. 将旧表及其余记录作为分区附加到新表中。 
    
将从 ('2000-06-01') 到 ('2022-06-01') 之间的值修改表格 car_data_old 分区 car_data_old；
  9. 删除该约束，因为它不再需要了。 
    
更改表 car_data_old 删除约束条件 car_data_check_constraint；
  10. 完成交易。 
    
提交；

分离分区 car_data_old 后 ，表 c ar_data 将仅包含来自先前定义 的新分区（从 2022-07-01 到 2022-08-01）的数据。请注意，再次附加旧表时，分区边界已更改。这是因为一些数据已移至新的分区 car_data_p 202206。此外，每次运行此步骤时都必须定义检查约束条件并对其进行验证，因为这样可以避免每次定义新的分区边界时进行表扫描。继续执行此步骤，直到包含旧表的整个分区被划分为较小的分区。

清理

完成实施此解决方案的所有步骤后， 删除 RDS 数据库实例 或 删除 Aurora 数据库集群 。

结论

在这篇文章中，我向您展示了如何使用 PostgreSQL 原生命令将现有的非分区表迁移到停机时间最少的分区表。表可能不可用，因为在重命名和分区过程中运行 ALTER 命令时会被锁定。当我们定义检查约束时，通过跳过将表作为分区附加时所需的昂贵表扫描，可以大大减少分区期间的停机时间。确保使用前面提到的在事务中运行的命令，否则您将无法达到预期的结果。

最后，我鼓励你自己测试这个过程，完成后清理资源。如果您有任何问题或建议，请将其留在评论部分。

作者简介

Soumyadeep Dey 是 亚马逊云科技 的数据库专家解决方案架构师。他与 亚马逊云科技 客户合作，使用 SQL 和 NoSQL 数据库产品在云上设计可扩展、高性能和强大的数据库架构。

*前述特定亚马逊云科技生成式人工智能相关的服务仅在亚马逊云科技海外区域可用，亚马逊云科技中国仅为帮助您发展海外业务和/或了解行业前沿技术选择推荐该服务。