Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Tue, May 19, 2020 at 10:06 AM Ron <ronljohnsonjr@xxxxxxxxx> wrote:
On 5/19/20 11:51 AM, Tory M Blue wrote:


On Tue, May 19, 2020 at 6:40 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Tory M Blue <tmblue@xxxxxxxxx> 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 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux