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

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

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

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

# 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

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

-- ✅ 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 for Flexible Data

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

Primary Keys

UUID (Guid) - Recommended

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

Benefits:

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

Serial/BigSerial Alternative

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

Indexing Strategies

Index Foreign Keys

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

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

Unique Indexes

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

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

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

Covering Indexes (INCLUDE)

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

Full-Text Search Indexes

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

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

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

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

Foreign Key Constraints

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

Check Constra