Yeah definitely this is one of those big problems DBA encounters in large and busy environments. The solution is always painful but you described here a graceful solution. Thanks for sharing, best regards from Brazil.
Thanks for the awesome talk. I had a query, will we ever see pg_repack or logical replication type util that will help move data without this age old procedure. With logical replication i can move the db to another db live, but that would be too much.
pg_repack does something different, it can't help in this case. But copying the table onto a partitioned table on another server with logical replication is not as crazy as it sounds.
Thanks for your comment. This operation can't be done for free anyway, and that's exactly what autovacuum is there for. I would actually suggest increasing its limits before starting, because it's better to trade off a little performance than accumulate bloat. The point of this exercise is to perform a necessary action without blocking usage.
@@JimmyAngelakos in Our case, we use different approach to handle this data type issue with minimum downtime from apps, the downtime less than 5 mins actually
Following the procedure you presented, I find the primary key column(id) to be in the second place of the table. How can I put the primary column(ID) in the first column?
Why would you care about column order? One way to change it would be to recreate the table, which kind of defeats the purpose of this exercise. Another would be to repeat this procedure for all columns to rotate them until id becomes the first one. Again, time consuming and not worth the effort.
