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.


Your Answer

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

Browse other questions tagged or ask your own question.