I came across these very small pogo pins by Mill-Max, specifically the 0906-0-15-20-76-14-11-0. These are only 0.2" / 5.0 mm long in total and feature a short stroke of 0.039" / 1.0 mm.
Other AVR ISP adapter with pogo pins feature long pins and look difficult to position and solder, but I haven't used any of them so maybe they are simple to use. But as I think with these very short pogo pins it will be very easy to position, I designed an adapter featuring these.
To make positioning even easier I added alignment marks to the outer edge which can match up with marks on a ISP header footprint. I tested one of the pogo pins on a regular TH header footprint with 0.04" / 1.02 mm drills which connects great so I'll probably use a modified header footprint with alignment marks on a future board to test better. Also since I forget every time which direction to connect the ISP cable, I added arrows to indicate that, but it should be obvious since you want to see the pogo pins to align.
A regular TH 2x3 2.54 mm header is on the top which connects to the ISP cable, and the pogo pins are on the bottom side.
Image of pogo pin from Farnell.