Good question, Marvin.
From my experience, the answer is that it depends on your needs and your way of working (or your organization's). If you work in a collaborative environment with a team of people using PDI, CDE, Report Designer, etc., it makes sense to create the repository in a robust database like Postgresql, SQL Server, Oracle, etc. The Hypersonic repository is meant to be a "demo" repository to facilitate software testing, but not for a production environment. However, many Pentaho CE users nowadays only use PDI and find it more practical to use a file system with a version control system like Git, so they discard the use of a database repository.