Merancang Multi-Tenant System dengan Go: Dari Isolasi Database sampai Shared Pool

Bikin sistem yang dipakai 1 company itu easy. Bikin sistem yang dipakai 100 company sekaligus — masing-masing dengan konfigurasi, role, dan data yang terisolasi — itu problema arsitektur yang berbeda class.

Shared DB vs Dedicated Schema — Trade-offs

Sebelum masuk technical details, kita perlu decision paling fundamental di multi-tenant: bagaimana cara kita isolate data antar tenants?

Ada 3 pendekatan utama:

• Shared database, shared schema (row-level isolation) — semua tenants share tabel yang sama, data dipisahkan via tenant_id column
• Shared database, separate schema — setiap tenant dapat schema sendiri, isolate via PostgreSQL schema
• Separate database per tenant — setiap tenant dapat database instance sendiri

Masing-masing punya trade-offs yang perlu dipahami:

Di KoinWorks, kami pakai shared database dengan row-level isolation karena cost efficiency penting untuk SME segment. Di PrivyID, pakai schema per tenant untuk compliance requirement yang lebih ketat.

"Tidak ada yang perfect — yang ada adalah yang paling sesuai dengan constraint bisnis kamu. Cost, compliance, dan operational capacity."

Data Isolation Strategy: Row-Level Security

Untuk PostgreSQL, Row-Level Security (RLS) adalah tool yang powerful untuk enforce tenant isolation di level database. Dengan RLS, policy di database memastikan query hanya bisa akses data milik tenant yang authorized.

-- Enable Row-Level Security pada tabel
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: user hanya bisa lihat orders milik tenant-nya
CREATE POLICY tenant_isolation_policy ON orders
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Policy untuk INSERT — auto-set tenant_id
CREATE POLICY tenant_insert_policy ON orders
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

-- Policy untuk UPDATE/DELETE
CREATE POLICY tenant_update_policy ON orders
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant')::uuid)
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

Di application layer, kita set tenant context via PostgreSQL session settings:

package postgres

import (
    "context"
    "database/sql"
    "fmt"
)

type TenantDB struct {
    db        *sql.DB
    tenantID  string
}

func NewTenantDB(db *sql.DB, tenantID string) *TenantDB {
    return &TenantDB{
        db:       db,
        tenantID: tenantID,
    }
}

// ExecContext executes query dengan tenant context
func (t *TenantDB) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error) {
    // Set tenant context sebelum execute
    ctx = context.WithValue(ctx, "tenant_id", t.tenantID)

    // Set PostgreSQL session variable untuk RLS
    _, err := t.db.ExecContext(ctx, "SET app.current_tenant = $1", t.tenantID)
    if err != nil {
        return nil, fmt.Errorf("failed to set tenant context: %w", err)
    }

    return t.db.ExecContext(ctx, query, args...)
}

// QueryContext dengan tenant context
func (t *TenantDB) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) {
    _, err := t.db.ExecContext(ctx, "SET app.current_tenant = $1", t.tenantID)
    if err != nil {
        return nil, fmt.Errorf("failed to set tenant context: %w", err)
    }
    return t.db.QueryContext(ctx, query, args...)
}

Tenant Context Propagation di Go

Tenant context harus propagate through entire request lifecycle. Di Go, kita pakai context package yang sudah disediakan:

package tenant

import (
    "context"
    "errors"
)

type contextKey string
const tenantIDKey contextKey = "tenant_id"

var ErrNoTenant = errors.New("tenant context not found")

// WithTenant adds tenant ID to context
func WithTenant(ctx context.Context, tenantID string) context.Context {
    return context.WithValue(ctx, tenantIDKey, tenantID)
}

// FromContext extracts tenant ID from context
func FromContext(ctx context.Context) (string, error) {
    if tenantID, ok := ctx.Value(tenantIDKey).(string); ok && tenantID != "" {
        return tenantID, nil
    }
    return "", ErrNoTenant
}

// MustFromContext extracts tenant ID, panics if not found
func MustFromContext(ctx context.Context) string {
    tenantID, err := FromContext(ctx)
    if err != nil {
        panic("tenant context is required")
    }
    return tenantID
}

