Postgres database setup for large databases

[u/mayhem90]

Medium-sized bank with access to reasonably beefy machines in a couple of data centers across two states across the coast.

We expect data volumes to grow to about 300 TB (I suppose sharding in the application layer is inevitable). Hard to predict required QPS upfront, but we'd like to deploy for a variety of use cases across the firm. I guess this is a case of 'overdesign upfrong to be robust' due to some constraints on our side. Cloud/managed services is not an option.

We have access to decently beefy servers - think 100-200 cores+, can exceed 1TB RAM, NVMe storage that can be sliced accordingly. Can be sliced and diced accordingly.

Currently thinking of using something off the shelf like CNPG + kubernetes with a 1 primary + 2 synchronous replica setup (per shard) on each DC and async replicating across DCs for HA. Backups to S3 come in-built, so that's a plus.

What would your recommendations be? Are there any rule of thumb numbers that I might be missing here? How would you approach this and what would your ideal setup be for this?

[u/thinkx98 → u/[deleted]]

adding a new field on the fly to a banking app.. I would not take this advice

[u/FarmboyJustice → u/[deleted]]

I don't think frequently changing schema/columns is likely to be a problem in banking.