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:

```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 = ?` ❌ (