Обсуждение: questions about PG update performance

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.

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.

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.

On Mon, Oct 26, 2015 at 10:52 AM, Kisung Kim 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

On Mon, Oct 26, 2015 at 9:03 AM, Любен Каравелов <karavelov@mail.bg> wrote:
>
>
> ----- Цитат от Kisung Kim (kskim@bitnine.co.kr), на 26.10.2015 в 04:36 -----
>
> > 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.
> >
>
> Oracle is also using MVCC but copies the old row in the rollback segment and
> rewrites the values in-place.

I think Oracle just copies the changed part of old row to rollback segment.

Also in Redo logs, it just writes the changed column value (both old and

new).  So for the case we are discussing in this thread (one changed

column out of 200 columns), Oracle will just write the old value of that

column in Redo and then in rollback segment, and write the new value

in Redo and then do the in-place update in heap row.

On Mon, Oct 26, 2015 at 10:19 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:

I think Oracle just copies the changed part of old row to rollback segment.

Also in Redo logs, it just writes the changed column value (both old and

new).  So for the case we are discussing in this thread (one changed

column out of 200 columns), Oracle will just write the old value of that

column in Redo and then in rollback segment, and write the new value

in Redo and then do the in-place update in heap row.

In that case, readers would pay the penalty for constructing the row.

On Mon, Oct 26, 2015 at 12:07 PM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote:

On Mon, Oct 26, 2015 at 10:19 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:

I think Oracle just copies the changed part of old row to rollback segment.

Also in Redo logs, it just writes the changed column value (both old and

new).  So for the case we are discussing in this thread (one changed

column out of 200 columns), Oracle will just write the old value of that

column in Redo and then in rollback segment, and write the new value

in Redo and then do the in-place update in heap row.

In that case, readers would pay the penalty for constructing the row.

Readers that have snapshot older than update-transaction needs to

pay such cost, otherwise all newer transactions can directly read from

page.  Also not all old-transaction readers have to pay any such cost.

Not only that, such a design has an advantage that the bloat due to

older data won't be there.

On Mon, Oct 26, 2015 at 4:09 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Mon, Oct 26, 2015 at 12:07 PM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote:

On Mon, Oct 26, 2015 at 10:19 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:

I think Oracle just copies the changed part of old row to rollback segment.

Also in Redo logs, it just writes the changed column value (both old and

new).  So for the case we are discussing in this thread (one changed

column out of 200 columns), Oracle will just write the old value of that

column in Redo and then in rollback segment, and write the new value

in Redo and then do the in-place update in heap row.

In that case, readers would pay the penalty for constructing the row.

Readers that have snapshot older than update-transaction needs to

pay such cost, otherwise all newer transactions can directly read from

page.  Also not all old-transaction readers have to pay any such cost.

Can you please explain your last sentence?