Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pMc7v-00076G-6M for pgsql-admin@arkaria.postgresql.org; Mon, 30 Jan 2023 21:55:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pMc7s-00068e-Ij for pgsql-admin@arkaria.postgresql.org; Mon, 30 Jan 2023 21:55:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pMc7s-00068O-6I for pgsql-admin@lists.postgresql.org; Mon, 30 Jan 2023 21:55:44 +0000 Received: from mail-oo1-xc2b.google.com ([2607:f8b0:4864:20::c2b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pMc7p-0006wr-7a for pgsql-admin@lists.postgresql.org; Mon, 30 Jan 2023 21:55:43 +0000 Received: by mail-oo1-xc2b.google.com with SMTP id z25-20020a4ad1b9000000b00517affa07c0so63314oor.7 for ; Mon, 30 Jan 2023 13:55:40 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=nBXaMkZN+KGgG4dvr0lrD/cXwXYVsG0qc+vAEzyadUI=; b=CBcv3wr/4x8VydeboHIgGO6h7GtJ4eAxxRKfoydNLUGDyxG98Solb1OXnVoBVrRRND 8Ex5qEfeaolaSd9uJiFRSk/Ok0yCaoDv8+HHBsekdUSoCRfyf0rkfLMoGPF0poPwIsqP w52dnhKmrEa6zSOsV2dasgpowtbWZ4TCIFuEnSkXFPdeFjodyOCDXUwBahxQQefGvAV7 6OPbFcEpOxc2mZgRonCAg+Fm/BLtpEMgufkbeDBWjER3kx4m++AWNDYRxMsrxe8/1j10 Ci7I1zR4t+SyB4F/vkSlXp8ifFi1pNQbpv10qJnjA3TDbe/V7+fmRGsKqGvENFWGXs57 e6mQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=nBXaMkZN+KGgG4dvr0lrD/cXwXYVsG0qc+vAEzyadUI=; b=DVRhZS3/VV6rnCCUzeHf4jP33daRoxjQTwl7ZWi4zFasYqtqrXyFRrldnA8I3SVdWo B+dw3qckjhu3mDmYd5cZsQ4V4Xs9Z0llqXpbQ9ZopaFFd1g3j+ib3Q8M0sB3WeCxZtrQ LMOjW+3KyTnLwcDe76d4ewUJ/YSMnR6KloM0xawdM2XlJzodpVlqUClTOFnZ9GY+pDSl jFr48Zgo+jXaw01FscrqqfbcCQb8skdz9fI0L3EVF7E7FbY/5nmOLYIg8Z/tncqq4sVp UOa9/9Ii33RfrueNcNx7y0KXzfGVcVVpmmedsCTa4LKgNzB3ta+6hWcsIGBz/qpwnx84 OQUQ== X-Gm-Message-State: AFqh2kr6iDoI4OnESbAvKxJnA+usAfmNXa4SGRIQ8lZDuxHb60RFXLjD uTRi1YYGVCfB2O7mIVluHjJYA7/+eMU= X-Google-Smtp-Source: AMrXdXuoLOqnFw7z3BZ9PjsRqj6z3xRp2tYZ+cPME1PPvePBBdRzptYw3ugZRhq4tmsgBddqYBhG9g== X-Received: by 2002:a4a:e68e:0:b0:4f5:200e:a1bf with SMTP id u14-20020a4ae68e000000b004f5200ea1bfmr23138509oot.1.1675115738983; Mon, 30 Jan 2023 13:55:38 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id z73-20020a4a494c000000b00517ac3017b5sm407959ooa.33.2023.01.30.13.55.38 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 30 Jan 2023 13:55:38 -0800 (PST) Content-Type: multipart/alternative; boundary="------------G7u8Gg7FXg8eleHe3wzgNhDt" Message-ID: <5b627408-a8b1-050b-cac1-3fa71c13b691@gmail.com> Date: Mon, 30 Jan 2023 15:55:38 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Foreign key creation on table with huge record count. Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------G7u8Gg7FXg8eleHe3wzgNhDt Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/30/23 14:12, Gambhir Singh wrote: > Hi, > > I have a table with a record count of around 100 Million records. while > creating foreign key on the table took a significant amount time to get > created. > > Is there any way to speed up the execution of creation of foreign key > constraint ? I combine the suggestions of the other two emails: 1. Make sure that there's an index on the relevant field of the referenced table. 2. ALTER TABLE foo ADD CONSTRAINT foo_fk FOREIGN KEY(bar) REFERENCES blarge (bar) NOT VALID; 3. ALTER TABLE foo VALIDATE CONSTRAINT. It's *really* fast. (Step 1 is vital for operations like purging old records.  Without that index deletes can take months.) -- Born in Arizona, moved to Babylonia. --------------G7u8Gg7FXg8eleHe3wzgNhDt Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 1/30/23 14:12, Gambhir Singh wrote:
Hi, 

I have a table with a record count of around 100 Million records. while creating foreign key on the table took a significant amount time to get created.

Is there any way to speed up the execution of creation of foreign key constraint ?

I combine the suggestions of the other two emails:
1. Make sure that there's an index on the relevant field of the referenced table.
2. ALTER TABLE foo ADD CONSTRAINT foo_fk FOREIGN KEY(bar) REFERENCES blarge (bar) NOT VALID;
3. ALTER TABLE foo VALIDATE CONSTRAINT.

It's really fast.

(Step 1 is vital for operations like purging old records.  Without that index deletes can take months.)

--
Born in Arizona, moved to Babylonia.
--------------G7u8Gg7FXg8eleHe3wzgNhDt--