PostgreSQL has become the default open-source relational database for new .NET projects, and the reason is more than licensing: a mature driver, a first-class EF Core provider and an extension ecosystem that now includes vector search. This guide covers Npgsql, the ADO.NET driver everything else sits on, connection pooling and multiplexing, the parts of the EF Core provider that go beyond plain SQL (JSONB, arrays, enums and full-text search), bulk loading with COPY, similarity search with pgvector, and what changes when you host on Azure Database for PostgreSQL.

What Npgsql Brings to .NET Developers#

Npgsql is the open-source ADO.NET provider for PostgreSQL and the foundation for everything else in this guide: Dapper, raw ADO.NET code and the EF Core provider all issue commands through it. Current Npgsql releases are versioned to track the wider .NET ecosystem, so Npgsql 10.x is the version to reach for on .NET 10, and it multi-targets net8.0, net9.0 and net10.0, so the same package works on the .NET 8 LTS and .NET 9 STS you may still be running while you migrate. Both of those reach end of support on November 10, 2026, alongside .NET 10 LTS becoming the safe long-term target.

The EF Core provider, Npgsql.EntityFrameworkCore.PostgreSQL, follows a different, EF-aligned versioning scheme: its major version matches the EF Core major version it targets, so the 10.x provider line depends on Microsoft.EntityFrameworkCore 10.x, the 9.x line targets EF Core 9, and so on. Pin the provider version to your EF Core version, not to your Npgsql driver version; EF Core pulls in the exact Npgsql driver version it needs as a transitive dependency.

Npgsql goes beyond basic query execution with native support for PostgreSQL types that map awkwardly through generic ADO.NET: arrays, ranges, composites, enums, jsonb and, through plugins, PostGIS geometries and NodaTime date/time types. That native mapping is what lets the EF Core provider expose PostgreSQL-specific LINQ translations instead of forcing everything through plain scalar columns.

How Npgsql Talks to PostgreSQL#

