Subject: Re: avoid update - insert churn
To: Kevin Duffy <kevind0718@gmail.com>, pgsql-sql@postgresql.org
References: 
 <CAHCyeW1ErcVHaiRReSwBDBr6mcZ4D+6ZzZjvV9g_K9ohjo2p3Q@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <4d61f745-7fba-d768-5bbf-5fdbf2b89569@aklaver.com>
Date: Sun, 5 Mar 2017 11:56:03 -0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.7.0
MIME-Version: 1.0
In-Reply-To: 
 <CAHCyeW1ErcVHaiRReSwBDBr6mcZ4D+6ZzZjvV9g_K9ohjo2p3Q@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 03/05/2017 10:20 AM, Kevin Duffy wrote:
>
> 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.

You do not say what version of Postgres you are using, but if 9.5+

https://www.postgresql.org/docs/9.5/static/sql-insert.html

"ON CONFLICT Clause

The optional ON CONFLICT clause specifies an alternative action to 
raising a unique violation or exclusion constraint violation error. For 
each individual row proposed for insertion, either the insertion 
proceeds, or, if an arbiter constraint or index specified by 
conflict_target is violated, the alternative conflict_action is taken. 
ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative 
action. ON CONFLICT DO UPDATE updates the existing row that conflicts 
with the row proposed for insertion as its alternative action."

It wraps the INSERT/UPDATE as an UPSERT in one command.


>
> But at the end of the day, it do believe Keep It Simple.
>
> Many thanks for considering this problem.
>
> KD
>
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql