How useful you'll find Photo Stream depends a lot on what you need or expect. If you just want a completely automated way to mirror all the photos you shoot on with your iPhone and iPad to your other computers and devices, then congratulations, this app's for you. If your needs stray beyond that to selective syncing/backup, the ability to manage what's in your Photo Stream cache, integration with other applications, and so on, then you're out of luck.
On each supported platform Photo Stream monitors a a pair of folders, one for uploads and one for the entire synced collection. It's actually more flexible on Windows than on the Mac; on Windows you can manually choose those folders (theoretically). On Apple mobile devices it's integrated into the relevant photo application, i.e., iPhoto or Aperture for OS X and Photos on iOS 5. Photos are only stored in iCloud for 30 days or the last 1,000 photos, whichever comes first. The first 5GB of iCloud storage is free, but it's not clear if buying extra capacity also increases your Stream size.
Photo Stream does have some image intelligence. If you're syncing camera photos along with camera phone photos, it sends a resolution-appropriate version to the device. For instance, a 685K version of one image went to my iPad while the full 5 megabyte version went to the MacBook. It sends a low-resolution JPEG version of a raw file to the iPad, but the complete raw version to the MacBook. Interestingly, when I cropped a version and saved a copy, Adobe Bridge somehow knew it was a crop. It will only sync over Wi-Fi in order to save bandwidth costs.
Some things are a mixed blessing. For example, I was thrilled when the iPad screens I captured just showed up in my Stream on my PC for inserting into blog posts; it's the fastest way to get them from my iPad or MacBook to my production system. Then I realized there would be times when I didn't want them to automatically feed into the Stream. That could potentially become a security or confidentiality issue. There's no way to pause the pipe; you either have to stop it, which deletes the cloud-based cache, or live with it. You can't delete photos once they're in the Stream. And on the desktop there's no way to algorithmically tell it, for example, to only sync JPEGs, not PNGs or raw files. Once the photos appear in your Photo Stream tab in iPhoto, you then have to manually import them. (I don't have the latest version of Aperture, so I couldn't check out how it works with that.)
While it manages the basics, in some ways it feels like iCloud isn't fully baked yet. The Help system still goes to the Apple Developer site, for example. And when I changed the upload directory from the default in the Windows iCloud control panel, the photos didn't sync. (Plus the Windows iCloud panel gives an error when you make changes if you don't have Outlook installed, even if you don't choose to sync your contacts, calendars, tasks or email.)
But I can't really be sure of the problem there, because of one of the most frustrating aspects of Photo Stream, at least in these early days: You can't poke it with a sharp stick. If the photos aren't appearing on the synced devices, there's no way to figure out why. You can't ping the server to force an upload, no alert pops up to tell you if there's an issue on the server end, nothing spins endlessly, whirs or beeps. You can't tell if it's not uploading or not downloading. (Update, 10/14/11: Apple has rolled out an iCloud status page.) All the problems seem to be desktop-to-iCloud related; iPad uploads and syncs always worked fine.
It seems like Photo Stream really needs some love from application developers--and perhaps a little more from Apple--before it's robust enough for those of us with heterogenous ecosystems of devices and software. But if you just want your iPhone or iPad photos to show up on your other systems or Apple TV, it's free and that aspect works.