Verdict: Installing and updating takes hours but 10.6 is much faster on an old machine than 10.4 and a bit faster than 10.5 on a newer one. There are lots of small improvements that make computer use smoother, especially for basic standalone users.
With 10.7 Apple are dropping support for one of the Macs in the family so I stopped holding out on 10.4 and bought the Mac Box Set with OS X 10.6, iLife 09 and iWork 09 in it. My boxset had a product code of MC582Z/A, came with Mac OS X 10.6.3 and was half the price it was originally launched at/Apple sell an updated boxset for.
I bought a family pack boxset but as mentioned elsewhere on the web, the only way to tell a single user and family pack boxset apart is a small sticker on the box itself or a product label on the bottom of the box. This makes it difficult to know if you are going to get the "right" pack beforehand as from the software perspective it doesn't seem to care. Perhaps this will change with 10.7 and Internet installs...
I installed a first generation Intel Mac from 10.4 and a 2008 MacBook which was on 10.5. In both cases the experience was fairly similar and smooth as I manually did "clean" installs (this disk can actually upgrade and install). The install process of Leopard itself took around forty minutes with the install of the extra pieces of the boxset (e.g. iLife) taking about 15-20 minutes each. OS X, iLife and iWork are provided on separate DVDs and iLife/iWork can only be installed once OSX is up and running.
The fun comes after the install though - at the time of writing the latest update to 10.6 is 10.6.7 which is 1Gbyte in size. I suspect that updates for all the software in this boxset (OS X 10.6, iLife 09, iWork 09) comes to about 2Gbytes and adds at least another hour or two to the total installation time (I had the biggest updates pre-downloaded before hand so this estimate does not include download time).
The post update experience was good with both computers working well. The highlights were the facial recognition in iPhoto '09, the App Store (that arrived after the 10.6.7 update), the screen sharing via iChat, the vastly better Terminal over the one in 10.4 and improved speed/responsiveness over 10.4. Manually putting settings back is not for the faint hearted so I would recommend going the automatic upgrade route to most people.
I don't have much 3rd party software on these Macs and what I do use is free so I just downloaded new versions which worked fine after the install. The old printer I have (from 2000!) continued to be supported by open source printer drivers OS X has always provided (it still can't tell me ink supply levels though). The Macs connect to the Internet router via ethernet and wifi without issue and the rest of my family are happy that the interface is still familiar enough that no major relearning was required.
Downsides? Exchange 2003 is not supported by Mail.app so if you have a company still using it you will be out of luck (Exchange 2007 is apparently supported though) and Mail.app had major issues synchronising a Google Mail inbox which contained thousands of mails.