Untuk HTTP services, kita buat middleware yang extract tenant dari JWT atau header:

package middleware

func TenantMiddleware(next http.Handler) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        // Extract tenant ID dari JWT claims atau header
        tenantID := extractTenantID(r)

        if tenantID == "" {
            http.Error(w, "Unauthorized: missing tenant", http.StatusUnauthorized)
            return
        }

        // Validate tenant exists and is active
        if !isValidTenant(tenantID) {
            http.Error(w, "Forbidden: invalid tenant", http.StatusForbidden)
            return
        }

        // Add to context and continue
        ctx := tenant.WithTenant(r.Context(), tenantID)
        next.ServeHTTP(w, r.WithContext(ctx))
    })
}

func extractTenantID(r *http.Request) string {
    // Option 1: dari JWT claims
    if claims := getJWTClaims(r); claims != nil {
        return claims.TenantID
    }

    // Option 2: dari header (untuk service-to-service)
    if tenantID := r.Header.Get("X-Tenant-ID"); tenantID != "" {
        return tenantID
    }

    return ""
}

Di service layer, setiap query otomatis ter-filter berdasarkan tenant:

package service

import (
    "context"

    "github.com/indodax/multi-tenant/internal/domain/entity"
    "github.com/indodax/multi-tenant/internal/domain/repository"
    "github.com/indodax/multi-tenant/pkg/tenant"
)

type OrderService struct {
    orderRepo repository.OrderRepository
}

func NewOrderService(orderRepo repository.OrderRepository) *OrderService {
    return &OrderService{orderRepo: orderRepo}
}

func (s *OrderService) CreateOrder(ctx context.Context, input CreateOrderInput) (*entity.Order, error) {
    // Ambil tenant ID dari context — sudah di-set oleh middleware
    tenantID, err := tenant.FromContext(ctx)
    if err != nil {
        return nil, err
    }

    order := &entity.Order{
        TenantID:  tenantID, // Auto-set dari context
        ProductID: input.ProductID,
        Quantity:  input.Quantity,
        Status:    entity.OrderStatusPending,
    }

    if err := s.orderRepo.Save(ctx, order); err != nil {
        return nil, err
    }

    return order, nil
}

func (s *OrderService) ListOrders(ctx context.Context, filter ListOrderFilter) ([]*entity.Order, error) {
    tenantID, err := tenant.FromContext(ctx)
    if err != nil {
        return nil, err
    }

    filter.TenantID = tenantID // Auto-inject tenant ID
    return s.orderRepo.List(ctx, filter)
}

RBAC Multi-Tenant

Role-Based Access Control di multi-tenant environment butuh careful design. Ada 2 level permissions:

1. Tenant-level roles — apa yang bisa dilakukan dalam tenant ini (admin, manager, user)
2. Platform-level roles — apa yang bisa dilakukan terhadap tenants secara keseluruhan (platform superadmin)

package auth

// Tenant roles
const (
    RoleTenantAdmin   = "tenant:admin"
    RoleTenantManager = "tenant:manager"
    RoleTenantUser    = "tenant:user"
)

// Platform roles
const (
    RolePlatformSuperAdmin = "platform:superadmin"
    RolePlatformSupport    = "platform:support"
)

// Permission definition
type Permission struct {
    Resource string // e.g., "orders", "users", "reports"
    Action   string // e.g., "create", "read", "update", "delete"
}

// RolePermissions maps role to permissions
var RolePermissions = map[string][]Permission{
    RoleTenantAdmin: {
        {Resource: "orders", Action: "*"},
        {Resource: "users", Action: "*"},
        {Resource: "reports", Action: "*"},
        {Resource: "settings", Action: "*"},
    },
    RoleTenantManager: {
        {Resource: "orders", Action: "*"},
        {Resource: "users", Action: "read"},
        {Resource: "reports", Action: "read"},
    },
    RoleTenantUser: {
        {Resource: "orders", Action: "create"},
        {Resource: "orders", Action: "read:own"},
    },
    RolePlatformSuperAdmin: {
        {Resource: "tenants", Action: "*"},
        {Resource: "platform:audit", Action: "*"},
    },
}

