PostgreSQL Sharding with GORM: Deep Technical Guide

At some scale, a single PostgreSQL instance eventually hits hard limits: CPU, IOPS, storage, and connection pressure. Vertical scaling buys time, but sustained growth usually requires horizontal data distribution. That is where sharding becomes relevant.

In this post, we cover PostgreSQL sharding design and practical implementation patterns in Go with GORM.

What Is Sharding?

Sharding splits data horizontally across multiple independent database nodes. Each node stores only a subset of records. The application (or a router layer) decides which shard should handle each request.

Diagram: Router and Shards

                    +-----------------------+
                    |   API / Service       |
                    +-----------+-----------+
                                |
                      compute shard key
                                |
                    +-----------v-----------+
                    |   Query Router        |
                    | (hash/range lookup)   |
                    +----+----------+-------+
                         |          |
              +----------+--+    +--+----------+
              |  Shard-01  |    |  Shard-02   |
              | PostgreSQL |    | PostgreSQL  |
              +------+-----+    +------+------+
                     |                 |
                 +---v---+         +---v---+
                 | Read  |         | Read  |
                 |Replica|         |Replica|
                 +-------+         +-------+

Unlike partitioning, there is no single shared storage engine for all data.

When Sharding Is a Better Fit than Partitioning

Partitioning and sharding solve different scaling constraints:

• Partitioning: Intra-cluster data organization and pruning
• Sharding: Inter-cluster horizontal scale and isolation

Sharding becomes a strong option when:

• A single node repeatedly approaches hardware limits
• Write load saturates one instance
• Multi-tenant isolation is required
• You want smaller failure domains

Shard Key Design (Most Critical Decision)

A poor shard key can permanently limit your architecture. A good key should provide:

• High cardinality
• Even distribution
• Alignment with dominant query patterns
• Reasonable re-sharding path

Common strategies

1. Tenant-ID based
   • Very common in SaaS
   • Strong isolation and operability
2. Hash of user_id
   • Good distribution in consumer workloads
3. Time-based key
   • Works for specific event streams, but hotspot-prone

Diagram: Hash-Based Routing

shard = hash(tenant_id) % N

tenant_1001 -> hash -> 1 -> shard_1
tenant_1002 -> hash -> 0 -> shard_0
tenant_1003 -> hash -> 3 -> shard_3
tenant_1004 -> hash -> 1 -> shard_1

If N changes, mapping can break badly. Consistent hashing or virtual nodes are common mitigations.

Schema Pattern per Shard

Each shard typically hosts the same schema:

CREATE TABLE orders (
  id           bigserial PRIMARY KEY,
  tenant_id    bigint NOT NULL,
  customer_id  bigint NOT NULL,
  amount       numeric(12,2) NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_tenant_created
ON orders (tenant_id, created_at DESC);

A critical application rule: avoid queries without the shard key whenever possible.

Using GORM with Sharded PostgreSQL

GORM does not magically distribute records by itself. In most production systems, the service layer chooses the target *gorm.DB based on shard logic.

1) Shard manager

type ShardManager struct {
    shards []*gorm.DB
}

func (sm *ShardManager) shardIndex(tenantID int64) int {
    h := fnv.New32a()
    _, _ = h.Write([]byte(strconv.FormatInt(tenantID, 10)))
    return int(h.Sum32()) % len(sm.shards)
}

func (sm *ShardManager) DBForTenant(tenantID int64) *gorm.DB {
    return sm.shards[sm.shardIndex(tenantID)]
}

2) Repository-level routing

type Order struct {
    ID         int64     `gorm:"primaryKey"`
    TenantID   int64     `gorm:"index"`
    CustomerID int64
    Amount     float64
    CreatedAt  time.Time
}

type OrderRepo struct {
    shardManager *ShardManager
}

func (r *OrderRepo) Create(ctx context.Context, o *Order) error {
    db := r.shardManager.DBForTenant(o.TenantID).WithContext(ctx)
    return db.Create(o).Error
}

func (r *OrderRepo) ListByTenant(ctx context.Context, tenantID int64, limit int) ([]Order, error) {
    db := r.shardManager.DBForTenant(tenantID).WithContext(ctx)
    var out []Order
    err := db.Where("tenant_id = ?", tenantID).
        Order("created_at desc").
        Limit(limit).
        Find(&out).Error
    return out, err
}

This pattern keeps shard routing explicit and testable.

Cross-Shard Query Trade-offs

Cross-shard queries are the hardest part operationally. Example: global monthly revenue across all tenants.

Typical approaches:

• Parallel fan-out from app layer + aggregate
• ETL into analytics warehouse
• Precomputed summary tables

Do not overload OLTP shards with heavy analytics by default.

Transactions and Consistency

Single-shard transactions are straightforward. Multi-shard transactions are distributed-systems problems.

Practical patterns:

• Keep critical business workflows shard-local
• Use outbox/SAGA patterns
• Build idempotent operations and retries

Avoid assuming cheap global ACID semantics across shards.

Connection Pooling and Capacity Math

As shard count grows, total connections can explode.

Tune per-shard pools (MaxOpenConns, MaxIdleConns, lifetimes) and validate aggregate capacity:

total_conn = shard_count * service_instances * max_open_conns

This must stay inside PostgreSQL and infrastructure limits.

Migration from Single DB to Shards

A safer migration path:

1. Introduce shard-map and routing layer
2. Onboard new tenants directly to shards
3. Migrate existing tenants in batches
4. Validate with counts/checksums/business invariants
5. Cut over and retire legacy path

Incremental migration usually beats big-bang migration on risk and rollback ability.

Observability Checklist

• Per-shard P95/P99 latency
• Per-shard QPS and error rate
• Hot-shard detection (skew)
• Replica lag
• Connection saturation

In practice, sharding performance is often limited by the hottest shard, not average shard metrics.

Common Mistakes

• Choosing shard key without query-pattern analysis
• Underestimating cross-shard query volume
• Ignoring re-sharding strategy early
• Missing shard-level alerting and runbooks

Conclusion

PostgreSQL sharding is a powerful way to move beyond single-node limits, but it requires disciplined architecture decisions: key selection, routing, consistency boundaries, and strong operations.

With a clean shard router + repository pattern in GORM, you can keep code maintainable while scaling data safely.