Re: questions about PG update performance

On Mon, Oct 26, 2015 at 9:03 AM, Любен Каравелов <karavelov(at)mail(dot)bg> wrote:
>
>
> ----- Цитат от Kisung Kim (kskim(at)bitnine(dot)co(dot)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.

In PostgreSQL, the whole new row is written in heap and diff tuple
(difference
of old and new tuple; this optimization is done in 9.4, commit a3115f0d)
in WAL. I think we can try to optimize and use the same technique for
heap as used for WAL to make PostgreSQL more efficient for such scenario's,
however as of today, my guess is that PostgreSQL's update would be lagging
in this area.

> It is still 2 writes as in Postgres.

The difference is in the amount of data written per write.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com