ReviseAlgo Logo

Data Modification

INSERT, UPDATE, and DELETE

Core write, modify, and delete database operations.

Last Updated: June 15, 2026 • 18 min read

1. Introduction

DML (Data Manipulation Language) statements are the workhorses of database operations. INSERT adds new rows, UPDATE modifies existing rows, and DELETE removes rows. Together, they form the CRUD (Create, Read, Update, Delete) foundation that every application relies on for persistent data management.

2. Why It Matters

  • Application backbone: Every web API, mobile app, and batch job depends on DML to persist user actions, process orders, and update state.
  • Data integrity: Properly written DML respects constraints (foreign keys, unique, NOT NULL) and prevents silent data corruption.
  • Performance impact: Bulk INSERTs, batch UPDATEs, and careful DELETE strategies can mean the difference between sub-second operations and hours-long maintenance windows.
  • Audit and compliance: Understanding DML behavior is critical for audit trails, soft deletes, and regulatory requirements like GDPR right-to-erasure.

3. Real-World Analogy

Think of a library's card catalog system. INSERT is adding a new book card to the catalog. UPDATE is correcting the author's name on an existing card. DELETE is removing a card when the book is permanently withdrawn. Each operation must be precise — removing the wrong card loses a book, and adding a duplicate creates confusion.

4. How It Works

INSERT adds one or more rows to a table:

UPDATE modifies existing rows based on a condition:

DELETE removes rows matching a condition:

5. Internal Architecture

PostgreSQL never modifies rows in place. Every UPDATE creates a new tuple version, and DELETE marks the old version invisible. The VACUUM process later reclaims space from dead tuples. This design enables MVCC — concurrent readers don't block writers and vice versa.

6. Visual Explanation

7. Practical Example

Bulk insert with COPY for maximum performance:

Safe update pattern with transaction:

8. Common Mistakes

UPDATE or DELETE without WHERE

Running UPDATE products SET price = 0 without a WHERE clause sets every product's price to zero. Always write the WHERE clause first, then verify with a SELECT before executing.

INSERT without column list

Writing INSERT INTO users VALUES (...) without specifying columns breaks when columns are added or reordered. Always list columns explicitly.

Ignoring foreign key cascade effects

Deleting a parent row can cascade delete child rows silently. Always check FK constraints with ON DELETE CASCADE vs ON DELETE SET NULL vs the default RESTRICT behavior.

Interview Insight

"Why does PostgreSQL create a new row for UPDATE instead of modifying in place?" — Because of MVCC. Other transactions reading the old row version must still see the original data. In-place modification would break snapshot isolation.

9. Quick Quiz

Q1: What happens if you run DELETE without a WHERE clause?

Answer: All rows in the table are deleted (but the table structure remains). This is different from TRUNCATE which is faster and resets identity columns.

Q2: Why is COPY faster than INSERT for bulk loading?

Answer: COPY bypasses the per-row overhead of INSERT (planning, constraint checking per row, WAL per row). It streams data directly into table pages with a single WAL flush, making it 10-100x faster for large datasets.

10. Scenario-Based Challenge

Challenge: E-Commerce Order Fulfillment

Design the DML operations for fulfilling an order:

  1. INSERT a new order and order_items in a single transaction.
  2. UPDATE inventory to decrement stock quantities for each item.
  3. Handle the case where stock is insufficient — ROLLBACK the entire transaction.
  4. DELETE any expired cart items older than 7 days using a scheduled cleanup query.
  5. Add soft-delete support: UPDATE orders SET deleted_at = NOW() instead of actual DELETE.

11. Debugging Exercise

Find the bugs in this update query:

Issues:

  1. Wrong formula: total * 0.10 sets total to 10% of original (90% discount). Fix: total * 0.90 for a 10% discount.
  2. No transaction wrapping: If this affects more rows than expected, you can't undo it. Wrap in BEGIN/COMMIT and verify the row count first.

12. Interview Questions

Q1: What is the difference between DELETE and TRUNCATE?

A: DELETE removes rows one by one, fires triggers, and can have a WHERE clause. TRUNCATE deallocates pages instantly, resets identity columns, and acquires an exclusive table lock. TRUNCATE is much faster but cannot be filtered and may not fire triggers.

Q2: How does PostgreSQL handle UPDATE internally?

A: PostgreSQL never modifies rows in place. UPDATE creates a new tuple version and marks the old one as deleted (xmax). This enables MVCC — concurrent transactions still see the old version. Dead tuples are later reclaimed by VACUUM.

Q3: How do you safely perform bulk deletes on a production table?

A: Use batch deletes with LIMIT (delete in chunks of 1000-10000), wrap each batch in a transaction, add a delay between batches, and run during off-peak hours. This prevents long locks, excessive WAL generation, and table bloat from overwhelming VACUUM.

13. Production Considerations

  • Batch size limits: Break large INSERT/UPDATE/DELETE operations into batches of 1,000–10,000 rows to avoid lock escalation, excessive WAL, and transaction log bloat.
  • VACUUM tuning: Heavy DELETE operations create dead tuples. Ensure autovacuum is configured aggressively for high-churn tables (autovacuum_vacuum_scale_factor = 0.01).
  • Soft deletes: Consider adding a deleted_at column instead of hard deletes. This preserves audit trails and simplifies data recovery.
  • COPY for bulk loads: Use COPY instead of multi-row INSERT for loading more than 10,000 rows — it's dramatically faster.
  • Trigger overhead: Every INSERT/UPDATE/DELETE fires associated triggers. Audit triggers on high-write tables can double write latency — profile carefully.