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 1sh2Cv-00BzHm-4K for pgsql-general@arkaria.postgresql.org; Thu, 22 Aug 2024 07:26:09 +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 1sh2Ct-0004uI-8C for pgsql-general@arkaria.postgresql.org; Thu, 22 Aug 2024 07:26:07 +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 1sh2Cs-0004rr-Ns for pgsql-general@lists.postgresql.org; Thu, 22 Aug 2024 07:26:07 +0000 Received: from mail-yb1-xb30.google.com ([2607:f8b0:4864:20::b30]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sh2Cm-000rdQ-Pv for pgsql-general@lists.postgresql.org; Thu, 22 Aug 2024 07:26:06 +0000 Received: by mail-yb1-xb30.google.com with SMTP id 3f1490d57ef6-e1633202008so515317276.2 for ; Thu, 22 Aug 2024 00:26:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724311560; x=1724916360; 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=d6x6aYi3qkI3sXrYAK/yZK2Xcovh9ST0NkqyByWw7YI=; b=Pcyyb3gopJWlKUw6d9HCEY8+COS0cxf7B6+nluq4Ubbmw/h0E61uKC55kev50cydHM owx0mTQWmayj3Ufn8l2xaJdjOliz3SxBvaofaM/RNUlB3/f31GDs0ps6MDznsNE2pB3v JyYbHxh8WOtSxeSOMBZtjCryaHoUPYTIpZCmtBiqnG3ebHzoFl6VoY/YtS5V2J5ZqH02 ZU8h4c5ol7eA6NUGRcwpWSQd5+fPMsqSxwDNNZo+buVfVS9JV4+I/77H+8d0kMFaD6sy LhNpF0hsvFqkbMWIOdmXIeBsD0A1TLWXfAZ43av9YIX3ArWa0PF7xDZK0WaG1HWYmeUi jHTA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724311560; x=1724916360; 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=d6x6aYi3qkI3sXrYAK/yZK2Xcovh9ST0NkqyByWw7YI=; b=n0yJsRh9fmPpyyrGRGAAx2FRlTBzXBZ9GOMdVQ/Jw0h0MFpS0w3nhX7YubrhBbO8DD XAo4t0uVQS/zjmY89QJCe6iSdaV1FI9/lLAhF+SuvNeNMUPvsXWOoEjGT9WXdVB/vmtA LoEKvOcLaTiOwt0MobDl2PFBb+nwliRoH4rbd+jOo8vVLrtv5PAb3B8qk0vsBvUnYGCT a6ihJc6H+B8kXgnngt7Zbii4GQ2COvgMrXSpq+JQX2MIqTmCDPknze9HkmhTysA5rhYz as1BsO9dJtUjb+WKYQLOfvIAU9xQkvtuLrWfM0b48nUs2mau9gzHEm1Z9P2+u5Z4btgm RIfQ== X-Forwarded-Encrypted: i=1; AJvYcCUO8YS1N7nO3ZHru+I2K3OPymHJ02rSjaOHVCikftBTrRsoJKTCYGc2js0F4WVUUShXpo/zIfjm3bFVXYqB@lists.postgresql.org X-Gm-Message-State: AOJu0YzG7qf9g1zU56QkzefuGlPUmbmftIYlHmp93GCu5A4RXopSKd/F 66YUc09WAMnalwMIaD4o8KKaLAvS4UtJsELVfiMJ4DOFV4GmlOatKkU7x9GmO7HYlOB8aHK378P wPL4dtjEa24Bup6OUuDfD8j9lY3w= X-Google-Smtp-Source: AGHT+IHpoC80PYt0zFdwRDinJ10tiQWFz1LfBahktIgkp/SWsNUnqiMj7Ib3snDbXyuPjhuHacqG3LlTkazvJLSt/dI= X-Received: by 2002:a05:6902:e04:b0:e03:a227:6476 with SMTP id 3f1490d57ef6-e1665445990mr5380285276.17.1724311559792; Thu, 22 Aug 2024 00:25:59 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Lok P Date: Thu, 22 Aug 2024 12:55:48 +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="00000000000000ef0206204092b7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000000ef0206204092b7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, 15 Aug, 2024, 9:18 pm Alban Hertroys, wrote: > > > On 15 Aug 2024, at 14:15, Lok P wrote: > > (=E2=80=A6) > > > Hello Greg, > > > > In terms of testing on sample data and extrapolating, as i picked the > avg partition sizeof the table (which is ~20GB) and i created a non > partitioned table with exactly same columns and populated with similar da= ta > and also created same set of indexes on it and the underlying hardware is > exactly same as its on production. I am seeing it's taking ~5minutes to > alter all the four columns on this table. So we have ~90 partitions in > production with data in them and the other few are future partitions and > are blank. (Note- I executed the alter with "work_mem=3D4GB, > maintenance_work_mem=3D30gb, max_parallel_worker_per_gather=3D8, > max_parallel_maintenance_worker =3D16" ) > > > > So considering the above figures , can i safely assume it will take > ~90*5minutes=3D ~7.5hours in production and thus that many hours of downt= ime > needed for this alter OR do we need to consider any other factors or > activity here? > > 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 would > script the detach and re-attach processes first, to save some extra. > > Admittedly, I haven=E2=80=99t actually tried that procedure, but I see no= reason > why it wouldn=E2=80=99t work. > > Apart perhaps, from inserts happening that should have gone to some of > those detached partitions. Maybe those could be sent to a =E2=80=98defaul= t=E2=80=99 > partition that gets detached at step 7, after which you can insert+select > those from the default into the appropriate partitions? > > But you were going to test that first anyway, obviously. > We were checking this strategy , but what we found is while attaching any of the historical partition back to the child table , if there runs any existing inserts on the other live partitions of the same child table that attach keeps on hang state. Also during this period the parent table (which is also partitioned) takes an exclusive lock on itself!! Even detaching any partition "concurrently" also waits for any inserts to finish, even those are on other partitions. Is this behavior expected? --00000000000000ef0206204092b7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Thu, 15 Aug, 2024, 9:18 pm Alban Hertroys, <hara= mrae@gmail.com> wrote:

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

(=E2=80=A6)

> Hello Greg,
>
> In terms of testing on sample data and extrapolating, as i picked the = avg partition sizeof the table (which is ~20GB) and i created a non partiti= oned table with exactly same columns and populated with similar data and al= so created same set of indexes on it and the underlying hardware is exactly= same as its on production. I am seeing it's taking ~5minutes to alter = all the four columns on this table. So we have ~90 partitions in production= with data in them and the other few are future partitions and are blank. (= Note- I executed the alter with "work_mem=3D4GB, maintenance_work_mem= =3D30gb, max_parallel_worker_per_gather=3D8, max_parallel_maintenance_worke= r =3D16" )
>
> So considering the above figures , can i safely assume it will take ~9= 0*5minutes=3D ~7.5hours in production and thus that many hours of downtime = needed for this alter OR do we need to consider any other factors or activi= ty here?

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.

Admittedly, I haven=E2=80=99t actually tried that procedure, but I see no r= eason why it wouldn=E2=80=99t work.

Apart perhaps, from inserts happening that should have gone to some of thos= e detached partitions. Maybe those could be sent to a =E2=80=98default=E2= =80=99 partition that gets detached at step 7, after which you can insert+s= elect those from the default into the appropriate partitions?

But you were going to test that first anyway, obviously.

--00000000000000ef0206204092b7--