Database Management Patterns

manutej/luxor-claude-marketplace/plugins/luxor-database-pro/skills/database-management-patterns

作者 manutej1861cc9720d5無授權條款62 個星標收錄於 2026年10月8日更新於 2026年10月8日儲存庫3 個月前更新

Comprehensive guide for database management patterns covering PostgreSQL and MongoDB including schema design, indexing, transactions, replication, and performance tuning

僅含說明Software Development

AI 產生的概覽

PostgreSQL 與 MongoDB 資料庫管理模式的參考指南，涵蓋結構設計、索引、交易、複寫與效能調校。

功能: 此技能是一份關於 PostgreSQL 與 MongoDB 資料庫管理模式的文字參考。內容說明結構設計、正規化與反正規化、條件約束、觸發程序、檢視表、文件建模、彙總管線、索引策略、交易、複寫、分片與效能調校，並附有 SQL 與 JavaScript 程式碼範例。它產出的是說明性指引，而非可執行的工具。
適用情境: 適用於設計或重構資料庫結構、在 SQL 與 NoSQL 之間做選擇，以及規劃索引、一致性與擴充策略的情境。也適合排查查詢效能問題，以及為高流量系統考量複寫、分片與容錯移轉。
執行需求: 不隨附任何指令碼或工具，僅為說明與參考資料。閱讀本身不需要套件、憑證或網路存取，但套用其中的模式需要可用的 PostgreSQL 或 MongoDB 執行個體。

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 and ACID guarantees
Scaling databases horizontally with sharding and replication
Managing high-traffic applications requiring distributed databases
Ensuring data integrity with constraints, triggers, and validation
Troubleshooting performance issues using explain plans and query analysis
Building fault-tolerant systems with replication and failover strategies
Working with complex data relationships (relational) or flexible schemas (document)

Core Concepts

Database Paradigms Comparison

Relational Databases (PostgreSQL)

Strengths:

ACID Transactions: Strong consistency guarantees
Complex Queries: JOIN operations, subqueries, CTEs
Data Integrity: Foreign keys, constraints, triggers
Normalized Data: Reduced redundancy, consistent updates
Mature Ecosystem: Rich tooling, extensions, community

Best For:

Financial systems requiring strict consistency
Complex relationships and data integrity requirements
Applications with structured, well-defined schemas
Systems requiring complex analytical queries
Multi-step transactions across multiple tables

Document Databases (MongoDB)

Strengths:

Flexible Schema: Easy schema evolution, polymorphic data
Horizontal Scalability: Built-in sharding support
JSON-Native: Natural fit for modern application development
Embedded Documents: Denormalized data for performance
Aggregation Framework: Powerful data processing pipeline

Best For:

Rapidly evolving applications with changing requirements
Content management systems with varied data structures
Real-time analytics and event logging
Mobile and web applications with JSON APIs
Hierarchical or nested data structures

ACID Properties

Atomicity: All operations in a transaction succeed or fail together Consistency: Transactions bring database from one valid state to another Isolation: Concurrent transactions don't interfere with each other Durability: Committed transactions survive system failures

CAP Theorem

In distributed systems, choose two of three:

Consistency: All nodes see the same data
Availability: System remains operational
Partition Tolerance: System continues despite network failures

PostgreSQL emphasizes CP (Consistency + Partition Tolerance) MongoDB can be configured for CP or AP depending on write/read concerns

PostgreSQL Patterns

Schema Design Fundamentals

Normalization Levels

First Normal Form (1NF)

Atomic values (no arrays or lists in columns)
Each row is unique (primary key exists)
No repeating groups

Second Normal Form (2NF)

Meets 1NF requirements
All non-key attributes depend on the entire primary key

Third Normal Form (3NF)

Meets 2NF requirements
No transitive dependencies (non-key attributes depend only on primary key)

When to Denormalize:

Read-heavy workloads where joins are expensive
Frequently accessed aggregate data
Historical snapshots that shouldn't change
Performance-critical queries

Table Design Patterns

Primary Keys:

sql

