dotnet-postgresql-best-practices

.NET 的 PostgreSQL 最佳实践

概览

使用 .NET 和 Entity Framework Core 时，在 PostgreSQL 数据库设计、命名约定、索引和性能优化方面的最佳实践。

快速参考

类别	最佳实践
命名	表和列使用 snake_case
主键	使用 uuid (Guid) 或 bigserial
时间戳	使用 timestamptz 并采用 UTC
索引	为外键和唯一约束创建索引
文本	除非需要长度限制，否则使用 text，而非 varchar
JSON	使用 jsonb，而非 json

---

命名约定

Snake Case 标准

PostgreSQL 的惯例是所有标识符都使用 snake_case：

-- ✅ 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 配置

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

# Install package
dotnet add package EFCore.NamingConventions

命名模式

对象	模式	示例
表	snake_case（复数）	user_profiles、order_items
列	snake_case	first_name、created_at
主键	pk_{table}	pk_users、pk_orders
外键	fk_{table}_{ref_table}	fk_orders_users
索引	ix_{table}_{column(s)}	ix_users_email
唯一索引	uix_{table}_{column(s)}	uix_users_email
检查约束	ck_{table}_{column}	ck_users_age
序列	seq_{table}_{column}	seq_orders_order_number

---

数据类型

推荐类型

C# 类型	PostgreSQL 类型	备注
Guid	uuid	主键首选
string（不限长度）	text	比 varchar 更灵活
string（限长）	varchar(n)	仅在需要长度限制时使用
int	integer	4 字节，-2B 到 +2B
long	bigint	8 字节
decimal	numeric(p,s)	精确精度
double	double precision	浮点数
bool	boolean	true/false/null
DateTime	timestamptz	始终使用带时区的类型
byte[]	bytea	二进制数据
Dictionary<string,object>	jsonb	结构化数据
string[]	text[]	数组类型

Text 与 Varchar

-- ✅ 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
);

为什么使用 text？

• 在 PostgreSQL 中，两者没有性能差异
• 更灵活（没有任意长度限制）
• 更容易修改（更改长度无需迁移）

时间戳

-- ✅ 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

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

使用 JSONB 存储灵活数据

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

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

// 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
});

---

主键

UUID (Guid) - 推荐

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

// 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
}

优点：

• 全局唯一（不同数据库之间不会冲突）
• 可在客户端生成
• 更适合分布式系统
• 不存在顺序枚举带来的安全风险

Serial/BigSerial 替代方案

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

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

---

索引策略

为外键创建索引

-- ✅ 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 会自动创建这些索引，但请确认：

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

唯一索引

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

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

复合索引

-- ✅ 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);

顺序很重要！ (user_id, status) 上的索引适用于：

• WHERE user_id = ? AND status = ? ✅
• WHERE user_id = ? ✅
• WHERE status = ? ❌（不使用该索引）

部分索引

-- ✅ 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;

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

覆盖索引 (INCLUDE)

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

全文搜索索引

-- ✅ 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 索引

-- ✅ 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'));

不要创建索引的情况

❌ 不要为以下内容创建索引：

• 非常小的表（< 1000 行）
• 很少用于 WHERE/JOIN 的列
• 基数较低（不同值较少）的列
• 经常变化的列

---

约束

主键约束

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

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

外键约束

-- ✅ 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

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

检查约束

-- ✅ 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)

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

唯一约束

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

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

---

性能优化

连接池

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

预处理语句

Npgsql 会自动为重复查询使用预处理语句。

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

批量操作

// ✅ 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

// ✅ 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();

编译查询

// 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);

分页

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

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

避免 N+1 查询

// ❌ 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();

---

维护

分析和清理

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

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

自动清理配置

PostgreSQL 会自动运行自动清理，但如有需要，可以进行调整：

-- 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
);

监控索引使用情况

-- 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;

表膨胀

-- 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;

---

安全最佳实践

连接安全

// ✅ 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"

最小权限

-- ✅ 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

参数化查询

// ✅ 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 扩展

推荐的扩展

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

使用扩展

-- 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;

---

高并发模式

使用 xmin 系统列实现乐观并发控制

PostgreSQL 有一些隐藏的系统列，包括 xmin，它保存最近一次更新的事务 ID。它非常适合用于乐观并发控制：

// 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; }
}

// 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
    }
}

处理并发冲突

// 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.");
    }
}

使用 Polly 进行指数退避重试

// 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;
        }
    });
});

// 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 池提升性能

// 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();
});

// 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);
    }
}

连接池配置

// 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;

对关键操作使用行级锁

-- ✅ 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;

// 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);
}

使用咨询锁进行分布式协调

-- 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);

// 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);
    }
}

禁用线程安全检查（高性能）

// ⚠️ 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
});

---

关键规则

1. 使用 snake_case - PostgreSQL 标准，请使用 EFCore.NamingConventions
2. 使用 text 而非 varchar - 除非需要强制长度限制
3. 始终使用 timestamptz - 以 UTC 存储时间
4. 为外键创建索引 - EF Core 会自动执行此操作
5. 将 uuid 用作 PK - 更适合分布式系统
6. 使用 jsonb 而非 json - 二进制格式速度更快
7. 使用参数化查询 - 防止 SQL 注入
8. 监控索引使用情况 - 删除未使用的索引
9. 使用连接池 - 默认已启用
10. 定期运行 ANALYZE - 保持统计信息最新
11. 使用 xmin 实现乐观并发控制 - 每次行变更时都会自动更新
12. 实现带退避的重试 - 妥善处理瞬时故障

---

相关技能

• 06-dotnet-ef-core-configuration - EF Core 实体配置
• 05-dotnet-repository-pattern - 数据访问模式
• 19-dotnet-dapper-query-builder - 使用 Dapper 执行原始 SQL
• 01-dotnet-clean-architecture - 整体架构