Database Management Patterns A comprehensive skill for mastering database management across SQL (PostgreSQL) and NoSQL (MongoDB) systems. This skill covers schema design, indexing strategies, transaction management, replication, sharding, and performance optimization for production-grade applications. When to Use This Skill Use this skill when: - Designing database schemas for new applications or refactoring existing ones - Choosing between SQL and NoSQL databases for your use case - Optimizing query performance with proper indexing strategies - Implementing data consistency with transactions…

)\n);\n\n-- Addresses table (one customer, many addresses)\nCREATE TABLE addresses (\n id SERIAL PRIMARY KEY,\n customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,\n address_type VARCHAR(20) NOT NULL CHECK (address_type IN ('billing', 'shipping')),\n street_line1 VARCHAR(255) NOT NULL,\n street_line2 VARCHAR(255),\n city VARCHAR(100) NOT NULL,\n state VARCHAR(50) NOT NULL,\n postal_code VARCHAR(20) NOT NULL,\n country VARCHAR(50) NOT NULL DEFAULT 'US',\n is_default BOOLEAN DEFAULT false,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Categories table (hierarchical)\nCREATE TABLE categories (\n id SERIAL PRIMARY KEY,\n name VARCHAR(100) NOT NULL,\n slug VARCHAR(100) UNIQUE NOT NULL,\n parent_id INTEGER REFERENCES categories(id),\n description TEXT,\n display_order INTEGER DEFAULT 0,\n is_active BOOLEAN DEFAULT true,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Products table\nCREATE TABLE products (\n id SERIAL PRIMARY KEY,\n category_id INTEGER NOT NULL REFERENCES categories(id),\n sku VARCHAR(50) UNIQUE NOT NULL,\n name VARCHAR(255) NOT NULL,\n description TEXT,\n price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),\n cost NUMERIC(10, 2) CHECK (cost >= 0),\n weight_kg NUMERIC(8, 2),\n dimensions JSONB, -- { length, width, height, unit }\n is_active BOOLEAN DEFAULT true,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n CONSTRAINT price_cost CHECK (price >= cost)\n);\n\n-- Inventory table\nCREATE TABLE inventory (\n id SERIAL PRIMARY KEY,\n product_id INTEGER NOT NULL REFERENCES products(id),\n warehouse_id INTEGER NOT NULL,\n quantity INTEGER NOT NULL DEFAULT 0 CHECK (quantity >= 0),\n reserved_quantity INTEGER NOT NULL DEFAULT 0 CHECK (reserved_quantity >= 0),\n reorder_level INTEGER DEFAULT 10,\n last_restocked TIMESTAMP,\n UNIQUE (product_id, warehouse_id),\n CONSTRAINT available_stock CHECK (quantity >= reserved_quantity)\n);\n\n-- Orders table\nCREATE TABLE orders (\n id SERIAL PRIMARY KEY,\n customer_id INTEGER NOT NULL REFERENCES customers(id),\n order_number VARCHAR(50) UNIQUE NOT NULL,\n status VARCHAR(20) NOT NULL DEFAULT 'pending'\n CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),\n subtotal NUMERIC(10, 2) NOT NULL CHECK (subtotal >= 0),\n tax_amount NUMERIC(10, 2) NOT NULL DEFAULT 0 CHECK (tax_amount >= 0),\n shipping_amount NUMERIC(10, 2) NOT NULL DEFAULT 0 CHECK (shipping_amount >= 0),\n total_amount NUMERIC(10, 2) NOT NULL CHECK (total_amount >= 0),\n shipping_address_id INTEGER REFERENCES addresses(id),\n billing_address_id INTEGER REFERENCES addresses(id),\n notes TEXT,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n CONSTRAINT total_calculation CHECK (\n total_amount = subtotal + tax_amount + shipping_amount\n )\n);\n\n-- Order items table\nCREATE TABLE order_items (\n id SERIAL PRIMARY KEY,\n order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,\n product_id INTEGER NOT NULL REFERENCES products(id),\n quantity INTEGER NOT NULL CHECK (quantity > 0),\n unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0),\n subtotal NUMERIC(10, 2) NOT NULL CHECK (subtotal >= 0),\n discount_amount NUMERIC(10, 2) DEFAULT 0 CHECK (discount_amount >= 0),\n CONSTRAINT subtotal_calculation CHECK (\n subtotal = (unit_price * quantity) - discount_amount\n )\n);\n\n-- Indexes for performance\nCREATE INDEX idx_customers_email ON customers(email);\nCREATE INDEX idx_addresses_customer ON addresses(customer_id);\nCREATE INDEX idx_products_category ON products(category_id);\nCREATE INDEX idx_products_sku ON products(sku);\nCREATE INDEX idx_products_active ON products(is_active) WHERE is_active = true;\nCREATE INDEX idx_inventory_product ON inventory(product_id);\nCREATE INDEX idx_orders_customer ON orders(customer_id);\nCREATE INDEX idx_orders_status ON orders(status);\nCREATE INDEX idx_orders_created ON orders(created_at DESC);\nCREATE INDEX idx_order_items_order ON order_items(order_id);\nCREATE INDEX idx_order_items_product ON order_items(product_id);\n\n-- Trigger to update updated_at timestamp\nCREATE OR REPLACE FUNCTION update_timestamp()\nRETURNS TRIGGER AS $\nBEGIN\n NEW.updated_at = CURRENT_TIMESTAMP;\n RETURN NEW;\nEND;\n$ LANGUAGE plpgsql;\n\nCREATE TRIGGER customers_update_timestamp\n BEFORE UPDATE ON customers\n FOR EACH ROW EXECUTE FUNCTION update_timestamp();\n\nCREATE TRIGGER products_update_timestamp\n BEFORE UPDATE ON products\n FOR EACH ROW EXECUTE FUNCTION update_timestamp();\n\nCREATE TRIGGER orders_update_timestamp\n BEFORE UPDATE ON orders\n FOR EACH ROW EXECUTE FUNCTION update_timestamp();\n```\n\n### Example 2: Multi-Tenant SaaS Application\n\n**Scenario**: Design schema for a multi-tenant application with row-level security.\n\n```sql\n-- Tenants table\nCREATE TABLE tenants (\n id SERIAL PRIMARY KEY,\n name VARCHAR(255) NOT NULL,\n subdomain VARCHAR(100) UNIQUE NOT NULL,\n plan VARCHAR(50) NOT NULL CHECK (plan IN ('free', 'starter', 'professional', 'enterprise')),\n settings JSONB DEFAULT '{}',\n is_active BOOLEAN DEFAULT true,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n trial_ends_at TIMESTAMP\n);\n\n-- Users table (multi-tenant)\nCREATE TABLE users (\n id SERIAL PRIMARY KEY,\n tenant_id INTEGER NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,\n email VARCHAR(255) NOT NULL,\n password_hash VARCHAR(255) NOT NULL,\n role VARCHAR(50) NOT NULL CHECK (role IN ('admin', 'member', 'viewer')),\n is_active BOOLEAN DEFAULT true,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n UNIQUE (tenant_id, email)\n);\n\n-- Projects table (multi-tenant)\nCREATE TABLE projects (\n id SERIAL PRIMARY KEY,\n tenant_id INTEGER NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,\n name VARCHAR(255) NOT NULL,\n description TEXT,\n owner_id INTEGER NOT NULL REFERENCES users(id),\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Enable row-level security\nALTER TABLE projects ENABLE ROW LEVEL SECURITY;\n\n-- Policy: Users can only see projects from their tenant\nCREATE POLICY tenant_isolation_policy ON projects\n FOR ALL\n USING (tenant_id = current_setting('app.current_tenant_id')::INTEGER);\n\n-- Policy: Admins can see all projects in their tenant\nCREATE POLICY admin_policy ON projects\n FOR ALL\n USING (\n tenant_id = current_setting('app.current_tenant_id')::INTEGER\n AND EXISTS (\n SELECT 1 FROM users\n WHERE id = current_setting('app.current_user_id')::INTEGER\n AND role = 'admin'\n AND tenant_id = projects.tenant_id\n )\n );\n\n-- Application sets tenant context before queries\n-- SET app.current_tenant_id = 123;\n-- SET app.current_user_id = 456;\n```\n\n### Example 3: Audit Logging with Triggers\n\n**Scenario**: Track all changes to critical tables for compliance and debugging.\n\n```sql\n-- Generic audit log table\nCREATE TABLE audit_log (\n id BIGSERIAL PRIMARY KEY,\n schema_name VARCHAR(100) NOT NULL,\n table_name VARCHAR(100) NOT NULL,\n operation VARCHAR(10) NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),\n record_id INTEGER NOT NULL,\n old_data JSONB,\n new_data JSONB,\n changed_fields TEXT[],\n user_id INTEGER,\n username VARCHAR(255),\n ip_address INET,\n timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Create index for efficient queries\nCREATE INDEX idx_audit_table ON audit_log(table_name, record_id);\nCREATE INDEX idx_audit_timestamp ON audit_log(timestamp DESC);\nCREATE INDEX idx_audit_user ON audit_log(user_id);\n\n-- Generic audit trigger function\nCREATE OR REPLACE FUNCTION audit_trigger_function()\nRETURNS TRIGGER AS $\nDECLARE\n old_row JSONB;\n new_row JSONB;\n changed_fields TEXT[];\nBEGIN\n -- Convert rows to JSON\n IF TG_OP = 'DELETE' THEN\n old_row = row_to_json(OLD)::JSONB;\n new_row = NULL;\n ELSIF TG_OP = 'INSERT' THEN\n old_row = NULL;\n new_row = row_to_json(NEW)::JSONB;\n ELSE -- UPDATE\n old_row = row_to_json(OLD)::JSONB;\n new_row = row_to_json(NEW)::JSONB;\n\n -- Find changed fields\n SELECT ARRAY_AGG(key)\n INTO changed_fields\n FROM jsonb_each(old_row) o\n WHERE o.value IS DISTINCT FROM new_row->o.key;\n END IF;\n\n -- Insert audit record\n INSERT INTO audit_log (\n schema_name,\n table_name,\n operation,\n record_id,\n old_data,\n new_data,\n changed_fields,\n username\n ) VALUES (\n TG_TABLE_SCHEMA,\n TG_TABLE_NAME,\n TG_OP,\n COALESCE(NEW.id, OLD.id),\n old_row,\n new_row,\n changed_fields,\n current_user\n );\n\n IF TG_OP = 'DELETE' THEN\n RETURN OLD;\n ELSE\n RETURN NEW;\n END IF;\nEND;\n$ LANGUAGE plpgsql;\n\n-- Attach audit trigger to tables\nCREATE TRIGGER orders_audit_trigger\n AFTER INSERT OR UPDATE OR DELETE ON orders\n FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();\n\nCREATE TRIGGER products_audit_trigger\n AFTER INSERT OR UPDATE OR DELETE ON products\n FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();\n\n-- Query audit log\nSELECT\n table_name,\n operation,\n record_id,\n changed_fields,\n username,\n timestamp\nFROM audit_log\nWHERE table_name = 'orders'\n AND record_id = 12345\nORDER BY timestamp DESC;\n```\n\n### Example 4: Hierarchical Data (Categories/Organization Chart)\n\n**Scenario**: Store and query hierarchical organizational structure efficiently.\n\n```sql\n-- Organization table using materialized path pattern\nCREATE TABLE organizations (\n id SERIAL PRIMARY KEY,\n name VARCHAR(255) NOT NULL,\n parent_id INTEGER REFERENCES organizations(id),\n path VARCHAR(500) NOT NULL, -- e.g., '1.5.12' for nested hierarchy\n level INTEGER NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Trigger to maintain path and level\nCREATE OR REPLACE FUNCTION update_org_path()\nRETURNS TRIGGER AS $\nDECLARE\n parent_path VARCHAR(500);\n parent_level INTEGER;\nBEGIN\n IF NEW.parent_id IS NULL THEN\n -- Root node\n NEW.path = NEW.id::VARCHAR;\n NEW.level = 0;\n ELSE\n -- Get parent's path and level\n SELECT path, level INTO parent_path, parent_level\n FROM organizations\n WHERE id = NEW.parent_id;\n\n NEW.path = parent_path || '.' || NEW.id;\n NEW.level = parent_level + 1;\n END IF;\n\n RETURN NEW;\nEND;\n$ LANGUAGE plpgsql;\n\nCREATE TRIGGER organizations_path_trigger\n BEFORE INSERT OR UPDATE ON organizations\n FOR EACH ROW EXECUTE FUNCTION update_org_path();\n\n-- Efficient queries\n\n-- Get all descendants of a node\nSELECT *\nFROM organizations\nWHERE path LIKE (\n SELECT path || '.%'\n FROM organizations\n WHERE id = 5\n);\n\n-- Get all ancestors of a node\nSELECT *\nFROM organizations\nWHERE id IN (\n SELECT unnest(string_to_array(\n (SELECT path FROM organizations WHERE id = 12),\n '.'\n )::INTEGER[])\n);\n\n-- Get immediate children\nSELECT *\nFROM organizations\nWHERE parent_id = 5;\n\n-- Get leaf nodes (no children)\nSELECT o.*\nFROM organizations o\nLEFT JOIN organizations c ON c.parent_id = o.id\nWHERE c.id IS NULL;\n\n-- Get depth of tree\nSELECT MAX(level) as max_depth\nFROM organizations;\n```\n\n### Example 5: Time-Series Data with Partitioning\n\n**Scenario**: Store sensor data with automatic partitioning by month.\n\n```sql\n-- Parent table (partitioned by range)\nCREATE TABLE sensor_readings (\n id BIGSERIAL,\n sensor_id INTEGER NOT NULL,\n reading_time TIMESTAMP NOT NULL,\n temperature NUMERIC(5, 2),\n humidity NUMERIC(5, 2),\n pressure NUMERIC(7, 2),\n metadata JSONB,\n PRIMARY KEY (id, reading_time)\n) PARTITION BY RANGE (reading_time);\n\n-- Create partitions for each month\nCREATE TABLE sensor_readings_2025_01 PARTITION OF sensor_readings\n FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');\n\nCREATE TABLE sensor_readings_2025_02 PARTITION OF sensor_readings\n FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');\n\nCREATE TABLE sensor_readings_2025_03 PARTITION OF sensor_readings\n FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');\n\n-- Indexes on partitions (created automatically on parent)\nCREATE INDEX idx_sensor_readings_sensor_time\n ON sensor_readings(sensor_id, reading_time DESC);\n\nCREATE INDEX idx_sensor_readings_metadata\n ON sensor_readings USING GIN(metadata);\n\n-- Function to automatically create next month's partition\nCREATE OR REPLACE FUNCTION create_next_partition()\nRETURNS void AS $\nDECLARE\n next_month DATE;\n following_month DATE;\n partition_name VARCHAR(100);\nBEGIN\n next_month := date_trunc('month', CURRENT_DATE + INTERVAL '1 month');\n following_month := next_month + INTERVAL '1 month';\n partition_name := 'sensor_readings_' || to_char(next_month, 'YYYY_MM');\n\n EXECUTE format(\n 'CREATE TABLE IF NOT EXISTS %I PARTITION OF sensor_readings\n FOR VALUES FROM (%L) TO (%L)',\n partition_name,\n next_month,\n following_month\n );\nEND;\n$ LANGUAGE plpgsql;\n\n-- Schedule this function to run monthly (via cron or pg_cron extension)\n\n-- Query benefits from partition pruning\nSELECT\n sensor_id,\n AVG(temperature) as avg_temp,\n MAX(temperature) as max_temp,\n MIN(temperature) as min_temp\nFROM sensor_readings\nWHERE reading_time >= '2025-01-15'\n AND reading_time \u003c '2025-01-20'\n AND sensor_id = 42\nGROUP BY sensor_id;\n-- Only scans sensor_readings_2025_01 partition!\n```\n\n---\n\n## PostgreSQL Advanced Queries\n\n### Example 6: Window Functions for Analytics\n\n**Scenario**: Calculate running totals, rankings, and moving averages.\n\n```sql\n-- Sample data: daily sales\nCREATE TABLE daily_sales (\n sale_date DATE NOT NULL,\n product_id INTEGER NOT NULL,\n category VARCHAR(50),\n revenue NUMERIC(10, 2),\n units_sold INTEGER,\n PRIMARY KEY (sale_date, product_id)\n);\n\n-- Running total revenue by date\nSELECT\n sale_date,\n revenue,\n SUM(revenue) OVER (ORDER BY sale_date) as running_total,\n AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7day\nFROM daily_sales\nWHERE product_id = 100\nORDER BY sale_date;\n\n-- Rank products by revenue within each category\nSELECT\n category,\n product_id,\n revenue,\n RANK() OVER (PARTITION BY category ORDER BY revenue DESC) as rank_in_category,\n PERCENT_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) as percentile\nFROM daily_sales\nWHERE sale_date = '2025-01-15';\n\n-- Year-over-year comparison\nSELECT\n DATE_TRUNC('month', sale_date) as month,\n SUM(revenue) as current_revenue,\n LAG(SUM(revenue), 12) OVER (ORDER BY DATE_TRUNC('month', sale_date)) as previous_year_revenue,\n (SUM(revenue) - LAG(SUM(revenue), 12) OVER (ORDER BY DATE_TRUNC('month', sale_date)))\n / LAG(SUM(revenue), 12) OVER (ORDER BY DATE_TRUNC('month', sale_date)) * 100 as yoy_growth_pct\nFROM daily_sales\nGROUP BY DATE_TRUNC('month', sale_date)\nORDER BY month;\n\n-- Cumulative distribution\nSELECT\n product_id,\n revenue,\n CUME_DIST() OVER (ORDER BY revenue) as cumulative_distribution,\n NTILE(4) OVER (ORDER BY revenue) as quartile\nFROM daily_sales\nWHERE sale_date = '2025-01-15';\n```\n\n### Example 7: Common Table Expressions (CTEs) and Recursion\n\n**Scenario**: Find all related records recursively and perform complex multi-step analysis.\n\n```sql\n-- Recursive CTE: Find all employees in reporting hierarchy\nWITH RECURSIVE employee_hierarchy AS (\n -- Base case: start with CEO\n SELECT\n id,\n name,\n manager_id,\n title,\n 1 as level,\n name::TEXT as path\n FROM employees\n WHERE manager_id IS NULL\n\n UNION ALL\n\n -- Recursive case: find direct reports\n SELECT\n e.id,\n e.name,\n e.manager_id,\n e.title,\n eh.level + 1,\n eh.path || ' > ' || e.name\n FROM employees e\n INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id\n)\nSELECT\n level,\n name,\n title,\n path\nFROM employee_hierarchy\nORDER BY level, name;\n\n-- Multi-step analysis with CTEs\nWITH\n-- Step 1: Aggregate sales by customer\ncustomer_totals AS (\n SELECT\n customer_id,\n COUNT(*) as order_count,\n SUM(total_amount) as total_spent,\n MAX(created_at) as last_order_date\n FROM orders\n WHERE created_at >= CURRENT_DATE - INTERVAL '1 year'\n GROUP BY customer_id\n),\n-- Step 2: Classify customers\ncustomer_segments AS (\n SELECT\n customer_id,\n order_count,\n total_spent,\n last_order_date,\n CASE\n WHEN total_spent >= 10000 THEN 'VIP'\n WHEN total_spent >= 5000 THEN 'Premium'\n WHEN total_spent >= 1000 THEN 'Regular'\n ELSE 'Occasional'\n END as segment,\n CASE\n WHEN last_order_date >= CURRENT_DATE - INTERVAL '30 days' THEN 'Active'\n WHEN last_order_date >= CURRENT_DATE - INTERVAL '90 days' THEN 'At Risk'\n ELSE 'Churned'\n END as status\n FROM customer_totals\n),\n-- Step 3: Calculate segment statistics\nsegment_stats AS (\n SELECT\n segment,\n status,\n COUNT(*) as customer_count,\n AVG(total_spent) as avg_spent,\n SUM(total_spent) as segment_revenue\n FROM customer_segments\n GROUP BY segment, status\n)\n-- Final output\nSELECT\n segment,\n status,\n customer_count,\n ROUND(avg_spent, 2) as avg_spent,\n segment_revenue,\n ROUND(segment_revenue * 100.0 / SUM(segment_revenue) OVER (), 2) as pct_of_total_revenue\nFROM segment_stats\nORDER BY segment_revenue DESC;\n```\n\n### Example 8: Full-Text Search with Ranking\n\n**Scenario**: Implement full-text search with relevance ranking.\n\n```sql\n-- Add tsvector column for full-text search\nALTER TABLE articles\n ADD COLUMN search_vector tsvector;\n\n-- Populate search vector from title and content\nUPDATE articles\nSET search_vector =\n setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||\n setweight(to_tsvector('english', COALESCE(content, '')), 'B') ||\n setweight(to_tsvector('english', COALESCE(tags::text, '')), 'C');\n\n-- Create GIN index for fast full-text search\nCREATE INDEX idx_articles_search ON articles USING GIN(search_vector);\n\n-- Trigger to keep search_vector updated\nCREATE OR REPLACE FUNCTION articles_search_trigger()\nRETURNS TRIGGER AS $\nBEGIN\n NEW.search_vector :=\n setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||\n setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'B') ||\n setweight(to_tsvector('english', COALESCE(NEW.tags::text, '')), 'C');\n RETURN NEW;\nEND;\n$ LANGUAGE plpgsql;\n\nCREATE TRIGGER articles_search_update\n BEFORE INSERT OR UPDATE ON articles\n FOR EACH ROW EXECUTE FUNCTION articles_search_trigger();\n\n-- Search with ranking\nSELECT\n id,\n title,\n ts_rank(search_vector, query) as rank,\n ts_headline('english', content, query, 'MaxWords=50, MinWords=25') as snippet\nFROM\n articles,\n to_tsquery('english', 'database & (design | pattern)') query\nWHERE\n search_vector @@ query\nORDER BY rank DESC\nLIMIT 20;\n\n-- Advanced search with phrase matching\nSELECT\n title,\n ts_rank_cd(search_vector, query) as rank\nFROM\n articles,\n phraseto_tsquery('english', 'database design patterns') query\nWHERE\n search_vector @@ query\nORDER BY rank DESC;\n```\n\n---\n\n## PostgreSQL Performance Optimization\n\n### Example 9: Index Optimization Strategies\n\n```sql\n-- Analyze table for query planner\nANALYZE products;\n\n-- Check index usage statistics\nSELECT\n schemaname,\n tablename,\n indexname,\n idx_scan,\n idx_tup_read,\n idx_tup_fetch,\n pg_size_pretty(pg_relation_size(indexrelid)) as index_size\nFROM pg_stat_user_indexes\nORDER BY idx_scan DESC;\n\n-- Find unused indexes (candidates for removal)\nSELECT\n schemaname || '.' || tablename AS table,\n indexname,\n pg_size_pretty(pg_relation_size(indexrelid)) AS size,\n idx_scan,\n idx_tup_read\nFROM pg_stat_user_indexes\nWHERE idx_scan = 0\n AND indexrelname NOT LIKE 'pg_toast%'\n AND schemaname = 'public'\nORDER BY pg_relation_size(indexrelid) DESC;\n\n-- Create partial index for active records only\nCREATE INDEX idx_active_products\nON products(category_id, name)\nWHERE is_active = true AND stock_quantity > 0;\n\n-- Expression index for case-insensitive search\nCREATE INDEX idx_products_name_lower\nON products(LOWER(name));\n\n-- Query that uses expression index\nSELECT * FROM products\nWHERE LOWER(name) = LOWER('Widget Pro');\n\n-- Covering index (index-only scan)\nCREATE INDEX idx_orders_covering\nON orders(customer_id, status)\nINCLUDE (total_amount, created_at);\n\n-- This query can be served entirely from index\nSELECT customer_id, status, total_amount, created_at\nFROM orders\nWHERE customer_id = 123 AND status = 'completed';\n```\n\n### Example 10: Query Optimization Patterns\n\n```sql\n-- BEFORE: Inefficient subquery in SELECT\nSELECT\n p.name,\n (SELECT COUNT(*) FROM order_items oi WHERE oi.product_id = p.id) as times_ordered\nFROM products p;\n\n-- AFTER: Use LEFT JOIN with GROUP BY\nSELECT\n p.name,\n COALESCE(COUNT(oi.id), 0) as times_ordered\nFROM products p\nLEFT JOIN order_items oi ON p.id = oi.product_id\nGROUP BY p.id, p.name;\n\n-- BEFORE: IN clause with large subquery\nSELECT * FROM orders\nWHERE customer_id IN (\n SELECT id FROM customers WHERE country = 'US'\n);\n\n-- AFTER: Use EXISTS or JOIN\nSELECT o.* FROM orders o\nWHERE EXISTS (\n SELECT 1 FROM customers c\n WHERE c.id = o.customer_id AND c.country = 'US'\n);\n\n-- Or using JOIN (often faster)\nSELECT o.* FROM orders o\nINNER JOIN customers c ON o.customer_id = c.id\nWHERE c.country = 'US';\n\n-- BEFORE: Function in WHERE clause prevents index usage\nSELECT * FROM orders\nWHERE EXTRACT(YEAR FROM created_at) = 2025;\n\n-- AFTER: Use range query\nSELECT * FROM orders\nWHERE created_at >= '2025-01-01'\n AND created_at \u003c '2026-01-01';\n\n-- BEFORE: OR conditions that prevent index usage\nSELECT * FROM products\nWHERE category_id = 10 OR category_id = 20;\n\n-- AFTER: Use IN clause or UNION\nSELECT * FROM products\nWHERE category_id IN (10, 20);\n\n-- Materialized CTE for reuse (PostgreSQL 12+)\nWITH product_stats AS MATERIALIZED (\n SELECT\n product_id,\n COUNT(*) as order_count,\n SUM(quantity) as total_quantity\n FROM order_items\n GROUP BY product_id\n)\nSELECT\n p.name,\n ps.order_count,\n ps.total_quantity,\n ps.total_quantity / ps.order_count as avg_quantity_per_order\nFROM products p\nINNER JOIN product_stats ps ON p.id = ps.product_id\nWHERE ps.order_count > 100\nORDER BY ps.total_quantity DESC;\n```\n\n---\n\n## MongoDB Schema Design Examples\n\n### Example 11: E-Commerce Product Catalog (Polymorphic Pattern)\n\n**Scenario**: Store different product types with varying attributes in single collection.\n\n```javascript\n// Book product\n{\n _id: ObjectId(\"507f1f77bcf86cd799439011\"),\n type: \"book\",\n name: \"Database Design Patterns\",\n slug: \"database-design-patterns\",\n price: 49.99,\n currency: \"USD\",\n in_stock: true,\n\n // Book-specific fields\n isbn: \"978-0-123456-78-9\",\n author: \"John Smith\",\n publisher: \"Tech Books Inc\",\n pages: 456,\n publication_date: ISODate(\"2024-06-15\"),\n language: \"English\",\n format: \"Hardcover\",\n\n // Common fields\n description: \"Comprehensive guide to database design...\",\n images: [\n { url: \"https://cdn.example.com/book-cover.jpg\", alt: \"Book cover\", order: 0 },\n { url: \"https://cdn.example.com/book-back.jpg\", alt: \"Back cover\", order: 1 }\n ],\n categories: [\"Technology\", \"Databases\", \"Software Development\"],\n tags: [\"database\", \"design\", \"sql\", \"nosql\"],\n reviews: {\n average: 4.7,\n count: 243\n },\n created_at: ISODate(\"2024-05-01\"),\n updated_at: ISODate(\"2025-01-10\")\n}\n\n// Electronics product\n{\n _id: ObjectId(\"507f1f77bcf86cd799439012\"),\n type: \"electronics\",\n name: \"Wireless Noise-Cancelling Headphones\",\n slug: \"wireless-noise-cancelling-headphones\",\n price: 299.99,\n currency: \"USD\",\n in_stock: true,\n\n // Electronics-specific fields\n brand: \"AudioTech\",\n model: \"AT-5000\",\n sku: \"AUDIO-HP-5000\",\n warranty_months: 24,\n specifications: {\n battery_life: \"30 hours\",\n bluetooth_version: \"5.2\",\n driver_size: \"40mm\",\n frequency_response: \"20Hz - 20kHz\",\n weight: \"250g\",\n colors: [\"Black\", \"Silver\", \"Blue\"]\n },\n features: [\n \"Active Noise Cancellation\",\n \"Bluetooth 5.2\",\n \"30-hour battery life\",\n \"Quick charge (5 min = 2 hours)\",\n \"Multipoint connection\"\n ],\n\n // Common fields\n description: \"Premium wireless headphones with...\",\n images: [\n { url: \"https://cdn.example.com/hp-main.jpg\", alt: \"Main view\", order: 0 },\n { url: \"https://cdn.example.com/hp-side.jpg\", alt: \"Side view\", order: 1 },\n { url: \"https://cdn.example.com/hp-case.jpg\", alt: \"With case\", order: 2 }\n ],\n categories: [\"Electronics\", \"Audio\", \"Headphones\"],\n tags: [\"wireless\", \"bluetooth\", \"noise-cancelling\", \"headphones\"],\n reviews: {\n average: 4.5,\n count: 892\n },\n created_at: ISODate(\"2024-03-20\"),\n updated_at: ISODate(\"2025-01-12\")\n}\n\n// Indexes for polymorphic collection\ndb.products.createIndex({ type: 1, slug: 1 }, { unique: true })\ndb.products.createIndex({ categories: 1, price: 1 })\ndb.products.createIndex({ tags: 1 })\ndb.products.createIndex({ \"reviews.average\": -1 })\n\n// Type-specific indexes\ndb.products.createIndex({ isbn: 1 }, {\n unique: true,\n partialFilterExpression: { type: \"book\" }\n})\ndb.products.createIndex({ sku: 1 }, {\n unique: true,\n partialFilterExpression: { type: \"electronics\" }\n})\n\n// Query books by author\ndb.products.find({\n type: \"book\",\n author: \"John Smith\"\n})\n\n// Query electronics by brand and price range\ndb.products.find({\n type: \"electronics\",\n brand: \"AudioTech\",\n price: { $gte: 200, $lte: 400 }\n}).sort({ \"reviews.average\": -1 })\n```\n\n### Example 12: Social Media Application (Embedded vs Referenced)\n\n**Scenario**: Design schema for posts, comments, likes with appropriate embedding strategy.\n\n```javascript\n// Users collection (separate - frequently updated, referenced by many)\n{\n _id: ObjectId(\"user1\"),\n username: \"john_doe\",\n email: \"[email protected]\",\n profile: {\n full_name: \"John Doe\",\n avatar_url: \"https://cdn.example.com/avatars/john.jpg\",\n bio: \"Software engineer and database enthusiast\",\n location: \"San Francisco, CA\",\n website: \"https://johndoe.dev\"\n },\n stats: {\n followers: 1523,\n following: 342,\n posts: 89\n },\n created_at: ISODate(\"2023-01-15\"),\n last_seen: ISODate(\"2025-01-18T10:30:00Z\")\n}\n\n// Posts collection (embed comments, reference user)\n{\n _id: ObjectId(\"post1\"),\n\n // Author reference with selective denormalization\n author: {\n id: ObjectId(\"user1\"),\n username: \"john_doe\",\n avatar_url: \"https://cdn.example.com/avatars/john.jpg\"\n // Denormalize frequently accessed, rarely changing fields\n },\n\n content: {\n text: \"Just published a comprehensive guide to database design patterns!\",\n media: [\n {\n type: \"image\",\n url: \"https://cdn.example.com/posts/db-guide.jpg\",\n width: 1200,\n height: 630,\n alt: \"Database design book cover\"\n }\n ],\n links: [\n {\n url: \"https://example.com/db-guide\",\n title: \"Database Design Patterns\",\n description: \"Learn advanced patterns...\",\n image: \"https://example.com/preview.jpg\"\n }\n ]\n },\n\n // Embed comments (one-to-many, bounded, accessed together)\n comments: [\n {\n _id: ObjectId(\"comment1\"),\n author: {\n id: ObjectId(\"user2\"),\n username: \"jane_smith\",\n avatar_url: \"https://cdn.example.com/avatars/jane.jpg\"\n },\n text: \"This looks great! Can't wait to read it.\",\n created_at: ISODate(\"2025-01-15T11:00:00Z\"),\n likes: 12,\n\n // Nested replies (limited depth)\n replies: [\n {\n _id: ObjectId(\"reply1\"),\n author: {\n id: ObjectId(\"user1\"),\n username: \"john_doe\",\n avatar_url: \"https://cdn.example.com/avatars/john.jpg\"\n },\n text: \"Thanks! Hope you find it useful.\",\n created_at: ISODate(\"2025-01-15T11:30:00Z\"),\n likes: 3\n }\n ]\n },\n {\n _id: ObjectId(\"comment2\"),\n author: {\n id: ObjectId(\"user3\"),\n username: \"bob_wilson\",\n avatar_url: \"https://cdn.example.com/avatars/bob.jpg\"\n },\n text: \"Excellent timing! We're redesigning our database.\",\n created_at: ISODate(\"2025-01-15T14:20:00Z\"),\n likes: 8,\n replies: []\n }\n ],\n\n // Stats embedded (frequently updated together)\n stats: {\n views: 3542,\n likes: 234,\n shares: 45,\n comments: 2\n },\n\n // Tags for categorization\n tags: [\"database\", \"software\", \"tutorial\"],\n\n // Metadata\n created_at: ISODate(\"2025-01-15T10:00:00Z\"),\n updated_at: ISODate(\"2025-01-15T14:20:00Z\"),\n visibility: \"public\", // public, followers, private\n is_pinned: false\n}\n\n// Likes collection (separate - unbounded, may be millions)\n{\n _id: ObjectId(\"like1\"),\n post_id: ObjectId(\"post1\"),\n user_id: ObjectId(\"user2\"),\n created_at: ISODate(\"2025-01-15T11:00:00Z\")\n}\n\n// Indexes\ndb.posts.createIndex({ \"author.id\": 1, created_at: -1 })\ndb.posts.createIndex({ created_at: -1 })\ndb.posts.createIndex({ tags: 1 })\ndb.posts.createIndex({ \"stats.likes\": -1 })\n\ndb.likes.createIndex({ post_id: 1, user_id: 1 }, { unique: true })\ndb.likes.createIndex({ user_id: 1, created_at: -1 })\n\n// Query: Get user's feed (posts from people they follow)\ndb.posts.find({\n \"author.id\": { $in: followingUserIds },\n visibility: { $in: [\"public\", \"followers\"] }\n}).sort({ created_at: -1 }).limit(20)\n\n// Query: Check if user liked a post\ndb.likes.findOne({\n post_id: ObjectId(\"post1\"),\n user_id: ObjectId(\"user2\")\n})\n\n// Update: Increment like count (atomic operation)\ndb.posts.updateOne(\n { _id: ObjectId(\"post1\") },\n {\n $inc: { \"stats.likes\": 1 },\n $set: { updated_at: new Date() }\n }\n)\n```\n\n### Example 13: Time-Series Data (Bucketing Pattern)\n\n**Scenario**: Store IoT sensor data efficiently using the bucket pattern.\n\n```javascript\n// BAD: One document per reading (millions of tiny documents)\n{\n _id: ObjectId(\"...\"),\n sensor_id: \"temp_sensor_001\",\n timestamp: ISODate(\"2025-01-15T10:00:00Z\"),\n temperature: 72.5,\n humidity: 45.2\n}\n\n// GOOD: Bucket pattern - group readings by hour\n{\n _id: ObjectId(\"...\"),\n sensor_id: \"temp_sensor_001\",\n date: ISODate(\"2025-01-15\"),\n hour: 10,\n\n // Array of measurements (up to 60 for 1-minute intervals)\n measurements: [\n {\n minute: 0,\n timestamp: ISODate(\"2025-01-15T10:00:00Z\"),\n temperature: 72.5,\n humidity: 45.2,\n pressure: 1013.25\n },\n {\n minute: 1,\n timestamp: ISODate(\"2025-01-15T10:01:00Z\"),\n temperature: 72.6,\n humidity: 45.1,\n pressure: 1013.30\n },\n // ... up to 60 measurements\n ],\n\n // Pre-computed summary statistics\n summary: {\n count: 60,\n temperature: {\n min: 71.8,\n max: 73.2,\n avg: 72.5,\n sum: 4350.0\n },\n humidity: {\n min: 44.5,\n max: 46.1,\n avg: 45.2,\n sum: 2712.0\n },\n pressure: {\n min: 1012.80,\n max: 1013.90,\n avg: 1013.25\n }\n },\n\n metadata: {\n sensor_location: \"Building A - Room 101\",\n sensor_type: \"DHT22\",\n firmware_version: \"2.1.3\"\n }\n}\n\n// Indexes for efficient queries\ndb.sensor_data.createIndex({ sensor_id: 1, date: 1, hour: 1 }, { unique: true })\ndb.sensor_data.createIndex({ date: 1, hour: 1 })\ndb.sensor_data.createIndex({ \"metadata.sensor_location\": 1, date: 1 })\n\n// Query: Get all readings for a sensor on a specific day\ndb.sensor_data.find({\n sensor_id: \"temp_sensor_001\",\n date: ISODate(\"2025-01-15\")\n}).sort({ hour: 1 })\n\n// Query: Get hourly averages for a date range\ndb.sensor_data.aggregate([\n {\n $match: {\n sensor_id: \"temp_sensor_001\",\n date: {\n $gte: ISODate(\"2025-01-01\"),\n $lte: ISODate(\"2025-01-31\")\n }\n }\n },\n {\n $project: {\n date: 1,\n hour: 1,\n avg_temperature: \"$summary.temperature.avg\",\n avg_humidity: \"$summary.humidity.avg\"\n }\n },\n {\n $sort: { date: 1, hour: 1 }\n }\n])\n\n// Insert new measurement (update bucket)\ndb.sensor_data.updateOne(\n {\n sensor_id: \"temp_sensor_001\",\n date: ISODate(\"2025-01-15\"),\n hour: 10\n },\n {\n $push: {\n measurements: {\n minute: 30,\n timestamp: ISODate(\"2025-01-15T10:30:00Z\"),\n temperature: 72.8,\n humidity: 45.5,\n pressure: 1013.40\n }\n },\n $inc: {\n \"summary.count\": 1,\n \"summary.temperature.sum\": 72.8\n },\n $min: {\n \"summary.temperature.min\": 72.8\n },\n $max: {\n \"summary.temperature.max\": 72.8\n }\n },\n { upsert: true }\n)\n\n// Benefits:\n// - 60x fewer documents\n// - Better index efficiency\n// - Pre-computed statistics\n// - Easier time-range queries\n// - Reduced disk I/O\n```\n\n---\n\n## MongoDB Aggregation Examples\n\n### Example 14: Complex Multi-Stage Aggregation Pipeline\n\n**Scenario**: Analyze e-commerce sales data with multiple transformations.\n\n```javascript\ndb.orders.aggregate([\n // Stage 1: Filter to completed orders in date range\n {\n $match: {\n status: \"completed\",\n created_at: {\n $gte: ISODate(\"2025-01-01\"),\n $lt: ISODate(\"2025-02-01\")\n }\n }\n },\n\n // Stage 2: Unwind order items array\n {\n $unwind: \"$items\"\n },\n\n // Stage 3: Lookup product details\n {\n $lookup: {\n from: \"products\",\n localField: \"items.product_id\",\n foreignField: \"_id\",\n as: \"product_info\"\n }\n },\n\n // Stage 4: Unwind product info (should be single doc)\n {\n $unwind: \"$product_info\"\n },\n\n // Stage 5: Group by product and calculate metrics\n {\n $group: {\n _id: {\n product_id: \"$items.product_id\",\n product_name: \"$product_info.name\",\n category: \"$product_info.category\"\n },\n total_quantity: { $sum: \"$items.quantity\" },\n total_revenue: { $sum: \"$items.subtotal\" },\n order_count: { $sum: 1 },\n avg_quantity_per_order: { $avg: \"$items.quantity\" },\n avg_price: { $avg: \"$items.unit_price\" },\n customers: { $addToSet: \"$customer_id\" }\n }\n },\n\n // Stage 6: Calculate unique customer count\n {\n $addFields: {\n unique_customers: { $size: \"$customers\" }\n }\n },\n\n // Stage 7: Group by category for summary\n {\n $group: {\n _id: \"$_id.category\",\n products: {\n $push: {\n product_id: \"$_id.product_id\",\n product_name: \"$_id.product_name\",\n total_quantity: \"$total_quantity\",\n total_revenue: \"$total_revenue\",\n unique_customers: \"$unique_customers\"\n }\n },\n category_revenue: { $sum: \"$total_revenue\" },\n category_units: { $sum: \"$total_quantity\" }\n }\n },\n\n // Stage 8: Sort products within each category\n {\n $addFields: {\n products: {\n $slice: [\n {\n $sortArray: {\n input: \"$products\",\n sortBy: { total_revenue: -1 }\n }\n },\n 5 // Top 5 products per category\n ]\n }\n }\n },\n\n // Stage 9: Sort categories by revenue\n {\n $sort: { category_revenue: -1 }\n },\n\n // Stage 10: Format output\n {\n $project: {\n category: \"$_id\",\n total_revenue: {\n $round: [\"$category_revenue\", 2]\n },\n total_units: \"$category_units\",\n top_products: \"$products\",\n _id: 0\n }\n }\n])\n\n// Example output:\n[\n {\n \"category\": \"Electronics\",\n \"total_revenue\": 125430.50,\n \"total_units\": 1823,\n \"top_products\": [\n {\n \"product_id\": ObjectId(\"...\"),\n \"product_name\": \"Wireless Headphones\",\n \"total_quantity\": 234,\n \"total_revenue\": 69882.00,\n \"unique_customers\": 198\n },\n // ... 4 more products\n ]\n },\n // ... more categories\n]\n```\n\n### Example 15: Aggregation with Facets (Multiple Pipelines)\n\n**Scenario**: Execute multiple aggregation pipelines in parallel for a dashboard.\n\n```javascript\ndb.orders.aggregate([\n // Common filter stage\n {\n $match: {\n created_at: {\n $gte: ISODate(\"2025-01-01\"),\n $lt: ISODate(\"2025-02-01\")\n }\n }\n },\n\n // Facet: Multiple parallel aggregations\n {\n $facet: {\n // Facet 1: Revenue by day\n daily_revenue: [\n {\n $group: {\n _id: {\n $dateToString: {\n format: \"%Y-%m-%d\",\n date: \"$created_at\"\n }\n },\n revenue: { $sum: \"$total_amount\" },\n orders: { $sum: 1 }\n }\n },\n {\n $sort: { _id: 1 }\n },\n {\n $project: {\n date: \"$_id\",\n revenue: { $round: [\"$revenue\", 2] },\n orders: 1,\n _id: 0\n }\n }\n ],\n\n // Facet 2: Top customers\n top_customers: [\n {\n $group: {\n _id: \"$customer_id\",\n total_spent: { $sum: \"$total_amount\" },\n order_count: { $sum: 1 }\n }\n },\n {\n $sort: { total_spent: -1 }\n },\n {\n $limit: 10\n },\n {\n $lookup: {\n from: \"customers\",\n localField: \"_id\",\n foreignField: \"_id\",\n as: \"customer\"\n }\n },\n {\n $unwind: \"$customer\"\n },\n {\n $project: {\n customer_id: \"$_id\",\n customer_name: \"$customer.name\",\n email: \"$customer.email\",\n total_spent: { $round: [\"$total_spent\", 2] },\n order_count: 1,\n _id: 0\n }\n }\n ],\n\n // Facet 3: Status distribution\n status_distribution: [\n {\n $group: {\n _id: \"$status\",\n count: { $sum: 1 },\n total_value: { $sum: \"$total_amount\" }\n }\n },\n {\n $project: {\n status: \"$_id\",\n count: 1,\n total_value: { $round: [\"$total_value\", 2] },\n _id: 0\n }\n }\n ],\n\n // Facet 4: Overall statistics\n summary: [\n {\n $group: {\n _id: null,\n total_orders: { $sum: 1 },\n total_revenue: { $sum: \"$total_amount\" },\n avg_order_value: { $avg: \"$total_amount\" },\n unique_customers: { $addToSet: \"$customer_id\" }\n }\n },\n {\n $project: {\n total_orders: 1,\n total_revenue: { $round: [\"$total_revenue\", 2] },\n avg_order_value: { $round: [\"$avg_order_value\", 2] },\n unique_customers: { $size: \"$unique_customers\" },\n _id: 0\n }\n }\n ]\n }\n }\n])\n```\n\n---\n\n## MongoDB Sharding Examples\n\n### Example 16: Sharding Setup and Configuration\n\n**Scenario**: Set up a sharded cluster with zone-aware sharding for geographic distribution.\n\n```javascript\n// Step 1: Enable sharding on database\nsh.enableSharding(\"ecommerce\")\n\n// Step 2: Choose appropriate shard key\n// Option A: Hashed shard key for even distribution\nsh.shardCollection(\"ecommerce.orders\", { _id: \"hashed\" })\n\n// Option B: Range-based compound key for query isolation\nsh.shardCollection(\"ecommerce.users\", {\n region: 1,\n user_id: 1\n})\n\n// Option C: Hashed compound key\nsh.shardCollection(\"ecommerce.events\", {\n user_id: \"hashed\",\n timestamp: 1\n})\n\n// Step 3: Create zones for geographic sharding\nsh.addShardToZone(\"shard-us-east\", \"US-EAST\")\nsh.addShardToZone(\"shard-us-west\", \"US-WEST\")\nsh.addShardToZone(\"shard-eu\", \"EU\")\nsh.addShardToZone(\"shard-apac\", \"APAC\")\n\n// Step 4: Define zone ranges\nsh.updateZoneKeyRange(\n \"ecommerce.users\",\n { region: \"US\", user_id: MinKey },\n { region: \"US\", user_id: MaxKey },\n \"US-EAST\"\n)\n\nsh.updateZoneKeyRange(\n \"ecommerce.users\",\n { region: \"EU\", user_id: MinKey },\n { region: \"EU\", user_id: MaxKey },\n \"EU\"\n)\n\nsh.updateZoneKeyRange(\n \"ecommerce.users\",\n { region: \"APAC\", user_id: MinKey },\n { region: \"APAC\", user_id: MaxKey },\n \"APAC\"\n)\n\n// Step 5: Monitor sharding status\nsh.status()\n\n// Step 6: Check chunk distribution\ndb.getSiblingDB(\"config\").chunks.aggregate([\n {\n $group: {\n _id: { ns: \"$ns\", shard: \"$shard\" },\n count: { $sum: 1 }\n }\n },\n {\n $sort: { \"_id.ns\": 1, \"_id.shard\": 1 }\n }\n])\n\n// Step 7: Enable balancer (if disabled)\nsh.startBalancer()\nsh.getBalancerState()\n\n// Step 8: Check for jumbo chunks\ndb.getSiblingDB(\"config\").chunks.find({ jumbo: true })\n\n// Example: Targeted query (routes to single shard)\ndb.users.find({\n region: \"US\",\n email: \"[email protected]\"\n})\n// Routes only to US-EAST shard\n\n// Example: Scatter-gather query (routes to all shards)\ndb.users.find({\n email: \"[email protected]\"\n})\n// Routes to all shards (no shard key in query)\n```\n\n---\n\n## Cross-Database Patterns\n\n### Example 17: Polyglot Persistence Pattern\n\n**Scenario**: Use PostgreSQL for transactional data and MongoDB for product catalog.\n\n```javascript\n// Application Architecture:\n// - PostgreSQL: Orders, payments, inventory (ACID transactions)\n// - MongoDB: Product catalog, user sessions, logs (flexible schema)\n// - Sync critical data between systems\n\n// PostgreSQL: Orders table\nCREATE TABLE orders (\n id SERIAL PRIMARY KEY,\n order_number VARCHAR(50) UNIQUE NOT NULL,\n customer_id INTEGER NOT NULL,\n status VARCHAR(20) NOT NULL,\n total_amount NUMERIC(10, 2) NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE order_items (\n id SERIAL PRIMARY KEY,\n order_id INTEGER NOT NULL REFERENCES orders(id),\n product_id VARCHAR(50) NOT NULL, -- References MongoDB product_id\n quantity INTEGER NOT NULL,\n unit_price NUMERIC(10, 2) NOT NULL,\n subtotal NUMERIC(10, 2) NOT NULL\n);\n\n// MongoDB: Product catalog\n{\n _id: \"PROD-12345\",\n name: \"Wireless Mouse\",\n description: \"Ergonomic wireless mouse...\",\n price: 29.99,\n categories: [\"Electronics\", \"Computer Accessories\"],\n specifications: {\n battery_life: \"12 months\",\n connectivity: \"2.4GHz wireless\",\n dpi: \"1600\",\n buttons: 5\n },\n images: [...],\n inventory: {\n available: 150,\n reserved: 23\n },\n seo: {\n meta_title: \"...\",\n meta_description: \"...\",\n keywords: [...]\n }\n}\n\n// Synchronization pattern:\n// 1. Application creates order in PostgreSQL (transactional)\n// 2. Application reserves inventory in MongoDB\n// 3. If either fails, rollback both (saga pattern)\n\n// Application code (pseudo-code):\nasync function createOrder(orderData) {\n const pgClient = await pgPool.connect()\n const mongoSession = mongoClient.startSession()\n\n try {\n // Start PostgreSQL transaction\n await pgClient.query('BEGIN')\n\n // Start MongoDB transaction\n mongoSession.startTransaction()\n\n // 1. Create order in PostgreSQL\n const orderResult = await pgClient.query(\n 'INSERT INTO orders (customer_id, total_amount) VALUES ($1, $2) RETURNING id',\n [orderData.customer_id, orderData.total]\n )\n const orderId = orderResult.rows[0].id\n\n // 2. Insert order items\n for (const item of orderData.items) {\n await pgClient.query(\n 'INSERT INTO order_items (order_id, product_id, quantity, unit_price, subtotal) VALUES ($1, $2, $3, $4, $5)',\n [orderId, item.product_id, item.quantity, item.price, item.subtotal]\n )\n }\n\n // 3. Reserve inventory in MongoDB\n for (const item of orderData.items) {\n const result = await db.products.updateOne(\n {\n _id: item.product_id,\n \"inventory.available\": { $gte: item.quantity }\n },\n {\n $inc: {\n \"inventory.available\": -item.quantity,\n \"inventory.reserved\": item.quantity\n }\n },\n { session: mongoSession }\n )\n\n if (result.modifiedCount === 0) {\n throw new Error(`Insufficient inventory for product ${item.product_id}`)\n }\n }\n\n // Commit both transactions\n await pgClient.query('COMMIT')\n await mongoSession.commitTransaction()\n\n return { success: true, orderId }\n\n } catch (error) {\n // Rollback both transactions\n await pgClient.query('ROLLBACK')\n await mongoSession.abortTransaction()\n\n throw error\n\n } finally {\n pgClient.release()\n mongoSession.endSession()\n }\n}\n```\n\n---\n\n## Real-World Use Cases\n\n### Example 18: Multi-Tenant SaaS Application\n\n**Scenario**: Design database for a multi-tenant project management SaaS.\n\n**PostgreSQL Approach (Row-Level Security):**\n\n```sql\n-- Single database, row-level isolation\nCREATE TABLE tenants (\n id SERIAL PRIMARY KEY,\n name VARCHAR(255) NOT NULL,\n subdomain VARCHAR(100) UNIQUE NOT NULL,\n plan VARCHAR(50) NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE projects (\n id SERIAL PRIMARY KEY,\n tenant_id INTEGER NOT NULL REFERENCES tenants(id),\n name VARCHAR(255) NOT NULL,\n description TEXT,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE tasks (\n id SERIAL PRIMARY KEY,\n tenant_id INTEGER NOT NULL REFERENCES tenants(id),\n project_id INTEGER NOT NULL REFERENCES projects(id),\n title VARCHAR(255) NOT NULL,\n status VARCHAR(50),\n assignee_id INTEGER,\n due_date DATE\n);\n\n-- Enable row-level security\nALTER TABLE projects ENABLE ROW LEVEL SECURITY;\nALTER TABLE tasks ENABLE ROW LEVEL SECURITY;\n\n-- Policy: Users can only access their tenant's data\nCREATE POLICY tenant_isolation ON projects\n FOR ALL\n USING (tenant_id = current_setting('app.current_tenant')::INTEGER);\n\nCREATE POLICY tenant_isolation ON tasks\n FOR ALL\n USING (tenant_id = current_setting('app.current_tenant')::INTEGER);\n\n-- Application sets tenant context\nSET app.current_tenant = 42;\nSELECT * FROM projects; -- Only sees tenant 42's projects\n```\n\n**MongoDB Approach (Database-per-Tenant):**\n\n```javascript\n// Separate database for each tenant\n// tenant_42 database\n{\n _id: ObjectId(\"...\"),\n name: \"Website Redesign\",\n description: \"Redesign company website\",\n owner_id: ObjectId(\"...\"),\n members: [\n {\n user_id: ObjectId(\"...\"),\n role: \"admin\",\n joined_at: ISODate(\"2025-01-01\")\n },\n {\n user_id: ObjectId(\"...\"),\n role: \"member\",\n joined_at: ISODate(\"2025-01-05\")\n }\n ],\n tasks: [\n {\n _id: ObjectId(\"...\"),\n title: \"Create wireframes\",\n status: \"completed\",\n assignee_id: ObjectId(\"...\"),\n due_date: ISODate(\"2025-01-15\"),\n completed_at: ISODate(\"2025-01-14\")\n },\n {\n _id: ObjectId(\"...\"),\n title: \"Design homepage mockup\",\n status: \"in_progress\",\n assignee_id: ObjectId(\"...\"),\n due_date: ISODate(\"2025-01-20\")\n }\n ],\n created_at: ISODate(\"2024-12-01\"),\n updated_at: ISODate(\"2025-01-18\")\n}\n\n// Application routing\nfunction getTenantDatabase(tenantId) {\n return mongoClient.db(`tenant_${tenantId}`)\n}\n\nconst tenantDb = getTenantDatabase(42)\nconst projects = await tenantDb.collection('projects').find().toArray()\n```\n\n### Example 19: Real-Time Analytics Dashboard\n\n**Scenario**: Build real-time analytics for e-commerce platform.\n\n```javascript\n// MongoDB: Pre-aggregated metrics collection\n{\n _id: ObjectId(\"...\"),\n metric_type: \"daily_sales\",\n date: ISODate(\"2025-01-15\"),\n\n // Pre-computed hourly breakdown\n hourly_data: [\n { hour: 0, orders: 23, revenue: 1245.50, customers: 18 },\n { hour: 1, orders: 18, revenue: 987.25, customers: 15 },\n // ... 24 hours\n ],\n\n // Overall daily totals\n totals: {\n orders: 542,\n revenue: 28456.75,\n unique_customers: 387,\n avg_order_value: 52.48,\n items_sold: 1234\n },\n\n // Top products\n top_products: [\n {\n product_id: \"PROD-123\",\n name: \"Wireless Mouse\",\n quantity: 89,\n revenue: 2581.11\n },\n // ... top 10\n ],\n\n // Category breakdown\n by_category: [\n {\n category: \"Electronics\",\n orders: 234,\n revenue: 15678.50\n },\n // ... all categories\n ],\n\n computed_at: ISODate(\"2025-01-16T00:05:00Z\")\n}\n\n// Aggregation pipeline to compute metrics (run hourly/daily)\ndb.orders.aggregate([\n {\n $match: {\n created_at: {\n $gte: ISODate(\"2025-01-15T00:00:00Z\"),\n $lt: ISODate(\"2025-01-16T00:00:00Z\")\n },\n status: \"completed\"\n }\n },\n {\n $facet: {\n // Hourly breakdown\n hourly: [\n {\n $group: {\n _id: { $hour: \"$created_at\" },\n orders: { $sum: 1 },\n revenue: { $sum: \"$total_amount\" },\n customers: { $addToSet: \"$customer_id\" }\n }\n },\n {\n $project: {\n hour: \"$_id\",\n orders: 1,\n revenue: 1,\n customers: { $size: \"$customers\" },\n _id: 0\n }\n },\n {\n $sort: { hour: 1 }\n }\n ],\n\n // Overall totals\n totals: [\n {\n $group: {\n _id: null,\n orders: { $sum: 1 },\n revenue: { $sum: \"$total_amount\" },\n customers: { $addToSet: \"$customer_id\" }\n }\n },\n {\n $project: {\n orders: 1,\n revenue: 1,\n unique_customers: { $size: \"$customers\" },\n avg_order_value: { $divide: [\"$revenue\", \"$orders\"] },\n _id: 0\n }\n }\n ],\n\n // Top products\n top_products: [\n { $unwind: \"$items\" },\n {\n $group: {\n _id: \"$items.product_id\",\n quantity: { $sum: \"$items.quantity\" },\n revenue: { $sum: \"$items.subtotal\" }\n }\n },\n {\n $lookup: {\n from: \"products\",\n localField: \"_id\",\n foreignField: \"_id\",\n as: \"product\"\n }\n },\n { $unwind: \"$product\" },\n {\n $project: {\n product_id: \"$_id\",\n name: \"$product.name\",\n quantity: 1,\n revenue: 1,\n _id: 0\n }\n },\n { $sort: { revenue: -1 } },\n { $limit: 10 }\n ]\n }\n },\n // Merge facets and store\n {\n $project: {\n metric_type: { $literal: \"daily_sales\" },\n date: { $literal: ISODate(\"2025-01-15\") },\n hourly_data: \"$hourly\",\n totals: { $arrayElemAt: [\"$totals\", 0] },\n top_products: \"$top_products\",\n computed_at: { $literal: new Date() }\n }\n },\n // Output to metrics collection\n {\n $merge: {\n into: \"daily_metrics\",\n whenMatched: \"replace\",\n whenNotMatched: \"insert\"\n }\n }\n])\n\n// Dashboard query (fast - pre-computed)\ndb.daily_metrics.find({\n metric_type: \"daily_sales\",\n date: {\n $gte: ISODate(\"2025-01-01\"),\n $lte: ISODate(\"2025-01-31\")\n }\n}).sort({ date: 1 })\n```\n\n### Example 20: Event Sourcing Pattern\n\n**Scenario**: Implement event sourcing for order management.\n\n**PostgreSQL Event Store:**\n\n```sql\n-- Events table (append-only)\nCREATE TABLE order_events (\n id BIGSERIAL PRIMARY KEY,\n aggregate_id UUID NOT NULL,\n aggregate_type VARCHAR(50) NOT NULL,\n event_type VARCHAR(100) NOT NULL,\n event_data JSONB NOT NULL,\n metadata JSONB,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n version INTEGER NOT NULL,\n UNIQUE (aggregate_id, version)\n);\n\nCREATE INDEX idx_events_aggregate ON order_events(aggregate_id, version);\nCREATE INDEX idx_events_type ON order_events(event_type, created_at);\n\n-- Snapshots table (for performance)\nCREATE TABLE order_snapshots (\n aggregate_id UUID PRIMARY KEY,\n state JSONB NOT NULL,\n version INTEGER NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Example: Order lifecycle events\nINSERT INTO order_events (aggregate_id, aggregate_type, event_type, event_data, version)\nVALUES\n -- Event 1: Order created\n (\n '550e8400-e29b-41d4-a716-446655440000',\n 'Order',\n 'OrderCreated',\n '{\"customer_id\": 123, \"items\": [...], \"total\": 99.99}'::jsonb,\n 1\n ),\n -- Event 2: Payment received\n (\n '550e8400-e29b-41d4-a716-446655440000',\n 'Order',\n 'PaymentReceived',\n '{\"payment_method\": \"credit_card\", \"amount\": 99.99, \"transaction_id\": \"TXN-123\"}'::jsonb,\n 2\n ),\n -- Event 3: Order shipped\n (\n '550e8400-e29b-41d4-a716-446655440000',\n 'Order',\n 'OrderShipped',\n '{\"tracking_number\": \"TRK-456\", \"carrier\": \"UPS\", \"shipped_at\": \"2025-01-16T10:00:00Z\"}'::jsonb,\n 3\n );\n\n-- Rebuild order state from events\nWITH order_events_sorted AS (\n SELECT event_type, event_data, created_at\n FROM order_events\n WHERE aggregate_id = '550e8400-e29b-41d4-a716-446655440000'\n ORDER BY version\n)\nSELECT\n jsonb_agg(\n jsonb_build_object(\n 'event', event_type,\n 'data', event_data,\n 'timestamp', created_at\n )\n ORDER BY created_at\n ) as event_history\nFROM order_events_sorted;\n\n-- Create snapshot for performance\nINSERT INTO order_snapshots (aggregate_id, state, version)\nVALUES (\n '550e8400-e29b-41d4-a716-446655440000',\n '{\n \"customer_id\": 123,\n \"status\": \"shipped\",\n \"total\": 99.99,\n \"payment_status\": \"paid\",\n \"tracking_number\": \"TRK-456\"\n }'::jsonb,\n 3\n)\nON CONFLICT (aggregate_id)\nDO UPDATE SET\n state = EXCLUDED.state,\n version = EXCLUDED.version,\n created_at = CURRENT_TIMESTAMP;\n```\n\n---\n\n**Total Examples**: 20+ comprehensive, production-ready examples covering:\n- PostgreSQL schema design, advanced queries, performance tuning\n- MongoDB document modeling, aggregation, sharding\n- Cross-database patterns and real-world use cases\n- Event sourcing, multi-tenancy, analytics, time-series data\n\nThese examples integrate concepts from the Context7 documentation and demonstrate practical application of database management patterns.\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":59607,"content_sha256":"73cc3a3acc960cb782be3c69c956d66e783e59cdb21852060681b44567262bb0"},{"filename":"README.md","content":"# Database Management Patterns\n\nComprehensive skill for mastering database design, optimization, and management across PostgreSQL (SQL) and MongoDB (NoSQL) systems.\n\n## Overview\n\nThis skill provides production-ready patterns and best practices for:\n\n- **Schema Design**: Normalization vs denormalization, relational vs document models\n- **Indexing**: B-tree, hash, compound, partial, text, and geospatial indexes\n- **Transactions**: ACID guarantees, isolation levels, multi-document operations\n- **Replication**: Primary-standby, replica sets, failover strategies\n- **Sharding**: Horizontal scaling, shard key selection, zone sharding\n- **Performance**: Query optimization, explain plans, connection pooling\n- **Operations**: Monitoring, troubleshooting, maintenance\n\n## Quick Reference\n\n### Database Selection Guide\n\n| Requirement | PostgreSQL | MongoDB |\n|-------------|-----------|---------|\n| **Strong ACID transactions** | ✓ Excellent | ⚠️ Limited (single replica set) |\n| **Complex JOINs** | ✓ Excellent | ⚠️ $lookup (expensive) |\n| **Flexible schema** | ⚠️ Requires migrations | ✓ Native support |\n| **Horizontal scaling** | ⚠️ Manual sharding | ✓ Built-in sharding |\n| **JSON/Document storage** | ✓ JSONB type | ✓ Native BSON |\n| **Nested hierarchies** | ⚠️ Requires CTEs/recursion | ✓ Natural fit |\n| **Aggregation pipelines** | ✓ Window functions, CTEs | ✓ Aggregation framework |\n| **Full-text search** | ✓ tsvector/GIN | ✓ Text indexes |\n| **Geospatial queries** | ✓ PostGIS extension | ✓ Native 2dsphere |\n| **Maturity & tooling** | ✓ Very mature | ✓ Mature |\n\n### When to Use PostgreSQL\n\n✅ **Ideal for:**\n- Financial applications requiring strict consistency\n- Complex data relationships with frequent JOINs\n- Well-defined schemas that change infrequently\n- Applications requiring advanced SQL features\n- Strong data integrity guarantees (foreign keys, constraints)\n- Multi-step transactions across multiple tables\n- Regulatory compliance requiring audit trails\n\n**Example use cases:**\n- E-commerce order processing\n- Banking and financial systems\n- Inventory management\n- Enterprise resource planning (ERP)\n- Customer relationship management (CRM)\n\n### When to Use MongoDB\n\n✅ **Ideal for:**\n- Applications with evolving/flexible schemas\n- Rapid prototyping and agile development\n- Content management systems with varied document types\n- Real-time analytics and event logging\n- Mobile and web apps with JSON APIs\n- Hierarchical or deeply nested data\n- Applications requiring horizontal scalability\n\n**Example use cases:**\n- Content management systems (CMS)\n- Mobile app backends\n- Real-time analytics dashboards\n- Product catalogs with varied attributes\n- Session storage and caching\n- Internet of Things (IoT) event data\n\n## Schema Design Decision Framework\n\n### PostgreSQL Schema Design\n\n```\nStart: What is your data structure?\n│\n├─ Well-defined, stable relationships?\n│ └─ Use normalized tables with foreign keys\n│\n├─ Need for data integrity constraints?\n│ └─ Use CHECK constraints, triggers, foreign keys\n│\n├─ Hierarchical data (categories, org charts)?\n│ ├─ Shallow hierarchy → Adjacency list (parent_id)\n│ └─ Deep hierarchy → Materialized path or closure table\n│\n├─ Temporal data (historical tracking)?\n│ └─ Use temporal tables or audit log pattern\n│\n└─ JSON data within relational structure?\n └─ Use JSONB columns for flexible attributes\n```\n\n### MongoDB Schema Design\n\n```\nStart: What are your access patterns?\n│\n├─ Data always accessed together?\n│ └─ Embed documents (denormalize)\n│\n├─ Data accessed independently?\n│ └─ Reference documents (normalize)\n│\n├─ One-to-few relationship (\u003c 100 items)?\n│ └─ Embed array in parent document\n│\n├─ One-to-many relationship (100-10,000 items)?\n│ └─ Store parent reference in child documents\n│\n├─ One-to-squillions (unbounded)?\n│ └─ Store child reference array in parent (paginate)\n│\n└─ Many-to-many relationship?\n └─ Use intermediate collection with references\n```\n\n## Core Indexing Strategies\n\n### PostgreSQL Index Types\n\n| Index Type | Use Case | Example |\n|-----------|----------|---------|\n| **B-tree** (default) | Equality, range, sorting | `CREATE INDEX idx_email ON users(email)` |\n| **Hash** | Equality only | `CREATE INDEX USING HASH ON sessions(token)` |\n| **GIN** | Full-text, JSONB, arrays | `CREATE INDEX USING GIN ON docs(content_tsv)` |\n| **GiST** | Geometric, full-text | `CREATE INDEX USING GIST ON locations(geom)` |\n| **BRIN** | Very large tables, sorted | `CREATE INDEX USING BRIN ON logs(timestamp)` |\n| **Partial** | Subset of rows | `CREATE INDEX ON users(email) WHERE active=true` |\n| **Expression** | Computed values | `CREATE INDEX ON users(LOWER(email))` |\n\n### MongoDB Index Types\n\n| Index Type | Use Case | Example |\n|-----------|----------|---------|\n| **Single field** | Simple queries | `db.users.createIndex({ email: 1 })` |\n| **Compound** | Multiple field queries | `db.posts.createIndex({ author: 1, date: -1 })` |\n| **Multikey** | Array fields | `db.posts.createIndex({ tags: 1 })` |\n| **Text** | Full-text search | `db.articles.createIndex({ content: \"text\" })` |\n| **Geospatial** | Location queries | `db.places.createIndex({ loc: \"2dsphere\" })` |\n| **Hashed** | Even distribution (sharding) | `db.users.createIndex({ _id: \"hashed\" })` |\n| **Wildcard** | Flexible schema fields | `db.products.createIndex({ \"$**\": 1 })` |\n\n### ESR Rule for Compound Indexes (MongoDB)\n\nOptimal compound index column order:\n\n1. **Equality** filters first (exact matches)\n2. **Sort** fields second\n3. **Range** filters last\n\n**Example:**\n```javascript\n// Query pattern\ndb.orders.find({\n status: \"completed\", // Equality\n total: { $gte: 100 } // Range\n}).sort({ created_at: -1 }) // Sort\n\n// Optimal index order\ndb.orders.createIndex({\n status: 1, // 1. Equality\n created_at: -1, // 2. Sort\n total: 1 // 3. Range\n})\n```\n\n## Transaction Patterns\n\n### PostgreSQL Isolation Levels\n\n| Level | Dirty Read | Non-Repeatable Read | Phantom Read | Performance |\n|-------|-----------|---------------------|--------------|-------------|\n| **Read Uncommitted** | Possible | Possible | Possible | Fastest |\n| **Read Committed** (default) | Prevented | Possible | Possible | Fast |\n| **Repeatable Read** | Prevented | Prevented | Possible | Slower |\n| **Serializable** | Prevented | Prevented | Prevented | Slowest |\n\n**Common scenarios:**\n- **Read Committed**: Most web applications (default, good balance)\n- **Repeatable Read**: Reports requiring consistent snapshots\n- **Serializable**: Financial transactions requiring strict ordering\n\n### MongoDB Read/Write Concerns\n\n**Write Concern Levels:**\n- `w: 1` - Acknowledge after writing to primary (fast, less durable)\n- `w: \"majority\"` - Acknowledge after majority of replica set (slower, durable)\n- `j: true` - Wait for journal write (durability guarantee)\n\n**Read Concern Levels:**\n- `local` - Return latest data from node (fastest, may read rolled-back data)\n- `majority` - Return data acknowledged by majority (slower, consistent)\n- `linearizable` - Strongest consistency (slowest, serializable)\n\n**Recommendation:**\n- **Critical data** (payments, orders): `{ w: \"majority\", j: true }`\n- **Regular data**: `{ w: 1 }`\n- **Analytics/reporting**: Read from secondaries with `readPreference: \"secondary\"`\n\n## Performance Optimization Checklist\n\n### PostgreSQL Performance\n\n- [ ] Enable and configure `pg_stat_statements` extension\n- [ ] Set appropriate `shared_buffers` (25% of RAM)\n- [ ] Configure `effective_cache_size` (50-75% of RAM)\n- [ ] Enable autovacuum with appropriate thresholds\n- [ ] Create indexes on foreign keys\n- [ ] Use `EXPLAIN ANALYZE` for slow queries\n- [ ] Implement connection pooling (PgBouncer)\n- [ ] Monitor long-running queries\n- [ ] Partition large tables (>10M rows)\n- [ ] Use prepared statements in application code\n\n### MongoDB Performance\n\n- [ ] Create indexes matching query patterns\n- [ ] Use covered queries when possible\n- [ ] Enable profiling for slow queries\n- [ ] Monitor index usage with `$indexStats`\n- [ ] Choose appropriate shard key (high cardinality, even distribution)\n- [ ] Configure replica set with appropriate read preferences\n- [ ] Use projection to limit returned fields\n- [ ] Batch operations when possible\n- [ ] Monitor replication lag\n- [ ] Set appropriate connection pool size\n\n## Replication & High Availability\n\n### PostgreSQL Replication Setup\n\n**Streaming Replication (Primary-Standby):**\n```\nPrimary Server\n │\n ├─→ Standby 1 (synchronous)\n ├─→ Standby 2 (asynchronous)\n └─→ Standby 3 (asynchronous)\n```\n\n**Benefits:**\n- Read scaling (read queries from standbys)\n- High availability (automatic failover)\n- Zero data loss (synchronous replication)\n- Point-in-time recovery\n\n**Configuration:**\n- Synchronous replication: Zero data loss, slower writes\n- Asynchronous replication: Faster writes, possible data loss on failure\n\n### MongoDB Replica Set\n\n**Typical 3-Node Replica Set:**\n```\nPrimary (writes)\n │\n ├─→ Secondary 1 (reads, failover)\n └─→ Secondary 2 (reads, failover)\n```\n\n**Benefits:**\n- Automatic failover (election in ~12 seconds)\n- Read scaling (read from secondaries)\n- Data redundancy\n- Rolling upgrades without downtime\n\n**Topology Options:**\n- 3 data-bearing members (standard)\n- 2 data + 1 arbiter (voting only, saves storage)\n- 5+ members for critical systems\n- Hidden members for analytics\n- Delayed members for disaster recovery\n\n## Sharding Strategies\n\n### MongoDB Sharding Architectures\n\n**Range-Based Sharding:**\n```\nShard Key: timestamp\n─────────────────────────────\nShard 1: 2020-01-01 to 2022-12-31\nShard 2: 2023-01-01 to 2024-12-31\nShard 3: 2025-01-01 to current\n```\n- **Pros**: Range queries target specific shards\n- **Cons**: Uneven distribution (recent data gets all writes)\n\n**Hashed Sharding:**\n```\nShard Key: _id (hashed)\n─────────────────────────────\nShard 1: hash values 0-3333...\nShard 2: hash values 3333...-6666...\nShard 3: hash values 6666...-9999...\n```\n- **Pros**: Even distribution\n- **Cons**: Range queries scatter to all shards\n\n**Zone/Tag-Aware Sharding:**\n```\nGeographic distribution:\n─────────────────────────────\nUS Shard: { region: \"US\" }\nEU Shard: { region: \"EU\" }\nAPAC Shard: { region: \"APAC\" }\n```\n- **Pros**: Data locality, compliance (GDPR)\n- **Cons**: Requires careful capacity planning\n\n### PostgreSQL Partitioning\n\n**Horizontal Partitioning (Sharding):**\n- Use Citus extension for distributed PostgreSQL\n- Application-level sharding with multiple databases\n- Foreign Data Wrappers (FDW) for federated queries\n\n**Vertical Partitioning:**\n- Split large tables into frequently/rarely accessed columns\n- Store BLOBs in separate table\n\n## Monitoring and Observability\n\n### Key PostgreSQL Metrics\n\n| Metric | Target | Command |\n|--------|--------|---------|\n| **Cache hit ratio** | > 99% | `SELECT * FROM pg_stat_database` |\n| **Active connections** | \u003c max_connections | `SELECT count(*) FROM pg_stat_activity` |\n| **Deadlocks** | Minimal | `SELECT deadlocks FROM pg_stat_database` |\n| **Replication lag** | \u003c 1 second | `SELECT pg_wal_lsn_diff(...)` |\n| **Bloat** | \u003c 20% | `pgstattuple` extension |\n| **Slow queries** | None > 1s | `pg_stat_statements` |\n\n### Key MongoDB Metrics\n\n| Metric | Target | Command |\n|--------|--------|---------|\n| **Replication lag** | \u003c 1 second | `rs.printSecondaryReplicationInfo()` |\n| **Index efficiency** | 1:1 ratio | docs examined / docs returned |\n| **Connection count** | \u003c pool max | `db.serverStatus().connections` |\n| **Queue depth** | \u003c 10 | `db.serverStatus().globalLock.currentQueue` |\n| **Memory usage** | \u003c 80% | `db.serverStatus().mem` |\n| **Chunk distribution** | Even | `sh.status()` |\n\n## Common Design Patterns\n\n### PostgreSQL Patterns\n\n1. **Audit Trail**: Triggers + audit table for change history\n2. **Soft Delete**: `deleted_at` column instead of DELETE\n3. **Optimistic Locking**: Version column to detect concurrent updates\n4. **Event Sourcing**: Immutable event log, rebuild state\n5. **Materialized View**: Pre-computed aggregations for fast reads\n6. **Temporal Tables**: System-versioned tables for time travel\n7. **Queue Pattern**: `FOR UPDATE SKIP LOCKED` for job queues\n\n### MongoDB Patterns\n\n1. **Embedded**: Store related data in single document\n2. **Bucketing**: Group time-series into periodic buckets\n3. **Computed**: Store pre-aggregated values\n4. **Subset**: Store frequently accessed fields, reference full data\n5. **Extended Reference**: Embed key fields, reference for full data\n6. **Approximation**: Store statistical approximations for large sets\n7. **Outlier**: Separate handling for edge cases (e.g., popular items)\n\n## Migration Strategies\n\n### SQL to NoSQL Migration\n\n**When to migrate:**\n- Schema changes too frequent/expensive\n- Need horizontal scalability beyond single server\n- Document-oriented data is natural fit\n- Application primarily JSON/REST API\n\n**Approach:**\n1. **Analyze access patterns**: Understand how data is queried\n2. **Design document model**: Embed vs reference decisions\n3. **Dual-write period**: Write to both databases\n4. **Gradual read migration**: Move reads collection by collection\n5. **Deprecate old system**: After validation period\n\n### NoSQL to SQL Migration\n\n**When to migrate:**\n- Need for complex JOINs and relational queries\n- Strong consistency requirements\n- Schema has stabilized\n- Advanced SQL features needed (window functions, CTEs)\n\n**Approach:**\n1. **Normalize schema**: Break documents into related tables\n2. **Create foreign keys**: Establish relationships\n3. **Migrate data**: Write ETL scripts\n4. **Validate integrity**: Check constraints and references\n5. **Update application**: Modify queries and ORM models\n\n## Security Best Practices\n\n### PostgreSQL Security\n\n- ✅ Use SSL/TLS for all connections\n- ✅ Implement row-level security (RLS) for multi-tenant apps\n- ✅ Grant minimum necessary privileges (principle of least privilege)\n- ✅ Use parameterized queries (prevent SQL injection)\n- ✅ Enable audit logging for sensitive tables\n- ✅ Rotate passwords regularly\n- ✅ Encrypt sensitive columns (pgcrypto extension)\n- ✅ Backup encryption for WAL archives\n\n### MongoDB Security\n\n- ✅ Enable authentication and authorization\n- ✅ Use TLS/SSL for client and replica set connections\n- ✅ Implement role-based access control (RBAC)\n- ✅ Enable audit logging (Enterprise feature)\n- ✅ Encrypt data at rest\n- ✅ Network isolation (private networks, VPNs)\n- ✅ Regular backups with encryption\n- ✅ Disable JavaScript execution if not needed\n\n## Troubleshooting Quick Reference\n\n### PostgreSQL Issues\n\n| Symptom | Likely Cause | Solution |\n|---------|--------------|----------|\n| Slow queries | Missing index | Run `EXPLAIN ANALYZE`, add index |\n| High CPU | Expensive queries | Check `pg_stat_statements`, optimize |\n| Connection errors | Max connections | Increase `max_connections`, use pooling |\n| Deadlocks | Lock ordering | Review transaction logic |\n| Bloat | No vacuuming | Enable autovacuum, run manual VACUUM |\n| Replication lag | Network/load | Check bandwidth, reduce write load |\n\n### MongoDB Issues\n\n| Symptom | Likely Cause | Solution |\n|---------|--------------|----------|\n| Slow queries | Missing index | Run `.explain()`, create index |\n| High memory | Working set > RAM | Add RAM, optimize queries, scale out |\n| Write conflicts | Hotspot shard key | Choose better shard key |\n| Replication lag | Oplog too small | Increase oplog size |\n| Uneven sharding | Poor shard key | Re-shard with better key |\n| Jumbo chunks | Indivisible data | Refine shard key, manual split |\n\n## Resources & Tools\n\n### PostgreSQL Tools\n- **pgAdmin**: GUI administration\n- **psql**: Command-line client\n- **pg_stat_statements**: Query performance analysis\n- **PgBouncer**: Connection pooling\n- **Patroni**: High availability and failover\n- **Barman**: Backup and recovery\n- **PostGIS**: Geospatial extension\n\n### MongoDB Tools\n- **MongoDB Compass**: GUI explorer\n- **mongosh**: Modern shell\n- **MongoDB Atlas**: Managed cloud service\n- **mongo-express**: Web-based admin\n- **Percona Monitoring**: Performance monitoring\n- **mongodump/mongorestore**: Backup utilities\n\n---\n\n**Quick Links:**\n- [Full SKILL.md Documentation](./SKILL.md)\n- [Detailed Examples](./EXAMPLES.md)\n- PostgreSQL Docs: https://www.postgresql.org/docs/\n- MongoDB Docs: https://docs.mongodb.com/\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":16714,"content_sha256":"00f1a83822f12ccacc76b8d190e942b6b73ad31032c672523c796a16065c4a76"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"Database Management Patterns","type":"text"}]},{"type":"paragraph","content":[{"text":"A comprehensive skill for mastering database management across SQL (PostgreSQL) and NoSQL (MongoDB) systems. This skill covers schema design, indexing strategies, transaction management, replication, sharding, and performance optimization for production-grade applications.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"When to Use This Skill","type":"text"}]},{"type":"paragraph","content":[{"text":"Use this skill when:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Designing database schemas","type":"text","marks":[{"type":"strong"}]},{"text":" for new applications or refactoring existing ones","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Choosing between SQL and NoSQL","type":"text","marks":[{"type":"strong"}]},{"text":" databases for your use case","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Optimizing query performance","type":"text","marks":[{"type":"strong"}]},{"text":" with proper indexing strategies","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Implementing data consistency","type":"text","marks":[{"type":"strong"}]},{"text":" with transactions and ACID guarantees","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Scaling databases","type":"text","marks":[{"type":"strong"}]},{"text":" horizontally with sharding and replication","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Managing high-traffic applications","type":"text","marks":[{"type":"strong"}]},{"text":" requiring distributed databases","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Ensuring data integrity","type":"text","marks":[{"type":"strong"}]},{"text":" with constraints, triggers, and validation","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Troubleshooting performance issues","type":"text","marks":[{"type":"strong"}]},{"text":" using explain plans and query analysis","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Building fault-tolerant systems","type":"text","marks":[{"type":"strong"}]},{"text":" with replication and failover strategies","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Working with complex data relationships","type":"text","marks":[{"type":"strong"}]},{"text":" (relational) or flexible schemas (document)","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Core Concepts","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Database Paradigms Comparison","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Relational Databases (PostgreSQL)","type":"text"}]},{"type":"paragraph","content":[{"text":"Strengths:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"ACID Transactions","type":"text","marks":[{"type":"strong"}]},{"text":": Strong consistency guarantees","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Complex Queries","type":"text","marks":[{"type":"strong"}]},{"text":": JOIN operations, subqueries, CTEs","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Data Integrity","type":"text","marks":[{"type":"strong"}]},{"text":": Foreign keys, constraints, triggers","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Normalized Data","type":"text","marks":[{"type":"strong"}]},{"text":": Reduced redundancy, consistent updates","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Mature Ecosystem","type":"text","marks":[{"type":"strong"}]},{"text":": Rich tooling, extensions, community","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Best For:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Financial systems requiring strict consistency","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Complex relationships and data integrity requirements","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Applications with structured, well-defined schemas","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Systems requiring complex analytical queries","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Multi-step transactions across multiple tables","type":"text"}]}]}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Document Databases (MongoDB)","type":"text"}]},{"type":"paragraph","content":[{"text":"Strengths:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Flexible Schema","type":"text","marks":[{"type":"strong"}]},{"text":": Easy schema evolution, polymorphic data","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Horizontal Scalability","type":"text","marks":[{"type":"strong"}]},{"text":": Built-in sharding support","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"JSON-Native","type":"text","marks":[{"type":"strong"}]},{"text":": Natural fit for modern application development","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Embedded Documents","type":"text","marks":[{"type":"strong"}]},{"text":": Denormalized data for performance","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Aggregation Framework","type":"text","marks":[{"type":"strong"}]},{"text":": Powerful data processing pipeline","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Best For:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Rapidly evolving applications with changing requirements","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Content management systems with varied data structures","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Real-time analytics and event logging","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Mobile and web applications with JSON APIs","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Hierarchical or nested data structures","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"ACID Properties","type":"text"}]},{"type":"paragraph","content":[{"text":"Atomicity","type":"text","marks":[{"type":"strong"}]},{"text":": All operations in a transaction succeed or fail together ","type":"text"},{"text":"Consistency","type":"text","marks":[{"type":"strong"}]},{"text":": Transactions bring database from one valid state to another ","type":"text"},{"text":"Isolation","type":"text","marks":[{"type":"strong"}]},{"text":": Concurrent transactions don't interfere with each other ","type":"text"},{"text":"Durability","type":"text","marks":[{"type":"strong"}]},{"text":": Committed transactions survive system failures","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"CAP Theorem","type":"text"}]},{"type":"paragraph","content":[{"text":"In distributed systems, choose two of three:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Consistency","type":"text","marks":[{"type":"strong"}]},{"text":": All nodes see the same data","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Availability","type":"text","marks":[{"type":"strong"}]},{"text":": System remains operational","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Partition Tolerance","type":"text","marks":[{"type":"strong"}]},{"text":": System continues despite network failures","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"PostgreSQL emphasizes CP (Consistency + Partition Tolerance) MongoDB can be configured for CP or AP depending on write/read concerns","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"PostgreSQL Patterns","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Schema Design Fundamentals","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Normalization Levels","type":"text"}]},{"type":"paragraph","content":[{"text":"First Normal Form (1NF)","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Atomic values (no arrays or lists in columns)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Each row is unique (primary key exists)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"No repeating groups","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Second Normal Form (2NF)","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Meets 1NF requirements","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"All non-key attributes depend on the entire primary key","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Third Normal Form (3NF)","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Meets 2NF requirements","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"No transitive dependencies (non-key attributes depend only on primary key)","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"When to Denormalize:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Read-heavy workloads where joins are expensive","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Frequently accessed aggregate data","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Historical snapshots that shouldn't change","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Performance-critical queries","type":"text"}]}]}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Table Design Patterns","type":"text"}]},{"type":"paragraph","content":[{"text":"Primary Keys:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Serial auto-increment (traditional)\nCREATE TABLE users (\n id SERIAL PRIMARY KEY,\n email VARCHAR(255) UNIQUE NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- UUID for distributed systems\nCREATE TABLE accounts (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n name VARCHAR(255) NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Composite primary key\nCREATE TABLE order_items (\n order_id INTEGER NOT NULL,\n product_id INTEGER NOT NULL,\n quantity INTEGER NOT NULL,\n price NUMERIC(10, 2) NOT NULL,\n PRIMARY KEY (order_id, product_id),\n FOREIGN KEY (order_id) REFERENCES orders(id),\n FOREIGN KEY (product_id) REFERENCES products(id)\n);","type":"text"}]},{"type":"paragraph","content":[{"text":"Foreign Key Constraints:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Cascade delete: Remove child records when parent deleted\nCREATE TABLE posts (\n id SERIAL PRIMARY KEY,\n user_id INTEGER NOT NULL,\n title VARCHAR(255) NOT NULL,\n content TEXT,\n FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE\n);\n\n-- Set null: Preserve child records, nullify reference\nCREATE TABLE comments (\n id SERIAL PRIMARY KEY,\n post_id INTEGER,\n user_id INTEGER,\n content TEXT NOT NULL,\n FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE SET NULL,\n FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL\n);\n\n-- Restrict: Prevent deletion if child records exist\nCREATE TABLE categories (\n id SERIAL PRIMARY KEY,\n name VARCHAR(255) UNIQUE NOT NULL\n);\n\nCREATE TABLE products (\n id SERIAL PRIMARY KEY,\n category_id INTEGER NOT NULL,\n name VARCHAR(255) NOT NULL,\n FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT\n);","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Advanced Constraints","type":"text"}]},{"type":"paragraph","content":[{"text":"Check Constraints:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE TABLE products (\n id SERIAL PRIMARY KEY,\n name VARCHAR(255) NOT NULL,\n price NUMERIC(10, 2) NOT NULL CHECK (price > 0),\n discount_percent INTEGER CHECK (discount_percent BETWEEN 0 AND 100),\n stock_quantity INTEGER NOT NULL CHECK (stock_quantity >= 0)\n);\n\n-- Table-level check constraint\nCREATE TABLE date_ranges (\n id SERIAL PRIMARY KEY,\n start_date DATE NOT NULL,\n end_date DATE NOT NULL,\n CHECK (end_date > start_date)\n);","type":"text"}]},{"type":"paragraph","content":[{"text":"Unique Constraints:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Single column unique\nCREATE TABLE users (\n id SERIAL PRIMARY KEY,\n email VARCHAR(255) UNIQUE NOT NULL,\n username VARCHAR(50) UNIQUE NOT NULL\n);\n\n-- Composite unique constraint\nCREATE TABLE user_permissions (\n user_id INTEGER NOT NULL,\n permission_id INTEGER NOT NULL,\n granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n UNIQUE (user_id, permission_id),\n FOREIGN KEY (user_id) REFERENCES users(id),\n FOREIGN KEY (permission_id) REFERENCES permissions(id)\n);\n\n-- Partial unique index (unique where condition met)\nCREATE UNIQUE INDEX unique_active_email\nON users (email)\nWHERE active = true;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Triggers and Functions","type":"text"}]},{"type":"paragraph","content":[{"text":"Audit Trail Pattern:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Audit table\nCREATE TABLE audit_log (\n id SERIAL PRIMARY KEY,\n table_name VARCHAR(255) NOT NULL,\n record_id INTEGER NOT NULL,\n action VARCHAR(10) NOT NULL,\n old_data JSONB,\n new_data JSONB,\n changed_by VARCHAR(255),\n changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Trigger function\nCREATE OR REPLACE FUNCTION audit_trigger_function()\nRETURNS TRIGGER AS $\nBEGIN\n IF TG_OP = 'INSERT' THEN\n INSERT INTO audit_log (table_name, record_id, action, new_data, changed_by)\n VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', row_to_json(NEW), current_user);\n RETURN NEW;\n ELSIF TG_OP = 'UPDATE' THEN\n INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by)\n VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', row_to_json(OLD), row_to_json(NEW), current_user);\n RETURN NEW;\n ELSIF TG_OP = 'DELETE' THEN\n INSERT INTO audit_log (table_name, record_id, action, old_data, changed_by)\n VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', row_to_json(OLD), current_user);\n RETURN OLD;\n END IF;\nEND;\n$ LANGUAGE plpgsql;\n\n-- Attach trigger to table\nCREATE TRIGGER users_audit_trigger\nAFTER INSERT OR UPDATE OR DELETE ON users\nFOR EACH ROW EXECUTE FUNCTION audit_trigger_function();","type":"text"}]},{"type":"paragraph","content":[{"text":"Timestamp Update Pattern:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE OR REPLACE FUNCTION update_modified_timestamp()\nRETURNS TRIGGER AS $\nBEGIN\n NEW.updated_at = CURRENT_TIMESTAMP;\n RETURN NEW;\nEND;\n$ LANGUAGE plpgsql;\n\nCREATE TABLE posts (\n id SERIAL PRIMARY KEY,\n title VARCHAR(255) NOT NULL,\n content TEXT,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TRIGGER posts_update_timestamp\nBEFORE UPDATE ON posts\nFOR EACH ROW EXECUTE FUNCTION update_modified_timestamp();","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Views and Materialized Views","type":"text"}]},{"type":"paragraph","content":[{"text":"Standard Views:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Virtual table - computed on each query\nCREATE VIEW active_users_with_posts AS\nSELECT\n u.id,\n u.username,\n u.email,\n COUNT(p.id) as post_count,\n MAX(p.created_at) as last_post_date\nFROM users u\nLEFT JOIN posts p ON u.id = p.user_id\nWHERE u.active = true\nGROUP BY u.id, u.username, u.email;\n\n-- Use view like a table\nSELECT * FROM active_users_with_posts WHERE post_count > 10;","type":"text"}]},{"type":"paragraph","content":[{"text":"Materialized Views:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Physical table - stores computed results\nCREATE MATERIALIZED VIEW user_statistics AS\nSELECT\n u.id,\n u.username,\n COUNT(DISTINCT p.id) as total_posts,\n COUNT(DISTINCT c.id) as total_comments,\n AVG(p.views) as avg_post_views,\n MAX(p.created_at) as last_activity\nFROM users u\nLEFT JOIN posts p ON u.id = p.user_id\nLEFT JOIN comments c ON u.id = c.user_id\nGROUP BY u.id, u.username;\n\n-- Create index on materialized view\nCREATE INDEX idx_user_stats_posts ON user_statistics(total_posts);\n\n-- Refresh materialized view (update data)\nREFRESH MATERIALIZED VIEW user_statistics;\n\n-- Concurrent refresh (allows reads during refresh)\nREFRESH MATERIALIZED VIEW CONCURRENTLY user_statistics;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"MongoDB Patterns","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Document Modeling Strategies","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Embedding vs Referencing","type":"text"}]},{"type":"paragraph","content":[{"text":"Embedding Pattern (Denormalization):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// One-to-few: Embed when relationship is contained\n// Example: Blog post with comments\n{\n _id: ObjectId(\"...\"),\n title: \"Database Design Patterns\",\n author: \"John Doe\",\n content: \"...\",\n published_at: ISODate(\"2025-01-15\"),\n comments: [\n {\n _id: ObjectId(\"...\"),\n author: \"Jane Smith\",\n text: \"Great article!\",\n created_at: ISODate(\"2025-01-16\")\n },\n {\n _id: ObjectId(\"...\"),\n author: \"Bob Johnson\",\n text: \"Very helpful, thanks!\",\n created_at: ISODate(\"2025-01-17\")\n }\n ],\n tags: [\"database\", \"design\", \"patterns\"],\n stats: {\n views: 1523,\n likes: 89,\n shares: 23\n }\n}\n\n// Benefits:\n// - Single query to retrieve post with comments\n// - Better read performance\n// - Atomic updates to entire document\n//\n// Drawbacks:\n// - Document size limits (16MB in MongoDB)\n// - Difficult to query comments independently\n// - May duplicate data if comments need to appear elsewhere","type":"text"}]},{"type":"paragraph","content":[{"text":"Referencing Pattern (Normalization):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// One-to-many or many-to-many: Reference when relationship is unbounded\n// Example: User with many posts\n\n// Users collection\n{\n _id: ObjectId(\"507f1f77bcf86cd799439011\"),\n username: \"john_doe\",\n email: \"[email protected]\",\n profile: {\n bio: \"Software engineer\",\n avatar_url: \"https://...\",\n location: \"San Francisco\"\n },\n created_at: ISODate(\"2024-01-01\")\n}\n\n// Posts collection (references user)\n{\n _id: ObjectId(\"507f191e810c19729de860ea\"),\n user_id: ObjectId(\"507f1f77bcf86cd799439011\"),\n title: \"My First Post\",\n content: \"...\",\n published_at: ISODate(\"2025-01-15\"),\n comment_ids: [\n ObjectId(\"...\"),\n ObjectId(\"...\")\n ]\n}\n\n// Benefits:\n// - No duplication of user data\n// - Flexible: users can have unlimited posts\n// - Easy to update user information once\n//\n// Drawbacks:\n// - Requires multiple queries or $lookup\n// - Slower read performance for joined data","type":"text"}]},{"type":"paragraph","content":[{"text":"Hybrid Approach (Selective Denormalization):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Store frequently accessed fields from referenced document\n{\n _id: ObjectId(\"...\"),\n title: \"Database Patterns\",\n content: \"...\",\n author: {\n // Embedded: frequently accessed, rarely changes\n id: ObjectId(\"507f1f77bcf86cd799439011\"),\n username: \"john_doe\",\n avatar_url: \"https://...\"\n },\n // Reference: full user data available if needed\n author_id: ObjectId(\"507f1f77bcf86cd799439011\"),\n published_at: ISODate(\"2025-01-15\")\n}\n\n// Benefits:\n// - Fast reads with embedded frequently-used data\n// - Can still get full user data when needed\n// - Balance between performance and flexibility\n//\n// Tradeoffs:\n// - Need to update embedded data when user changes username/avatar\n// - Slightly larger documents","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Schema Design Patterns","type":"text"}]},{"type":"paragraph","content":[{"text":"Bucket Pattern (Time-Series Data):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Instead of one document per measurement:\n// BAD: Millions of tiny documents\n{\n sensor_id: \"sensor_001\",\n timestamp: ISODate(\"2025-01-15T10:00:00Z\"),\n temperature: 72.5,\n humidity: 45\n}\n\n// GOOD: Bucket documents with arrays of measurements\n{\n sensor_id: \"sensor_001\",\n date: ISODate(\"2025-01-15\"),\n hour: 10,\n measurements: [\n { minute: 0, temperature: 72.5, humidity: 45 },\n { minute: 1, temperature: 72.6, humidity: 45 },\n { minute: 2, temperature: 72.4, humidity: 46 },\n // ... up to 60 measurements per hour\n ],\n summary: {\n count: 60,\n avg_temperature: 72.5,\n min_temperature: 71.8,\n max_temperature: 73.2\n }\n}\n\n// Benefits:\n// - Reduced document count (60x fewer documents)\n// - Better index efficiency\n// - Pre-computed summaries\n// - Easier to query by time ranges","type":"text"}]},{"type":"paragraph","content":[{"text":"Computed Pattern (Pre-Aggregated Data):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Store computed values to avoid expensive aggregations\n{\n _id: ObjectId(\"...\"),\n product_id: \"PROD-123\",\n month: \"2025-01\",\n total_sales: 15420.50,\n units_sold: 234,\n unique_customers: 187,\n avg_order_value: 65.90,\n top_customers: [\n { customer_id: \"CUST-456\", revenue: 890.50 },\n { customer_id: \"CUST-789\", revenue: 675.25 }\n ],\n computed_at: ISODate(\"2025-02-01T00:00:00Z\")\n}\n\n// Update pattern: Scheduled job or trigger updates computed values","type":"text"}]},{"type":"paragraph","content":[{"text":"Polymorphic Pattern (Varied Schemas):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Handle different product types in single collection\n{\n _id: ObjectId(\"...\"),\n type: \"book\",\n name: \"Database Design\",\n price: 49.99,\n // Book-specific fields\n isbn: \"978-0-123456-78-9\",\n author: \"John Smith\",\n pages: 456,\n publisher: \"Tech Books Inc\"\n}\n\n{\n _id: ObjectId(\"...\"),\n type: \"electronics\",\n name: \"Wireless Mouse\",\n price: 29.99,\n // Electronics-specific fields\n brand: \"TechBrand\",\n warranty_months: 24,\n specifications: {\n battery_life: \"6 months\",\n connectivity: \"Bluetooth 5.0\"\n }\n}\n\n// Query by type\ndb.products.find({ type: \"book\", author: \"John Smith\" })\ndb.products.find({ type: \"electronics\", \"specifications.connectivity\": /Bluetooth/ })","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Aggregation Framework","type":"text"}]},{"type":"paragraph","content":[{"text":"Basic Aggregation Pipeline:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Group by author and count posts\ndb.posts.aggregate([\n {\n $match: { published: true } // Filter stage\n },\n {\n $group: {\n _id: \"$author_id\",\n total_posts: { $sum: 1 },\n total_views: { $sum: \"$views\" },\n avg_views: { $avg: \"$views\" },\n latest_post: { $max: \"$published_at\" }\n }\n },\n {\n $sort: { total_posts: -1 } // Sort by post count\n },\n {\n $limit: 10 // Top 10 authors\n }\n])","type":"text"}]},{"type":"paragraph","content":[{"text":"Advanced Pipeline with Lookup (Join):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Join posts with user data\ndb.posts.aggregate([\n {\n $match: {\n published_at: { $gte: ISODate(\"2025-01-01\") }\n }\n },\n {\n $lookup: {\n from: \"users\",\n localField: \"author_id\",\n foreignField: \"_id\",\n as: \"author\"\n }\n },\n {\n $unwind: \"$author\" // Flatten author array\n },\n {\n $project: {\n title: 1,\n content: 1,\n views: 1,\n \"author.username\": 1,\n \"author.email\": 1,\n days_since_publish: {\n $divide: [\n { $subtract: [new Date(), \"$published_at\"] },\n 1000 * 60 * 60 * 24\n ]\n }\n }\n },\n {\n $sort: { views: -1 }\n }\n])","type":"text"}]},{"type":"paragraph","content":[{"text":"Aggregation with Grouping and Reshaping:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Complex aggregation: Sales analysis\ndb.orders.aggregate([\n {\n $match: {\n status: \"completed\",\n created_at: {\n $gte: ISODate(\"2025-01-01\"),\n $lt: ISODate(\"2025-02-01\")\n }\n }\n },\n {\n $unwind: \"$items\" // Flatten order items\n },\n {\n $group: {\n _id: {\n product_id: \"$items.product_id\",\n customer_region: \"$customer.region\"\n },\n total_quantity: { $sum: \"$items.quantity\" },\n total_revenue: { $sum: \"$items.total_price\" },\n order_count: { $sum: 1 },\n avg_order_value: { $avg: \"$items.total_price\" }\n }\n },\n {\n $group: {\n _id: \"$_id.product_id\",\n regions: {\n $push: {\n region: \"$_id.customer_region\",\n quantity: \"$total_quantity\",\n revenue: \"$total_revenue\"\n }\n },\n total_quantity: { $sum: \"$total_quantity\" },\n total_revenue: { $sum: \"$total_revenue\" }\n }\n },\n {\n $sort: { total_revenue: -1 }\n }\n])","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Indexing Strategies","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"PostgreSQL Indexes","type":"text"}]},{"type":"paragraph","content":[{"text":"B-tree Indexes (Default):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Single column index\nCREATE INDEX idx_users_email ON users(email);\n\n-- Composite index (order matters!)\nCREATE INDEX idx_posts_author_published\nON posts(author_id, published_at);\n\n-- Query can use index:\n-- SELECT * FROM posts WHERE author_id = 123 ORDER BY published_at;\n-- SELECT * FROM posts WHERE author_id = 123 AND published_at > '2025-01-01';\n\n-- Query CANNOT fully use index:\n-- SELECT * FROM posts WHERE published_at > '2025-01-01'; (only uses first column)","type":"text"}]},{"type":"paragraph","content":[{"text":"Partial Indexes:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Index only active users\nCREATE INDEX idx_active_users\nON users(username)\nWHERE active = true;\n\n-- Index only recent orders\nCREATE INDEX idx_recent_orders\nON orders(created_at, status)\nWHERE created_at > '2024-01-01';\n\n-- Benefits: Smaller index size, faster queries on filtered data","type":"text"}]},{"type":"paragraph","content":[{"text":"Expression Indexes:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Index on lowercase email for case-insensitive search\nCREATE INDEX idx_users_email_lower\nON users(LOWER(email));\n\n-- Query that uses this index:\nSELECT * FROM users WHERE LOWER(email) = '[email protected]';\n\n-- Index on JSONB field extraction\nCREATE INDEX idx_metadata_tags\nON products((metadata->>'category'));","type":"text"}]},{"type":"paragraph","content":[{"text":"Full-Text Search Indexes:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Add tsvector column for full-text search\nALTER TABLE articles\nADD COLUMN tsv_content tsvector;\n\n-- Populate tsvector column\nUPDATE articles\nSET tsv_content = to_tsvector('english', title || ' ' || content);\n\n-- Create GIN index for full-text search\nCREATE INDEX idx_articles_tsv ON articles USING GIN(tsv_content);\n\n-- Full-text search query\nSELECT title, ts_rank(tsv_content, query) as rank\nFROM articles, to_tsquery('english', 'database & design') query\nWHERE tsv_content @@ query\nORDER BY rank DESC;\n\n-- Trigger to auto-update tsvector\nCREATE TRIGGER articles_tsv_update\nBEFORE INSERT OR UPDATE ON articles\nFOR EACH ROW EXECUTE FUNCTION\ntsvector_update_trigger(tsv_content, 'pg_catalog.english', title, content);","type":"text"}]},{"type":"paragraph","content":[{"text":"JSONB Indexes:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- GIN index for JSONB containment queries\nCREATE INDEX idx_products_metadata\nON products USING GIN(metadata);\n\n-- Queries that use this index:\nSELECT * FROM products WHERE metadata @> '{\"color\": \"blue\"}';\nSELECT * FROM products WHERE metadata ? 'size';\n\n-- Index on specific JSONB path\nCREATE INDEX idx_products_category\nON products((metadata->>'category'));","type":"text"}]},{"type":"paragraph","content":[{"text":"Index Monitoring:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Find unused indexes\nSELECT\n schemaname,\n tablename,\n indexname,\n idx_scan,\n idx_tup_read,\n idx_tup_fetch,\n pg_size_pretty(pg_relation_size(indexrelid)) as index_size\nFROM pg_stat_user_indexes\nWHERE idx_scan = 0\nORDER BY pg_relation_size(indexrelid) DESC;\n\n-- Check index usage\nSELECT\n relname as table_name,\n indexrelname as index_name,\n idx_scan as times_used,\n idx_tup_read as tuples_read,\n idx_tup_fetch as tuples_fetched\nFROM pg_stat_user_indexes\nORDER BY idx_scan ASC;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"MongoDB Indexes","type":"text"}]},{"type":"paragraph","content":[{"text":"Single Field Indexes:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Create index on single field\ndb.users.createIndex({ email: 1 }) // 1 = ascending, -1 = descending\n\n// Unique index\ndb.users.createIndex({ username: 1 }, { unique: true })\n\n// Sparse index (only index documents with the field)\ndb.users.createIndex({ phone_number: 1 }, { sparse: true })","type":"text"}]},{"type":"paragraph","content":[{"text":"Compound Indexes:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Index on multiple fields (order matters!)\ndb.posts.createIndex({ author_id: 1, published_at: -1 })\n\n// Efficient queries:\n// - { author_id: \"123\" }\n// - { author_id: \"123\", published_at: { $gte: ... } }\n// - { author_id: \"123\" } with sort by published_at\n\n// Inefficient:\n// - { published_at: { $gte: ... } } alone (doesn't use index efficiently)\n\n// ESR Rule: Equality, Sort, Range\n// Best compound index order:\n// 1. Equality filters first\n// 2. Sort fields second\n// 3. Range filters last\ndb.orders.createIndex({\n status: 1, // Equality\n created_at: -1, // Sort\n total_amount: 1 // Range\n})","type":"text"}]},{"type":"paragraph","content":[{"text":"Multikey Indexes (Array Fields):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Index on array field\ndb.posts.createIndex({ tags: 1 })\n\n// Document with array\n{\n _id: ObjectId(\"...\"),\n title: \"Database Design\",\n tags: [\"database\", \"mongodb\", \"schema\"]\n}\n\n// Query that uses multikey index\ndb.posts.find({ tags: \"mongodb\" })\ndb.posts.find({ tags: { $in: [\"database\", \"nosql\"] } })\n\n// Compound multikey index (max one array field)\ndb.posts.createIndex({ tags: 1, published_at: -1 }) // Valid\n// db.posts.createIndex({ tags: 1, categories: 1 }) // Invalid if both are arrays","type":"text"}]},{"type":"paragraph","content":[{"text":"Text Indexes:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Create text index for full-text search\ndb.articles.createIndex({\n title: \"text\",\n content: \"text\"\n})\n\n// Text search query\ndb.articles.find({\n $text: { $search: \"database design patterns\" }\n})\n\n// Search with relevance score\ndb.articles.find(\n { $text: { $search: \"database design\" } },\n { score: { $meta: \"textScore\" } }\n).sort({ score: { $meta: \"textScore\" } })\n\n// Weighted text index (prioritize title over content)\ndb.articles.createIndex(\n { title: \"text\", content: \"text\" },\n { weights: { title: 10, content: 5 } }\n)","type":"text"}]},{"type":"paragraph","content":[{"text":"Geospatial Indexes:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// 2dsphere index for geographic queries\ndb.locations.createIndex({ coordinates: \"2dsphere\" })\n\n// Document format\n{\n name: \"Coffee Shop\",\n coordinates: {\n type: \"Point\",\n coordinates: [-122.4194, 37.7749] // [longitude, latitude]\n }\n}\n\n// Find locations near a point\ndb.locations.find({\n coordinates: {\n $near: {\n $geometry: {\n type: \"Point\",\n coordinates: [-122.4194, 37.7749]\n },\n $maxDistance: 1000 // meters\n }\n }\n})","type":"text"}]},{"type":"paragraph","content":[{"text":"Index Properties:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// TTL Index (auto-delete documents after time)\ndb.sessions.createIndex(\n { created_at: 1 },\n { expireAfterSeconds: 3600 } // 1 hour\n)\n\n// Partial Index (index subset of documents)\ndb.orders.createIndex(\n { status: 1, created_at: -1 },\n { partialFilterExpression: { status: { $eq: \"pending\" } } }\n)\n\n// Case-insensitive index\ndb.users.createIndex(\n { email: 1 },\n { collation: { locale: \"en\", strength: 2 } }\n)\n\n// Background index creation (doesn't block operations)\ndb.large_collection.createIndex(\n { field: 1 },\n { background: true }\n)","type":"text"}]},{"type":"paragraph","content":[{"text":"Index Analysis:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Explain query execution\ndb.posts.find({ author_id: \"123\" }).explain(\"executionStats\")\n\n// Check index usage\ndb.posts.aggregate([\n { $indexStats: {} }\n])\n\n// List all indexes on collection\ndb.posts.getIndexes()\n\n// Drop unused index\ndb.posts.dropIndex(\"index_name\")","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Transactions","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"PostgreSQL Transaction Management","type":"text"}]},{"type":"paragraph","content":[{"text":"Basic Transactions:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Explicit transaction\nBEGIN;\n\nUPDATE accounts SET balance = balance - 100 WHERE id = 1;\nUPDATE accounts SET balance = balance + 100 WHERE id = 2;\n\nCOMMIT;\n-- or ROLLBACK; to cancel changes","type":"text"}]},{"type":"paragraph","content":[{"text":"Savepoints (Partial Rollback):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"BEGIN;\n\nUPDATE inventory SET quantity = quantity - 10 WHERE product_id = 'PROD-123';\n\nSAVEPOINT before_audit;\n\nINSERT INTO audit_log (action, details) VALUES ('inventory_update', '...');\n-- Oops, error in audit log\n\nROLLBACK TO SAVEPOINT before_audit;\n-- Inventory update preserved, audit insert rolled back\n\n-- Fix and retry\nINSERT INTO audit_log (action, details) VALUES ('inventory_update', 'correct details');\n\nCOMMIT;","type":"text"}]},{"type":"paragraph","content":[{"text":"Isolation Levels:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Read Uncommitted (not supported in PostgreSQL, defaults to Read Committed)\n-- Read Committed (default) - sees only committed data\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED;\n\n-- Repeatable Read - sees snapshot at transaction start\nBEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;\nSELECT * FROM accounts WHERE id = 1; -- Returns balance 1000\n-- Another transaction updates balance to 1500 and commits\nSELECT * FROM accounts WHERE id = 1; -- Still returns 1000 (repeatable read)\nCOMMIT;\n\n-- Serializable - strictest isolation, prevents all anomalies\nBEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;\n-- If concurrent transactions would violate serializability, one aborts\nCOMMIT;","type":"text"}]},{"type":"paragraph","content":[{"text":"Advisory Locks (Application-Level Locking):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Exclusive lock on arbitrary number\nSELECT pg_advisory_lock(12345);\n-- ... perform critical operation ...\nSELECT pg_advisory_unlock(12345);\n\n-- Try lock (non-blocking)\nSELECT pg_try_advisory_lock(12345); -- Returns true if acquired, false otherwise\n\n-- Session-level advisory lock (auto-released on disconnect)\nSELECT pg_advisory_lock(user_id);","type":"text"}]},{"type":"paragraph","content":[{"text":"Row-Level Locking:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- SELECT FOR UPDATE - lock rows for update\nBEGIN;\n\nSELECT * FROM products\nWHERE id = 123\nFOR UPDATE; -- Locks this row\n\nUPDATE products SET quantity = quantity - 1 WHERE id = 123;\n\nCOMMIT;\n\n-- SELECT FOR SHARE - shared lock (allows other reads, blocks writes)\nSELECT * FROM products WHERE id = 123 FOR SHARE;\n\n-- SKIP LOCKED - skip locked rows instead of waiting\nSELECT * FROM queue\nWHERE processed = false\nORDER BY priority\nLIMIT 10\nFOR UPDATE SKIP LOCKED;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"MongoDB Transactions","type":"text"}]},{"type":"paragraph","content":[{"text":"Multi-Document Transactions:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Transactions require replica set or sharded cluster\nconst session = db.getMongo().startSession()\nsession.startTransaction()\n\ntry {\n const accountsCol = session.getDatabase(\"mydb\").accounts\n\n // Debit account\n accountsCol.updateOne(\n { _id: \"account1\" },\n { $inc: { balance: -100 } },\n { session }\n )\n\n // Credit account\n accountsCol.updateOne(\n { _id: \"account2\" },\n { $inc: { balance: 100 } },\n { session }\n )\n\n // Commit transaction\n session.commitTransaction()\n} catch (error) {\n // Abort on error\n session.abortTransaction()\n throw error\n} finally {\n session.endSession()\n}","type":"text"}]},{"type":"paragraph","content":[{"text":"Read and Write Concerns:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Write Concern: Acknowledgment level\ndb.orders.insertOne(\n { customer_id: \"123\", items: [...] },\n {\n writeConcern: {\n w: \"majority\", // Wait for majority of replica set\n j: true, // Wait for journal write\n wtimeout: 5000 // Timeout after 5 seconds\n }\n }\n)\n\n// Read Concern: Data consistency level\ndb.orders.find(\n { status: \"pending\" }\n).readConcern(\"majority\") // Only return data acknowledged by majority\n\n// Read Preference: Which replica to read from\ndb.orders.find({ ... }).readPref(\"secondary\") // Read from secondary replica","type":"text"}]},{"type":"paragraph","content":[{"text":"Atomic Operations (Single Document):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Single document updates are atomic by default\ndb.counters.updateOne(\n { _id: \"page_views\" },\n {\n $inc: { count: 1 },\n $set: { last_updated: new Date() }\n }\n)\n\n// Atomic array operations\ndb.posts.updateOne(\n { _id: ObjectId(\"...\") },\n {\n $push: {\n comments: {\n $each: [{ author: \"John\", text: \"Great!\" }],\n $position: 0 // Insert at beginning\n }\n }\n }\n)\n\n// Find and modify (atomic read-modify-write)\ndb.queue.findOneAndUpdate(\n { status: \"pending\" },\n { $set: { status: \"processing\", processor_id: \"worker-1\" } },\n {\n sort: { priority: -1 },\n returnDocument: \"after\" // Return updated document\n }\n)","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Replication","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"PostgreSQL Replication","type":"text"}]},{"type":"paragraph","content":[{"text":"Streaming Replication (Primary-Standby):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Primary server configuration (postgresql.conf)\nwal_level = replica\nmax_wal_senders = 10\nwal_keep_size = '1GB'\nhot_standby = on\n\n-- Create replication user\nCREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';\n\n-- pg_hba.conf on primary\nhost replication replicator standby_ip/32 md5\n\n-- Standby server (recovery.conf or postgresql.auto.conf)\nprimary_conninfo = 'host=primary_ip port=5432 user=replicator password=...'\nrestore_command = 'cp /var/lib/postgresql/archive/%f %p'","type":"text"}]},{"type":"paragraph","content":[{"text":"Logical Replication (Selective Replication):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- On publisher (source)\nCREATE PUBLICATION my_publication FOR TABLE users, posts;\n-- or FOR ALL TABLES;\n\n-- On subscriber (destination)\nCREATE SUBSCRIPTION my_subscription\nCONNECTION 'host=publisher_ip dbname=mydb user=replicator password=...'\nPUBLICATION my_publication;\n\n-- Monitor replication\nSELECT * FROM pg_stat_replication;\nSELECT * FROM pg_replication_slots;","type":"text"}]},{"type":"paragraph","content":[{"text":"Failover and Promotion:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Promote standby to primary\npg_ctl promote -D /var/lib/postgresql/data\n\n-- Check replication lag\nSELECT\n client_addr,\n state,\n sent_lsn,\n write_lsn,\n flush_lsn,\n replay_lsn,\n sync_state,\n pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes\nFROM pg_stat_replication;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"MongoDB Replication","type":"text"}]},{"type":"paragraph","content":[{"text":"Replica Set Configuration:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Initialize replica set\nrs.initiate({\n _id: \"myReplicaSet\",\n members: [\n { _id: 0, host: \"mongodb1.example.com:27017\", priority: 2 },\n { _id: 1, host: \"mongodb2.example.com:27017\", priority: 1 },\n { _id: 2, host: \"mongodb3.example.com:27017\", priority: 1 }\n ]\n})\n\n// Add member to existing replica set\nrs.add(\"mongodb4.example.com:27017\")\n\n// Remove member\nrs.remove(\"mongodb4.example.com:27017\")\n\n// Check replica set status\nrs.status()\n\n// Check replication lag\nrs.printSecondaryReplicationInfo()","type":"text"}]},{"type":"paragraph","content":[{"text":"Replica Set Roles:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Priority 0 member (cannot become primary)\nrs.add({\n host: \"analytics.example.com:27017\",\n priority: 0,\n hidden: true // Hidden from application drivers\n})\n\n// Arbiter (voting only, no data)\nrs.addArb(\"arbiter.example.com:27017\")\n\n// Delayed member (disaster recovery)\nrs.add({\n host: \"delayed.example.com:27017\",\n priority: 0,\n hidden: true,\n slaveDelay: 3600 // 1 hour behind\n})","type":"text"}]},{"type":"paragraph","content":[{"text":"Read Preference Configuration:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Application connection with read preference\nconst client = new MongoClient(uri, {\n readPreference: \"secondaryPreferred\", // Try secondary, fallback to primary\n readConcernLevel: \"majority\"\n})\n\n// Read Preference Modes:\n// - primary (default): Read from primary only\n// - primaryPreferred: Primary if available, else secondary\n// - secondary: Read from secondary only\n// - secondaryPreferred: Secondary if available, else primary\n// - nearest: Read from nearest member (lowest latency)","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Sharding","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"MongoDB Sharding Architecture","type":"text"}]},{"type":"paragraph","content":[{"text":"Shard Key Selection:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Good shard key characteristics:\n// 1. High cardinality (many distinct values)\n// 2. Even distribution\n// 3. Query isolation (queries target specific shards)\n\n// Example: User-based application\nsh.shardCollection(\"mydb.users\", { user_id: \"hashed\" })\n\n// Hashed shard key: Even distribution, random data location\nsh.shardCollection(\"mydb.events\", { event_id: \"hashed\" })\n\n// Range-based shard key: Ordered data, good for range queries\nsh.shardCollection(\"mydb.logs\", { timestamp: 1, server_id: 1 })\n\n// Compound shard key\nsh.shardCollection(\"mydb.orders\", {\n customer_region: 1, // Coarse grouping\n order_date: 1 // Fine grouping\n})","type":"text"}]},{"type":"paragraph","content":[{"text":"Sharding Setup:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// 1. Start config servers (replica set)\nmongod --configsvr --replSet configRS --port 27019\n\n// 2. Initialize config server replica set\nrs.initiate({\n _id: \"configRS\",\n configsvr: true,\n members: [\n { _id: 0, host: \"cfg1.example.com:27019\" },\n { _id: 1, host: \"cfg2.example.com:27019\" },\n { _id: 2, host: \"cfg3.example.com:27019\" }\n ]\n})\n\n// 3. Start shard servers (each is a replica set)\nmongod --shardsvr --replSet shard1RS --port 27018\n\n// 4. Start mongos (query router)\nmongos --configdb configRS/cfg1.example.com:27019,cfg2.example.com:27019\n\n// 5. Add shards to cluster\nsh.addShard(\"shard1RS/shard1-a.example.com:27018\")\nsh.addShard(\"shard2RS/shard2-a.example.com:27018\")\n\n// 6. Enable sharding on database\nsh.enableSharding(\"mydb\")\n\n// 7. Shard collections\nsh.shardCollection(\"mydb.users\", { user_id: \"hashed\" })","type":"text"}]},{"type":"paragraph","content":[{"text":"Query Targeting:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Targeted query (includes shard key)\ndb.users.find({ user_id: \"12345\" })\n// Routes to single shard\n\n// Scatter-gather query (no shard key)\ndb.users.find({ email: \"[email protected]\" })\n// Queries all shards, merges results\n\n// Check query targeting\ndb.users.find({ user_id: \"12345\" }).explain()\n// Look for \"SINGLE_SHARD\" vs \"ALL_SHARDS\"","type":"text"}]},{"type":"paragraph","content":[{"text":"Zone Sharding (Geographic Distribution):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Define zones for geographic sharding\nsh.addShardToZone(\"shard1\", \"US\")\nsh.addShardToZone(\"shard2\", \"EU\")\n\n// Define zone ranges\nsh.updateZoneKeyRange(\n \"mydb.users\",\n { region: \"US\", user_id: MinKey },\n { region: \"US\", user_id: MaxKey },\n \"US\"\n)\n\nsh.updateZoneKeyRange(\n \"mydb.users\",\n { region: \"EU\", user_id: MinKey },\n { region: \"EU\", user_id: MaxKey },\n \"EU\"\n)\n\n// Shard collection with zone-aware key\nsh.shardCollection(\"mydb.users\", { region: 1, user_id: 1 })","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"PostgreSQL Horizontal Partitioning","type":"text"}]},{"type":"paragraph","content":[{"text":"Declarative Partitioning:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Range partitioning\nCREATE TABLE logs (\n id BIGSERIAL,\n log_time TIMESTAMP NOT NULL,\n message TEXT,\n level VARCHAR(10)\n) PARTITION BY RANGE (log_time);\n\n-- Create partitions\nCREATE TABLE logs_2025_01 PARTITION OF logs\nFOR VALUES FROM ('2025-01-01') TO ('2025-02-01');\n\nCREATE TABLE logs_2025_02 PARTITION OF logs\nFOR VALUES FROM ('2025-02-01') TO ('2025-03-01');\n\n-- List partitioning\nCREATE TABLE customers (\n id SERIAL,\n name VARCHAR(255),\n region VARCHAR(50)\n) PARTITION BY LIST (region);\n\nCREATE TABLE customers_us PARTITION OF customers\nFOR VALUES IN ('US', 'CA', 'MX');\n\nCREATE TABLE customers_eu PARTITION OF customers\nFOR VALUES IN ('UK', 'DE', 'FR', 'IT');\n\n-- Hash partitioning\nCREATE TABLE events (\n id BIGSERIAL,\n event_type VARCHAR(50),\n data JSONB\n) PARTITION BY HASH (id);\n\nCREATE TABLE events_0 PARTITION OF events\nFOR VALUES WITH (MODULUS 4, REMAINDER 0);\n\nCREATE TABLE events_1 PARTITION OF events\nFOR VALUES WITH (MODULUS 4, REMAINDER 1);\n-- ... events_2 and events_3","type":"text"}]},{"type":"paragraph","content":[{"text":"Partition Pruning (Query Optimization):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Query automatically uses only relevant partition\nSELECT * FROM logs\nWHERE log_time BETWEEN '2025-01-15' AND '2025-01-20';\n-- Only scans logs_2025_01 partition\n\n-- Check query plan\nEXPLAIN SELECT * FROM logs WHERE log_time > '2025-01-01';\n-- Shows which partitions are scanned","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Performance Tuning","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Query Optimization Techniques","type":"text"}]},{"type":"paragraph","content":[{"text":"PostgreSQL Query Analysis:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Basic explain\nEXPLAIN SELECT * FROM users WHERE email = '[email protected]';\n\n-- Analyze with actual execution statistics\nEXPLAIN ANALYZE\nSELECT u.username, COUNT(p.id) as post_count\nFROM users u\nLEFT JOIN posts p ON u.id = p.user_id\nWHERE u.active = true\nGROUP BY u.id, u.username\nORDER BY post_count DESC\nLIMIT 10;\n\n-- Identify slow queries\nSELECT\n query,\n calls,\n total_exec_time,\n mean_exec_time,\n max_exec_time\nFROM pg_stat_statements\nORDER BY total_exec_time DESC\nLIMIT 20;\n\n-- Table statistics\nANALYZE users; -- Update query planner statistics\n\n-- Vacuum and analyze\nVACUUM ANALYZE posts; -- Reclaim space and update stats","type":"text"}]},{"type":"paragraph","content":[{"text":"Common Query Patterns:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Avoid SELECT * (retrieve only needed columns)\n-- BAD\nSELECT * FROM users WHERE id = 123;\n\n-- GOOD\nSELECT id, username, email FROM users WHERE id = 123;\n\n-- Use EXISTS instead of IN for large subqueries\n-- BAD\nSELECT * FROM posts WHERE author_id IN (\n SELECT id FROM users WHERE active = true\n);\n\n-- GOOD\nSELECT * FROM posts p WHERE EXISTS (\n SELECT 1 FROM users u\n WHERE u.id = p.author_id AND u.active = true\n);\n\n-- Use JOINs instead of multiple queries\n-- BAD (N+1 query problem)\n-- SELECT * FROM posts;\n-- Then for each post: SELECT * FROM users WHERE id = post.author_id;\n\n-- GOOD\nSELECT p.*, u.username, u.email\nFROM posts p\nJOIN users u ON p.author_id = u.id;\n\n-- Window functions instead of self-joins\n-- Calculate running total\nSELECT\n order_date,\n amount,\n SUM(amount) OVER (ORDER BY order_date) as running_total\nFROM orders;\n\n-- Rank within groups\nSELECT\n category,\n product_name,\n sales,\n RANK() OVER (PARTITION BY category ORDER BY sales DESC) as rank_in_category\nFROM products;","type":"text"}]},{"type":"paragraph","content":[{"text":"MongoDB Query Optimization:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Use projection to limit returned fields\n// BAD\ndb.users.find({ active: true })\n\n// GOOD\ndb.users.find(\n { active: true },\n { username: 1, email: 1, _id: 0 }\n)\n\n// Use covered queries (index covers all fields)\ndb.users.createIndex({ username: 1, email: 1 })\ndb.users.find(\n { username: \"john_doe\" },\n { username: 1, email: 1, _id: 0 }\n) // Entire query served from index\n\n// Avoid negation operators\n// BAD (cannot use index efficiently)\ndb.products.find({ status: { $ne: \"discontinued\" } })\n\n// GOOD\ndb.products.find({ status: { $in: [\"active\", \"pending\", \"sold\"] } })\n\n// Use $lookup sparingly (expensive operation)\n// Consider embedding data instead if appropriate\n\n// Aggregation optimization: Filter early\n// BAD\ndb.orders.aggregate([\n { $lookup: { ... } }, // Expensive join\n { $match: { status: \"completed\" } } // Filter after join\n])\n\n// GOOD\ndb.orders.aggregate([\n { $match: { status: \"completed\" } }, // Filter first\n { $lookup: { ... } } // Join fewer documents\n])","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Connection Pooling","type":"text"}]},{"type":"paragraph","content":[{"text":"PostgreSQL Connection Pooling:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Using node-postgres (pg) with pool\nconst { Pool } = require('pg')\n\nconst pool = new Pool({\n host: 'localhost',\n port: 5432,\n database: 'mydb',\n user: 'dbuser',\n password: 'secret',\n max: 20, // Maximum pool size\n idleTimeoutMillis: 30000,\n connectionTimeoutMillis: 2000\n})\n\n// Execute query\nconst result = await pool.query('SELECT * FROM users WHERE id = $1', [123])\n\n// Use PgBouncer for server-side pooling\n// pgbouncer.ini\n// [databases]\n// mydb = host=localhost port=5432 dbname=mydb\n//\n// [pgbouncer]\n// pool_mode = transaction\n// max_client_conn = 1000\n// default_pool_size = 25","type":"text"}]},{"type":"paragraph","content":[{"text":"MongoDB Connection Pooling:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// MongoClient automatically manages connection pool\nconst { MongoClient } = require('mongodb')\n\nconst client = new MongoClient(uri, {\n maxPoolSize: 50, // Max connections\n minPoolSize: 10, // Min connections\n maxIdleTimeMS: 30000, // Close idle connections\n waitQueueTimeoutMS: 5000 // Wait for available connection\n})\n\nawait client.connect()\nconst db = client.db('mydb')\n// Connection automatically returned to pool after use","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Best Practices","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"PostgreSQL Best Practices","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Schema Design","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Normalize for data integrity, denormalize for performance","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use appropriate data types (avoid TEXT for short strings)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Define NOT NULL constraints where appropriate","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use SERIAL or UUID for primary keys consistently","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Indexing","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Index foreign keys for JOIN performance","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Create indexes on frequently filtered/sorted columns","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use partial indexes for selective queries","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Monitor and remove unused indexes","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Keep composite index column count reasonable (typically ≤ 3-4)","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Query Performance","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use EXPLAIN ANALYZE to understand query plans","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Avoid SELECT * in application code","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use prepared statements to prevent SQL injection","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Limit result sets with LIMIT","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use connection pooling","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Maintenance","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Run VACUUM regularly (or enable autovacuum)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Update statistics with ANALYZE","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Monitor slow query log","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Set appropriate autovacuum thresholds","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Regular backup with pg_dump or WAL archiving","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Security","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use SSL/TLS for connections","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Implement row-level security for multi-tenant apps","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Grant minimum necessary privileges","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use parameterized queries","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Regular security updates","type":"text"}]}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"MongoDB Best Practices","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Schema Design","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Embed related data that is accessed together","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Reference data that is large or rarely accessed","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use polymorphic pattern for varied schemas","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Limit document size to reasonable bounds (\u003c 1-2 MB typically)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Design for your query patterns","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Indexing","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Index on fields used in queries and sorts","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use compound indexes with ESR rule (Equality, Sort, Range)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Create text indexes for full-text search","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Monitor index usage with $indexStats","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Avoid too many indexes (write performance impact)","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Query Performance","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use projection to limit returned fields","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Create covered queries when possible","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Filter early in aggregation pipelines","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Avoid $lookup when embedding is appropriate","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use explain() to verify index usage","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Scalability","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Choose appropriate shard key (high cardinality, even distribution)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use replica sets for high availability","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Configure appropriate read/write concerns","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Monitor chunk distribution in sharded clusters","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use zones for geographic distribution","type":"text"}]}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Operations","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Enable authentication and authorization","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use TLS for client connections","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Regular backups (mongodump or filesystem snapshots)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Monitor with MongoDB Atlas, Ops Manager, or custom tools","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Keep MongoDB version updated","type":"text"}]}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Data Modeling Decision Framework","type":"text"}]},{"type":"paragraph","content":[{"text":"Choose PostgreSQL when:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Strong ACID guarantees required (financial transactions)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Complex relationships with many JOINs","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Data structure is well-defined and stable","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Need for advanced SQL features (window functions, CTEs, stored procedures)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Compliance requirements demand strict consistency","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Choose MongoDB when:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Schema flexibility needed (rapid development, evolving requirements)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Horizontal scalability is priority (sharding required)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Document-oriented data (JSON/BSON native format)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Hierarchical or nested data structures","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"High write throughput with eventual consistency acceptable","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Hybrid Approach:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use both databases for different parts of application","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"PostgreSQL for transactional data (orders, payments)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"MongoDB for catalog, logs, user sessions","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Synchronize critical data between systems","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Common Patterns and Anti-Patterns","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"PostgreSQL Anti-Patterns","type":"text"}]},{"type":"paragraph","content":[{"text":"❌ ","type":"text"},{"text":"Storing JSON when relational fits better","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- BAD: Using JSONB for structured, queryable data\nCREATE TABLE users (\n id SERIAL PRIMARY KEY,\n data JSONB -- { name, email, address: { street, city, state } }\n);\n\n-- GOOD: Proper normalization\nCREATE TABLE users (\n id SERIAL PRIMARY KEY,\n name VARCHAR(255),\n email VARCHAR(255)\n);\n\nCREATE TABLE addresses (\n id SERIAL PRIMARY KEY,\n user_id INTEGER REFERENCES users(id),\n street VARCHAR(255),\n city VARCHAR(100),\n state VARCHAR(50)\n);","type":"text"}]},{"type":"paragraph","content":[{"text":"❌ ","type":"text"},{"text":"Over-indexing","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- BAD: Index on every column \"just in case\"\nCREATE INDEX idx1 ON users(username);\nCREATE INDEX idx2 ON users(email);\nCREATE INDEX idx3 ON users(created_at);\nCREATE INDEX idx4 ON users(updated_at);\nCREATE INDEX idx5 ON users(active);\n-- Result: Slow writes, large database size\n\n-- GOOD: Index based on actual query patterns\nCREATE INDEX idx_users_email ON users(email); -- Login queries\nCREATE INDEX idx_active_users_created ON users(created_at) WHERE active = true; -- Partial","type":"text"}]},{"type":"paragraph","content":[{"text":"❌ ","type":"text"},{"text":"N+1 Query Problem","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- BAD: Multiple queries in loop\nSELECT * FROM posts; -- Returns 100 posts\n-- Then for each post:\nSELECT * FROM users WHERE id = ?; -- 100 additional queries!\n\n-- GOOD: Single query with JOIN\nSELECT p.*, u.username, u.email\nFROM posts p\nJOIN users u ON p.author_id = u.id;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"MongoDB Anti-Patterns","type":"text"}]},{"type":"paragraph","content":[{"text":"❌ ","type":"text"},{"text":"Massive arrays in documents","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// BAD: Unbounded array growth\n{\n _id: ObjectId(\"...\"),\n username: \"popular_user\",\n followers: [\n ObjectId(\"follower1\"),\n ObjectId(\"follower2\"),\n // ... 100,000+ follower IDs\n // Document exceeds 16MB limit!\n ]\n}\n\n// GOOD: Separate collection with references\n// users collection\n{ _id: ObjectId(\"...\"), username: \"popular_user\" }\n\n// followers collection\n{ _id: ObjectId(\"...\"), user_id: ObjectId(\"...\"), follower_id: ObjectId(\"...\") }\ndb.followers.createIndex({ user_id: 1, follower_id: 1 })","type":"text"}]},{"type":"paragraph","content":[{"text":"❌ ","type":"text"},{"text":"Poor shard key selection","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// BAD: Monotonically increasing shard key\nsh.shardCollection(\"mydb.events\", { _id: 1 })\n// All writes go to same shard (highest _id range)\n\n// BAD: Low cardinality shard key\nsh.shardCollection(\"mydb.users\", { country: 1 })\n// Most users in few countries = uneven distribution\n\n// GOOD: Hashed _id or compound key\nsh.shardCollection(\"mydb.events\", { _id: \"hashed\" }) // Even distribution\nsh.shardCollection(\"mydb.users\", { country: 1, user_id: 1 }) // Compound","type":"text"}]},{"type":"paragraph","content":[{"text":"❌ ","type":"text"},{"text":"Ignoring indexes on embedded documents","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Document structure\n{\n username: \"john_doe\",\n profile: {\n email: \"[email protected]\",\n age: 30,\n city: \"San Francisco\"\n }\n}\n\n// Query on embedded field\ndb.users.find({ \"profile.email\": \"[email protected]\" })\n\n// MISSING: Index on embedded field\ndb.users.createIndex({ \"profile.email\": 1 })","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Troubleshooting Guide","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"PostgreSQL Issues","type":"text"}]},{"type":"paragraph","content":[{"text":"Slow Queries:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Enable slow query logging (postgresql.conf)\n-- log_min_duration_statement = 1000 # Log queries > 1 second\n\n-- Find slow queries\nSELECT\n query,\n calls,\n total_exec_time / calls as avg_time_ms,\n rows / calls as avg_rows\nFROM pg_stat_statements\nWHERE calls > 100\nORDER BY total_exec_time DESC\nLIMIT 20;\n\n-- Analyze specific slow query\nEXPLAIN (ANALYZE, BUFFERS, VERBOSE)\nSELECT ... FROM ... WHERE ...;","type":"text"}]},{"type":"paragraph","content":[{"text":"High CPU Usage:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Check running queries\nSELECT\n pid,\n now() - query_start as duration,\n state,\n query\nFROM pg_stat_activity\nWHERE state != 'idle'\nORDER BY duration DESC;\n\n-- Terminate long-running query\nSELECT pg_terminate_backend(pid);","type":"text"}]},{"type":"paragraph","content":[{"text":"Lock Contention:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- View locks\nSELECT\n locktype,\n relation::regclass,\n mode,\n granted,\n pid\nFROM pg_locks\nWHERE NOT granted;\n\n-- Find blocking queries\nSELECT\n blocked_locks.pid AS blocked_pid,\n blocking_locks.pid AS blocking_pid,\n blocked_activity.query AS blocked_query,\n blocking_activity.query AS blocking_query\nFROM pg_locks blocked_locks\nJOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid\nJOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype\nJOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid\nWHERE NOT blocked_locks.granted AND blocking_locks.granted;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"MongoDB Issues","type":"text"}]},{"type":"paragraph","content":[{"text":"Slow Queries:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Enable profiling\ndb.setProfilingLevel(1, { slowms: 100 }) // Log queries > 100ms\n\n// View slow queries\ndb.system.profile.find().sort({ ts: -1 }).limit(10)\n\n// Analyze query performance\ndb.collection.find({ ... }).explain(\"executionStats\")\n// Check: totalDocsExamined vs nReturned (should be close)\n// Check: executionTimeMillis\n// Check: indexName (should show index usage)","type":"text"}]},{"type":"paragraph","content":[{"text":"Replication Lag:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Check lag on secondary\nrs.printSecondaryReplicationInfo()\n\n// Check oplog size\ndb.getReplicationInfo()\n\n// Increase oplog size if needed\ndb.adminCommand({ replSetResizeOplog: 1, size: 16384 }) // 16GB","type":"text"}]},{"type":"paragraph","content":[{"text":"Sharding Issues:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"javascript"},"content":[{"text":"// Check chunk distribution\nsh.status()\n\n// Check balancer status\nsh.getBalancerState()\nsh.isBalancerRunning()\n\n// Balance specific collection\nsh.enableBalancing(\"mydb.mycollection\")\n\n// Check for jumbo chunks\ndb.chunks.find({ jumbo: true })","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Resources","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"PostgreSQL Resources","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Official Documentation: https://www.postgresql.org/docs/","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"PostgreSQL Wiki: https://wiki.postgresql.org/","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Performance Tuning: https://wiki.postgresql.org/wiki/Performance_Optimization","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Explain Visualizer: https://explain.dalibo.com/","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"pg_stat_statements Extension: Essential for query analysis","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"MongoDB Resources","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Official Documentation: https://docs.mongodb.com/","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"MongoDB University: Free courses and certification","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Aggregation Framework: https://docs.mongodb.com/manual/aggregation/","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Sharding Guide: https://docs.mongodb.com/manual/sharding/","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Schema Design Patterns: https://www.mongodb.com/blog/post/building-with-patterns-a-summary","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Books","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"PostgreSQL: \"PostgreSQL: Up and Running\" by Regina Obe & Leo Hsu","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"MongoDB: \"MongoDB: The Definitive Guide\" by Shannon Bradshaw, Eoin Brazil, Kristina Chodorow","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Skill Version","type":"text","marks":[{"type":"strong"}]},{"text":": 1.0.0 ","type":"text"},{"text":"Last Updated","type":"text","marks":[{"type":"strong"}]},{"text":": January 2025 ","type":"text"},{"text":"Skill Category","type":"text","marks":[{"type":"strong"}]},{"text":": Database Management, Data Architecture, Performance Optimization ","type":"text"},{"text":"Technologies","type":"text","marks":[{"type":"strong"}]},{"text":": PostgreSQL 16+, MongoDB 7+","type":"text"}]}]},"metadata":{"date":"2026-06-05","name":"database-management-patterns","tags":["database","postgresql","mongodb","sql","nosql","indexing","transactions","replication","sharding","performance","schema-design"],"tier":"tier-1","author":"@skillopedia","source":{"stars":57,"repo_name":"luxor-claude-marketplace","origin_url":"https://github.com/manutej/luxor-claude-marketplace/blob/HEAD/plugins/luxor-database-pro/skills/database-management-patterns/SKILL.md","repo_owner":"manutej","body_sha256":"03c0d502851a6140b71c798b88b7a869291bff886c196643bd0a957454e326f5","cluster_key":"dcdfe4697a54194ad15701f3bfc197ee834932a0c52e04c066e7f5d6365bf86b","clean_bundle":{"format":"clean-skill-bundle-v1","source":"manutej/luxor-claude-marketplace/plugins/luxor-database-pro/skills/database-management-patterns/SKILL.md","attachments":[{"id":"fb6d0484-dbb9-58da-92a0-17f05f035dea","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/fb6d0484-dbb9-58da-92a0-17f05f035dea/attachment.md","path":"EXAMPLES.md","size":59607,"sha256":"73cc3a3acc960cb782be3c69c956d66e783e59cdb21852060681b44567262bb0","contentType":"text/markdown; charset=utf-8"},{"id":"e0783a6f-d672-5dd3-8841-596b5a831625","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/e0783a6f-d672-5dd3-8841-596b5a831625/attachment.md","path":"README.md","size":16714,"sha256":"00f1a83822f12ccacc76b8d190e942b6b73ad31032c672523c796a16065c4a76","contentType":"text/markdown; charset=utf-8"}],"bundle_sha256":"b2d470140dee96446c3aa6bf7669fadde0c1772b400eb9b8434558a4dde02f6c","attachment_count":2,"text_attachments":2,"attachment_storage":"skillopedia-attachments-v1","binary_attachments":0,"excluded_attachments":[]},"cluster_size":1,"skill_md_path":"plugins/luxor-database-pro/skills/database-management-patterns/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"web-development","category_label":"Web"},"exact_dupes_collapsed_into_this":0},"version":"v1","category":"web-development","import_tag":"clean-skills-v1","description":"Comprehensive guide for database management patterns covering PostgreSQL and MongoDB including schema design, indexing, transactions, replication, and performance tuning"}},"renderedAt":1782979465509}

Database Management Patterns A comprehensive skill for mastering database management across SQL (PostgreSQL) and NoSQL (MongoDB) systems. This skill covers schema design, indexing strategies, transaction management, replication, sharding, and performance optimization for production-grade applications. When to Use This Skill Use this skill when: - Designing database schemas for new applications or refactoring existing ones - Choosing between SQL and NoSQL databases for your use case - Optimizing query performance with proper indexing strategies - Implementing data consistency with transactions…