Do academics worry much about the growing insignificance (for lack of a better word) of their work?
This looks like a personal question, so I'll give a personal answer. I have no idea how widely my opinion is shared, so don't attach too much "statistical significance" to it. IMHO, most of us (myself included) have never had any chance to make any "major breakthrough" in anything by ourselves. Such breakthroughs can occasionally be made out of the blue (at least, in mathematics) but more often than not they just occur when the time is ripe meaning that the relevant tools and ideas get slowly developed to solve lesser problems, many of which will be later just forgotten. So, I view my task as merely trying to figure out things that haven't been figured out yet in the hope that in the process I'll find some new approach or, at least, a new twist of an old technique that will come handy later when some people smarter than I will solve some "big problems". The nice thing (again, I can confidently say it about mathematics only, but it may apply to other sciences as well) is that our knowledge is (at least currently) resembling not a solid disk like in that famous depiction of PhD, but rather the Sierpinski carpet, so you can make a small side step from any position and find yourself on an uncharted territory where life immediately gets interesting enough to render all your existing knowledge if not totally irrelevant, then, at least, rather hard to use. Moreover, you don't need to search hard for those side steps.
is any knowledge gained worth the effort I would say yes, but not
simply because the academic enjoys studying the topic.
To start with, enjoyment is a rare event. The normal state is somewhere between slight frustration and deep depression and the main feeling is that of being totally inept and stupid. Normally with any problem worth being called by that name you start as "a blind kitten in a dark alley" going by touch, bumping your head against everything, and backtracking from dead ends at every turn. Slowly you may start seeing some light and distinguish some shapes (the more alien they look, the more interesting). Finally, if you are lucky (more often than not that event never comes), your eyes suddenly open fully and you see the surroundings and your way through. Then you have your few minutes of "enjoyment".
The value of any piece of knowledge comes primarily from the fact that the connections between problems, their relative significance, etc. are totally unclear until everything is done (by which time one writes short end elegant textbook expositions for students using about 1% of the work that went into obtaining a theorem). You cannot possibly predict what effect the solution of any particular "small problem" will have "on the big scale" as long as that solution is not achieved by totally routine means that have been available before. Usually you are just collecting sand grains that will be later mixed with water and cement powder to make blocks that later will be put together to form walls of magnificent buildings that together will form the city we call "scientific knowledge". Thinking of great cities, one rarely thinks even of cement blocks, let alone the sand particles (though some individual buildings may still come to mind and, on rare occasions, even the architect's names, but not the names of more numerous masons that did the work). The same with mathematics. The collectors of sand particles get always forgotten and the sand itself just becomes "common building material" available in abundance, but it is an endless process of sand collecting that makes the higher level building possible in principle. So, I'm personally quite content with being a "sand collector".
One may argue endlessly about whether there exists a qualitative difference between the process of obtaining a mathematical equivalent of a sand grain and that of building a grand theory. I would just say that a good theory builder works at higher level than a bad sand collector and a good sand collector works at higher level than a bad theory builder and stop at that (like a good CEO is worth more than a bad janitor but a good janitor is worth more than a bad CEO).
As to "most large contributions have already been made", I doubt it very much (though, by the very nature of the question, I am unable to come with explicit examples of the large contributions to be made within the next 50 years). The life is abundant with crazy twists and so is science. One just plays the game and sees how much he can score, leaving the erection of various "Halls of Fame" to future generations. Let me ask you one question however: you mention "general relativity" and "quantum mechanics". How well do you know either one? (I should confess that I'm almost a total ignoramus). If not really well, then those "major contributions" haven't happened in your life yet (though you can benefit from them indirectly), so it may be a good idea to scale down a bit and see how many relatively major things happened recently in the domain you have good knowledge about (say, within the particular area of research you work on) and then project the number upward making (perhaps, unwarranted) assumption that you just don't see the whole large picture but it is not dissimilar to the smaller scale one.