You have shipped .NET APIs backed by SQL Server for years, and the next project lands with a PostgreSQL requirement. Or maybe your team is evaluating whether to drop SQL Server licence costs entirely. Either way, you are about to discover that "it is just another relational database" is only partially true.
Both SQL Server and PostgreSQL are mature, ACID-compliant databases with excellent EF Core support, but they make very different decisions around licensing, data types, and SQL dialect. This post unpacks those differences with real code so you can make the switch with confidence instead of surprises.
By the end you will have a side-by-side code comparison, a clear breakdown of the tradeoffs, and a decision framework you can use in your next architecture conversation.
SQL Server grew out of Microsoft's partnership with Sybase in the late 1980s and became the default relational database for Windows-based enterprise .NET applications. It is proprietary, commercially licensed, and deeply integrated with the Microsoft tooling ecosystem.
PostgreSQL descended from the POSTGRES project at UC Berkeley, went fully open-source in 1996, and has since grown into one of the most feature-rich databases in existence, with a reputation for standards compliance and extensibility.
Both use a cost-based query planner, support full ACID transactions, foreign keys, triggers, and stored procedures, and both have first-class EF Core providers that track releases closely.
To make the differences concrete, here is the same EF Core setup performed for each database: registering the context, applying a migration, and executing a simple parameterised query.
SQL Server approach
// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(
builder.Configuration.GetConnectionString("Default"),
sql => sql.MigrationsHistoryTable("__EFMigrationsHistory", "dbo")
));
// AppDbContext.cs
public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
public DbSet<Product> Products => Set<Product>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>()
.Property(p => p.Name)
.HasMaxLength(256)
.IsRequired();
}
}
PostgreSQL approach
// Program.cs — only the provider registration changes
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseNpgsql(
builder.Configuration.GetConnectionString("Default"),
npgsql => npgsql.MigrationsHistoryTable("__ef_migrations_history", "public")
));
// AppDbContext.cs — identical; EF Core abstracts the provider completely
public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
public DbSet<Product> Products => Set<Product>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>()
.Property(p => p.Name)
.HasMaxLength(256)
.IsRequired();
}
}
The DbContext and entity configuration are identical. The provider swap is contained entirely in the Program.cs registration. The first gotcha surfaces in migrations: PostgreSQL folds unquoted identifiers to lowercase, so a column named Name becomes name on disk. The Npgsql provider handles this transparently in generated migrations, but any raw SQL you have written against SQL Server conventions will need a review pass before it works on PostgreSQL.
1. Licensing and cost
SQL Server Standard edition starts at around $900 per core; Enterprise runs into the tens of thousands. Developer edition is free but cannot be used in production. PostgreSQL is completely free under the PostgreSQL Licence with no core count or server caps. For teams running many environments (dev, staging, pre-prod, multiple production regions), the licence difference is significant. Azure Database for PostgreSQL Flexible Server also prices lower than Azure SQL on equivalent compute tiers, though your specific configuration will vary.
Does this matter to you? If you are building SaaS and want to avoid per-core fee growth as you scale, PostgreSQL wins on economics. If your organisation already covers SQL Server under a Microsoft Enterprise Agreement, the marginal cost may already be absorbed.
2. Data types
PostgreSQL ships with native types that SQL Server lacks or emulates awkwardly: jsonb (binary JSON with index support), uuid (first-class UUID without the byte-order quirks of uniqueidentifier), typed arrays, range types, and the citext extension for case-insensitive text. SQL Server stores JSON as plain nvarchar(max) and queries it through scalar functions like JSON_VALUE and OPENJSON, without binary storage or index support on that data.
// EF Core 8: JSONB column via owned entity (PostgreSQL maps ToJson() to jsonb)
public class Product
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public ProductMetadata? Metadata { get; set; }
}
public class ProductMetadata
{
public string? Sku { get; set; }
public List<string> Tags { get; set; } = new();
}
// In OnModelCreating:
modelBuilder.Entity<Product>()
.OwnsOne(p => p.Metadata, b => b.ToJson());
// Npgsql emits: "Metadata" jsonb
// SQL Server emits: "Metadata" nvarchar(max)
Same EF Core API; different storage reality. On PostgreSQL you can index into that jsonb column with a GIN index and run efficient containment queries. On SQL Server you are doing a full table scan through a function call.
3. SQL dialect differences
PostgreSQL is stricter about ANSI SQL compliance. Several T-SQL patterns break immediately: TOP becomes LIMIT, string concatenation with + must become || or CONCAT(), and GETDATE() becomes NOW() or CURRENT_TIMESTAMP. LINQ queries translated by EF Core are provider-aware and generally safe, but anything written with FromSqlRaw, Dapper, or stored procedures needs a dialect review. The case-sensitivity difference also catches teams off-guard: SQL Server string comparisons are case-insensitive by default (depending on collation); PostgreSQL is case-sensitive by default.
4. Tooling
SQL Server Management Studio is a mature, battle-tested GUI with profiler integration, visual execution plan analysis, and a large ecosystem of DBA tooling. PostgreSQL's first-party tool, pgAdmin 4, has improved significantly but still lags SSMS for complex query plan visualisation. Third-party tools such as DBeaver Community and JetBrains DataGrip fill that gap well. For .NET developers, the EF Core CLI (dotnet ef migrations add, dotnet ef database update) works identically across providers.
5. Extensions and ecosystem
PostgreSQL's extension system is one of its biggest differentiators. PostGIS adds production-grade geospatial support. TimescaleDB turns it into a time-series database. pg_trgm enables trigram similarity search. pgvector adds vector embeddings for AI workloads. These capabilities require separate licences or entirely different database products under SQL Server.
The decision comes down to your licensing economics, the PostgreSQL-specific features your domain genuinely needs, and your team's operational familiarity.
Choose SQL Server when:
- Your organisation covers SQL Server licences under a Microsoft EA and switching has no financial upside
- Your team depends on SSMS, SQL Profiler, SQL Server Agent, or SSIS for operational workflows
- You are integrating with SSRS, SSIS, or other SQL Server ecosystem components
- Your team has deep T-SQL expertise and limited bandwidth to ramp on PostgreSQL dialect differences
Choose PostgreSQL when:
- You want to eliminate per-core licence costs, particularly across many environments
- Your domain benefits from native
jsonb, arrays, range types, or extensions like PostGIS or pgvector - You are deploying to Linux containers, AWS RDS, or Azure Database for PostgreSQL Flexible Server
- SQL portability and ANSI standards compliance matter to your architecture
- You need full-text search, geospatial queries, or vector similarity without additional licences
You can also run both in the same solution. EF Core's per-context provider model makes it straightforward to keep an existing SQL Server context alongside a new PostgreSQL context for a service that needs JSONB or cost efficiency.
// Running two providers side by side in the same .NET 8 application
builder.Services.AddDbContext<LegacyDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("Legacy")));
builder.Services.AddDbContext<CatalogDbContext>(options =>
options.UseNpgsql(builder.Configuration.GetConnectionString("Catalog")));
| Concern | SQL Server | PostgreSQL |
|---|---|---|
| Licence | Commercial (free Developer edition, not for prod) | Open-source, free in all environments |
| EF Core NuGet | Microsoft.EntityFrameworkCore.SqlServer | Npgsql.EntityFrameworkCore.PostgreSQL |
| Native JSON storage | nvarchar(max) with scalar functions | jsonb with binary indexing |
| UUID type | uniqueidentifier (non-standard byte order) | uuid (RFC 4122, native) |
| Case sensitivity | Case-insensitive by default (collation-dependent) | Case-sensitive by default |
| Full-text search | Separate FT Catalog and Index, CONTAINS/FREETEXT | Built-in via tsvector and tsquery |
| Arrays | Not supported natively | First-class typed array columns |
| Extensions | Limited; additional features require licences | PostGIS, pgvector, TimescaleDB, pg_trgm, and more |
| Primary GUI | SQL Server Management Studio | pgAdmin 4, DBeaver, DataGrip |
| Docker image | mcr.microsoft.com/mssql/server | postgres (official, minimal, fast to pull) |
If you are starting a new project today and do not have SQL Server licences already in place, PostgreSQL is the stronger default for .NET work in 2024. The Npgsql EF Core provider is excellent, the type system is richer, cloud-managed costs are lower, and the extension ecosystem opens doors that would require separate products under SQL Server.
If your team lives in SSMS, your SQL Server licences are paid for, and your domain has no need for JSONB or PostgreSQL-specific extensions, there is no compelling reason to switch for its own sake. The migration effort is real, and "it's free" is not a good enough reason on its own. Use PostgreSQL when the specific features or the economics give you a clear return on the investment.
- Spin up PostgreSQL locally: Run
docker run --name pg-dev -e POSTGRES_PASSWORD=dev -p 5432:5432 -d postgres:16and point your first EF Core migration at it - Explore JSONB: The next post in this series covers JSONB columns in EF Core 8 with query examples, GIN indexing, and performance considerations
- Audit your raw SQL: Before migrating an existing project, grep your codebase for
FromSqlRawand Dapper queries and flag any T-SQL-specific syntax for rewriting
No comments: