Date: Sat, 24 Feb 2018 15:56:50 -0500
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
Message-ID: <20180224205650.GS2416@tamriel.snowman.net>
References: 
 <BY2PR15MB0872700CE29FE00DC9E9647885CC0@BY2PR15MB0872.namprd15.prod.outlook.com>
 <20180224000940.GP2416@tamriel.snowman.net>
 <BY2PR15MB0872D2208E95046125EC577385C30@BY2PR15MB0872.namprd15.prod.outlook.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="1lLO2V1n4mNUM7Yc"
Content-Disposition: inline
In-Reply-To: 
 <BY2PR15MB0872D2208E95046125EC577385C30@BY2PR15MB0872.namprd15.prod.outlook.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--1lLO2V1n4mNUM7Yc
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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 wond=
ering
> > why I should pay the "bloat" penalty for this type of transaction. Is t=
here a trick
> > that could be use here?
> >=20
> > 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 t=
he
> > 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=
 and
> > other changes to be captured during this process, avoiding the need to =
lock 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?=
 That
> > 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 t=
hink about
> > what happens when you issue a rollback...
>=20
> [Laurent Hasson]=20
> This table several other tables with foreign keys into it... So any physi=
cal replacement of the table wouldn't work I believe. I'd have to disable/r=
emove the foreign keys across the other tables, do this work, and then re-s=
et 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 i=
nformation. The updated column in that Visits table is not part of the key.
>=20
> As for the rollback, I didn't think about it because in our case, short o=
f a db/hardware failure, this operation wouldn't fail... But the risk is th=
ere and I understand the engine must be prepared for anything and fulfill t=
he 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 entrie=
s 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

--1lLO2V1n4mNUM7Yc
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJakdGRAAoJEO1sijiDR2RVwVAP/0vytNq3M3cU7oWlBLM/GA5N
gy3uwKiks5vcsVpf+t5ZeqZ46Eq1UwrfdRN6LVFWiiztUSjGMDEr3IjTG+5l7Ik5
RFD4snWV2ot04vql4lua9PRCvSL3QxPCz5gJv91GGuxM1jvYrYCGk0Q7BO0PxiJ/
QBrRM4n6itxBo9IuzdYBjSqvtevZ2Axpg/LoaMd3vawQHHl9YbJbuPmIqw42HQDk
Gsgpb3hKR4uFm6aTmRogePe1l4h5C7np24FzBkQN1AQqXp1nSPK0GL30nf6H01M3
GltIORD2l9tPzsRyQ0WCT1SH1HPHmeri6zvXAYgWOxhbTE60B4m7kJOXl/jeqJlO
2gRPtf82+25gM7A6FXMFNzXHAV2g4dNMk+4YRgHUU7yoA7KbqdhGgIFTo3nXVCqV
5WLUnbQ5vEioNfwXvK7eh0kK2tqZij88UGFx9qJhK3EhmPL7cHZ00ZdzE2dZQC82
y04Foy/yQphe9UISw90lgSHEbeb3cn974iiFtkk/gorvdtWP/IizlcuV48Eo1C4+
31lR0xrduehaRGezK3Umh7mnaFy5GWVryY6sAbsOaUVdXxDQRnikPwuqe9MIhHhB
9avnmNa2xnRAC/lFrIt6zt1WjA31cjsbrvhryh1x2W56RHOuMiCdavc9u5YXbmT5
XgERkCKq4EPuM3Ox9KlC
=gd2W
-----END PGP SIGNATURE-----

--1lLO2V1n4mNUM7Yc--