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

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: Foreign Key Constraints: Advanced Constraints Check Constraints: Unique Constraints: Triggers and Functions Audit Trail Pattern: Timestamp Update Pattern: Views and Materialized Views Standard Views: Materialized Views: MongoDB Patterns Document Modeling Strategies Embedding vs Referencing Embedding Pattern (Denormalization): Referencing Pattern (Normalization): Hybrid Approach (Selective Denormalization): Schema Design Patterns Bucket Pattern (Time Series Data): Computed Pattern (Pre Aggregated Data): Polymorphic Pattern (Varied Schemas): Aggregation Framework Basic Aggregation Pipeline: Advanced Pipeline with Lookup (Join): Aggregation with Grouping and Reshaping: Indexing Strategies PostgreSQL Indexes B tree Indexes (Default): Partial Indexes: Expression Indexes: Full Text Search Indexes: JSONB Indexes: Index Monitoring: MongoDB Indexes Single Field Indexes: Compound Indexes: Multikey Indexes (Array Fields): Text Indexes: Geospatial Indexes: Index Properties: Index Analysis: Transactions PostgreSQL Transaction Management Basic Transactions: Savepoints (Partial Rollback): Isolation Levels: Advisory Locks (Application Level Locking): Row Level Locking: MongoDB Transactions Multi Document Transactions: Read and Write Concerns: Atomic Operations (Single Document): Replication PostgreSQL Replication Streaming Replication (Primary Standby): Logical Replication (Selective Replication): Failover and Promotion: MongoDB Replication Replica Set Configuration: Replica Set Roles: Read Preference Configuration: Sharding MongoDB Sharding Architecture Shard Key Selection: Sharding Setup: Query Targeting: Zone Sharding (Geographic Distribution): PostgreSQL Horizontal Partitioning Declarative Partitioning: Partition Pruning (Query Optimization): Performance Tuning Query Optimization Techniques PostgreSQL Query Analysis: Common Query Patterns: MongoDB Query Optimization: Connection Pooling PostgreSQL Connection Pooling: MongoDB Connection Pooling: Best Practices PostgreSQL Best Practices 1. 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 2. 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) 3. 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 4. 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 5. 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 1. 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 2. 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) 3. 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 4. 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 5. 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 ❌ Over indexing ❌ N+1 Query Problem MongoDB Anti Patterns ❌ Massive arrays in documents ❌ Poor shard key selection ❌ Ignoring indexes on embedded documents Troubleshooting Guide PostgreSQL Issues Slow Queries: High CPU Usage: Lock Contention: MongoDB Issues Slow Queries: Replication Lag: Sharding Issues: 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+

database-management-patterns