From: "ldh@laurent-hasson.com" <ldh@laurent-hasson.com>
To: Stephen Frost <sfrost@snowman.net>
CC: "pgsql-performance@lists.postgresql.org"
	<pgsql-performance@lists.postgresql.org>
Subject: RE: Updating large tables without dead tuples
Thread-Topic: Updating large tables without dead tuples
Thread-Index: AdOs999XVgVoZU7YRAuMVlWzH/i5PQAC+QgAACOygIA=
Date: Sat, 24 Feb 2018 17:19:21 +0000
Message-ID: 
 <BY2PR15MB0872D2208E95046125EC577385C30@BY2PR15MB0872.namprd15.prod.outlook.com>
References: 
 <BY2PR15MB0872700CE29FE00DC9E9647885CC0@BY2PR15MB0872.namprd15.prod.outlook.com>
 <20180224000940.GP2416@tamriel.snowman.net>
In-Reply-To: <20180224000940.GP2416@tamriel.snowman.net>
Accept-Language: en-US
Content-Language: en-US
received-spf: None (protection.outlook.com: laurent-hasson.com does not
 designate permitted sender hosts)
spamdiagnosticoutput: 1:99
spamdiagnosticmetadata: NSPM
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 5f611c22-3b74-4aa6-489b-08d57baabe9f
X-MS-Exchange-CrossTenant-originalarrivaltime: 24 Feb 2018 17:19:21.4309
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 7f9c0a0d-79fd-4329-b129-754c4501a177
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY2PR15MB0790
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Precedence: bulk

> -----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
>=20
> Greetings,
>=20
> * 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 wonder=
ing
> why I should pay the "bloat" penalty for this type of transaction. Is the=
re a trick
> that could be use here?
>=20
> Yes, create a new table and INSERT the data into that table, then swap th=
e 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.
>=20
> There's other options too, involving triggers and such to allow updates a=
nd
> other changes to be captured during this process, avoiding the need to lo=
ck the
> table, but that gets a bit complicated.
>=20
> > 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? T=
hat
> would make quite a performance improvement for this type of work though.
>=20
> I'm afraid it wouldn't be quite that simple, particularly you have to thi=
nk about
> what happens when you issue a rollback...
>=20
> Thanks!
>=20
> Stephen

[Laurent Hasson]=20
[Laurent Hasson]=20
This table several other tables with foreign keys into it... So any physica=
l replacement of the table wouldn't work I believe. I'd have to disable/rem=
ove 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 t=
he current implementation.

This table represents Hospital visits, off of which hang a lot of other inf=
ormation. 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 ther=
e 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 rollback logs, there are 2 entries =
for each row updated as a result?

Thank you,
Laurent.