Stored Procedures, Triggers & Functions

Moving logic into the database: when, why, and how

Database Logic: Power and Trade-offs

Should validation logic live in your application code or database constraints? Should calculations happen in Python or SQL functions? Should audit logging use application middleware or database triggers? These questions divide developers into camps: those who keep databases "dumb" (storage only) and those who leverage database logic (stored procedures, triggers, functions) for performance, consistency, and enforcement. This lesson covers stored procedures (reusable SQL code blocks), triggers(automatic actions on INSERT/UPDATE/DELETE), and user-defined functions(custom operations). You'll learn when database logic outperforms application logic (complex queries, atomic operations, data integrity), when it creates problems (testing, portability, version control), and how companies like GitHub, Stripe, and Netflix balance these trade-offs.

The Great Debate: Application logic is easier to test, version, and scale horizontally. Database logic is faster for data-heavy operations, enforces constraints universally, and can't be bypassed. Most production systems use both strategically: triggers for audit trails, functions for complex calculations, procedures for batch operations, but keep business logic in application code.

Database Logic vs Application Logic: The Trade-offs

The fundamental question: where should your logic live? Understanding the trade-offs helps you make informed architectural decisions.

Application Logic (Python/Java/Node)

Advantages:

Easy to test (unit tests, mocks)
Version control friendly (Git)
Language flexibility (use best tool)
Scales horizontally (add app servers)
Database-agnostic (switch DB easily)

Disadvantages:

Network round-trips (slower)
Can be bypassed (direct DB access)
Data transferred to app (memory)
Consistency harder across apps

Database Logic (SQL/PL/pgSQL)

Advantages:

