How to Choose a Database: SQL vs NoSQL vs NewSQL vs Edge

Choosing the wrong database engine for a workload forces expensive migrations later: a product catalog that runs fine on MongoDB at 1,000 records can degrade to full-collection scans at 100,000 without proper schema and index design. The mismatch between data model and access pattern is the root cause, not the database itself. SQL, NoSQL, NewSQL, and edge database categories each carry concrete trade-offs that point to the right choice before the first migration becomes necessary.

For most projects the default is PostgreSQL, with Redis in front of it for the access patterns PostgreSQL handles poorly. Every other category below earns its place only when a specific requirement rules that pair out: write locality across regions, time-series volume, offline replication, or a document model that genuinely matches the domain.

The Cost of the Wrong Choice

Technical Debt Explosion: Switching databases mid-project touches every layer that reads or writes data. Using MySQL for time-series data creates query complexity with excessive date functions and subqueries. Moving to a specialized store like InfluxDB means rewriting the query layer, the retention logic, and every dashboard built on top of them.

Team Productivity Impact: Your choice directly affects development velocity. A team fluent in SQL that inherits MongoDB document queries spends months relearning how to model, index, and debug reads. NoSQL experts forced into rigid SQL schemas tend to over-engineer simple problems in the other direction.

Hidden Operational Costs:

• Self-hosted PostgreSQL: smallest invoice, largest share of engineer time (patching, vacuum tuning, failover drills)
• Managed PostgreSQL (RDS, Cloud SQL, Neon): larger invoice, most of that operational time handed to the provider
• DynamoDB: usage-based invoice and almost no administration, provided the access patterns were designed up front

The cheapest line on the invoice is often the most expensive once engineering time is counted.

Classical Database Categories

Relational (SQL) Databases

PostgreSQL: The Swiss Army Knife

PostgreSQL is an excellent default choice for most projects. It’s boring in the best possible way: reliable, well-documented, and graceful about edge cases. Recent major releases keep improving performance and extending SQL/JSON standard support, so the JSON gap that once pushed teams toward a document store has mostly closed.

When PostgreSQL Shines:

• Complex business logic requiring ACID transactions
• Analytics workloads with sophisticated queries
• Applications needing both relational and document storage (JSONB)
• Teams comfortable with SQL

Where JSONB Pays Off: Teams that move a relational app to PostgreSQL for its JSON support usually do it to delete a second datastore. Storing flexible metadata next to relational columns removes the document store from the diagram, and one engine to tune, back up, and monitor beats two engines to keep in sync.

// PostgreSQL with JSONB - best of both worlds
const user = await db.query(`
  SELECT id, email, 
         preferences->>'theme' as theme,
         preferences->'notifications'->>'email' as email_notifications
  FROM users 
  WHERE preferences @> '{"beta_features": true}'
`);

Gotchas:

• Write amplification with frequent updates (use HOT updates wisely)
• Connection management: use pgBouncer in production
• Vacuum tuning required for high-write workloads

MySQL: The Web-Scale Workhorse

MySQL earned its reputation powering the web’s biggest sites. It’s fast, well-understood, and has an ecosystem built around web applications.

When MySQL Works:

• Read-heavy web applications
• Applications requiring master-slave replication
• Teams with existing MySQL expertise
• Cost-conscious projects (excellent community support)

Read Scaling in Practice: MySQL scales reads by adding replicas and treating them like a cache tier: denormalized data, aggressive indexing, and strategic partitioning. The throughput ceiling depends far more on row size and index fit than on the engine, so measure with your own schema before sizing a fleet.

-- MySQL optimized for read performance
CREATE TABLE user_stats (
  user_id INT PRIMARY KEY,
  total_orders INT DEFAULT 0,
  last_order_date DATE,
  lifetime_value DECIMAL(10,2),
  INDEX idx_lifetime_value (lifetime_value DESC),
  INDEX idx_last_order (last_order_date)
) ENGINE=InnoDB;

Trade-offs:

• Less sophisticated query planner than PostgreSQL
• JSON support exists but feels bolted-on
• Replication lag can be tricky in multi-master setups

SQLite: The Embedded Champion

