Both "i" and "y" may mean that the family of that person is from a particular geographic location.
The reasons why are there family names with a "y" termination may vary.
The first answer that came to my mind upon reading your question was the possible noble origins (like the Eszterházy and Horthy names you mentioned) of those with the names finishing in "y". Most people are told so in school about these names. However, it seems that this is not always the case.
Further reading about the subject showed, that the preferences of the nobles to finish their names varied from one geographic location to another. An article
I have found says that those nobles who lived to the north or east from the Tisza river used the "i" ending, while those to the west from the river used the "y". There are noble families, whose names finishes with "i" (such as Rákóczi, Teleki).
Be warned though that there are digraphs in Hungarian language, such as "gy", "ly", "ny" (there are others, and even a trigraph, but those have no "y" in them). There are cases, where the "y" is just the latter part of these digraphs, like in the "Nagy" name, which is very common. Nagy also means big, great, large in Hungarian as an adjective.
For Example: Charlemagne = Nagy Károly, Alexander the Great = Nagy Sándor, etc.
Hope you find my post helpful