Interview › Databases & Caching
What is the difference between DELETE, TRUNCATE, and DROP?
Databases & Caching · Intermediate level
Answer
DELETE removes selected rows, usually with a WHERE clause. TRUNCATE removes all rows from a table more quickly but with stronger table-level implications. DROP removes the table object and its definition entirely.
Technical explanation
DELETE is selective and can be batched, but huge deletes create logs, bloat, locks, and replica lag.
TRUNCATE is fast for emptying a table but usually takes stronger locks and affects the whole table.
DROP removes schema and data, so it requires backups, approvals, and migration controls.
Hands-on example
Safe batch delete:
DELETE FROM events WHERE id IN (SELECT id FROM events WHERE created_at < now() - interval '180 days' LIMIT 10000);
Repeat with sleeps and monitor locks, WAL, replica lag, and table bloat.
Check how well your resume matches the role with our free resume checker— match score, ATS check, and the skills you're missing.
More Databases & Caching interview questions
- What is Amazon RDS, and what does it manage for you versus self-managed databases?
- What database engines does RDS support?
- What is the difference between RDS and Aurora?
- What is Multi-AZ in RDS, and how does automatic failover work?
- How long does an RDS Multi-AZ failover typically take, and what triggers it?
- What is the difference between Multi-AZ and a read replica?
- When would you use a read replica, and can it become a standalone database?
- Can a read replica be in a different region, and why would you do that?