PostgreSQL Advanced SQL & Performance

A. Query Performance

EXPLAIN

• Purpose: Shows query execution plan without running the query.

• Characteristics: Estimates costs, rows, and operations. Helps understand how PostgreSQL will execute query.

• Use: Analyze query structure, identify missing indexes, understand join strategies.

sql

-- Basic explain
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- With formatting options
EXPLAIN (FORMAT JSON) SELECT * FROM users;
EXPLAIN (VERBOSE) SELECT * FROM users;
EXPLAIN (COSTS, TIMING, BUFFERS) SELECT * FROM users;

-- Understanding output:
-- Seq Scan = Full table scan (slow for large tables)
-- Index Scan = Using index (fast)
-- Nested Loop = For joins
-- Hash Join = For larger joins
-- Sort = Sorting operation

EXPLAIN ANALYZE

• Purpose: Actually runs the query and shows real execution statistics.

• Characteristics: Shows actual vs estimated rows, execution time. Runs the query.

• Use: Get accurate performance metrics, identify bottlenecks.

sql

-- Run and analyze
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- With detailed statistics
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE created_at > '2024-01-01';

-- Output shows:
-- Planning time: Time to create plan
-- Execution time: Time to run query
-- Actual rows: Real rows returned vs estimated
-- Buffers: Disk I/O operations

Query Plans

• Purpose: PostgreSQL's strategy for executing a query.

• Characteristics: Tree structure of operations. Cost-based optimizer chooses best plan.

• Use: Understand execution order, identify optimization opportunities.

sql

-- Complex query plan example
EXPLAIN
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2023-01-01'
GROUP BY u.id
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC;

-- Common plan operations:
-- Seq Scan: Sequential scan (full table read)
-- Index Scan: Read via index
-- Index Only Scan: Everything from index (fastest)
-- Bitmap Heap Scan: Multiple index scans combined
-- Hash Join: Build hash table from one table
-- Nested Loop: For each row in outer table, scan inner
-- Merge Join: Both sides sorted, then merged
-- Sort: Sorting operation (expensive!)
-- Aggregate: Group by operations

Common Performance Pitfalls

sql

-- 1. Missing indexes
SELECT * FROM users WHERE lower(email) = 'test@example.com';
-- Solution: CREATE INDEX idx_users_lower_email ON users(lower(email));

-- 2. N+1 queries in application code
-- Instead of: Get user, then get orders for each user
-- Use: SELECT * FROM users JOIN orders ON users.id = orders.user_id;

-- 3. SELECT * (fetching unused columns)
SELECT * FROM users; -- BAD
SELECT id, name FROM users; -- BETTER

-- 4. Functions on indexed columns
SELECT * FROM users WHERE date_part('year', created_at) = 2024;
-- Solution: CREATE INDEX idx_users_created_year ON users(date_part('year', created_at));

-- 5. Large IN clauses
SELECT * FROM users WHERE id IN (1,2,3,...10000);
-- Better: Use JOIN with temporary table or WHERE EXISTS

-- 6. Not using LIMIT when appropriate
SELECT * FROM users ORDER BY created_at DESC; -- BAD
SELECT * FROM users ORDER BY created_at DESC LIMIT 100; -- BETTER

-- 7. Cross joins (cartesian products)
SELECT * FROM users, orders; -- BAD (returns users*orders rows)
SELECT * FROM users CROSS JOIN orders; -- EXPLICIT but same result

B. Index Types (Postgres-specific)

B-tree vs Hash

• B-tree: Default index. Balanced tree structure. Supports range queries, sorting, equality. Most versatile.

• Hash: Faster for exact equality matches only. No range queries. Less commonly used.

sql

-- B-tree (default, supports ranges)
CREATE INDEX idx_users_email ON users(email);  -- Default is B-tree
-- Supports: =, <, >, <=, >=, BETWEEN, LIKE 'prefix%'

