Postgres 12 Scalability using table Partitioning and Foreign Data Wrappers

Question

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.

Answer 1

Currently cloud based solution is not an option due to costs and complexity

At the scale you are talking about, costs and complexity are not optional. Don't rule out cloud due to something you can't avoid anyway.

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

Why? That seems arbitrary. The advantage of colo is you can do whatever you want. Why can't you make a RAID with more than 2.3TB?

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.

I don't see how this fixes anything. If your yearly partitioned tables on the different server is still subject to the arbitrary 2.3TB limit, it will still run out of space. On the other hand, if your different server is not subject to that limit, why can't you make your master also not subject to it?

If for some reason this is just the way it is, you can certainly set up something where the live data is on one server and the archived data is on separate server, and connect them together with FDW. It would probably be best to have the FDW run from archive to live, and then anyone who needs archived data (plus possibly live data) would need to connect to the archive server to get it. But be warned that FDW does not come for free. The overhead of data access over a FDW is high, and for complex queries the planner is likely to make some horrific mistakes. Optimizing queries in a FDW environment is much more tedious.

One advantage to the FDW solutions is resilience. If the archive server crashes, the live-data server is unaffected, other than not being able to access the archived data (which if you took my advice from the previous paragraph, it can't do anyway). If you have all data on the same PostgreSQL instance, perhaps with multiple tablespaces, then if it crashes you need to restore all the data together. You can't just restore the live data and let the archived data restore in the background (at least not if you are using WAL-based backups, which at this scale you probably are).