Compare diff between similar objects stored in two different databases and delete the missing records from the later

Question

We have a legacy system (app 1) that has millions of records of an object Project. It has ProjectId and OrganizationId columns along with other properties. A recently added service (app 2) gets the same object created again in its database as part of some migration. The new object will be created with slightly different schema. Let's say it has ProjectKey and TenantId along with other properties. There are cases when project is deleted in the older application but its counter-part in new application still exists. These dead records need to be cleaned up.

Sample Legacy App's Database - DB1 (SQL):

ProjectId	OrganizationId
1	Org1
2	Org1
3	Org1
7	Org2
8	Org2
9	Org2

Sample Modern App's Database - DB2 (PostgreSQL):

ProjectKey	TenantId
1	Org1
2	Org1
3	Org1
4	Org1
5	Org1
6	Org1
7	Org2
8	Org2
9	Org2
10	Org2
11	Org2
12	Org2

From DB2, records with ProjectKeys 4, 5, 6, 10, 11, 12 need to be deleted.

I'm working on a cleanup tool that will be executed periodically. The two databases from two completely different applications are really huge with millions of records. And the stale records can also be in the millions.

I wanted some inputs on the best approach to handle this. The periodic task that cleans up the dead records shouldn't put too much load on the databases.

The newly added application has an API to get all projects of a given TenantId. I've to do a similar all query on the legacy application and then do a diff of the two lists. But I'm worried of this diff operation when the two lists are very big.

Also, the second application that gets projects of a TenantId does so in batches.

Here are some of the ways I've thought of:

1. Get all records from modern app (list2) and legacy app (list1) and then do a list2 minus list1 to get the stale records and call delete API of the modern application to cleanup.
2. Get records from modern app in batches and call a stored proc in DB1. Let DB1 do the diff and give out the ProjectIds that are missing. This stored proc will be called numerous times until all the ProjectKeys from app 2 are checked in DB1. My concern was why should DB1 intake a parameter from another DB and do a diff?

Should the identification of missing projectIds happen in a stored procedure of the DB1 or would it be better to do it in the tool?

Or are there any other more efficient ways to design this?

Answer 1

If the number of deletions is relatively small compared to the total number of projects, and if you also don't have to cleanup projects that were added only in the new system, then you could add a trigger to DB1. That trigger would cause a record to be written to a new table with information on which project got deleted.

Your tool can then read that new table and delete the corresponding records from DB2. At your discretion, you can choose if the processed records are then also removed from the table of deletions.

The advantage is that you don't have to compare two sets of millions of records, because you have the list of what to delete right at hand. A big drawback is that your tool cannot remove records that were never created in DB1.

Answer 2

When I've addressed similar problems to this, I've basically built a script that connects to both DBs and runs an SQL statement against both of them. The SQL statements produce results that are sorted in the same way. The script simply reads records from both scripts and compares them row by row and outputs those that are not found in both result sets.

When you compare each result, if the rows are not equivalent, the one that is 'less than' (or comes first) is missing from the other set. In your case, you might be able to assume that the 'modern' DB always has all the results that exist in the legacy, which makes it simpler.

Essentially you start by getting the first row from each result then:

1. compare legacy row with modern row
2. if the two rows match, get the next row from each result, repeat from 1.
3. else the modern row key is recorded for deletion, get next modern row only, repeat from 1.

In step 3., the modern row should be 'less than' (comes before) the legacy row or there should be no legacy rows remaining. It's probably a good idea to verify this and error out if it's not true

Once this is done, take the output (deletion queue) and execute the deletes against the modern DB. You could try to do the deletions while running the comparisons but I probably would keep it simple unless you have a good reason to do it inline.

This will have a very low footprint and should perform well with a reasonable fetch size.