Don’t underestimate SQLite. It now runs far beyond mobile apps, and with proper configuration it handles surprising workloads.

Perfect For:

• Edge applications with local data requirements
• Development and testing environments
• Applications with <100GB data and modest concurrency
• Embedded systems and IoT devices

Performance Reality Check: reads are ordinary file reads served from page cache, which is why a single node goes further than most people expect. Concurrent writes are the real constraint: even in WAL mode, one writer holds the database at a time while readers continue uninterrupted.

// better-sqlite3: WAL mode for concurrent readers
import Database from 'better-sqlite3';

const db = new Database('app.db');
db.pragma('journal_mode = WAL');
db.pragma('synchronous = NORMAL');  // durable enough with WAL, far fewer fsyncs
db.pragma('cache_size = -64000');   // negative value means KiB, so 64MB
db.pragma('temp_store = MEMORY');

NoSQL Databases

MongoDB: The Document Store

MongoDB gets a lot of hate, often deserved, but it genuinely excels in specific scenarios. The key is understanding its strengths and designing around its limitations.

Where MongoDB Excels:

• Rapid prototyping with evolving schemas
• Content management systems
• Catalog systems with varied product attributes
• Applications where document structure matches business logic

Important Consideration: Always design your indexes first. MongoDB without proper indexes is a Ferrari without wheels: impressive specs, unusable performance.

// MongoDB indexing strategy for e-commerce
db.products.createIndex({
  "category": 1,
  "price": 1,
  "createdAt": -1
});

// Compound index for faceted search
db.products.createIndex({
  "category": 1,
  "attributes.brand": 1,
  "attributes.color": 1,
  "price": 1
});

Production Gotchas:

• Memory usage grows with working set size
• Aggregation pipelines can be memory-intensive
• Sharding requires careful planning of shard keys

Redis: The Speed Demon

Redis is a data structure server that happens to be very good at caching. The other structures solve coordination problems that SQL handles awkwardly.

Redis Use Cases Beyond Caching:

• Session storage with automatic expiration
• Rate limiting with sliding windows
• Real-time leaderboards and counters
• Pub/sub for real-time features
• Distributed locks for coordination

Common Pattern: distributed rate limiting shared across services:

// Sliding window rate limiter in Redis
async function checkRateLimit(userId: string, limit: number, windowMs: number) {
  const key = `rate_limit:${userId}`;
  const now = Date.now();
  const windowStart = now - windowMs;
  
  const pipeline = redis.pipeline();
  pipeline.zremrangebyscore(key, 0, windowStart);
  pipeline.zadd(key, now, now);
  pipeline.zcard(key);
  pipeline.expire(key, Math.ceil(windowMs / 1000));
  
  const results = await pipeline.exec();
  const currentCount = results[2][1] as number;
  
  return currentCount <= limit;
}

DynamoDB: The Serverless Powerhouse

DynamoDB is either amazing or terrible depending on how well you understand its data model. There’s no middle ground.

DynamoDB Strengths:

• True serverless with pay-per-use pricing
• Predictable single-digit millisecond latency
• Automatic scaling and backup
• Global tables for multi-region applications

The DynamoDB Mental Model: the table is shaped by the queries it has to answer. List every access pattern before you create the table, because adding one afterwards usually costs a new GSI or a full backfill.

// DynamoDB single-table design pattern
interface GameRecord {
  PK: string;  // USER#123 or GAME#456
  SK: string;  // PROFILE or SCORE#2024-01-15
  Type: string;  // USER or GAME or SCORE
  GSI1PK?: string; // For secondary access patterns
  GSI1SK?: string;
  // ... other attributes
}

// Query user's recent scores (AWS SDK v3)
import { DynamoDBClient } from '@aws-sdk/client-dynamodb';
import { DynamoDBDocumentClient, QueryCommand } from '@aws-sdk/lib-dynamodb';

const docClient = DynamoDBDocumentClient.from(new DynamoDBClient({}));

const { Items } = await docClient.send(new QueryCommand({
  TableName: 'GameData',
  KeyConditionExpression: 'PK = :pk AND begins_with(SK, :sk)',
  ExpressionAttributeValues: {
    ':pk': 'USER#123',
    ':sk': 'SCORE#'
  },
  ScanIndexForward: false, // Latest first
  Limit: 10
}));

