The difference is much more noticeable on photos taken in pitch black, but in those situations, the images came out noisy and blurry, even though colors are accurate. Google said that Night Sight measures the natural shakiness of your hand as well as how much motion is in the scene before you press the shutter button. When there's less movement, the software spends more time capturing light for less noise. Otherwise, it uses shorter exposures to minimize blur. In most cases, this appeared to work well, save for the example below, which was shot in my closet in complete darkness.

Left: shot with Night Sight. Right: shot in regular Camera mode.

Google recommended that we test Night Sight on a Pixel 3, which already took decent shots in low light. But I still saw a noticeable improvement in quality in some photos in a dark corner in my kitchen. Colors were more saturated, and details like the ridges on a jar's cap were sharper.