Date: Fri, 23 Feb 2018 19:09:40 -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: <20180224000940.GP2416@tamriel.snowman.net>
References: 
 <BY2PR15MB0872700CE29FE00DC9E9647885CC0@BY2PR15MB0872.namprd15.prod.outlook.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="c/ZpJO2TidRLk/xN"
Content-Disposition: inline
In-Reply-To: 
 <BY2PR15MB0872700CE29FE00DC9E9647885CC0@BY2PR15MB0872.namprd15.prod.outlook.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--c/ZpJO2TidRLk/xN
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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

--c/ZpJO2TidRLk/xN
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJakK1EAAoJEO1sijiDR2RVQ/4P/3C9HLXC6a4hmfIj+ZaW2XzH
wbJol5kDJvNqzbA5CZHFxwsRQ5EZrPcBUsbQ15iffetjvbFkRx4SXc5vPrCPO3QS
lUy1hlLz6ash7pzI67+frijeCS/RkJCbdCAQsF0ii+F+KjvruUtxEwSOpF16RA/6
7suju9TBZCiYrHGI0XSHJbrVZ8qwpO2FKAWk/xDl7dAiEzVuwh5O5vJqxu/nhPWv
l8W801IW9OBnQTJ0nvI4ypdwElJdfWyfF27JGkQ0eXXwTw+B4rebYpUkCQ3IyrGR
yQ2Pqye3p+SvS1d+ed7afnQ/uCgqJkmFDwkpUyTDf6Jug24RJ7Jq7wOZpCg65zCx
9IsW55i99jj8WqPXqzHzij7HsHxYWEDY82Opud5F/zDkWNNEuX+sb2KBGwq3Cf2b
OAbdOAuqKtQWHhE5yA3KOhbo0VqaXNYXq3YN5I2wfXqWfXCUdJ8r/fqBvcK/xz+W
k0oipRqltfetXRh8AYQdDyE3YmfYpxHH4GoJ8m2mUMbXMyenWuc9etCbE79Kxh3i
sWC8ooaRXEykhOLw8CkOMTtsr8ySo/dd0T1oDGO7U1OYNHQQ2UxExOpT9VSXBQVE
2+r8rHOY8yjQk5dAVd0lrF3dG2nE8wGe/1Y4BQfL3F8oAneKNp8PvHLrfJ+UXQnB
myQeiNMvb5smBlaQjTo5
=Xm9A
-----END PGP SIGNATURE-----

--c/ZpJO2TidRLk/xN--