SQL & NoSQL Database Solutions

• PostgreSQL: Best For Primary relational data (users, orders, products, billing, organisations). Key Capabilities ACID transactions, row-level security, JSONB, full-text search, PostGIS, logical replication, 35+ years production stability. When NOT to Use Write throughput exceeds 100K TPS on single node, or data model is genuinely document-oriented with no joins required.
• Redis: Best For Caching, session storage, job queues (BullMQ), rate limiting, real-time pub/sub. Key Capabilities Sub-millisecond latency, Lua scripting, sorted sets, pub/sub, Streams (append-only log), Redis Cluster for horizontal scaling. When NOT to Use As a primary data store for data that cannot be lost Redis is in-memory by default, persistence is optional and adds overhead.
• MongoDB: Best For Document data with variable schemas (CMS content, product catalogues with variable attributes, IoT sensor payloads). Key Capabilities Flexible document model, horizontal sharding, aggregation pipeline, Atlas full-text/vector search, change streams (CDC). When NOT to Use Data has relationships that require joins MongoDB's document model makes joins expensive, a sign the data is better modelled relationally.
• DynamoDB: Best For High-throughput key-value and single-table access patterns at serverless scale (gaming leaderboards, shopping carts). Key Capabilities Single-digit millisecond at any scale, serverless (no cluster management), global tables for multi-region, DynamoDB Streams. When NOT to Use Queries require flexible ad-hoc access patterns DynamoDB requires access patterns to be defined at design time, not discovered later.
• Elasticsearch / OpenSearch: Best For Full-text search, log aggregation, time series monitoring, faceted search. Key Capabilities Inverted index for full-text search, relevance scoring (BM25), aggregations, Kibana/OpenSearch Dashboards visualisation. When NOT to Use As a primary transactional database Elasticsearch is eventually consistent and does not guarantee ACID transactions.
• ClickHouse: Best For Real-time analytical queries on large datasets (product analytics, business intelligence, event data). Key Capabilities Columnar storage (100x faster aggregations than PostgreSQL on analytical queries), vectorised execution, lossless data compression (10:1). When NOT to Use Transactional workloads ClickHouse is an OLAP database, not OLTP. Point lookups on single rows are slow compared to PostgreSQL.

DynamoDB's single-table design is an architectural pattern where all entity types (users, orders, products, sessions) are stored in a single DynamoDB table differentiated by their primary key prefix and sort key structure. The rationale: DynamoDB is optimised for single key-value lookups and range queries on the sort key; joins across tables require two separate reads at the application level. By placing all related entities in the same table with carefully designed sort key structures, you can retrieve a user and all their orders in a single table query. The controversy: single-table design requires knowing all access patterns at design time (it is very difficult to add new access patterns post-launch without a full reindex), it is counterintuitive to relational database thinking (requires a mental model shift), and it makes the data harder to inspect and debug. ClickMasters uses single-table design for DynamoDB when the access patterns are well-defined and stable, and recommends PostgreSQL when access patterns are exploratory or likely to evolve.

Polyglot persistence is the architectural approach of using multiple database technologies each selected based on the specific requirements of the data it stores. A typical polyglot stack for a B2B SaaS product: PostgreSQL for transactional data (user accounts, billing, application state), Redis for caching and queues (hot data, session tokens, background job queues), Elasticsearch for search (indexed from PostgreSQL via CDC users search products, support agents search tickets), and ClickHouse for analytics (event data from Kafka, aggregations that would be too expensive in PostgreSQL). The benefit: each database is used for what it does best. The cost: operational complexity (multiple database technologies to monitor, back up, scale, and update), data synchronisation (changes in the primary database must propagate to derived databases CDC pipelines, consistency delays), and developer expertise (the team needs proficiency in multiple database technologies). Polyglot persistence pays off at scale; for smaller teams and products, PostgreSQL with Redis is sufficient for the majority of use cases.