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 1sehHh-00Aphq-31 for pgsql-general@arkaria.postgresql.org; Thu, 15 Aug 2024 20:41:25 +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 1sehHf-0019EU-Nl for pgsql-general@arkaria.postgresql.org; Thu, 15 Aug 2024 20:41:23 +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 1sehHf-0019EM-86 for pgsql-general@lists.postgresql.org; Thu, 15 Aug 2024 20:41:23 +0000 Received: from mail-yw1-x112c.google.com ([2607:f8b0:4864:20::112c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sehHY-004ukg-Gm for pgsql-general@lists.postgresql.org; Thu, 15 Aug 2024 20:41:21 +0000 Received: by mail-yw1-x112c.google.com with SMTP id 00721157ae682-6af71330ebbso1405667b3.1 for ; Thu, 15 Aug 2024 13:41:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1723754476; x=1724359276; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=bDcX300tPSgKGBWvgglES+Gz2aU/pXsPr0mxgG8Cyew=; b=ExsqTOKv6mJuddIVi7ZlKRGfULO53HibT3efkjJMpgVNB7SHHKO1EruuyqoX9NBalD fMtun+qB2YyxFL6wveaKW9ebfkM9luSrxfMXNpmqbwCaSD6zrTyuA2rhGjZo9mJjnq1S 4kX5vP8PtQ2g4cVpAFpJhWmDpxd75M92Um1viEjOPzUi0R4U/F+wUhO8bV74NQVpYTrx H0TUrrWn+u9JtM1tms4o/vU+FbM2cbJRhNeR3v9pns2c4UUVwQ3pC1u7KHytxSe3dV6U TTr8eKsuoIEh2Td1GkKXXGW15H2E+mSv/e8vk/Oqz7TkdCvu8xeTbTOmcvULa4hGO3MW PwJQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1723754476; x=1724359276; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=bDcX300tPSgKGBWvgglES+Gz2aU/pXsPr0mxgG8Cyew=; b=plCMC2synIZbqos1IyPfKWdrSyApAmTWu1JXNfpiFeXLekTHb/u/qzVbm6OdG/ehve S8hjMf9CirSfB33wTMNKN0oHA9OQz5/92S2m4txzL7y4G0estWvEvl9crZcTTNsIuflr 84V7G/HC4n46rn2fuV/7AJX3Hq1cLj7+rhevPb9PxspJX9pzsxZf3tkCk44f8Hlethl+ MmUaQBgL4vEsqLtnNrNOenTAEJv+lJWl6x30VNniQNSpDaqTARQ0rnn1WNrAEzApUI0e lA8hQmM6YtD1fvozKG2il5M7pXje0vW1IkR8Q/OkkCBF6zDtvMOkJC8qvV3SJlCMnQ1b CYpw== X-Forwarded-Encrypted: i=1; AJvYcCX7WA+4sKfy4dzgnaXpvgGhTwAGvpPzgOLk4F8ChfeUfrNYV/ZtyVIiC15AIzZk5D2swuWZc4a/ycgni495YOJ5YmIdKMKSswtQEAF7ZfP6CeDw X-Gm-Message-State: AOJu0YwmbCjK3/jDSdMGrfvvlpLtvUlmXUaqL96qTcA2/h4afIQzURWi jo5wlTphqr9z8fobKTzVAwXJU4xYwXR5sQoTpRekvfEZl+3wukDbzUA1wkn0NvjtvUZezt07orK iIeSRDpnEvdmmQxlACIWNUSTJ3m0= X-Google-Smtp-Source: AGHT+IG2PAdY+B7TBYWYXF2CW7+ybRVMVA/+vdg22YW5jjAzusxD6ZNzXuRtIwFijtm2c6NnCt/SFx87+tCAq8w6YZw= X-Received: by 2002:a05:690c:ecb:b0:695:d608:e7e9 with SMTP id 00721157ae682-6b1b1f516dcmr5342617b3.0.1723754475647; Thu, 15 Aug 2024 13:41:15 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Lok P Date: Fri, 16 Aug 2024 02:11:03 +0530 Message-ID: Subject: Re: Column type modification in big tables To: Alban Hertroys Cc: Greg Sabino Mullane , sud , pgsql-general Content-Type: multipart/alternative; boundary="000000000000337d2a061fbedd8b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000337d2a061fbedd8b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Aug 16, 2024 at 2:04=E2=80=AFAM Lok P wrote: > > On Thu, Aug 15, 2024 at 9:18=E2=80=AFPM Alban Hertroys wrote: > >> >> > On 15 Aug 2024, at 14:15, Lok P wrote: >> >> (=E2=80=A6) >> Are all those partitions critical, or only a relative few? >> >> If that=E2=80=99s the case, you could: >> 1) detach the non-critical partitions >> 2) take the system down for maintenance >> 3) update the critical partitions >> 4) take the system up again >> 5) update the non-critical partitions >> 6) re-attach the non-critical partitions >> >> That could shave a significant amount of time off your down-time. I woul= d >> script the detach and re-attach processes first, to save some extra. >> >> > Thank you so much. > > The partition table which we are planning to apply the ALTER script is a > child table to another big partition table. And we have foreign key > defined on table level but not partition to partition. So will detaching > the partitions and then altering column of each detached partition and th= en > re-attaching will revalidate the foreign key again? If that is the case > then the re-attaching partition step might consume a lot of time. Is my > understanding correct here? > Additionally , if we are okay with the 7.5hrs of down time , is my calculation/extrapolation of total time consumption based on a sample table, for direct alter, accurate? Because, in that case , I was thinking it's less complex and also less error prone to just do it in a single alter command rather than going for multiple steps of detach, alter, attach partition. --000000000000337d2a061fbedd8b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Fri, Aug 16, 2024 at 2:04=E2=80=AFAM L= ok P <loknath.73@gmail.com&g= t; wrote:

On Thu, Aug 15, 2024 at 9:18=E2=80=AFPM Alban= Hertroys <haram= rae@gmail.com> wrote:

> On 15 Aug 2024, at 14:15, Lok P <loknath.73@gmail.com> wrote:

(=E2=80=A6)
Are all those partitions critical, or only a relative few?
If that=E2=80=99s the case, you could:
=C2=A0 =C2=A0 =C2=A0 =C2=A0 1) detach the non-critical partitions
=C2=A0 =C2=A0 =C2=A0 =C2=A0 2) take the system down for maintenance
=C2=A0 =C2=A0 =C2=A0 =C2=A0 3) update the critical partitions
=C2=A0 =C2=A0 =C2=A0 =C2=A0 4) take the system up again
=C2=A0 =C2=A0 =C2=A0 =C2=A0 5) update the non-critical partitions
=C2=A0 =C2=A0 =C2=A0 =C2=A0 6) re-attach the non-critical partitions

That could shave a significant amount of time off your down-time. I would s= cript the detach and re-attach processes first, to save some extra.

=

Thank you so much.=C2=A0

The partition table which we are planning to apply the ALTER script= is a child table to another big partition table. And we have foreign=C2=A0= key defined=C2=A0on table level but not partition to partition. So will det= aching the partitions and then altering column of each detached partition a= nd then re-attaching will revalidate the foreign key again? If that is the = case then the re-attaching partition step might consume a lot of time. Is m= y understanding correct here?

=
Additionally , if we are okay with the 7.5hrs of down time , is my cal= culation/extrapolation of total time consumption based on a sample table,= =C2=A0 for direct alter, accurate? Because, in that case , I was thinking i= t's less complex and also less error prone to just do it in a single al= ter command rather than going for multiple steps of detach, alter, attach p= artition.
--000000000000337d2a061fbedd8b--