Re: Updating large tables without dead tuples - Mailing list pgsql-performance

From Stephen Frost
Subject Re: Updating large tables without dead tuples
Date
Msg-id 20180224205650.GS2416@tamriel.snowman.net
Whole thread Raw
In response to RE: Updating large tables without dead tuples  ("ldh@laurent-hasson.com" <ldh@laurent-hasson.com>)
List pgsql-performance
Greetings,

* ldh@laurent-hasson.com (ldh@laurent-hasson.com) wrote:
> > * ldh@laurent-hasson.com (ldh@laurent-hasson.com) wrote:
> > > This was done during a maintenance window, and that table is read-only
> > except when we ETL data to it on a weekly basis, and so I was just wondering
> > why I should pay the "bloat" penalty for this type of transaction. Is there a trick
> > that could be use here?
> >
> > Yes, create a new table and INSERT the data into that table, then swap the new
> > table into place as the old table.  Another option, if you don't mind the
> > exclusive lock taken on the table, is to dump the data to another table, then
> > TRUNCATE the current one and then INSERT into it.
> >
> > There's other options too, involving triggers and such to allow updates and
> > other changes to be captured during this process, avoiding the need to lock the
> > table, but that gets a bit complicated.
> >
> > > More generally, I suspect that the MVCC architecture is so deep that
> > something like LOCK TABLE, which would guarantee that there won't be
> > contentions, couldn't be used as a heuristic to not create dead tuples? That
> > would make quite a performance improvement for this type of work though.
> >
> > I'm afraid it wouldn't be quite that simple, particularly you have to think about
> > what happens when you issue a rollback...
>
> [Laurent Hasson]
> This table several other tables with foreign keys into it... So any physical replacement of the table wouldn't work I
believe.I'd have to disable/remove the foreign keys across the other tables, do this work, and then re-set the foreign
keys.Overall time in aggregate may not be much shorter than the current implementation. 

That would depend on the FKs, of course, but certainly having them does
add to the level of effort required.

> This table represents Hospital visits, off of which hang a lot of other information. The updated column in that
Visitstable is not part of the key. 
>
> As for the rollback, I didn't think about it because in our case, short of a db/hardware failure, this operation
wouldn'tfail... But the risk is there and I understand the engine must be prepared for anything and fulfill the ACID
principles.

Right, PG still needs to be able to provide the ability to perform a
rollback.

> With respect to that, I read in many places that an UPDATE is effectively a DELETE + INSERT. Does that mean in the
rollbacklogs, there are 2 entries for each row updated as a result? 

The short answer is yes.  The existing row is updated with a marker
saying "not valid as of this transaction" and a new row is added with a
marker saying "valid as of this transaction."  Each of those changes
also ends up in WAL (possibly as a full-page image, if that was the
first time that page was changed during that checkpoint, or possibly as
just a partial page change if the page had already been modified during
that checkpoint and a prior full-page image written out).  Indexes also
may need to be updated, depending on if the new row ended up on the same
page or not and depending on which columns were indexed and which were
being changed.

There has been discussion around having an undo-log type of approach,
where the page is modified in-place and a log of what existed previously
stored off to the side, to allow for rollback, but it doesn't seem
likely that we'll have that any time soon, and that space to store the
undo log would have to be accounted for as well.

Thanks!

Stephen

Attachment

pgsql-performance by date:

Previous
From: "ldh@laurent-hasson.com"
Date:
Subject: RE: Updating large tables without dead tuples
Next
From: PropAAS DBA
Date:
Subject: Re: Please help