From: Stephen Frost <sfrost@snowman.net>
To: ldh@laurent-hasson.com <ldh@laurent-hasson.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Updating large tables without dead tuples
Date: Fri, 23 Feb 2018 19:09:40 -0500
Message-ID: <20180224000940.GP2416@tamriel.snowman.net> (raw)
In-Reply-To: <BY2PR15MB0872700CE29FE00DC9E9647885CC0@BY2PR15MB0872.namprd15.prod.outlook.com>
References: <BY2PR15MB0872700CE29FE00DC9E9647885CC0@BY2PR15MB0872.namprd15.prod.outlook.com>
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
Attachments:
[application/pgp-signature] signature.asc (818B, ../20180224000940.GP2416@tamriel.snowman.net/2-signature.asc)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sfrost@snowman.net, ldh@laurent-hasson.com, pgsql-performance@lists.postgresql.org
Subject: Re: Updating large tables without dead tuples
In-Reply-To: <20180224000940.GP2416@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox