I'd like to get your thoughts on the approach to archiving data from a set of tables with foreign key constraints. Please find the tables details below.
booking (Table):ID (UUID), created_date_time updated_date_time transaction_id (UUID)
transaction (Table):ID (UUID), created_date_time updated_date_time booking_id (UUID)
transaction_event (Table):ID (UUID), created_date_time updated_date_time transaction_id (UUID)
Record Count: booking (25,65,881), transaction(92,21,261), transaction_event(2,27,65,114)
Foreign Key Constraints: Booking.transaction_id to Transaction.ID
Transaction.booking_id to Booking.ID
Transaction_Event.transaction_id to Transaction.ID
1* Booking Table (transaction_id) references to Transaction Table (ID).
2* Transaction Table (booking_id) references to Booking Table (ID).
3* Transaction_Event Table (transaction_id) references to Transaction Table (ID).
Requirement : Archive older than 6 months data to different Archive DB Server and purge the same data on Live tables.
Issue : created_date and updated_date is not same for the records in all the 3 reference tables. So, archiving data older than 6 months in all the 3 tables seems to be bit difficult. Purging data older than 6 months is not an issue, because I am using the constraint ON DELETE CASCADE.
I have created a procedure which copies older than 6 months records in all the 3 tables from Live to Archive DB. Also created inner procedures which will check for any related records from all these 3 tables which are greater than remaining time. But it takes much time to complete.
NOTE: I need to configure Archive job to run every night to archive data older than 6 Months.
I'd be very grateful if someone has any other option to help me on this . Thank you.
