The default answer for a new product is PostgreSQL, and the useful discussion is about when that default breaks rather than about comparing feature matrices. Postgres handles relational data, JSON documents, full-text search, geospatial queries and vector similarity in one system, with transactions and constraints that stop bad data existing in the first place. Most products never outgrow it, and the ones that do outgrow one specific dimension of it rather than the whole thing.
The reason to start relational is not performance, it is correctness. Constraints, foreign keys and transactions are how you make invalid states impossible. Document stores let you defer schema decisions, which feels fast early and means that two years in, the same conceptual record exists in six shapes across your collection, and every read path has to handle all of them. Schema-on-read becomes schema-on-every-read-you-ever-write.
Document databases genuinely fit when the shape of a record legitimately varies per instance and you never need to query across those variations - product catalogues with wildly different attributes per category, event payloads from many sources, content management. The tell is whether you find yourself wanting joins. If you do, you chose wrong, and application-side joins are slower and buggier than the ones the database would have done.
For the specialised stores: Redis for caching and ephemeral state, where losing everything on restart is acceptable by design. Elasticsearch when search relevance ranking is a product feature rather than a lookup - though Postgres full-text search is adequate for a great deal more than people assume, and one system you understand beats two you half-know. A time-series database when you are genuinely writing high-frequency metrics. A dedicated vector database when your embedding volume outgrows pgvector, which happens later than vendors suggest.
The decision that costs most later is not which database but how many. Every additional store is another thing to back up, monitor, secure, upgrade, and reason about during an incident - and any data that lives in two places will eventually disagree. Adding a second database should require a specific justification you would defend in writing, not a preference.
One practical constraint that decides it for many teams: run managed unless you have a reason not to. Managed Postgres from any major cloud handles backups, failover, patching and point-in-time recovery competently. The engineering time you would spend operating your own is almost never the best available use of it, and the failure you are protecting against - a restore you have never rehearsed - is exactly the one managed services handle better than a small team.
