You want databases to be boring. Stick to tried and true stuff so you can focus on your product rather than beta testing fortune-500 crap.
B) there’s a lot of tricky stuff with indices on PG and you generally need a DB admin from day 1
C) your comment is probably more appropriate for either layered databases or new fangled stuff like time series or graph db’s etc
Sorry this is absolute nonsense. Any software engineer worth their salary should be comfortable working with RDBMS index concepts and interrogating their relational model to determine best practice and direction for table indexing.