Can photo sync be as effortless as music sync, between macOS and iOS?
Yes - enable iCloud Photo Library. I’ve been happy with this for my main personal library consisting of 90 GB library size and 75,000 photos.

I do maintain some side libraries that don’t sync to iCloud - but those are specific professional and trip specific libraries where I just save my best 100 or so photos from those libraries to my “main” library.

I have a MacPro that keeps original size of all photos. The rest of the iPad, iPhone, Apple TV and Mac that sync are allowed to “optimize” photo sizes when the main storage for that device runs low.

I’m happy knowing I can use Time Machine (or SuperDuper / Arq / CrashPlan / whatever) to make offline and off-site backups of my Main libraries and not trust that iCloud Photo Library will never mess things up. So far it hasn’t, but I have a plan to walk away from iCloud any time and restore from backup if needed.

—– 

Even better - Apple offers caching service so you could cache all your iCloud data locally on pretty much and Mac you can leave running with an inexpensive external drive if needed. I like to give 500 GB of space to cache downloads and iCloud data and that works well for 5 people, each with several devices and similar sized photo and music libraries. The caching server makes backup and restore very fast as well as updates and upgrades to new OS and new devices when they get configured the first time.

On 10.12 and lower - this caching service is part of Server.app and works great.
On 10.13 and higher - there are signs you won’t need server.app to cache apps and iCloud data locally. I have no complaints yet testing this in beta.