PostgreSQL 18 Performance Tuning for EF Core Applications

Database performance directly affects the responsiveness and scalability of every ASP.NET Core application. Even well-written EF Core code can perform poorly if PostgreSQL is not properly configured or queries are not optimized.

Common issues such as slow queries, missing indexes, excessive data loading, and inefficient tracking can significantly increase response times and database load.

In this article, you'll learn practical techniques to optimize PostgreSQL 18 for EF Core applications, improve query performance, and build a repeatable performance tuning process for production environments.

Note: This article focuses on optimization techniques and query analysis. Actual performance improvements depend on your schema, workload, hardware, and PostgreSQL configuration.

Why Database Performance Matters

A typical API request involves several steps:

Client
   │
   ▼
ASP.NET Core API
   │
   ▼
EF Core
   │
   ▼
PostgreSQL
   │
   ▼
Return Results

If PostgreSQL takes hundreds of milliseconds to execute a query, no amount of API optimization can compensate for that delay.

Performance tuning should therefore begin with the database.

Configure EF Core with PostgreSQL

Install the PostgreSQL provider.

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

Configure the database context.

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(
        builder.Configuration.GetConnectionString("Postgres")));

A simple connection setup is enough to begin, but production environments often include connection pooling, SSL configuration, and retry policies.

Use Indexes Effectively

One of the most common causes of slow queries is missing indexes.

Suppose products are frequently searched by category.

public class Product
{
    public int Id { get; set; }

    public string Category { get; set; } = "";

    public decimal Price { get; set; }
}

Create an index.

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<Product>()
        .HasIndex(p => p.Category);
}

Generate the migration.

dotnet ef migrations add AddCategoryIndex
dotnet ef database update

Indexes reduce the amount of data PostgreSQL must scan during queries.

Retrieve Only Required Columns

Avoid loading unnecessary data.

Instead of:

var products = await context.Products.ToListAsync();

Project only the required fields.

var products = await context.Products
    .Select(p => new
    {
        p.Id,
        p.Name,
        p.Price
    })
    .ToListAsync();

This reduces:

Network traffic
Memory usage
Query execution time

Disable Tracking for Read-Only Queries

EF Core tracks entities by default.

For read-only operations, tracking adds unnecessary overhead.

Instead of:

var products = await context.Products.ToListAsync();

Use:

var products = await context.Products
    .AsNoTracking()
    .ToListAsync();

AsNoTracking() reduces memory usage and improves query performance.

Avoid N+1 Queries

Consider the following code.

var orders = await context.Orders.ToListAsync();

foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name);
}

Each customer may trigger another database query.

Instead, use eager loading.

var orders = await context.Orders
    .Include(o => o.Customer)
    .ToListAsync();

This retrieves related data in fewer database round trips.

Use Pagination

Loading thousands of records is rarely necessary.

Avoid:

var products = await context.Products.ToListAsync();

Use pagination.

var products = await context.Products
    .OrderBy(p => p.Id)
    .Skip(50)
    .Take(20)
    .ToListAsync();

Pagination improves response times and reduces memory consumption.

Execute Raw SQL When Appropriate

Some complex queries are easier to express in SQL.

var products = await context.Products
    .FromSqlInterpolated($"""
        SELECT *
        FROM "Products"
        WHERE "Price" > {500}
    """)
    .ToListAsync();

Use raw SQL carefully and only when it provides a measurable advantage or better expresses complex database operations.

Analyze Query Plans

PostgreSQL provides execution plans using EXPLAIN ANALYZE.

Example:

EXPLAIN ANALYZE
SELECT *
FROM "Products"
WHERE "Category" = 'Laptop';

Review the output for:

Sequential scans
Index scans
Execution time
Estimated vs. actual rows
Cost estimates

Execution plans often reveal missing indexes or inefficient query patterns.

Connection Pooling

Opening a new database connection for every request is expensive.

The Npgsql provider supports connection pooling automatically.

Example connection string:

Host=localhost;
Database=Store;
Username=postgres;
Password=password;
Maximum Pool Size=100;

Adjust pool sizes according to application workload and server capacity.

Compiled Queries

Frequently executed queries can benefit from compiled queries.