-- Serial auto-increment (traditional)CREATE TABLE users (    id SERIAL PRIMARY KEY,    email VARCHAR(255) UNIQUE NOT NULL,    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
-- UUID for distributed systemsCREATE TABLE accounts (    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),    name VARCHAR(255) NOT NULL,    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
-- Composite primary keyCREATE TABLE order_items (    order_id INTEGER NOT NULL,    product_id INTEGER NOT NULL,    quantity INTEGER NOT NULL,    price NUMERIC(10, 2) NOT NULL,    PRIMARY KEY (order_id, product_id),    FOREIGN KEY (order_id) REFERENCES orders(id),    FOREIGN KEY (product_id) REFERENCES products(id));

-- Serial auto-increment (traditional)CREATE TABLE users (    id SERIAL PRIMARY KEY,    email VARCHAR(255) UNIQUE NOT NULL,    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
-- UUID for distributed systemsCREATE TABLE accounts (    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),    name VARCHAR(255) NOT NULL,    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
-- Composite primary keyCREATE TABLE order_items (    order_id INTEGER NOT NULL,    product_id INTEGER NOT NULL,    quantity INTEGER NOT NULL,    price NUMERIC(10, 2) NOT NULL,    PRIMARY KEY (order_id, product_id),    FOREIGN KEY (order_id) REFERENCES orders(id),    FOREIGN KEY (product_id) REFERENCES products(id));

Foreign Key Constraints:

sql

-- Cascade delete: Remove child records when parent deletedCREATE TABLE posts (    id SERIAL PRIMARY KEY,    user_id INTEGER NOT NULL,    title VARCHAR(255) NOT NULL,    content TEXT,    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE);
-- Set null: Preserve child records, nullify referenceCREATE TABLE comments (    id SERIAL PRIMARY KEY,    post_id INTEGER,    user_id INTEGER,    content TEXT NOT NULL,    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE SET NULL,    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL);
-- Restrict: Prevent deletion if child records existCREATE TABLE categories (    id SERIAL PRIMARY KEY,    name VARCHAR(255) UNIQUE NOT NULL);
CREATE TABLE products (    id SERIAL PRIMARY KEY,    category_id INTEGER NOT NULL,    name VARCHAR(255) NOT NULL,    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT);

-- Cascade delete: Remove child records when parent deletedCREATE TABLE posts (    id SERIAL PRIMARY KEY,    user_id INTEGER NOT NULL,    title VARCHAR(255) NOT NULL,    content TEXT,    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE);
-- Set null: Preserve child records, nullify referenceCREATE TABLE comments (    id SERIAL PRIMARY KEY,    post_id INTEGER,    user_id INTEGER,    content TEXT NOT NULL,    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE SET NULL,    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL);
-- Restrict: Prevent deletion if child records existCREATE TABLE categories (    id SERIAL PRIMARY KEY,    name VARCHAR(255) UNIQUE NOT NULL);
CREATE TABLE products (    id SERIAL PRIMARY KEY,    category_id INTEGER NOT NULL,    name VARCHAR(255) NOT NULL,    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT);

Advanced Constraints

Check Constraints:

sql

CREATE TABLE products (    id SERIAL PRIMARY KEY,    name VARCHAR(255) NOT NULL,    price NUMERIC(10, 2) NOT NULL CHECK (price > 0),    discount_percent INTEGER CHECK (discount_percent BETWEEN 0 AND 100),    stock_quantity INTEGER NOT NULL CHECK (stock_quantity >= 0));
-- Table-level check constraintCREATE TABLE date_ranges (    id SERIAL PRIMARY KEY,    start_date DATE NOT NULL,    end_date DATE NOT NULL,    CHECK (end_date > start_date));

CREATE TABLE products (    id SERIAL PRIMARY KEY,    name VARCHAR(255) NOT NULL,    price NUMERIC(10, 2) NOT NULL CHECK (price > 0),    discount_percent INTEGER CHECK (discount_percent BETWEEN 0 AND 100),    stock_quantity INTEGER NOT NULL CHECK (stock_quantity >= 0));
-- Table-level check constraintCREATE TABLE date_ranges (    id SERIAL PRIMARY KEY,    start_date DATE NOT NULL,    end_date DATE NOT NULL,    CHECK (end_date > start_date));

Unique Constraints:

sql

-- Single column uniqueCREATE TABLE users (    id SERIAL PRIMARY KEY,    email VARCHAR(255) UNIQUE NOT NULL,    username VARCHAR(50) UNIQUE NOT NULL);
-- Composite unique constraintCREATE TABLE user_permissions (    user_id INTEGER NOT NULL,    permission_id INTEGER NOT NULL,    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    UNIQUE (user_id, permission_id),    FOREIGN KEY (user_id) REFERENCES users(id),    FOREIGN KEY (permission_id) REFERENCES permissions(id));
-- Partial unique index (unique where condition met)CREATE UNIQUE INDEX unique_active_emailON users (email)WHERE active = true;

-- Single column uniqueCREATE TABLE users (    id SERIAL PRIMARY KEY,    email VARCHAR(255) UNIQUE NOT NULL,    username VARCHAR(50) UNIQUE NOT NULL);
-- Composite unique constraintCREATE TABLE user_permissions (    user_id INTEGER NOT NULL,    permission_id INTEGER NOT NULL,    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    UNIQUE (user_id, permission_id),    FOREIGN KEY (user_id) REFERENCES users(id),    FOREIGN KEY (permission_id) REFERENCES permissions(id));
-- Partial unique index (unique where condition met)CREATE UNIQUE INDEX unique_active_emailON users (email)WHERE active = true;

Triggers and Functions

Audit Trail Pattern:

sql

-- Audit tableCREATE TABLE audit_log (    id SERIAL PRIMARY KEY,    table_name VARCHAR(255) NOT NULL,    record_id INTEGER NOT NULL,    action VARCHAR(10) NOT NULL,    old_data JSONB,    new_data JSONB,    changed_by VARCHAR(255),    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
-- Trigger functionCREATE OR REPLACE FUNCTION audit_trigger_function()RETURNS TRIGGER AS $$BEGIN    IF TG_OP = 'INSERT' THEN        INSERT INTO audit_log (table_name, record_id, action, new_data, changed_by)        VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', row_to_json(NEW), current_user);        RETURN NEW;    ELSIF TG_OP = 'UPDATE' THEN        INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by)        VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', row_to_json(OLD), row_to_json(NEW), current_user);        RETURN NEW;    ELSIF TG_OP = 'DELETE' THEN        INSERT INTO audit_log (table_name, record_id, action, old_data, changed_by)        VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', row_to_json(OLD), current_user);        RETURN OLD;    END IF;END;$$ LANGUAGE plpgsql;
-- Attach trigger to tableCREATE TRIGGER users_audit_triggerAFTER INSERT OR UPDATE OR DELETE ON usersFOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

-- Audit tableCREATE TABLE audit_log (    id SERIAL PRIMARY KEY,    table_name VARCHAR(255) NOT NULL,    record_id INTEGER NOT NULL,    action VARCHAR(10) NOT NULL,    old_data JSONB,    new_data JSONB,    changed_by VARCHAR(255),    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
-- Trigger functionCREATE OR REPLACE FUNCTION audit_trigger_function()RETURNS TRIGGER AS $$BEGIN    IF TG_OP = 'INSERT' THEN        INSERT INTO audit_log (table_name, record_id, action, new_data, changed_by)        VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', row_to_json(NEW), current_user);        RETURN NEW;    ELSIF TG_OP = 'UPDATE' THEN        INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by)        VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', row_to_json(OLD), row_to_json(NEW), current_user);        RETURN NEW;    ELSIF TG_OP = 'DELETE' THEN        INSERT INTO audit_log (table_name, record_id, action, old_data, changed_by)        VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', row_to_json(OLD), current_user);        RETURN OLD;    END IF;END;$$ LANGUAGE plpgsql;
-- Attach trigger to tableCREATE TRIGGER users_audit_triggerAFTER INSERT OR UPDATE OR DELETE ON usersFOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

Timestamp Update Pattern:

sql

CREATE OR REPLACE FUNCTION update_modified_timestamp()RETURNS TRIGGER AS $$BEGIN    NEW.updated_at = CURRENT_TIMESTAMP;    RETURN NEW;END;$$ LANGUAGE plpgsql;
CREATE TABLE posts (    id SERIAL PRIMARY KEY,    title VARCHAR(255) NOT NULL,    content TEXT,    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TRIGGER posts_update_timestampBEFORE UPDATE ON postsFOR EACH ROW EXECUTE FUNCTION update_modified_timestamp();

CREATE OR REPLACE FUNCTION update_modified_timestamp()RETURNS TRIGGER AS $$BEGIN    NEW.updated_at = CURRENT_TIMESTAMP;    RETURN NEW;END;$$ LANGUAGE plpgsql;
CREATE TABLE posts (    id SERIAL PRIMARY KEY,    title VARCHAR(255) NOT NULL,    content TEXT,    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TRIGGER posts_update_timestampBEFORE UPDATE ON postsFOR EACH ROW EXECUTE FUNCTION update_modified_timestamp();

Views and Materialized Views

Standard Views:

sql

-- Virtual table - computed on each queryCREATE VIEW active_users_with_posts ASSELECT    u.id,    u.username,    u.email,    COUNT(p.id) as post_count,    MAX(p.created_at) as last_post_dateFROM users uLEFT JOIN posts p ON u.id = p.user_idWHERE u.active = trueGROUP BY u.id, u.username, u.email;
-- Use view like a tableSELECT * FROM active_users_with_posts WHERE post_count > 10;

-- Virtual table - computed on each queryCREATE VIEW active_users_with_posts ASSELECT    u.id,    u.username,    u.email,    COUNT(p.id) as post_count,    MAX(p.created_at) as last_post_dateFROM users uLEFT JOIN posts p ON u.id = p.user_idWHERE u.active = trueGROUP BY u.id, u.username, u.email;
-- Use view like a tableSELECT * FROM active_users_with_posts WHERE post_count > 10;

Materialized Views:

sql

-- Physical table - stores computed resultsCREATE MATERIALIZED VIEW user_statistics ASSELECT    u.id,    u.username,    COUNT(DISTINCT p.id) as total_posts,    COUNT(DISTINCT c.id) as total_comments,    AVG(p.views) as avg_post_views,    MAX(p.created_at) as last_activityFROM users uLEFT JOIN posts p ON u.id = p.user_idLEFT JOIN comments c ON u.id = c.user_idGROUP BY u.id, u.username;
-- Create index on materialized viewCREATE INDEX idx_user_stats_posts ON user_statistics(total_posts);
-- Refresh materialized view (update data)REFRESH MATERIALIZED VIEW user_statistics;
-- Concurrent refresh (allows reads during refresh)REFRESH MATERIALIZED VIEW CONCURRENTLY user_statistics;

-- Physical table - stores computed resultsCREATE MATERIALIZED VIEW user_statistics ASSELECT    u.id,    u.username,    COUNT(DISTINCT p.id) as total_posts,    COUNT(DISTINCT c.id) as total_comments,    AVG(p.views) as avg_post_views,    MAX(p.created_at) as last_activityFROM users uLEFT JOIN posts p ON u.id = p.user_idLEFT JOIN comments c ON u.id = c.user_idGROUP BY u.id, u.username;
-- Create index on materialized viewCREATE INDEX idx_user_stats_posts ON user_statistics(total_posts);
-- Refresh materialized view (update data)REFRESH MATERIALIZED VIEW user_statistics;
-- Concurrent refresh (allows reads during refresh)REFRESH MATERIALIZED VIEW CONCURRENTLY user_statistics;

MongoDB Patterns

Document Modeling Strategies

Embedding vs Referencing

Embedding Pattern (Denormalization):

javascript

// One-to-few: Embed when relationship is contained// Example: Blog post with comments{    _id: ObjectId("..."),    title: "Database Design Patterns",    author: "John Doe",    content: "...",    published_at: ISODate("2025-01-15"),    comments: [        {            _id: ObjectId("..."),            author: "Jane Smith",            text: "Great article!",            created_at: ISODate("2025-01-16")        },        {            _id: ObjectId("..."),            author: "Bob Johnson",            text: "Very helpful, thanks!",            created_at: ISODate("2025-01-17")        }    ],    tags: ["database", "design", "patterns"],    stats: {        views: 1523,        likes: 89,        shares: 23    }}
// Benefits:// - Single query to retrieve post with comments// - Better read performance// - Atomic updates to entire document//// Drawbacks:// - Document size limits (16MB in MongoDB)// - Difficult to query comments independently// - May duplicate data if comments need to appear elsewhere

// One-to-few: Embed when relationship is contained// Example: Blog post with comments{    _id: ObjectId("..."),    title: "Database Design Patterns",    author: "John Doe",    content: "...",    published_at: ISODate("2025-01-15"),    comments: [        {            _id: ObjectId("..."),            author: "Jane Smith",            text: "Great article!",            created_at: ISODate("2025-01-16")        },        {            _id: ObjectId("..."),            author: "Bob Johnson",            text: "Very helpful, thanks!",            created_at: ISODate("2025-01-17")        }    ],    tags: ["database", "design", "patterns"],    stats: {        views: 1523,        likes: 89,        shares: 23    }}
// Benefits:// - Single query to retrieve post with comments// - Better read performance// - Atomic updates to entire document//// Drawbacks:// - Document size limits (16MB in MongoDB)// - Difficult to query comments independently// - May duplicate data if comments need to appear elsewhere

Referencing Pattern (Normalization):

javascript

// One-to-many or many-to-many: Reference when relationship is unbounded// Example: User with many posts
// Users collection{    _id: ObjectId("507f1f77bcf86cd799439011"),    username: "john_doe",    email: "[email protected]",    profile: {        bio: "Software engineer",        avatar_url: "https://...",        location: "San Francisco"    },    created_at: ISODate("2024-01-01")}
// Posts collection (references user){    _id: ObjectId("507f191e810c19729de860ea"),    user_id: ObjectId("507f1f77bcf86cd799439011"),    title: "My First Post",    content: "...",    published_at: ISODate("2025-01-15"),    comment_ids: [        ObjectId("..."),        ObjectId("...")    ]}
// Benefits:// - No duplication of user data// - Flexible: users can have unlimited posts// - Easy to update user information once//// Drawbacks:// - Requires multiple queries or $lookup// - Slower read performance for joined data

// One-to-many or many-to-many: Reference when relationship is unbounded// Example: User with many posts
// Users collection{    _id: ObjectId("507f1f77bcf86cd799439011"),    username: "john_doe",    email: "[email protected]",    profile: {        bio: "Software engineer",        avatar_url: "https://...",        location: "San Francisco"    },    created_at: ISODate("2024-01-01")}
// Posts collection (references user){    _id: ObjectId("507f191e810c19729de860ea"),    user_id: ObjectId("507f1f77bcf86cd799439011"),    title: "My First Post",    content: "...",    published_at: ISODate("2025-01-15"),    comment_ids: [        ObjectId("..."),        ObjectId("...")    ]}
// Benefits:// - No duplication of user data// - Flexible: users can have unlimited posts// - Easy to update user information once//// Drawbacks:// - Requires multiple queries or $lookup// - Slower read performance for joined data

Hybrid Approach (Selective Denormalization):

javascript

// Store frequently accessed fields from referenced document{    _id: ObjectId("..."),    title: "Database Patterns",    content: "...",    author: {        // Embedded: frequently accessed, rarely changes        id: ObjectId("507f1f77bcf86cd799439011"),        username: "john_doe",        avatar_url: "https://..."    },    // Reference: full user data available if needed    author_id: ObjectId("507f1f77bcf86cd799439011"),    published_at: ISODate("2025-01-15")}
// Benefits:// - Fast reads with embedded frequently-used data// - Can still get full user data when needed// - Balance between performance and flexibility//// Tradeoffs:// - Need to update embedded data when user changes username/avatar// - Slightly larger documents

// Store frequently accessed fields from referenced document{    _id: ObjectId("..."),    title: "Database Patterns",    content: "...",    author: {        // Embedded: frequently accessed, rarely changes        id: ObjectId("507f1f77bcf86cd799439011"),        username: "john_doe",        avatar_url: "https://..."    },    // Reference: full user data available if needed    author_id: ObjectId("507f1f77bcf86cd799439011"),    published_at: ISODate("2025-01-15")}
// Benefits:// - Fast reads with embedded frequently-used data// - Can still get full user data when needed// - Balance between performance and flexibility//// Tradeoffs:// - Need to update embedded data when user changes username/avatar// - Slightly larger documents

Schema Design Patterns

Bucket Pattern (Time-Series Data):

javascript

// Instead of one document per measurement:// BAD: Millions of tiny documents{    sensor_id: "sensor_001",    timestamp: ISODate("2025-01-15T10:00:00Z"),    temperature: 72.5,    humidity: 45}
// GOOD: Bucket documents with arrays of measurements{    sensor_id: "sensor_001",    date: ISODate("2025-01-15"),    hour: 10,    measurements: [        { minute: 0, temperature: 72.5, humidity: 45 },        { minute: 1, temperature: 72.6, humidity: 45 },        { minute: 2, temperature: 72.4, humidity: 46 },        // ... up to 60 measurements per hour    ],    summary: {        count: 60,        avg_temperature: 72.5,        min_temperature: 71.8,        max_temperature: 73.2    }}
// Benefits:// - Reduced document count (60x fewer documents)// - Better index efficiency// - Pre-computed summaries// - Easier to query by time ranges

// Instead of one document per measurement:// BAD: Millions of tiny documents{    sensor_id: "sensor_001",    timestamp: ISODate("2025-01-15T10:00:00Z"),    temperature: 72.5,    humidity: 45}
// GOOD: Bucket documents with arrays of measurements{    sensor_id: "sensor_001",    date: ISODate("2025-01-15"),    hour: 10,    measurements: [        { minute: 0, temperature: 72.5, humidity: 45 },        { minute: 1, temperature: 72.6, humidity: 45 },        { minute: 2, temperature: 72.4, humidity: 46 },        // ... up to 60 measurements per hour    ],    summary: {        count: 60,        avg_temperature: 72.5,        min_temperature: 71.8,        max_temperature: 73.2    }}
// Benefits:// - Reduced document count (60x fewer documents)// - Better index efficiency// - Pre-computed summaries// - Easier to query by time ranges

Computed Pattern (Pre-Aggregated Data):

javascript

// Store computed values to avoid expensive aggregations{    _id: ObjectId("..."),    product_id: "PROD-123",    month: "2025-01",    total_sales: 15420.50,    units_sold: 234,    unique_customers: 187,    avg_order_value: 65.90,    top_customers: [        { customer_id: "CUST-456", revenue: 890.50 },        { customer_id: "CUST-789", revenue: 675.25 }    ],    computed_at: ISODate("2025-02-01T00:00:00Z")}
// Update pattern: Scheduled job or trigger updates computed values

// Store computed values to avoid expensive aggregations{    _id: ObjectId("..."),    product_id: "PROD-123",    month: "2025-01",    total_sales: 15420.50,    units_sold: 234,    unique_customers: 187,    avg_order_value: 65.90,    top_customers: [        { customer_id: "CUST-456", revenue: 890.50 },        { customer_id: "CUST-789", revenue: 675.25 }    ],    computed_at: ISODate("2025-02-01T00:00:00Z")}
// Update pattern: Scheduled job or trigger updates computed values

Polymorphic Pattern (Varied Schemas):

javascript

// Handle different product types in single collection{    _id: ObjectId("..."),    type: "book",    name: "Database Design",    price: 49.99,    // Book-specific fields    isbn: "978-0-123456-78-9",    author: "John Smith",    pages: 456,    publisher: "Tech Books Inc"}
{    _id: ObjectId("..."),    type: "electronics",    name: "Wireless Mouse",    price: 29.99,    // Electronics-specific fields    brand: "TechBrand",    warranty_months: 24,    specifications: {        battery_life: "6 months",        connectivity: "Bluetooth 5.0"    }}
// Query by typedb.products.find({ type: "book", author: "John Smith" })db.products.find({ type: "electronics", "specifications.connectivity": /Bluetooth/ })

// Handle different product types in single collection{    _id: ObjectId("..."),    type: "book",    name: "Database Design",    price: 49.99,    // Book-specific fields    isbn: "978-0-123456-78-9",    author: "John Smith",    pages: 456,    publisher: "Tech Books Inc"}
{    _id: ObjectId("..."),    type: "electronics",    name: "Wireless Mouse",    price: 29.99,    // Electronics-specific fields    brand: "TechBrand",    warranty_months: 24,    specifications: {        battery_life: "6 months",        connectivity: "Bluetooth 5.0"    }}
// Query by typedb.products.find({ type: "book", author: "John Smith" })db.products.find({ type: "electronics", "specifications.connectivity": /Bluetooth/ })

Aggregation Framework

Basic Aggregation Pipeline:

javascript

// Group by author and count postsdb.posts.aggregate([    {        $match: { published: true } // Filter stage    },    {        $group: {            _id: "$author_id",            total_posts: { $sum: 1 },            total_views: { $sum: "$views" },            avg_views: { $avg: "$views" },            latest_post: { $max: "$published_at" }        }    },    {        $sort: { total_posts: -1 } // Sort by post count    },    {        $limit: 10 // Top 10 authors    }])

// Group by author and count postsdb.posts.aggregate([    {        $match: { published: true } // Filter stage    },    {        $group: {            _id: "$author_id",            total_posts: { $sum: 1 },            total_views: { $sum: "$views" },            avg_views: { $avg: "$views" },            latest_post: { $max: "$published_at" }        }    },    {        $sort: { total_posts: -1 } // Sort by post count    },    {        $limit: 10 // Top 10 authors    }])

Advanced Pipeline with Lookup (Join):

javascript

// Join posts with user datadb.posts.aggregate([    {        $match: {            published_at: { $gte: ISODate("2025-01-01") }        }    },    {        $lookup: {            from: "users",            localField: "author_id",            foreignField: "_id",            as: "author"        }    },    {        $unwind: "$author" // Flatten author array    },    {        $project: {            title: 1,            content: 1,            views: 1,            "author.username": 1,            "author.email": 1,            days_since_publish: {                $divide: [                    { $subtract: [new Date(), "$published_at"] },                    1000 * 60 * 60 * 24                ]            }        }    },    {        $sort: { views: -1 }    }])

// Join posts with user datadb.posts.aggregate([    {        $match: {            published_at: { $gte: ISODate("2025-01-01") }        }    },    {        $lookup: {            from: "users",            localField: "author_id",            foreignField: "_id",            as: "author"        }    },    {        $unwind: "$author" // Flatten author array    },    {        $project: {            title: 1,            content: 1,            views: 1,            "author.username": 1,            "author.email": 1,            days_since_publish: {                $divide: [                    { $subtract: [new Date(), "$published_at"] },                    1000 * 60 * 60 * 24                ]            }        }    },    {        $sort: { views: -1 }    }])

Aggregation with Grouping and Reshaping:

javascript

// Complex aggregation: Sales analysisdb.orders.aggregate([    {        $match: {            status: "completed",            created_at: {                $gte: ISODate("2025-01-01"),                $lt: ISODate("2025-02-01")            }        }    },    {        $unwind: "$items" // Flatten order items    },    {        $group: {            _id: {                product_id: "$items.product_id",                customer_region: "$customer.region"            },            total_quantity: { $sum: "$items.quantity" },            total_revenue: { $sum: "$items.total_price" },            order_count: { $sum: 1 },            avg_order_value: { $avg: "$items.total_price" }        }    },    {        $group: {            _id: "$_id.product_id",            regions: {                $push: {                    region: "$_id.customer_region",                    quantity: "$total_quantity",                    revenue: "$total_revenue"                }            },            total_quantity: { $sum: "$total_quantity" },            total_revenue: { $sum: "$total_revenue" }        }    },    {        $sort: { total_revenue: -1 }    }])

// Complex aggregation: Sales analysisdb.orders.aggregate([    {        $match: {            status: "completed",            created_at: {                $gte: ISODate("2025-01-01"),                $lt: ISODate("2025-02-01")            }        }    },    {        $unwind: "$items" // Flatten order items    },    {        $group: {            _id: {                product_id: "$items.product_id",                customer_region: "$customer.region"            },            total_quantity: { $sum: "$items.quantity" },            total_revenue: { $sum: "$items.total_price" },            order_count: { $sum: 1 },            avg_order_value: { $avg: "$items.total_price" }        }    },    {        $group: {            _id: "$_id.product_id",            regions: {                $push: {                    region: "$_id.customer_region",                    quantity: "$total_quantity",                    revenue: "$total_revenue"                }            },            total_quantity: { $sum: "$total_quantity" },            total_revenue: { $sum: "$total_revenue" }        }    },    {        $sort: { total_revenue: -1 }    }])

Indexing Strategies

PostgreSQL Indexes

B-tree Indexes (Default):

sql

-- Single column indexCREATE INDEX idx_users_email ON users(email);
-- Composite index (order matters!)CREATE INDEX idx_posts_author_publishedON posts(author_id, published_at);
-- Query can use index:-- SELECT * FROM posts WHERE author_id = 123 ORDER BY published_at;-- SELECT * FROM posts WHERE author_id = 123 AND published_at > '2025-01-01';
-- Query CANNOT fully use index:-- SELECT * FROM posts WHERE published_at > '2025-01-01'; (only uses first column)

-- Single column indexCREATE INDEX idx_users_email ON users(email);
-- Composite index (order matters!)CREATE INDEX idx_posts_author_publishedON posts(author_id, published_at);
-- Query can use index:-- SELECT * FROM posts WHERE author_id = 123 ORDER BY published_at;-- SELECT * FROM posts WHERE author_id = 123 AND published_at > '2025-01-01';
-- Query CANNOT fully use index:-- SELECT * FROM posts WHERE published_at > '2025-01-01'; (only uses first column)

Partial Indexes:

sql

-- Index only active usersCREATE INDEX idx_active_usersON users(username)WHERE active = true;
-- Index only recent ordersCREATE INDEX idx_recent_ordersON orders(created_at, status)WHERE created_at > '2024-01-01';
-- Benefits: Smaller index size, faster queries on filtered data

-- Index only active usersCREATE INDEX idx_active_usersON users(username)WHERE active = true;
-- Index only recent ordersCREATE INDEX idx_recent_ordersON orders(created_at, status)WHERE created_at > '2024-01-01';
-- Benefits: Smaller index size, faster queries on filtered data

Expression Indexes:

sql

-- Index on lowercase email for case-insensitive searchCREATE INDEX idx_users_email_lowerON users(LOWER(email));
-- Query that uses this index:SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Index on JSONB field extractionCREATE INDEX idx_metadata_tagsON products((metadata->>'category'));

-- Index on lowercase email for case-insensitive searchCREATE INDEX idx_users_email_lowerON users(LOWER(email));
-- Query that uses this index:SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Index on JSONB field extractionCREATE INDEX idx_metadata_tagsON products((metadata->>'category'));

Full-Text Search Indexes:

sql

-- Add tsvector column for full-text searchALTER TABLE articlesADD COLUMN tsv_content tsvector;
-- Populate tsvector columnUPDATE articlesSET tsv_content = to_tsvector('english', title || ' ' || content);
-- Create GIN index for full-text searchCREATE INDEX idx_articles_tsv ON articles USING GIN(tsv_content);
-- Full-text search querySELECT title, ts_rank(tsv_content, query) as rankFROM articles, to_tsquery('english', 'database & design') queryWHERE tsv_content @@ queryORDER BY rank DESC;
-- Trigger to auto-update tsvectorCREATE TRIGGER articles_tsv_updateBEFORE INSERT OR UPDATE ON articlesFOR EACH ROW EXECUTE FUNCTIONtsvector_update_trigger(tsv_content, 'pg_catalog.english', title, content);

-- Add tsvector column for full-text searchALTER TABLE articlesADD COLUMN tsv_content tsvector;
-- Populate tsvector columnUPDATE articlesSET tsv_content = to_tsvector('english', title || ' ' || content);
-- Create GIN index for full-text searchCREATE INDEX idx_articles_tsv ON articles USING GIN(tsv_content);
-- Full-text search querySELECT title, ts_rank(tsv_content, query) as rankFROM articles, to_tsquery('english', 'database & design') queryWHERE tsv_content @@ queryORDER BY rank DESC;
-- Trigger to auto-update tsvectorCREATE TRIGGER articles_tsv_updateBEFORE INSERT OR UPDATE ON articlesFOR EACH ROW EXECUTE FUNCTIONtsvector_update_trigger(tsv_content, 'pg_catalog.english', title, content);

JSONB Indexes:

sql

-- GIN index for JSONB containment queriesCREATE INDEX idx_products_metadataON products USING GIN(metadata);
-- Queries that use this index:SELECT * FROM products WHERE metadata @> '{"color": "blue"}';SELECT * FROM products WHERE metadata ? 'size';
-- Index on specific JSONB pathCREATE INDEX idx_products_categoryON products((metadata->>'category'));

-- GIN index for JSONB containment queriesCREATE INDEX idx_products_metadataON products USING GIN(metadata);
-- Queries that use this index:SELECT * FROM products WHERE metadata @> '{"color": "blue"}';SELECT * FROM products WHERE metadata ? 'size';
-- Index on specific JSONB pathCREATE INDEX idx_products_categoryON products((metadata->>'category'));

Index Monitoring:

sql

-- Find unused indexesSELECT    schemaname,    tablename,    indexname,    idx_scan,    idx_tup_read,    idx_tup_fetch,    pg_size_pretty(pg_relation_size(indexrelid)) as index_sizeFROM pg_stat_user_indexesWHERE idx_scan = 0ORDER BY pg_relation_size(indexrelid) DESC;
-- Check index usageSELECT    relname as table_name,    indexrelname as index_name,    idx_scan as times_used,    idx_tup_read as tuples_read,    idx_tup_fetch as tuples_fetchedFROM pg_stat_user_indexesORDER BY idx_scan ASC;

-- Find unused indexesSELECT    schemaname,    tablename,    indexname,    idx_scan,    idx_tup_read,    idx_tup_fetch,    pg_size_pretty(pg_relation_size(indexrelid)) as index_sizeFROM pg_stat_user_indexesWHERE idx_scan = 0ORDER BY pg_relation_size(indexrelid) DESC;
-- Check index usageSELECT    relname as table_name,    indexrelname as index_name,    idx_scan as times_used,    idx_tup_read as tuples_read,    idx_tup_fetch as tuples_fetchedFROM pg_stat_user_indexesORDER BY idx_scan ASC;

MongoDB Indexes

Single Field Indexes:

javascript

// Create index on single fielddb.users.createIndex({ email: 1 }) // 1 = ascending, -1 = descending
// Unique indexdb.users.createIndex({ username: 1 }, { unique: true })
// Sparse index (only index documents with the field)db.users.createIndex({ phone_number: 1 }, { sparse: true })

// Create index on single fielddb.users.createIndex({ email: 1 }) // 1 = ascending, -1 = descending
// Unique indexdb.users.createIndex({ username: 1 }, { unique: true })
// Sparse index (only index documents with the field)db.users.createIndex({ phone_number: 1 }, { sparse: true })

Compound Indexes:

javascript

// Index on multiple fields (order matters!)db.posts.createIndex({ author_id: 1, published_at: -1 })
// Efficient queries:// - { author_id: "123" }// - { author_id: "123", published_at: { $gte: ... } }// - { author_id: "123" } with sort by published_at
// Inefficient:// - { published_at: { $gte: ... } } alone (doesn't use index efficiently)
// ESR Rule: Equality, Sort, Range// Best compound index order:// 1. Equality filters first// 2. Sort fields second// 3. Range filters lastdb.orders.createIndex({    status: 1,           // Equality    created_at: -1,      // Sort    total_amount: 1      // Range})

// Index on multiple fields (order matters!)db.posts.createIndex({ author_id: 1, published_at: -1 })
// Efficient queries:// - { author_id: "123" }// - { author_id: "123", published_at: { $gte: ... } }// - { author_id: "123" } with sort by published_at
// Inefficient:// - { published_at: { $gte: ... } } alone (doesn't use index efficiently)
// ESR Rule: Equality, Sort, Range// Best compound index order:// 1. Equality filters first// 2. Sort fields second// 3. Range filters lastdb.orders.createIndex({    status: 1,           // Equality    created_at: -1,      // Sort    total_amount: 1      // Range})

Multikey Indexes (Array Fields):

javascript

// Index on array fielddb.posts.createIndex({ tags: 1 })
// Document with array{    _id: ObjectId("..."),    title: "Database Design",    tags: ["database", "mongodb", "schema"]}
// Query that uses multikey indexdb.posts.find({ tags: "mongodb" })db.posts.find({ tags: { $in: ["database", "nosql"] } })
// Compound multikey index (max one array field)db.posts.createIndex({ tags: 1, published_at: -1 }) // Valid// db.posts.createIndex({ tags: 1, categories: 1 }) // Invalid if both are arrays

// Index on array fielddb.posts.createIndex({ tags: 1 })
// Document with array{    _id: ObjectId("..."),    title: "Database Design",    tags: ["database", "mongodb", "schema"]}
// Query that uses multikey indexdb.posts.find({ tags: "mongodb" })db.posts.find({ tags: { $in: ["database", "nosql"] } })
// Compound multikey index (max one array field)db.posts.createIndex({ tags: 1, published_at: -1 }) // Valid// db.posts.createIndex({ tags: 1, categories: 1 }) // Invalid if both are arrays

Text Indexes:

javascript

// Create text index for full-text searchdb.articles.createIndex({    title: "text",    content: "text"})
// Text search querydb.articles.find({    $text: { $search: "database design patterns" }})
// Search with relevance scoredb.articles.find(    { $text: { $search: "database design" } },    { score: { $meta: "textScore" } }).sort({ score: { $meta: "textScore" } })
// Weighted text index (prioritize title over content)db.articles.createIndex(    { title: "text", content: "text" },    { weights: { title: 10, content: 5 } })

// Create text index for full-text searchdb.articles.createIndex({    title: "text",    content: "text"})
// Text search querydb.articles.find({    $text: { $search: "database design patterns" }})
// Search with relevance scoredb.articles.find(    { $text: { $search: "database design" } },    { score: { $meta: "textScore" } }).sort({ score: { $meta: "textScore" } })
// Weighted text index (prioritize title over content)db.articles.createIndex(    { title: "text", content: "text" },    { weights: { title: 10, content: 5 } })

Geospatial Indexes:

javascript

// 2dsphere index for geographic queriesdb.locations.createIndex({ coordinates: "2dsphere" })
// Document format{    name: "Coffee Shop",    coordinates: {        type: "Point",        coordinates: [-122.4194, 37.7749] // [longitude, latitude]    }}
// Find locations near a pointdb.locations.find({    coordinates: {        $near: {            $geometry: {                type: "Point",                coordinates: [-122.4194, 37.7749]            },            $maxDistance: 1000 // meters        }    }})

// 2dsphere index for geographic queriesdb.locations.createIndex({ coordinates: "2dsphere" })
// Document format{    name: "Coffee Shop",    coordinates: {        type: "Point",        coordinates: [-122.4194, 37.7749] // [longitude, latitude]    }}
// Find locations near a pointdb.locations.find({    coordinates: {        $near: {            $geometry: {                type: "Point",                coordinates: [-122.4194, 37.7749]            },            $maxDistance: 1000 // meters        }    }})

Index Properties:

javascript

// TTL Index (auto-delete documents after time)db.sessions.createIndex(    { created_at: 1 },    { expireAfterSeconds: 3600 } // 1 hour)
// Partial Index (index subset of documents)db.orders.createIndex(    { status: 1, created_at: -1 },    { partialFilterExpression: { status: { $eq: "pending" } } })
// Case-insensitive indexdb.users.createIndex(    { email: 1 },    { collation: { locale: "en", strength: 2 } })
// Background index creation (doesn't block operations)db.large_collection.createIndex(    { field: 1 },    { background: true })

// TTL Index (auto-delete documents after time)db.sessions.createIndex(    { created_at: 1 },    { expireAfterSeconds: 3600 } // 1 hour)
// Partial Index (index subset of documents)db.orders.createIndex(    { status: 1, created_at: -1 },    { partialFilterExpression: { status: { $eq: "pending" } } })
// Case-insensitive indexdb.users.createIndex(    { email: 1 },    { collation: { locale: "en", strength: 2 } })
// Background index creation (doesn't block operations)db.large_collection.createIndex(    { field: 1 },    { background: true })

Index Analysis:

javascript

// Explain query executiondb.posts.find({ author_id: "123" }).explain("executionStats")
// Check index usagedb.posts.aggregate([    { $indexStats: {} }])
// List all indexes on collectiondb.posts.getIndexes()
// Drop unused indexdb.posts.dropIndex("index_name")

// Explain query executiondb.posts.find({ author_id: "123" }).explain("executionStats")
// Check index usagedb.posts.aggregate([    { $indexStats: {} }])
// List all indexes on collectiondb.posts.getIndexes()
// Drop unused indexdb.posts.dropIndex("index_name")

Transactions

PostgreSQL Transaction Management

Basic Transactions:

sql

-- Explicit transactionBEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;-- or ROLLBACK; to cancel changes

-- Explicit transactionBEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;-- or ROLLBACK; to cancel changes

Savepoints (Partial Rollback):

sql

BEGIN;
UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 'PROD-123';
SAVEPOINT before_audit;
INSERT INTO audit_log (action, details) VALUES ('inventory_update', '...');-- Oops, error in audit log
ROLLBACK TO SAVEPOINT before_audit;-- Inventory update preserved, audit insert rolled back
-- Fix and retryINSERT INTO audit_log (action, details) VALUES ('inventory_update', 'correct details');
COMMIT;

BEGIN;
UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 'PROD-123';
SAVEPOINT before_audit;
INSERT INTO audit_log (action, details) VALUES ('inventory_update', '...');-- Oops, error in audit log
ROLLBACK TO SAVEPOINT before_audit;-- Inventory update preserved, audit insert rolled back
-- Fix and retryINSERT INTO audit_log (action, details) VALUES ('inventory_update', 'correct details');
COMMIT;

Isolation Levels:

sql

-- Read Uncommitted (not supported in PostgreSQL, defaults to Read Committed)-- Read Committed (default) - sees only committed dataSET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Repeatable Read - sees snapshot at transaction startBEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;SELECT * FROM accounts WHERE id = 1; -- Returns balance 1000-- Another transaction updates balance to 1500 and commitsSELECT * FROM accounts WHERE id = 1; -- Still returns 1000 (repeatable read)COMMIT;
-- Serializable - strictest isolation, prevents all anomaliesBEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;-- If concurrent transactions would violate serializability, one abortsCOMMIT;

-- Read Uncommitted (not supported in PostgreSQL, defaults to Read Committed)-- Read Committed (default) - sees only committed dataSET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Repeatable Read - sees snapshot at transaction startBEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;SELECT * FROM accounts WHERE id = 1; -- Returns balance 1000-- Another transaction updates balance to 1500 and commitsSELECT * FROM accounts WHERE id = 1; -- Still returns 1000 (repeatable read)COMMIT;
-- Serializable - strictest isolation, prevents all anomaliesBEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;-- If concurrent transactions would violate serializability, one abortsCOMMIT;

Advisory Locks (Application-Level Locking):

sql

-- Exclusive lock on arbitrary numberSELECT pg_advisory_lock(12345);-- ... perform critical operation ...SELECT pg_advisory_unlock(12345);
-- Try lock (non-blocking)SELECT pg_try_advisory_lock(12345); -- Returns true if acquired, false otherwise
-- Session-level advisory lock (auto-released on disconnect)SELECT pg_advisory_lock(user_id);

-- Exclusive lock on arbitrary numberSELECT pg_advisory_lock(12345);-- ... perform critical operation ...SELECT pg_advisory_unlock(12345);
-- Try lock (non-blocking)SELECT pg_try_advisory_lock(12345); -- Returns true if acquired, false otherwise
-- Session-level advisory lock (auto-released on disconnect)SELECT pg_advisory_lock(user_id);

Row-Level Locking:

sql

-- SELECT FOR UPDATE - lock rows for updateBEGIN;
SELECT * FROM productsWHERE id = 123FOR UPDATE; -- Locks this row
UPDATE products SET quantity = quantity - 1 WHERE id = 123;
COMMIT;
-- SELECT FOR SHARE - shared lock (allows other reads, blocks writes)SELECT * FROM products WHERE id = 123 FOR SHARE;
-- SKIP LOCKED - skip locked rows instead of waitingSELECT * FROM queueWHERE processed = falseORDER BY priorityLIMIT 10FOR UPDATE SKIP LOCKED;

-- SELECT FOR UPDATE - lock rows for updateBEGIN;
SELECT * FROM productsWHERE id = 123FOR UPDATE; -- Locks this row
UPDATE products SET quantity = quantity - 1 WHERE id = 123;
COMMIT;
-- SELECT FOR SHARE - shared lock (allows other reads, blocks writes)SELECT * FROM products WHERE id = 123 FOR SHARE;
-- SKIP LOCKED - skip locked rows instead of waitingSELECT * FROM queueWHERE processed = falseORDER BY priorityLIMIT 10FOR UPDATE SKIP LOCKED;

MongoDB Transactions

Multi-Document Transactions:

javascript

// Transactions require replica set or sharded clusterconst session = db.getMongo().startSession()session.startTransaction()
try {    const accountsCol = session.getDatabase("mydb").accounts
    // Debit account    accountsCol.updateOne(        { _id: "account1" },        { $inc: { balance: -100 } },        { session }    )
    // Credit account    accountsCol.updateOne(        { _id: "account2" },        { $inc: { balance: 100 } },        { session }    )
    // Commit transaction    session.commitTransaction()} catch (error) {    // Abort on error    session.abortTransaction()    throw error} finally {    session.endSession()}

// Transactions require replica set or sharded clusterconst session = db.getMongo().startSession()session.startTransaction()
try {    const accountsCol = session.getDatabase("mydb").accounts
    // Debit account    accountsCol.updateOne(        { _id: "account1" },        { $inc: { balance: -100 } },        { session }    )
    // Credit account    accountsCol.updateOne(        { _id: "account2" },        { $inc: { balance: 100 } },        { session }    )
    // Commit transaction    session.commitTransaction()} catch (error) {    // Abort on error    session.abortTransaction()    throw error} finally {    session.endSession()}

Read and Write Concerns:

javascript

// Write Concern: Acknowledgment leveldb.orders.insertOne(    { customer_id: "123", items: [...] },    {        writeConcern: {            w: "majority",        // Wait for majority of replica set            j: true,              // Wait for journal write            wtimeout: 5000        // Timeout after 5 seconds        }    })
// Read Concern: Data consistency leveldb.orders.find(    { status: "pending" }).readConcern("majority") // Only return data acknowledged by majority
// Read Preference: Which replica to read fromdb.orders.find({ ... }).readPref("secondary") // Read from secondary replica

// Write Concern: Acknowledgment leveldb.orders.insertOne(    { customer_id: "123", items: [...] },    {        writeConcern: {            w: "majority",        // Wait for majority of replica set            j: true,              // Wait for journal write            wtimeout: 5000        // Timeout after 5 seconds        }    })
// Read Concern: Data consistency leveldb.orders.find(    { status: "pending" }).readConcern("majority") // Only return data acknowledged by majority
// Read Preference: Which replica to read fromdb.orders.find({ ... }).readPref("secondary") // Read from secondary replica

Atomic Operations (Single Document):

javascript

// Single document updates are atomic by defaultdb.counters.updateOne(    { _id: "page_views" },    {        $inc: { count: 1 },        $set: { last_updated: new Date() }    })
// Atomic array operationsdb.posts.updateOne(    { _id: ObjectId("...") },    {        $push: {            comments: {                $each: [{ author: "John", text: "Great!" }],                $position: 0 // Insert at beginning            }        }    })
// Find and modify (atomic read-modify-write)db.queue.findOneAndUpdate(    { status: "pending" },    { $set: { status: "processing", processor_id: "worker-1" } },    {        sort: { priority: -1 },        returnDocument: "after" // Return updated document    })

// Single document updates are atomic by defaultdb.counters.updateOne(    { _id: "page_views" },    {        $inc: { count: 1 },        $set: { last_updated: new Date() }    })
// Atomic array operationsdb.posts.updateOne(    { _id: ObjectId("...") },    {        $push: {            comments: {                $each: [{ author: "John", text: "Great!" }],                $position: 0 // Insert at beginning            }        }    })
// Find and modify (atomic read-modify-write)db.queue.findOneAndUpdate(    { status: "pending" },    { $set: { status: "processing", processor_id: "worker-1" } },    {        sort: { priority: -1 },        returnDocument: "after" // Return updated document    })

Replication

PostgreSQL Replication

Streaming Replication (Primary-Standby):

sql

-- Primary server configuration (postgresql.conf)wal_level = replicamax_wal_senders = 10wal_keep_size = '1GB'hot_standby = on
-- Create replication userCREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';
-- pg_hba.conf on primaryhost replication replicator standby_ip/32 md5
-- Standby server (recovery.conf or postgresql.auto.conf)primary_conninfo = 'host=primary_ip port=5432 user=replicator password=...'restore_command = 'cp /var/lib/postgresql/archive/%f %p'

-- Primary server configuration (postgresql.conf)wal_level = replicamax_wal_senders = 10wal_keep_size = '1GB'hot_standby = on
-- Create replication userCREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';
-- pg_hba.conf on primaryhost replication replicator standby_ip/32 md5
-- Standby server (recovery.conf or postgresql.auto.conf)primary_conninfo = 'host=primary_ip port=5432 user=replicator password=...'restore_command = 'cp /var/lib/postgresql/archive/%f %p'

Logical Replication (Selective Replication):

sql

-- On publisher (source)CREATE PUBLICATION my_publication FOR TABLE users, posts;-- or FOR ALL TABLES;
-- On subscriber (destination)CREATE SUBSCRIPTION my_subscriptionCONNECTION 'host=publisher_ip dbname=mydb user=replicator password=...'PUBLICATION my_publication;
-- Monitor replicationSELECT * FROM pg_stat_replication;SELECT * FROM pg_replication_slots;

-- On publisher (source)CREATE PUBLICATION my_publication FOR TABLE users, posts;-- or FOR ALL TABLES;
-- On subscriber (destination)CREATE SUBSCRIPTION my_subscriptionCONNECTION 'host=publisher_ip dbname=mydb user=replicator password=...'PUBLICATION my_publication;
-- Monitor replicationSELECT * FROM pg_stat_replication;SELECT * FROM pg_replication_slots;

Failover and Promotion:

sql

-- Promote standby to primarypg_ctl promote -D /var/lib/postgresql/data
-- Check replication lagSELECT    client_addr,    state,    sent_lsn,    write_lsn,    flush_lsn,    replay_lsn,    sync_state,    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytesFROM pg_stat_replication;

-- Promote standby to primarypg_ctl promote -D /var/lib/postgresql/data
-- Check replication lagSELECT    client_addr,    state,    sent_lsn,    write_lsn,    flush_lsn,    replay_lsn,    sync_state,    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytesFROM pg_stat_replication;

MongoDB Replication

Replica Set Configuration:

javascript

// Initialize replica setrs.initiate({    _id: "myReplicaSet",    members: [        { _id: 0, host: "mongodb1.example.com:27017", priority: 2 },        { _id: 1, host: "mongodb2.example.com:27017", priority: 1 },        { _id: 2, host: "mongodb3.example.com:27017", priority: 1 }    ]})
// Add member to existing replica setrs.add("mongodb4.example.com:27017")
// Remove memberrs.remove("mongodb4.example.com:27017")
// Check replica set statusrs.status()
// Check replication lagrs.printSecondaryReplicationInfo()

// Initialize replica setrs.initiate({    _id: "myReplicaSet",    members: [        { _id: 0, host: "mongodb1.example.com:27017", priority: 2 },        { _id: 1, host: "mongodb2.example.com:27017", priority: 1 },        { _id: 2, host: "mongodb3.example.com:27017", priority: 1 }    ]})
// Add member to existing replica setrs.add("mongodb4.example.com:27017")
// Remove memberrs.remove("mongodb4.example.com:27017")
// Check replica set statusrs.status()
// Check replication lagrs.printSecondaryReplicationInfo()

Replica Set Roles:

javascript

// Priority 0 member (cannot become primary)rs.add({    host: "analytics.example.com:27017",    priority: 0,    hidden: true // Hidden from application drivers})
// Arbiter (voting only, no data)rs.addArb("arbiter.example.com:27017")
// Delayed member (disaster recovery)rs.add({    host: "delayed.example.com:27017",    priority: 0,    hidden: true,    slaveDelay: 3600 // 1 hour behind})

// Priority 0 member (cannot become primary)rs.add({    host: "analytics.example.com:27017",    priority: 0,    hidden: true // Hidden from application drivers})
// Arbiter (voting only, no data)rs.addArb("arbiter.example.com:27017")
// Delayed member (disaster recovery)rs.add({    host: "delayed.example.com:27017",    priority: 0,    hidden: true,    slaveDelay: 3600 // 1 hour behind})

Read Preference Configuration:

javascript

// Application connection with read preferenceconst client = new MongoClient(uri, {    readPreference: "secondaryPreferred", // Try secondary, fallback to primary    readConcernLevel: "majority"})
// Read Preference Modes:// - primary (default): Read from primary only// - primaryPreferred: Primary if available, else secondary// - secondary: Read from secondary only// - secondaryPreferred: Secondary if available, else primary// - nearest: Read from nearest member (lowest latency)

// Application connection with read preferenceconst client = new MongoClient(uri, {    readPreference: "secondaryPreferred", // Try secondary, fallback to primary    readConcernLevel: "majority"})
// Read Preference Modes:// - primary (default): Read from primary only// - primaryPreferred: Primary if available, else secondary// - secondary: Read from secondary only// - secondaryPreferred: Secondary if available, else primary// - nearest: Read from nearest member (lowest latency)

Sharding

MongoDB Sharding Architecture

Shard Key Selection:

javascript

// Good shard key characteristics:// 1. High cardinality (many distinct values)// 2. Even distribution// 3. Query isolation (queries target specific shards)
// Example: User-based applicationsh.shardCollection("mydb.users", { user_id: "hashed" })
// Hashed shard key: Even distribution, random data locationsh.shardCollection("mydb.events", { event_id: "hashed" })
// Range-based shard key: Ordered data, good for range queriessh.shardCollection("mydb.logs", { timestamp: 1, server_id: 1 })
// Compound shard keysh.shardCollection("mydb.orders", {    customer_region: 1,  // Coarse grouping    order_date: 1        // Fine grouping})

// Good shard key characteristics:// 1. High cardinality (many distinct values)// 2. Even distribution// 3. Query isolation (queries target specific shards)
// Example: User-based applicationsh.shardCollection("mydb.users", { user_id: "hashed" })
// Hashed shard key: Even distribution, random data locationsh.shardCollection("mydb.events", { event_id: "hashed" })
// Range-based shard key: Ordered data, good for range queriessh.shardCollection("mydb.logs", { timestamp: 1, server_id: 1 })
// Compound shard keysh.shardCollection("mydb.orders", {    customer_region: 1,  // Coarse grouping    order_date: 1        // Fine grouping})

Sharding Setup:

javascript

// 1. Start config servers (replica set)mongod --configsvr --replSet configRS --port 27019
// 2. Initialize config server replica setrs.initiate({    _id: "configRS",    configsvr: true,    members: [        { _id: 0, host: "cfg1.example.com:27019" },        { _id: 1, host: "cfg2.example.com:27019" },        { _id: 2, host: "cfg3.example.com:27019" }    ]})
// 3. Start shard servers (each is a replica set)mongod --shardsvr --replSet shard1RS --port 27018
// 4. Start mongos (query router)mongos --configdb configRS/cfg1.example.com:27019,cfg2.example.com:27019
// 5. Add shards to clustersh.addShard("shard1RS/shard1-a.example.com:27018")sh.addShard("shard2RS/shard2-a.example.com:27018")
// 6. Enable sharding on databasesh.enableSharding("mydb")
// 7. Shard collectionssh.shardCollection("mydb.users", { user_id: "hashed" })

// 1. Start config servers (replica set)mongod --configsvr --replSet configRS --port 27019
// 2. Initialize config server replica setrs.initiate({    _id: "configRS",    configsvr: true,    members: [        { _id: 0, host: "cfg1.example.com:27019" },        { _id: 1, host: "cfg2.example.com:27019" },        { _id: 2, host: "cfg3.example.com:27019" }    ]})
// 3. Start shard servers (each is a replica set)mongod --shardsvr --replSet shard1RS --port 27018
// 4. Start mongos (query router)mongos --configdb configRS/cfg1.example.com:27019,cfg2.example.com:27019
// 5. Add shards to clustersh.addShard("shard1RS/shard1-a.example.com:27018")sh.addShard("shard2RS/shard2-a.example.com:27018")
// 6. Enable sharding on databasesh.enableSharding("mydb")
// 7. Shard collectionssh.shardCollection("mydb.users", { user_id: "hashed" })

Query Targeting:

javascript

// Targeted query (includes shard key)db.users.find({ user_id: "12345" })// Routes to single shard
// Scatter-gather query (no shard key)db.users.find({ email: "[email protected]" })// Queries all shards, merges results
// Check query targetingdb.users.find({ user_id: "12345" }).explain()// Look for "SINGLE_SHARD" vs "ALL_SHARDS"

// Targeted query (includes shard key)db.users.find({ user_id: "12345" })// Routes to single shard
// Scatter-gather query (no shard key)db.users.find({ email: "[email protected]" })// Queries all shards, merges results
// Check query targetingdb.users.find({ user_id: "12345" }).explain()// Look for "SINGLE_SHARD" vs "ALL_SHARDS"

Zone Sharding (Geographic Distribution):

javascript

// Define zones for geographic shardingsh.addShardToZone("shard1", "US")sh.addShardToZone("shard2", "EU")
// Define zone rangessh.updateZoneKeyRange(    "mydb.users",    { region: "US", user_id: MinKey },    { region: "US", user_id: MaxKey },    "US")
sh.updateZoneKeyRange(    "mydb.users",    { region: "EU", user_id: MinKey },    { region: "EU", user_id: MaxKey },    "EU")
// Shard collection with zone-aware keysh.shardCollection("mydb.users", { region: 1, user_id: 1 })

// Define zones for geographic shardingsh.addShardToZone("shard1", "US")sh.addShardToZone("shard2", "EU")
// Define zone rangessh.updateZoneKeyRange(    "mydb.users",    { region: "US", user_id: MinKey },    { region: "US", user_id: MaxKey },    "US")
sh.updateZoneKeyRange(    "mydb.users",    { region: "EU", user_id: MinKey },    { region: "EU", user_id: MaxKey },    "EU")
// Shard collection with zone-aware keysh.shardCollection("mydb.users", { region: 1, user_id: 1 })

PostgreSQL Horizontal Partitioning

Declarative Partitioning:

sql

-- Range partitioningCREATE TABLE logs (    id BIGSERIAL,    log_time TIMESTAMP NOT NULL,    message TEXT,    level VARCHAR(10)) PARTITION BY RANGE (log_time);
-- Create partitionsCREATE TABLE logs_2025_01 PARTITION OF logsFOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE logs_2025_02 PARTITION OF logsFOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- List partitioningCREATE TABLE customers (    id SERIAL,    name VARCHAR(255),    region VARCHAR(50)) PARTITION BY LIST (region);
CREATE TABLE customers_us PARTITION OF customersFOR VALUES IN ('US', 'CA', 'MX');
CREATE TABLE customers_eu PARTITION OF customersFOR VALUES IN ('UK', 'DE', 'FR', 'IT');
-- Hash partitioningCREATE TABLE events (    id BIGSERIAL,    event_type VARCHAR(50),    data JSONB) PARTITION BY HASH (id);
CREATE TABLE events_0 PARTITION OF eventsFOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_1 PARTITION OF eventsFOR VALUES WITH (MODULUS 4, REMAINDER 1);-- ... events_2 and events_3

-- Range partitioningCREATE TABLE logs (    id BIGSERIAL,    log_time TIMESTAMP NOT NULL,    message TEXT,    level VARCHAR(10)) PARTITION BY RANGE (log_time);
-- Create partitionsCREATE TABLE logs_2025_01 PARTITION OF logsFOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE logs_2025_02 PARTITION OF logsFOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- List partitioningCREATE TABLE customers (    id SERIAL,    name VARCHAR(255),    region VARCHAR(50)) PARTITION BY LIST (region);
CREATE TABLE customers_us PARTITION OF customersFOR VALUES IN ('US', 'CA', 'MX');
CREATE TABLE customers_eu PARTITION OF customersFOR VALUES IN ('UK', 'DE', 'FR', 'IT');
-- Hash partitioningCREATE TABLE events (    id BIGSERIAL,    event_type VARCHAR(50),    data JSONB) PARTITION BY HASH (id);
CREATE TABLE events_0 PARTITION OF eventsFOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_1 PARTITION OF eventsFOR VALUES WITH (MODULUS 4, REMAINDER 1);-- ... events_2 and events_3

Partition Pruning (Query Optimization):

sql

-- Query automatically uses only relevant partitionSELECT * FROM logsWHERE log_time BETWEEN '2025-01-15' AND '2025-01-20';-- Only scans logs_2025_01 partition
-- Check query planEXPLAIN SELECT * FROM logs WHERE log_time > '2025-01-01';-- Shows which partitions are scanned

-- Query automatically uses only relevant partitionSELECT * FROM logsWHERE log_time BETWEEN '2025-01-15' AND '2025-01-20';-- Only scans logs_2025_01 partition
-- Check query planEXPLAIN SELECT * FROM logs WHERE log_time > '2025-01-01';-- Shows which partitions are scanned

Performance Tuning

Query Optimization Techniques

PostgreSQL Query Analysis:

sql

-- Basic explainEXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- Analyze with actual execution statisticsEXPLAIN ANALYZESELECT u.username, COUNT(p.id) as post_countFROM users uLEFT JOIN posts p ON u.id = p.user_idWHERE u.active = trueGROUP BY u.id, u.usernameORDER BY post_count DESCLIMIT 10;
-- Identify slow queriesSELECT    query,    calls,    total_exec_time,    mean_exec_time,    max_exec_timeFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 20;
-- Table statisticsANALYZE users; -- Update query planner statistics
-- Vacuum and analyzeVACUUM ANALYZE posts; -- Reclaim space and update stats

-- Basic explainEXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- Analyze with actual execution statisticsEXPLAIN ANALYZESELECT u.username, COUNT(p.id) as post_countFROM users uLEFT JOIN posts p ON u.id = p.user_idWHERE u.active = trueGROUP BY u.id, u.usernameORDER BY post_count DESCLIMIT 10;
-- Identify slow queriesSELECT    query,    calls,    total_exec_time,    mean_exec_time,    max_exec_timeFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 20;
-- Table statisticsANALYZE users; -- Update query planner statistics
-- Vacuum and analyzeVACUUM ANALYZE posts; -- Reclaim space and update stats

Common Query Patterns:

sql

-- Avoid SELECT * (retrieve only needed columns)-- BADSELECT * FROM users WHERE id = 123;
-- GOODSELECT id, username, email FROM users WHERE id = 123;
-- Use EXISTS instead of IN for large subqueries-- BADSELECT * FROM posts WHERE author_id IN (    SELECT id FROM users WHERE active = true);
-- GOODSELECT * FROM posts p WHERE EXISTS (    SELECT 1 FROM users u    WHERE u.id = p.author_id AND u.active = true);
-- Use JOINs instead of multiple queries-- BAD (N+1 query problem)-- SELECT * FROM posts;-- Then for each post: SELECT * FROM users WHERE id = post.author_id;
-- GOODSELECT p.*, u.username, u.emailFROM posts pJOIN users u ON p.author_id = u.id;
-- Window functions instead of self-joins-- Calculate running totalSELECT    order_date,    amount,    SUM(amount) OVER (ORDER BY order_date) as running_totalFROM orders;
-- Rank within groupsSELECT    category,    product_name,    sales,    RANK() OVER (PARTITION BY category ORDER BY sales DESC) as rank_in_categoryFROM products;

-- Avoid SELECT * (retrieve only needed columns)-- BADSELECT * FROM users WHERE id = 123;
-- GOODSELECT id, username, email FROM users WHERE id = 123;
-- Use EXISTS instead of IN for large subqueries-- BADSELECT * FROM posts WHERE author_id IN (    SELECT id FROM users WHERE active = true);
-- GOODSELECT * FROM posts p WHERE EXISTS (    SELECT 1 FROM users u    WHERE u.id = p.author_id AND u.active = true);
-- Use JOINs instead of multiple queries-- BAD (N+1 query problem)-- SELECT * FROM posts;-- Then for each post: SELECT * FROM users WHERE id = post.author_id;
-- GOODSELECT p.*, u.username, u.emailFROM posts pJOIN users u ON p.author_id = u.id;
-- Window functions instead of self-joins-- Calculate running totalSELECT    order_date,    amount,    SUM(amount) OVER (ORDER BY order_date) as running_totalFROM orders;
-- Rank within groupsSELECT    category,    product_name,    sales,    RANK() OVER (PARTITION BY category ORDER BY sales DESC) as rank_in_categoryFROM products;

MongoDB Query Optimization:

javascript

// Use projection to limit returned fields// BADdb.users.find({ active: true })
// GOODdb.users.find(    { active: true },    { username: 1, email: 1, _id: 0 })
// Use covered queries (index covers all fields)db.users.createIndex({ username: 1, email: 1 })db.users.find(    { username: "john_doe" },    { username: 1, email: 1, _id: 0 }) // Entire query served from index
// Avoid negation operators// BAD (cannot use index efficiently)db.products.find({ status: { $ne: "discontinued" } })
// GOODdb.products.find({ status: { $in: ["active", "pending", "sold"] } })
// Use $lookup sparingly (expensive operation)// Consider embedding data instead if appropriate
// Aggregation optimization: Filter early// BADdb.orders.aggregate([    { $lookup: { ... } },        // Expensive join    { $match: { status: "completed" } } // Filter after join])
// GOODdb.orders.aggregate([    { $match: { status: "completed" } }, // Filter first    { $lookup: { ... } }                  // Join fewer documents])

// Use projection to limit returned fields// BADdb.users.find({ active: true })
// GOODdb.users.find(    { active: true },    { username: 1, email: 1, _id: 0 })
// Use covered queries (index covers all fields)db.users.createIndex({ username: 1, email: 1 })db.users.find(    { username: "john_doe" },    { username: 1, email: 1, _id: 0 }) // Entire query served from index
// Avoid negation operators// BAD (cannot use index efficiently)db.products.find({ status: { $ne: "discontinued" } })
// GOODdb.products.find({ status: { $in: ["active", "pending", "sold"] } })
// Use $lookup sparingly (expensive operation)// Consider embedding data instead if appropriate
// Aggregation optimization: Filter early// BADdb.orders.aggregate([    { $lookup: { ... } },        // Expensive join    { $match: { status: "completed" } } // Filter after join])
// GOODdb.orders.aggregate([    { $match: { status: "completed" } }, // Filter first    { $lookup: { ... } }                  // Join fewer documents])

Connection Pooling

PostgreSQL Connection Pooling:

javascript

// Using node-postgres (pg) with poolconst { Pool } = require('pg')
const pool = new Pool({    host: 'localhost',    port: 5432,    database: 'mydb',    user: 'dbuser',    password: 'secret',    max: 20,              // Maximum pool size    idleTimeoutMillis: 30000,    connectionTimeoutMillis: 2000})
// Execute queryconst result = await pool.query('SELECT * FROM users WHERE id = $1', [123])
// Use PgBouncer for server-side pooling// pgbouncer.ini// [databases]// mydb = host=localhost port=5432 dbname=mydb//// [pgbouncer]// pool_mode = transaction// max_client_conn = 1000// default_pool_size = 25

// Using node-postgres (pg) with poolconst { Pool } = require('pg')
const pool = new Pool({    host: 'localhost',    port: 5432,    database: 'mydb',    user: 'dbuser',    password: 'secret',    max: 20,              // Maximum pool size    idleTimeoutMillis: 30000,    connectionTimeoutMillis: 2000})
// Execute queryconst result = await pool.query('SELECT * FROM users WHERE id = $1', [123])
// Use PgBouncer for server-side pooling// pgbouncer.ini// [databases]// mydb = host=localhost port=5432 dbname=mydb//// [pgbouncer]// pool_mode = transaction// max_client_conn = 1000// default_pool_size = 25

MongoDB Connection Pooling:

javascript

// MongoClient automatically manages connection poolconst { MongoClient } = require('mongodb')
const client = new MongoClient(uri, {    maxPoolSize: 50,           // Max connections    minPoolSize: 10,           // Min connections    maxIdleTimeMS: 30000,      // Close idle connections    waitQueueTimeoutMS: 5000   // Wait for available connection})
await client.connect()const db = client.db('mydb')// Connection automatically returned to pool after use

// MongoClient automatically manages connection poolconst { MongoClient } = require('mongodb')
const client = new MongoClient(uri, {    maxPoolSize: 50,           // Max connections    minPoolSize: 10,           // Min connections    maxIdleTimeMS: 30000,      // Close idle connections    waitQueueTimeoutMS: 5000   // Wait for available connection})
await client.connect()const db = client.db('mydb')// Connection automatically returned to pool after use

Best Practices

PostgreSQL Best Practices

Schema Design
- Normalize for data integrity, denormalize for performance
- Use appropriate data types (avoid TEXT for short strings)
- Define NOT NULL constraints where appropriate
- Use SERIAL or UUID for primary keys consistently
Indexing
- Index foreign keys for JOIN performance
- Create indexes on frequently filtered/sorted columns
- Use partial indexes for selective queries
- Monitor and remove unused indexes
- Keep composite index column count reasonable (typically ≤ 3-4)
Query Performance
- Use EXPLAIN ANALYZE to understand query plans
- Avoid SELECT * in application code
- Use prepared statements to prevent SQL injection
- Limit result sets with LIMIT
- Use connection pooling
Maintenance
- Run VACUUM regularly (or enable autovacuum)
- Update statistics with ANALYZE
- Monitor slow query log
- Set appropriate autovacuum thresholds
- Regular backup with pg_dump or WAL archiving
Security
- Use SSL/TLS for connections
- Implement row-level security for multi-tenant apps
- Grant minimum necessary privileges
- Use parameterized queries
- Regular security updates

MongoDB Best Practices

Schema Design
- Embed related data that is accessed together
- Reference data that is large or rarely accessed
- Use polymorphic pattern for varied schemas
- Limit document size to reasonable bounds (< 1-2 MB typically)
- Design for your query patterns
Indexing
- Index on fields used in queries and sorts
- Use compound indexes with ESR rule (Equality, Sort, Range)
- Create text indexes for full-text search
- Monitor index usage with $indexStats
- Avoid too many indexes (write performance impact)
Query Performance
- Use projection to limit returned fields
- Create covered queries when possible
- Filter early in aggregation pipelines
- Avoid $lookup when embedding is appropriate
- Use explain() to verify index usage
Scalability
- Choose appropriate shard key (high cardinality, even distribution)
- Use replica sets for high availability
- Configure appropriate read/write concerns
- Monitor chunk distribution in sharded clusters
- Use zones for geographic distribution
Operations
- Enable authentication and authorization
- Use TLS for client connections
- Regular backups (mongodump or filesystem snapshots)
- Monitor with MongoDB Atlas, Ops Manager, or custom tools
- Keep MongoDB version updated

Data Modeling Decision Framework

Choose PostgreSQL when:

Strong ACID guarantees required (financial transactions)
Complex relationships with many JOINs
Data structure is well-defined and stable
Need for advanced SQL features (window functions, CTEs, stored procedures)
Compliance requirements demand strict consistency

Choose MongoDB when:

Schema flexibility needed (rapid development, evolving requirements)
Horizontal scalability is priority (sharding required)
Document-oriented data (JSON/BSON native format)
Hierarchical or nested data structures
High write throughput with eventual consistency acceptable

Hybrid Approach:

Use both databases for different parts of application
PostgreSQL for transactional data (orders, payments)
MongoDB for catalog, logs, user sessions
Synchronize critical data between systems

Common Patterns and Anti-Patterns

PostgreSQL Anti-Patterns

❌ Storing JSON when relational fits better

sql

-- BAD: Using JSONB for structured, queryable dataCREATE TABLE users (    id SERIAL PRIMARY KEY,    data JSONB -- { name, email, address: { street, city, state } });
-- GOOD: Proper normalizationCREATE TABLE users (    id SERIAL PRIMARY KEY,    name VARCHAR(255),    email VARCHAR(255));
CREATE TABLE addresses (    id SERIAL PRIMARY KEY,    user_id INTEGER REFERENCES users(id),    street VARCHAR(255),    city VARCHAR(100),    state VARCHAR(50));

-- BAD: Using JSONB for structured, queryable dataCREATE TABLE users (    id SERIAL PRIMARY KEY,    data JSONB -- { name, email, address: { street, city, state } });
-- GOOD: Proper normalizationCREATE TABLE users (    id SERIAL PRIMARY KEY,    name VARCHAR(255),    email VARCHAR(255));
CREATE TABLE addresses (    id SERIAL PRIMARY KEY,    user_id INTEGER REFERENCES users(id),    street VARCHAR(255),    city VARCHAR(100),    state VARCHAR(50));

❌ Over-indexing

sql

-- BAD: Index on every column "just in case"CREATE INDEX idx1 ON users(username);CREATE INDEX idx2 ON users(email);CREATE INDEX idx3 ON users(created_at);CREATE INDEX idx4 ON users(updated_at);CREATE INDEX idx5 ON users(active);-- Result: Slow writes, large database size
-- GOOD: Index based on actual query patternsCREATE INDEX idx_users_email ON users(email); -- Login queriesCREATE INDEX idx_active_users_created ON users(created_at) WHERE active = true; -- Partial

-- BAD: Index on every column "just in case"CREATE INDEX idx1 ON users(username);CREATE INDEX idx2 ON users(email);CREATE INDEX idx3 ON users(created_at);CREATE INDEX idx4 ON users(updated_at);CREATE INDEX idx5 ON users(active);-- Result: Slow writes, large database size
-- GOOD: Index based on actual query patternsCREATE INDEX idx_users_email ON users(email); -- Login queriesCREATE INDEX idx_active_users_created ON users(created_at) WHERE active = true; -- Partial

❌ N+1 Query Problem

sql

-- BAD: Multiple queries in loopSELECT * FROM posts; -- Returns 100 posts-- Then for each post:SELECT * FROM users WHERE id = ?; -- 100 additional queries!
-- GOOD: Single query with JOINSELECT p.*, u.username, u.emailFROM posts pJOIN users u ON p.author_id = u.id;

-- BAD: Multiple queries in loopSELECT * FROM posts; -- Returns 100 posts-- Then for each post:SELECT * FROM users WHERE id = ?; -- 100 additional queries!
-- GOOD: Single query with JOINSELECT p.*, u.username, u.emailFROM posts pJOIN users u ON p.author_id = u.id;

MongoDB Anti-Patterns

❌ Massive arrays in documents

javascript

// BAD: Unbounded array growth{    _id: ObjectId("..."),    username: "popular_user",    followers: [        ObjectId("follower1"),        ObjectId("follower2"),        // ... 100,000+ follower IDs        // Document exceeds 16MB limit!    ]}
// GOOD: Separate collection with references// users collection{ _id: ObjectId("..."), username: "popular_user" }
// followers collection{ _id: ObjectId("..."), user_id: ObjectId("..."), follower_id: ObjectId("...") }db.followers.createIndex({ user_id: 1, follower_id: 1 })

// BAD: Unbounded array growth{    _id: ObjectId("..."),    username: "popular_user",    followers: [        ObjectId("follower1"),        ObjectId("follower2"),        // ... 100,000+ follower IDs        // Document exceeds 16MB limit!    ]}
// GOOD: Separate collection with references// users collection{ _id: ObjectId("..."), username: "popular_user" }
// followers collection{ _id: ObjectId("..."), user_id: ObjectId("..."), follower_id: ObjectId("...") }db.followers.createIndex({ user_id: 1, follower_id: 1 })

❌ Poor shard key selection

javascript

// BAD: Monotonically increasing shard keysh.shardCollection("mydb.events", { _id: 1 })// All writes go to same shard (highest _id range)
// BAD: Low cardinality shard keysh.shardCollection("mydb.users", { country: 1 })// Most users in few countries = uneven distribution
// GOOD: Hashed _id or compound keysh.shardCollection("mydb.events", { _id: "hashed" }) // Even distributionsh.shardCollection("mydb.users", { country: 1, user_id: 1 }) // Compound

// BAD: Monotonically increasing shard keysh.shardCollection("mydb.events", { _id: 1 })// All writes go to same shard (highest _id range)
// BAD: Low cardinality shard keysh.shardCollection("mydb.users", { country: 1 })// Most users in few countries = uneven distribution
// GOOD: Hashed _id or compound keysh.shardCollection("mydb.events", { _id: "hashed" }) // Even distributionsh.shardCollection("mydb.users", { country: 1, user_id: 1 }) // Compound

❌ Ignoring indexes on embedded documents

javascript

// Document structure{    username: "john_doe",    profile: {        email: "[email protected]",        age: 30,        city: "San Francisco"    }}
// Query on embedded fielddb.users.find({ "profile.email": "[email protected]" })
// MISSING: Index on embedded fielddb.users.createIndex({ "profile.email": 1 })

// Document structure{    username: "john_doe",    profile: {        email: "[email protected]",        age: 30,        city: "San Francisco"    }}
// Query on embedded fielddb.users.find({ "profile.email": "[email protected]" })
// MISSING: Index on embedded fielddb.users.createIndex({ "profile.email": 1 })

Troubleshooting Guide

PostgreSQL Issues

Slow Queries:

sql

-- Enable slow query logging (postgresql.conf)-- log_min_duration_statement = 1000  # Log queries > 1 second
-- Find slow queriesSELECT    query,    calls,    total_exec_time / calls as avg_time_ms,    rows / calls as avg_rowsFROM pg_stat_statementsWHERE calls > 100ORDER BY total_exec_time DESCLIMIT 20;
-- Analyze specific slow queryEXPLAIN (ANALYZE, BUFFERS, VERBOSE)SELECT ... FROM ... WHERE ...;

-- Enable slow query logging (postgresql.conf)-- log_min_duration_statement = 1000  # Log queries > 1 second
-- Find slow queriesSELECT    query,    calls,    total_exec_time / calls as avg_time_ms,    rows / calls as avg_rowsFROM pg_stat_statementsWHERE calls > 100ORDER BY total_exec_time DESCLIMIT 20;
-- Analyze specific slow queryEXPLAIN (ANALYZE, BUFFERS, VERBOSE)SELECT ... FROM ... WHERE ...;

High CPU Usage:

sql

-- Check running queriesSELECT    pid,    now() - query_start as duration,    state,    queryFROM pg_stat_activityWHERE state != 'idle'ORDER BY duration DESC;
-- Terminate long-running querySELECT pg_terminate_backend(pid);

-- Check running queriesSELECT    pid,    now() - query_start as duration,    state,    queryFROM pg_stat_activityWHERE state != 'idle'ORDER BY duration DESC;
-- Terminate long-running querySELECT pg_terminate_backend(pid);

Lock Contention:

sql

-- View locksSELECT    locktype,    relation::regclass,    mode,    granted,    pidFROM pg_locksWHERE NOT granted;
-- Find blocking queriesSELECT    blocked_locks.pid AS blocked_pid,    blocking_locks.pid AS blocking_pid,    blocked_activity.query AS blocked_query,    blocking_activity.query AS blocking_queryFROM pg_locks blocked_locksJOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pidJOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktypeJOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pidWHERE NOT blocked_locks.granted AND blocking_locks.granted;

-- View locksSELECT    locktype,    relation::regclass,    mode,    granted,    pidFROM pg_locksWHERE NOT granted;
-- Find blocking queriesSELECT    blocked_locks.pid AS blocked_pid,    blocking_locks.pid AS blocking_pid,    blocked_activity.query AS blocked_query,    blocking_activity.query AS blocking_queryFROM pg_locks blocked_locksJOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pidJOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktypeJOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pidWHERE NOT blocked_locks.granted AND blocking_locks.granted;

MongoDB Issues

Slow Queries:

javascript

// Enable profilingdb.setProfilingLevel(1, { slowms: 100 }) // Log queries > 100ms
// View slow queriesdb.system.profile.find().sort({ ts: -1 }).limit(10)
// Analyze query performancedb.collection.find({ ... }).explain("executionStats")// Check: totalDocsExamined vs nReturned (should be close)// Check: executionTimeMillis// Check: indexName (should show index usage)

// Enable profilingdb.setProfilingLevel(1, { slowms: 100 }) // Log queries > 100ms
// View slow queriesdb.system.profile.find().sort({ ts: -1 }).limit(10)
// Analyze query performancedb.collection.find({ ... }).explain("executionStats")// Check: totalDocsExamined vs nReturned (should be close)// Check: executionTimeMillis// Check: indexName (should show index usage)

Replication Lag:

javascript

// Check lag on secondaryrs.printSecondaryReplicationInfo()
// Check oplog sizedb.getReplicationInfo()
// Increase oplog size if neededdb.adminCommand({ replSetResizeOplog: 1, size: 16384 }) // 16GB

// Check lag on secondaryrs.printSecondaryReplicationInfo()
// Check oplog sizedb.getReplicationInfo()
// Increase oplog size if neededdb.adminCommand({ replSetResizeOplog: 1, size: 16384 }) // 16GB

Sharding Issues:

javascript

// Check chunk distributionsh.status()
// Check balancer statussh.getBalancerState()sh.isBalancerRunning()
// Balance specific collectionsh.enableBalancing("mydb.mycollection")
// Check for jumbo chunksdb.chunks.find({ jumbo: true })

// Check chunk distributionsh.status()
// Check balancer statussh.getBalancerState()sh.isBalancerRunning()
// Balance specific collectionsh.enableBalancing("mydb.mycollection")
// Check for jumbo chunksdb.chunks.find({ jumbo: true })

Resources

PostgreSQL Resources

Official Documentation: https://www.postgresql.org/docs/
PostgreSQL Wiki: https://wiki.postgresql.org/
Performance Tuning: https://wiki.postgresql.org/wiki/Performance_Optimization
Explain Visualizer: https://explain.dalibo.com/
pg_stat_statements Extension: Essential for query analysis

MongoDB Resources

Official Documentation: https://docs.mongodb.com/
MongoDB University: Free courses and certification
Aggregation Framework: https://docs.mongodb.com/manual/aggregation/
Sharding Guide: https://docs.mongodb.com/manual/sharding/
Schema Design Patterns: https://www.mongodb.com/blog/post/building-with-patterns-a-summary

Books

PostgreSQL: "PostgreSQL: Up and Running" by Regina Obe & Leo Hsu
MongoDB: "MongoDB: The Definitive Guide" by Shannon Bradshaw, Eoin Brazil, Kristina Chodorow

Skill Version: 1.0.0 Last Updated: January 2025 Skill Category: Database Management, Data Architecture, Performance Optimization Technologies: PostgreSQL 16+, MongoDB 7+

來源與署名

來源：manutej/luxor-claude-marketplace位於plugins/luxor-database-pro/skills/database-management-patterns提交1861cc9

授權條款：無授權條款

內容歸原作者所有。SourceWeft 從公開儲存庫中收錄這些內容。

檢舉或申請下架

Database Management Patterns

Database Management Patterns

When to Use This Skill

Core Concepts

Database Paradigms Comparison

Relational Databases (PostgreSQL)

Document Databases (MongoDB)

ACID Properties

CAP Theorem

PostgreSQL Patterns

Schema Design Fundamentals

Normalization Levels

Table Design Patterns

Advanced Constraints

Triggers and Functions

Views and Materialized Views

MongoDB Patterns

Document Modeling Strategies

Embedding vs Referencing

Schema Design Patterns

Aggregation Framework

Indexing Strategies

PostgreSQL Indexes

MongoDB Indexes

Transactions

PostgreSQL Transaction Management

MongoDB Transactions

Replication

PostgreSQL Replication

MongoDB Replication

Sharding

MongoDB Sharding Architecture

PostgreSQL Horizontal Partitioning

Performance Tuning

Query Optimization Techniques

Connection Pooling

Best Practices

PostgreSQL Best Practices

MongoDB Best Practices

Data Modeling Decision Framework

Common Patterns and Anti-Patterns

PostgreSQL Anti-Patterns

MongoDB Anti-Patterns

Troubleshooting Guide

PostgreSQL Issues

MongoDB Issues

Resources

PostgreSQL Resources

MongoDB Resources

Books

來源與署名

更多來自 manutej/luxor-claude-marketplace 的技能

Ui Design Patterns

Tailwind Css

Responsive Design

React Patterns

React Development

Shell Testing Framework

更多Software Development技能

Playground

M5 Onboard

Cardputer Buddy

Web Design Guidelines

Vercel React Native Skills

Vercel React Best Practices