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 1qmdry-008PaP-Bw for pgsql-general@arkaria.postgresql.org; Sat, 30 Sep 2023 17:35:10 +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 1qmdrw-00ELWF-I8 for pgsql-general@arkaria.postgresql.org; Sat, 30 Sep 2023 17:35:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qmdrw-00ELVj-1J for pgsql-general@lists.postgresql.org; Sat, 30 Sep 2023 17:35:08 +0000 Received: from momjian.us ([72.94.173.45]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qmdrt-008ThG-71 for pgsql-general@lists.postgresql.org; Sat, 30 Sep 2023 17:35:07 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=momjian.us; s=2023062407; h=In-Reply-To:Content-Type:MIME-Version:References:Message-ID: Subject:Cc:To:From:Date:Sender:Reply-To:Content-Transfer-Encoding:Content-ID: Content-Description; bh=/5vvGlwjIC4/VKvyzY9PbohJrfwJOoEUXrgEy8EZMWU=; b=2d2Td NB+MqEtCgYRYYpmxXi/P/6nKOa4UJkAJU0AueG5EDPd5pqVha3AOaN9QOqSUlMPXXb2EBLxvYCtJc tcIXIfwORAAoiV/rEp2Yrd5elSxU1AxwqCazeCy8Yl9bTz2pwKBHzey5PsRrig9/SVxUE+E+GATBh JbKH8wuEBzX+yMqfEhtzy9L/lzUDOYhOwqM1OH7iBRB4MIonoUG1fo7gEou9IFW49dfdL/2ULvSPv QH6SMxF4moeXN1CHiCWtw4tqLEpoDI1WUPUPNSlzYCke3WLNAEoSgABwckqIBlhWbAnCm2ILwddJP 6nrvKDkOIMz0WHoOyInWulajylzSw==; Received: from bruce by momjian.us with local (Exim 4.96) (envelope-from ) id 1qmdrq-002w6x-1u; Sat, 30 Sep 2023 13:35:02 -0400 Date: Sat, 30 Sep 2023 13:35:02 -0400 From: Bruce Momjian To: James Healy Cc: pgsql-general@lists.postgresql.org Subject: Re: Gradual migration from integer to bigint? Message-ID: References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Sat, Sep 30, 2023 at 03:55:20PM +1000, James Healy wrote: > My organization has a number of very large tables (most 100s of GB, a > couple over a Tb) that were created many years ago by a tool that > defaulted to integer PKs rather than bigint. Those PKs have a number > of integer FKs in related tables as well. We shouldn't have let them > get so big, but that's a conversation for another day. > > Some are approaching overflow and we're slowly doing the work to > migrate to bigint. Mostly via the well understood "add a new id_bigint > column, populate on new tuples, backfill the old, switch the PK" > method. The backfill is slow on these large tables, but it works and > there's plenty of blog posts and documentation to follow. > > It did make me curious though: would it be possible for postgres to > support gradual migration from integer to bigint in a more transparent > way, where new and updated tuples are written as bigint, but existing > tuples can be read as integer? > > I assume maybe a complication is that the catalog says the column is > either 32bit int or 64bit bigint and making that conditional is hard. > There's presumably other considerations I'm unaware of too. My core > question: are there significant technical blockers to supporting this > kind of gradual in place migration, or has it just not been enough of > a problem that it's received attention? I think this talk will help you: https://www.youtube.com/watch?v=XYRgTazYuZ4 -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.