DEV Community: Rajat Thakur

How to Choose the Right SQL Database for Your Project

Rajat Thakur — Mon, 15 Jun 2026 18:34:30 +0000

Most developers learn one database and use it for everything. Then traffic grows, queries slow down, costs spike — and they wonder why.

The answer is usually simple: wrong database for the workload.

This guide breaks down the 5 types of SQL databases, how each one scales, and exactly when to use which.

🧱 SQL Basics

Skip this if you already know SQL well.

Tables store data in rows and columns. Types are strict — define them upfront, the database enforces them.

Foreign keys link tables instead of duplicating data. An orders table holds a user_id pointing at users. The database prevents orphaned records.

ACID is what makes SQL trustworthy:

Letter	Meaning
Atomic	A transaction fully completes or fully rolls back — no partial writes
Consistent	Data is always in a valid state
Isolated	Concurrent transactions don't interfere with each other
Durable	Committed data survives crashes and restarts

Indexes are the single biggest performance lever. Without one, a query on a million-row table scans every row. With one on the right column, it reads tens.

💡 Always index foreign keys and any column you filter or sort by.

🗂️ The 5 Types of SQL Databases

⚡ 1. OLTP — For Your App Backend

The everyday workhorse. Built for lots of small, fast reads and writes — logins, cart updates, payment records. Strong on concurrency and data consistency.

📈 Scales:  Vertically first → read replicas → sharding
🎯 Use for: Web apps · SaaS · e-commerce · fintech
🛢️ DBs:     PostgreSQL · MySQL · MariaDB · SQL Server · Oracle

📊 2. OLAP — For Analytics

Stores data by column instead of by row. A query summing revenue across 10 billion orders only reads the revenue column — not the whole row. Queries that take 20 minutes in PostgreSQL run in 3 seconds here.

📈 Scales:  Massively parallel clusters · separate storage & compute
🎯 Use for: BI dashboards · financial reporting · data warehouses
🛢️ DBs:     Snowflake · BigQuery · Redshift · ClickHouse · Databricks

📦 3. Embedded — No Server Required

Runs as a library inside your app. The database is just a file on disk. No server, no config, no network.

📈 Scales:  Single device only (~1TB)
🎯 Use for: Mobile apps · desktop tools · local-first software
🛢️ DBs:     SQLite · DuckDB

🌍 4. Distributed SQL — Global Scale, Still SQL

Full ACID transactions and SQL — but data is spread across multiple servers or regions. Any region can accept writes. Users in Tokyo don't round-trip to Virginia.

📈 Scales:  Add nodes → auto-rebalances · multi-region active-active
🎯 Use for: Global apps · multi-region SaaS · geo-redundant finance
🛢️ DBs:     CockroachDB · YugabyteDB · Cloud Spanner · TiDB · PlanetScale

☁️ 5. Cloud-Managed — No DBA Needed

Your cloud provider handles backups, patches, failover, and replication. You just connect and query.

📈 Scales:  One-click vertical · read replicas in minutes · Aurora → 128TB
🎯 Use for: Startups · teams on AWS/GCP/Azure · apps needing managed HA
🛢️ DBs:     Amazon RDS · Aurora · Azure SQL · Google Cloud SQL · Neon

📋 Scaling at a Glance

Type	How it scales	Max scale	Complexity
OLTP	Vertical + read replicas	~10TB	Medium
OLAP	Parallel compute clusters	Petabytes	Low
Embedded	Single device	~1TB	Very low
Distributed SQL	Horizontal, multi-region	Petabytes	High
Cloud-managed	Managed vertical/HA	~128TB	Very low

🎯 How to Pick the Right One

📱 Building a mobile or desktop app → SQLite

No server, no config. Ships as a single file with your app. Used by iOS, Android, Chrome, WhatsApp, and Airbus aircraft.

Scaling path: Up to ~1TB on device → migrate to PostgreSQL + sync over API → Turso / Cloudflare D1 for edge.

⚠️ Not for multi-user concurrent writes — SQLite uses file-level locking.

🔍 Analysing local files (CSV, Parquet, JSON) → DuckDB

Query a 10GB CSV directly without importing it. Vectorised columnar execution — often 100× faster than SQLite for aggregations.

Scaling path: Single machine → MotherDuck for teams → push results to BigQuery.

⚠️ Not for transactional workloads — not built for frequent small writes.

🌐 Building a web or SaaS app → PostgreSQL or MySQL

Click to compare them

