405 — Performance Tuning and Security

Advanced

Master the two pillars of production databases: making queries fast with EXPLAIN ANALYZE, query plans, and indexing strategies, and keeping data safe with SQL injection prevention, parameterized queries, role-based access control, and row-level security. This lesson continues the compact PostgreSQL schema you created in Lessons 402-404 inside the `pg-sql-course` container.

Learning Objectives

Read and interpret EXPLAIN ANALYZE output in PostgreSQL

Understand query plan nodes: sequential scan, index scan, nested loop, hash join

Identify slow queries using pg_stat_statements

Apply common optimization patterns to speed up queries

Understand the N+1 query problem and how to avoid it

Explain SQL injection and demonstrate how it works

Write parameterized queries and prepared statements to prevent injection

Implement role-based access control with GRANT and REVOKE

Understand row-level security for multi-tenant databases

Step 1

EXPLAIN ANALYZE — your performance microscope

EXPLAIN ANALYZE runs a query and shows exactly how PostgreSQL executed it: which tables it scanned, which indexes it used, and how long each step took. Every example in this lesson uses the `devops_sql` schema from Lessons 402-404, not the separate capstone project in Lesson 501.

Commands to Run

docker exec -it pg-sql-course psql -U postgres -d devops_sql

-- Basic EXPLAIN (shows plan without running the query):
EXPLAIN SELECT * FROM orders WHERE status = 'delivered';

-- EXPLAIN ANALYZE (runs the query and shows actual times):
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'delivered';

-- Verbose format with buffers:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.name, o.order_id, o.status
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'delivered';

-- Compare: query without index vs with index
EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'Electronics';
CREATE INDEX idx_products_category ON products(category);
EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'Electronics';

What This Does

EXPLAIN shows what PostgreSQL plans to do. EXPLAIN ANALYZE actually executes the query and shows real timings. Key metrics: 'Seq Scan' means it read every row (slow for large tables). 'Index Scan' means it used an index (fast). 'actual time' shows milliseconds for the first row and total. 'rows' shows how many rows each step processed. BUFFERS shows disk I/O. After adding an index, notice the plan switches from Seq Scan to Index Scan.

Expected Outcome

Before the index: Seq Scan on products. After: Index Scan using idx_products_category. The actual time may be similar (small table), but the plan change is what matters for large tables.

Pro Tips

1
Always use EXPLAIN ANALYZE (not just EXPLAIN) for real performance analysis
2
Read plans bottom-up — the innermost node executes first
3
Watch for Seq Scan on large tables — it often means a missing index
4
BUFFERS shows whether data came from cache (shared hit) or disk (read)

Common Mistakes to Avoid

⚠️Using EXPLAIN ANALYZE on DELETE or UPDATE queries — it actually runs them! Use BEGIN/ROLLBACK to protect data
⚠️Assuming Seq Scan is always bad — for small tables, it's faster than an index lookup
⚠️Only looking at total time — check row estimates vs actuals to find planning errors

Was this step helpful?