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 1s74v4-003U9t-M4 for pgsql-general@arkaria.postgresql.org; Wed, 15 May 2024 03:03:08 +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 1s74v4-007WtF-AE for pgsql-general@arkaria.postgresql.org; Wed, 15 May 2024 03:03:06 +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 1s74v3-007Wt6-VI for pgsql-general@lists.postgresql.org; Wed, 15 May 2024 03:03:05 +0000 Received: from mail-ua1-x935.google.com ([2607:f8b0:4864:20::935]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s74v0-000IKE-Kx for pgsql-general@lists.postgresql.org; Wed, 15 May 2024 03:03:05 +0000 Received: by mail-ua1-x935.google.com with SMTP id a1e0cc1a2514c-7f169d3ef53so2749487241.1 for ; Tue, 14 May 2024 20:03:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1715742182; x=1716346982; 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=xbSBwWwOvT/F72I6Bv5XB3WGF2BOhD7zs/ssIE2RqqM=; b=ZEVeoAM1AI0dgtS0o4AQSWN0sjL5f+hbN5sHZhS1vr3PAA/DpI1m9Z3ivMvBBG4S0/ 3s8i3nlQCgfyTAmH++cKAdQRt/PzpU257KL2rJ8RAFEFY5dqhbadt+piOJEa5g1KcGBn Uf6umN1xpPKOi6UGM6GS0W0i4G7DbF7a1mzI0oaYSgFKTaNsJgflgmlvGqe2sCkwihqI HbURmdUEdNzUzVfXdGUdrlnkiDUH7oNC3dyJ3NrWYSyqazhJPp5SCkddb5IJBvXMAshw E3xekzwU8IAwMNQnFFBGYS3YvSGZwIT+Hpb5/O0dkS7ngEWJn1SufeJQ8GnMXV6TjQmZ jmZw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1715742182; x=1716346982; 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=xbSBwWwOvT/F72I6Bv5XB3WGF2BOhD7zs/ssIE2RqqM=; b=fwg7CO+WmVn3y/0Ztkrwd9oCiAFCCFSEtQEi8MBLFe5Dp+sg3scrA7vBVWUeE4clUF NFPaiff6GfuQfA0lj/M1hHC/NsF4YU7DlSTxJKnp7u13QjHoQrrxF9uauZHEAI4LMAEN F/VB12J8/14SQZiQ2u87gmwiEyPrf0V2EI2M7zaj0w66XW0zh/PFs93fxs3p7L0b6vnm avt+xQC7guhdhEIgNY6h6hRWYkUshVeOiMvz9Ew3CcllYXjtMz4Pix6pwYheYNCn3qPs Hmz9Lazi/LILbl6HFp7XMRbnhKHJoT69HQiVCXxshG6N/MlNVlR0K39v1OXopX4QbYgn 3Ntw== X-Gm-Message-State: AOJu0YyPRd4RERxHWAlX0186uw19jSyChyafkd3OAWFvO83gnVj1GAoV 5WXZIH8uTaBGojR82qBSUB8k1Y9EeOBJVEjxYq9HIckgvDYq/C7Nv63QbbZk7SURga/2+6zOJ6/ /7W0XyIv1gb2+FV7zFTHh7BZay/U= X-Google-Smtp-Source: AGHT+IF+9cAC8Pi66KYLmOoarCQKTFAy3YlWREZQlCuP0h1dUMja9At9mfT4oREdUTm2rxbaUtAAy6QNipFZfY02YQU= X-Received: by 2002:a05:6102:150b:b0:47e:f1f1:d052 with SMTP id ada2fe7eead31-48077eb625dmr18114613137.33.1715742181621; Tue, 14 May 2024 20:03:01 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: sud Date: Wed, 15 May 2024 08:32:50 +0530 Message-ID: Subject: Re: Adding constraints faster To: Ron Johnson Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000004304fd0618755b6b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004304fd0618755b6b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, May 15, 2024 at 2:09=E2=80=AFAM Ron Johnson wrote: > On Tue, May 14, 2024 at 3:59=E2=80=AFPM sud wrote: > >> ***** >> > ALTER TABLE ADD FOREIGN KEY ... NOT VALID. >> ALTER TABLE ... VALIDATE CONSTRAINT; >> >> > This is what we did, back in the PG 12.x period. VALIDATE CONSTRAINT was > almost instantaneous. (Supporting indices existed, though.) > > Thank you. Actually we do have a composite index on the child table columns which is referring to the parent table. And in the parent table those columns are the primary keys. So even then it's taking ~20minutes for each partition. So is there some other way to make it run faster or Can we run it concurrently or Should we keep the existing constraints in "not valid" state only? --0000000000004304fd0618755b6b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Wed, May 15, 2024 at 2:09=E2=80=AFAM R= on Johnson <ronljohnsonjr@gma= il.com> wrote:
On Tue, May 14, 2024 at 3:59=E2=80= =AFPM sud <suds1= 434@gmail.com> wrote:
*****
ALTER TABLE ADD FOREIGN KEY ... NOT VALID.
A= LTER TABLE ... VALIDATE CONSTRAINT;


This is what we did, back in the PG 12.x period.= =C2=A0 VALIDATE CONSTRAINT was almost instantaneous.=C2=A0 (Supporting indi= ces existed, though.)=C2=A0


Thank you. Actually we do have a composite index on t= he child table columns which is referring to the parent table. And in the p= arent table those columns are the primary keys. So even then it's takin= g ~20minutes for each partition.=C2=A0

So is there= some other way to make it run faster=C2=A0
or
=C2=A0Ca= n we run it concurrently=C2=A0
or
Should we keep the ex= isting constraints in "not valid" state only?

--0000000000004304fd0618755b6b--