2

I have a bunch of rows of users in a users table, for example:

| id |  name |
|----|-------|
|  1 | Chris |
|  2 | Max   |
|  3 | Steve |

For each one of these users, I'd like to create a row in another table called user_flags:

| user_id |  flag  |
|---------|--------|
|       1 | myflag |
|       2 | myflag |
|       3 | myflag |

Currently what I'm doing is downloading users in batches of ~100 at a time, and then doing a bulk INSERT query for each batch. However I think that there should be a quicker way.

The goals:

  • Insert the flag rows as quickly as possible
  • Long running queries are fine, as long as there's no table locking anywhere
  • These are real-time queries, so indexes cannot be dropped
  • Should be able to comfortably run over millions of user rows

Is this possible? I feel like with some smart functions it could/should be.

1
  • What should go into each new flag column? The string 'myflag'? Commented Aug 21, 2015 at 13:46

1 Answer 1

3

This would seem rather simple:

INSERT INTO user_flags (user_id, flag)
SELECT u.id, 'myflag'   -- or whatever to write to this field
FROM   users u
LEFT   JOIN user_flags uf ON uf.user_id = u.id
WHERE  uf.user_id IS NULL;  -- to exclude already existing

How to ..

While this is susceptible to race conditions with concurrent transactions, that should not be a problem since you are not going to execute this INSERT in more than one transaction at a time.

You might limit the operations to n rows per call, say 100000, simply append LIMIT, possibly combined with ORDER BY:

...
ORDER  BY u.id
LIMIT  100000;

Repeat the query until you find < 100000 rows, in separate transactions to release locks. Normally, you would do it all in one command, though, even with millions of rows.

I suggest a FOREIGN KEY constraint to enforce referential integrity - if you don't have one, yet.

ALTER TABLE user_flags
  ADD CONSTRAINT user_flags_user_id_fkey FOREIGN KEY (user_id)
  REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;

And a UNIQUE or PRIMARY KEY constraint on user_flags.user_id to make the 1:1 relationship complete.

2
  • Amazing - thanks for this answer! I haven't used JOIN in inserts before, but you're absolutely right, this ain't rocket science. Good shout on race conditions. Out of interest - what was the reason behind limiting it to 100k per batch? Commented Aug 21, 2015 at 17:27
  • @ChrisHoughton: Just trying to address the parts in your question downloading users in batches of ~100 at a time in combination with Should be able to comfortably run over millions of user rows. To keep transactions and locks short, you can split this operation at will. 100,000 seems more reasonable than 100. Run inserts in separate transactions to actually release locks. I would do it in one fell swoop unless there is a compelling reason not to. Commented Aug 21, 2015 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.