使用 psycopg2 删除索引在提交之前还是之后生效? [英] To drop an index with psycopg2 takes effect before or after commit?

问题描述

我正在做一个 python 脚本来将几个数据插入到 postgresql 数据库中.

I'm doing a python script to insert several data into a postgresql database.

遵循 postgresql 文档 以加快加载速度处理我的脚本有这种结构

Following the postgresql documentation in order to speed up the load process my script has this sort of structure

• 连接到数据库并创建一个游标
• 删除所有索引
• 使用复制"命令加载所有数据
• 重新创建所有索引
• 提交和关闭游标和连接(仅在整个脚本中提交)

所以我的问题是:在提交之前删除索引是否对加速加载有任何影响?

So my question is: Is dropping the indexes before the commit, taking any effect in terms of speeding up the loading, or not ?

推荐答案

commit 只是将任何正在进行的事务提交到您的数据库.

commit just commits any ongoing transaction in progress to your database.

您实际上要问的是，删除索引然后在同一事务中复制是否会提供与先删除一个事务中的索引然后复制新事务中的数据相同的加速.

What you actually are asking if whether dropping indexes and then copying within the same transaction will provide the same speedup as first dropping indexes in one transaction and then copying data in a new transaction.

来自 docs 的直接引用说:

The direct quote from docs says that:

如果您要向现有表中添加大量数据，删除索引、加载表然后重新创建索引可能会更好.当然，在索引丢失期间，其他用户的数据库性能可能会受到影响. 在删除唯一索引之前还应该三思，因为唯一约束提供的错误检查将丢失索引丢失.

If you are adding large amounts of data to an existing table, it might be a win to drop the indexes, load the table, and then recreate the indexes. Of course, the database performance for other users might suffer during the time the indexes are missing. One should also think twice before dropping a unique index, since the error checking afforded by the unique constraint will be lost while the index is missing.

粗体部分间接告诉你应该在删除索引后提交，因为删除索引而不提交(完成事务)不应该对数据库的其他用户产生任何影响.

The bolded part indirectly tells that you should commit after dropping the indexes, as dropping indexes without committing (completing the transaction) should not have any impact on other users of the database.

所以解决方案应该是这样的:

So the solution should be something along these lines:

删除索引、提交、复制数据、创建新索引并再次提交.

drop your indexes, commit, copy the data, create new indexes and commit again.

请注意，将事务拆分为两个事务时，会失去原子性.IE.您的索引可能会被删除，但没有复制任何数据(例如，如果在复制事务期间电源或网络丢失)，并且永远不会重新创建索引.

Note that as you split your transaction into two transactions, you lose atomicity. I.e. it's possible that your indexes are dropped, but no data is copied (if power or network for example is lost during the copying transaction) and the indexes would never be recreated.

这篇关于使用 psycopg2 删除索引在提交之前还是之后生效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！