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

On Tue, May 19, 2020 at 12:17 AM Tory M Blue <tmblue@gmail.com> wrote:

The db is only 2TB, these tables are about 50% of the DB.

Just wondering what I can boost to give me some elevated temp performance
for what I would think would be a fairly quick operation.

Give this query more work_mem? Mine are set pretty low, based on previous
performance notes, this is a 9.5 server.

Thanks
Tory

The command i'm using is

ALTER TABLE tablename SET WITHOUT OIDS;

Would a drop column oid be better?

Tory

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 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?

Thanks again Tom

Tory

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
<mailto:tgl@sss.pgh.pa.us>> wrote:

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

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. 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

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

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

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

--
Angular momentum makes the world go 'round.