How to properly rename column in postgresql if there are 2 or more instances?

Question

I was doing database migration, where I had to rename column product_id to column entity_id. It was an easy task at a glance, but when we as a team, deployed it in qa env, we've started to get massive number of exceptions, cause our older instance(lets say 1.0.0v) used previous column name.

I've used this simple migration at first

alter table products
    rename column product_id to entity_id;

But now I'm very confused, how can we rename column, so there will be zero downtime? I've thought about copying the whole column(product_id) to a new one(entity_id), maybe this will work? Is there any best practices about how to do that?

Answer 1

You can hide your underlying tables behind views and/or rule system: demo at db<>fiddle

create table products (product_id int);
insert into products values (1);

begin;
alter table products rename to t_products;
create view products
  with (check_option=cascaded,
        security_barrier=true,
        security_invoker=true)
  as table t_products; 
comment on view products is 'legacy layout of "t_products"';
comment on column products.product_id is 'legacy name of "t_products"."entity_id"';
alter table t_products rename product_id to entity_id;
commit;

You can manipulate the table all you want and no one who used products in their queries will notice. Even those who used select * from products or table products hoping the names, number and order of columns will never change, are safe. Until you decide to drop or alter the type of one of the original columns, the view keeps showing a "snapshot" of the table structure as of when it was set up, hiding new columns and automatically mapping current column order and naming to match the original layout.

alter table t_products add column new_column text;
select * from products;

product_id
1

The view also re-routes all DML appropriately, not just select - it qualifies as updatable:

insert into products values (2) returning *;

product_id
2

update products set product_id=3 where product_id=2 returning *;

product_id
3

delete from products where product_id=3 returning *;

product_id
3