When should I use TRUNCATE TABLE, and when should I use DELETE FROM TABLE?
Truncate and delete are both used for removing rows from a table. However, they have subtle differences in behavior that is important to note.
The TRUNCATE operation is a metadata operation like an ALTER, and as such, it does not require additional memory usage or overhead to remove the rows from the table. Like an ALTER, it takes a momentary lock on the table. See the Remarks section of the TRUNCATE documentation for more information.
The DELETE operation consumes memory because a DELETE will label the rows as "deleted", but will not immediately physically delete them. After rows are deleted using the DELETE command, the rows are designated as deleted until the garbage collection process occurs in the background, eventually clearing the deleted data.
Truncating a table is preferred if you want to remove all table data, while delete is the only approach you can take if you want to selectively remove data from a table. If you are deleting large portions of data and having issues, it's recommended that you try deleting in smaller chunks (i.e., using the LIMIT parameter of the DELETE command) to spread out the overhead of the delete operation.