Does anyone know whether there's an upper limit to the number of keywords possible in Lightroom without experiencing a lack in performance?
Your question isn't correctly written. I'll try to untangle it, tough.
You cannot say that till "n" keywords you won't get any performance degradation and from "n"+1 (ok, "n"+100 or whatever) you suddenly will feel it.
The performance degradation is a quite incremental process. It is rather an oblique line and not a stair-like graph. Also, of course, it depends on the hardware on which you're on.
That said, Lr organizes internally the keywords in several tables which are rather fat (wide - with many fields) in comparison with other programs thing which means that in some situations (queries) it will be (much) slower than other programs. I have practical experience with eg. XnView MP which is much faster in these things because it has just one table for keywords and this is almost a half wide (7 fields vs. 13 in Lightroom).
So, Rule #1: In Lightroom try to keep your total number of keywords at minimum. But if you cannot then there are other, better solutions.
Besides of the keywords table there is another table, the table of keywords assigned to each image.
This table is very narrow but, again, here Lr choose to have 3 fields instead of 2 (which is the minimum). Ok, one can argue that it is 33% increase but frankly because the 3rd field is an integer, I don't think that for 100.000-200.000 rows the performance degradation will be so big.
However, if the cardinality (the number of rows) grows, and this happens quickly if one assigns many keywords to each photo, then the difference in performance increases.
So, Rule #2: In Lightroom try to keep your number of keywords per image low. But if you cannot then there are better solutions, but the difference will show up only from several thousands images above.
Also, we have here a corollary:
Corollary #2a: If you cannot afford leaving Lightroom be sure to keep your catalogs small.
In conclusion we have Rule #3:
Rule #3: Lightroom wasn't designed for scalability in mind. If you want such a thing you can get other DAMs.
Disclaimer: While I tested other programs from performance POV (AfterShotPro is one of the best, Zoner has a hardcoded upper limit, ACDSee has a rather slow DB, like Lightroom), as I said, I personally use XnView MP together with Photoshop and/or Photivo (an awesome free RAW editor). It just happens to know well how XnView MP's DB backend is designed vs Lightroom and how is its performance curve. You can download it to see if it fits for you - it is freeware.
Are you wondering about the distinct list of keywords in an entire catalog or are you wondering about the number of keywords for a single image?
I am doubtful that there is any practical limit to the number of keywords allowed in a Lightroom catalog. It wouldn't make sense to have an artificial limit in the software itself but it is possible. Lightroom is based on the very efficient relational database SQLite which should allow for essentially an endless amount of keywords to be added without any performance implications.
On this site people have argued for and against using a single large catalog, but it is not unheard of to have a catalog(not previews, smart previews, etc.) in the 2GB+ range and still have great performance.
Lightroom uses a SQLite database to hold its keyword information. SQLite does have size limits, but they are so high that it simply isn't worth worrying about them. You can have zillions of keywords in your catalog.
HOWEVER. It is not only possible to make Lightroom slow to a crawl, I can do it at will. Heavy keyword use is one of the easiest and most reliable ways to do it.
Here is a catalog of Lightroom keyword failure modes and myths, all culled from my long experience with Lightroom, going back to the pre-1.0 beta days:
The more keywords you add, the slower Lightroom will get.
I don't mean lots of keywords on a single photo here. I mean that if you take a pile of photos without any keywords on them and add one or two keywords to each, Lightroom will be noticeably slower after one or two hundred keyword additions. If you restart Lightroom, performance returns.
If you continue pushing Lightroom this way, you can get it to the point where it takes many seconds to apply a keyword.
I've seen Lightroom sit there, nearly unresponsive, for a minute or two while it churns through its backlog of work.
The computer is not the problem. I've seen this on several different computers, both Windows and Mac, which ranged from reasonably fast to positively high-end. In fact, if you go open up another program, leaving Lightroom churning away in the background, your new foreground program will be fairly responsive, since Lightroom doesn't use 100% of the computer's resources while it is in such a state. I've spent a fair bit of time playing Borderlands 2 while Lr grinds away in the background. There's a bit of a hit to the frame rate, but it's still playable.
If you want to see this for yourself, here's a sure-fire Lightroom tar baby recipe:
Start with a catalog of many thousand photos, with lots (10+) of keywords on each, selected from a nice deep hierarchy of keywords. Don't bother if you're still using a small, flat keyword list.
Now take one of the most popular photos in your keyword set, and move it somewhere else in the hierarchy. Lightroom will re-keyword every affected photo.
Once that completes, add another keyword to any photo in your library. It doesn't have to be one of those you just touched. See the difference?
Relaunch Lightroom. Performance is back to where it was before you started, isn't it?
When you get Lightroom into this state, if you launch Activity Monitor on OS X, you should see that it pegs one CPU core, leaving the others idle. This tells me that keywording is essentially a single-threaded activity. You will also see that disk I/O is fairly low, meaning it isn't being I/O bound. It just isn't using your CPU's full horsepower to rearrange its keyword database.
I assume the same thing happens on Windows, but I haven't tested that.
Reducing the size of your catalog helps, but not all that much.
Whenever I have a big session of heavy keywording to do, I slice the set of photos I want to work on out into a separate temporary catalog. Here's the workflow:
Select all the photos you want to work on
Say File → Export as Catalog...
I recommend that you put it on your Desktop and call it something that clearly marks it as a temporary catalog:
x, etc. You want easy access to it, and you don't want to leave it cluttering your disk once you're through with it.
Press Ctrl/Cmd-S to make sure all metadata is saved to disk for those photos.
Do this even if you have "Automatically write changes into XMP" enabled in your Catalog Settings. For reasons that should be increasingly clear to you, dear reader, Lightroom can get badly backlogged. When you quit Lightroom with pending unsaved metadata, it will save only the minimum information it needs to be able to start back up and continue work. You must force it to finish all pending work before you continue, else you risk a mismatch between the information in the catalog and the information stored in the photo via EXIF, IPTC, XMP, etc.
Say Metadata → Export Keywords... You will see why in just a bit.
Exit Lightroom, then double-click the new catalog you just created to open it instead of your default. Alternatively, launch Lightroom with Option/Alt held, so that it prompts you for the catalog to open.
Say Metadata → Import Keywords... and point it at the file you just created above.
If you don't do this, your new catalog will only contain those keywords present in the photos you exported. You presumably want all of your keywords available in the new catalog.
Do your keywording.
Select all the photos, then say Cmd/Ctrl-S to save the changes to disk. You must do this even if you have the "Automatically write changes into XMP" setting enabled in your main catalog. It's a per-catalog setting, and it defaults to "off" for new catalogs.
(See the next major point for more on this.)
Relaunch Lightroom again, opening the main catalog this time.
Say Metadata → Read Metadata From File to load your changes back into the main catalog.
Once you're satisfied that the changes have all made it back into the main catalog, quit Lightroom and delete the temporary catalog. We don't need it any more.
Having jumped through all those hoops, you should notice that Lightroom is a bit faster when running with the much smaller catalog. I find it to be barely enough of an advantage to bother doing it.
This should convince you that the overall size of the catalog is not the main thing affecting the speed of Lightroom. The keyword set size and the number of keywords per photo have a disproportionate effect on the speed of Lightroom.
Disabling the "Automatically write changes into XMP" setting in your Catalog Settings helps, but not all that much if you have a fast disk.
Tip: Have a fast disk. :)
Ideally, put the catalog file on a fast SSD, and the tree holding your master photos off on a separate fast RAID. (Or if you're really well-heeled, put the masters on a RAID of SSDs!) Keeping the two separate gives the OS opportunities to write to both at the same time, opportunities that aren't available when both requests must go to a single disk.
Smart Collections are a speed hit, particularly if they have keyword-based rules.
The good news is that there's an escape hatch: Smart Collections only hurt performance once they've been opened within a given Lightroom session. If you fold up all your Collection Sets and twist the Collections panel's disclosure triange closed, then re-launch Lightroom, none of your Smart Collections will impact Lightroom's speed.
But beware! If you twist open some of those disclosure triangles and then immediately twist them closed again, the damage is done: the speed hit will continue to affect Lightroom until you re-launch it.
The most reliable way to speed Lightroom up again is to re-launch it.
I spend a lot of time re-launching Lightroom. Sigh.
The easiest way to check this is to import a controlled vocabulary with large number of files. For example, David Rieck, an author of the ControlledVocabulary.com has a LR's version of his CV that contains several thousands of well-structured keywords.
Performance is a wide termin and it might reflect to different aspects of the program. In your case, you might see a degradation of the first program launching, expanding the keywords tree, searching by keywords, metadata syncyng, etc... Plus different PC configuration will have different thresould for performance degradation.
Using hierarchical keyword structure can improve both: program performance and convenience of keyword's management.
BTW our Daminion Server can import > 20000 keywords and work without performance degradation on i3/4Gb PC configuration.