Pick PostgreSQL when:

Schema is complex or frequently evolving
You need JSONB, geospatial (PostGIS), or vector search (pgvector)
You run complex joins, CTEs, or window functions
Data integrity is non-negotiable

Pick MySQL when:

Queries are simple CRUD
Your team already knows MySQL
Building on LAMP or WordPress
You want maximum hosting provider support

Scaling path (both):
Single instance → Read replicas → Connection pooling → Table partitioning → Sharding

⚠️ Don't start with distributed SQL. The complexity isn't worth it until you have millions of active users.

📈 Dashboards over large datasets → Snowflake, BigQuery, ClickHouse, or Redshift

Database	Sweet spot
Snowflake	Enterprise BI, SQL-familiar teams
BigQuery	GCP-native, serverless pay-per-query
Redshift	AWS-native, existing Postgres workloads
ClickHouse	Real-time analytics, highest ingestion speed

Scaling path: Virtually unlimited — auto-scale per query (Snowflake/BigQuery) or dozens of self-hosted nodes (ClickHouse).

⚠️ Don't use these as your app's main database — slow for small individual writes.

🗺️ Low latency across multiple regions → CockroachDB, YugabyteDB, or Cloud Spanner

Full PostgreSQL/MySQL-compatible SQL, strongly consistent across regions. Users read from a local replica.

Scaling path: Add regions → nodes rebalance automatically → active-active across all regions.

⚠️ Expensive and operationally complex. Only reach for this when you genuinely have multi-region traffic.

🤖 No DBA on the team → RDS, Aurora, Azure SQL, or Cloud SQL

Fully managed — backups, failover, patching, all automatic. Neon adds serverless Postgres that scales to zero when idle.

Scaling path: One-click vertical → Multi-AZ HA → Aurora Serverless v2 for automatic compute scaling.

⚠️ Costs more than self-hosted at very high throughput. At scale, running your own cluster on EC2 is significantly cheaper.

🗺️ Quick Reference

Scenario	Choose	Key strength	Managed option
Mobile / desktop	SQLite	Zero setup	Turso / D1
Local analytics	DuckDB	Columnar speed	MotherDuck
Web app (complex)	PostgreSQL	Rich features	Supabase / Neon
Web app (simple)	MySQL	Easy to learn	PlanetScale
BI dashboards	Snowflake / BigQuery	Parallel compute	Native cloud
Real-time analytics	ClickHouse	Fastest ingestion	ClickHouse Cloud
Global multi-region	CockroachDB	Multi-region ACID	CockroachDB Dedicated
PG-compatible dist.	YugabyteDB	PostgreSQL API	YugabyteDB Managed
Google stack	Cloud Spanner	External consistency	GCP managed
MySQL distributed	TiDB	HTAP workloads	TiDB Cloud
AWS managed	Amazon Aurora	128TB, 15 replicas	AWS managed
Azure / .NET	Azure SQL	Microsoft ecosystem	Azure managed
GCP stack	Google Cloud SQL	GCP integration	GCP managed

🧪 Seed any database with realistic fake data

Once you've picked your database, try sql-faker — it generates dialect-correct INSERT statements for all 15 databases in this guide, with the right syntax per database (SERIAL, AUTO_INCREMENT, IDENTITY(1,1), TIMESTAMP_NTZ, MergeTree, and more).

# PostgreSQL
npx sql-faker --db postgresql --template users --rows 1000 --output seed.sql

# Push directly into a live database
npx sql-faker --db mysql --template orders --rows 500 \
  --push "mysql://user:pass@localhost:3306/mydb"

# Your own schema
npx sql-faker --schema-example > schema.json
npx sql-faker --template custom --schema schema.json --db snowflake --rows 100

💡 The golden rule

Start simple. A well-indexed PostgreSQL or MySQL instance handles more traffic than most apps will ever need. Add complexity only when you have a proven need — the upgrade path is clear once you actually hit the wall.

Why p-retry isn't enough for production and what to do instead

Rajat Thakur — Sun, 14 Jun 2026 17:12:13 +0000

You're sending an email. The SMTP server hiccups. Your code throws. The email never sends. Nobody knows.

That's the silent failure problem. Most retry libraries solve half of it. They retry. But they don't answer the harder question:

What happens to the jobs that never succeed?

This article walks through three production failure patterns and how to fix all of them with job-retry, a Node.js package I built that handles backoff, timeouts, and dead letter queues in one place.

