Diffraction is an optical effect which can limit the total resolution of your photography– no matter how many megapixels your camera may have. Ordinarily light travels in straight lines through uniform air, however it begins to disperse or “diffract” when squeezed through a small hole (such as your camera’s aperture). This effect is normally negligible, but increases for very small apertures. Since photographers pursuing better sharpness use smaller apertures to achieve a greater depth of field, at some aperture the softening effects of diffraction offset any gain in sharpness due to better depth of field. When this occurs your camera optics are said to have become diffraction limited. Knowing this limit can help you to avoid any subsequent softening, and the unnecessarily long exposure time or high ISO speed required for such a small aperture.
Although quite technical, this page does help to explain the phenomenon of “circles of confusion”. The lens diffraction problem is something that does affect scale model photographers, because we tend to use the smallest apertures we can reasonably get away with.
The effect in my photos is to give a soft focus effect to objects in the distance. I don’t actually mind it, because it does – to my mind – at a level of haze that is seen in real life.
A way to overcome the problem in small scale work would to use focus stacking, where several images are taken focused at different points, and then combined using software later.