Re: questions about PG update performance

2015-10-26 11:12 GMT+09:00 Michael Paquier <michael(dot)paquier(at)gmail(dot)com>:

>
>
> On Mon, Oct 26, 2015 at 10:52 AM, Kisung Kim <kskim(at)bitnine(dot)co(dot)kr>wrote:
>
>> Because of the internal implementation of MVCC in PG
>> the update of a row is actually a insertion of a new version row.
>> So if the size of a row is huge, then it incurs some overhead compare to
>> in-place update strategy.
>>
>
> Yeah, that's how an UPDATE in Postgres for MVCC usage. The xmax of the old
> row is updated, and a new row is inserted with an xmin equal to the
> previous xmax. So if you update tuple fields one by one the cost is going
> to be high.
>
>
>> Let's assume that a table has 200 columns,
>> and a user updates one of the columns of one row in the table.
>> Then PG will rewrite the whole contents of the updated row
>> including the updated columns and not-updated columns.
>>
>
> When a table has a large number of columns, usually I would say that you
> have a normalization problem and such schemas could be split into a smaller
> set of tables, minimizing the UPDATE cost.
>
>
>> I'm not sure about the implementation of Oracle's update.
>> But if the Oracle can overwrite only the updated column,
>> the performance difference between Oracle and PG in that case may be
>> significant.
>>
>> I researched about this issues in mailing list and google.
>> But I've not found anything related to this issues.
>>
>
> What you are looking at here is columnar storage, Alvaro and 2nd Quadrant
> folks have been doing some work in this area recently:
> http://www.postgresql.org/message-id/20150831225328.GM2912@alvherre.pgsql
> Also, you may want to have a look at cstore_fdw:
> https://github.com/citusdata/cstore_fdw.
> Regards,
> --
> Michael
>

Thank you for your reply.
I already know about the column store and that it is optimized for the case
I described.

However, what I want to know is about the update performance difference
between PG and Oracle if there any.
The case I described is for exaggerating the difference between PG and
Oracle.

I want to explain for our clients that PG's update performance is
comparable to Oracle's.

Regards,