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:
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:
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:
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.