-- Hash (equality only, faster)
CREATE INDEX idx_users_id_hash ON users USING HASH (id);
-- Supports only: =

-- Example comparison
SELECT * FROM users WHERE email = 'test@example.com'; -- Both work
SELECT * FROM users WHERE email LIKE 'test%'; -- Only B-tree works
SELECT * FROM users WHERE email > 'test'; -- Only B-tree works

GIN indexes (JSONB, arrays)

• Purpose: Generalized Inverted Index for composite values.

• Characteristics: Optimized for "contains" operations on arrays, JSONB, full-text search.

• Use: JSONB documents, array columns, full-text search vectors.

sql

-- For JSONB columns
CREATE INDEX idx_docs_data ON documents USING GIN (data);

-- Query with JSONB operators (uses index)
SELECT * FROM documents WHERE data @> '{"status": "active"}';
SELECT * FROM documents WHERE data ? 'email';

-- For array columns
CREATE INDEX idx_products_tags ON products USING GIN (tags);
SELECT * FROM products WHERE tags @> ARRAY['sale'];
SELECT * FROM products WHERE tags && ARRAY['sale', 'featured'];

-- For full-text search
CREATE INDEX idx_articles_content ON articles 
USING GIN (to_tsvector('english', content));

GiST indexes

• Purpose: Generalized Search Tree for complex data types.

• Characteristics: Supports geometric data, full-text search, network addresses. Slower than B-tree for simple types.

• Use: Geospatial data (PostGIS), range types, network addresses.

sql

-- With PostGIS extension (geospatial data)
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);
SELECT * FROM locations 
WHERE ST_DWithin(geom, ST_Point(0, 0), 1000);

-- For range types
CREATE TABLE reservations (
    room_id INT,
    during TSRANGE
);
CREATE INDEX idx_reservations_during ON reservations USING GIST (during);
SELECT * FROM reservations WHERE during && '[2024-01-15, 2024-01-20]';

BRIN indexes

• Purpose: Block Range INdex for very large tables with natural sort order.

• Characteristics: Very small size, fast for range scans on sorted data. Minimal maintenance.

• Use: Time-series data, append-only tables, large tables with correlated physical order.

sql

-- For time-series data (created_at naturally sorted)
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);
SELECT * FROM logs 
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

-- With pages_per_range tuning (default 128)
CREATE INDEX idx_sensors_timestamp ON sensor_data 
USING BRIN (timestamp) WITH (pages_per_range = 32);

Partial indexes

• Purpose: Index only subset of rows based on condition.

• Characteristics: Smaller size, faster for queries matching condition. Doesn't index all rows.

• Use: Frequently queried subsets, active/inactive records, sparse data.

sql

-- Index only active users
CREATE INDEX idx_users_active ON users(email) WHERE is_active = true;
SELECT * FROM users WHERE is_active = true AND email = 'test@example.com';

-- Index only recent orders
CREATE INDEX idx_orders_recent ON orders(user_id) 
WHERE created_at > '2024-01-01';

-- Index only non-null values
CREATE INDEX idx_users_phone ON users(phone) WHERE phone IS NOT NULL;

Expression indexes

• Purpose: Index on expression/function result, not column value.

• Characteristics: Useful for case-insensitive searches, computed columns.

• Use: Functional queries, computed columns, transformed data.

sql

-- Case-insensitive search
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = LOWER('Test@Example.com');

-- Date part extraction
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM created_at));
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024;

-- Computed columns
CREATE INDEX idx_products_name_upper ON products(UPPER(name));
SELECT * FROM products WHERE UPPER(name) = 'LAPTOP';

C. Locks & Concurrency

Row-level locks

• Purpose: Lock individual rows during modifications.

• Characteristics: Minimal blocking, high concurrency. Automatic for UPDATE/DELETE.

• Use: Allow concurrent access to different rows in same table.

sql

-- Automatic row locks on UPDATE
BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 1;
-- Row 1 is locked, other rows accessible
COMMIT;

