SQL vs NoSQL for .NET Apps: How to Choose

The database choice is the most expensive design decision in any .NET service - not because the wrong database is slow, but because moving off it later means rewriting the data access layer, the schema migration tooling, the test setup, and often the consistency model. This chapter gives you a decision tree that bottoms out in "PostgreSQL plus EF Core" for 80% of services and explains exactly when to deviate.

When does the default - PostgreSQL + EF Core - run out?

Three concrete signals.

Single-node write QPS pegs at the box ceiling. A modern Postgres instance handles 5K-20K write/s comfortably; with synchronous_commit = off and SSD it hits 50K. When peak write QPS from chapter 2 approaches that, the database is the bottleneck.

Storage exceeds ~1 TB of hot data per node. Postgres handles much more, but query plans on huge tables become harder, vacuums slow down, and backups balloon. Sharding or columnar storage starts to matter.

Working set exceeds RAM. The "boring" Postgres scaling story relies on the OS page cache holding the hot pages. Once your working set is 10x your RAM, every query hits disk and latency collapses.

If none of these are true, you are not running out of Postgres - you are running out of patience or marketing.

What numbers should I budget for the database tier?

Operation                            Latency       Cost/QPS
Postgres SELECT by PK                ~1-3 ms       cheap
Postgres SELECT with 1 join          ~5-10 ms      medium
Postgres SELECT with aggregate       ~20-200 ms    expensive
Postgres INSERT (single row)         ~1-3 ms       cheap
Postgres INSERT (batched 100)        ~5-10 ms      cheaper per row
EF Core SaveChanges (small batch)    +~1-2 ms overhead
DynamoDB GetItem                     ~5-10 ms      flat
MongoDB findOne by index             ~2-5 ms       cheap

The key intuition: the engine is cheap, the .NET overhead is small, the cost is in the query shape. A poorly-indexed join on a million rows costs more than a well-indexed cross-collection lookup in MongoDB. Schema design matters more than database choice for most .NET services.

What does the minimal architecture look like?

flowchart LR
    App1[ASP.NET Core 1] --> PG[(Postgres primary<br/>writes)]
    App2[ASP.NET Core 2] --> PG
    App1 -.read-only.-> RR[(Read replica)]
    App2 -.read-only.-> RR
    PG -. async replication .-> RR

Two replicas of the web tier behind a load balancer; one Postgres primary handles writes; one read replica serves dashboards and analytical reads. The architecture covers ~80% of services through the first three years of growth. Sharding, document stores, and multi-region come after you have outgrown this.

How does EF Core wire up to a primary + read replica?

Two DbContext instances pointed at different connection strings:

// Primary - writes and read-after-write reads
builder.Services.AddDbContextPool<AppDbContext>(opt =>
    opt.UseNpgsql(builder.Configuration.GetConnectionString("Primary")));

// Read-only replica context
builder.Services.AddDbContextPool<AppReadDbContext>(opt =>
    opt.UseNpgsql(builder.Configuration.GetConnectionString("Replica"))
       .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

// Usage:
public class ProductController(AppDbContext write, AppReadDbContext read) : Controller
{
    [HttpGet]
    public Task<List<Product>> ListAsync()
        => read.Products.AsNoTracking().ToListAsync();   // hits replica

    [HttpPost]
    public async Task CreateAsync(Product p)
    {
        write.Products.Add(p);
        await write.SaveChangesAsync();                  // hits primary
    }
}

Two design rules. First, never write through the read context - it will silently work in dev and explode in prod when the replica is read-only. Second, immediately after a write, read from the primary for that user (the stickiness cookie from chapter 3) - replication lag is real and visible.

When should I shard, and what does it cost?

Sharding splits one logical table across multiple physical databases by some key. The trade-offs are sharp.

Pros: write throughput scales linearly with the number of shards; storage scales linearly; failures are isolated to one shard.

Cons: every query must include the shard key; cross-shard joins become application-layer code; transactions are local-only; schema migrations multiply.

The .NET ecosystem reaches sharding through Citus (a Postgres extension that turns it into a distributed system) or through manual sharding with one DbContext per shard plus a routing layer. Both are heavy. Default to delaying sharding until the metrics from chapter 13 demand it.

What failure modes does the database tier introduce?

The four that show up first:

• Connection-pool exhaustion - more concurrent requests than the pool size, all waiting on the DB. Fix: raise Maximum Pool Size in the connection string and watch Npgsql counters; better, fix the slow query.
• Replication lag spikes - read replica falls behind primary during a write burst. Fix: route reads-after-writes to primary (the cookie trick); alert when lag > 5 s.
• Long transactions blocking vacuum - a transaction left open for hours stops Postgres from reclaiming dead tuples; the table bloats, queries slow. Fix: kill long transactions, never use IsolationLevel.Serializable for read-only.
• Migration locks - ALTER TABLE on a hot table can hold an exclusive lock long enough to cause an outage. Fix: zero-downtime migration patterns (add column nullable, backfill async, then enforce NOT NULL).

When is NoSQL actually the right answer?

Two cases that are clear-cut.

Case 1: hot key write throughput beyond a single Postgres node. A globally distributed key-value workload (session cache, real-time counters, IoT telemetry) where every write is independent fits Cassandra or DynamoDB perfectly. Postgres requires sharding to keep up; NoSQL was designed for it from day one.

Case 2: schema variability across tenants. A SaaS that lets each customer add custom fields ends up with JSONB columns in Postgres or - more honestly - documents in MongoDB. The schema is the data; forcing it into rows is rowing upstream. The caching layer covers small KV needs even before this point.

Where should you go from here?

Next chapter: message queues for .NET - when synchronous database writes are the wrong shape and you need to buffer through a queue. After that, the rest of the building blocks compose with this database tier as the durable home for state.