As mentioned on the product page “It’s a way to create galleries as part of the editorial content on your website”. So, for a few select galleries created by admins. Unlike Gallery, you get a clean “editorial look” and you can add text where necessary, including text-only pages for chapters, intro and so on. I specifically created that with a use for a museum in mind, where we want to create clean virtual exhibitions which guide the user step by step through the exhibition.
The Gallery app is centered around user-content and much more powerful in that regard. Ordering, image manipulation and so on.
SuperGallery uses the stock Pages features, as you can see in the video. Title, Content Field, Record Image. You could uninstall SuperGallery and would have a regular working Pages database, just without the gallery look.