Postgres for .NET devs: the “why” and common pitfalls vs SQL Server

 

Postgres for .NET Developers:

The “Why” and the Common Pitfalls vs SQL Server

If you’re a .NET developer, chances are your database muscle memory was built on SQL Server. It’s been the default pairing for ASP.NET for years: strong tooling, tight integration, familiar types, and predictable behaviour.

So why are more .NET teams choosing PostgreSQL today?

And more importantly:

Why do so many teams trip over the same issues  when moving from SQL Server to Postgres?

This article is the first in a PostgreSQL-focused stream for .NET developers. We’ll cover:

• Why Postgres is attractive for modern .NET systems

• The mental model differences vs SQL Server

• The most common pitfalls: types, casing, schemas, migrations, and connection pooling

• Practical guidance to avoid pain early

This is not a “Postgres basics” article.
This is about unlearning SQL Server assumptions.

---

Why Postgres Has Become Popular with .NET Teams

Postgres is not “SQL Server but open source”. It’s a different system with different strengths.

1. First-class open source (no second-class features)

• One engine

• One feature set

• No “Enterprise-only” surprises

• No licensing gymnastics in production

For SaaS and multi-tenant systems, this matters.

2. Strong data modelling features

Postgres shines when you need:

• jsonb for semi-structured data

• partial indexes

• functional indexes

• rich constraints

• window functions and CTEs that feel designed, not bolted on

3. Cloud-native friendliness

Postgres works well across:

• Azure Database for PostgreSQL

• AWS RDS / Aurora

• self-hosted containers

• managed services without vendor lock-in

4. Excellent .NET support (now)

Historically, Postgres support in .NET was “fine”.
Today, Npgsql + EF Core is genuinely excellent.

But only if you understand the differences.

---

The First Big Mental Shift: Postgres Is Case-Sensitive (Mostly)

SQL Server mental model

In SQL Server:

• Identifiers are case-insensitive

• Users, users, USERS all resolve the same (by default)

Postgres reality

In Postgres:

• Unquoted identifiers are folded to lowercase

• Quoted identifiers are case-sensitive

That means this table:

CREATE TABLE Users (
    Id uuid PRIMARY KEY,
    Name text
);

Actually becomes:

users

Unless you used quotes:

CREATE TABLE "Users" (
    "Id" uuid PRIMARY KEY,
    "Name" text
);

Now you’ve created a future problem.

Why this bites .NET devs

EF Core will happily generate quoted identifiers if you’re not careful.

You’ll end up with:

• "Users" instead of users

• "CreatedAt" instead of created_at

Then:

• raw SQL becomes painful

• migrations become noisy

• cross-tool usage (psql, admin tools) becomes error-prone

Recommendation

Use lowercase, snake_case everywhere.

In EF Core:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.UseSnakeCaseNamingConvention();
}

This single line avoids years of friction.

---

Types: Postgres Is Explicit (and That’s a Good Thing)

GUIDs vs UUIDs

SQL Server:

uniqueidentifier

Postgres:

uuid

In .NET:

Guid

This mapping is excellent and native in Postgres.
No NEWID() nonsense, no conversion hacks.

---

Strings: No NVARCHAR

SQL Server devs often overthink this.

Postgres:

text

• Unicode by default

• No length limits

• No performance penalty vs varchar(n)

Use text unless you actually need a constraint.

---

Dates and times (this one matters)

SQL Server:

• datetime

• datetime2

• timezone handling is mostly “good luck”

Postgres:

• timestamp without time zone

• timestamp with time zone

Important rule:

timestamp with time zone does not store the timezone, it stores UTC and normalises.

Recommended mapping

For .NET:

• Use DateTimeOffset in your domain

• Map to timestamp with time zone

Example:

public DateTimeOffset CreatedAt { get; set; }

Avoid DateTime unless you really know what you’re doing.

---

JSON: jsonb is not NVARCHAR(MAX)

This is where Postgres starts to feel different.

SQL Server:

nvarchar(max) -- with JSON functions layered on

Postgres:jsonb

• is binary

• is indexable

• supports operators

• supports GIN indexes

EF Core mapping:

builder
    .Property(x => x.Metadata)
    .HasColumnType("jsonb");

This enables:

• schema-flexible fields

• versioned payloads

• event-style storage

Without abandoning relational guarantees.

---

Schemas: Not Just “dbo with a Different Name”

SQL Server:

• schemas are often ignored

• everything lives under dbo

Postgres:

• schemas are real namespaces

• search_path determines resolution order

Common pitfall

Your app connects and suddenly can’t find tables.

Why?
Because:

• tables exist in app

• but search_path only includes public

Recommendation

Be explicit.

Connection string:

Search Path=app

Or in SQL:

SET search_path TO app;

Or better:

• fully qualify critical objects

• treat schemas as versioning and isolation tools

---

Migrations: EF Core Is Not SQL Server Here

EF Core migrations work well with Postgres, but assumptions leak.

Common mistakes

• Renaming columns without understanding how Postgres handles it

• Dropping columns on large tables without thinking about locks

• Treating migrations as “free”

Zero-downtime mindset

Postgres forces you to be honest.

Instead of:

1. Rename column

2. Deploy

You do:

1. Add new column

2. Backfill

3. Update code

4. Remove old column later

This is not a weakness, it’s a production safety feature.

---

Connection Pooling: You Will Get This Wrong at First

This is the #1 production issue for new Postgres users in .NET.

SQL Server mental model

• Server handles lots of idle connections well

• Pool exhaustion is rare

Postgres reality

• Each connection = a backend process

• Too many connections = memory + context switching pain

Npgsql pooling

Npgsql has client-side pooling enabled by default.

Key settings:

Pooling=true;
Maximum Pool Size=100;
Minimum Pool Size=0;

Common anti-pattern

Creating new connections manually per operation without pooling awareness.

Correct pattern:

services.AddNpgsqlDataSource(connectionString);

Then inject and reuse the data source.

If you scale

You will eventually need:

• PgBouncer

• sensible max pool sizes

• observability on connection counts

Postgres rewards discipline here.

---

Tooling Differences That Surprise People

• EXPLAIN ANALYZE is essential, learn it early

• Index choice matters more (btree vs gin vs gist)

• Autovacuum is real and must be respected

• Locks behave differently than SQL Server

None of this is bad, but it is different.

---

The Big Picture: Postgres Forces Better Habits

This is the real reason many teams stick with Postgres once they adapt.

Postgres:

• punishes lazy schemas

• exposes inefficient queries

• rewards good data modelling

• encourages explicitness

For modern .NET systems, especially SaaS, multi-tenant, and event-driven architectures, that’s a feature, not a drawback.

---

Summary: What to Unlearn from SQL Server

If you remember nothing else:

• Stop relying on case-insensitivity

• Stop treating schemas as optional

• Stop assuming the database will “fix it later”

• Embrace explicit types and migrations

• Treat connections as a finite resource

Do that, and Postgres becomes a joy rather than a fight.