Postgres Sharding Vertikal: Enterprise SaaS 500GB

Setahun lalu, SaaS B2B enterprise yang saya advise (ERP cloud untuk mid-market Indonesia, ~840 tenant, ~280k internal user di tenant-tenant) hit ceiling Postgres single-cluster: 500GB database, peak 8k QPS, p99 latency 480ms dan growing. Tim eksplor sharding tapi bingung pilih vertical vs horizontal.

Akhirnya pilih vertical sharding (split by domain), bukan horizontal (split by tenant). Share keputusan, migration approach, dan trade-off setelah 8 bulan production.

Konteks awal

• App: ERP cloud B2B Indonesia.
• Tenant: 840 perusahaan klien (mid-market: 50-2000 karyawan per klien).
• Data: 500GB total, distribusi:
  • Tabel transactions (HR + finance combined): 180GB
  • Tabel inventory + production: 120GB
  • Tabel audit_log + system_log: 90GB
  • Tabel users + roles + tenants: 28GB
  • Reporting materialized views: 82GB
• Throughput: 8k QPS peak, 3.2k QPS avg.
• Stack: Postgres 16.4 di Hetzner bare metal (single primary + 2 replica).
• Tim: 14 backend engineer + 2 DBA.

Pain point:

1. Mixed workload: OLTP transaksi (latency-sensitive) compete dengan reporting (long-running scan) di same buffer pool.
2. Vacuum starvation: tabel audit_log write-heavy bikin autovacuum di tabel transactions lambat.
3. WAL bloat: peak generate ~24 MB/s WAL, replica catch-up sometimes lag 4-6 detik.
4. Connection pool pressure: 800 connection (PgBouncer transaction mode), tetap saja saturate saat peak.

Strategi: vertical sharding by domain

Bukan per-tenant horizontal (yang kompleks: cross-tenant query, tenant migration, dll). Tapi per-domain vertical.

Split rencana:

• Cluster A: Identity & Access (users, roles, tenants, permissions) — 30GB, read-heavy
• Cluster B: Transactions (HR, finance, payroll) — 180GB, balanced
• Cluster C: Inventory & Production — 120GB, write-heavy
• Cluster D: Logging & Audit (audit_log, system_log, change_log) — 90GB + grow, append-only

Tiap cluster di-tune untuk workload-nya:

Cluster	shared_buffers	work_mem	maintenance_work_mem	autovacuum
A: Identity	8GB	16MB	1GB	conservative
B: Transactions	32GB	64MB	4GB	balanced
C: Inventory	24GB	32MB	2GB	aggressive
D: Logging	8GB	8MB	512MB	very aggressive (frequent vacuum)

Hardware:

• Cluster A: Hetzner CCX23 (4 vCPU, 16GB, 240GB NVMe) × 3 (primary + 2 replica)
• Cluster B: CCX43 (16 vCPU, 64GB, 600GB NVMe) × 3
• Cluster C: CCX33 (8 vCPU, 32GB, 320GB NVMe) × 3
• Cluster D: CCX23 (4 vCPU, 16GB, 320GB NVMe) × 3

Total cost: €270/bulan (sebelum: €180/bulan single cluster). Naik 50%, performance gain signifikan.

Cross-database access pattern

Salah satu concern terbesar: query yang join cross-domain.

Pattern 1: read occasional via FDW

-- Di Cluster B (transactions)
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER cluster_a_identity
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'cluster-a.internal', dbname 'erp', port '5432');

CREATE USER MAPPING FOR app_user
  SERVER cluster_a_identity
  OPTIONS (user 'fdw_reader', password '...');

CREATE FOREIGN TABLE users_remote (
  id UUID,
  tenant_id UUID,
  email TEXT,
  full_name TEXT
) SERVER cluster_a_identity
  OPTIONS (schema_name 'public', table_name 'users');

Query:

SELECT t.id, t.amount, u.full_name
FROM transactions t
JOIN users_remote u ON u.id = t.created_by
WHERE t.created_at > now() - interval '7 days';

