Luckily Postgres' autovacuum works really well in normal workloads. If there's an even mix of inserts spread throughout time then it's probably best to just rely on it.

For data warehouses inserts can happen in bulk on a regular cadence. In that case it can help to vacuum right after. I'm not sure if it has a huge impact in practice.

Nightmare is bit of an overreaction. It's just a single command at the end signifying "Ok, done for now". It's not forced on users, the system works just fine if you don't do this. It just works better if you do let the system know when is a good time to perform maintenance tasks and collect statistics.

Postgresql is great for OLTP workloads out of the box. I don't think that it's easy (or even possible) to be a great database for both OLTP and OLAP workloads without any tweaking or input from the user.