"Simply put, a preposition indicates a relation between things mentioned in a sentence."
For example, in the above sentence, between is a preposition. There's the noun "relation", and the noun "things". The word "between" introduces a relation between them.
Wiktionary defines "preposition" as follows:
A closed class of non-inflecting words typically employed to connect a noun or a pronoun, in an adjectival or adverbial sense, with some other word: a particle used with a noun or pronoun (in English always in the objective case) to make a phrase limiting some other word.
Merriam-Webster says:
Definition of PREPOSITION: a function word that typically combines with a noun phrase to form a phrase which usually expresses a modification or predication
If you are still not quite sure what that all means, there's a list of English prepositions over at Wikipedia.
Also, as Kosmonaut points out in his comment, prepositions usually come before the words they modify — hence the name, "pre" + "position". However, note that there are mechanisms in place that allow prepositions to be "detached" from the words they modify; see, for example, these questions:
You can browse the full list of questions tagged "prepositions" for further insight.
In Ancient Greek, it is assumed that most, if not all, prepositions were once adverbs. That is why most prepositions can still be used as adverbs starting a sentence in Ancient Greek, as in "upon [that event], the King refused to...". If the same applies to the Germanic languages, it accounts for the existence of our ubiquitous phrasal and separable verbs, which still use "prepositions" in a non-prepositional way. The fact that new phrasal and separable verbs can still be created supports this hypothesis.
On a side note, the preverbial affix e- (the augment) for the past tenses in Ancient Greek probably came from an adverbial constituent **he* meaning something like "past" or "then". (There is evidence suggesting that this is cognate to the Proto-Germanic prefix that is used with past participles in German and Dutch, ge-, though others suggest instead that ge- is related to Latin con-, Greek sun-, "together". If the latter, I don't know whether **he-* would be related to con-/sun- as well. In any case, Old English used to have ge- as well, the vestiges of which can still be seen in many words, such as a-like (Dutch gelijk) and e-nough (Dutch "genoeg"). For more on the English prefix, see the question What we've gelost.)
It is my theory that most elements of syntax are relatively new (some post-Proto-Indo-European) and originate in separate words that melted with content-words and turned into affixes by clisis (enclisis, proclisis, etc). There is evidence that points to this for inflection: it is believed that, say, the dative ending -i was once a separate word, perhaps some postpositional adverb, which accompanied the direction of an action. Evidence for this might be the use of certain suffixes in Greek that are sometimes interchangeable with cases: -the(n) is mostly a (poetic) suffix of separation, which is normally expressed by the genitive; but it can often take on other functions of the genitive too, such as possession. And there are other suffixes that imitate partial cases: -de for direction (instead of accusative/dative/preposition), -(s)ô approximately for a forward position.
The birth of relative pronouns (classical hos) and demonstrative pronouns (classical houtos) in Ancient Greek is sometimes estimated to be not long before the time of Homer, because, in his epics, there is usually no difference in form between these pronouns and the article; it is often hard to decide on the interpretation of an instance of to (neuter article in classical Attic) or hos (masculine relative pronoun in Attic), when all three options seem possible for each (article, demonstrative, relative). This is evidence that syntax can also develop from differentiation between allophones/allomorphs, or out of nowhere.
Best Answer
What a neat, thoughtful question. In my writings, I've only ever used therefore, thereby, and therein (with the expression "Therein lies the problem.") I think it's the same for many other average Joes :) I wouldn't consider someone who employs the other ones when talking "insane," just quite quaint (in a good way).
Google Ngram colourfully conveys that, while most its siblings have been steadily obsolescing for the past three centuries, thereby stablized to a comfy plateau in the mid-19th century and only in the last decade does it show a minimal amount of decline.
Thereof used to be quite a favour among writers but is now even less common than thereto, which itself is pretty rare but had seen a resurgence in use in the '50s.
It's only logical to compare this cohort to their vis-à-vis: The "here-" adverbs. Even the most popular hereafter has always been much less prevalent than even thereof. (Note the extra zero to right of the decimal point on the y-axis.) The Here Family is even more formal and archaic, as a whole, than the Theres.
So you were right-on in your assumptions. Last productive since centuries ago, (t)here + prep. are now very limited constructions. They're once-thriving dynasties that mostly came into existence in between the 9th and 11th centuries, just before Old English began to evolve into Chaucer's Middle English.
The last progenies to the families were therefrom (1250s), hereat (1550s) and herefrom (1590s), which all practically died out a couple of centuries after invented, as people started to slice these concise words into separate and more flexible units or simply opted for other prepositions altogether, as indicated by the Oxford Engl. Dict. Moribound, they survive on linguistic respirators such as officialese and legalisms.