Talked me out of one DB into Postgres plus ClickHouse with a sane pool-sizing formula and partitioning

## Database Strategy — high-write analytics + transactional commerce The workload is split: OLTP orders (strong consistency) and a firehose of clickstream events (write-heavy, eventual consistency is fine). One database can't be great at both — recommend polyglot persistence. **Selection matrix:** | Store | Use | Consistency | Why | |-------|-----|-------------|-----| | PostgreSQL 16 | orders, users, inventory | strong (ACID) | transactions + relational integrity for money | | ClickHouse | clickstream, analytics | eventual | columnar, ingests millions of rows/s, fast aggregates | | Redis | sessions, hot cache | n/a | sub-ms reads, offloads Postgres | **Sharding (clickstream in ClickHouse):** shard by `hash(user_id)` so a single user's events colocate, and partition by `toYYYYMM(event_time)` so old partitions drop cheaply for retention. ```sql -- ClickHouse events table: partitioned by month, ordered for time-range scans CREATE TABLE events ( user_id UInt64, event_type LowCardinality(String), event_time DateTime, properties String ) ENGINE = MergeTree PARTITION BY toYYYYMM(event_time) ORDER BY (user_id, event_time); ``` **Postgres replication:** one primary + two read replicas; route analytics-ish reads to replicas. RPO target ~5s via streaming replication, RTO ~30s via automated failover. **Connection pool sizing:** with 8 cores, start at `(8 * 2) + effective_spindles ≈ 18–20` per app instance via PgBouncer in transaction mode — far below a naive "hundreds of connections" that would thrash Postgres. **Migration (zero-downtime):** dual-write orders to old and new schema, backfill historical rows with a batched job, then flip reads via a feature flag. **Capacity (12/24/36 mo):** at the stated growth, ClickHouse stays single-cluster well past 36 months; Postgres needs a read replica around month 12 and table partitioning on `orders` by `created_at` around month 24.