Skip to main content

Posts

Showing posts from July 29, 2020

The missing guide to Application DBA tricks

Some tips about database development Be that guy... Image by  CommitStrip Table of Contents Update Only What Needs Updating UPDATE  is a relatively expensive operation. To speed up an  UPDATE  command it's best to make sure you only update what needs updating. Take this query for example that normalizes an email column: db=# UPDATE users SET email = lower ( email ); UPDATE 1010000 Time: 1583.935 ms (00:01.584) Looks innocent, right? the query updated emails of 1,010,000 users. But, did all rows really needed to update? db=# UPDATE users SET email = lower ( email ) db-# WHERE email != lower ( email ); UPDATE 10000 Time: 299.470 ms Only 10,000 rows needed to update. By reducing the amount of affected rows, the execution time went down from 1.5 seconds to just less than 300ms. Updating fewer rows also saves the database maintenance later on. Update Only What Needs Updating This type of large updates are very common in data migration scripts. So the next time you