The number of potential links in a graph of n vertices scales like O(n^2). That does not mean that the actual number of links in the knowledge base should scale like O(n^2). However, it could mean that the overhead work to maintaining and building the connections in the knowledge base becomes unbearable. That is, the solution is not saving time anymore. Solutions like these need, imo, really good link prediction solutions to make it easier to manage them.

Are any of these personal knowledge base solutions offering link prediction as a feature to alleviate this problem?

I think this depends on the approach to links. If you try to link on recall instead of create, it is much simpler.

Wikis work for notes if linking to other pages isn't a chore and is either completely automatic or so simple you can do it while you write.

If I need to start adding some (link markup)[linkmarkup.example] while writing, I can't be bothered.

> are notes in Roam/Obsidian/Logseq/Joplin/Dendron useful on the long term?

Very useful but the UX is very far from perfect. I would change/add dozens things in Obsidian. I also hate storing notes in a tree hierarchy - a very annoying overhead. I want it to be a freeform graph only with no taxonomy requiring a note to be located in a single specific place.

What I actually want is an open-source (fundamentally extensible but not necessarily free - I would pay up to $100 one-time if I really like it and probably donate more once it becomes an integral part of my life) offline sqlite-backed hybrid of Notion and Obsidian.

The most important feature I miss in Obsidian (as long as it is file system based) is a second file tree - dragging a note file to a distant (not fitting in the screen) place in one panel is painful.

Try Logseq. It fits your requirements.