PostgreSQL & MySQL Deep Dive Interview Guide: Beyond the Basics

-- PostgreSQL strengths: Window functions with complex logic
SELECT
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total,
    amount - LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) as change_from_previous
FROM orders;
 
-- JSONB querying with indexing
CREATE INDEX idx_orders_metadata ON orders USING GIN (metadata jsonb_path_ops);
SELECT * FROM orders WHERE metadata @> '{"priority": "high"}';

-- MySQL strengths: Simple read replica setup
-- Primary
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='primary.example.com',
    SOURCE_USER='repl',
    SOURCE_PASSWORD='password',
    SOURCE_AUTO_POSITION=1;
START REPLICA;

-- Composite index on (country, city, created_at)
CREATE INDEX idx_location_date ON users(country, city, created_at);
 
-- Uses full index (all 3 columns)
SELECT * FROM users
WHERE country = 'US' AND city = 'NYC' AND created_at > '2024-01-01';
 
-- Uses first 2 columns
SELECT * FROM users WHERE country = 'US' AND city = 'NYC';
 
-- Uses first column only
SELECT * FROM users WHERE country = 'US';
 
-- CANNOT use index efficiently (skips first column)
SELECT * FROM users WHERE city = 'NYC';  -- Full scan!
 
-- CANNOT use index efficiently (range on non-last column)
SELECT * FROM users WHERE country = 'US' AND created_at > '2024-01-01';
-- Only uses 'country', then scans

-- PostgreSQL index types
 
-- Hash index: Only equality, faster for = but not <, >
CREATE INDEX idx_email_hash ON users USING HASH (email);
 
-- GIN (Generalized Inverted Index): Arrays, JSONB, full-text
CREATE INDEX idx_tags ON articles USING GIN (tags);
CREATE INDEX idx_metadata ON products USING GIN (metadata jsonb_path_ops);
CREATE INDEX idx_search ON documents USING GIN (to_tsvector('english', content));
 
-- GiST (Generalized Search Tree): Geometric, range types, full-text
CREATE INDEX idx_location ON places USING GIST (coordinates);
CREATE INDEX idx_schedule ON events USING GIST (time_range);
 
-- BRIN (Block Range Index): Large sequential data, minimal storage
CREATE INDEX idx_created ON logs USING BRIN (created_at);

-- Regular index: Must fetch row from table
CREATE INDEX idx_email ON users(email);
SELECT name FROM users WHERE email = 'test@example.com';
-- Index lookup → Row fetch → Return name
 
-- Covering index: All data in index
CREATE INDEX idx_email_name ON users(email) INCLUDE (name);
SELECT name FROM users WHERE email = 'test@example.com';
-- Index lookup → Return name (no table access!)
 
-- PostgreSQL INCLUDE syntax (non-key columns)
CREATE INDEX idx_order_lookup ON orders(customer_id)
    INCLUDE (order_date, total);

-- Index only active users (much smaller than full index)
CREATE INDEX idx_active_users ON users(email)
    WHERE status = 'active';
 
-- Index only recent orders
CREATE INDEX idx_recent_orders ON orders(customer_id, created_at)
    WHERE created_at > '2024-01-01';
 
-- Index only non-null values
CREATE INDEX idx_phone ON users(phone)
    WHERE phone IS NOT NULL;
 
-- Query must match WHERE clause to use partial index
SELECT * FROM users WHERE email = 'test@example.com' AND status = 'active';

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.country = 'US'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 10;

-- Anti-pattern 1: Function on indexed column
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';  -- No index!
-- Fix: Expression index
CREATE INDEX idx_email_lower ON users(LOWER(email));
 
-- Anti-pattern 2: Implicit type conversion
SELECT * FROM orders WHERE order_id = '12345';  -- order_id is INT
-- Fix: Use correct type
SELECT * FROM orders WHERE order_id = 12345;
 
-- Anti-pattern 3: Leading wildcard
SELECT * FROM users WHERE name LIKE '%smith';  -- Full scan!
-- Fix: Full-text search or trigram index
CREATE INDEX idx_name_trgm ON users USING GIN (name gin_trgm_ops);
 
-- Anti-pattern 4: OR on different columns
SELECT * FROM users WHERE email = 'a@b.com' OR phone = '555-1234';
-- Fix: UNION or separate indexes with bitmap scan
SELECT * FROM users WHERE email = 'a@b.com'
UNION
SELECT * FROM users WHERE phone = '555-1234';
 
