Database Sizing
These figures are anchored on a real running system, not theoretical row sizes, and they are all-in - data, indexes, MVCC bloat, audit / event / CDC-outbox tables, and WAL slack, exactly as they consume disk in production.
This is a CEILING model
The measured rate comes from real, actively-operating merchants. Applying it to N merchants assumes all N are equally active - which is the worst case for storage. Real deployments are a mix: most merchants are far less active, so actual usage trends below the ceiling tables. Size the upper bound from the ceiling; size the realistic case from the mixed-fleet section.
Measured baseline (production)
| Metric | Value |
|---|---|
| Merchants (all active) | 10 |
| Period | 1,368 days (~3.75 years) |
| Total database size | 24 GB |
Derived per-merchant ceiling rate:
24 GB ÷ 10 merchants ÷ 1,368 days = 1.75 MB / active merchant / day
≈ 0.64 GB / active merchant / yearThis already includes real-world overhead, so it is ~5-6× a naive "core transactional rows only" estimate. It is the ceiling per active merchant.
Merchants are not uniform
A real fleet spans very different activity profiles. Only active merchants drive storage; dormant ones add a one-time static footprint (account + catalog ≈ 0.4 MB) and almost no daily growth.
| Tier | Profile | Daily data (× ceiling) |
|---|---|---|
| Heavy | High-traffic outlet / chain | up to ~1× (the ceiling absorbs these at fleet level) |
| Active | Typical operating business - the measured baseline | 1× = 1.75 MB/day |
| Light / seasonal | Low or intermittent volume | ~0.2-0.4× |
| Dormant / trial | Registered, not selling | ~0 (static 0.4 MB one-time only) |
Key consequence: total storage ≈ (active-equivalent merchants) × ceiling rate. The number of registered merchants barely matters for size - each dormant one adds only ~0.4 MB. Plan by active merchants, not total accounts.
Ceiling projection - every merchant active (upper bound)
The merchant column here = active merchants. "Keep all data" = everything retained in the primary DB. "Hot DB" = transactional data older than 180 days archived to cold storage, so the primary stays bounded.
| Active merchants | Growth / day | DB / year (keep all) | Hot DB, steady (180-day archive) |
|---|---|---|---|
| 10 (measured) | 17.5 MB | 6.4 GB | 3.2 GB |
| 100 | 175 MB | 64 GB | 32 GB |
| 1,000 | 1.75 GB | 0.64 TB | 0.32 TB |
| 10,000 | 17.5 GB | 6.4 TB | 3.2 TB |
| 50,000 | 88 GB | 32 TB | 16 TB |
| 100,000 | 175 GB | 64 TB | 32 TB |
100,000 active merchants over time
| Horizon | Total DB (keep all data) |
|---|---|
| Per day | ~175 GB |
| 1 month | ~5.3 TB |
| 6 months | ~32 TB |
| 1 year | ~64 TB |
| 3 years | ~192 TB |
Realistic projection - mixed fleet of 100,000 registered merchants
Most registered merchants are not fully active. Because dormant merchants add ~0 daily, total ≈ active share × ceiling. Pick the active share that matches the rollout.
| Active share | Active merchants | DB / year (keep all) | Hot DB (180-day) |
|---|---|---|---|
| 100% (ceiling) | 100,000 | ~64 TB | ~32 TB |
| 50% | 50,000 | ~32 TB | ~16 TB |
| 30% | 30,000 | ~19 TB | ~9.6 TB |
| 20% | 20,000 | ~13 TB | ~6.4 TB |
| 10% | 10,000 | ~6.4 TB | ~3.2 TB |
(Plus ~40 GB static for 100,000 registered accounts + catalogs - negligible at this scale.)
Workload sizing - transactions, customers, connections
Reference fleet: 1,000 active merchants. Transactions and customers scale linearly; connections do not (they track service topology, not merchant count).
Transactions & customers
Derived from the 1.75 MB / merchant / day anchor (~10 KB all-in per order):
| Metric | Per active merchant | × 1,000 merchants |
|---|---|---|
| Transactions (orders/bills) | ~175 / day | ~175,000 / day |
| DB write statements (~20 per order) | ~3,500 / day | ~3.5 M / day (peak ~300-600 /s) |
| New customers¹ | ~10-50 / day | ~10-50 K / day |
| Customers accumulated (~3 yr) | ~5-15 K rows | ~5-15 M rows |
¹ Softest number - most POS sales are walk-in; only loyalty signups create rows.
Connections
Two numbers matter, and they are very different. The set that is actually busy at any moment is small - PgCat transaction-pools the ~360 app clients (18 processes × pool 10 × ~2 HA instances) down to a handful of working server connections. But max_connections is not sized for the busy set; it has to leave room for every consumer that can hold a connection at once, plus headroom for operations and incidents. Provision it from that operating view:
| Consumer on master | Connections |
|---|---|
App via PgCat write pool (pool_size, raised from 25 for slow-transaction headroom) | ~50 |
| Debezium CDC - connectors + parallel snapshots | ~15 |
| Streaming replication walsenders (per replica, plus rebuilds) | ~5 |
| Migrations during deploy (several services at once) | ~15 |
Backup - parallel pg_dump -j / pg_basebackup to rebuild a replica | ~10 |
| Monitoring & APM exporters | ~5 |
DBA / ops sessions + maintenance (VACUUM, REINDEX, ad-hoc) | ~15 |
superuser_reserved_connections | 5 |
| Operating subtotal | ~120 |
| × ~2 headroom (incident pile-up under slow queries, spikes, service growth) | ~240 |
| Node | Provision max_connections |
|---|---|
| Master | 300 |
| Each replica (read pool + replication receiver + exporter + backup + ops) | 200 |
The cost is small: ~5-10 MB RAM per connection, so 300 ≈ 2-3 GB against 32-64 GB. The real throughput lever is still PgCat pool_size - raise it (and max_connections with it) if app clients start queuing during slow back-office reports or lock waits, not by inflating every service's pool.
Architecture for scale
- Partition
SaleOrder/SaleOrderItem/FinanceTransactionby month (range partitioning) - keeps active indexes in memory and lets old partitions be detached cheaply. - Read replicas for back-office reports and dashboards; POS checkout writes to the primary only.
- Archive transactional data older than 180 days to cold storage (ClickHouse / S3) - this is what keeps the primary at the Hot DB column instead of the keep-all column.
Method & levers
- Basis: one real production system (10 active merchants, 1,368 days, 24 GB). Real, all-in. Treated as the per-active-merchant ceiling.
- Two levers: (1) active-equivalent merchant count, (2) per-merchant rate (1.75 MB/day) - adjust if average activity differs from the measured fleet.
- Formulas:
- keep-all total ≈
active_merchants × days × 1.75 MB - hot DB ≈
active_merchants × 180 × 1.75 MB - static ≈
registered_merchants × 0.4 MB - transactions/day ≈
active_merchants × 175 orders(≈× 20DB write statements) max_connections(master) ≈(pgcat_pool_size + CDC + replication + migration + backup + monitoring + ops + reserved) × ~2 headroom→ ~300
- keep-all total ≈
- Bound: ceiling tables are the upper bound; realistic usage sits at the active-share row that matches the deployment.