Thread: Updating large tables without dead tuples
Hello
I work with a large and wide table (about 300 million rows, about 50 columns), and from time to time, we get business requirements to make some modifications. But sometimes, it’s just some plain mistake. This has happened to us a few weeks ago where someone made a mistake and we had to update a single column of a large and wide table. Literally, the source data screwed up a zip code and we had to patch on our end.
Anyways… Query ran was:
update T set source_id = substr(sourceId, 2, 10);
Took about 10h and created 100’s of millions of dead tuples, causing another couple of hours of vacuum.
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?
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.
Thank you,
Laurent.
Greetings, * 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 thatcould 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 thatthere won't be contentions, couldn't be used as a heuristic to not create dead tuples? That would make quite a performanceimprovement 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... Thanks! Stephen
Attachment
> -----Original Message----- > From: Stephen Frost [mailto:sfrost@snowman.net] > Sent: Friday, February 23, 2018 19:10 > To: ldh@laurent-hasson.com > Cc: pgsql-performance@lists.postgresql.org > Subject: Re: Updating large tables without dead tuples > > Greetings, > > * 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... > > Thanks! > > Stephen [Laurent Hasson] [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. This table represents Hospital visits, off of which hang a lot of other information. The updated column in that Visits tableis 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. 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? Thank you, Laurent.
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
On 02/24/2018 12:27 AM, ldh@laurent-hasson.com wrote:
> Hello
>
>
>
> I work with a large and wide table (about 300 million rows, about 50
> columns), and from time to time, we get business requirements to make
> some modifications. But sometimes, it’s just some plain mistake. This
> has happened to us a few weeks ago where someone made a mistake and we
> had to update a single column of a large and wide table. Literally, the
> source data screwed up a zip code and we had to patch on our end.
>
>
>
> Anyways… Query ran was:
>
> update T set source_id = substr(sourceId, 2, 10);
>
> Took about 10h and created 100’s of millions of dead tuples, causing
> another couple of hours of vacuum.
>
>
>
> 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, there is a trick I like to use here, as long as you don't mind
locking the table (even against reads).
I'll assume T.source_id is of type text. If it's not, use whatever the
actual type is.
ALTER TABLE T
ALTER COLUMN source_id TYPE text USING substr(sourceId, 2, 10);
I copied what you had verbatim, I earnestly hope you don't have two
columns source_id and sourceId in your table.
This will rewrite the entire table just the same as a VACUUM FULL after
your UPDATE would.
Don't forget to VACUUM ANALYZE this table after the operation. Even
though there will be no dead rows, you still need to VACUUM it to
generate the visibility map and you need to ANALYZE it for statistics on
your "new" column.
Foreign keys remain intact with this solution and you don't have double
wal logging like for an UPDATE.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
> -----Original Message----- > From: Vik Fearing [mailto:vik.fearing@2ndquadrant.com] > Sent: Friday, March 02, 2018 20:56 > To: ldh@laurent-hasson.com; pgsql-performance@lists.postgresql.org > Cc: Stephen Frost <sfrost@snowman.net> > Subject: Re: Updating large tables without dead tuples > > On 02/24/2018 12:27 AM, ldh@laurent-hasson.com wrote: > > Hello > > > > > > > > I work with a large and wide table (about 300 million rows, about 50 > > columns), and from time to time, we get business requirements to make > > some modifications. But sometimes, it's just some plain mistake. This > > has happened to us a few weeks ago where someone made a mistake and we > > had to update a single column of a large and wide table. Literally, > > the source data screwed up a zip code and we had to patch on our end. > > > > > > > > Anyways. Query ran was: > > > > update T set source_id = substr(sourceId, 2, 10); > > > > Took about 10h and created 100's of millions of dead tuples, causing > > another couple of hours of vacuum. > > > > > > > > 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, there is a trick I like to use here, as long as you don't mind locking the > table (even against reads). > > I'll assume T.source_id is of type text. If it's not, use whatever the actual type > is. > > ALTER TABLE T > ALTER COLUMN source_id TYPE text USING substr(sourceId, 2, 10); > > I copied what you had verbatim, I earnestly hope you don't have two columns > source_id and sourceId in your table. > > This will rewrite the entire table just the same as a VACUUM FULL after your > UPDATE would. > > Don't forget to VACUUM ANALYZE this table after the operation. Even though > there will be no dead rows, you still need to VACUUM it to generate the > visibility map and you need to ANALYZE it for statistics on your "new" column. > > Foreign keys remain intact with this solution and you don't have double wal > logging like for an UPDATE. > -- > Vik Fearing +33 6 46 75 15 36 > http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support [Laurent Hasson] Yes, sorry... only a single column source_id. I understand your idea... Is that because a TEXT field (vs a varchar) wouldbe considered TOAST and be treated differently? Thanks, Laurent.