Re: Huge tables, trying to delete OID's taking 6+hours per table

On 5/20/20 11:22 AM, Tory M Blue wrote:
>
>
> On Tue, May 19, 2020 at 10:06 AM Ron <ronljohnsonjr(at)gmail(dot)com
> <mailto: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
>> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>>
>> Tory M Blue <tmblue(at)gmail(dot)com <mailto: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.

Since the index creation would be separate from your application's access,
it doesn't actually matter.

> Also this has been running for 16 hours now and still not done. I think
> it's forcing index creation regardless.
>

If it's rewriting the table's pages, then the indexes must be rewritten, too,

When removing OIDs, is the priority to minimize absolute time, or to
minimize downtime/degradation as sen by the application?

--
Angular momentum makes the world go 'round.