High Performance PostgreSQL for Rails

Comparing Direct Updates and Clone and Replace

For your scrubbing system, you’ll compare two techniques that we’ll call direct updates and clone and replace.

Direct updates are SQL UPDATE statements that overwrite values in place. Direct updates work well for smaller tables, for example, ones with less than one million rows. This strategy doesn’t scale for very large tables because UPDATE statements slow down as row counts increase (you’ll learn more about that later).

Direct updates are more straightforward to write and automate, but they do have a trade-off in that maintenance must run afterward. You haven’t yet worked much with maintenance, so we’ll just touch on the basics here. Due to the design of PostgreSQL, an UPDATE statement leaves ...