1. You have to keep in mind that the user always knows more than the database engine itself. In addition to that, the administrator has access to a lot of external information about the operating system, hardware, user patterns, and all that. A database engine cannot decide whether a user's query is pointless or not—it does not know its purpose. Therefore, administrators and developers are always at an advantage over the database and are, therefore, necessary (and most likely always will be). 
2. No. My company provides services for thousands of businesses. We rarely see cases of database corruption—and if there is corruption, it is usually caused by hardware issues.
3. Usually not, unless you are using the database in a suboptimal way. In general, PostgreSQL does a lot of stuff on its own and takes care of many things, such as VACUUM, automatically...