Dotnet Postgresql Best Practices

PostgreSQL Best Practices for .NET

Overview

Best practices for PostgreSQL database design, naming conventions, indexing, and performance optimization when using with .NET and Entity Framework Core.

Quick Reference

Naming Conventions

Snake Case Standard

PostgreSQL convention is snake_case for all identifiers:

sqlCopy code
-- ✅ CORRECT: Snake case
CREATE TABLE user_profiles (
    user_id uuid PRIMARY KEY,
    first_name text NOT NULL,
    last_name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
    updated_at timestamptz NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')
);

-- ❌ WRONG: PascalCase or camelCase
CREATE TABLE UserProfiles (
    UserId uuid PRIMARY KEY,
    firstName text NOT NULL
);

EF Core Snake Case Setup

csharpCopy code
// DependencyInjection.cs
services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseNpgsql(connectionString)
           .UseSnakeCaseNamingConvention();  // Converts C# PascalCase to snake_case
});

bashCopy code
# Install package
dotnet add package EFCore.NamingConventions

Naming Patterns

Data Types

Recommended Types

Text vs Varchar

sqlCopy code
-- ✅ PREFERRED: Use text for most string columns
CREATE TABLE products (
    id uuid PRIMARY KEY,
    name text NOT NULL,
    description text
);

-- ⚠️ USE SPARINGLY: Only when you need to enforce length
CREATE TABLE users (
    id uuid PRIMARY KEY,
    email varchar(255) NOT NULL  -- Email has practical length limit
);

Why text?

• No performance difference in PostgreSQL
• More flexible (no arbitrary limits)
• Easier to modify (no migration needed to change length)

Timestamps

sqlCopy code
-- ✅ CORRECT: timestamptz with UTC default
created_at timestamptz NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')
updated_at timestamptz NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')

-- ❌ WRONG: timestamp without time zone
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

csharpCopy code
// EF Core configuration
builder.Property(e => e.CreatedAt)
    .HasColumnType("timestamptz")
    .IsRequired()
    .HasDefaultValueSql("CURRENT_TIMESTAMP AT TIME ZONE 'UTC'");

JSONB for Flexible Data

sqlCopy code
-- ✅ Use jsonb (binary JSON, faster, indexable)
metadata jsonb

-- ❌ Don't use json (text-based, slower)
metadata json

csharpCopy code
// EF Core configuration
builder.Property(e => e.Metadata)
    .HasColumnType("jsonb");

// Or for owned types (EF Core 7+)
builder.OwnsOne(e => e.Settings, settingsBuilder =>
{
    settingsBuilder.ToJson();  // Stores as jsonb
});

Primary Keys

UUID (Guid) - Recommended

sqlCopy code
-- ✅ RECOMMENDED: UUID primary keys
CREATE TABLE users (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    email text NOT NULL
);

csharpCopy code
// EF Core - App generates GUIDs
builder.Property(e => e.Id)
    .ValueGeneratedNever();  // Don't let DB generate

// In domain entity
public static User Create(...)
{
    return new User(Guid.NewGuid(), ...);  // App generates
}

Benefits:

• Globally unique (no collisions across databases)
• Can generate client-side
• Easier for distributed systems
• No sequential enumeration security risk

Serial/BigSerial Alternative

sqlCopy code
-- Alternative: Auto-increment
CREATE TABLE orders (
    id bigserial PRIMARY KEY,
    order_number text NOT NULL
);

csharpCopy code
// EF Core
builder.Property(e => e.Id)
    .UseIdentityColumn();  // PostgreSQL IDENTITY

Indexing Strategies

Index Foreign Keys

sqlCopy code
-- ✅ ALWAYS index foreign keys
CREATE INDEX ix_orders_user_id ON orders(user_id);
CREATE INDEX ix_order_items_order_id ON order_items(order_id);

EF Core creates these automatically, but verify:

csharpCopy code
builder.HasIndex(o => o.UserId);

