Mind blowing. Literally.
Point Lookout takes you on a boat ride to the swampy Point Lookout in Maryland, an area full of cults, ghouls, Swampfolk and madmen. Along the way, you'll meet a Ghoul colonialist, lose a piece of your mind, have the best vision sequence in a game ever, and possibly even kill a brain in a jar. FUN TIMES!
My favourite non-BS DLC by far, this is just an amazing journey from start to end. You get to battle cults, inbred madmen, ghouls by the ton - and that's all within the first section of the attached quest. The sidequests are awesome, the locations are awesome, the plot is fantastic - and then that psychedelic sequence kicks in, and it gets even better. It even has the best collection achievement - searching the entire bog for locations. It even ties back to the main landmass in a sidequest, and to one of the most awesome locations to boot. Simply superb.
Achievement-wise, there's the 3 main quest ones to pick up, and an all-locations one for big points.