Re: BUG #13846: INSERT ON CONFLICT consumessequencersonconflicts

Hi Andres, appreciate your replies. I hope you will still read this when yo=
u can, as I believe I can make the financial case, and that the solution is=
 relatively simple. I also believe there=E2=80=99s still some misunderstand=
ing to the problem. Maybe you could point me to the right place in the code=
.




>> Row headers, padding & alignment makes that absolutely different in
>> reality. It's like a sub 20% difference.

Regarding the space increase going from 10 INTs to 10 BIGINTS per row, usin=
g my example of 500 million fact records, and referring to http://www.postg=
resql.org/docs/9.5/static/storage-page-layout.html

Using 10 INT columns per record, with row-index, row-header, and 64 bit ali=
gnment overhead, requires 4B (row Idx) + 24B (row hdr) + 40B (row data) =3D=
 68B per row.  With a page size of 8192B, minus 24B for the page header, gi=
ves 8168 / 68 =3D ~120 rows per page.

Using 20 BIGINT columns per record, with row-index, row-header, and 64 bit =
alignment overhead, requires 4B (row Idx) + 24B (row hdr) + 80B (row data) =
=3D 108B per row.  With a page size of 8192B, minus 24B for the page header=
, gives 8168 / 108 =3D ~75 rows per page.

1 =E2=80=93 75 / 120 =3D ~38%

So its much nearer 40%, and not sub 20%. When dealing in 10s to 100=E2=80=
=99s of GBs (there are terabyte warehouses), 38% is significant, and can ha=
ve a real financial impact with respect to memory, network, primary and bac=
kup storage costs.=20




>> And in pretty much all the cases with sufficient insertion rates you're
>> going to want bigints anyway. If there's few rows it doesn't matter
>> anyway.

This is true for the surrogate key (generated by a sequencer default) of a =
Fact table (which comes from operational business transactions, like taking=
 orders, or web-page visits). As I stated, there are warehouses with billio=
ns of fact records, that are just about always sequentially inserted and ha=
ve a timestamp holding when they occurred. I have never come across a pract=
ical need to do UPSERTs on Fact tables (i.e. transaction tables, event tabl=
es, data generated by things occurring in the real world etc.).

But the table where the UPSERT is being applied is not the Fact table! It=
=E2=80=99s a related Dimension table, and in most cases, the INSERT path wi=
ll not be taken!! A Fact table will have columns representing Members along=
 Dimensions. For example, an Order Fact table holding records for individua=
l Order Line Items, would most likely have a Dimension column to represent =
the Status of the Line Item; i.e. Open, Shipped, Invoiced, Closed. There wo=
uld be a separate Status dimension table, with a SMALLINT column, that woul=
d be used in the Fact table to relate an Order Line Item to a particular St=
atus. It is the Status Dimension table upon which an UPSERT would be perfor=
med for every Order Line Item ETL=E2=80=99d from the OLTP system into the w=
arehouse.

So, I=E2=80=99m not clear how your above statement applies exactly.



>> The fragility comes from the fact that it'd only be used in a subset of
>> cases. E.g. if a BEFORE trigger is present the to-be-inserted rows needs
>> to be "materialized" to be presented to the trigger.
(I=E2=80=99m assuming you mean the BEFORE INSERT trigger, and I=E2=80=99m n=
oting you state =E2=80=98to-be-inserted=E2=80=99 records.)

I would then argue that the BEFORE trigger is being applied inappropriately=
 in the case of an INSERT with an ON CONFLICT clause. First, I think the pu=
rpose of the INSERT ON CONFLICT is to implement UPSERTs; would you agree? W=
e were always able to implement UPSERT logic before, it just was a pain in =
the butt because of concurrency. Before, I would have had a separate INSERT=
 statement and a separate UPDATE statement. If I had a BEFORE INSERT trigge=
r, it would only execute for records that were actually going to be inserte=
d. However, you=E2=80=99re now stating that the INSERT ON CONFLICT will exe=
cute a BEFORE INSERT trigger even for records that are not inserted?? That =
doesn=E2=80=99t seem quite logical, and contrary to =E2=80=98old fashioned=
=E2=80=99 upsert logic. Does this also mean that the BEFORE UPDATE trigger =
is always called as well, or is it never called?



>> Wrong. Rows with sequences can very well conflict, there's nothing
>> forcing sequences to always be used.

My oversight, you are correct here, should an explicit value be provided fo=
r a column normally defaulted from a sequencer. But that doesn=E2=80=99t in=
validate the basic premise of what I=E2=80=99ve been trying to get across.



>> Also note that sequence default values aren't in any way different from =
other
>> default values, and that relevant pieces of code currently don't know
>> whether a default value is a nextval or not.

The relevant pieces of code wouldn=E2=80=99t need to know anything about ho=
w the default value is computed for any column. The relevant code merely ne=
eds to only evaluate default values for only the columns used to resolve co=
nflicts, and not all of the columns. Then, for the records that don=E2=80=
=99t conflict, evaluate any remaining default expressions, and then call th=
e BEFORE INSERT trigger with only the records that are actually going to be=
 inserted!!


I greatly welcome your thoughts, and please point to the right place in the=
 code.

Regards,
paul


From: Andres Freund
Sent: Wednesday, January 6, 2016 3:11 PM
To: Paul
Cc: Peter Geoghegan; pgsql-bugs
Subject: Re: [BUGS] BUG #13846: INSERT ON CONFLICT consumessequencersonconf=
licts

Hi,

On 2016-01-06 15:00:17 -0700, Paul wrote:
> I=E2=80=99m looking at math more like a single Fact table having  500 mil=
lion
> records, with 10 dimension columns. If INTs were used for the
> dimension columns, that=E2=80=99s 20GB.

> If I had to change those dimension columns to BIGINTs, that=E2=80=99s
> 40GB. This can impact how much you can fit into server memory, where
> olaps like to live, and just slow down moving stuff around between
> memory and disk and over the network and backups, etc.

Row headers, padding & alignment makes that absolutely different in
reality. It's like a sub 20% difference.

And in pretty much all the cases with sufficient insertion rates you're
going to want bigints anyway. If there's few rows it doesn't matter
anyway.


> On a technical note, why is the following flow considered =E2=80=98fragil=
e=E2=80=99?

The fragility comes from the fact that it'd only be used in a subset of
cases. E.g. if a BEFORE trigger is present the to-be-inserted rows needs
to be "materialized" to be presented to the trigger.


> 1) Evaluate only columns used in conflict_target
> a. Conflict-resolving Columns with default nextval() increment the corres=
ponding sequencer
> i. And in this case, there were never be conflicts by definition, so
> ON CONFLICT can always be ignored

Wrong. Rows with sequences can very well conflict, there's nothing
forcing sequences to always be used.

Also note that sequence default values aren't in any way different from oth=
er
default values, and that relevant pieces of code currently don't know
whether a default value is a nextval or not.

> 2) If conflict, DO UPDATE
> a. If nextval()-defaulted column used in conflict_target, we never get he=
re
> b. Defaults never evaluated
> 3) Else Evaluate remaining columns not used in conflict_target and INSERT
> a. Columns with nextval() increment their corresponding sequencer

Perhaps read the code, and prototype it? I can tell you that it'd be a
significant amount of work, and that I'm personally absolutely not
interested in investing significant amounts time into it. But that
doesn't have to stop *you*.


Anyway, EOD for me.