pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedavoid update - insert churn
4+ messages / 3 participants
[nested] [flat]
* avoid update - insert churn
@ 2017-03-05 18:20 Kevin Duffy <kevind0718@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Kevin Duffy @ 2017-03-05 18:20 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: avoid update - insert churn
@ 2017-03-05 19:56 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Kevin Duffy <kevind0718@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Adrian Klaver @ 2017-03-05 19:56 UTC (permalink / raw)
To: Kevin Duffy <kevind0718@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: avoid update - insert churn
@ 2017-03-05 20:07 Kevin Duffy <kevind0718@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 4+ messages in thread
From: Kevin Duffy @ 2017-03-05 20:07 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-sql
"PostgreSQL 9.5.4, compiled by Visual C++ build 1800, 64-bit"
some examples here: http://www.postgresqltutorial.com/postgresql-upsert/
many thanks for your swift reply.
KD
On Sun, Mar 5, 2017 at 2:56 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: avoid update - insert churn
@ 2017-03-06 03:55 Steve Midgley <science@misuse.org>
parent: Kevin Duffy <kevind0718@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Steve Midgley @ 2017-03-06 03:55 UTC (permalink / raw)
To: Kevin Duffy <kevind0718@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-sql
On Sun, Mar 5, 2017 at 12:07 PM, Kevin Duffy <kevind0718@gmail.com> wrote:
> "PostgreSQL 9.5.4, compiled by Visual C++ build 1800, 64-bit"
>
> some examples here: http://www.postgresqltutorial.com/postgresql-upsert/
>
> many thanks for your swift reply.
>
> KD
>
> On Sun, Mar 5, 2017 at 2:56 PM, Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>
>> 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
>>>
>>>
This seems more like a data modeling problem than a PG SQL issue per se
(though obviously your SQL vocab matters in terms what what/how you
implement). If that's right, I'd suggest taking a look at Ralph Kimball's
book Data Warehouse Toolkit
<https://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247;.
It basically deals with practical issues like this in many ways. Your
summary table is basically a warehouse fact table aggregated against a
couple of dimensions. If you like his approach, you might find that it
eliminates the design problems you're facing and improves performance on
the underlying calculations as well.
I haven't spent much time doing data warehouse work for more than 10 years,
so I'm rusty, but I do remember reading his book and feeling grateful at
all the many hours of time he saved me, and countless errors avoided.
Steve
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-03-06 03:55 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-03-05 18:20 avoid update - insert churn Kevin Duffy <kevind0718@gmail.com>
2017-03-05 19:56 ` Adrian Klaver <adrian.klaver@aklaver.com>
2017-03-05 20:07 ` Kevin Duffy <kevind0718@gmail.com>
2017-03-06 03:55 ` Steve Midgley <science@misuse.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox