2

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.

  • An alter table will take an exclusive lock and will block read write access. – a_horse_with_no_name Nov 3 '18 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 '18 at 21:54
  • 1
    adding a column or deleting a column is faster, updating all rows is going to be slow. – Jasen Nov 4 '18 at 4:04
  • Why not store the client-added fields using jsonb? – Colin 't Hart Nov 5 '18 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 '18 at 3:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.