First off, being a freshwater aquarium enthusiast I am only talking about algae eater fish for the freshwater aquarium and focusing on smaller tanks, simply because if you have the expertise to set up and run a large tank, you are probably past asking questions about algae eating fish.
First off what do I call small? Well anything between 10 and thirty gallons. Anything less than this and you are into micro tanks which are managed completely different to what I call normal aquariums. I am also going to assume that you have a planed tank simply because I believe wholeheartedly in planed tanks in preference to unplanted.
So now on to the algae eating fish. One of my favorites is the Oto Catfish. This little fellow grows to an absolute maximum of two inches, often smaller and lasts quite slim so it's load on the biological filter is minimal. They are a shoaling fish and best kept in small groups of between three and six. They will happily eat the algae off of the glass and plants without causing any damage to leaves. A word of caution, they are quite delicate so only add them to an established tank that has fully cycled and make sure that you acclimatise them slowly. Once they have settled in they are easy to keep. Supplement their algae diet with some pellet food for catfish every now and again.
Siamese Algae Eaters grow to about 4-5 inches when mature so think carefully before adding them to a smaller tank. Having said that they are probably the most effective algae eater, clearing plants and gravel of algae and uneaten food. They can be confused with other fish so always buy from a reputable dealer.
Chinese algae eaters, although great when they are young quickly develop aggressive behavior as they grow so I would avoid them in a small community tank.
Finally the Plecostomus. The Plecostomus is probably one of the most popular algae eaters for beginners and many beginners have lived to regret the day that they got one. Get the wrong Plecostomus and it will grow up to two feet in length, far too big for all but the largest tanks. It will also eat every plant available leaving the tank bare. There are now several rivers in the USA where these fish have become a real pest, destroying the local eco-system after being released into the wild by irresponsible owners.
Do not expect to be excited by algae eater fish. Most of them move slowly and stay in one place for hours on end. Their beauty is in how they keep the tank clean so that you can enjoy all your fish.