优化 Azure Database for PostgreSQL - 单一服务器中的批量插入和使用瞬态数据Optimize bulk inserts and use transient data on an Azure Database for PostgreSQL - Single Server

本文介绍如何优化 Azure Database for PostgreSQL 服务器中的批量插入操作以及使用瞬态数据。This article describes how you can optimize bulk insert operations and use transient data on an Azure Database for PostgreSQL server.

使用无日志记录表Use unlogged tables

如果工作负荷操作涉及到瞬态数据或者需要批量插入大型数据集,可以考虑使用无日志记录表。If you have workload operations that involve transient data or that insert large datasets in bulk, consider using unlogged tables.

无日志记录表是一项 PostgreSQL 功能,使用它能够有效地优化批量插入。Unlogged tables is a PostgreSQL feature that can be used effectively to optimize bulk inserts. PostgreSQL 使用预写日志 (WAL)。PostgreSQL uses Write-Ahead Logging (WAL). 它在默认情况下提供原子性和持久性。It provides atomicity and durability, by default. 原子性、一致性、隔离性和持久性组成了 ACID 属性。Atomicity, consistency, isolation, and durability make up the ACID properties.

插入到无日志记录表中意味着 PostgreSQL 会进行插入而不会写入到事务日志中,后者本身是 I/O 操作。Inserting into an unlogged table means that PostgreSQL does inserts without writing into the transaction log, which itself is an I/O operation. 因此,这些表远远快于普通表。As a result, these tables are considerably faster than ordinary tables.

使用以下选项可创建无日志记录表:Use the following options to create an unlogged table:

  • 使用语法 CREATE UNLOGGED TABLE <tableName> 创建新的无日志记录表。Create a new unlogged table by using the syntax CREATE UNLOGGED TABLE <tableName>.
  • 使用语法 ALTER TABLE <tableName> SET UNLOGGED 将现有日志记录表转换为无日志记录表。Convert an existing logged table to an unlogged table by using the syntax ALTER TABLE <tableName> SET UNLOGGED.

若要撤消过程,请使用语法 ALTER TABLE <tableName> SET LOGGEDTo reverse the process, use the syntax ALTER TABLE <tableName> SET LOGGED.

无日志记录表的利弊Unlogged table tradeoff

无日志记录表无法防崩溃。Unlogged tables aren't crash-safe. 发生崩溃或者执行不彻底的关机后,无日志记录表将被自动截断。An unlogged table is automatically truncated after a crash or subject to an unclean shutdown. 此外,无日志记录表的内容不会复制到备用服务器。The contents of an unlogged table also aren't replicated to standby servers. 基于无日志记录表创建的任何索引也不会自动取消记录。Any indexes created on an unlogged table are automatically unlogged as well. 插入操作完成后,将表转换为日志记录表,使插入操作具有持久性。After the insert operation completes, convert the table to logged so that the insert is durable.

在使用无日志记录表时,某些客户工作负荷可体验到大约 15-20% 的性能改善。Some customer workloads have experienced approximately a 15 percent to 20 percent performance improvement when unlogged tables were used.

后续步骤Next steps

查看工作负荷中使用的瞬态数据和批量插入。Review your workload for uses of transient data and large bulk inserts. 请参阅以下 PostgreSQL 文档:See the following PostgreSQL documentation: