1

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?

1

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';

Your Answer

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

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