What are the best databases for my product/app/website?

It really depends on the nature of the data that you want to store, the relationship of entities in your data, and the number of queries it will be executing each minute, hour, and day. There's no silver bullet.

SQL-based database systems like MySQL don't have scalability issues. Distributed databases like MongoDB, CouchDB, and Cassandra aren't very scalable or stable when it comes to running them as primary databases. Some of the databases that can be considered are mentioned below.

MySQL: A relational database, that serves well for commercial/end-user applications where there's a clear relationship between different types of entities (e.g. customers and products, users and reviews). Used at most companies you've heard of.

SQLite: "SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed database engine in the world." It's used in Android, iPhone, and iOS devices, web browsers (Chrome, Firefox, Safar), PHP and Python, TV sets, automotive multimedia systems, etc. Though it a great for lightweight applications and mobile apps, it's not ideal for a website that performs thousands of transactions per second.

PostgreSQL: An ORDMBS, ACID-compliant, and transnational DB. There's a good comparison here between MySQL and PostgreSQL at MySQL PostgreSQL?

MongoDb: A document database. It stores data in rich structures like maps of maps of lists, which contain integers and floating-point data. Used for scaling (horizontal). Also, used when you've a simpler data model that requires fewer joins and needs a relatively easy development. MongoDb supports easy sharding, much easier than SQL.

Redis: "Redis is an open source, in-memory data structure store, used as a database, cache, and message broker. It supports data structures such as strings, hashes, lists, sets, sorted sets with range queries, bitmaps, hyperlogs, and geospatial indexes with radius queries. Redis has built-in replication, Lua scripting, LRU eviction, transactions, and different levels of on-disk persistence, and provides high availability via Redis Sentinel and automatic partitioning with Redis Cluster."

DynamoDb/SimpleDb: NoSQL db services, accessed through web API. It's a fast, highly scalable database service offered. I've seen it used primarily for caching and relatively less complicated data structures. It is a high cost as compared to DB systems. There's predicable retrieval speed, and theoretically unlimited storage. It's great for storing user sessions, data staging, and as a caching layer of some sort.

Depending on your application, you may have to use multiple of the above. The most commonly used pair is RDBM for data storage and a NoSQL db/caching layer for cache

When choosing a database for your product/app/website, consider these factors:

1. Data Structure:
   - Relational: This type is best for structured data with complex relationships and transactions (e.g., financial systems). Think SQL databases.
   - Non-Relational: This type of database is ideal for unstructured or semi-structured data and offers flexibility (e.g., content management, social media). Examples include document stores and key-value databases.

2. Scalability:
   - Relational: Typically scales vertically, suitable for moderate-sized applications.
   - Non-Relational: Scales horizontally, ideal for large-scale, distributed applications.
   - Distributed: Both relational and non-relational can be distributed across nodes for high availability and global reach.

3. Performance:
   - Relational: Excels in complex queries and transactional integrity.
   - Non-Relational**: Optimized for high read/write operations and real-time processing, often trading off consistency for speed.

4. Flexibility:
   - Relational: Rigid schema is best for stable data structures.
   - Non-Relational: Schema-less, adaptable to evolving data models.

5. Operational Complexity:
   - Relational: Requires structured management but can be simplified with managed services.
   - Non-Relational: Easier to scale but may need specialized tuning for consistency and performance.

6. Security & Compliance:
   - Both types offer robust security, but relational databases are often favored for strict transactional integrity.

7. Cost:
   - Relational: Cost-effective for smaller, stable applications.
   - Non-Relational: Potentially higher costs with horizontal scaling but better suited for large-scale, flexible applications.

Choose based on your application's data structure, scalability needs, performance, flexibility, and operational complexity.

Here are examples of well-known databases for each category:

1. Relational Databases:

• PostgreSQL: Known for its advanced features and extensibility.

• MySQL: Popular for web applications with strong community support.

• Oracle Database: Enterprise-level database with robust performance and security features.

2. Non-Relational (NoSQL) Databases:

• MongoDB: A document-oriented database ideal for flexible, unstructured data.

• Cassandra: A wide-column store designed for high availability and scalability.

• Redis: An in-memory key-value store known for low-latency performance.

3. Distributed Databases:

• CockroachDB: A distributed SQL database offering strong consistency and global scalability.

• Google Cloud Spanner: A globally distributed relational database with horizontal scaling.

• Apache Cassandra: It also fits here, as it’s designed for distribution across multiple data centers.

These examples highlight some of the top choices in each category, depending on your specific needs.