Deleting rows in production is one of the fastest ways to break an application if you’re not careful. The danger usually isn’t the DELETE itself — it’s deleting the wrong rows, or deleting too many rows at once, or ignoring foreign key constraints.

In this guide, we’ll walk through how to safely delete data using the Pagila Sample Database, but these techniques apply to any real production PostgreSQL system.

1. Always Start with a SELECT

Before you delete anything, you must confirm that your WHERE clause filters exactly the rows you expect. Let’s say we want to delete old rentals from before 2005.

SELECT rental_id, rental_date, customer_id
FROM rental
WHERE rental_date < '2005-01-01';
    

If this SELECT doesn’t return exactly what you expect, your DELETE won’t either.

2. Use LIMIT to Inspect a Small Sample

LIMIT helps you sanity-check your WHERE clause before making a big change.

SELECT rental_id, rental_date
FROM rental
WHERE rental_date < '2005-01-01'
LIMIT 20;
    

Only when you’re confident the filter is correct should you move forward.

3. Use a Transaction for Safety

A transaction lets you delete rows and then double-check your work before committing the change.

BEGIN;

DELETE FROM rental
WHERE rental_date < '2005-01-01';

-- Validate how many rows remain
SELECT COUNT(*) FROM rental;

-- If everything looks good:
COMMIT;

-- If something looks wrong:
ROLLBACK;
    

Never run a large DELETE in autocommit mode — that’s how accidents happen!

4. Delete in Batches to Avoid Locking

Large deletes create table bloat and can block other queries. Instead of deleting 100,000 rows at once, delete in small batches.

WITH batch AS (
  SELECT rental_id
  FROM rental
  WHERE rental_date < '2005-01-01'
  ORDER BY rental_id
  LIMIT 5000
)
DELETE FROM rental
WHERE rental_id IN (SELECT rental_id FROM batch);
    

Run this repeatedly until no rows match. The table stays responsive and locks stay short.

5. Use ORDER BY When Batching

Ordering makes batch deletes deterministic and avoids PostgreSQL scanning the table randomly.

WITH batch AS (
  SELECT rental_id
  FROM rental
  WHERE rental_date < '2005-01-01'
  ORDER BY rental_id
  LIMIT 5000
)
DELETE FROM rental
WHERE rental_id IN (SELECT rental_id FROM batch);
    

This reduces deadlocks and makes progress predictable.

6. Be Aware of Foreign Key Constraints

In Pagila, rental has foreign keys to inventory, customer, and staff. But more importantly, payment references rental. If you delete a rental, PostgreSQL must check that no payments depend on it.

To inspect foreign keys:

SELECT
  tc.table_name AS child_table,
  kcu.column_name AS child_column,
  ccu.table_name AS parent_table,
  ccu.column_name AS parent_column
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND ccu.table_name = 'rental';
    

This tells you exactly which tables will block or cascade your delete.

7. Consider Soft Deletes Instead

If you are not sure whether data should be permanently removed, add a soft delete flag. Pagila doesn’t include one by default, but here’s how it would look:

ALTER TABLE customer ADD COLUMN is_deleted BOOLEAN DEFAULT false;

UPDATE customer
SET is_deleted = true
WHERE active = 0;
    

Soft deletes are great when you need reversibility or auditability.

8. Copy Rows to a Backup Table Before Deleting

This technique is extremely common in real production environments. You delete from the main table but keep a temporary safety copy.

Example: Back up old payments before deleting them.

-- Create a backup table with the rows you plan to delete
CREATE TABLE payment_backup_2025_12_22 AS
SELECT *
FROM payment
WHERE payment_date < '2005-01-01';

-- Delete inside a transaction
BEGIN;

DELETE FROM payment
WHERE payment_date < '2005-01-01';

-- Validate the results
SELECT COUNT(*) FROM payment;
SELECT COUNT(*) FROM payment_backup_2025_12_22;

-- Approve the change
COMMIT;

-- Or undo it
-- ROLLBACK;
    

If everything looks good after a week or month, you can safely drop the backup table:

DROP TABLE payment_backup_2025_12_22;
    

This gives you peace of mind without permanently storing soft-deleted rows.

9. Clean Up Bloat After Big Deletes

Deletes leave behind dead tuples that PostgreSQL must eventually clean up. After a large purge, run:

VACUUM (ANALYZE) rental;
    

For very large purges:

VACUUM FULL rental;
    

But be careful — VACUUM FULL locks the table exclusively while it rewrites it.

Conclusion

Deleting rows in production doesn’t have to be scary — you just need a disciplined process. Always inspect your rows first, delete in batches, understand your foreign keys, and use transactions for safety. When in doubt, back up rows before deleting or use soft deletes instead.

Follow these principles in Pagila — and in real production systems — and you’ll avoid outages, lockups, and 3am emergencies!