Select/Insert concurrently in Postgresql

Question

I have a table like this

`articles`
 id: int
 data: timestamp
 body: varchar

for some reason, I want to split this table into 2 separate tables:

`articles`
 id: int
 data: timestamp

`article_body` 
 id: int
 article_id: foreign key
 body

I can easily achieve that after creating the article_body table by the following statement:

INSERT INTO article_body (article_id, body) 
select id, body
from articles;

and then drop body from articles and it's really good if you have a few rows. But in a special case when your table has many rows say 100 million rows it may take hours.

One way is to do that with some other program like python and run concurrent statements with offset, but I want to know is there a way to it faster inside SQL.

As I said in question I'm using PostgreSQL.

Answer 1

One way to "batch" it might be something like:

INSERT INTO article_body (article_id, body) 
SELECT id, body
FROM articles
WHERE NOT EXISTS(
    SELECT 1 FROM article_body WHERE articles.id = article_body.article_id
)
LIMIT <batch_size>;

or some equivalent NOT IN() or LEFT JOIN .. WHERE article_id IS NULL.

You can run the query repeatedly anyway you want and it finds N rows that were not yet processed and transfers them. It spends some additional time on finding rows to process even with the right indexes, but you can throttle it the way you need, pause and resume anytime.