PostgreSQL is where we start unless something makes a strong case against it. It handles relational data, documents, full-text search, geospatial queries and queues well enough that most products never need a second store.
The schema decides the next two years
Constraints in the database, not only in the application. Foreign keys that are real. Enumerations that cannot hold a typo. Money in a type that does not round. Every one of those is a class of bug that becomes impossible rather than caught in review.
Indexes from evidence
We add indexes because a query plan asked for one, not because a column sounded important. Every index costs write throughput and disk, and a table with eleven of them usually has three that have never been used — which is measurable, so we measure it.
Migrations without a window
Adding a column, backfilling and switching over are separate deploys. Long locks are avoided deliberately. The rule is simple: a migration that cannot roll forward safely on a live table is not ready yet.
When the app database stops being the reporting database
Analysts writing heavy queries against production is a well-trodden route to an outage. Once reporting gets serious, it moves to BigQuery and the application keeps a database sized for the application.
Operations
Point-in-time recovery configured and tested, connection pooling in front of it, slow-query logging on, and alerts on replication lag and bloat rather than on disk being nearly full.
Paired with Redis for cache and queues; alternatives are covered under MongoDB.