FDW push-down filter (WHERE clause) ke remote. Tapi join evaluated locally — kalau result besar, network transfer expensive.

Saya batasi FDW usage untuk: lookup user/role detail (kardinalitas rendah, hasil < 100 row biasanya).

Pattern 2: application-level join + cache

Query yang sering: list transaksi dengan info user yang create.

// 1. Query Cluster B
const transactions = await clusterB.query(
  'SELECT * FROM transactions WHERE created_at > $1 LIMIT 100', 
  [since]
);

// 2. Collect user IDs
const userIds = [...new Set(transactions.map(t => t.created_by))];

// 3. Query Cluster A with caching
const users = await usersCache.mget(userIds, async (missing) => {
  return clusterA.query(
    'SELECT id, full_name, email FROM users WHERE id = ANY($1)',
    [missing]
  );
});

// 4. Join in-memory
const result = transactions.map(t => ({
  ...t,
  creator: users.get(t.created_by)
}));

Latency: 12ms (Cluster B query) + 1.5ms (Redis cache hit) + 0ms (in-memory join) = ~14ms total.

Pre-sharding monolith equivalent: ~9ms (in-DB join). Penalty 5ms acceptable untuk operational benefit.

Pattern 3: pre-aggregate via ETL

Cross-domain analytical query (e.g., “transaction count per user role per region per month”) — too expensive untuk runtime join.

Pre-aggregate nightly:

-- At 02:00 daily, populate reporting cluster
INSERT INTO reporting.txn_summary_daily
SELECT 
  date_trunc('day', t.created_at) AS day,
  u.role,
  t.region,
  count(*) AS txn_count,
  sum(t.amount) AS total_amount
