1

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).

Options:

  1. Trigger to a new table - can slowdown the insert rate to the report table and we cant slow down the insert rate.
  2. 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.
  3. Materialized view.
  4. Any other option ... ?
  • Many questions: Could anybody else be deleting or updating rows concurrently, or is this job the only one doing that and all others just insert? How do rows for a single key differ? Which row to pick from a set of dupes? Can you tell new entries from old ones? Are there key combinations that will not be written any more? Depends how much space on disk you can spare and what your paramount objective is: Read performance? What kind of queries? Write performance? Safety? Do you backup the DB routinely already? – Erwin Brandstetter Nov 14 '14 at 2:42
  • To me it sounds as if a materialized view is your best choice. The refresh is transactional so concurrent inserts are not seen during the refresh. The question is how often you need to refresh it and whether building the mview would be fast enough. – a_horse_with_no_name Nov 14 '14 at 7:53
  • This table looks like an input queue. I'd consider a queue implementation with, at its output, an always-running consumer to consolidate the rows. Have a look at PGQ – Daniel Vérité Nov 14 '14 at 15:09
  • The report table is written by multiple processor that write the (they do upsert) of the data on a daily basic (day is part of the key). However this make the report table 10 size bigger because each processor upsert his data to the table. My task is to merge each key to single line (ignore processor). I want the aggregation to be update up to 1 hour. – Avihai Marchiano Nov 17 '14 at 6:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.