The inexact wording of an interview question has been as follows.
A PostgreSQL database contains a table called “apps”, having millions of rows, numerous indexes, and is heavily used. There are at least two columns in this table:
"id": an integer serving as the primary key "type": a string containing unindexed values such as "SlackApp", "GithubApp", etc.
You need to update this table so all occurrences of a particular string in the “type” column are replaced with another string. This process should take place in a migration that will be executed from one of the fleet hosts while the other hosts in the fleet are still running, therefore this change should happen without requiring downtime.
How would you go about solving this problem? What is the estimated time spent in the migration process? What are the tradeoffs of your solution compared to other solutions?
My answer to the above question would be to issue a transactional SQL statement to keep atomicity of operations and that would be linearly dependent on number of rows. Is there much more detail that I could add to the answer?