PostgreSQL 中的最大事务大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/709708/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Maximum transaction size in PostgreSQL
提问by Salman A. Kagzi
I have a utility in my application where i need to perform bulk load of INSERT, UPDATE & DELETE operations. I am trying to create transaction around this so that once this system is invoke and the data is fed to it, it is ensured that it is either all or none added to the database.
我的应用程序中有一个实用程序，我需要在其中执行 INSERT、UPDATE 和 DELETE 操作的批量加载。我正在尝试围绕此创建事务，以便一旦调用该系统并将数据馈送到它，就可以确保将其全部或不添加到数据库中。
The concern what is have is what is the boundary conditions here? How many INSERT, UPDATE & DELETE can i have in one transaction? Is transaction size configurable?
所关心的是这里的边界条件是什么？一笔交易中可以有多少 INSERT、UPDATE 和 DELETE？交易大小是否可配置？
采纳答案by araqnid
I don't think there's a maximum amount of work that can be performed in a transaction. Data keeps getting added to the table files, and eventually the transaction either commits or rolls backs: AIUI this result gets stored in pg_clog; if it rolls back, the space will eventually be reclaimed by vacuum. So it's not as if the ongoing transaction work is held in memory and flushed at commit time, for instance.
我不认为在事务中可以执行的工作量是最大的。数据不断添加到表文件中，最终事务要么提交要么回滚：AIUI 这个结果被存储在 pg_clog 中；如果它回滚，空间最终会被真空回收。因此，例如，正在进行的事务工作并不是保存在内存中并在提交时刷新。
回答by Magnus Hagander
A single transaction can run approximately two billion commands in it (2^31, minus IIRC a tiny bit of overhead. Actually, come to think of it, that may be 2^32 - the commandcounter is unsigned I think).
单个事务可以在其中运行大约 20 亿个命令（2^31，减去 IIRC 一点点开销。实际上，想想看，这可能是 2^32 - 我认为命令计数器是无符号的）。
Each of those commands can modify multiple rows, of course.
当然，这些命令中的每一个都可以修改多行。
回答by bortzmeyer
For a project I work on, I perform 20 millions of INSERT. I tried with one big transaction and with one transaction for every million of INSERT and the performances seem exactly the same.
对于我从事的一个项目，我执行了 2000 万次 INSERT。我尝试过一次大交易，每百万次 INSERT 一次交易，性能似乎完全一样。
PostgreSQL 8.3
PostgreSQL 8.3
回答by bortzmeyer
I believe the maximum amount of work is limited by your log file size. The database will never allow itself to not be able to rollback, so if you consume all your log space during the transaction, it will halt until you give it more space or rollback. This is a generally true for all databases.
我相信最大工作量受日志文件大小的限制。数据库永远不会允许自己无法回滚，因此如果您在事务期间消耗了所有日志空间，它将停止，直到您给它更多空间或回滚。对于所有数据库来说，这通常是正确的。
I would recommend chunking your updates into manageable chunks that take a most a couple of minutes of execution time, that way you know if there's a problem earlier (eg what normally takes 1 minute is still running after 10 minutes... hmmm, did someone drop an index?)
我建议将您的更新分块到可管理的块中，最多需要几分钟的执行时间，这样您就可以提前知道是否存在问题（例如，通常需要 1 分钟的内容在 10 分钟后仍在运行......嗯，有人吗？删除索引？）