// Authorizer checks if a role has permission for an action
type Authorizer struct{}

func (a *Authorizer) HasPermission(role, resource, action string) bool {
    perms, ok := RolePermissions[role]
    if !ok {
        return false
    }

    for _, p := range perms {
        if p.Resource == resource && (p.Action == "*" || p.Action == action) {
            return true
        }
    }
    return false
}

// Enforce middleware untuk RBAC
func RBACMiddleware(authorizer *Authorizer, resource, action string) func(http.Handler) http.Handler {
    return func(next http.Handler) http.Handler {
        return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
            claims := getJWTClaims(r)
            if claims == nil {
                http.Error(w, "Unauthorized", http.StatusUnauthorized)
                return
            }

            role := claims.Role

            // Superadmin bypasses all checks
            if role == RolePlatformSuperAdmin {
                next.ServeHTTP(w, r)
                return
            }

            if !authorizer.HasPermission(role, resource, action) {
                http.Error(w, "Forbidden", http.StatusForbidden)
                return
            }

            next.ServeHTTP(w, r)
        })
    }
}

Reporting dan Analytics Cross-Tenant

Begabung reporting untuk cross-tenant analytics butuh pendekatan khusus — kita tidak bisa просто filter by tenant_id kalau yang dibutuhkan adalah agregasi lintas tenant.

Pattern yang kami pakai: dedicated reporting service dengan read-only access ke data semua tenants:

package reporting

import (
    "context"
    "database/sql"
    "fmt"
    "time"

    "github.com/indodax/multi-tenant/internal/domain/vo"
)

type ReportingService struct {
    db *sql.DB // Read-only connection, different credentials
}

func NewReportingService(db *sql.DB) *ReportingService {
    return &ReportingService{db: db}
}

// TenantPerformanceReport — aggregate metrics per tenant
type TenantPerformanceReport struct {
    TenantID       string    `json:"tenant_id"`
    TenantName     string    `json:"tenant_name"`
    TotalOrders    int64     `json:"total_orders"`
    TotalRevenue   vo.Money  `json:"total_revenue"`
    AvgOrderValue  vo.Money  `json:"avg_order_value"`
    ActiveUsers    int64     `json:"active_users"`
    PeriodStart    time.Time `json:"period_start"`
    PeriodEnd      time.Time `json:"period_end"`
}

func (s *ReportingService) GetTenantPerformance(ctx context.Context, period vo.DateRange) ([]*TenantPerformanceReport, error) {
    query := `
        SELECT
            o.tenant_id,
            t.name as tenant_name,
            COUNT(o.id) as total_orders,
            SUM(o.total_amount) as total_revenue,
            AVG(o.total_amount) as avg_order_value,
            COUNT(DISTINCT o.user_id) as active_users
        FROM orders o
        JOIN tenants t ON t.id = o.tenant_id
        WHERE o.created_at BETWEEN $1 AND $2
        GROUP BY o.tenant_id, t.name
        ORDER BY total_revenue DESC
    `

    rows, err := s.db.QueryContext(ctx, query, period.Start, period.End)
    if err != nil {
        return nil, fmt.Errorf("failed to query tenant performance: %w", err)
    }
    defer rows.Close()

    var reports []*TenantPerformanceReport
    for rows.Next() {
        var r TenantPerformanceReport
        if err := rows.Scan(
            &r.TenantID, &r.TenantName, &r.TotalOrders,
            &r.TotalRevenue, &r.AvgOrderValue, &r.ActiveUsers,
        ); err != nil {
            return nil, fmt.Errorf("failed to scan row: %w", err)
        }
        r.PeriodStart = period.Start
        r.PeriodEnd = period.End
        reports = append(reports, &r)
    }

    return reports, nil
}

Untuk cross-tenant reporting, read replica dengan separate credentials yang tidak punya RLS policy itu essential. Ini memastikan reporting service bisa aggregate data tanpa interference.

Deployment Strategy

Multi-tenant deployment perlu considerations khusus:

Container Orchestration dengan Tenant-Aware Scaling

# Kubernetes deployment dengan resource quotas per tenant
apiVersion: apps/v1
kind: Deployment
metadata:
  name: order-service