Modern Npgsql code is built around NpgsqlDataSource rather than constructing NpgsqlConnection objects directly. A data source owns the connection pool, holds type-mapping plugins (such as pgvector's) and hands out ready-to-use connections and commands. You build one with NpgsqlDataSourceBuilder, configure it once at startup, register it as a singleton, and every part of the app asks it for connections instead of new-ing them up.

C#
using Npgsql;

var connectionString = builder.Configuration.GetConnectionString("Shop")
    ?? throw new InvalidOperationException("Set the Shop connection string.");

var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
dataSourceBuilder.EnableParameterLogging(); // development only: logs bound parameter values
await using var dataSource = dataSourceBuilder.Build();

builder.Services.AddSingleton(dataSource);

Because the data source is where plugins and pooling live, it is also where you opt in to pgvector support and Azure's passwordless authentication, both covered later in this guide.

Getting Started: A Minimal Npgsql Query#

A production-shaped query uses async I/O throughout, a cancellation token, and typed parameters so PostgreSQL never has to guess a type from a literal:

C#
using Npgsql;
using NpgsqlTypes;

public sealed record RecentOrder(long OrderId, DateTime OrderDate, decimal Total);

public static async Task<List<RecentOrder>> GetRecentOrdersAsync(
    NpgsqlDataSource dataSource, int customerId, CancellationToken ct)
{
    await using var command = dataSource.CreateCommand(
        """
        SELECT order_id, order_date, total
        FROM orders
        WHERE customer_id = @customerId
        ORDER BY order_date DESC
        LIMIT 20;
        """);
    command.Parameters.Add(new NpgsqlParameter("customerId", NpgsqlDbType.Integer)
    {
        Value = customerId,
    });

    var results = new List<RecentOrder>();
    await using var reader = await command.ExecuteReaderAsync(ct);
    while (await reader.ReadAsync(ct))
    {
        results.Add(new RecentOrder(
            reader.GetInt64(0), reader.GetDateTime(1), reader.GetDecimal(2)));
    }
    return results;
}

CreateCommand pulls a connection from the pool for the lifetime of the command and returns it automatically when the command is disposed, so there is no separate OpenAsync call to remember. The Dapper and ADO.NET guide covers the equivalent mapping layer in more depth if you prefer a thin object mapper over raw readers.

Connection Pooling and Multiplexing#

Pooling is on by default: Pooling=true, Minimum Pool Size=0 and Maximum Pool Size=100 are the connection-string defaults, and idle connections above the minimum are closed after Connection Idle Lifetime (300 seconds by default), checked every Connection Pruning Interval (10 seconds). As with any pooled ADO.NET provider, exhaustion almost always traces back to leaked connections, transactions held across slow work, or sync-over-async code rather than a pool that is genuinely too small.

Multiplexing is a separate, opt-in feature unique among mainstream .NET database drivers. With Multiplexing=true, Npgsql no longer hands one physical connection to one in-flight command. Instead, many concurrent commands share a much smaller set of physical connections: Npgsql writes each command's data to the connection's outbound buffer as it arrives and reads responses back off the wire as they complete, so throughput no longer scales with the number of open sockets. This matters most for workloads with many small, concurrent, latency-bound queries, such as a busy web API talking to a database a few milliseconds away. It is less useful for long-running commands, LISTEN/NOTIFY sessions or code that depends on session-level state (temp tables, advisory locks, SET statements) persisting across calls, because multiplexed commands are not guaranteed to reuse the same physical connection between calls.

Text
Host=pg-shop.example.com;Database=shop;Username=shop_app;Password=***;
Multiplexing=true;Maximum Pool Size=50;SSL Mode=Require

A smaller Maximum Pool Size is normal once multiplexing is on, since a handful of physical connections can now carry far more concurrent logical commands than one connection per command ever could.

Setting Up EF Core with the Npgsql Provider#

Install Npgsql.EntityFrameworkCore.PostgreSQL and register your DbContext with UseNpgsql. EnableRetryOnFailure adds an execution strategy that retries transient network and connection errors, which matters more on managed PostgreSQL than on a database on the same rack:

C#
using Microsoft.EntityFrameworkCore;

builder.Services.AddDbContextPool<ShopDbContext>(options =>
    options.UseNpgsql(connectionString, npgsql =>
    {
        npgsql.EnableRetryOnFailure(maxRetryCount: 3);
        npgsql.MigrationsHistoryTable("__ef_migrations_history", "shop");
    }));

AddDbContextPool reuses DbContext instances from a pool instead of allocating one per request, which is worth doing for high-throughput APIs; just make sure the context has no per-request mutable state that would leak between reuses. The EF Core performance guide covers pooling, compiled queries and change-tracking trade-offs in more depth.

Mapping JSONB, Arrays and Enums#

This is where the Npgsql provider earns its keep over a generic relational mapper: PostgreSQL-native types come through as native .NET types, not as strings you parse by hand.

C#
public sealed class Product
{
    public int Id { get; set; }
    public required string Name { get; set; }

    // Maps to a native PostgreSQL enum type, not an integer or text column
    public ProductStatus Status { get; set; }

    // Maps directly to a text[] column; LINQ .Contains() becomes = ANY(tags)
    public List<string> Tags { get; set; } = [];

    // Structured attributes stored as jsonb, queryable with EF.Functions.JsonContains
    public ProductAttributes Attributes { get; set; } = new();
}

public enum ProductStatus { Draft, Active, Discontinued }

public sealed class ProductAttributes
{
    public string? Color { get; set; }
    public decimal? WeightKg { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasPostgresEnum<ProductStatus>();

    modelBuilder.Entity<Product>(entity =>
    {
        entity.OwnsOne(p => p.Attributes, owned => owned.ToJson()); // stored as jsonb
        entity.HasIndex(p => p.Tags).HasMethod("GIN");              // fast array containment
    });
}

HasPostgresEnum<T>() creates and maps a real PostgreSQL CREATE TYPE ... AS ENUM type, so invalid values are rejected by the database, not just by your C# compiler. .ToJson() on an owned type stores it as jsonb and lets you query into it with EF.Functions.JsonContains and related translations, instead of hand-rolling ->>'field' expressions in raw SQL. Arrays need no special configuration at all: a List<string> or string[] property maps straight to text[], and a GIN index makes Contains and overlap queries fast at scale.

PostgreSQL feature.NET mappingGood for
Native enumC# enum + HasPostgresEnum<T>()Small, closed sets of values enforced by the database
jsonbOwned type + .ToJson(), or JsonDocumentSemi-structured attributes that vary per row
text[] / int[]List<T> / T[], no extra configSmall, unordered tag or ID lists
tsvectorNpgsqlTsVector + generated columnFull-text search without a separate search engine
vector(n)Pgvector.Vector via the pgvector packagesEmbeddings and similarity search

Full-Text Search Without a Separate Search Engine#

PostgreSQL's built-in text search avoids standing up Elasticsearch or Azure AI Search for many applications. A generated tsvector column, kept in sync by PostgreSQL itself, backs a GIN index for fast matching:

C#
public sealed class Article
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public required string Body { get; set; }
    public NpgsqlTsVector? SearchVector { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Article>()
        .HasGeneratedTsVectorColumn(a => a.SearchVector, "english", a => new { a.Title, a.Body })
        .HasIndex(a => a.SearchVector)
        .HasMethod("GIN");
}

public static Task<List<Article>> SearchAsync(ShopDbContext db, string term, CancellationToken ct) =>
    db.Articles
        .Where(a => a.SearchVector!.Matches(EF.Functions.ToTsQuery("english", term)))
        .ToListAsync(ct);

HasGeneratedTsVectorColumn tells PostgreSQL to maintain the column as a STORED GENERATED expression over the listed properties, so search stays current on every insert and update with no application code. Matches translates to the @@ text-search operator, which the GIN index makes fast even over large tables. For queries where relevance ranking or synonym handling matters more than this, layer ts_rank in raw SQL or reach for a dedicated search product; for most in-app search boxes, generated tsvector columns are enough.

Bulk Loading with COPY#

INSERT statements, even batched ones, do not compete with PostgreSQL's binary COPY protocol for loading large volumes of data. Npgsql exposes it directly as NpgsqlBinaryImporter:

C#
using Npgsql;
using NpgsqlTypes;

public static async Task ImportOrdersAsync(
    NpgsqlConnection connection, IReadOnlyList<OrderRow> orders, CancellationToken ct)
{
    await using var importer = await connection.BeginBinaryImportAsync(
        "COPY orders (order_id, customer_id, order_date, total) FROM STDIN (FORMAT BINARY)", ct);

    foreach (var order in orders)
    {
        await importer.StartRowAsync(ct);
        await importer.WriteAsync(order.OrderId, NpgsqlDbType.Bigint, ct);
        await importer.WriteAsync(order.CustomerId, NpgsqlDbType.Integer, ct);
        await importer.WriteAsync(order.OrderDate, NpgsqlDbType.TimestampTz, ct);
        await importer.WriteAsync(order.Total, NpgsqlDbType.Numeric, ct);
    }

    await importer.CompleteAsync(ct);
}

public sealed record OrderRow(long OrderId, int CustomerId, DateTime OrderDate, decimal Total);

BeginBinaryImportAsync opens a COPY ... FROM STDIN (FORMAT BINARY) stream, and each WriteAsync call sends one typed column value in PostgreSQL's binary wire format, skipping the text parsing that ordinary INSERT statements pay for on every row. CompleteAsync commits the load; disposing the importer without completing it cancels the whole import, so wrap it in a try/catch if partial failure needs cleanup logic. Drop non-unique indexes before a very large load and rebuild them afterward if index maintenance dominates the import time. The database migrations guide covers moving schema and data changes into production safely.

Similarity Search with pgvector#

pgvector turns PostgreSQL into a capable vector store: embeddings live next to the relational data that describes them, so a similarity search can filter on ordinary WHERE clauses instead of round-tripping to a separate database. From .NET, the Pgvector package supplies the Vector type and Pgvector.EntityFrameworkCore wires it into the Npgsql EF Core provider.

Bash
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Pgvector
dotnet add package Pgvector.EntityFrameworkCore
C#
using Pgvector;
using Pgvector.EntityFrameworkCore;

public sealed class ProductEmbedding
{
    public int ProductId { get; set; }

    [Column(TypeName = "vector(1536)")]
    public Vector? Embedding { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasPostgresExtension("vector");

    modelBuilder.Entity<ProductEmbedding>()
        .HasIndex(p => p.Embedding)
        .HasMethod("hnsw")
        .HasOperators("vector_cosine_ops")
        .HasStorageParameter("m", 16)
        .HasStorageParameter("ef_construction", 64);
}

Register vector support on both the data source and the DbContext (o.UseVector() inside UseNpgsql), then query with the LINQ distance operators the package adds:

C#
public static Task<List<ProductEmbedding>> FindSimilarAsync(
    ShopDbContext db, Vector queryEmbedding, CancellationToken ct) =>
    db.Set<ProductEmbedding>()
        .OrderBy(p => p.Embedding!.CosineDistance(queryEmbedding))
        .Take(10)
        .ToListAsync(ct);

CosineDistance, L2Distance and MaxInnerProduct all translate to the matching pgvector operator, so ordering by them lets the query planner use the index instead of scoring every row in .NET. HNSW indexes give strong recall with predictable query latency and are the default choice for most workloads; IVFFlat trades some recall for a faster, cheaper build and suits very large, mostly static collections better. Either way, exact search without an index is fine up to tens of thousands of rows and gives perfect recall, so do not add index complexity before you have measured a need for it. See embeddings and vector databases in .NET for how this fits into a full retrieval pipeline, and the RAG guide for the end-to-end picture.

Hosting on Azure Database for PostgreSQL#

Azure Database for PostgreSQL Flexible Server is Microsoft's current managed offering, and most of what differs from a self-hosted server is configuration rather than code. Extensions must be allow-listed on the server before CREATE EXTENSION will succeed, including vector:

Bash
az postgres flexible-server parameter set \
  --resource-group rg-shop --server-name pg-shop-prod \
  --name azure.extensions --value VECTOR

Prefer passwordless authentication with Microsoft Entra ID over embedding a password in configuration. Npgsql supports this through a periodic password provider that fetches and refreshes an access token in the background:

C#
using Azure.Core;
using Azure.Identity;

var credential = new DefaultAzureCredential();
dataSourceBuilder.UsePeriodicPasswordProvider(async (_, ct) =>
    {
        var token = await credential.GetTokenAsync(
            new TokenRequestContext(["https://ossrdbms-aad.database.windows.net/.default"]), ct);
        return token.Token;
    },
    successRefreshInterval: TimeSpan.FromMinutes(45),
    failureRefreshInterval: TimeSpan.FromSeconds(10));

Flexible Server also offers built-in connection pooling (PgBouncer-based) as a server-side option, which is worth enabling for applications that open many short-lived connections instead of pooling in-process, and it complements rather than replaces Npgsql's own client-side pooling. Combine it with zone-redundant high availability and read replicas for reporting workloads, the same way you would plan around Azure hosting options for the compute tier in front of the database.

Npgsql and EF Core vs Dapper for PostgreSQL Access#

Neither tool is wrong; they suit different parts of an application.

ConcernEF Core with NpgsqlDapper / raw Npgsql
Productivity for CRUDHigh: change tracking, migrations, LINQLower: you write SQL and mapping by hand
Control over generated SQLGood, but an abstraction layerTotal: you own every statement
PostgreSQL-specific featuresFirst-class (enums, arrays, jsonb, tsvector, vector)Available, but you wire up type handlers yourself
Bulk loadsNot built in; drop to NpgsqlConnection for COPYNatural fit, same as EF Core underneath
Best forDomain-heavy CRUD apps, typical web APIsReporting queries, hot paths, bulk operations

Most production systems use both: EF Core for the bulk of the domain model, and raw Npgsql or Dapper for the handful of queries where generated SQL is not good enough or where COPY and binary imports matter. The Dapper and ADO.NET guide goes deeper into that side.

Best Practices#

  • Build one NpgsqlDataSource per connection string and register it as a singleton; do not construct ad hoc NpgsqlConnection instances outside it.
  • Enable multiplexing for high-concurrency APIs with many short queries, and size the pool down accordingly.
  • Prefer native types over generic ones. Native enums, arrays and jsonb give the database real constraints and indexes that a varchar blob cannot.
  • Use COPY for bulk loads, not batched INSERT, and drop non-unique indexes first for very large imports.
  • Start pgvector with exact search and add HNSW only once you measure a need, then tune m and ef_construction against your recall and latency targets.
  • Go passwordless on Azure with UsePeriodicPasswordProvider and Microsoft Entra ID instead of static passwords.

Common Pitfalls#

  • Ignoring multiplexing's limits. Session state such as advisory locks, SET and temp tables does not reliably survive between multiplexed calls; use a dedicated, non-multiplexed connection for those.
  • Mapping jsonb as plain text. You lose EF.Functions.JsonContains and indexable JSON queries; use an owned type with .ToJson() instead.
  • Skipping the azure.extensions allow-list. CREATE EXTENSION vector fails on Flexible Server until the parameter is set, which looks like a permissions bug but is not.
  • Building an HNSW index too early. On small tables it adds write overhead and build time for no query-time benefit over exact search.
  • Forgetting CompleteAsync on a binary importer. A disposed-but-incomplete NpgsqlBinaryImporter cancels the entire COPY, silently discarding rows already written.
  • Mixing provider major versions. An EF Core 10 app with the 9.x Npgsql provider (or vice versa) fails to restore; keep the provider's major version aligned with your EF Core version.

Frequently Asked Questions#

Should I always enable Npgsql multiplexing?#

No. It helps most for APIs issuing many small, concurrent, latency-bound queries against a smaller connection pool. Workloads that rely on session state surviving across calls, run long commands, or use LISTEN/NOTIFY should keep dedicated, non-multiplexed connections for that work.

Do I need a separate package for pgvector, or does the EF Core provider include it?#

You need Pgvector for the Vector type and Pgvector.EntityFrameworkCore to wire vector mapping and LINQ distance functions into Npgsql.EntityFrameworkCore.PostgreSQL. Vector support is not built into the main provider package.

Can PostgreSQL full-text search replace a dedicated search engine?#

For many applications, yes. Generated tsvector columns with a GIN index handle typical search-box queries well and need no extra infrastructure. Reach for a dedicated engine when you need relevance tuning, faceting, typo tolerance or search volumes that a single PostgreSQL instance cannot serve.

HNSW or IVFFlat for a pgvector index?#

Start with HNSW for most workloads: it gives strong recall at query time with a predictable latency profile, at the cost of a slower index build. IVFFlat builds faster and costs less to maintain, which suits very large, largely static collections better than frequently updated ones.

Is EF Core fast enough for bulk PostgreSQL imports?#

Not for large volumes. SaveChanges issues one round trip per batch of tracked changes at best, while COPY through NpgsqlBinaryImporter streams rows in PostgreSQL's binary wire format. Use EF Core for everyday CRUD and drop to NpgsqlConnection for bulk loads.

Summary#

  • Build a single NpgsqlDataSource, enable multiplexing for high-concurrency APIs, and size the pool down once you do.
  • The EF Core provider maps native PostgreSQL enums, arrays, jsonb and tsvector directly, which is most of the reason to choose it over a generic mapper.
  • Use COPY through NpgsqlBinaryImporter for bulk loads, never row-by-row INSERT.
  • pgvector adds embeddings and similarity search alongside relational data; start with exact search and add an HNSW index once you have measured the need.
  • On Azure Database for PostgreSQL, allow-list extensions explicitly and prefer Microsoft Entra ID authentication over passwords.

Further Reading#