I have a aggregation table that is written by multiple processors (around 20). Each row has a composite key - location-id, campaign-id,... and aggregate stats about it.
I need to merge the aggregation rows to single line per key. Now it has 10 lines per key because each processor write its own data in order to avoid locking in the database.
I dont care to write it to new table or to merge it in this table. i prefer to merge it in this table so i will not need to read from multiple tables.
Some info abut the table: Events are written to table all the time in heigh rate. We write 1 million row per day (merge them will decrease it to 50K rows per day).
- Trigger to a new table - can slowdown the insert rate to the report table and we cant slow down the insert rate.
- An hour procedure that will update an aggregation table - how to do the merge ? read aggregation of all day ? and delete this day from the aggregation table ? long read while this data still updated in the report table will create lots of versioning on the report rows.
- Materialized view.
- Any other option ... ?