Updating unindexed values with postgresql question 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?
PostgreSQL doesn't table-lock on update, it row-locks.


  You need to update this table so all occurrences of a particular string in the “type” column are replaced with another string. 


That's simple,

UPDATE apps
SET type = regexp_replace(type, 'SearchRegex', 'Replacement', 'g')  
WHERE type ~ 'SearchRegex';