spec:
  replicas: 3
  selector:
    matchLabels:
      app: order-service
  template:
    spec:
      containers:
      - name: order-service
        image: order-service:v1.2.3
        resources:
          requests:
            memory: "256Mi"
            cpu: "250m"
          limits:
            memory: "512Mi"
            cpu: "500m"
        env:
        - name: DB_MAX_OPEN_CONNS
          value: "25"
        - name: DB_MAX_IDLE_CONNS
          value: "10"
---
# Pod Disruption Budget untuk high availability
apiVersion: policy/v1
kind: PodDisruptionBudget
metadata:
  name: order-service-pdb
spec:
  minAvailable: 2
  selector:
    matchLabels:
      app: order-service

Database Connection Pooling

Dengan banyak tenants, connection pool management jadi critical. Kami pakai PgBouncer untuk connection pooling:

# pgbouncer.ini
[databases]
; Template untuk multi-tenant databases
template = host=postgres port=5432 dbname=template1

; Tenant-specific databases
tenant1 = host=postgres port=5432 dbname=tenant1
tenant2 = host=postgres port=5432 dbname=tenant2

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Connection pool settings
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

Tenant Provisioning Automation

package tenant

import (
    "context"
    "fmt"
    "time"

    "github.com/google/uuid"
    "github.com/indodax/multi-tenant/internal/infrastructure/database/postgres"
)

type Provisioner struct {
    db     *sql.DB
    schema string // "public" atau named schema
}

func NewProvisioner(db *sql.DB) *Provisioner {
    return &Provisioner{db: db}
}

// ProvisionTenant creates all necessary resources for a new tenant
func (p *Provisioner) ProvisionTenant(ctx context.Context, req ProvisionTenantRequest) (*Tenant, error) {
    // 1. Create tenant record
    tenant := &Tenant{
        ID:        uuid.New().String(),
        Name:      req.Name,
        Plan:      req.Plan,
        Status:    TenantStatusActive,
        CreatedAt: time.Now(),
    }

    if err := p.createTenantRecord(ctx, tenant); err != nil {
        return nil, fmt.Errorf("create tenant record: %w", err)
    }

    // 2. Provision database resources (for schema-per-tenant approach)
    if req.IsolatedSchema {
        if err := p.createTenantSchema(ctx, tenant.ID); err != nil {
            // Rollback
            p.deleteTenantRecord(ctx, tenant.ID)
            return nil, fmt.Errorf("create schema: %w", err)
        }
    }

    // 3. Run migrations untuk tenant-specific tables
    if err := p.runMigrations(ctx, tenant.ID); err != nil {
        return nil, fmt.Errorf("run migrations: %w", err)
    }

    // 4. Create default roles for tenant
    if err := p.createDefaultRoles(ctx, tenant.ID); err != nil {
        return nil, fmt.Errorf("create default roles: %w", err)
    }

    // 5. Send welcome notification
    p.notifyTenantCreated(tenant)

    return tenant, nil
}

func (p *Provisioner) createTenantSchema(ctx context.Context, tenantID string) error {
    schemaName := fmt.Sprintf("tenant_%s", tenantID)
    query := fmt.Sprintf("CREATE SCHEMA IF NOT EXISTS %s", schemaName)
    _, err := p.db.ExecContext(ctx, query)
    return err
}

Key Takeaways

"Multi-tenant architecture bukan tentang satu pattern yang benar — tapi tentang memahami trade-offs dan memilih yang paling sesuai dengan requirements."

Lessons learned dari implementasi multi-tenant di beberapa sistem:

1. Start dengan shared schema unless you have strong reasons not to — operational complexity dari schema-per-tenant itu significant
2. Row-Level Security itu essential — don't rely solely on application-level filtering. Defense in depth.
3. Tenant context harus explicit everywhere — tidak boleh ada query yang bisa lupa filter by tenant
4. Monitoring per tenant itu harus — jangan wait sampai satu tenant's workload affects others
5. Provision dan deprovision automation — manual tenant management tidak scale

Semoga article ini useful. Multi-tenant systems memang complex, tapi dengan architecture yang proper, bisa di-manage dan di-scale dengan manageable complexity. 🚀