Greetings, * ldh@xxxxxxxxxxxxxxxxxx (ldh@xxxxxxxxxxxxxxxxxx) wrote: > > * ldh@xxxxxxxxxxxxxxxxxx (ldh@xxxxxxxxxxxxxxxxxx) 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 Visits table 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't fail... 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 rollback logs, 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:
signature.asc
Description: PGP signature
