TRUNCATE vs DELETE: Efficiently Clearing Data from a Postgres Table

Behind every fast direct mail campaign is solid engineering. While you're focused on conversion rates, we make sure the foundation is strong—so your campaigns don't miss a beat.

Understand the overhead

Our team's productivity lives and dies by how fast this test suite runs. These tests happen constantly and are crucial for getting code deployed.

You might be surprised by the overhead of clearing table data, as we were when we measured exactly how much time it took. Let's explore why it matters.

When we swapped the 'TRUNCATE's for 'DELETE's, the overhead of each test dropped to 1-3 milliseconds. This reduced the combined overhead of all the tests to a total of about 10 seconds. In the graph below, you can see that average test suite run time dropped by over 33%—from over 3 minutes to about 2 minutes.

‍

Understand the difference between TRUNCATE and DELETE

You'll likely be excited about this substantial performance boost, but you might wonder: how did switching from 'TRUNCATE' to 'DELETE' make such a difference?

On the surface, both commands do the same thing: your data disappears. But under the hood? They work completely differently. With 'DELETE's, dead rows remain in database pages and their dead pointers are still present in indices. 'TRUNCATE', on the other hand, is a bit more thorough in wiping the slate clean. The resulting table looks almost identical internally to a newly 'CREATE'ed table. There are no dead rows, the indices are empty, and the table statistics are reset. There are quite a few other differences as well, all highlighted by a terrifically detailed post by Craig Ringer.

Also, 'TRUNCATE' is a constant time operation while 'DELETE' complexity depends on the number of rows in the table. 'TRUNCATE' is a fast operation to quickly clear out huge tables, but 'DELETE' is much faster when a table contains just a handful of rows. This explains why you'll see significant speed improvements in similar scenarios: when your tests only insert a handful of rows.

Conclusion

What made this change so satisfying? We got massive speed improvements with a simple fix.

Additional resources

• This wasn't the first time the team needed to level-up our SQL know-how; here's how we tackled a deep dive into PostgreSQL.
• How we run Postgres database changes with zero downtime.

From the archives:

• Convenient SQL for Monitoring Postgres Database Health
  (an open-source library of SQL snippets that aid in Postgres administration)
• Automated VACUUM's are helpful in keeping Postgres lean and performant. However, these regular cleanups can miss some types of database dust bunnies:
  Running VACUUM FULL on RDS Postgres with minimal downtime
  ‍

‍