On Mon, 28 Nov 2022 at 12:46, Venkata B Nagothi <nag1010@gmail.com> wrote: > Coming back to this thread after a while.. we have to remove OID on a 6 TB (5 TB of indexes) table and ALTER TABLE is gonna block the table and is gonna take hours...
You may want to look into exploiting table inheritance for this. Something like:
create table tab (a int, b int) with oids; -- the existing table
begin; -- do make the following atomic alter table tab rename to old_tab; create table tab (a int, b int) without oids; -- new version of the table, without oids alter table old_tab inherit tab; -- make it so querying the new table also gets rows from the old table. commit;
-- do this a bunch of times over the course of a few days until old_tab is empty. with del as (delete from old_tab where a in (select a from old_tab limit 1000) returning *) insert into tab select * from del;
you can then drop the old table.
You'll need to think carefully about unique constraints and any other constraints which are on the table in question. You'll want to do a lot of testing before committing to doing this too.