Properly maintaining every aspect of a database is time-consuming work, but the downside of not doing it can be even worse. This is particularly true when it comes to good vacuuming practice, where routine mistakes can build up over time into extremely expensive operations requiring downtime to fully recover. Similarly, monitoring your query logs to be proactive about finding ones that execute slowly takes regular review, but the downside there can also be downtime if your server falls apart under a heavy load.
MVCC will keep routine read and write conflicts from happening, but you still need to code applications to lock resources when only one session should have access to a row at once.
Any time you UPDATE, DELETE, or ROLLBACK a transaction, it will leave a dead row behind (and potentially a dead index entry) that needs to be cleaned up later by some form of vacuum.
...
