If you're a Mac user you must give VERY serious consideration to Aperture. Having used it for a couple of years I simply couldn't imagine photographic life without it!
I've used LightRoom too, and it pretty much matches Aperture in most respects. Choosing between the two comes down to personal preference, but for me, Aperture just edges ahead. The editing facilities are nowhere near as powerful as Photoshop, etc. but if you just want to fine tune your photos to get the best out of them (straighten, crop, tweak colours, remove blemishes, etc), then Aperture/LR are perfectly adequate.
I store all my photos on an external Firewire drive and reference them from within Aperture. That way the originals remain entirely under my control and can be backed up etc the way I want. The database contains only metadata and editing information; and it's only a minor disaster if it gets corrupted.
I use a <YYYY-MM description> folder name and keep the originals as-is. Aperture renames the versions as <YYYYMMDD HHMMSS>. This can be a problem because at 4fps you get four images with the same name!
There is no doubt that things slow as libraries grow; but as Jonny points out above, if they're that big it's as well to split them.
I think it's probably better to think of Aperture and LR primarily as powerful image management tools that include some (now quite good) editing features. Conversely, Photoshop is primarily an, extremely powerful, image editing tool.