DynamoDB Gotchas:

• Hot partitions can throttle your entire application
• Query patterns must be known upfront
• Complex relationships require careful GSI design
• FilterExpressions still consume read capacity

NewSQL: Best of Both Worlds

CockroachDB: Distributed SQL Done Right

CockroachDB promises PostgreSQL compatibility with global distribution. In practice it delivers on most of that, with some important caveats: the wire protocol is PostgreSQL’s, but the execution model underneath is a distributed consensus layer, and that shows up in latency and in the feature gaps.

When CockroachDB Makes Sense:

• Global applications requiring strong consistency
• Financial systems needing ACID across regions
• Applications outgrowing single-node PostgreSQL
• Teams wanting SQL with automatic sharding

Implementation Example: CockroachDB works well for fintech applications spanning multiple regions. The automatic geo-partitioning keeps user data in the right regions for compliance, while maintaining strong consistency for financial transactions.

-- CockroachDB geo-partitioning.
-- The partition column must be a prefix of the primary index,
-- so region leads the primary key.
CREATE TABLE users (
  id UUID NOT NULL DEFAULT gen_random_uuid(),
  email STRING UNIQUE,
  region STRING NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  PRIMARY KEY (region, id)
) PARTITION BY LIST (region) (
  PARTITION us_users VALUES IN ('us-east', 'us-west'),
  PARTITION eu_users VALUES IN ('eu-west', 'eu-central')
);

Trade-offs:

• Higher latency than single-node databases due to consensus
• More expensive than traditional PostgreSQL
• Some PostgreSQL features still missing or different

Edge Database Solutions

PouchDB/CouchDB: Offline-First Architecture

For applications that need to work offline, CouchDB’s replication model is still the reference design. PouchDB brings the same protocol to the browser.

Perfect For:

• Field service applications
• Mobile apps in areas with poor connectivity
• Collaborative applications with eventual consistency needs

Implementation Pattern:

// PouchDB offline-first pattern
const localDB = new PouchDB('local-data');
const remoteDB = new PouchDB('https://server.com/data');

// Two-way sync with conflict resolution
const sync = localDB.sync(remoteDB, {
  live: true,
  retry: true
}).on('change', (info) => {
  console.log('Sync change:', info);
}).on('error', (err) => {
  console.log('Sync error:', err);
});

// App works offline, syncs when online
await localDB.put({
  _id: 'user-123',
  name: 'John Doe',
  lastModified: new Date().toISOString()
});

InfluxDB: Time-Series Specialist

When you’re dealing with metrics, logs, or IoT data, specialized time-series databases like InfluxDB outperform general-purpose engines on ingest rate, storage footprint, and range queries.

InfluxDB Advantages:

• Automatic downsampling and retention policies
• Built-in time-based functions and aggregations
• Efficient storage for time-series data
• Native integration with monitoring tools

-- InfluxQL query for system metrics
SELECT mean("cpu_usage") 
FROM "system_metrics" 
WHERE time >= now() - 24h 
GROUP BY time(1h), "host"

Database Selection Matrix

By Use Case

E-commerce Platform:

• Catalog: PostgreSQL (structured product data + JSONB for attributes)
• Sessions: Redis (fast access + automatic expiration)
• Orders: PostgreSQL (ACID compliance for financial data)
• Analytics: ClickHouse or BigQuery (analytical workloads)

IoT Application:

• Device State: Redis (real-time updates)
• Time Series: InfluxDB (sensor data)
• Configuration: PostgreSQL (device management)
• Edge Cache: SQLite (local device storage)

Social Media App:

• User Profiles: PostgreSQL (relational data)
• Posts/Timeline: DynamoDB (high scale, simple queries)
• Real-time: Redis Streams (notifications, chat)
• Search: Elasticsearch (content discovery)

By Scale Requirements

Small Scale (1K-100K users): PostgreSQL + Redis covers 90% of use cases. Simple, well-understood, cost-effective.

Medium Scale (100K-10M users):

