Hello, Vex Ing,
I am still trying to keep the system Photos library small. It has not grown much over the years. Currently it is containing 54094 photos and 594 videos.
It is not because of performance issues, that I want to keep the library small. I am using iCloud Photos to sync the library between my Macs and the iPhone and iPad. So the library needs to be small enough to fit onto the device with the smallest storage; my iPhone has only 512GB storage, and I do not want to have to fall back on "Optimise Storage". I am frequently finding myself without internet access, for example, when travelling, so "Optimise Storage" is out of question. And I prefer to have all photos locally available to be able to use Time Machine for backing up the library.
Another reason for trying to keep the library moderately sized is the user interface of Photos. Some features have not been designed to be used with a large library. For example, the People album is just a flat list, and no tools to sort it automatically or to structure it by grouping related persons in to folders or similar. We only can sort it manually by dragging the faces around, across many, many pages of huge thumbnails and risking to merge people accidentally by dropping the face we are dragging onto another face. The Keyword Manager is also showing only a very long, flat list of keywords, when we try to edit the keywords, and needs a lot of scrolling.
Apple is adding more and more artificial intelligence to Photos to get the most common tasks automatic. And the background processes to analyse the library are needing more and more processing power. Most of these tasks seem to need a processing time and storage that is proportional to the number of items in the library, for example the classification of the objects and scenes according to categories, but for some processes the complexity seems to be of a higher order, for example the curation of the of the best photos and composing a layout, the duplicate detection, because it requires to compare the photos with each other.
There are some recent improvements. The neural engine in the new Macs helps to make the background processing a lot faster. With a new Mac you may be able to cope with a large Photos Library much better than on an Intel Mac.
With macOS 12 it is easier to work with several libraries. We can now import photos directly from one library into another library, lossless - the edited versions will be stay stacked with the original when importing. There are some limitations, however: Importing photos directly from another Photos Library into the currently open Library
And it is easier to work with photos across different platforms. We can now add captions on an iPhone or iPad with iOS 15/iPadOS 15, and even see the metadata.