How to update a postgres table and migrate data from another table with better performance on large data - postgresql

Question

I have following two tables in my potgres database with each type.

user

userid       | bigint (PK) NOT NULL
username     | character varying(255)
businessname | character varying(255)

inbox

messageid    | bigint (PK) NOT NULL
username     | character varying(255)
businessname | character varying(255)

Upto now the referencing to user table from inbox happens using username and businessname fields without foreign key constraints. My intention is to introduce a new field to inbox as userRefId and refer to the userid in user table without a foreign key so i can drop username and businessname fields which has been a bad way of lookup up for user from inbox.

For now i've prepared postgres ALTER and UPDATE queries to do this migration, but the issue is its taking so much time in live database with too much records. So now i want to create a stored procedure to do this task.

Queries

ALTER TABLE inbox ADD userRefId bigint;

UPDATE inbox SET userRefId = (SELECT userid FROM user WHERE username=inbox.username AND 
businessname=inbox.businessname) WHERE (SELECT userid FROM user WHERE username=inbox.username AND
businessname=inbox.businessname) IS NOT NULL;

What is the best approach to address this issue? Improving this existing query with indexing or crating a stored procedure. Provided examples are highly appreciated as i'm fairly new.

As a side note: using a "magic" number like 255 for the max length of a varchar column has no performance or storage advantages over e.g. 342 or 271. — user330315
– user330315, Commented Nov 6, 2020 at 20:18

user330315 · Accepted Answer · 2020-11-06 20:17:24Z

4

Typically an UPDATE with a FROM clause is faster than a co-related subquery

UPDATE inbox 
  SET userRefId = u.userid
from "user" u
WHERE u.username = inbox.username 
  AND u.businessname = inbox.businessname

answered Nov 6, 2020 at 20:17

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

dinesh alwis Over a year ago

Any suggestion for a stored procedure option?

user330315 Over a year ago

@dineshalwis: the UPDATE won't be faster if you put it into a stored procedure.

Collectives™ on Stack Overflow

How to update a postgres table and migrate data from another table with better performance on large data - postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related