Unique Indexes

sqlCopy code
-- ✅ Unique constraints
CREATE UNIQUE INDEX uix_users_email ON users(email);
CREATE UNIQUE INDEX uix_users_username ON users(username);

csharpCopy code
builder.HasIndex(u => u.Email).IsUnique();

Composite Indexes

sqlCopy code
-- ✅ Composite indexes for common query patterns
CREATE INDEX ix_orders_user_status ON orders(user_id, status);
CREATE INDEX ix_orders_created_status ON orders(created_at DESC, status);

Order matters! Index on (user_id, status) helps:

• WHERE user_id = ? AND status = ? ✅
• WHERE user_id = ? ✅
• WHERE status = ? ❌ (doesn't use index)

Partial Indexes

sqlCopy code
-- ✅ Index only relevant rows
CREATE INDEX ix_orders_pending ON orders(created_at) 
WHERE status = 'Pending';

CREATE INDEX ix_users_active_email ON users(email) 
WHERE is_deleted = false;

csharpCopy code
// EF Core
builder.HasIndex(o => o.CreatedAt)
    .HasFilter("status = 'Pending'");

Covering Indexes (INCLUDE)

sqlCopy code
-- ✅ Include frequently accessed columns
CREATE INDEX ix_users_email_include ON users(email) 
INCLUDE (first_name, last_name);

Full-Text Search Indexes

sqlCopy code
-- ✅ GIN index for full-text search
ALTER TABLE products 
ADD COLUMN search_vector tsvector 
GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))
) STORED;

CREATE INDEX ix_products_search ON products USING GIN(search_vector);

JSONB Indexes

sqlCopy code
-- ✅ GIN index for JSONB queries
CREATE INDEX ix_products_metadata ON products USING GIN(metadata);

-- Specific path index
CREATE INDEX ix_products_metadata_tags ON products USING GIN((metadata -> 'tags'));

When NOT to Index

❌ Don't index:

• Very small tables (< 1000 rows)
• Columns rarely used in WHERE/JOIN
• Columns with low cardinality (few distinct values)
• Columns that change frequently

Constraints

Primary Key Constraints

sqlCopy code
-- ✅ Named primary key
CONSTRAINT pk_users PRIMARY KEY (id)

csharpCopy code
// EF Core names automatically: pk_{table}
builder.HasKey(u => u.Id);

Foreign Key Constraints

sqlCopy code
-- ✅ Named foreign keys with appropriate delete behavior
CONSTRAINT fk_orders_users 
    FOREIGN KEY (user_id) 
    REFERENCES users(id) 
    ON DELETE RESTRICT;  -- Prevent orphans

CONSTRAINT fk_order_items_orders 
    FOREIGN KEY (order_id) 
    REFERENCES orders(id) 
    ON DELETE CASCADE;  -- Delete items with order

csharpCopy code
builder.HasOne(o => o.User)
    .WithMany(u => u.Orders)
    .HasForeignKey(o => o.UserId)
    .OnDelete(DeleteBehavior.Restrict);

Check Constraints

sqlCopy code
-- ✅ Enforce business rules at database level
CONSTRAINT ck_users_age CHECK (age >= 18 AND age <= 120)
CONSTRAINT ck_products_price CHECK (price >= 0)
CONSTRAINT ck_orders_quantity CHECK (quantity > 0)

csharpCopy code
builder.ToTable(t => t.HasCheckConstraint(
    "ck_products_price", 
    "price >= 0"));

Unique Constraints

sqlCopy code
-- ✅ Composite unique constraints
CONSTRAINT uq_user_profiles_user_type UNIQUE (user_id, profile_type)

csharpCopy code
builder.HasIndex(p => new { p.UserId, p.ProfileType }).IsUnique();

Performance Optimization

Connection Pooling

csharpCopy code
// Connection string with pooling (default enabled)
"Host=localhost;Database=mydb;Username=postgres;Password=pass;Pooling=true;MinPoolSize=1;MaxPoolSize=100"

