The Cleaner module allows the admin to set a schedule for clearing caches, watchdog, and old sessions.
There are functions in Drupal which will cause "expired" entries in some cache tables to be deleted. This is vastly improved in Drupal 6. "Minimum_cache_lifetime" is a partial solution, but still not totally complete.
There are still times and/or cache tables that don't get cleared in any of those scenarios. Many sites will not be impacted by this, but a few will (just search on drupal.org and you will see many posts from people having problems).
Before you dismiss this module as "silly," which I did for a while after writing it, consider an actual experience. I maintain a site that is based on Panels, Nodequeue, and Views. In this set up, the cache entries can get very large after some time. MySql has a parameter ("max_allowed_packet") that limits how much data can be read in. If this limit is exceeded, MySql does not throw an error, rather it just provides what data it could read. With no error indication, the site just goes merrily on its way with what was given to it. In this case both the panel definition and nodequeue contents went haywire. Clearing the cache resolved the issue for the moment.
Then I remembered this module that I had written and dismissed as "silly" after getting it working. Now, I will admit that the hooks provided here probably are "silly" but I had included them originally, so they are still there. I don't know why anyone would use them, but if you want, knock yourself out.
Drupal 7 Status
The first cut at converting to D7 has occurred, but there is no guarantee the the module is functional yet. If you'd like to test it (and submit patches) that would be a big help.