It very much depends on the type of documents you are looking for, as they can be found in different archives. I will talk about Spain, as you mention it explicitly.

The administrations of the different eras generated all kinds of documents, and depending on their nature you have to look for them in different archives. Furthermore, nowadays the regional administrations have varying procedures, so the location can differ from place to place.

One of the better known type of records is ecclesiastical documentation. Depending on the diocese or archdiocese, the archivistical practices vary. In the case of Valladolid's archdiocese, they opted for centralising the posession of historical records into one archive, located in the upper rooms of the cathedral. So, if you are looking for baptismal records, marriage registries, accounting books of the several parishes, from Tordesillas, Medina del Campo, Mayorga, or other place, you will find them in Valladolid. The archdiocese of León has a different methdology, and they opted for local access to the documents, so the records are kept by each parish. If you want baptismal records from Sahagún, you'll have to go to Sahagún.

The feudal administrations in the period before the abolishment of lordships were managed by each aristocratic family, which makes sense, as it was literally their business. Depending on which nobiliary houses we are talking about, there are more records or fewer records. For example, documents relating to many of the most prominent houses are kept in Toledo, in the Archivo Histórico de la Nobleza. One dynasty that kept extensive records and preserved them for unspeakably long times is the Ducal House of Feria. Their records are in Toledo, and they are in process of being digitised. The Ducal House of Alba keeps their records in their palace of Liria (in Madrid).

If you are more inclined to find documents pertaining to Inquisitorial activity, you would have to pay a visit to the Archivo Histórico Nacional, in Madrid. That's where the archives of the Inquisition are held, duly catalogued by inquisitorial jurisdiction.

More ecclesiastical records that could be of use, depending on the territory on intends to research about. The documents from the knightly-churchly orders of Santiago, Alcántara, and Calatrava are also held in the Archivo Histórico Nacional. Other ecclesiastic orders, such as the Trinitarians also have their records in the AHN (dockets Códices 118, and Códices 120 contain the registries of Cervantes' ransom, for example). When many monasteries were confiscated in the 19th century, their archives were transferred to the AHN too, like the monastery of San Salvador de Celanova, or the monastery of Santa María de Melón.

In the time of King Philip II there was an attempt at a general census, but it contained far more than censual data: types of commerce in the region, cultivars, land ownership, jurisdiction, taxes paid, etc. The Relaciones Topográficas are far from complete, but they give good insight into several territories in the second half of the 16th century.

Judiciary records are also important and interesting. During the Ancien Regime, the highest judiciary instance was the Real Audiencia y Chancillería de Valladolid for procedures north of the river Tajo, and the Real Audiencia y Chancillería de Granada for those started south of that famous river. The records of both chancillerías are extremely complete, so they can give a lot of information about places that do not exist anymore if any lawsuit involved people from such locations.

The disappearance of populations and municipalities normally led to their records being moved to the municipal or provincial archives of the most immediate jurisdiction, so provincial and municipal archives are other institutions one should look at. Bear in mind that records were kept locally, so with the different wars and disasters some may be missing or partially destroyed.

Then there is also the Archivo General de Simancas, in the castle of Simancas, some 10 km from Valladolid, which is the general archive of anything prior to the 18th century. Many things produced paper trails that ended in Simancas, like the General Registry of the Court's Seal (Registro General del Sello de la Corte), or the Chamber of Castile (Cámara de Castilla), so in their immense documentary troves you can find information of villages that don't exist anymore.

At a local or provincial level there are also the notarial archives. People in the past used the services of notaries the same as today: for formalising business, for registering property, for last wills and testaments, etc. Depending on the place, you can get notarial documentation from the 12-13th century onwards, which is quite remarkable. These archives are normally in the buildings of the Colleges of Notaries (Colegio Notarial), which are the professional associations of notaries.

If anyone from one such ancient or disappeared village migrated to the Americas, then that person must have left a paper trail in the Archivo General de Indias (in Seville), in the section Pasajeros a Indias.

For preliminary searches, I recommend you check out Portal de Archivos Españoles.

http://pares.mcu.es/ParesBusquedas20/catalogo/search

Edit: Added the reference to the notaries. I don't know how I could forget about them.

I inquired a bit to conduct research around my small town as I was helping an archeologist some years ago. For context, I am from a small town in the mountain of southern italy.

While there are a few public libraries which can be entered with a simple pass, they didn't have much more than some of the mainstream secondary sources about the area. By mainstream of course I mean that dozen books written by some local historians years ago.

Any primary source, or other secondary for that matter, was kept in diocesis or family archives which required direct authorisation. Nothing more than a request by phone or some connections with relevant people, but certainly there wasn't a direct easy channel to access the archives.

It makes ot surely hard for anyone who is an outsider firstly to discover such locations, then to obtain access, especially if one doesn't speak the language. Nevermind the absence of comprehensive online catalogues, so one needs to visit in order to figure out if there is anything useful to begin with.

Years ago, a historian from Naples dug into one of these family archived, an old family of notaries which kept documents of just about any transaction from the 1400 (possibly even before that) onwards. He was married locally though, and moved to my town, else I think he would have never even developed the idea of doing what he did.

Occasionally though sources about small towns are discovered elsewhere. A few years ago, a diary was discovered in Venice (if I remember correctly, it was someplace in the north, unfortunately I don't have the sources with me) which told in great detail the history of the siege of my town in 1496. It was an eyewitness account. It did attract quite a lot of historians which published a number of papers about it. I am not sure though if someone went looking specifically for it or if it was a fortuitous find.