Prepared Statements

Npgsql automatically uses prepared statements for repeated queries.

csharpCopy code
// EF Core automatically prepares statements
var users = await context.Users
    .Where(u => u.Email == email)  // Prepared on first execution
    .ToListAsync();

Batch Operations

csharpCopy code
// ✅ Batch inserts
context.Users.AddRange(users);
await context.SaveChangesAsync();  // Single round-trip

// ❌ Individual inserts
foreach (var user in users)
{
    context.Users.Add(user);
    await context.SaveChangesAsync();  // Multiple round-trips!
}

AsNoTracking for Read-Only Queries

csharpCopy code
// ✅ Read-only queries
var users = await context.Users
    .AsNoTracking()  // Faster, no change tracking
    .ToListAsync();

// ✅ Write operations (default)
var user = await context.Users.FindAsync(id);
user.Update(...);
await context.SaveChangesAsync();

Compiled Queries

csharpCopy code
// Define once
private static readonly Func<ApplicationDbContext, string, Task<User?>> GetUserByEmail =
    EF.CompileAsyncQuery((ApplicationDbContext context, string email) =>
        context.Users.FirstOrDefault(u => u.Email == email));

// Use many times (faster)
var user = await GetUserByEmail(context, email);

Pagination

sqlCopy code
-- ✅ Efficient pagination
SELECT * FROM users 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 0;

csharpCopy code
// ✅ EF Core pagination
var users = await context.Users
    .OrderByDescending(u => u.CreatedAt)
    .Skip(pageNumber * pageSize)
    .Take(pageSize)
    .ToListAsync();

Avoid N+1 Queries

csharpCopy code
// ❌ N+1 problem
var orders = await context.Orders.ToListAsync();
foreach (var order in orders)
{
    var user = await context.Users.FindAsync(order.UserId);  // N queries!
}

// ✅ Eager loading
var orders = await context.Orders
    .Include(o => o.User)  // Single query with JOIN
    .ToListAsync();

// ✅ Split query for multiple collections
var orders = await context.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .AsSplitQuery()  // Separate queries, avoids cartesian explosion
    .ToListAsync();

Maintenance

Analyze and Vacuum

sqlCopy code
-- Analyze tables (update statistics)
ANALYZE users;
ANALYZE VERBOSE;  -- All tables

-- Vacuum (reclaim space)
VACUUM users;
VACUUM ANALYZE users;  -- Both operations

Autovacuum Configuration

PostgreSQL runs autovacuum automatically, but tune if needed:

sqlCopy code
-- Check autovacuum settings
SHOW autovacuum;
SHOW autovacuum_naptime;

-- Per-table autovacuum tuning
ALTER TABLE high_traffic_table SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02
);

Monitor Index Usage

sqlCopy code
-- Find unused indexes
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Find missing indexes (sequential scans on large tables)
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / seq_scan AS avg_seq_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

Table Bloat

sqlCopy code
-- Check table bloat
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS external_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Security Best Practices

Connection Security

csharpCopy code
// ✅ Use SSL/TLS
"Host=prod-server;Database=mydb;Username=app_user;Password=pass;SSL Mode=Require"

// ✅ Use connection pooling limits
"Host=localhost;Database=mydb;Username=postgres;Password=pass;MaxPoolSize=50;Timeout=30"

Least Privilege

sqlCopy code
-- ✅ Create application-specific user
CREATE USER app_user WITH PASSWORD 'secure_password';

-- Grant only necessary permissions
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- ❌ Don't use superuser for application
-- Don't grant ALL PRIVILEGES

Parameterized Queries

csharpCopy code
// ✅ EF Core uses parameters automatically (safe from SQL injection)
var users = await context.Users
    .Where(u => u.Email == email)  // Parameterized
    .ToListAsync();

