-- Index strategies for different query patterns
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    price DECIMAL(10,2),
    tags TEXT[],
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- B-tree index for exact matches and range queries
CREATE INDEX idx_products_category ON products (category);
CREATE INDEX idx_products_price_range ON products (price) WHERE price > 0;

-- Composite index for multiple columns
CREATE INDEX idx_products_category_price ON products (category, price DESC);

-- Partial index for specific conditions
CREATE INDEX idx_recent_products ON products (created_at)
WHERE created_at >= '2024-01-01';

-- GIN index for array searches
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- Use EXPLAIN ANALYZE to understand query performance
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT p.name, p.price, c.name as category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.price BETWEEN 100 AND 500
  AND p.created_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY p.price DESC
LIMIT 20;

-- Optimize with appropriate indexes and query structure
-- Instead of LIKE with leading wildcard (slow)
SELECT * FROM products WHERE name LIKE '%widget%';

-- Use full-text search (faster)
ALTER TABLE products ADD COLUMN search_vector tsvector;
UPDATE products SET search_vector = to_tsvector('english', name);
CREATE INDEX idx_products_search ON products USING GIN (search_vector);

SELECT * FROM products
WHERE search_vector @@ plainto_tsquery('english', 'widget');

-- Monitor active connections and performance
SELECT
    state,
    COUNT(*) as connection_count,
    AVG(EXTRACT(EPOCH FROM (now() - query_start))) as avg_duration_seconds
FROM pg_stat_activity
WHERE state IS NOT NULL
GROUP BY state;

-- Check for slow queries
SELECT
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
WHERE mean_time > 1000  -- queries taking more than 1 second on average
ORDER BY mean_time DESC
LIMIT 10;

# Memory settings (adjust based on available RAM)
shared_buffers = 256MB                    # 25% of RAM
effective_cache_size = 1GB                # 75% of RAM
work_mem = 4MB                           # Per operation memory
maintenance_work_mem = 64MB              # For maintenance operations

# Connection settings
max_connections = 200
shared_preload_libraries = 'pg_stat_statements'

# WAL and checkpointing
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 1GB
min_wal_size = 80MB

# Query planner
random_page_cost = 1.1                   # For SSD storage
effective_io_concurrency = 200           # For SSD storage

# Logging
log_min_duration_statement = 1000        # Log slow queries (1 second+)
log_statement = 'mod'                    # Log DDL and DML statements