PostgreSQL中的最大事务大小

Question

我的应用程序中有一个实用工具，需要执行批量插入、更新和删除操作。我试图在这些操作的周围创建事务，以便一旦调用此系统并将数据提供给它，就确保将所有数据全部添加到数据库中或全部不添加。

我的担忧是什么时候会出现边界条件？我可以在一个事务中有多少个插入、更新和删除操作？事务大小是否可配置？

- Salman

Answer 1

我认为在一个事务中执行的工作量没有上限。数据不断被添加到表文件中，最终事务要么提交要么回滚：根据我的理解，这个结果会被存储在pg_clog中；如果它回滚了，这个空间最终会被vacuum回收。因此，进行中的事务工作并不会在提交时被保留在内存中并被刷新。

- araqnid

---

这只是部分正确的。在每个事务内部都有一个命令计数器，处理事务内的可见性。这是一个32位数字，如果您有非常大的事务（数十亿个命令），它将最终溢出。VACUUM、pg_clog等仅处理系统中的总事务数，而不涉及其中任何一个事务的内部情况。 - Magnus Hagander

@MagnusHagander 这个32位数还有效吗？这个数字已经更新为64位了吗？ - Kuberchaun

如果我正确理解了您的澄清，那么在v10之前（现在是pg_xact），pg_clog仅包含事务元数据，如命令计数器，而实际数据无论事务是否已提交，仍最终存储在pg_xlog/pg_wal中。这就是我在我的快速测试中看到的情况。这个答案让我觉得数据只有在提交后才会移动到wal文件中。 - atomic77

因此，在我的.sql文件中，我可以这样做：- begin;（开始事务），随后是大量插入SQL语句（大约有200万个插入语句）；commit;（提交事务）。 - firstpostcommenter

Answer 2

一次事务可以运行大约20亿条命令（2^31，减去一点点开销。想一想，实际上可能是2^32——我认为commandcounter是无符号的）。

当然，每个命令都可以修改多行。

- Magnus Hagander

Answer 3

我参与的一个项目需要执行2000万次INSERT操作。我尝试过使用一个大事务和每一百万次INSERT操作使用一个单独的事务，但是性能表现完全相同。

使用的数据库版本是PostgreSQL 8.3。

- bortzmeyer

---

系统是本地的吗？我认为在延迟是一个因素的系统上进行此操作，性能会有所不同。 - user2677679

你的程序性能没有区别。但其他用户的表现如何呢？ - ceving

Answer 4

我相信最大的工作量受到日志文件大小的限制。数据库永远不会允许自己无法回滚，因此，如果在事务期间消耗了所有的日志空间，它将停止，直到您提供更多的空间或回滚。这通常适用于所有数据库。

我建议将更新划分为可管理的块，执行时间最长不超过几分钟，这样您就可以在更早的时间发现问题（例如，通常需要1分钟的操作在10分钟后仍在运行...嗯，是有人删除了索引吗？）

- Glen

---

这并不适用于PostgreSQL。我们可以在运行事务期间回收日志空间。如果你使用归档日志记录，显然需要存储归档位置的空间，但对于本地事务日志，则不需要（当然，你需要为磁盘上的数据提供实际的磁盘空间）。 - Magnus Hagander