// ✅ Dapper with parameters
var users = await connection.QueryAsync<User>(
    "SELECT * FROM users WHERE email = @Email",
    new { Email = email });

// ❌ NEVER concatenate SQL
var sql = $"SELECT * FROM users WHERE email = '{email}'";  // SQL INJECTION!

PostgreSQL Extensions

Recommended Extensions

sqlCopy code
-- UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- gen_random_uuid()

-- Full-text search
CREATE EXTENSION IF NOT EXISTS "pg_trgm";  -- Trigram matching

-- Case-insensitive text
CREATE EXTENSION IF NOT EXISTS "citext";

-- Additional types
CREATE EXTENSION IF NOT EXISTS "hstore";  -- Key-value store
CREATE EXTENSION IF NOT EXISTS "ltree";   -- Hierarchical data

Using Extensions

sqlCopy code
-- UUID generation
CREATE TABLE users (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid()
);

-- Case-insensitive email
CREATE TABLE users (
    id uuid PRIMARY KEY,
    email citext UNIQUE NOT NULL
);

-- Trigram similarity search
CREATE INDEX ix_products_name_trgm ON products USING GIN(name gin_trgm_ops);

SELECT * FROM products 
WHERE name % 'search term'  -- Similarity operator
ORDER BY similarity(name, 'search term') DESC;

High Concurrency Patterns

Optimistic Concurrency with xmin System Column

PostgreSQL has hidden system columns including xmin which holds the transaction ID of the last update. This is ideal for optimistic concurrency:

csharpCopy code
// src/{name}.domain/{Entity}/{Entity}.cs
public class Order
{
    public Guid Id { get; private set; }
    public string Status { get; private set; }

    // Concurrency token - maps to PostgreSQL xmin system column
    public uint RowVersion { get; private set; }
}

csharpCopy code
// src/{name}.infrastructure/Configurations/OrderConfiguration.cs
internal sealed class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.ToTable("orders");
        builder.HasKey(o => o.Id);

        // ═══════════════════════════════════════════════════════════════
        // OPTIMISTIC CONCURRENCY WITH xmin
        // PostgreSQL xmin system column auto-updates on every row change
        // ═══════════════════════════════════════════════════════════════
        builder.Property(o => o.RowVersion)
            .IsRowVersion();  // Npgsql maps this to xmin automatically
    }
}

Handling Concurrency Conflicts

csharpCopy code
// src/{name}.infrastructure/Repositories/OrderRepository.cs
public async Task UpdateAsync(Order order, CancellationToken cancellationToken)
{
    try
    {
        context.Orders.Update(order);
        await context.SaveChangesAsync(cancellationToken);
    }
    catch (DbUpdateConcurrencyException ex)
    {
        // Another user modified this row since we loaded it
        var entry = ex.Entries.Single();
        var databaseValues = await entry.GetDatabaseValuesAsync(cancellationToken);

        if (databaseValues is null)
        {
            throw new EntityNotFoundException("Order was deleted by another user");
        }

        // Option 1: Client wins - overwrite database values
        // entry.OriginalValues.SetValues(databaseValues);
        // await context.SaveChangesAsync(cancellationToken);

        // Option 2: Database wins - throw and let caller retry
        throw new ConcurrencyException("Order was modified by another user. Please refresh and try again.");
    }
}

Retry with Exponential Backoff (Polly)

csharpCopy code
// src/{name}.infrastructure/DependencyInjection.cs
using Polly;
using Polly.Retry;

// Configure retry policy for transient database errors
services.AddResiliencePipeline("database", builder =>
{
    builder.AddRetry(new RetryStrategyOptions
    {
        ShouldHandle = new PredicateBuilder()
            .Handle<DbUpdateConcurrencyException>()
            .Handle<NpgsqlException>(ex => ex.IsTransient),
        MaxRetryAttempts = 3,
        Delay = TimeSpan.FromMilliseconds(200),
        BackoffType = DelayBackoffType.Exponential,
        UseJitter = true,  // Adds randomness to prevent thundering herd
        OnRetry = static args =>
        {
            Console.WriteLine($"Retry attempt {args.AttemptNumber} after {args.RetryDelay}");
            return default;
        }
    });
});

