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

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.