Mark Chavez
eat. sleep. repeat.
👾 coding, ⌨️ keyboarding, 🚴🏼 cycling.
Adding timestamps to existing DB table
07/28/2023
Your database tables should always have timestamps. Without these, investigating future bug reports will be hard as hell. Always have timestamps.
Adding timestamps to existing tables # Make sure to set the timestamps with the NOT NULL constraint
add_timestamps :table_name, null: true
# Backfill timestamps of old records
time = Time.zone.parse("2000-01-01 00:00:00")
update "UPDATE table_name SET created_at = '#{time}'"
update "UPDATE table_name SET updated_at = '#{time}'"
# Restore NOT NULL constraints to be in-line with the Rails default
change_column_null :table_name, :created_at, false
change_column_null :table_name, :updated_at, false
💡
Also keep in mind how large the table is. There may be instances where you have to add
disable_ddl_transaction! to run the migration without transactions in context.
Backfilling references:
- https://medium.com/@aweirdcraft/backfilling-data-in-rails-5-0-few-thoughts-on-writing-safe-migrations-1f4c8c4454c9
- https://medium.com/doctolib/stop-worrying-about-postgresql-locks-in-your-rails-migrations-3426027e9cc9
About 2 years ago, we had our first downtime due to a migration, we added a column without any constraint on a relatively small table (less than 100k rows), which looks pretty safe, and the platform went down for a whole minute.