I am genuinely curious of what a good solution would be to this problem, because I love Photos on Mac but its not perfect either, one key thing Photos does insanely well is face grouping.

I use Digikam on a 3 TiB collection and it works great.

What database are you using and did you slowly build it up or what?

The Digikam DB is SQLite on a fast NVMe drive. The photos are on a 4 TB SSD. I have been using Digikam for maybe a decads, so the collection has been growing with me, but no issues so far.