Potential performance and developer experience improvements/investigation around database inserts and PKs

[yevgenypats]

Describe the resource.

Currently the insert logic is as follows:

• Resolve main table (for account and region)
• CopyFrom array/map and insert into db
  • if CopyFrom fails insert one by one - interesting to have statistics how many times this fails and why?
  • If there is a conflict on primary key delete cascade the parent and insert the new resource
• after fetch for resource and specific client (i.e for aws account_id and region) finish it deletes data with deletefilter from previous fetch cycle

Given that in big account most of the resources stays the same will CopyFrom always fail? Can we benchmark the onconflict replace?

Another thought is that there is two highly related fields - Primary keys and DeleteFilter.

Might be interesting to benchmark the following insert algorithm:

• Resolve main table
• CopyFrom to temp table
• When account_id, region and resource finished run the following transaction:
  - Delete from table where account_id=something, region=something # (or any deletefilter) ;
  - insert into table from table_tmp where delete_filter;

Things to measure:
- database performance in both cases

Advantages:
- Developer experience: PK might not be needed in addition to deletefilter as well as bugs with wrong pks might occur less.
- the main table might be more consistent as it wont contain "delete resource"
- Given that most of the bugs where incorrect PKs and not duplicate data (apart from a few global resources) those bugs and maintains can be solved.

Disadvantages:
- ( Depending on the performance test ) It might take more time for the main table to be updated with new data
- It might be harder to detect duplicate data

Use Case

Performance and dev experience

Additional context

No response