• Read replicas for PostgreSQL
• DynamoDB for high-traffic features
• Elasticsearch for search
• Redis cluster for caching

Large Scale (10M+ users):

• Sharded PostgreSQL or CockroachDB
• DynamoDB with careful partition design
• Redis Cluster with consistent hashing
• Specialized databases for specific workloads

Selection Criteria Deep Dive

Consistency Requirements

Strong Consistency (ACID): PostgreSQL, CockroachDB, SQL Server

• Financial transactions
• Inventory management
• User authentication

Eventual Consistency (BASE): DynamoDB, MongoDB, Cassandra

• Social media feeds
• Content catalogs
• Analytics data

Choose Strong When: Data integrity is more important than availability Choose Eventual When: Availability and partition tolerance are priority

Performance Patterns

Read-Heavy Workloads: MySQL with read replicas, Redis caching layer Write-Heavy Workloads: DynamoDB, Cassandra, or sharded PostgreSQL Mixed Workloads: PostgreSQL with proper indexing and connection pooling

Latency Requirements:

• <1ms: Redis (in-memory)
• <10ms: DynamoDB, well-tuned PostgreSQL
• <100ms: Most SQL databases with proper indexing

• 100ms: Acceptable for analytical workloads

Migration Scenarios

MongoDB to PostgreSQL

The Problem: Content management systems using MongoDB can struggle with complex queries. Aggregation pipelines become unmaintainable, and the lack of schema validation causes data quality issues.

The Solution: move to PostgreSQL with JSONB columns for the flexible parts of the content, keeping the benefits of document storage while gaining SQL’s query power.

The Cutover: a dual-write pattern keeps the old store readable while traffic moves, so a rollback stays possible until the last reader is migrated:

// Dual-write migration pattern
class ContentService {
  async createPost(post: Post) {
    // Write to new PostgreSQL database
    const pgResult = await this.postgresDB.insert(post);
    
    try {
      // Write to legacy MongoDB (for rollback safety)
      await this.mongoDB.insertOne(post);
    } catch (error) {
      // MongoDB failure shouldn't break the flow
      console.error('MongoDB write failed:', error);
    }
    
    return pgResult;
  }
}

Outcomes:

• Schema validation and constraints move data-quality checks out of application code
• Multi-entity reads become joins instead of hand-written aggregation stages
• One engine to back up, monitor, and tune instead of two

Single-Region to Multi-Region

The Challenge: Growing SaaS applications need to expand from single-region to global, requiring data residency compliance and low latency worldwide.

The Solution: Migrating from single PostgreSQL to CockroachDB with geo-partitioning allows user data to stay in their regions while maintaining global consistency for billing and analytics.

Implementation:

-- Geo-partitioned user data
ALTER TABLE users CONFIGURE ZONE USING constraints = '[+region=us-east1]';
ALTER TABLE user_profiles CONFIGURE ZONE USING constraints = '[+region=us-east1]';

-- Global data (billing, analytics)
ALTER TABLE subscriptions CONFIGURE ZONE USING constraints = '[]';

Trade-offs:

• Reads are served from a nearby replica instead of crossing an ocean, which is where the latency win comes from
• Data residency requirements are satisfied by pinning rows to a region
• Every cross-region write now pays a consensus round trip, and the cluster costs more to run

Performance Characteristics

Read Performance

Point-lookup throughput tracks the storage medium more than the engine name. In-memory Redis sits at the top, managed key-value stores such as DynamoDB come next, and indexed relational or document engines follow. The gaps narrow sharply once the working set fits in page cache, which is why published numbers rarely transfer between deployments. Measure with your own record size, index shape, and concurrency before treating any ordering as fixed.

Complex Queries (analytical workloads):

• PostgreSQL: Excellent (sophisticated query planner)
• CockroachDB: Good (distributed but still SQL)
• MongoDB: Poor (aggregation pipelines)
• DynamoDB: Not applicable (limited query capabilities)

Write Performance Under Load

Concurrent Writes:

• DynamoDB: Scales automatically, consistent performance until a partition goes hot
• Redis: Excellent until memory limit
• PostgreSQL: Good with proper connection pooling
• MongoDB: Degrades with document size growth

Implementation Patterns