private static readonly Func<AppDbContext, int, Task<Product?>>
GetProduct =
    EF.CompileAsyncQuery(
        (AppDbContext context, int id) =>
            context.Products.FirstOrDefault(p => p.Id == id));

Invoke the compiled query.

var product = await GetProduct(context, 5);

Compiled queries reduce query translation overhead for repeated execution.

End-to-End Optimization Workflow

A practical optimization process looks like this:

Identify slow endpoints.
Enable EF Core SQL logging.
Review generated SQL.
Analyze execution plans using EXPLAIN ANALYZE.
Add or adjust indexes.
Reduce unnecessary data retrieval.
Use AsNoTracking() where appropriate.
Re-test under production-like workloads.

This structured workflow helps identify root causes instead of relying on assumptions.

Common Optimization Techniques

Technique	Benefit
Indexes	Faster lookups
Projection	Reduced network traffic
AsNoTracking	Lower memory usage
Pagination	Smaller result sets
Include	Eliminates N+1 queries
Compiled Queries	Lower query translation cost
Connection Pooling	Reduced connection overhead
Execution Plans	Identify slow operations

Performance Tuning Methodology

The research brief does not include benchmark results or production measurements. To evaluate performance improvements in your own environment:

Test Environment

Maintain consistency across benchmark runs:

PostgreSQL version
.NET SDK
EF Core version
Database size
Hardware configuration

Test Scenarios

Measure:

Single-row lookups
Filtered searches
Large result sets
Joins
Insert operations
Update operations
Concurrent users

Metrics to Collect

Record:

Query execution time
API response time
CPU utilization
Memory usage
Database connections
Throughput
Slow query count

Useful Tools

Performance investigations commonly use:

EXPLAIN ANALYZE
pgAdmin
PostgreSQL logs
dotnet-counters
dotnet-trace
BenchmarkDotNet (for isolated code paths)
k6 or Apache JMeter (for API load testing)

Avoid comparing results across different hardware or database sizes, as workload characteristics significantly influence performance.

Best Practices

Index frequently filtered columns.
Use AsNoTracking() for read-only queries.
Retrieve only required columns.
Apply pagination to large datasets.
Analyze execution plans regularly.
Enable connection pooling.
Monitor slow queries in production.
Keep EF Core and PostgreSQL versions aligned with supported releases.

Common Mistakes

Mistake	Impact
Missing indexes	Slow table scans
Loading entire tables	High memory usage
Ignoring execution plans	Hidden bottlenecks
Returning unnecessary columns	Increased network traffic
Excessive eager loading	Large SQL queries
Disabling pagination	Poor scalability

Troubleshooting

Queries Become Increasingly Slow

Check:

Missing indexes
Database statistics
Execution plans
Lock contention
Large table growth

High Memory Usage

Review:

Tracking behavior
Result set size
Object projections
Pagination strategy

Database CPU Usage Is High

Investigate:

Sequential scans
Expensive joins
Repeated queries
Missing indexes
Inefficient SQL generation

FAQs

Should every column have an index?

No. Indexes improve read performance but increase storage requirements and slow insert, update, and delete operations. Index only columns that are frequently filtered, sorted, or joined.

When should I use `AsNoTracking()`?

Use it for read-only queries where entities are not modified after retrieval.

Are compiled queries always faster?

Compiled queries reduce query compilation overhead for frequently executed queries, but the benefit depends on workload characteristics.

How do I identify slow PostgreSQL queries?

Use EXPLAIN ANALYZE, PostgreSQL logs, and query monitoring tools to review execution plans and identify bottlenecks.

Should I use raw SQL instead of EF Core?

Most applications perform well with EF Core LINQ queries. Use raw SQL only when it provides a measurable advantage or better expresses complex database operations.

Conclusion

Optimizing PostgreSQL for EF Core applications requires more than adding indexes or increasing hardware resources. Effective tuning involves understanding query execution, minimizing unnecessary data retrieval, using efficient EF Core features, and continuously analyzing database performance.

By combining execution plan analysis, proper indexing, connection pooling, compiled queries, and production monitoring, you can build PostgreSQL-backed applications that remain responsive and scalable as data volume and user traffic grow.