-3

I have a table Customer with fields id, account_balance, etc. At the end of every month at midnight I want to copy the whole list of customers with their account balance to another table(month, year, customer_id and account_balance).

The problem is if the table grows large, the time to copy will keep on increasing and it would allow system to make changes to the balance while it's being copied.

How can I solve this problem? The database I am using is Postgresql 10.6

  • The time to copy depends on the number of rows you need to copy, not necessarily on the total size of the table. Do you want to copy the rows, or do you actually want to move them to the other table? If it's the latter, I would partition both tables and then simply detach the partition from one table and attach it to the other. – a_horse_with_no_name Jul 31 '19 at 7:41
  • I want to copy the rows. – mrsud Jul 31 '19 at 7:48
1

The problem is if the table grows large, the time to copy will keep on increasing and it would allow system to make changes to the balance while it's being copied.

Yes, but your copy doesn't have to care about that. When doing an INSERT INTO target_table SELECT ... FROM source_table ..., any change that occur in source_table during the INSERT is ignored. This is guaranteed by the database engine even in the lowest transaction isolation level.

In the case that your copy process consists of several SQL instructions, you may run it inside a transaction isolated in Repeatable Read mode, in which all these instructions will see a consistent of snapshot of all tables during the entire transaction. This is what pg_dump does to produce a consistent dump of an entire database while writes continue to occur.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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