-- Explicit row locking
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- Other transactions can't modify row 1 until we commit

-- FOR SHARE (weaker lock)
SELECT * FROM users WHERE id = 1 FOR SHARE;
-- Others can also SELECT FOR SHARE, but not FOR UPDATE

Table-level locks

• Purpose: Lock entire table for DDL operations or bulk changes.

• Characteristics: Blocking, low concurrency. Automatic for schema changes.

• Use: Schema modifications, mass updates, consistency guarantees.

sql

-- Automatic table locks for DDL
ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;
-- Table locked exclusively during operation

-- Explicit table locking
BEGIN;
LOCK TABLE users IN SHARE MODE;  -- Others can read but not write
-- Bulk operations...
COMMIT;

-- Lock modes:
-- ACCESS SHARE: Allow reads, block DROP (default for SELECT)
-- ROW SHARE: Allow reads, block exclusive lock
-- ROW EXCLUSIVE: Auto for UPDATE/DELETE/INSERT
-- SHARE UPDATE EXCLUSIVE: Allow reads, block schema changes
-- SHARE: Block writes
-- SHARE ROW EXCLUSIVE: Block writes and most locks
-- EXCLUSIVE: Block writes and reads
-- ACCESS EXCLUSIVE: Block everything (for DDL)

Deadlocks

• Purpose: When two transactions wait for each other's locks.

• Characteristics: PostgreSQL detects and aborts one transaction.

• Use: Avoid with consistent lock order, keep transactions short.

sql

-- Deadlock scenario:
-- Transaction 1:
BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 1;
-- Transaction 2:
BEGIN;
UPDATE users SET balance = balance + 100 WHERE id = 2;
-- Transaction 1:
UPDATE users SET balance = balance + 100 WHERE id = 2; -- Waits
-- Transaction 2:
UPDATE users SET balance = balance - 100 WHERE id = 1; -- DEADLOCK!

-- PostgreSQL will: ERROR: deadlock detected

-- Prevention:
-- 1. Always lock in same order (e.g., always lower ID first)
-- 2. Keep transactions short
-- 3. Use row-level locks when possible
-- 4. Use retry logic in application

SELECT FOR UPDATE

• Purpose: Lock rows for update, preventing concurrent modifications.

• Characteristics: Pessimistic locking. Blocks other writers.

• Use: Prevent lost updates, ensure consistency in concurrent modifications.

sql

-- Banking transfer example
BEGIN;
-- Lock sender's row
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Lock receiver's row  
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- With NOWAIT (fail immediately if locked)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- SKIP LOCKED (skip locked rows, continue with others)
SELECT * FROM jobs WHERE status = 'pending' 
FOR UPDATE SKIP LOCKED LIMIT 1;
-- Useful for job queues

D. Window Functions (Advanced SQL)

OVER()

• Purpose: Defines window/partition for window function calculations.

• Characteristics: Doesn't group rows like GROUP BY, returns all rows.

• Use: Calculate aggregates without collapsing rows.

sql

-- Basic window
SELECT 
    id, 
    amount,
    SUM(amount) OVER() as total_amount  -- Sum of all rows
FROM orders;

-- With PARTITION BY (like GROUP BY but keeps rows)
SELECT 
    user_id,
    order_date,
    amount,
    SUM(amount) OVER(PARTITION BY user_id) as user_total,
    AVG(amount) OVER(PARTITION BY user_id) as user_avg
FROM orders;

-- With ORDER BY in window (cumulative sums)
SELECT 
    order_date,
    amount,
    SUM(amount) OVER(ORDER BY order_date) as running_total
FROM orders;

ROW_NUMBER

• Purpose: Assign unique sequential number to each row in partition.

• Characteristics: Always unique (no ties). Resets for each partition.

• Use: Ranking, pagination, deduplication.

sql

-- Number orders per user
SELECT 
    user_id,
    order_date,
    amount,
    ROW_NUMBER() OVER(
        PARTITION BY user_id 
        ORDER BY order_date DESC
    ) as order_rank
