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
| Category | Best Practice | |
|---|
| Naming | snake_case for tables/columns | |
| Primary Keys | Use uuid (Guid) or bigserial | |
| Timestamps | Use timestamptz with UTC | |
| Indexes | Index foreign keys, unique constraints | |
| Text | Use text not varchar unless limit needed | |
| JSON | Use jsonb not json | |
Naming Conventions
Snake Case Standard
PostgreSQL convention is snake_case for all identifiers:
sql
-- ✅ 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
csharp
// DependencyInjection.cs
services.AddDbContext<ApplicationDbContext>(options =>
{
options.UseNpgsql(connectionString)
.UseSnakeCaseNamingConvention(); // Converts C# PascalCase to snake_case
});
bash
# Install package
dotnet add package EFCore.NamingConventions
Naming Patterns
| Object | Pattern | Example | |
|---|
| Tables | snake_case (plural) | user_profiles, order_items | |
| Columns | snake_case | first_name, created_at | |
| Primary Keys | pk_{table} | pk_users, pk_orders | |
| Foreign Keys | fk_{table}_{ref_table} | fk_orders_users | |
| Indexes | ix_{table}_{column(s)} | ix_users_email | |
| Unique Indexes | uix_{table}_{column(s)} | uix_users_email | |
| Check Constraints | ck_{table}_{column} | ck_users_age | |
| Sequences | seq_{table}_{column} | seq_orders_order_number | |
Data Types
Recommended Types
| C# Type | PostgreSQL Type | Notes | |
|---|
Guid | uuid | Preferred for primary keys | |
string (unlimited) | text | More flexible than varchar | |
string (limited) | varchar(n) | Only when length limit needed | |
int | integer | 4 bytes, -2B to +2B | |
long | bigint | 8 bytes | |
decimal | numeric(p,s) | Exact precision | |
double | double precision | Floating point | |
bool | boolean | true/false/null | |
DateTime | timestamptz | Always use with time zone | |
byte[] | bytea | Binary data | |
Dictionary<string,object> | jsonb | Structured data | |
string[] | text[] | Array type | |
Text vs Varchar
sql
-- ✅ 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
sql
-- ✅ 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
csharp
// EF Core configuration
builder.Property(e => e.CreatedAt)
.HasColumnType("timestamptz")
.IsRequired()
.HasDefaultValueSql("CURRENT_TIMESTAMP AT TIME ZONE 'UTC'");
JSONB for Flexible Data
sql
-- ✅ Use jsonb (binary JSON, faster, indexable)
metadata jsonb
-- ❌ Don't use json (text-based, slower)
metadata json
csharp
// 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
sql
-- ✅ RECOMMENDED: UUID primary keys
CREATE TABLE users (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
email text NOT NULL
);
csharp
// 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
sql
-- Alternative: Auto-increment
CREATE TABLE orders (
id bigserial PRIMARY KEY,
order_number text NOT NULL
);
csharp
// EF Core
builder.Property(e => e.Id)
.UseIdentityColumn(); // PostgreSQL IDENTITY
Indexing Strategies
Index Foreign Keys
sql
-- ✅ 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:
csharp
builder.HasIndex(o => o.UserId);
Unique Indexes
sql
-- ✅ Unique constraints
CREATE UNIQUE INDEX uix_users_email ON users(email);
CREATE UNIQUE INDEX uix_users_username ON users(username);
csharp
builder.HasIndex(u => u.Email).IsUnique();
Composite Indexes
sql
-- ✅ 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
sql
-- ✅ 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;
csharp
// EF Core
builder.HasIndex(o => o.CreatedAt)
.HasFilter("status = 'Pending'");
Covering Indexes (INCLUDE)
sql
-- ✅ Include frequently accessed columns
CREATE INDEX ix_users_email_include ON users(email)
INCLUDE (first_name, last_name);
Full-Text Search Indexes
sql
-- ✅ 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
sql
-- ✅ 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
sql
-- ✅ Named primary key
CONSTRAINT pk_users PRIMARY KEY (id)
csharp
// EF Core names automatically: pk_{table}
builder.HasKey(u => u.Id);
Foreign Key Constraints
sql
-- ✅ 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
csharp
builder.HasOne(o => o.User)
.WithMany(u => u.Orders)
.HasForeignKey(o => o.UserId)
.OnDelete(DeleteBehavior.Restrict);
Check Constraints
sql
-- ✅ 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)
csharp
builder.ToTable(t => t.HasCheckConstraint(
"ck_products_price",
"price >= 0"));
Unique Constraints
sql
-- ✅ Composite unique constraints
CONSTRAINT uq_user_profiles_user_type UNIQUE (user_id, profile_type)
csharp
builder.HasIndex(p => new { p.UserId, p.ProfileType }).IsUnique();
Performance Optimization
Connection Pooling
csharp
// 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.
csharp
// EF Core automatically prepares statements
var users = await context.Users
.Where(u => u.Email == email) // Prepared on first execution
.ToListAsync();
Batch Operations
csharp
// ✅ 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
csharp
// ✅ 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
csharp
// 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
sql
-- ✅ Efficient pagination
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
csharp
// ✅ EF Core pagination
var users = await context.Users
.OrderByDescending(u => u.CreatedAt)
.Skip(pageNumber * pageSize)
.Take(pageSize)
.ToListAsync();
Avoid N+1 Queries
csharp
// ❌ 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
sql
-- 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:
sql
-- 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
sql
-- 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