csharpCopy code
// Usage in handler
public class UpdateOrderHandler : ICommandHandler<UpdateOrderCommand, Unit>
{
    private readonly ResiliencePipeline _pipeline;

    public async Task<Unit> HandleAsync(UpdateOrderCommand command, CancellationToken ct)
    {
        await _pipeline.ExecuteAsync(async token =>
        {
            var order = await _repository.GetByIdAsync(command.OrderId, token);
            order.Update(command.Status);
            await _repository.UpdateAsync(order, token);
        }, ct);

        return Unit.Value;
    }
}

DbContext Pooling for High Performance

csharpCopy code
// src/{name}.infrastructure/DependencyInjection.cs
services.AddDbContextPool<ApplicationDbContext>(options =>
{
    options.UseNpgsql(connectionString)
           .UseSnakeCaseNamingConvention();
}, poolSize: 128);  // Default is 1024, tune based on load

// For even higher performance, use PooledDbContextFactory
services.AddPooledDbContextFactory<ApplicationDbContext>(options =>
{
    options.UseNpgsql(connectionString)
           .UseSnakeCaseNamingConvention();
});

csharpCopy code
// Usage with PooledDbContextFactory (avoids DI overhead)
public class HighPerformanceQueryHandler
{
    private readonly IDbContextFactory<ApplicationDbContext> _contextFactory;

    public async Task<List<Order>> GetOrdersAsync(CancellationToken ct)
    {
        await using var context = await _contextFactory.CreateDbContextAsync(ct);

        return await context.Orders
            .AsNoTracking()
            .Where(o => o.Status == "Active")
            .ToListAsync(ct);
    }
}

Connection Pooling Configuration

csharpCopy code
// Optimized connection string for high concurrency
var connectionString = new NpgsqlConnectionStringBuilder
{
    Host = "localhost",
    Database = "mydb",
    Username = "app_user",
    Password = "secret",

    // ═══════════════════════════════════════════════════════════════
    // CONNECTION POOLING SETTINGS
    // ═══════════════════════════════════════════════════════════════
    Pooling = true,
    MinPoolSize = 10,           // Keep 10 connections warm
    MaxPoolSize = 100,          // Maximum concurrent connections
    ConnectionIdleLifetime = 300, // Close idle connections after 5 min
    ConnectionPruningInterval = 10, // Check for idle every 10 sec

    // ═══════════════════════════════════════════════════════════════
    // TIMEOUT SETTINGS
    // ═══════════════════════════════════════════════════════════════
    Timeout = 30,               // Connection timeout in seconds
    CommandTimeout = 60,        // Query timeout in seconds

    // ═══════════════════════════════════════════════════════════════
    // PERFORMANCE SETTINGS
    // ═══════════════════════════════════════════════════════════════
    MaxAutoPrepare = 20,        // Auto-prepare frequently used queries
    AutoPrepareMinUsages = 5,   // Prepare after 5 uses
    WriteBufferSize = 16384,    // 16KB write buffer
    ReadBufferSize = 16384,     // 16KB read buffer

    // ═══════════════════════════════════════════════════════════════
    // MULTIPLEXING (Npgsql 6.0+)
    // ═══════════════════════════════════════════════════════════════
    Multiplexing = true,        // Share connections for multiple commands

    // ═══════════════════════════════════════════════════════════════
    // SSL FOR PRODUCTION
    // ═══════════════════════════════════════════════════════════════
    SslMode = SslMode.Require,
    TrustServerCertificate = false
}.ConnectionString;

Row-Level Locking for Critical Operations

sqlCopy code
-- ✅ SELECT FOR UPDATE - Lock rows during transaction
SELECT * FROM accounts WHERE id = $1 FOR UPDATE;

