4

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.

Improve this question
5
  • An alter table will take an exclusive lock and will block read write access.
    – user1822
    Nov 3, 2018 at 21:16
  • Is there something I can do to reduce the time it's locked? And how long will it be locked? Is there any benchmarking done for this?
    – navinpai
    Nov 3, 2018 at 21:54
  • 1
    adding a column or deleting a column is faster, updating all rows is going to be slow.
    – Jasen
    Nov 4, 2018 at 4:04
  • Why not store the client-added fields using jsonb?
    – Colin 't Hart
    Nov 5, 2018 at 16:07
  • I've considered that. But many user added fields need to be indexed and searchable as well, but I'm not sure what the performance on GIN indices on multiple different JSONb fields will be
    – navinpai
    Nov 6, 2018 at 3:30

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.