As apple gave both IPHOTO and PHOTOS the ability to have more than one library - it implies they recognize that fact.
That is not necessarily because of performance problems.
You will want to have more than one Photos Library, if you want to separate photos for different tasks.
i have one library with the photos I am using for my job and a different library with private photos.
Another reason to split the library would be, if it simply does no longer fit on the drive and you want to avoid to turn it into a referenced library.
Apple does not document the maximum size of a photos library. But iPhoto and Aperture could both work well with libraries up to 1000000 photos, and and since Photos can open iPhoto Libraries and Aperture Libraries, the limit is at least 1000000 photos.
You will notice the size of the library mainly when making backup copies of the library, when you need to repair it, or during the initial migration. But when working with a huge Photos Library and searching it, the indexing of the library is so good, that the size does not really matter. Splitting the library into smaller entities and having continually to switch between libraries when you need photos from several libraries will slow you down more.
It is not compulsory to use iCloud Photo Library. I am using it, because I like to have my Photos Library in sync between all my Macs, but if you you are using just one Mac, there is not much advantage in uploading the photos to to iCloud.