-- Anti-pattern 5: NOT IN with NULLs
SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM inactive_customers);
-- Fix: NOT EXISTS or LEFT JOIN IS NULL
SELECT o.* FROM orders o
LEFT JOIN inactive_customers ic ON o.customer_id = ic.id
WHERE ic.id IS NULL;

-- N+1 pattern in application code (pseudocode):
users = query("SELECT * FROM users WHERE country = 'US'")  -- 1 query
for user in users:
    orders = query("SELECT * FROM orders WHERE user_id = ?", user.id)  -- N queries
 
-- Total: 1 + N queries (if 1000 users, that's 1001 queries!)
 
-- Solution: JOIN or subquery
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.country = 'US';  -- 1 query
 
-- Or with aggregation
SELECT
    u.*,
    COALESCE(order_stats.count, 0) as order_count,
    COALESCE(order_stats.total, 0) as order_total
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) as count, SUM(amount) as total
    FROM orders
    GROUP BY user_id
) order_stats ON order_stats.user_id = u.id
WHERE u.country = 'US';

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If either fails, both are rolled back
COMMIT;

-- Constraints ensure consistency
ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);
-- Transfer that would make balance negative will fail

-- Transaction A reads balance, Transaction B updates it
-- Isolation level determines what A sees

COMMIT;  -- After this, data is safe even if server crashes

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- All reads in this transaction see consistent snapshot
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000
-- Even if another transaction commits a change...
SELECT balance FROM accounts WHERE id = 1;  -- Still returns 1000
COMMIT;
 
-- Serializable: Full isolation, may fail with serialization error
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT SUM(balance) FROM accounts;  -- Locks prevent concurrent writes
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;  -- May fail: "could not serialize access"

-- Check transaction ID
SELECT txid_current();
 
-- See dead rows (before vacuum)
SELECT * FROM pg_stat_user_tables WHERE relname = 'accounts';
-- n_dead_tup shows dead rows needing vacuum

-- Pessimistic locking: Lock row immediately
BEGIN;
SELECT * FROM inventory WHERE product_id = 1 FOR UPDATE;
-- Row is locked, other transactions wait
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1;
COMMIT;
 
-- FOR UPDATE variants:
SELECT * FROM inventory WHERE product_id = 1 FOR UPDATE NOWAIT;  -- Fail immediately if locked
SELECT * FROM inventory WHERE product_id = 1 FOR UPDATE SKIP LOCKED;  -- Skip locked rows
 
-- Optimistic locking: Check version at update time
-- Application maintains version column
UPDATE inventory
SET quantity = quantity - 1, version = version + 1
WHERE product_id = 1 AND version = 5;
-- If 0 rows affected, someone else modified it → retry
 
-- With timestamp
UPDATE inventory
SET quantity = quantity - 1, updated_at = NOW()
WHERE product_id = 1 AND updated_at = '2024-01-15 10:30:00';

-- JSONB: Binary storage, most common choice
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB  -- Flexible schema for product attributes
);
 
-- Insert JSONB
INSERT INTO products (name, attributes) VALUES
('Laptop', '{"brand": "Dell", "specs": {"ram": 16, "storage": "512GB"}}');
 
-- Query JSONB
SELECT * FROM products WHERE attributes->>'brand' = 'Dell';
SELECT * FROM products WHERE attributes @> '{"brand": "Dell"}';
SELECT * FROM products WHERE attributes->'specs'->>'ram' = '16';
 
-- JSONB operators
-- -> : Get JSON element (returns JSON)
-- ->> : Get JSON element as text
-- @> : Contains
-- ? : Key exists
-- ?| : Any key exists
-- ?& : All keys exist
 
-- Index JSONB for fast queries
CREATE INDEX idx_attributes ON products USING GIN (attributes);
CREATE INDEX idx_brand ON products USING GIN ((attributes->'brand'));
 
-- JSONB vs JSON
-- JSON: Preserves whitespace, key order, duplicates. Parsed on each access.
-- JSONB: Binary, deduplicated, indexable. Faster queries, slightly slower insert.

-- Path queries with jsonb_path_query
SELECT * FROM products
WHERE jsonb_path_exists(attributes, '$.specs.ram ? (@ > 8)');
 
-- Aggregate JSON
SELECT
    attributes->>'brand' as brand,
    COUNT(*),
    AVG((attributes->'specs'->>'ram')::int) as avg_ram
FROM products
GROUP BY attributes->>'brand';
 
-- Update nested JSONB
UPDATE products
SET attributes = jsonb_set(attributes, '{specs,ram}', '32')
WHERE id = 1;
 