Database Sharding Strategies

Horizontal Sharding (dividing data across servers):

// User-based sharding
function getShardForUser(userId: string): string {
  const hash = createHash('md5').update(userId).digest('hex');
  const shardIndex = parseInt(hash.substring(0, 8), 16) % NUM_SHARDS;
  return `shard_${shardIndex}`;
}

// Route queries to appropriate shard
class ShardedUserService {
  async getUser(userId: string) {
    const shard = getShardForUser(userId);
    return this.databases[shard].query('SELECT * FROM users WHERE id = ?', [userId]);
  }
}

Vertical Sharding (separating by feature):

// Separate databases by domain
class UserService {
  profiles = new DatabaseConnection('user_profiles_db');
  preferences = new DatabaseConnection('user_preferences_db');
  analytics = new DatabaseConnection('user_analytics_db');
  
  async getFullUser(userId: string) {
    const [profile, preferences, analytics] = await Promise.all([
      this.profiles.getUser(userId),
      this.preferences.getUser(userId),
      this.analytics.getUser(userId)
    ]);
    
    return { ...profile, preferences, analytics };
  }
}

Connection Management

PostgreSQL Connection Pooling:

// Production PostgreSQL setup
import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  // Critical production settings
  max: 20,  // Maximum connections
  idleTimeoutMillis: 30000,  // Close idle connections
  connectionTimeoutMillis: 2000, // Fail fast on connection issues
  maxUses: 7500,  // Rotate connections to prevent memory leaks
});

// Always use transactions for data consistency
async function transferMoney(fromUserId: string, toUserId: string, amount: number) {
  const client = await pool.connect();
  
  try {
    await client.query('BEGIN');
    
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE user_id = $2',
      [amount, fromUserId]
    );
    
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE user_id = $2',
      [amount, toUserId]
    );
    
    await client.query('COMMIT');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

Monitoring and Troubleshooting

Key Metrics to Track

PostgreSQL Essential Metrics:

• Connection usage (pg_stat_activity)
• Query performance (pg_stat_statements)
• Index usage (pg_stat_user_indexes)
• Replication lag (pg_stat_replication)

-- PostgreSQL health check queries
-- Long-running queries
SELECT pid, now() - query_start as duration, query 
FROM pg_stat_activity 
WHERE now() - query_start > interval '5 minutes';

-- Index usage statistics
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes 
ORDER BY idx_scan DESC;

-- Connection count by state
SELECT state, count(*) 
FROM pg_stat_activity 
GROUP BY state;

DynamoDB CloudWatch Metrics:

• ConsumedReadCapacityUnits / ConsumedWriteCapacityUnits
• ThrottledRequests (critical!)
• SuccessfulRequestLatency
• SystemErrors

Note: DynamoDB pricing was reduced by ~50% in November 2024, making on-demand pricing more cost-effective for variable workloads.

MongoDB Key Metrics:

• Operations per second (opcounters)
• Working set size vs available memory
• Lock percentage
• Replication lag

Common Performance Issues

The N+1 Query Problem:

// BAD: N+1 queries
async function getUsersWithPosts() {
  const users = await db.query('SELECT * FROM users');
  
  for (const user of users) {
    user.posts = await db.query('SELECT * FROM posts WHERE user_id = ?', [user.id]);
  }
  
  return users;
}

// GOOD: Single query with JOIN
async function getUsersWithPosts() {
  return db.query(`
    SELECT u.*, p.id as post_id, p.title, p.content
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    ORDER BY u.id, p.created_at DESC
  `);
}

Connection Pool Exhaustion:

// Monitoring connection pool health
setInterval(() => {
  console.log({
    totalConnections: pool.totalCount,
    idleConnections: pool.idleCount,
    waitingClients: pool.waitingCount
  });
  
  if (pool.waitingCount > 5) {
    console.warn('Connection pool under pressure!');
  }
}, 30000);

Future-Proofing Your Database Choice

Technology Trends to Watch

Vector Databases for AI/ML: pgvector for PostgreSQL, Pinecone, Weaviate

• Embedding storage for semantic search
• RAG (Retrieval-Augmented Generation) applications
• Image and document similarity search

