3

I have to ALTER a date field to timestamp on a huge table having 1 billion records.

I am using Postgres 9.5. I learnt that when we use alter query a heavy “access exclusive” lock is acquired, which shuts everything else out of the table.

How can we achieve this efficiently?

2

2 Answers 2

4

How to do it efficiently and how to do it without blocking everyone else for the duration are diametrically opposed goals. The efficient way is to schedule a downtime window in which to do it.

If that can't be done, then:

  • Add a new column of the desired type, with a NULL default value.

  • Change all the code which changes that old column or inserts new rows to make sure they correctly populate the new column as well (but make sure they don't depend on the new value already being correct--they just make it be correct)

  • Create an index to efficiently find rows where the new column is still NULL

  • Write a script to update rows with NULL values of new column to set them to be correct based on the value of the old column. Do this in chunks that are as large as feasible, given that the first row in the chunk will be locked for the duration of the processing of the chunk.

  • Once all chunks of this are done and verified, change all the code to rely on the new column rather than the old column.

  • Change all the code to stop trying to maintain the old column

  • Drop the old column

  • Drop the index that supports finding still-NULL values of the new column (unless it is still useful)

3
  • Your method needs in change of client code. It can be difficult or ever impossible. I think Your method can be modified as: Change all the code which changes can be replaced with trigger copied data from old column to new one while insert or update; Change all the code to stop + Drop the old column can be replaced with drop old + rename new + kill trigger under table lock (all operations seems to be fast).
    – Akina
    May 29, 2018 at 20:37
  • If you are changing the type from date to timestamptz, isn't the client code likely going to need to change anyway? But yes, the trigger could simplify things in some of the steps.
    – jjanes
    May 30, 2018 at 0:55
  • If you are changing the type from date to timestamptz, isn't the client code likely going to need to change anyway? Change of datatype is the main task. So I think the author is ready for it, he understands that some parts of client code is to be changed without alternative.
    – Akina
    May 30, 2018 at 4:22
0

Hello However i don't know how big the data and table size is. but in my opinion.

You can also do this in your test environment also.

  1. Schedule a downtime for website is good.

  2. Take the full backup of the database or the particular table with satisfactory measurements like don't forget to make not of primary and foreign and indexes also don't forget to procedure,functions backup also just make sure everything is fine.

  3. drop/rename the table and then create the New table with your specific timestamp setting for that particular column.

  4. Dump your data back into the table. In this Step Here you have to only check or use the date to timestamp conversion function if needed

  5. Benefit is that you don't have to change the Application Side code. until on those specific points where you are either casting or asking specifically particular date format either in **select query** or **insert query**. Don't forget to make indexes on column for better performance.

  6. If everything is fine then you can drop/delete that renamed table.

I hope it will give some idea.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.