Database Optimization: Real-World Performance Improvements

Understanding the Fundamental Differences

Before diving into optimization, it's crucial to understand that PostgreSQL and MongoDB are fundamentally different beasts:

Performance Benchmarks: Real-World Scenarios

Let's examine how both databases perform under different workload types. These metrics are based on real-world production environments with standard hardware configurations:

Scenario 1: E-Commerce Product Catalog (Read-Heavy)

MongoDB's document model suits product catalogs well, especially when products have varying attributes. The faster response times for document-based queries become noticeable during peak traffic - those milliseconds add up when serving thousands of concurrent users.

Scenario 2: Financial Transactions (Write-Heavy with Consistency)

PostgreSQL's mature transaction handling and full ACID compliance make it the safer choice for financial data. The lower transaction latencies and guaranteed consistency are critical for financial operations where every millisecond and every transaction counts.

PostgreSQL Optimization Deep Dive

1. Query Optimization

The foundation of PostgreSQL performance is efficient queries. Here's a real example from a production system:

SELECT o.*, c.name, c.email, 
       array_agg(oi.*) as items
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at > NOW() - INTERVAL '30 days'
GROUP BY o.id, c.name, c.email;

WITH recent_orders AS (
  SELECT * FROM orders 
  WHERE created_at > NOW() - INTERVAL '30 days'
  AND created_at <= NOW()
)
SELECT o.*, c.name, c.email,
       (SELECT json_agg(oi.*) 
        FROM order_items oi 
        WHERE oi.order_id = o.id) as items
FROM recent_orders o
JOIN customers c ON o.customer_id = c.id;

Key optimizations applied:

• Used CTE to limit the dataset early
• Replaced array_agg with json_agg in a subquery
• Added upper bound to timestamp comparison
• Ensured proper indexes exist

2. Index Strategy

Effective indexing is crucial for PostgreSQL performance:

-- Partial index for active records
CREATE INDEX idx_orders_recent_active 
ON orders (created_at, customer_id) 
WHERE status = 'active' 
AND created_at > '2025-01-01';

-- Covering index to avoid table lookups
CREATE INDEX idx_customers_email_include 
ON customers (email) 
INCLUDE (name, phone, address);

-- BRIN index for time-series data
CREATE INDEX idx_logs_created_brin 
ON logs USING BRIN (created_at) 
WITH (pages_per_range = 128);

-- GIN index for JSONB queries
CREATE INDEX idx_products_attributes 
ON products USING GIN (attributes);

-- Bloom filter for multi-column equality checks
CREATE EXTENSION bloom;
CREATE INDEX idx_orders_bloom 
ON orders USING bloom (customer_id, product_id, status)
WITH (length=80, col1=2, col2=2, col3=4);

3. Configuration Tuning

PostgreSQL's default configuration is conservative. Here's a configuration we've used successfully on dedicated servers with dual Xeon processors (16 cores, 64GB RAM):

# Memory Settings
shared_buffers = 16GB              # 25% of RAM
effective_cache_size = 48GB        # 75% of RAM  
work_mem = 128MB                   # Per operation - be careful!
maintenance_work_mem = 2GB         # For VACUUM, INDEX

# Write Performance
checkpoint_timeout = 30min
checkpoint_completion_target = 0.9
wal_buffers = 64MB
max_wal_size = 16GB

# Query Planning
random_page_cost = 1.1             # For SSD
effective_io_concurrency = 200     # For SSD
default_statistics_target = 500    # More accurate plans

# Connection Management
max_connections = 200
connection_pooling = on            # Via PgBouncer

# Parallel Query Execution
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
parallel_leader_participation = on

4. Partitioning Strategy

For large tables, partitioning is essential:

