How To Efficiently Update Postgres Using A Tuple Of The Pk And A Value?

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 intege

Solution 1:

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

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.

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

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

---

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 TransactionBEGIN;

-- Create a temporary table to hold the new valuesCREATE TEMPORARY TABLE temp_update_values (
    id integerPRIMARY KEY,
    text_location integer
) ONCOMMITDROP;

-- Populate itINSERTINTO 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 ONCOMMITDROPASSELECT a.id, COALESCE(n.text_location, a.text_location) AS text_location, a.title 
    FROM app_post AS a LEFTJOIN temp_update_values AS n ON a.id = n.id;

-- Empty the existing "app_post"TRUNCATETABLE app_post;

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

-- Commit the TransactionCOMMIT;

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.