INSERT, UPDATE, and DELETE

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

4. How It Works

INSERT adds one or more rows to a table:

-- Single row insert
INSERT INTO customers (first_name, last_name, email)
VALUES ('Alice', 'Chen', 'alice@example.com');
-- Multi-row insert
INSERT INTO customers (first_name, last_name, email)
VALUES
  ('Bob', 'Smith', 'bob@example.com'),
  ('Carol', 'Jones', 'carol@example.com');
-- Insert from another table
INSERT INTO archive_customers
SELECT * FROM customers WHERE last_order_date < '2020-01-01';

UPDATE modifies existing rows based on a condition:

-- Simple update
UPDATE products SET price = price * 1.10 WHERE category = 'Electronics';

-- Update with JOIN (PostgreSQL extension)
UPDATE order_items oi
SET discount = 0.15
FROM orders o
WHERE oi.order_id = o.id
  AND o.customer_tier = 'Gold';
-- Update from subquery
UPDATE employees e
SET department_id = d.id
FROM departments d
WHERE d.name = 'Engineering'
  AND e.title LIKE '%Software%';

DELETE removes rows matching a condition:

-- Conditional delete
DELETE FROM sessions WHERE expires_at < NOW();

-- Delete with subquery
DELETE FROM order_items
WHERE order_id IN (SELECT id FROM orders WHERE status = 'cancelled');
-- Delete using JOIN (PostgreSQL extension)
DELETE FROM cart_items ci
USING products p
WHERE ci.product_id = p.id AND p.discontinued = true;

5. Internal Architecture

PostgreSQL DML Internals:
INSERT Flow:
  Query → Parse → Plan → Find target table pages
  → Check constraints (NOT NULL, UNIQUE, FK, CHECK)
  → Write new tuple to heap page → Update indexes
  → Write WAL record → Return success
UPDATE Flow (PostgreSQL uses MVCC):
  Query → Parse → Plan → Find target rows
  → Acquire row locks → Check constraints
  → Create NEW version of the row (not in-place edit)
  → Old row marked with xmax (deleted by transaction)
  → New row inserted with xmin (created by transaction)
  → Update indexes → Write WAL
DELETE Flow:
  Query → Parse → Plan → Find target rows
  → Acquire row locks → Set xmax on tuple
  → Row becomes invisible to future transactions
  → Physical removal deferred to VACUUM process

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:

-- COPY is 10-100x faster than INSERT for large datasets
COPY products (name, price, category)
FROM '/tmp/products.csv'
WITH (FORMAT csv, HEADER true);
-- Batch insert with generate_series for testing
INSERT INTO test_data (name, value, created_at)
SELECT
    'item_' || i,
    random() * 1000,
    NOW() - (random() * interval '365 days')
FROM generate_series(1, 100000) AS s(i);

Safe update pattern with transaction:

BEGIN;
-- Check how many rows will be affected
SELECT COUNT(*) FROM orders
WHERE status = 'pending' AND created_at < '2025-01-01';

-- If count looks right, proceed
UPDATE orders
SET status = 'expired'
WHERE status = 'pending' AND created_at < '2025-01-01';
-- Verify the change
SELECT status, COUNT(*) FROM orders GROUP BY status;
COMMIT;  -- or ROLLBACK if something looks wrong

8. Common Mistakes

9. Quick Quiz

10. Scenario-Based Challenge

11. Debugging Exercise

-- Intended: Give 10% discount to Gold customers
UPDATE orders
SET total = total * 0.10
FROM customers
WHERE orders.customer_id = customers.id
  AND customers.tier = 'Gold';

12. Interview Questions

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.