如何使用PK和值的元组有效地更新postgres？ [英] How to efficiently update postgres using a tuple of the PK and a value?

问题描述

以下是我的SCHEMA，并且数据库中有〜4m个现有帖子需要更新。我正在添加一个指向文本位置的整数。

My SCHEMA is the following and I have ~ 4m existing posts in the DB that I need to update. I am adding an integer which points to a text location.

CREATE TABLE app_post (
id integer NOT NULL,
text_location integer,
title character varying(140)
);

我想用表示（id的长元组列表（1000-5000）来更新现有记录，text_location）：

I want to update existing records with a long (1000-5000) list of tuples that represent (id, text_location):

[(1, 123), (2,3), (9, 10)....]

最有效的方法是什么？

推荐答案

如果使用phyton即时生成值，则可以：

If you are generating the values on the fly using phyton, you could:

1. 创建一个包含单个 INSERT 语句的缓冲区


2. 开始交易


3. 创建一个临时表并在缓冲区中执行 INSERT 语句


4. 执行 UPDATE ... FROM


5. 提交事务，丢弃临时表。

1. Create a buffer containing a single INSERT statement
2. Start a transaction
3. Create a temporary table and perform the INSERT statement in your buffer
4. Perform an UPDATE ... FROM
5. Commit the transaction, discarding the temporary table.

UPDATE 语句看起来像这样（假设有一个表 new_values 包含您需要更新的那些新值）：

The UPDATE statement will look like this (assuming there is a table new_values containing those new values you need to update):

UPDATE app_post AS a SET text_location = n.text_location 
    FROM new_values AS n WHERE a.id = n.id

别忘了将 id 列定义为 PRIMARY KEY 或在其上创建索引。

Don't forget to define the columns id as PRIMARY KEY or create an index on them.

编辑：由于您的性能非常慢，因此另一个解决方法是重新创建整个表。下面的想法假定您没有对 app_post 表应用任何 FOREIGN KEY 约束

EDIT : Since you are experiencing very slow performance, another workaround could be to recreate the whole table. The following idea assumes you don't have any FOREIGN KEY constraint applied to app_post table, as you have shown in your initial post.

-- Begin the Transaction
BEGIN;

-- Create a temporary table to hold the new values
CREATE TEMPORARY TABLE temp_update_values (
    id integer PRIMARY KEY,
    text_location integer
) ON COMMIT DROP;

-- Populate it
INSERT INTO temp_update_values (id, text_location) VALUES (1, 123), (2, 456) /* ... #5000 total */ ;

-- Create a temporary table merging the existing "app_post" and "temp_update_values"
CREATE TEMPORARY TABLE temp_new_app_post ON COMMIT DROP AS 
    SELECT a.id, COALESCE(n.text_location, a.text_location) AS text_location, a.title 
    FROM app_post AS a LEFT JOIN temp_update_values AS n ON a.id = n.id;

-- Empty the existing "app_post"
TRUNCATE TABLE app_post;

-- Repopulate "app_post" table 
INSERT INTO app_post (id, text_location, title) 
    SELECT id, text_location, title FROM temp_new_app_post;

-- Commit the Transaction
COMMIT;

如果存在任何 FOREIGN KEY 约束，您应该照顾好它们，在截断 app_post 表之前将其删除，并在重新填充后重新创建它们。

If there are any FOREIGN KEY constraint, you should take care of them, dropping them before TRUNCATING the app_post table, and re-creating them after it's been repopulated.

这篇关于如何使用PK和值的元组有效地更新postgres？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！