I'm surprised no one has mentioned perhaps it's a good idea to partition this table while adding the pk. By your own statements the table is difficult to work with as is. Without partitioning the table, row inserts would need to walk the pk index and could be a factor. If this is static table then perhaps that's ok but if not...
Anyway I don't recall what type the ID was or how it's set but i suggest you seriously investigate using it to partition this table into manageable smaller tables.
Best of luck.
On Sun, Feb 4, 2024, 8:00 AM Marcos Pegoraro <marcos@xxxxxxxxxx> wrote:
insert into mytable2 select * from mytable1 order by ctid limit 10_000_000 offset 20_000_000;You can use min/max values grouping them by 10_000_000 records, so you don´t need that offset, then generate commands and run them.
select format('insert into mytable2 select * from mytable1 where i between %s and %s;', max(i), min(i)) from t group by i/10_000_000;
