'PostgreSQL - 100 million records transfer from archive to a new table

I have a requirement to transfer data from 2 tables (Table A and Table B) into a new table. I am using a query to join both A and B tables using an ID column. Table A and B are archive tables without any indexes. (Millions of records) Table X and Y are a replica of A and B with good indexes. (Some thousands of records)

Below is the code for my project.

with data as 
(
  SELECT a.*, b.* FROM A_archive a
    join B_archive b where a.transaction_id = b.transaction_id
  UNION 
  SELECT x.*, y.* FROM X x 
    join Y y where x.transaction_id = y.transaction_id
 )
INSERT INTO 
Another_Table 
(
  columns
)
select * from data
On Conflict(transaction_id)
  do udpate ...

The above whole thing is running in production environment and has nearly 140 million records. Due to this production database is taking almost 10 hours to process the data and failing.

I am also having a distributed job scheduler in AWS to schedule this query inside a function and retrieve the latest records every 5 hours. The archive tables store closed invoice data. Pega UI will be using this table for retrieving data about closed invoices and showing to the customer.

Please suggest something that is a bit more performant.

postgresqlquery-optimizationdatabase-performanceamazon-aurorajavascriptauthenticationsingle-sign-onlinkedinlti


Solution 1:[1]

That UNION must be costly, it pretty much builds a temp-table in the background containing all the A-B + X-Y records, sorts it (over all fields) and then removes any doubles. If you say 100 million records are involved then that's a LOT of sorting going on that most likely will involve swapping out to disk.

Keep in mind that you only need to do this if there are expected duplicates

  • in the result from the JOIN between A and B
  • in the result from the JOIN between X and Y
  • in the combined result from the two above

IF neither of those are expected, just use UNION ALL In fact, in that case, why not have 1 INSERT operation for A-B and another one for X-Y? Going by the description I'd say that whatever is in X-Y should overrule whatever is in A-B anyway, right?

Also, as mentioned by O.Jones, archive tables or not, they should come at least with a (preferably clustered) index on the transaction_id fields you're JOINing on. (same for the Another_Table btw)

All that said, processing 100M records in 1 transaction IS going to take some time, it's just a lot of data that's being moved around. But 10h does sound excessive indeed.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 deroby

Related Questions