I get verbose, so here's the TL;DR: The training pipeline is a very time consuming and stressful time. You'll have 3-4 months of time you'll have to be apart no matter what due to types of training. If you come with him, your support will certainly help him; but realize that he might not be able to be as engaged in the relationship or reciprocating of the support. You may also have trouble finding work, as UPT bases are in the middle of nowhere small towns.
If you stay back home, you'll spend a long time apart from one another; being alone will allow him to focus more on studying, but the stress of the program and strain on your relationship may make it tough for him to actually stay and complete the training.
Regardless of what you choose, it's not going to be an easy road, but it's manageable if you have the right expectations (whichever you choose), your relationship is in the right place, and you both understand that the sacrifices made are just short term; things will get better and return to more normalcy as time goes along.
Here's the longer, more in-depth version:
My .02 from inside UPT now and what I've experienced/heard from other UPT folks. I'm late-30's, married, and we had a 7 month old when this adventure started and enjoyed living in New York City. I brought them with me and it's been much better. Even if I decided to complicate things further by adding another kiddo to the mix, arriving before the end of UPT.
First, I went the Reserves route, because your training is all in one continuous line; he'll start OTS and will be on full-time orders from day 1 until popping out the other end (about 2 years) as a trained and unit-qualified pilot. The Guard has more breaks in training, where he might go to OTS, come home for a few months, then to SERE (takes a month), then home, then to UPT (takes a year), then home, then FTU (3-9 months, depending on airframe), then to his home unit. The breaks could be nice if you were staying put, but they could be more of a PITA if you're going with him and pulling up roots at your current home.
You're going to 100% have to spend OTS (2 months) IFT (6 weeks, I think. He'll have to go to this if he doesn't have his pilot's license before starting UPT/skips it if he does), and SERE (one month) apart, as you really have no ability to go with him to any of that, so you'll be separated regardless.
Then comes to the big chunk of time and stress; UPT.
To make a bunch of long stories short, the gist I've seen from multiple folks and is that, unless you're PREPARED to spend that much time apart, it is VERY hard to be successful at UPT without family with you. I know more people that have dropped due to not having families come with them and only know a few folks, every one of them prior military service folks that have dealt with separation before, that are doing okay without their families here. So, if you're not used to spending serious time apart, it's very possible the strain can be too much for the relationship.
The downsides of you coming are also notable. First is that UPT is a very demanding schedule and 12 hour days (just the time he'll be gone; not including him studying/mission planning at home) are very common. He'll be target-locked on studying and likely not be able to give as much time to you/your relationship as you're used to, since free moments are few and far between at many points. If you're used to him helping out a lot around the house, cooking dinner together, being able to sit and talk/watch TV with you for hours each night, etc., it'll likely be reduced greatly as he'll have to go study. Or, if he does still try to give that time to your relationship, he's going to be sacrificing the studying time and likely not doing as well in the program. There's only so much time in the day.
Personally, I have had many moments where I needed to study and the need to be an engaged husband and father also required my attention. I've certainly erred toward my family over hitting the books as hard, which has certainly affected my performance, but those are the calculated decisions I've chosen to make. Everyone does their own math and handles it all differently. If he is a perfectionist or struggles more with some aspects along the way, just realized he might choose differently. Many people have done it and made it all work, but each person is different and you'll have to take a long look at yourselves and how you think you'll handle it.
There's also the inability to get out of "this life" in that the bases are pretty much only for training pilots, so everyone is in the same boat. That can be good for support, but he is going to live and breathe UPT, which means you likely will, too. Your social interactions will involve him with other UPT students talking shop and you hanging with spouses that are also likely very involved with talking about UPT-related things. You're going to know where you're going being Guard/Reserves, so that stress doesn't exist for you guys, really.
You're also on the older side (like we are), so your life experiences, wants, and needs may be different from other students (most are single) and spouses that might be fresh out of college, not working/never had a career, not thinking about kids, etc.
Last big piece if you come with: UPT bases are in the middle of nowhere, so jobs can be tough to come by if you have something specialized that only exists in bigger cities. It's very likely the pay will be quite a bit lower; although your bills are much lower, so it kinda evens out. That said, chances of you finding a PR/Communications job you love as much as your current job (unless you can work remotely) close to one of the UPT bases are going to be pretty slim.
Again, YMMV and I'm certainly not trying to scare you. But, it's important to have rational and managed expectations of what the choice makes. It's an amazing thing and extremely rewarding, but it's not without pitfalls. But, that's life. Haha.
Anyway, sorry this got long. Feel free to ask away with any other questions you might have! Good luck to you both!