I'm currently evaluating PostgreSQL for an app we are developing which comes with a weird usecase. We want to allow clients to create new fields which in turn will create new columns in a client-specific table. At the same time, suppose a field needs to be updated from type int to (say) float, it should change the datatype in the table as well (of course with consideration for what the data in the table is and if implicit conversion is even possible).
I was wondering what the impact of such an operation is on the table. Does this get an exclusive lock as with some other table updation methods? I cannot find any data in the documentation about the performance of such updates.
Obviously, this operation is not expected to happen often, but I want to ensure if/when it does, the table does not completely lock up for a long time with reads/writes being blocked.
Pointers to Postgres performance under datatype updates would be appreciated.