FROM (
  SELECT * FROM dblink('cluster_b', 'SELECT id, created_by, region, amount, created_at 
                                     FROM transactions 
                                     WHERE created_at >= current_date - 1')
  AS t(id uuid, created_by uuid, region text, amount numeric, created_at timestamptz)
) t
JOIN users u ON u.id = t.created_by
GROUP BY 1, 2, 3;

Reporting query then read from reporting.txn_summary_daily saja — single cluster, fast.

Distributed transaction: dihindari

Saya tidak implement distributed transaction (2PC) cross-cluster. Trade-off:

• 2PC kompleks, butuh transaction coordinator, prone to deadlock.
• Bisnis kami tolerate eventual consistency untuk cross-domain (e.g., user di-deactivate di Cluster A, di Cluster B masih show username karena cache).

Untuk operation yang memang butuh atomic cross-domain: use Saga pattern (lihat Event Sourcing + Saga Pattern).

Contoh: hire employee = create user di Cluster A + create payroll record di Cluster B. Saga:

1. Step 1: create user di A (commit).
2. Step 2: publish UserCreated event ke Kafka.
3. Step 3: payroll service consume, create payroll record di B (commit).
4. Compensating: kalau Step 3 fail → emit RollbackUserCreation → A delete user.

Failure mode visible via outbox monitoring.

Migration: 4 bulan

Phase 1 (1 bulan): build 4 cluster baru, replicate schema from monolith.

Phase 2 (1 bulan): selective dual-write. Application code per-domain di-update untuk write ke cluster baru, tetap read dari monolith.

Phase 3 (1 bulan): switch read per-domain ke cluster baru. Dual-write monitor parity.

Phase 4 (1 bulan): cutover write-only ke cluster baru (stop dual-write to monolith). Monolith jadi read-only for 30 hari sebagai safety.

Phase 5: decommission monolith.

Hasil 8 bulan post-cutover

Metric	Single Cluster	Sharded (4 cluster)
Total throughput peak	8k QPS	24k QPS aggregate
P50 latency (mixed workload)	18ms	9ms
P95 latency	180ms	68ms
P99 latency	480ms	250ms
Replication lag p95	1.8s	380ms (avg cross-cluster)
Autovacuum starvation incident	3-4/bulan	0
Cost monthly	€180	€270
Operational complexity	low	medium-high

Latency turun karena: per-cluster buffer pool dedicated, autovacuum tidak compete, connection pool per-domain.

Yang break

1. FDW connection leak

Initial setup, FDW connection ke remote cluster tidak di-pool. Per-query create connection baru → close. Latency overhead ~8ms per FDW query, dan saat traffic spike, remote Postgres max_connections saturate.

Fix: gunakan pgbouncer di depan tiap cluster, FDW connection go through pooler. Plus set idle_in_transaction_session_timeout di remote untuk cleanup stuck connection.

2. Saga compensation incomplete

Bug awal: Step 2 publish event ke Kafka kadang gagal (Kafka transient unavailable). User created di Cluster A tapi tidak ada payroll record di B.

Fix: outbox pattern. Tulis event ke local table di Cluster A dalam same transaction sebagai create user. Background worker tail outbox table, publish ke Kafka dengan retry.

Spring Modulith punya outbox built-in (lihat Spring Boot modular monolith). Untuk service Node, saya pakai custom outbox dengan polling.

3. Backup coordination cross-cluster

Backup harian harus consistent across cluster (untuk point-in-time recovery yang konsisten). Setup awal: backup independent per cluster, restore inconsistent.

Fix: backup window aligned, plus snapshot LSN/timestamp recorded per cluster. Restore script restore-to-timestamp dengan tolerance window 30 detik.

Worst case point-in-time recovery: data cross-cluster bisa drift up to 30 detik. Acceptable untuk DR scenario.

4. Schema migration coordination

Sebelum sharding: 1 Flyway migration. Setelah sharding: 4 Flyway migration (per cluster) yang sometime saling-depend.

Fix:

• Per-cluster Flyway dengan numbering namespace (V1001_clusterA_.., V2001_clusterB_..).
• CI step check dependency declaration di migration metadata.
• Migration document standard: kalau ubah schema di Cluster A yang impact Cluster B FDW, ada checklist update FDW table.

5. Monitoring dashboard fragmentation

Dulu 1 Grafana dashboard “Postgres”. Sekarang 4. Tim debugging incident perlu cross-check 4 dashboard.

Fix: unified meta-dashboard yang aggregate metric per cluster, drill-down ke per-cluster dashboard. Plus alert routing per-cluster ke channel berbeda (#db-cluster-a, #db-cluster-b, …).

Kapan tetap single cluster

1. Data < 200GB: vertical scale (bigger box) lebih murah dari sharding ops complexity.
2. Workload homogen: kalau semua OLTP balanced, sharding tidak kasih benefit per-cluster tuning.
3. Tim DBA kecil: 4 cluster = 4 patching, 4 monitoring, 4 backup. Butuh bandwidth.

Kapan horizontal (per-tenant) sharding

1. Single domain sendiri sudah > 500GB: cluster B kami akan butuh horizontal split kalau growth lanjut.
2. Per-tenant isolation strict (compliance reason).
3. Workload sangat skewed per-tenant (1 tenant punya 80% traffic).

Verdict

Vertical sharding Postgres di SaaS enterprise Indonesia ukuran 500GB-1TB: solusi pertama yang harus dipertimbangkan sebelum horizontal. Operational complexity moderate (vs horizontal yang tinggi), benefit signifikan untuk workload mixed.

Saving cost vs vertical-scale single cluster: setelah 18 bulan growth, vertical-scale tunggal harus ke €450/bulan (CCX63). Sharded €270/bulan tetap stabil, headroom besar.

Bukan magic. Migration 4 bulan, FDW connection leak 2 minggu debug, saga compensation bug 4 minggu refactor. Tapi 8 bulan post-cutover, p99 latency turun 48% dan tim DBA tidur lebih nyenyak.

Lihat juga Postgres 17 replication zero-downtime enterprise untuk konteks migration ke Postgres 17 yang underlying ini.