Cavalry was primarily used in two roles during the Napoleonic Wars. The first was screening columns on the march, and the second was waiting as a reserve in battle.
Napoleonic armies typically advanced in several parallel columns, spread across a front of a couple days' march, generally 40-60km in total. These columns could be divisions of 8-12,000 men or corps of two or more divisions, depending on how many good roads were available and how close they were together. These columns could be anywhere from 3-15km long if multiple divisions were assigned the same road. Marching columns, compared to fully deployed corps and divisions, have relatively low endurance in combat; if attacked suddenly by a deployed force, they give way quickly. However, due to their length, they require a long time to deploy into battle formation. To ensure the column is ready to fight by the time the enemy makes their attack, advance warning is needed.
This is where cavalry comes in. The most indispensable role of cavalry in the Napoleonic Wars was to search out the enemy, relay their location, strength, direction etc. to the commander, and serve as a speedbump to check the enemy advance. The way this was done was usually by sending a cavalry force ahead of the column's march; on the way, it would break up into large posts, and then into small posts, then patrols and individual mounted sentries, as it found good points from which it could observe the surrounding area, keeping a special eye on roads, pathways, and stretches of open country. These posts could be up to 15 km to the flank or in advance of the main line of march, depending on where the column was in the army's array. Other detachments of cavalry would attempt to probe the enemy's position, ideally using concealment to ambush enemy detachments and so take prisoners to interrogate; countering the enemy's attempts at this was another crucial part of cavalry's operational mission.
Once contact was made with the enemy's advance guard or main force, cavalry functioned as a speed bump. The enemy can't advance past them without deploying for combat to force back the cavalry outposts, which slows the advance, thus giving friendly columns time to deploy into their own battle formation. In addition to cavalry in outposts, cavalry would also be kept in the advanced guard of the marching column. Together with light infantry, these fast-deploying forces were the first in action against the enemy. In this way, cavalry ensures the friendly columns are not attacked in marching formation. More cavalry makes an army more mobile; controlling more of the space between opposing armies allows one to arrange supply requisition further ahead, among other advantages.
Once the battle actually begun, cavalry was best used as a reserve. Due to their speed, mounted forces could reach any point on the battlefield in a relatively short time. Rather than the flanks, the typical position of the cavalry was thus behind the infantry, making it easier to use wherever they were needed. This was especially useful for the grinding battles of attrition that came to characterize warfare at this time; cavalry could attack an enemy while it was still disorganized from an engagement, either turning a local reverse into a small victory or completing the destruction of a repulsed enemy. Against troops not in formation,* any fresh and organized reserve is practically invincible, so cavalry's speed makes it good for exploiting this advantage to the fullest.
A good example took place during the battle of Waterloo; D'Erlon's corps attacked the center of the Allied position, driving in a brigade in disorder, but was stalled and thrown into disorder by the weight of fire. Two brigades of British cavalry from the reserves then counterattacked with such force as to overrun two regiments and capture their Eagles, along with 2,000 prisoners. Conversely, though, the French had cavalry reserves of their own; the British cavalry, thrown into complete disorder by their impetuous attack, quickly gave way. These French cavalry in turn fell into disorder in the violent combat and were also driven off by fresh Allied cavalry reserves.
In this tactical capacity, though, cavalry are less indispensable than they are operationally. A battalion of 800 foot, a squadron of 150 horse, and a battery of 8 field guns cost roughly the same to raise and upkeep. As armies became more skilled in employing firepower, infantry and artillery increasingly delivered more 'bang for buck' than cavalry did. Using firearms from horseback is obviously very hard, so the firepower of a cavalry squadron was usually a negligible quality. Cavalry are also rather unsuited to defense**. Artillery had the most firepower, and was considered the most destructive weapon, but obviously lacked the ability to charge or even stand and fight against an enemy attempting to storm its position. Infantry, though, combined firepower, offense, and defense in one weapon, suitable for all kinds of terrain.
As such, a battalion of infantry, costing the same as a cavalry squadron, was in many ways a considerably stronger unit. At the battle of Lutzen in 1813, Napoleon only had about 5,000 cavalry to the enemy's 25,000, but with 90,000 infantry to the Allies' 40,000, he was still able to win the battle. If one were to 'equalize' the forces on the basis of above-mentioned expenses, the advantage of infantry becomes even more apparent; Clausewitz asks rhetorically if it's even possible to imagine 140,000 infantry and 5,000 cavalry losing to 40,000 and 25,000, respectively. In a crude sense, one might say cavalry costs 5x as much as infantry, but delivers only 2.5x the abstract combat power, so a squadron has only half the total power of an infantry battalion. More immediately, infantry's firepower makes it very strong against cavalry, as Clausewitz knew from personal experience; horsemen fear nothing more than being shot at clothing-burning distance, so as long as the infantry keep some of their fire in reserve (for example, a three rank formation using the fire of only the first two ranks, ensuring the muskets of the third are always loaded), it is very hard for cavalry to force themselves to charge home. That said, actual war is not so simple; the qualitative advantages of cavalry give it importance out of proportion to an abstract calculation of combat power.
Cavalry's role as a reserve becomes more important towards the end of the battle. For the victor, cavalry still in reserve are perfect for spearheading a pursuit; if the enemy's cavalry were completely defeated by this point, the results against the enemy infantry could be very great indeed. This however is precisely why the loser of a battle typically retreated before he was forced to: with yet-uncommitted reserves, especially of cavalry, he could form a strong rearguard to stymie the enemy's pursuit, thus keeping the army from collapsing completely. Coming back to the battle of Lutzen, the Allied superiority in cavalry ensured that even as they began their retreat, the French were forced to keep a respectful distance, preventing them from exploiting their success and capturing any trophies for their victory. The main dividing line between a defeat and a complete downfall is the ability of the army to form a rearguard to stem the incoming tide. When it can no longer do this, as after Jena and Waterloo, the army has completely dissolved, and the cavalry will be able to make tens of thousands of prisoners and capture the whole artillery of the enemy.
*Formation has a dual meaning, on the one hand physical, the other psychological. Physically, one can say troops are in formation when attacking one means exposing yourself to the attacks of their neighbors; if you're close enough to stab one Frenchman in a square formation, you're close enough for two of his buddies to stab you. Psychologically, formation requires the soldier to feel the eyes of his commander and comrades upon them; self esteem and the desire to not appear a coward before them keep him in his assigned spot.
**Cavalry are effective because of their charge, which can be employed defensively, but this usually requires good open ground, as cavalry charges in terrain broken by forests, villages, streams, hills, and so on are less effective. At the battle of Aspern, cavalry covered the space between Aspern and Essling, charging and so paralyzing the Austrian thrusts in that sector, thus preventing the two French strongpoints from being cut off from eachother and their line of retreat.