What is the theoretically most optimal way of determining the longterm effects of a drug? Is it large scale population studies in which questionnaires determine frequency of use of a drug and comprehensive, detailed measurements attempt to rate a level on a wide variety of health characteristics?
I don't think it's possible to say any approach is "most ideal"; every approach has drawbacks and benefits, but these are not measurable on the same scale. Comparing the alternatives is about making judgments between those distinct pros/cons.
Free of time, money, and possible ethical constraints, a prospective randomized controlled trial is the best option. I suppose for "theoretically", this is probably the answer you'd find in a textbook, though that isn't specific to long-term effects but rather just about anything. Barriers are the cost of following individuals over time, loss of subjects to follow-up as they change addresses/phone numbers or move in and out of care facilities, ethical concerns assuming the drug you're giving is intended to treat some condition (hence you're maintaining a control group that loses access to any benefits), failure to adhere to the study design (people in the test group stop taking the drug; people in a control group are prescribed the drug or a different one to treat the same condition), and more. There is also sometimes a lack of validity in controlled trials, in that the conditions of a trial do not replicate well in the real-world, where dosing actually does vary: people miss doses or double up, temporarily wean on-and-off drugs as their finances or healthcare personnel change, or people are prescribed drugs despite not fitting into the originally intended population (off-label use, pediatric use, use in pregnancy).
Retrospective designs or natural experiments (such as before and after a drug is introduced to a population, or in different geographic locations that have or don't have access to a drug) can cover a lot of the cost and practical problems with randomized trials, but come with biases that make it harder to attribute causality. There is a lot of selection bias in who gets prescribed a drug. It can also be dangerous to make inferences from certain statistics, like causes of death. One might expect rates of cancer to increase in a population that is newly introduced to a heart medication simply because everyone eventually dies of something, and every person who lives 10 years longer because they didn't die of heart disease has more time to develop cancer.
Mechanistic studies or surrogate endpoints may let you make strong educated guesses about long-term effects without having to wait. For example, changes in liver enzymes may indicate liver toxicity: you need not wait until actual liver disease develops. Same for effects on blood pressure or heart rate.