-- ✅ Skip locked rows (for queue processing)
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;

-- ✅ NOWAIT - Fail immediately if locked
SELECT * FROM accounts WHERE id = $1 FOR UPDATE NOWAIT;

csharpCopy code
// EF Core equivalent using raw SQL
public async Task<Account?> GetForUpdateAsync(Guid id, CancellationToken ct)
{
    return await context.Accounts
        .FromSqlInterpolated($"SELECT * FROM accounts WHERE id = {id} FOR UPDATE")
        .FirstOrDefaultAsync(ct);
}

// Queue processing with SKIP LOCKED
public async Task<List<Job>> GetPendingJobsAsync(int batchSize, CancellationToken ct)
{
    return await context.Jobs
        .FromSqlInterpolated($@"
            SELECT * FROM jobs
            WHERE status = 'pending'
            ORDER BY created_at
            LIMIT {batchSize}
            FOR UPDATE SKIP LOCKED")
        .ToListAsync(ct);
}

Advisory Locks for Distributed Coordination

sqlCopy code
-- Application-level locks (not row-level)
-- Lock is held until session ends or explicitly released

-- Acquire blocking lock
SELECT pg_advisory_lock(12345);

-- Try to acquire (returns true/false immediately)
SELECT pg_try_advisory_lock(12345);

-- Release lock
SELECT pg_advisory_unlock(12345);

csharpCopy code
// EF Core advisory lock helper
public class AdvisoryLockService
{
    private readonly ApplicationDbContext _context;

    public async Task<bool> TryAcquireLockAsync(long lockId, CancellationToken ct)
    {
        var result = await _context.Database
            .SqlQuery<bool>($"SELECT pg_try_advisory_lock({lockId})")
            .FirstAsync(ct);
        return result;
    }

    public async Task ReleaseLockAsync(long lockId, CancellationToken ct)
    {
        await _context.Database
            .ExecuteSqlAsync($"SELECT pg_advisory_unlock({lockId})", ct);
    }
}

// Usage for distributed singleton operations
public async Task ProcessDailyReportAsync(CancellationToken ct)
{
    const long DAILY_REPORT_LOCK = 1001;

    if (!await _lockService.TryAcquireLockAsync(DAILY_REPORT_LOCK, ct))
    {
        _logger.LogInformation("Another instance is processing the daily report");
        return;
    }

    try
    {
        // Only one instance executes this
        await GenerateReportAsync(ct);
    }
    finally
    {
        await _lockService.ReleaseLockAsync(DAILY_REPORT_LOCK, ct);
    }
}

Disable Thread Safety Checks (High Performance)

csharpCopy code
// ⚠️ Only after thorough testing!
// Saves ~50ns per operation but removes safety checks
services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseNpgsql(connectionString)
           .UseSnakeCaseNamingConvention()
           .EnableThreadSafetyChecks(false);  // Disable in production
});

Critical Rules

1. Use snake_case - PostgreSQL standard, use EFCore.NamingConventions
2. Use text over varchar - Unless you need length enforcement
3. Always use timestamptz - Store times in UTC
4. Index foreign keys - EF Core does this automatically
5. Use uuid for PKs - Better for distributed systems
6. Use jsonb not json - Binary format is faster
7. Parameterize queries - Prevent SQL injection
8. Monitor index usage - Drop unused indexes
9. Use connection pooling - Enabled by default
10. Run ANALYZE regularly - Keep statistics current
11. Use xmin for optimistic concurrency - Auto-updates on every row change
12. Implement retry with backoff - Handle transient failures gracefully

Related Skills

• 06-dotnet-ef-core-configuration - EF Core entity configurations
• 05-dotnet-repository-pattern - Data access patterns
• 19-dotnet-dapper-query-builder - Raw SQL with Dapper
• 01-dotnet-clean-architecture - Overall architecture