From: | Tory M Blue <tmblue(at)gmail(dot)com> |
---|---|
To: | Ron <ronljohnsonjr(at)gmail(dot)com> |
Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Huge tables, trying to delete OID's taking 6+hours per table |
Date: | 2020-05-20 16:22:01 |
Message-ID: | CAEaSS0anJmJ_A9X5LSxAUd+-xdWWiP4Mf8U+PTjnVpF0tM8ASA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, May 19, 2020 at 10:06 AM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
> On 5/19/20 11:51 AM, Tory M Blue wrote:
>
>
>
> On Tue, May 19, 2020 at 6:40 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Tory M Blue <tmblue(at)gmail(dot)com> writes:
>> > The command i'm using is
>> > ALTER TABLE tablename SET WITHOUT OIDS;
>> > Would a drop column oid be better?
>>
>> Unfortunately, you're kind of stuck. OIDs are not like regular columns
>> (at least before v12) --- they are integrated into the tuple header in
>> a hackish way, and so there's no way to get rid of them without a table
>> rewrite.
>>
>> regards, tom lane
>>
>
> Poop :) kind of figured that, so it's just painful.
>
> But I guess if it's doing a table rewrite, is there any configuration
> params I could boost to help it? Shared_buffers, give it more, work mem,
> maintenance mem, temp buffers anything you can think of?
>
>
> There's an alternative if this is a "transaction table" (named, in this
> example, FOO) which never gets updated (only inserted into and selected
> from).
>
> Create a new, partitioned, oid-free copy of the table (named NEW_FOO)
> that's populated with *most* of the records (all except the most
> recent). When ready to cut over, you'd stop the applications, copy over
> the most current records from FOO to NEW_FOO and then rename FOO to OLD_FOO
> and FOO to OLD_FOO.
>
> Then you can drop OLD_FOO.
>
> --
> Angular momentum makes the world go 'round.
>
Thanks Ron,
Looked into this but we have large indexes that take 8-12 hours to create.
So my gut says this would not buy us anytime. Also this has been running
for 16 hours now and still not done. I think it's forcing index creation
regardless. Really a crappy situation!!!
Tory
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick FICHE | 2020-05-20 16:36:30 | SET ROLE and search_path |
Previous Message | edavis | 2020-05-20 16:16:41 | Re: PostgreSQLBook |