-- Range partitioning for time-series data
CREATE TABLE events (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    event_type TEXT,
    payload JSONB
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- Automated partition creation
CREATE OR REPLACE FUNCTION create_monthly_partition()
RETURNS void AS $$
DECLARE
    start_date DATE;
    end_date DATE;
    partition_name TEXT;
BEGIN
    start_date := DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 month');
    end_date := start_date + INTERVAL '1 month';
    partition_name := 'events_' || TO_CHAR(start_date, 'YYYY_MM');
    
    EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF events 
                    FOR VALUES FROM (%L) TO (%L)',
                    partition_name, start_date, end_date);
END;
$$ LANGUAGE plpgsql;

-- Schedule monthly execution
SELECT cron.schedule('create-partitions', '0 0 25 * *', 
                     'SELECT create_monthly_partition()');

MongoDB Optimization Deep Dive

1. Schema Design for Performance

MongoDB's flexible schema is both a blessing and a curse. Here's how to design for performance:

// Orders collection
{
  _id: ObjectId("..."),
  customer_id: ObjectId("..."),
  items: [
    { product_id: ObjectId("..."), quantity: 2 },
    { product_id: ObjectId("..."), quantity: 1 }
  ]
}

// Requires multiple queries and $lookup operations

// Orders collection with embedded data
{
  _id: ObjectId("..."),
  customer: {
    _id: ObjectId("..."),
    name: "John Doe",
    email: "[email protected]"
  },
  items: [
    {
      product_id: ObjectId("..."),
      name: "Widget",
      price: 29.99,
      quantity: 2
    }
  ],
  total: 89.97,
  created_at: ISODate("2025-05-20T10:30:00Z")
}

2. Index Optimization

MongoDB indexing strategies for different use cases:

// Compound index for sort operations
db.orders.createIndex({ 
  "customer._id": 1, 
  "created_at": -1 
})

// Text index for search
db.products.createIndex({ 
  name: "text", 
  description: "text" 
})

// Wildcard index for flexible queries
db.events.createIndex({ "payload.$**": 1 })

// Partial index to save space
db.users.createIndex(
  { email: 1 },
  { partialFilterExpression: { status: "active" } }
)

// TTL index for automatic cleanup
db.sessions.createIndex(
  { "expireAt": 1 },
  { expireAfterSeconds: 0 }
)

// Geospatial index
db.locations.createIndex({ coordinates: "2dsphere" })

3. Aggregation Pipeline Optimization

The aggregation pipeline is powerful but can be slow if not optimized:

// Inefficient pipeline
db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "_id",
      as: "customer"
    }
  },
  { $unwind: "$customer" },
  { $match: { "customer.country": "USA" } },
  { $sort: { created_at: -1 } },
  { $limit: 100 }
])

// Optimized pipeline
db.orders.aggregate([
  // Early filtering
  {
    $match: {
      created_at: { $gte: ISODate("2025-01-01") }
    }
  },
  // Use index for sort
  { $sort: { created_at: -1 } },
  { $limit: 100 },
  // Lookup only for final results
  {
    $lookup: {
      from: "customers",
      let: { cust_id: "$customer_id" },
      pipeline: [
        { $match: { 
          $expr: { $eq: ["$_id", "$$cust_id"] },
          country: "USA"
        }}
      ],
      as: "customer"
    }
  },
  { $unwind: "$customer" }
], { allowDiskUse: true })

4. Sharding Strategy

For horizontal scaling, proper sharding is crucial:

// Enable sharding on database
sh.enableSharding("myapp")

// Choose shard key carefully
// Good: High cardinality, even distribution
sh.shardCollection(
  "myapp.events",
  { customer_id: "hashed" }
)

// Better: Compound shard key for range queries
sh.shardCollection(
  "myapp.timeseries",
  { sensor_id: 1, timestamp: 1 }
)

// Configure zones for geo-distribution
sh.addShardTag("shard0", "US")
sh.addShardTag("shard1", "EU")
sh.addShardTag("shard2", "ASIA")

sh.addTagRange(
  "myapp.users",
  { region: "US", _id: MinKey },
  { region: "US", _id: MaxKey },
  "US"
)

Advanced Optimization Techniques

PostgreSQL: Advanced Features

