From: | Kevin Duffy <kevind0718(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | avoid update - insert churn |
Date: | 2017-03-05 18:20:11 |
Message-ID: | CAHCyeW1ErcVHaiRReSwBDBr6mcZ4D+6ZzZjvV9g_K9ohjo2p3Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello All:
I am looking for suggestions on how to optimize a relatively simple problem.
Say I have a group of shoe stores. The store report sales daily and daily
I
calc stats off on the sales numbers. Ie rolling averages & volatility
numbers, with a couple of different look backs.
I am storing the raw sales figures in one table and the calc'ed stats in
another.
Here is the interesting part, it is possible and it does happen that a
store will not
report sales for a day or two or prior figures reported will get revised.
There is at max a five day window on this.
For the prior dates there are two possibilities, I will be updating
a record or inserting a new record in sales table. For the current date's
sales it will be all inserts.
Currently what I do is loop through the data by store and shoe style
attempt an update and if that fails
do an insert.
Same deal for the stat's table except there are multiple records for each
Store/Shoe style pair.
So here is my question/issue: Is this there a better design pattern for
doing this?
I could gather up the data for the prior dates and do one "bulk" update.
But that would
fail for sales that have not been reported todate.
The current date's sales are of course an insert. And I could fix this to
be one "bulk" insert.
Was just chewing this over in my heading wondering if there is a more
efficient way to this.
Other than attempt update if fail insert. Seems to be a bit of churn to me.
But at the end of the day, it do believe Keep It Simple.
Many thanks for considering this problem.
KD
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2017-03-05 19:56:03 | Re: avoid update - insert churn |
Previous Message | Adrian Klaver | 2017-02-28 17:07:45 | Re: FDW Process ID (PID) |