Summary: Polyglot Persistence - The Answer Is "Mix and Match"
After 3 years of trial and error, my conclusion is this: "One database is not enough."
Modern services use Polyglot Persistence (multi-database strategy).
- Main data (users, orders, payments): PostgreSQL (ACID guarantees)
- Cache (sessions, API responses): Redis (super fast)
- Logs (real-time events): Cassandra (write-optimized)
- Search (full-text search): Elasticsearch (inverted index)
- Analytics (large aggregations): BigQuery (column store)
Using each tool in the right place is real "architecture."
When I tried to "do everything with MySQL" on my first project, it was like "trying to do all construction with just a hammer." You hammer nails, but you turn screws with a screwdriver. Databases are the same.
If you read this article, you can now confidently say "Our service needs THIS database." And when asked "What is CAP Theorem?" in a technical discussion, you can answer "In practice, it's about choosing CP or AP."