-- Good: Index matches query pattern
CREATE INDEX idx_user_status_created ON users(status, created_at);

-- Query that uses this index efficiently
SELECT * FROM users 
WHERE status = 'active' 
ORDER BY created_at DESC;

-- Bad: Wrong column order
CREATE INDEX idx_created_status ON users(created_at, status);
-- This won't help the above query as efficiently

-- Instead of this query hitting the table
SELECT id, name, email FROM users WHERE status = 'active';

-- Create a covering index
CREATE INDEX idx_users_status_covering 
ON users(status) 
INCLUDE (id, name, email);

// Bad: N+1 queries
const users = await db.query('SELECT * FROM users');
for (const user of users) {
  user.posts = await db.query('SELECT * FROM posts WHERE user_id = ?', [user.id]);
}

// Good: Single query with JOIN
const users = await db.query(`
  SELECT 
    u.*,
    json_agg(p.*) as posts
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
  GROUP BY u.id
`);

// Or: Two queries (sometimes faster)
const users = await db.query('SELECT * FROM users');
const userIds = users.map(u => u.id);
const posts = await db.query(
  'SELECT * FROM posts WHERE user_id = ANY($1)',
  [userIds]
);
// Then map posts to users in application code

EXPLAIN ANALYZE
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.created_at > '2023-01-01'
GROUP BY u.id, u.name
HAVING COUNT(p.id) > 10;

-- Good: Cursor-based pagination (for large datasets)
SELECT * FROM posts 
WHERE id > $1 
ORDER BY id 
LIMIT 20;

-- Good: Offset pagination (for smaller datasets)
SELECT * FROM posts 
ORDER BY created_at DESC 
LIMIT 20 OFFSET $1;

-- Bad: No limit
SELECT * FROM posts ORDER BY created_at DESC;

// Bad: New connection per request
app.get('/users', async (req, res) => {
  const connection = await db.connect();
  const users = await connection.query('SELECT * FROM users');
  connection.release();
  res.json(users);
});

// Good: Connection pool
const pool = new Pool({
  max: 20, // Maximum connections
  min: 5,  // Minimum connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

app.get('/users', async (req, res) => {
  const users = await pool.query('SELECT * FROM users');
  res.json(users);
});

-- Good: Normalized but practical
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Bad: Over-normalized
CREATE TABLE users (id, email, name);
CREATE TABLE user_emails (user_id, email_type, email_value);
CREATE TABLE user_names (user_id, name_type, name_value);
-- Too many joins needed for simple queries

-- Add redundant data for read performance
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  user_name VARCHAR(255), -- Denormalized
  title VARCHAR(255),
  content TEXT,
  created_at TIMESTAMP
);

-- Update user_name when user.name changes
CREATE TRIGGER update_post_user_name
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_posts_user_name();

// Redis caching example
async function getUser(id) {
  const cacheKey = `user:${id}`;
  
  // Try cache first
  const cached = await redis.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }
  
  // Cache miss - query database
  const user = await db.query('SELECT * FROM users WHERE id = $1', [id]);
  
  // Cache for 5 minutes
  await redis.setex(cacheKey, 300, JSON.stringify(user));
  
  return user;
}

-- Materialized views for complex aggregations
CREATE MATERIALIZED VIEW user_stats AS
SELECT 
  user_id,
  COUNT(*) as post_count,
  SUM(likes) as total_likes,
  AVG(rating) as avg_rating
FROM posts
GROUP BY user_id;

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

-- PostgreSQL
SET log_min_duration_statement = 1000; -- Log queries > 1 second

-- MySQL
SET slow_query_log = 'ON';
SET long_query_time = 1;