Pleroma is built using Elixir + Phoenix (iirc), which has excellent Postgres support with Ecto. That could have been a design decision. Postgres also supports some features SQLite doesn't, such as array types. For certain applications (such as tagged imageboards, boorus), that makes anything but Postgres nearly a non-starter. SQLite data type storage can also be a concern.

Chances are, if you have a server capable of running the Elixir + web framework stack, storage space for cached images, and a proxying web server, Postgres probably isn't much of an overhead on top of that.

Bare in mind that each fedi instance dosen't only host the content generated by users, but also whatever content they repeat/reply to. The fact it needs to be stored (or cached) is one reason why there's so much blocklisting/defederation - because people may not want to host certain content from instances known to commonly host it. It may even store more from the public timeline (I'm not sure about the specifics of Pleroma). Nevertheless, even with five users, you can fill up quite a lot of space in a database and on the filesystem.