As a physics student, I think diffraction is fascinating.
As a photographer, I think diffraction sucks. We can't stop it, but we can minimize the badness.
Diffraction softens the details in an image and has a noticeable impact on the image quality of your photos. I remember reading about diffraction in an old copy of Modern Photography magazine (1980s I think).
In a nutshell, the smaller the aperture, the more diffraction will impact your image.
There is a point in stopping down where your images stop getting sharper and start losing detail. That's diffraction taking over. To avoid diffraction, just avoid stopping down that far.
How far? How low can you go? Instead of doing a calculation, do a simple test. An open camera test.
Grab your tripod and your favorite lens. Find a detailed subject. The closer your can get - the higher the magnification you use for the test - the easier it will be to see diffraction. My macro lens is my favorite, so I'm all good here. Really though, just shoot whatever you want because it will tell you what you can tolerate in your normal use case.
Focus carefully. Keep the camera rock-solid on your tripod. Use your best ISO setting. Use a remote release. Use mirror lockup if you have an SLR (live view!). Use electronic shutter if you have a mirrorless body. Or use a flash to keep the speeds up and avoid vibration. In other words, use good technique to make sure you aren't seeing other problems like vibration or bad focus. Aperture priority is easiest.
Start with your aperture wide open, and make an image at every stop until you hit the smallest aperture on the lens.
Evaluate the results. Here is what you should expect from most lenses: The details will sharpen as you stop down, you'll see an "optimal" aperture for the lens, and then the details will soften beyond that. In APS-C and "full frame" cameras, what I see is optimal sharpness usually at f/5.6 or f/8, still good but a little softer at f/11, more softening at f/16, and downright soft at f/22 and smaller. I usually don't go past f/11. When I want to slow down the shutter even more, I use ND (neutral density) filters.
Note that no matter what camera I have used, the apertures above have always been the answer I've found. I'm happy up to f/8, I'm okay with f/11, and I avoid smaller apertures most of the time. That has been true within the limits of the cameras I've used... regardless of sensor size (APS-C or 'Full-frame'), film vs digital, or pixel density. Some people perform calculations for DLA (Diffraction Limited Aperture) but using your eyes is the only way to tell what you can tolerate.
Check out the progression from f/8 to f/22 on this coin (all at 100% magnification):
f/8:
f/16:
f/22:
So naturally, I didn't know if I should laugh or cry when I saw someone comparing two brands, had one stopped down to f/22 (and the other not!) and complained about the f/22 image being soft. His conclusion was that the lens wasn't sharp. Sigh. The reality: we have no idea how sharp that lens was because at f/22 there was zero chance of a sharp image.
Friends don't let friends use f/22!
For more details, read this: https://en.wikipedia.org/wiki/Diffraction