-- Add key
UPDATE products
SET attributes = attributes || '{"warranty": "2 years"}'
WHERE id = 1;
 
-- Remove key
UPDATE products
SET attributes = attributes - 'warranty'
WHERE id = 1;

-- Array column
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]
);
 
INSERT INTO articles (title, tags) VALUES
('PostgreSQL Tips', ARRAY['database', 'postgresql', 'performance']);
 
-- Query arrays
SELECT * FROM articles WHERE 'postgresql' = ANY(tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'database'];
SELECT * FROM articles WHERE tags && ARRAY['mysql', 'postgresql'];  -- Overlap
 
-- Array functions
SELECT array_length(tags, 1) FROM articles;
SELECT unnest(tags) FROM articles;  -- Expand to rows
 
-- Index for array queries
CREATE INDEX idx_tags ON articles USING GIN (tags);

-- Create text search column
ALTER TABLE articles ADD COLUMN search_vector tsvector;
 
UPDATE articles SET search_vector =
    to_tsvector('english', title || ' ' || COALESCE(content, ''));
 
CREATE INDEX idx_search ON articles USING GIN (search_vector);
 
-- Search with ranking
SELECT title, ts_rank(search_vector, query) as rank
FROM articles, to_tsquery('english', 'postgresql & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC;
 
-- Phrase search
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'database performance');
 
-- Auto-update with trigger
CREATE TRIGGER update_search_vector
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
    tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);

-- Primary configuration (postgresql.conf)
wal_level = replica
max_wal_senders = 10
synchronous_commit = on  -- or 'remote_apply' for sync replica
 
-- Replica setup
-- pg_basebackup -h primary -D /var/lib/postgresql/data -U replicator -P
 
-- standby.signal file indicates replica mode
-- primary_conninfo in postgresql.auto.conf
 
-- Check replication status (on primary)
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;
 
-- Check replication lag
SELECT NOW() - pg_last_xact_replay_timestamp() AS replication_lag;

-- Source (primary) configuration
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
 
-- Replica configuration
[mysqld]
server-id = 2
relay_log = relay-bin
read_only = ON
gtid_mode = ON
enforce_gtid_consistency = ON
 
-- Set up replication on replica
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'primary.example.com',
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = 'password',
    SOURCE_AUTO_POSITION = 1;
 
START REPLICA;
 
-- Check status
SHOW REPLICA STATUS\G

# PgBouncer configuration
[databases]
mydb = host=localhost dbname=mydb
 
[pgbouncer]
pool_mode = transaction  # or session, statement
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
 
# Pool modes:
# session: Connection held until client disconnects
# transaction: Connection returned after each transaction (most common)
# statement: Connection returned after each statement (no transactions)

# Memory
shared_buffers = 4GB          # 25% of RAM for dedicated server
effective_cache_size = 12GB   # 75% of RAM (estimate of OS cache)
work_mem = 256MB              # Per-operation memory (sorts, hashes)
maintenance_work_mem = 1GB    # For VACUUM, CREATE INDEX
 
# WAL
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
 
# Planner
random_page_cost = 1.1        # Lower for SSD (default 4.0)
effective_io_concurrency = 200 # For SSD
 
# Connections
max_connections = 200         # Use connection pooling for more

# Buffer pool (most important)
innodb_buffer_pool_size = 12G     # 70-80% of RAM
innodb_buffer_pool_instances = 8  # 1 per GB up to 8
 
# Logging
innodb_log_file_size = 2G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1  # 1=durable, 2=faster
 
# I/O
innodb_io_capacity = 2000          # SSD
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

-- PostgreSQL declarative partitioning
CREATE TABLE orders (
    id BIGSERIAL,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);
 
-- Create partitions
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
 
-- Queries automatically route to correct partition
SELECT * FROM orders WHERE order_date = '2024-02-15';
-- Only scans orders_2024_q1
 
-- Partition pruning
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';
-- Shows only orders_2024_q1 scanned

-- VACUUM: Reclaims dead row space
VACUUM orders;           -- Standard vacuum
VACUUM FULL orders;      -- Rewrites table, locks it
VACUUM ANALYZE orders;   -- Vacuum + update statistics
 
-- Check vacuum status
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables;
 
-- ANALYZE: Updates query planner statistics
ANALYZE orders;
 
-- Auto-vacuum settings
-- autovacuum_vacuum_threshold = 50
-- autovacuum_vacuum_scale_factor = 0.2
-- Vacuum when: dead_tuples > threshold + scale_factor * table_size
 
-- For high-write tables, tune per-table
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02
);