Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qmnGy-008xkS-2D for pgsql-general@arkaria.postgresql.org; Sun, 01 Oct 2023 03:37:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qmnGw-003uj5-30 for pgsql-general@arkaria.postgresql.org; Sun, 01 Oct 2023 03:37:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qmnGv-003uig-JS for pgsql-general@lists.postgresql.org; Sun, 01 Oct 2023 03:37:33 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qmnGq-007Y4i-SS for pgsql-general@lists.postgresql.org; Sun, 01 Oct 2023 03:37:32 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 3913bRFC1635510; Sat, 30 Sep 2023 23:37:27 -0400 From: Tom Lane To: James Healy cc: Bruce Momjian , pgsql-general@lists.postgresql.org Subject: Re: Gradual migration from integer to bigint? In-reply-to: References: Comments: In-reply-to James Healy message dated "Sun, 01 Oct 2023 14:16:53 +1100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1635508.1696131447.1@sss.pgh.pa.us> Date: Sat, 30 Sep 2023 23:37:27 -0400 Message-ID: <1635509.1696131447@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk James Healy writes: > However it doesn't really address the question of a gradual migration > process that can read 32bit ints but insert/update as 64bit bigints. I > remain curious about whether the postgres architecture just makes that > implausible, or if it could be done and just hasn't because the > options for a more manual migration are Good Enough. I think what you're asking for is a scheme whereby some rows in a table have datatype X in a particular column while other rows in the very same physical table have datatype Y in the same column. That is not happening, because there'd be no way to tell which case applies to any particular row. You could fantasize about labeling individual rows somehow, but it's mere fantasy because there's noplace to put such labels. To the limited extent that we can find spare space in the existing page layout, there are far better use-cases (see nearby discussions about 64-bit XIDs, for example). And nobody is going to advocate breaking on-disk compatibility for this, especially not a break that adds more per-row overhead. So really the only way forward for this would be to provide more automation for the existing conversion processes involving table rewrites. That's possible perhaps, but it doesn't really sound compelling enough to justify a lot of work. regards, tom lane