Re: BUG #13846: INSERT ON CONFLICT consumessequencersonconflicts

Hi Andres, appreciate your replies. I hope you will still read this when you can, as I believe I can make the financial case, and that the solution is relatively simple. I also believe there’s still some misunderstanding 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, using my example of 500 million fact records, and referring to http://www.postgresql.org/docs/9.5/static/storage-page-layout.html

Using 10 INT columns per record, with row-index, row-header, and 64 bit alignment overhead, requires 4B (row Idx) + 24B (row hdr) + 40B (row data) = 68B per row. With a page size of 8192B, minus 24B for the page header, gives 8168 / 68 = ~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) = 108B per row. With a page size of 8192B, minus 24B for the page header, gives 8168 / 108 = ~75 rows per page.

1 – 75 / 120 = ~38%

So its much nearer 40%, and not sub 20%. When dealing in 10s to 100’s of GBs (there are terabyte warehouses), 38% is significant, and can have a real financial impact with respect to memory, network, primary and backup storage costs.

>> 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 billions of fact records, that are just about always sequentially inserted and have a timestamp holding when they occurred. I have never come across a practical need to do UPSERTs on Fact tables (i.e. transaction tables, event tables, 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’s a related Dimension table, and in most cases, the INSERT path will not be taken!! A Fact table will have columns representing Members along Dimensions. For example, an Order Fact table holding records for individual 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 would be a separate Status dimension table, with a SMALLINT column, that would be used in the Fact table to relate an Order Line Item to a particular Status. It is the Status Dimension table upon which an UPSERT would be performed for every Order Line Item ETL’d from the OLTP system into the warehouse.

So, I’m 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’m assuming you mean the BEFORE INSERT trigger, and I’m noting you state ‘to-be-inserted’ 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 purpose of the INSERT ON CONFLICT is to implement UPSERTs; would you agree? We 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 trigger, it would only execute for records that were actually going to be inserted. However, you’re now stating that the INSERT ON CONFLICT will execute a BEFORE INSERT trigger even for records that are not inserted?? That doesn’t seem quite logical, and contrary to ‘old fashioned’ 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 for a column normally defaulted from a sequencer. But that doesn’t invalidate the basic premise of what I’ve 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’t need to know anything about how the default value is computed for any column. The relevant code merely needs to only evaluate default values for only the columns used to resolve conflicts, and not all of the columns. Then, for the records that don’t conflict, evaluate any remaining default expressions, and then call the 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 consumessequencersonconflicts

Hi,

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

> If I had to change those dimension columns to BIGINTs, that’s
> 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 ‘fragile’?

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 corresponding 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 other
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 here
> 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.