I've inherited a prominent engineer role in supporting an archiving product that uses pgsql to save various kinds of small to medium size documents (usually things like email, social media, text, PDFs etc). We're running 9.2 per CentOS 7 (we are working on a next gen product but at least for now it provides kind of a meta-index that uses pgsql as a backend).

The messages vary in size but generally average between 50K and 200K.

Some of our larger customers are in the 20TB range, and we are slowly migrating a dated group of servers to a single machine. Likely we will see 250-300 total message entries with various tables for indexable keywords, subject, several timestamps, CRC, etc.

We'd like to move to 10 if possible (not so ambitious as to try to get it up to 12-14) with the least pain possible. I understand that I could dump and restore it.

I have some questions I hope one of you veterans/DBAs might be helpful with:

Looking for a quick, relatively painless way to do this. We don't expect to just flip a switch, a bit concerned about the time moving to 10 and then reconfiguring it to work with the applications that speak to it.

It will take a very long time to pgdump it all. That's not necessarily a deal breaker, especially if we can drop most of it where it is now. Don't expect you to know about any customizations, still investigating that.

Is there a way, including a paid package that can do a good job of this?

Is there a recommended method? I see pg_dump and pg_restore and we'll do that if need be, trying to work out tests on smaller DBs first, and running a full backup before doing this.

How much of a fool's errand is this? We've generally had good luck up to around the 20TB size and things have gone well with this, though I can't seem to get it not to bloat horribly without crippling the indexing speed, it indexes far more quickly than vacuum can pick it up and end up doing a vacuum or if it's large enough, often a vacuum full. even a regular vacuum seems to be very bloaty from the WAL/XLOG - we are putting the xlog either on the main DB partition or a secondary partition depending on need. If there is a smart way to do this or a way to keep autovac from choking and seemingly hanging on larger machines, I'd love to know more, from you folks or reading more. I've messed with things like autovacuum_cost_delay and it seems like it helsp a bit but autovac still seems to jam up.

Any suggestions would be helpful. The quickest way to do this (it's a huge project and eating lots of my time so I can put a good amount into a solution for sure) would be cool, or any options for different ways would be awesome, I'd also love to learn more about tweaking autovac or perhaps running the autovac during imports/indexing hard enough to not bloat things up so much.

I'd heard various suggestions about possible size limits on the DBs, I heard that a 32TB table would be a limit without upgrading. Not sure we're going to hit that as things get broken up but it's be nice to prepare and build to avoid that pain.

I seem to recall reading that 9.6 does threading, but only for multiple queries, where 10+ can thread out single queries? We're running some decent gear, SMP 24 core (48 logical) machine (2 CPUs, 12 cores each) and a dedicated fairly quick array. Threads could really help and I can throw pretty much an arbitrary number of threads. Sadly, it's running VMware which I know really cuts I/O - I made this argument already but will probably be able to make a better argument if I can get this up and show a baremetal version crushing it in speed.

I'd like to handle this myself and am fairly technically inclined, just most of my linux/unix experience is less DB-centric. This project has taken up loads of time and I both want to stick around here and want to come up with an effective solution. Any input here would be greatly appreciated, and I'll try to fill in any details if you ask for them so long as they don't involve violating my NDA. Heh.

Thanks in advance for reading this.

TL;DR - pgsql 9.2 building a 50+TB DB, need to upgrade if possible without hugely increasing the length of the project, and make sure that whatever I land on won't break CentOS 7. autovac is chewing up lots of CPU on other large but less large DBs (say, 10-20TB) and it's hard to run a vacuum full on a live DB. some of these larger ones present an issue doing a vacuum full anyway beacuse they don't have 2+ times the size of the DB available for a rewrite.