1. Parallel Query Execution

-- Force parallel execution for testing
SET max_parallel_workers_per_gather = 4;
SET parallel_tuple_cost = 0;
SET parallel_setup_cost = 0;

-- Monitor parallel execution
SELECT query, mean_exec_time, calls, 
       total_exec_time/calls as avg_time,
       max_exec_time
FROM pg_stat_statements
WHERE query LIKE '%parallel%'
ORDER BY mean_exec_time DESC;

2. Just-in-Time Compilation

-- Enable JIT for complex queries
SET jit = on;
SET jit_above_cost = 100000;
SET jit_inline_above_cost = 500000;
SET jit_optimize_above_cost = 500000;

MongoDB: Advanced Features

1. Change Streams for Real-Time

// Efficient change stream with resumability
const pipeline = [
  { $match: { 
    "fullDocument.status": "pending",
    operationType: { $in: ["insert", "update"] }
  }},
  { $project: {
    "fullDocument.sensitive_data": 0
  }}
];

const options = {
  fullDocument: "updateLookup",
  resumeAfter: resumeToken
};

const changeStream = db.orders.watch(pipeline, options);

changeStream.on("change", async (change) => {
  // Process change
  await processOrder(change.fullDocument);
  // Store resume token
  await saveResumeToken(change._id);
});

2. Read Preference Optimization

// Configure read preferences for different workloads
// Analytics queries - read from secondary
db.orders.find().readPref("secondary", [
  { region: "us-east-1" }
])

// Real-time dashboards - read from nearest
db.metrics.find().readPref("nearest", [], {
  maxStalenessSeconds: 90
})

// Critical reads - primary only
db.accounts.find().readPref("primary")

Monitoring and Troubleshooting

PostgreSQL Monitoring

-- Top slow queries
SELECT query, mean_exec_time, calls, 
       total_exec_time, min_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Table bloat check
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as external_size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

-- Lock monitoring
SELECT pid, usename, pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

MongoDB Monitoring

// Performance statistics
db.runCommand({ serverStatus: 1 }).opcounters

// Current operations
db.currentOp({
  "active": true,
  "secs_running": { "$gt": 5 }
})

// Collection statistics
db.orders.stats({
  indexDetails: true,
  indexDetailsKey: { customer_id: 1, created_at: -1 }
})

// Profiler for slow queries
db.setProfilingLevel(1, { slowms: 100 })
db.system.profile.find().limit(10).sort({ ts: -1 })

When to Choose PostgreSQL vs MongoDB

Choose PostgreSQL When:

• ACID compliance is non-negotiable
• Complex relationships between entities
• Need for complex queries with joins
• Strong consistency requirements
• Mature ecosystem and tooling needed
• Team has SQL expertise

Choose MongoDB When:

• Document-oriented data model fits naturally
• Need for flexible, evolving schemas
• Horizontal scaling is a primary concern
• Working with semi-structured data
• Real-time analytics with aggregation pipeline
• Geographic distribution requirements

Hybrid Approaches: Best of Both Worlds

Many successful architectures use both databases:

Future Considerations

Both databases continue to evolve:

PostgreSQL Roadmap:

• Improved parallelism and JIT compilation
• Better JSON/JSONB performance
• Native columnar storage
• Enhanced logical replication

MongoDB Evolution:

• Improved transaction performance
• Better SQL compatibility
• Enhanced time-series collections
• Queryable encryption

Conclusion

The PostgreSQL vs MongoDB debate isn't about which is better—it's about which is better for your specific use case. Both are excellent databases that, when properly optimized, can handle massive scale and deliver outstanding performance.

The key to success lies in:

• Understanding your data model and access patterns
• Choosing the right database for each workload
• Implementing proper optimization strategies
• Continuous monitoring and tuning
• Being willing to use multiple databases when appropriate

Remember, the best database is the one that solves your problem efficiently, scales with your needs, and your team can operate effectively. Sometimes that's PostgreSQL, sometimes it's MongoDB, and often it's both.