Multi-Model Databases: FaunaDB, Azure Cosmos DB

• Single database supporting multiple data models
• Reduced operational complexity
• Unified query interfaces

Serverless-First Architectures:

• PlanetScale (serverless MySQL)
• Neon (serverless PostgreSQL)
• FaunaDB (serverless transactional)

Planning for Growth

Capacity Planning Framework:

// Database growth projection model
interface GrowthProjection {
  currentUsers: number;
  userGrowthRate: number; // monthly rate as a fraction, e.g. 0.05 for 5%
  avgDataPerUser: number; // in KB
  queryGrowthMultiplier: number; // queries per user, grows faster than users
}

function projectDatabaseNeeds(projection: GrowthProjection, months: number) {
  const futureUsers = projection.currentUsers * Math.pow(1 + projection.userGrowthRate, months);
  const futureDataSize = futureUsers * projection.avgDataPerUser;
  const futureQPS = futureUsers * projection.queryGrowthMultiplier;
  
  return {
    estimatedUsers: Math.round(futureUsers),
    estimatedDataSizeGB: Math.round(futureDataSize / 1024 / 1024),
    estimatedQPS: Math.round(futureQPS),
    recommendedShards: Math.ceil(futureQPS / 10000) // Assuming 10K QPS per shard
  };
}

Team Development Strategy

Skill Building Path:

1. Foundation: Master one SQL database deeply (PostgreSQL recommended)
2. NoSQL Understanding: Learn one document store (MongoDB) and one key-value (Redis)
3. Cloud Native: Understand one cloud database (DynamoDB or Cosmos DB)
4. Specialization: Deep dive into domain-specific databases (time-series, graph, etc.)

Knowledge Sharing Practices:

• Database design reviews for all new features
• Regular performance analysis sessions
• Post-mortem analysis of database-related incidents
• Cross-training on different database technologies

Decision Framework

When choosing a database for a new project, ask these questions in order:

1. Consistency Requirements

• Do you need ACID transactions? → SQL databases
• Can you work with eventual consistency? → NoSQL options open up

2. Query Complexity

• Complex analytical queries? → PostgreSQL, CockroachDB
• Simple key-value lookups? → Redis, DynamoDB
• Full-text search required? → Elasticsearch + primary database

3. Scale and Performance

• Current scale: <100K users → PostgreSQL + Redis
• Growth trajectory: >1M users → Consider sharding or cloud-native options
• Latency requirements: <10ms → In-memory (Redis) or optimized NoSQL

4. Team and Operational Constraints

• Team expertise: Stick close to existing skills initially
• Operational budget: Managed services vs. self-hosted
• Compliance requirements: Data residency, encryption, audit trails

5. Future Flexibility

• How likely is the data model to change? → Document stores for high change rate
• Multi-region expansion planned? → Consider distributed databases early
• Integration requirements: What other systems need to connect?

PostgreSQL with Redis in front of it holds until one of those answers forces a change: a write volume that no single primary absorbs, a residency rule that pins rows to a region, a time-series ingest rate that fills the disk, or a client that has to work offline. Each of those has a specialized answer above, and each one adds an engine to operate. Add the second engine once a requirement actually demands it.

References

• PostgreSQL Documentation - Official reference for PostgreSQL, covering data types, indexing, transactions, and advanced features like JSONB and full-text search
• What is Amazon DynamoDB? - AWS guide to DynamoDB’s key-value and document model, capacity modes, and consistency options
• MongoDB Manual - Official MongoDB documentation covering the document data model, aggregation pipeline, and schema design patterns
• Redis Documentation - Official Redis reference for in-memory data structures, persistence options, pub/sub, and cluster configuration
• Choosing an AWS Database Service - AWS decision guide comparing relational, key-value, document, in-memory, and graph databases for different workload types
• MySQL 8.4 Reference Manual - Official MySQL documentation covering the storage engine model, replication, and query optimization
• Amazon DynamoDB Pricing - Current on-demand and provisioned throughput rates, including the reduced on-demand pricing referenced above
• better-sqlite3 API Documentation - Reference for the synchronous SQLite driver used in the examples, including the pragma helper and WAL configuration