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

Tory M Blue <tmblue@gmail.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

On 5/19/20 11:51 AM, Tory M Blue wrote:

On Tue, May 19, 2020 at 6:40 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Tory M Blue <tmblue@gmail.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.

On 5/20/20 11:22 AM, Tory M Blue wrote:

On Tue, May 19, 2020 at 10:06 AM Ron <ronljohnsonjr@gmail.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@sss.pgh.pa.us> wrote:

Tory M Blue <tmblue@gmail.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?