I think it's important to note that whilst there can be side-effects, there are very often not, or where there are, they are mild and can often be dealt with by adjusting the type of drug used or the dosage. Obviously there are cases where the treatment turns out to be unsuitable - either not giving the required benefit, or producing intolerable side-effects. But this is actually rare. Death as a side-effect is exceptionally rare - malignant serotonin syndrome is lethal if it is not detected and treated but it is very unusual and recognisable (and anti-psychotics are avoided if someone has Lewy Body Dementia, as the syndrome is a much higher risk in such patients). There is a slight but currently unknow increased risk for stroke in patients with dementia - but the risk is small to start with - so even a risk doubled (which it isn't) is a small one if it starts at less than 1% anyway.
It's true that the brain remains a mysterious organ. However, medicine is full of long established, safe and effective treatment where no one knows the exact way they work. What the drugs do is very well known even though the precise functioning of the brain is not.
I strongly feel there is a place for these drugs if appropriately used. Most of the horror stories involve their inappropriate use - usually in a care home situation with residents heavily drugged into passive or sedated states for convenience.
I do not agree that the only answer is "better social care". Carers, professional and otherwise, have rights too. It is impossible to live with someone who is highly paranoid, delusional or agressive. It not only renders a very poor quality of life for the carers, it does the same for the caree as well. You do not have a quality of life where you are plagued with constant paranoid beliefs or frightening hallucinations, spend all your time in a state of anxiety or end up lashing out at everyone around you. You are miserable, you make those around miserable, you make it impossible for them to provide the needed care. These drugs can make the difference between remaining at home or going into a secure care facility because you become a danger to yourself or those around you. The former option is surely the better for everyone.