Date: Tue, 4 Jan 2022 14:32:20 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Maxim Orlov <orlovmg@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Add 64-bit XIDs into PostgreSQL 15
Message-ID: <20220104193220.GL15820@tamriel.snowman.net>
References: 
 <CACG=ezZe1NQSCnfHOr78AtAZxJZeCvxrts0ygrxYwe=pyyjVWA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="uMPAU7A2Er6+wvsD"
Content-Disposition: inline
In-Reply-To: 
 <CACG=ezZe1NQSCnfHOr78AtAZxJZeCvxrts0ygrxYwe=pyyjVWA@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20220104193220.GL15820%40tamriel.snowman.net>
Precedence: bulk


--uMPAU7A2Er6+wvsD
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Maxim Orlov (orlovmg@gmail.com) wrote:
> Long time wraparound was a really big pain for highly loaded systems. One
> source of performance degradation is the need to vacuum before every
> wraparound.
> And there were several proposals to make XIDs 64-bit like [1], [2], [3] a=
nd
> [4] to name a few.
>=20
> The approach [2] seems to have stalled on CF since 2018. But meanwhile it
> was successfully being used in our Postgres Pro fork all time since then.
> We have hundreds of customers using 64-bit XIDs. Dozens of instances are
> under load that require wraparound each 1-5 days with 32-bit XIDs.
> It really helps the customers with a huge transaction load that in the ca=
se
> of 32-bit XIDs could experience wraparounds every day. So I'd like to
> propose this approach modification to CF.
>=20
> PFA updated working patch v6 for PG15 development cycle.
> It is based on a patch by Alexander Korotkov version 5 [5] with a few
> fixes, refactoring and was rebased to PG15.

Just to confirm as I only did a quick look- if a transaction in such a
high rate system lasts for more than a day (which certainly isn't
completely out of the question, I've had week-long transactions
before..), and something tries to delete a tuple which has tuples on it
that can't be frozen yet due to the long-running transaction- it's just
going to fail?

Not saying that I've got any idea how to fix that case offhand, and we
don't really support such a thing today as the server would just stop
instead, but if I saw something in the release notes talking about PG
moving to 64bit transaction IDs, I'd probably be pretty surprised to
discover that there's still a 32bit limit that you have to watch out for
or your system will just start failing transactions.  Perhaps that's a
worthwhile tradeoff for being able to generally avoid having to vacuum
and deal with transaction wrap-around, but I have to wonder if there
might be a better answer.  Of course, also wonder about how we're going
to document and monitor for this potential issue and what kind of
corrective action will be needed (kill transactions older than a cerain
amount of transactions..?).

Thanks,

Stephen

--uMPAU7A2Er6+wvsD
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJh1KDEAAoJEO1sijiDR2RVC0UP/1eJvZEs6oQWHFA1EMtzpTl/
eujY6HRhpTobGQZJfUjFd/5MRpJR4fl6P8wbtKiUswea9SimB5v2l5ffHHO1GrEu
7UHaOGgYFLc/FTTt2NKe4WsPbLWDqP1KtgeWc6ZASqzM7/09gkLEjC+Xr67WJAGg
JEaCqFb4w/K9N69ayh+8wguOp8KKW2Z8O1reYgK27jqAD5xUXnMmFYER25HmN4Li
OsnLvbM5pHEdMn6h+M8xZMFt3QQmBQLxZvg66Ys868kbvxx0fu2J67h3woF3LRq3
Es5kMSIhntu/ipS9xOX0QUuQHulxMpU26h8fr6F3U0Jsf8C86Gc5yWmPCF8aD1NG
svbm1AOQ3TeDn3mZPXq3KI6bVKcO1uQBMvTwOw0SQP4X1gbxeNxFHdfGoajAfDJq
wni7bVZZJGR29tIl2hkkyEwGgIuGlw+nclqOICc7+btPAyUsTXCIy3K1sGYRYrGv
1kc35t8pDcR5pJRxZHbNfeK4F+iuZs/uG0o2Bkq1LqefH9PlesALwAJe1lDfXin9
8PpttPPKokz7vOzIncvn8PLXXTI9PyesiT3F/590GSx7YDx/G9Ynm4lO6EUnh/iK
qDuuoddfopQ5IyEywvTEXgDdQbX3l9BBDo/EVWQilEN45vFP+jk9X4CL0UJKjKby
r55X/Z2lcFF6x7YUIIhh
=SoMD
-----END PGP SIGNATURE-----

--uMPAU7A2Er6+wvsD--