Fast (no network, processes data in-place)
Enforced universally (can't bypass)
Atomic operations (no race conditions)
Direct data access (no serialization)
Set-based operations (bulk processing)

Disadvantages:

Harder to test (DB required)
Version control awkward (migrations)
Vendor lock-in (PostgreSQL-specific)
Scales vertically only (DB bottleneck)
Debugging harder (limited tooling)

Example: Calculating Order Total

APPLICATION LOGIC (Python):
┌──────────────────────────────────────────────┐
│  1. Query order items from database          │
│  2. Transfer data to Python (network)        │
│  3. Loop through items, sum prices           │
│  4. Apply discount logic in Python           │
│  5. Calculate tax in Python                  │
│  6. Update order total back to database      │
└──────────────────────────────────────────────┘
Pros: Testable, flexible, version controlled
Cons: Slow (network), can be inconsistent

DATABASE LOGIC (SQL Function):
┌──────────────────────────────────────────────┐
│  1. Call function: calculate_order_total()   │
│  2. Database processes everything in-place   │
│  3. Returns result immediately               │
└──────────────────────────────────────────────┘
Pros: Fast (no network), atomic, consistent
Cons: Harder to test, DB-specific syntax

Choose based on your priorities: testability vs performance, flexibility vs consistency

User-Defined Functions (UDFs)

User-defined functions encapsulate reusable logic in the database. They can return single values (scalar functions) or tables (table-valued functions).

Scalar Functions (Return Single Value)

-- Create function to calculate order total with tax
CREATE OR REPLACE FUNCTION calculate_order_total(
    order_id_param INTEGER,
    tax_rate DECIMAL DEFAULT 0.08
)
RETURNS DECIMAL(10, 2)
LANGUAGE plpgsql
AS $$
DECLARE
    subtotal DECIMAL(10, 2);
    total DECIMAL(10, 2);
BEGIN
    -- Calculate subtotal from order items
    SELECT COALESCE(SUM(quantity * unit_price), 0)
    INTO subtotal
    FROM order_items
    WHERE order_id = order_id_param;

    -- Apply tax
    total := subtotal * (1 + tax_rate);

    RETURN total;
END;
$$;

-- Usage in query
SELECT
    order_id,
    customer_id,
    calculate_order_total(order_id) as total,
    calculate_order_total(order_id, 0.10) as total_with_10pct_tax
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days';

Functions can be used in SELECT statements, WHERE clauses, and JOIN conditions

Using Functions from Python

import psycopg2

conn = psycopg2.connect("dbname=ecommerce user=postgres")
cursor = conn.cursor()

# Call function directly
order_id = 12345
cursor.execute("SELECT calculate_order_total(%s)", (order_id,))
total = cursor.fetchone()[0]
print(f"Order {order_id} total: ${total:.2f}")

# Use function in query
cursor.execute("""
    SELECT
        o.order_id,
        o.customer_id,
        calculate_order_total(o.order_id) as total_amount
    FROM orders o
    WHERE o.status = 'pending'
    ORDER BY total_amount DESC
    LIMIT 10
""")

print("\nTop 10 pending orders by value:")
for order_id, customer_id, total in cursor.fetchall():
    print(f"  Order {order_id} (Customer {customer_id}): ${total:.2f}")

cursor.close()
conn.close()

Result:
Order 12345 total: $299.99

Top 10 pending orders by value:
  Order 67890 (Customer 101): $1,299.99
  Order 67891 (Customer 205): $899.50
  Order 67892 (Customer 310): $749.99
  ...

Table-Valued Functions (Return Rows)

-- Function that returns table of customer purchase statistics
CREATE OR REPLACE FUNCTION get_customer_stats(
    customer_id_param INTEGER
)
RETURNS TABLE (
    total_orders INTEGER,
    total_spent DECIMAL(10, 2),
    avg_order_value DECIMAL(10, 2),
    last_order_date DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(*)::INTEGER as total_orders,
        COALESCE(SUM(total_amount), 0) as total_spent,
        COALESCE(AVG(total_amount), 0) as avg_order_value,
        MAX(order_date)::DATE as last_order_date
    FROM orders
    WHERE customer_id = customer_id_param
        AND status = 'completed';
END;
$$;

-- Use like a regular table
SELECT * FROM get_customer_stats(12345);

-- Join with other tables
SELECT
    c.customer_id,
    c.email,
    s.total_orders,
    s.total_spent
FROM customers c
CROSS JOIN LATERAL get_customer_stats(c.customer_id) s
WHERE s.total_spent > 1000
ORDER BY s.total_spent DESC;

Table-valued functions can be joined with other tables using LATERAL

Calling Table-Valued Functions from Python

import psycopg2

conn = psycopg2.connect("dbname=ecommerce user=postgres")
cursor = conn.cursor()

# Get stats for specific customer
customer_id = 12345
cursor.execute("SELECT * FROM get_customer_stats(%s)", (customer_id,))

stats = cursor.fetchone()
if stats:
    total_orders, total_spent, avg_order, last_order = stats
    print(f"Customer {customer_id} Statistics:")
    print(f"  Total Orders: {total_orders}")
    print(f"  Total Spent: ${total_spent:.2f}")
    print(f"  Average Order: ${avg_order:.2f}")
    print(f"  Last Order: {last_order}")

# Get all high-value customers
cursor.execute("""
    SELECT
        c.customer_id,
        c.email,
        s.total_orders,
        s.total_spent
    FROM customers c
    CROSS JOIN LATERAL get_customer_stats(c.customer_id) s
    WHERE s.total_spent > 5000
    ORDER BY s.total_spent DESC
    LIMIT 10
""")

print("\nTop 10 High-Value Customers:")
for cust_id, email, orders, spent in cursor.fetchall():
    print(f"  {email}: {orders} orders, ${spent:.2f} spent")

cursor.close()
conn.close()

Result:
Customer 12345 Statistics:
  Total Orders: 24
  Total Spent: $8,499.76
  Average Order: $354.16
  Last Order: 2024-06-15

Top 10 High-Value Customers:
  alice@example.com: 45 orders, $15,299.99 spent
  bob@example.com: 38 orders, $12,450.00 spent
  ...

Stored Procedures

Stored procedures are similar to functions but designed for complex operations that modify data, handle transactions, and perform multiple steps. Unlike functions, they don't return values directly but can have OUT parameters.

Creating a Stored Procedure

-- Procedure to process an order (multi-step transaction)
CREATE OR REPLACE PROCEDURE process_order(
    p_customer_id INTEGER,
    p_product_ids INTEGER[],
    p_quantities INTEGER[],
    OUT p_order_id INTEGER,
    OUT p_total_amount DECIMAL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_product_id INTEGER;
    v_quantity INTEGER;
    v_price DECIMAL;
    v_available_stock INTEGER;
BEGIN
    -- Start transaction (implicit in procedure)

    -- Create order
    INSERT INTO orders (customer_id, status, order_date)
    VALUES (p_customer_id, 'pending', CURRENT_TIMESTAMP)
    RETURNING order_id INTO p_order_id;

    -- Initialize total
    p_total_amount := 0;

    -- Process each item
    FOR i IN 1..array_length(p_product_ids, 1) LOOP
        v_product_id := p_product_ids[i];
        v_quantity := p_quantities[i];

        -- Get product price and check stock
        SELECT price, stock_quantity
        INTO v_price, v_available_stock
        FROM products
        WHERE product_id = v_product_id
        FOR UPDATE;  -- Lock row

        -- Validate stock
        IF v_available_stock < v_quantity THEN
            RAISE EXCEPTION 'Insufficient stock for product %', v_product_id;
        END IF;

        -- Add order item
        INSERT INTO order_items (order_id, product_id, quantity, unit_price)
        VALUES (p_order_id, v_product_id, v_quantity, v_price);

        -- Update inventory
        UPDATE products
        SET stock_quantity = stock_quantity - v_quantity
        WHERE product_id = v_product_id;

        -- Add to total
        p_total_amount := p_total_amount + (v_price * v_quantity);
    END LOOP;

    -- Update order total
    UPDATE orders
    SET total_amount = p_total_amount
    WHERE order_id = p_order_id;

    -- Transaction commits automatically if no exception
    RAISE NOTICE 'Order % created successfully', p_order_id;
END;
$$;

Stored procedures handle complex multi-step operations atomically

Calling Stored Procedures from Python

import psycopg2

conn = psycopg2.connect("dbname=ecommerce user=postgres")
cursor = conn.cursor()

# Prepare order data
customer_id = 12345
product_ids = [101, 102, 103]  # Products to order
quantities = [2, 1, 3]         # Quantities for each

try:
    # Call stored procedure
    cursor.execute("""
        CALL process_order(%s, %s, %s, NULL, NULL)
    """, (customer_id, product_ids, quantities))

    # Fetch OUT parameters (order_id and total_amount)
    # Note: PostgreSQL CALL doesn't return OUT params directly in psycopg2
    # Alternative: use a function that returns a record

    # Get the created order
    cursor.execute("""
        SELECT order_id, total_amount, status
        FROM orders
        WHERE customer_id = %s
        ORDER BY order_date DESC
        LIMIT 1
    """, (customer_id,))

    order_id, total, status = cursor.fetchone()
    print(f"Order created successfully!")
    print(f"  Order ID: {order_id}")
    print(f"  Total: ${total:.2f}")
    print(f"  Status: {status}")

    # Commit transaction
    conn.commit()

except psycopg2.Error as e:
    print(f"Error creating order: {e}")
    conn.rollback()

finally:
    cursor.close()
    conn.close()

Result:
Order created successfully!
  Order ID: 67890
  Total: $299.97
  Status: pending

Alternative: Function with Transaction Control

-- Function version that returns order details
CREATE OR REPLACE FUNCTION create_order(
    p_customer_id INTEGER,
    p_items JSONB  -- [{"product_id": 101, "quantity": 2}, ...]
)
RETURNS TABLE (
    order_id INTEGER,
    total_amount DECIMAL,
    items_count INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id INTEGER;
    v_total DECIMAL := 0;
    v_item JSONB;
    v_price DECIMAL;
BEGIN
    -- Create order
    INSERT INTO orders (customer_id, status)
    VALUES (p_customer_id, 'pending')
    RETURNING orders.order_id INTO v_order_id;

    -- Process each item
    FOR v_item IN SELECT * FROM jsonb_array_elements(p_items)
    LOOP
        SELECT price INTO v_price
        FROM products
        WHERE product_id = (v_item->>'product_id')::INTEGER;

        INSERT INTO order_items (order_id, product_id, quantity, unit_price)
        VALUES (
            v_order_id,
            (v_item->>'product_id')::INTEGER,
            (v_item->>'quantity')::INTEGER,
            v_price
        );

        v_total := v_total + (v_price * (v_item->>'quantity')::INTEGER);
    END LOOP;

    -- Update order total
    UPDATE orders SET total_amount = v_total WHERE orders.order_id = v_order_id;

    -- Return result
    RETURN QUERY
    SELECT v_order_id, v_total, jsonb_array_length(p_items);
END;
$$;

-- Python usage:
import json

items = [
    {"product_id": 101, "quantity": 2},
    {"product_id": 102, "quantity": 1}
]

cursor.execute("""
    SELECT * FROM create_order(%s, %s::jsonb)
""", (customer_id, json.dumps(items)))

order_id, total, count = cursor.fetchone()
print(f"Created order {order_id}: ${total:.2f} ({count} items)")

Result:
Created order 67891: $249.98 (2 items)

Triggers: Automatic Database Actions

Triggers automatically execute functions in response to INSERT, UPDATE, or DELETE operations. They're perfect for audit trails, data validation, denormalization, and enforcing complex business rules.

Audit Trail Trigger

-- Create audit log table
CREATE TABLE user_audit_log (
    audit_id SERIAL PRIMARY KEY,
    user_id INTEGER,
    action VARCHAR(10),  -- 'INSERT', 'UPDATE', 'DELETE'
    old_data JSONB,
    new_data JSONB,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Trigger function for auditing
CREATE OR REPLACE FUNCTION audit_user_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO user_audit_log (user_id, action, new_data, changed_by)
        VALUES (NEW.user_id, 'INSERT', row_to_json(NEW)::jsonb, current_user);
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO user_audit_log (user_id, action, old_data, new_data, changed_by)
        VALUES (
            NEW.user_id,
            'UPDATE',
            row_to_json(OLD)::jsonb,
            row_to_json(NEW)::jsonb,
            current_user
        );
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO user_audit_log (user_id, action, old_data, changed_by)
        VALUES (OLD.user_id, 'DELETE', row_to_json(OLD)::jsonb, current_user);
        RETURN OLD;
    END IF;
END;
$$;

-- Attach trigger to users table
CREATE TRIGGER users_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_user_changes();

Triggers fire automatically on every INSERT/UPDATE/DELETE - no application code needed

Testing the Audit Trigger

import psycopg2

conn = psycopg2.connect("dbname=ecommerce user=postgres")
cursor = conn.cursor()

# Insert a user (trigger fires automatically)
cursor.execute("""
    INSERT INTO users (email, username, status)
    VALUES ('alice@example.com', 'alice', 'active')
    RETURNING user_id
""")
user_id = cursor.fetchone()[0]
conn.commit()

print(f"Created user {user_id}")

# Update the user (trigger fires again)
cursor.execute("""
    UPDATE users
    SET status = 'inactive'
    WHERE user_id = %s
""", (user_id,))
conn.commit()

print(f"Updated user {user_id}")

# Check audit log
cursor.execute("""
    SELECT
        audit_id,
        action,
        old_data->>'status' as old_status,
        new_data->>'status' as new_status,
        changed_at
    FROM user_audit_log
    WHERE user_id = %s
    ORDER BY changed_at
""", (user_id,))

print(f"\nAudit trail for user {user_id}:")
for audit_id, action, old_status, new_status, changed_at in cursor.fetchall():
    if action == 'INSERT':
        print(f"  [{changed_at}] INSERT - New status: {new_status}")
    elif action == 'UPDATE':
        print(f"  [{changed_at}] UPDATE - Status: {old_status} → {new_status}")

cursor.close()
conn.close()

Result:
Created user 12345
Updated user 12345

Audit trail for user 12345:
[2024-06-15 10:30:00] INSERT - New status: active
[2024-06-15 10:30:05] UPDATE - Status: active → inactive

Validation Trigger (BEFORE INSERT/UPDATE)

-- Trigger function to validate and normalize email
CREATE OR REPLACE FUNCTION validate_user_email()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Normalize email to lowercase
    NEW.email := LOWER(TRIM(NEW.email));

    -- Validate email format
    IF NEW.email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$' THEN
        RAISE EXCEPTION 'Invalid email format: %', NEW.email;
    END IF;

    -- Prevent blocked domains
    IF NEW.email LIKE '%@spam.com' OR NEW.email LIKE '%@temporary.com' THEN
        RAISE EXCEPTION 'Email domain not allowed: %', NEW.email;
    END IF;

    RETURN NEW;
END;
$$;

-- Attach BEFORE trigger (runs before INSERT/UPDATE)
CREATE TRIGGER validate_email_trigger
BEFORE INSERT OR UPDATE OF email ON users
FOR EACH ROW
EXECUTE FUNCTION validate_user_email();

BEFORE triggers can modify data (NEW) or prevent the operation by raising an exception

Testing Validation Trigger

import psycopg2

conn = psycopg2.connect("dbname=ecommerce user=postgres")
cursor = conn.cursor()

# Valid email (will be normalized to lowercase)
try:
    cursor.execute("""
        INSERT INTO users (email, username)
        VALUES ('Alice@Example.COM', 'alice')
        RETURNING email
    """)
    normalized_email = cursor.fetchone()[0]
    conn.commit()
    print(f"✓ Email normalized: {normalized_email}")
except psycopg2.Error as e:
    print(f"✗ Error: {e}")
    conn.rollback()

# Invalid email format (will fail)
try:
    cursor.execute("""
        INSERT INTO users (email, username)
        VALUES ('not-an-email', 'bob')
    """)
    conn.commit()
except psycopg2.Error as e:
    print(f"✗ Validation failed (expected): {e.pgerror.split('CONTEXT')[0].strip()}")
    conn.rollback()

# Blocked domain (will fail)
try:
    cursor.execute("""
        INSERT INTO users (email, username)
        VALUES ('user@spam.com', 'charlie')
    """)
    conn.commit()
except psycopg2.Error as e:
    print(f"✗ Domain blocked (expected): {e.pgerror.split('CONTEXT')[0].strip()}")
    conn.rollback()

cursor.close()
conn.close()

Result:
✓ Email normalized: alice@example.com
✗ Validation failed (expected): ERROR: Invalid email format: not-an-email
✗ Domain blocked (expected): ERROR: Email domain not allowed: user@spam.com

Denormalization Trigger (Maintain Computed Columns)

-- Keep order.item_count and order.total_amount in sync with order_items
CREATE OR REPLACE FUNCTION update_order_totals()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id INTEGER;
BEGIN
    -- Determine which order to update
    IF TG_OP = 'DELETE' THEN
        v_order_id := OLD.order_id;
    ELSE
        v_order_id := NEW.order_id;
    END IF;

    -- Recalculate order totals
    UPDATE orders
    SET
        item_count = (
            SELECT COUNT(*)
            FROM order_items
            WHERE order_id = v_order_id
        ),
        total_amount = (
            SELECT COALESCE(SUM(quantity * unit_price), 0)
            FROM order_items
            WHERE order_id = v_order_id
        )
    WHERE order_id = v_order_id;

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;

-- Trigger fires when order_items change
CREATE TRIGGER maintain_order_totals
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW
EXECUTE FUNCTION update_order_totals();

Denormalization triggers keep derived data in sync automatically

Testing Denormalization Trigger

import psycopg2

conn = psycopg2.connect("dbname=ecommerce user=postgres")
cursor = conn.cursor()

# Create an order
cursor.execute("""
    INSERT INTO orders (customer_id, item_count, total_amount)
    VALUES (12345, 0, 0)
    RETURNING order_id
""")
order_id = cursor.fetchone()[0]
conn.commit()

print(f"Created order {order_id}")

# Add items (trigger updates order totals automatically)
items = [
    (order_id, 101, 2, 49.99),  # 2 items at $49.99
    (order_id, 102, 1, 99.99),  # 1 item at $99.99
]

for order, product, qty, price in items:
    cursor.execute("""
        INSERT INTO order_items (order_id, product_id, quantity, unit_price)
        VALUES (%s, %s, %s, %s)
    """, (order, product, qty, price))

conn.commit()

# Check order totals (updated by trigger)
cursor.execute("""
    SELECT item_count, total_amount
    FROM orders
    WHERE order_id = %s
""", (order_id,))

item_count, total = cursor.fetchone()
print(f"\nOrder {order_id} totals (auto-updated by trigger):")
print(f"  Item count: {item_count}")
print(f"  Total amount: ${total:.2f}")

# Delete an item (trigger updates totals again)
cursor.execute("""
    DELETE FROM order_items
    WHERE order_id = %s AND product_id = 102
""", (order_id,))
conn.commit()

cursor.execute("""
    SELECT item_count, total_amount
    FROM orders
    WHERE order_id = %s
""", (order_id,))

item_count, total = cursor.fetchone()
print(f"\nAfter deleting one item:")
print(f"  Item count: {item_count}")
print(f"  Total amount: ${total:.2f}")

cursor.close()
conn.close()

Result:
Created order 67890

Order 67890 totals (auto-updated by trigger):
  Item count: 2
  Total amount: $199.97

After deleting one item:
  Item count: 1
  Total amount: $99.98

Performance Implications

Database logic can be much faster or much slower than application logic, depending on the operation. Understanding when each approach wins is critical for performance.

When Database Logic is Faster

Set-based operations: Processing millions of rows with UPDATE/DELETE
Complex joins: Database optimizer handles efficiently
Aggregations: SUM, COUNT, AVG on large datasets
No network overhead: Data stays in database
Parallel execution: Database uses multiple cores

When Database Logic is Slower

Row-by-row loops: CURSOR loops are slower than set operations
External API calls: Can't make HTTP requests from DB
Complex logic: Procedural code often slower than Python
Heavy triggers: Slow down every INSERT/UPDATE
Limited CPU: Database can't scale horizontally

Performance Comparison Example

import psycopg2
import time

conn = psycopg2.connect("dbname=ecommerce user=postgres")
cursor = conn.cursor()

# Task: Update prices for 100,000 products with 10% increase

# APPROACH 1: Application Logic (SLOW)
start = time.time()

cursor.execute("SELECT product_id, price FROM products")
products = cursor.fetchall()  # Transfer 100K rows to Python

for product_id, price in products:
    new_price = price * 1.10
    cursor.execute("UPDATE products SET price = %s WHERE product_id = %s",
                   (new_price, product_id))

conn.commit()
app_time = time.time() - start
print(f"Application logic: {app_time:.2f} seconds")

# APPROACH 2: Database Logic (FAST)
start = time.time()

cursor.execute("UPDATE products SET price = price * 1.10")
conn.commit()

db_time = time.time() - start
print(f"Database logic: {db_time:.2f} seconds")

print(f"\nSpeedup: {app_time / db_time:.1f}x faster")

cursor.close()
conn.close()

Result:
Application logic: 45.67 seconds
Database logic: 0.23 seconds

Speedup: 198.6x faster

Set-based SQL operations are orders of magnitude faster than row-by-row loops

Trigger Performance Impact

import psycopg2
import time

conn = psycopg2.connect("dbname=ecommerce user=postgres")
cursor = conn.cursor()

# Measure insert performance WITHOUT triggers
cursor.execute("DROP TRIGGER IF EXISTS users_audit_trigger ON users")
conn.commit()

start = time.time()
for i in range(10000):
    cursor.execute("INSERT INTO users (email, username) VALUES (%s, %s)",
                   (f'user{i}@test.com', f'user{i}'))
conn.commit()
no_trigger_time = time.time() - start

print(f"10,000 inserts WITHOUT trigger: {no_trigger_time:.2f} seconds")

# Re-enable trigger
cursor.execute("""
    CREATE TRIGGER users_audit_trigger
    AFTER INSERT ON users
    FOR EACH ROW EXECUTE FUNCTION audit_user_changes()
""")
conn.commit()

# Measure insert performance WITH trigger
start = time.time()
for i in range(10000, 20000):
    cursor.execute("INSERT INTO users (email, username) VALUES (%s, %s)",
                   (f'user{i}@test.com', f'user{i}'))
conn.commit()
with_trigger_time = time.time() - start

print(f"10,000 inserts WITH audit trigger: {with_trigger_time:.2f} seconds")
print(f"\nOverhead: {((with_trigger_time / no_trigger_time - 1) * 100):.1f}% slower")

cursor.close()
conn.close()

Result:
10,000 inserts WITHOUT trigger: 2.34 seconds
10,000 inserts WITH audit trigger: 3.51 seconds

Overhead: 50.0% slower
Triggers add overhead to every operation - keep them lightweight!

Strategic Use of Database Logic: Decision Framework

Use this framework to decide when to use database logic vs application logic:

Use Case	Recommended Approach	Reason
Audit trails	Database (Triggers)	Can't be bypassed, captures all changes universally
Data validation	Both	App for UX, DB constraints for enforcement
Complex calculations	Database (Functions)	Faster, no network overhead, reusable in SQL
Business logic	Application	Easier to test, version, and modify
Bulk updates	Database (SQL)	Set-based operations are orders of magnitude faster
External API calls	Application	Database can't make HTTP requests
Denormalization	Database (Triggers)	Keeps derived data in sync automatically
Report generation	Database (Functions)	Process data where it lives, return only results
Machine learning	Application	Better libraries, GPU support, flexibility
Referential integrity	Database (Constraints)	Enforced at database level, can't be bypassed

Golden Rule: Use database logic for data integrity, consistency, and performance-critical operations. Use application logic for business rules, external integrations, and anything requiring testability and flexibility. Don't be dogmatic - measure and choose based on your specific constraints.

Real-World Patterns from Production Systems

How do successful companies use database logic in practice?

GitHub's Approach

Minimal database logic:

No stored procedures or triggers
Foreign key constraints for integrity
Check constraints for simple validation
All business logic in Ruby/Go
Why: Easier to test, deploy, and scale horizontally

Stripe's Approach

Strategic database logic:

Triggers for audit logs (compliance)
Functions for financial calculations
Constraints for money invariants
Business logic still in application
Why: Financial correctness requires DB enforcement

Netflix's Approach

Hybrid approach:

Functions for recommendation scoring
Procedures for ETL batch jobs
No triggers (prefer event streams)
Microservices for business logic
Why: Use DB for what it does best, apps for everything else

Your Approach (Recommended)

Pragmatic balance:

✓ Triggers for audit trails
✓ Constraints for data integrity
✓ Functions for complex queries
✗ Business logic in database
✗ Heavy procedural code in DB

Key Takeaways

Functions: Reusable logic that returns values (scalar or tables)
Stored procedures: Multi-step operations with transaction control
Triggers: Automatic actions on INSERT/UPDATE/DELETE
BEFORE triggers: Validate and modify data before saving

AFTER triggers: Audit trails, denormalization, notifications
Performance: Set-based DB operations beat row-by-row loops
Testing: Application logic easier to test than DB logic
Balance: Use DB for integrity, app for flexibility

Remember: Database logic is powerful for data integrity, audit trails, and performance-critical operations. Application logic is better for testability, flexibility, and business rules. Most successful systems use both strategically: triggers ensure audit trails can't be bypassed, functions optimize complex queries, constraints enforce invariants, but business logic stays in application code where it's easier to test and evolve. Measure performance, prioritize maintainability, and choose the right tool for each job.

Back to "Partitioning & Sharding"

Databases AdvancedLesson 16

Next: "Time-Series Databases"