That said, Postgres have had a somewhat common problem with poorly selected defaults.
I remember that the performance tests back over 10 years ago between Postgres (v7-v9) and MySQL (~v5.0) always showed MySQL way ahead. For a long time people assumed the reason was because MyISAM (the then-default) isn't transactional. Except InnoDB was still faster. Okay, but by default MySQL didn't `fsync()` after writes. But it was still faster when you enabled that, too.
Turns out that Postgres's default memory configurations were either largely unchanged from v6 initial releases a decade earlier, or perhaps more accurately they're simply set to make the DB not a resource hog out of the box. Well, outside of the development environment, most people set up a dedicated server, and they want the server to be a resource hog! Most RDBMSs do that automatically. It's an odd choice.
It's been 6-7 years since I used the platform daily, but I would not be surprised if it was still that way. And given the complexity of the memory configuration [0] it's hard to NOT make "basic" mistakes.
[0]: https://www.postgresql.org/docs/current/runtime-config-resou...
You should start by knowing how other people design the type of DB you're building. A reporting DB is going to work very differently from a transactional DB. If you're using PG's excellent JSONB support as an alternative to Mongo, that's also something you need to consider at the design stage.
pgbouncer is very commonly used in PG setups, to the point where I don't understand why it isn't included in the default build.
Sensible config generators:
- https://pgtune.leopard.in.ua/
- https://postgresqlco.nf/ (this one is more educational and works as a reference as well)
Security intro:
I am really loving Postgres after a foray with Oracle. But I get concerned about using it for large production loads. I welcome recommendations as well.
In general it seems like they could have solved all of their problems within Postgres, but someone at Uber seemed to just know MySQL better and want to switch. They also made other major changes (like removing schemas from certain data stores) that likely gave them more of an advantage than switching could have.