The three ways basic retry fails you

Problem 1: The Thundering Herd

Without jitter:

t = 0s  ┌─────────────────────────────────┐
        │  500 jobs all fail at once       │
        └─────────────────────────────────┘
                          │
t = 1s  ┌─────────────────────────────────┐
        │  500 jobs all retry at once  💥  │  <- server dies again
        └─────────────────────────────────┘
                          │
t = 2s  ┌─────────────────────────────────┐
        │  500 jobs all retry at once  💥  │  <- and again
        └─────────────────────────────────┘

Your service comes back up and immediately gets hammered by every single job retrying at the exact same millisecond. It goes down again.

Problem 2: The Hanging Promise

await fetch('https://slow-api.com/data')
        │
        │  ... 10 seconds pass
        │  ... 30 seconds pass
        │  ... 2 minutes pass
        │
        ▼
      nothing.   <- no error, no timeout, just hangs forever

Your API call never resolves. The retry loop waits forever. Memory leaks. Server crashes.

Problem 3: Silent Loss

p-retry:

  attempt 1 -> fail
  attempt 2 -> fail
  attempt 3 -> fail
  attempt 4 -> fail
  attempt 5 -> fail
       │
       ▼
    throws <- you catch it, log it, move on
       │
       ▼
    job is gone forever 🗑️
    no record of what failed
    no way to replay it
    customer never got their email

After p-retry gives up, the job disappears. No record of what failed, when, or why. No way to replay it after the issue is fixed.

The solution: job-retry

job-retry:

  attempt 1 -> fail -> wait (backoff + jitter)
  attempt 2 -> fail -> wait (backoff + jitter)
  attempt 3 -> fail -> wait (backoff + jitter)
  attempt 4 -> fail -> wait (backoff + jitter)
  attempt 5 -> fail
       │
       ▼
  ┌─────────────────────────────┐
  │       Dead Letter Queue     │  <- job saved with full context
  │  name, error, timestamp,    │
  │  attempts, original payload │
  └─────────────────────────────┘
       │
       ▼  (after you fix the underlying issue)
  dlq.retry(job.id, runner) -> job runs again ✅

Install

npm install job-retry

Zero dependencies for core usage. Only needs ioredis if you use the Redis DLQ backend.

Basic usage

import { JobRetry } from 'job-retry';

const runner = new JobRetry({
  attempts: 5,
  backoff: 'exponential',
  baseDelay: 1000,
  timeout: 5000,   // kill hanging attempts after 5s
  jitter: true,    // spread retries to avoid thundering herd
});

const result = await runner.run('sendEmail', () => sendEmail(user));

That's the whole API. Wrap any async function with runner.run(). Everything else is handled automatically.

How exponential backoff and jitter works

baseDelay: 1000ms

Without jitter:          With jitter:

attempt 1 -> wait 1000ms  attempt 1 -> wait 1200ms  (random between 1000-2000)
attempt 2 -> wait 2000ms  attempt 2 -> wait 2750ms  (random between 2000-4000)
attempt 3 -> wait 4000ms  attempt 3 -> wait 5100ms  (random between 4000-8000)
attempt 4 -> wait 8000ms  attempt 4 -> wait 9800ms  (random between 8000-16000)

All 500 jobs fire         Jobs spread out across a
at exactly the same       time window, service
moment -> 💥              recovers gracefully -> ✅

Strategy	Formula	Example (baseDelay: 1s)
`fixed`	`baseDelay`	1s · 1s · 1s · 1s
`linear`	`baseDelay x attempt`	1s · 2s · 3s · 4s
`exponential`	`baseDelay x 2^(n-1)`	1s · 2s · 4s · 8s

Per-attempt timeout

const runner = new JobRetry({
  attempts: 3,
  timeout: 5000,  // each attempt gets maximum 5 seconds
});

attempt 1:  ──────────────────────── timeout! (5s) -> retry
attempt 2:  ──────────────────────── timeout! (5s) -> retry
attempt 3:  ──────────────────────── timeout! (5s) -> DLQ

No more hanging promises. Every attempt has a hard deadline.

Dead letter queue

When all attempts fail, the job is saved instead of dropped:

const runner = new JobRetry({
  attempts: 3,
  baseDelay: 1000,
  dlq: 'memory',   // swap for 'file' or 'redis' in production
  onFailure: (job) => console.error('Saved to DLQ:', job.name),
});

try {
  await runner.run('processOrder', () => callPaymentService(order));
} catch {
  // all attempts failed, but the job is safe in the DLQ
}

