I have looked through the archives and I cant find any discussions around the following topic.
I have a fairly in depth question that I would appreciate some guidance with.
Current environment
- Current Postgres Version: 10
- Os: Ubuntu 14:04 (Soon to upgrade to 18.04)
- Harddrive has 2.3 TB max space. (Raid 10 SSD's)
- Current Postgres data size: 1.6 TB (Growing at 100gb per month)
- Currently have 1 master database and 2 replicas. (1 upstream and 1 downstream slave using cascading replication)
- 1 warehouse using logical replication
Based on the above, I'm sure its rather obvious that I will be running into some serious issues regarding available disk space within a few months. Just a couple things to mention before i provide my theoretical long term solution.
Currently cloud based solution is not an option due to costs and complexity Servers are hosted at an offsite DC and the max possible disk size we can achieve using SSD's in a Raid 10 configuration is 2.3 TB Currently we are handling load to a reasonable standard. Although that could change as our business grows
My thoughts on a possible solution
I need a long term scalable solution and we have been looking into upgrading to Postgres 12. Using the seemingly awesome table partitioning with Foreign data wrappers, could we achieve horizontal scaling if we partition key tables by date? if this is possible, then we could have the current years data on our primary master PostgreSQL server and our yearly partitioned tables on a different server. Therefore alleviating our space issues and achieving long term scalability
The above sounds feasible to me, but how would this affect my replications? I believe any partitioning changes i make on my Master DB, would be "replicated" through to the replications. More importantly, how would this work related to the foreign data wrappers?
Alternative solutions
I could move away from using SSD's in order to achieve more space in a raid 10 configuration. (Long term i would still encounter the same issues eventually and my application might pay a performance penalty) I could use a difference raid configuration to achieve more available space. ( Same long term issues as mentioned above) I could look to build a manual archiving process that would copy my "cold" data to a different server and delete from the data from master.
Apologies for the long question.