-- Create parent table
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE,
    amount DECIMAL,
    region VARCHAR(50)
) PARTITION BY RANGE (sale_date);

-- Create partitions
CREATE TABLE sales_2024_q1 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE sales_2024_q2 PARTITION OF sales
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

Foreign Data Wrappers (FDW)

FDW enables PostgreSQL to query data from remote servers:

-- Create foreign server
CREATE SERVER shard1_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'shard1.example.com', port '5432', dbname 'mydb');

-- Create user mapping
CREATE USER MAPPING FOR current_user
    SERVER shard1_server
    OPTIONS (user 'postgres', password 'password');

-- Create foreign table
CREATE FOREIGN TABLE remote_users (
    id INTEGER,
    name VARCHAR(100),
    email VARCHAR(100)
) SERVER shard1_server
OPTIONS (schema_name 'public', table_name 'users');

Third-Party Solutions

Several tools extend PostgreSQL’s sharding capabilities:

• Citus: Distributed PostgreSQL extension
• Postgres-XL: Multi-master cluster solution
• Greenplum: Massively parallel processing database

MongoDB Sharding Architecture

MongoDB provides native, automatic sharding capabilities built into the database engine.

Core Components

• Shard: Individual MongoDB instances storing data subsets
• Config Servers: Store cluster metadata and configuration
• Query Router (mongos): Route client requests to appropriate shards

Setting Up MongoDB Sharding

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

// Shard a collection
sh.shardCollection("myapp.users", { "user_id": 1 })

// Check sharding status
sh.status()

Shard Key Selection

MongoDB requires careful shard key selection:

// Good shard key - high cardinality, even distribution
sh.shardCollection("myapp.orders", { "customer_id": 1, "order_date": 1 })

// Poor shard key - low cardinality
sh.shardCollection("myapp.products", { "category": 1 }) // Avoid this

PostgreSQL vs MongoDB Sharding Comparison

Implementation Complexity

Performance Characteristics

PostgreSQL Advantages:

• Superior complex query performance
• ACID compliance across transactions
• Mature optimizer for analytical workloads

MongoDB Advantages:

• Automatic chunk migration and balancing
• Better horizontal scaling for simple operations
• Native support for geographically distributed shards

Scalability Patterns

-- PostgreSQL: Manual shard routing in application
function getUserData(userId) {
    const shardId = userId % NUM_SHARDS;
    const connection = getShardConnection(shardId);
    return connection.query('SELECT * FROM users WHERE id = $1', [userId]);
}

// MongoDB: Transparent routing
db.users.find({ user_id: 12345 }) // Automatically routed to correct shard

Data Consistency

• PostgreSQL: Strong consistency within shards, eventual consistency across shards
• MongoDB: Configurable consistency levels, strong consistency by default

Best Practices and Recommendations

When to Choose PostgreSQL Sharding

• Complex analytical queries and reporting
• Strong ACID requirements
• Existing PostgreSQL expertise
• Need for advanced SQL features

When to Choose MongoDB Sharding

• Rapid horizontal scaling requirements
• Document-oriented data models
• Geographically distributed applications
• Simplified operational overhead

Implementation Guidelines

PostgreSQL Sharding Best Practices:

• Design shard keys for even data distribution
• Implement connection pooling and routing logic
• Monitor shard performance and rebalance manually
• Use read replicas for scaling read operations

MongoDB Sharding Best Practices:

• Choose compound shard keys for better distribution
• Monitor chunk distribution and migration
• Configure appropriate read/write concerns
• Plan for shard key immutability

Conclusion

Both PostgreSQL and MongoDB offer viable sharding solutions, each with distinct advantages. PostgreSQL sharding provides flexibility and powerful SQL capabilities but requires more manual implementation effort. MongoDB delivers native sharding with automatic management features, making it easier to operate at scale.

The choice between these approaches depends on your specific requirements: data model complexity, consistency needs, operational expertise, and scaling patterns. Organizations with complex relational data and analytical workloads may prefer PostgreSQL’s approach, while those prioritizing rapid scaling and operational simplicity might find MongoDB’s native sharding more suitable.

Consider starting with vertical scaling and built-in partitioning features before implementing full sharding solutions, as the operational complexity of distributed databases requires careful planning and expertise.