R010 · default BLOCK

TRUNCATE in production migration

TRUNCATE destroys all data in the table and acquires AccessExclusiveLock. Almost always a mistake in a production migration.

Why this is a problem

If you need to delete specific rows, use DELETE with a predicate. If you need to replace a table, use the rename pattern. dbivio defaults TRUNCATE to BLOCK; opt in via repo settings if your environment is non-production (e.g., test fixtures).

Don't do this

TRUNCATE TABLE orders;

Do this instead

-- Targeted delete with predicate:
DELETE FROM orders WHERE created_at < NOW() - INTERVAL '1 year';

-- Or replace the table without TRUNCATE:
BEGIN;
  CREATE TABLE orders_new (LIKE orders INCLUDING ALL);
  -- migrate data into orders_new
  DROP TABLE orders;
  ALTER TABLE orders_new RENAME TO orders;
COMMIT;

References

• PG: TRUNCATE →