Re: update non-indexed value is slow if some non-related index/fk are enabled - Mailing list pgsql-general

On 2021-09-06 07:13:07 -0700, David G. Johnston wrote:
> On Monday, September 6, 2021, Philippe Doussot <philippe.doussot@up.coop>
> wrote:
>
>     I whas hopping the same optimisation as you: Write in place.
>
> How exactly would you expect “update-in-place” to work given the nature of
> MVCC?

Some databases (e.g. Oracle) store the old row in a separate place
(called an UNDO segment in Oracle) and then overwrite the row in place.
When another transaction tries to access the (not yet committed) row or
when the transaction is rolled the old row is retrieved from the undo
segment.

There are pros and cons to both approaches. Personally, I had less
problems with PostgreSQL's approach than with Oracle's.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"