// Inspect what failed
const failed = await runner.dlq.getAll();
// [{
//   id:        'abc-123',
//   name:      'processOrder',
//   error:     'Payment service timeout',
//   timestamp: 1718380800000,
//   attempts:  3
// }]

// Once the payment service is fixed, replay it
await runner.dlq.retry(failed[0].id, runner);

Three DLQ backends

┌─────────────────────────────────────────────────────────┐
│                   DLQBackend interface                   │
│  push · getAll · get · retry · remove · clear · size    │
└───────────────────────┬─────────────────────────────────┘
                        │  implemented by
          ┌─────────────┼──────────────┐
          ▼             ▼              ▼
    MemoryDLQ       FileDLQ       RedisDLQ
    (dev/test)   (single server) (production)

Memory: for development

new JobRetry({ dlq: 'memory' })
// Stored in-process. Lost on restart. Perfect for dev and tests.

File: for single-server apps

new JobRetry({
  dlq: 'file',
  dlqFilePath: './failed-jobs.json',
})
// Persisted to disk as JSON. Survives restarts.

Redis: for production

import Redis from 'ioredis';

new JobRetry({
  dlq: 'redis',
  dlqRedisClient: new Redis(),
})
// Shared across all servers. Survives restarts. Production-ready.

Hooks for full observability

const runner = new JobRetry({
  attempts: 5,
  baseDelay: 1000,

  onRetry: (error, attempt) => {
    // fired after each failed attempt except the last
    logger.warn('Job retry', { attempt, error: error.message });
    metrics.increment('jobs.retry');
  },

  onFailure: (job) => {
    // fired when job is moved to the DLQ
    logger.error('Job permanently failed', { name: job.name, id: job.id });
    metrics.increment('jobs.failed');
  },

  onSuccess: (result, attempts) => {
    // fired on success when at least one retry was needed
    logger.info('Job recovered', { attempts });
    metrics.increment('jobs.recovered');
  },
});

Real-world example: email queue with file DLQ

import { JobRetry } from 'job-retry';

const mailer = new JobRetry({
  attempts: 5,
  backoff: 'exponential',
  baseDelay: 2000,
  timeout: 10_000,
  jitter: true,
  dlq: 'file',
  dlqFilePath: './failed-emails.json',
  onRetry:   (err, n) => console.warn(`Email retry ${n}: ${err.message}`),
  onFailure: (job)    => console.error(`Saved to DLQ: ${job.name}`),
});

export async function sendWelcomeEmail(user: User) {
  return mailer.run(`welcome:${user.id}`, () =>
    smtp.send({ to: user.email, subject: 'Welcome!', body: `Hi ${user.name}` })
  );
}

// Run this script after fixing your SMTP server
async function replayFailedEmails() {
  const failed = await mailer.dlq.getAll();
  console.log(`Replaying ${failed.length} failed email(s)...`);

  for (const job of failed) {
    try {
      await mailer.dlq.retry(job.id, mailer);
      console.log(`✅ Replayed: ${job.name}`);
    } catch {
      console.error(`❌ Still failing: ${job.name}`);
    }
  }
}

Error handling

import { MaxAttemptsExceededError } from 'job-retry';

try {
  await runner.run('myJob', fn);
} catch (err) {
  if (err instanceof MaxAttemptsExceededError) {
    console.log(`Failed after ${err.attempts} attempts`);
    console.log('Root cause:', err.cause);
  }
}

All options

Option	Type	Default	Description
`attempts`	`number`	`3`	Max attempts before DLQ
`backoff`	`'fixed' \	'linear' \	'exponential'`
`baseDelay`	`number`	`1000`	Base delay in ms
`timeout`	`number`	none	Per-attempt timeout in ms
`jitter`	`boolean`	`false`	Randomise delay
`dlq`	`'memory' \	'file' \	'redis' \
{% raw %}`dlqFilePath`	`string`	`'./job-retry-dlq.json'`	File backend path
`dlqRedisClient`	`Redis`	none	ioredis client
`onRetry`	`(error, attempt) => void`	none	Non-final failure hook
`onFailure`	`(job) => void`	none	DLQ hook
`onSuccess`	`(result, attempts) => void`	none	Recovery hook

TypeScript

Full types ship with the package, no @types/job-retry needed.

import type { RetryOptions, DLQEntry, DLQBackend, BackoffStrategy } from 'job-retry';