From: | Mike Broers <mbroers(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: any tricks to get foreign key constraint builds faster |
Date: | 2011-06-06 19:11:58 |
Message-ID: | BANLkTikyv-no6x+H_NTnOFJi6CYttOsjNA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Should this be posted in performance instead?
On Fri, Jun 3, 2011 at 9:46 AM, Mike Broers <mbroers(at)gmail(dot)com> wrote:
> I am in the process of implementing cascade on delete constraints
> retroactively on rather large tables so I can cleanly remove deprecated
> data. The problem is recreating some foreign key constraints on tables of
> 55 million rows+ was taking much longer than the maintenance window I had,
> and now I am looking for tricks to speed up the process, hopefully there is
> something obvious i am overlooking.
>
> BEGIN;
> ALTER TABLE ONLY t1 DROP CONSTRAINT fk_t1_t2_id;
> ALTER TABLE ONLY t1 ADD CONSTRAINT fk_t1_t2_id FOREIGN KEY(id) REFERENCES
> t2(id)
> ON DELETE CASCADE
> DEFERRABLE INITIALLY DEFERRED;
> COMMIT;
>
> t1 has 55 million rows
> t2 has 72 million rows
>
> I have tried set constraints deferred, immediate, the id column on table 2
> is indexed, its the primary key. There may be memory settings to tweak, I
> was able to get it to run on a faster test server with local storage in
> about 10 minutes, but it was running for over an hour in our production
> environment.. We took down the application and I verified it wasnt waiting
> for an exclusive lock on the table or anything, it was running the alter
> table command for that duration.
>
> An additional question - is there any way to check how long postgres is
> estimating an operation will take to complete while it is running?
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2011-06-06 20:17:15 | Re: any tricks to get foreign key constraint builds faster |
Previous Message | Tom Lane | 2011-06-06 14:02:54 | Re: How I do from PostgreSQL to Oracle Db Link |