FROM orders;

-- Get first order per user
WITH numbered_orders AS (
    SELECT *,
        ROW_NUMBER() OVER(
            PARTITION BY user_id 
            ORDER BY order_date
        ) as rn
    FROM orders
)
SELECT * FROM numbered_orders WHERE rn = 1;

-- Pagination alternative to LIMIT/OFFSET
SELECT * FROM (
    SELECT *,
        ROW_NUMBER() OVER(ORDER BY created_at) as rn
    FROM users
) numbered
WHERE rn BETWEEN 11 AND 20;

RANK, DENSE_RANK

• RANK: Ranking with gaps for ties (1,2,2,4).

• DENSE_RANK: Ranking without gaps for ties (1,2,2,3).

• Use: Competition rankings, percentile calculations.

sql

-- Sales ranking example
SELECT 
    salesperson_id,
    revenue,
    RANK() OVER(ORDER BY revenue DESC) as rank_with_gaps,
    DENSE_RANK() OVER(ORDER BY revenue DESC) as dense_rank_no_gaps,
    ROW_NUMBER() OVER(ORDER BY revenue DESC) as row_num_unique
FROM sales;

-- Top 3 performers by region
WITH ranked_sales AS (
    SELECT *,
        DENSE_RANK() OVER(
            PARTITION BY region 
            ORDER BY revenue DESC
        ) as rank_in_region
    FROM sales
)
SELECT * FROM ranked_sales WHERE rank_in_region <= 3;

LAG, LEAD

• LAG: Access previous row's value.

• LEAD: Access next row's value.

• Use: Calculate differences, detect changes, time-series analysis.

sql

-- Daily revenue difference
SELECT 
    order_date,
    daily_revenue,
    LAG(daily_revenue) OVER(ORDER BY order_date) as prev_day,
    daily_revenue - LAG(daily_revenue) OVER(ORDER BY order_date) as diff,
    -- With default value for first row
    LAG(daily_revenue, 1, 0) OVER(ORDER BY order_date) as prev_with_default
FROM daily_sales;

-- Next order date per user
SELECT 
    user_id,
    order_date,
    amount,
    LEAD(order_date) OVER(
        PARTITION BY user_id 
        ORDER BY order_date
    ) as next_order_date,
    -- Days between orders
    LEAD(order_date) OVER(
        PARTITION BY user_id 
        ORDER BY order_date
    ) - order_date as days_between_orders
FROM orders;

Running totals

• Purpose: Cumulative sum over ordered rows.

• Characteristics: Requires ORDER BY in OVER() clause.

• Use: Running balances, cumulative metrics, progress tracking.

sql

-- Running balance
SELECT 
    transaction_date,
    amount,
    SUM(amount) OVER(
        ORDER BY transaction_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_balance
FROM transactions;

-- Rolling average (last 7 days)
SELECT 
    date,
    daily_sales,
    AVG(daily_sales) OVER(
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as rolling_7day_avg
FROM sales_data;

-- Different window frames:
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (default with ORDER BY)
-- ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING (5-row window)
-- RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW (time-based)

-- Partitioned running total
SELECT 
    user_id,
    order_date,
    amount,
    SUM(amount) OVER(
        PARTITION BY user_id
        ORDER BY order_date
    ) as user_running_total
FROM orders;

Key Performance Tips:

1. Index smartly: Use appropriate index type for data and queries

2. Analyze plans: Always check EXPLAIN ANALYZE for slow queries

3. Batch operations: Minimize round-trips with bulk operations

4. Connection pooling: Use PgBouncer or application-level pooling

5. Vacuum regularly: Prevent bloat with autovacuum tuning

6. Monitor locks: Use pg_locks view to detect blocking

7. Use window functions: Often more efficient than self-joins or subqueries

8. Keep statistics updated: Run ANALYZE after large data changes