DEV Community: Rhuturaj Takle

Azure DevOps: Microsoft's Platform for Repos, Pipelines, Boards, and Deployments

Rhuturaj Takle — Sat, 25 Jul 2026 13:17:20 +0000

Azure DevOps: Microsoft's Platform for Repos, Pipelines, Boards, and Deployments

A practical guide to Azure DevOps — Microsoft's integrated platform spanning source control, CI/CD pipelines, work item tracking, package management, and test planning, covering each service, YAML pipeline authoring, release strategies, and how it compares to GitHub Actions.

Introduction
The Five Services
Azure Repos
Azure Pipelines: YAML Fundamentals
A Complete .NET CI/CD Pipeline
Variables, Variable Groups, and Secrets
Multi-Stage Pipelines and Environments
Templates and Reusability
Azure Boards
Azure Artifacts
Azure Test Plans
Self-Hosted Agents
Azure DevOps vs. GitHub Actions/GitHub
Quick Reference Table
Conclusion

Introduction

Azure DevOps is Microsoft's integrated suite for the full software delivery lifecycle — source control, build/release pipelines, work tracking, package feeds, and manual/exploratory test management, all under one platform with a shared permission model and a long history predating (and still commonly used alongside or instead of) GitHub Actions. Organizations that adopted it in its earlier incarnation as Team Foundation Server (TFS) often still run substantial parts of their delivery pipeline on it today, and it remains a common choice for enterprises wanting an integrated, Microsoft-native alternative that combines project management (Boards) with CI/CD in a single product.

trigger:
  branches:
    include: [main]

pool:
  vmImage: 'ubuntu-latest'

steps:
  - task: UseDotNet@2
    inputs:
      version: '9.0.x'
  - script: dotnet restore
  - script: dotnet build --configuration Release
  - script: dotnet test --configuration Release

That YAML pipeline definition — conceptually similar to a GitHub Actions workflow, but with Azure DevOps' own task-based syntax — is the modern, version-controlled way to define builds and releases in Azure DevOps, replacing the classic drag-and-drop pipeline editor that was the platform's original interface.

1. The Five Services

Azure DevOps is organized into five distinct but interconnected services, each independently usable:

Service	Purpose
Azure Repos	Git (or legacy TFVC) source control repositories
Azure Pipelines	CI/CD build and release automation
Azure Boards	Work item tracking — backlogs, sprints, Kanban boards
Azure Artifacts	Package feeds (NuGet, npm, Maven, Python, universal packages)
Azure Test Plans	Manual and exploratory test case management

A team can use all five together as a unified toolchain, or use only the pieces they need — a common pattern is using GitHub for source control while still using Azure Pipelines for CI/CD (and vice versa), since Azure Pipelines can build from a GitHub repository directly, and Azure Boards work items can link to GitHub commits and pull requests.

2. Azure Repos

Git repositories with familiar workflows

Azure Repos provides standard Git hosting — branches, pull requests, branch policies — functionally similar to GitHub or GitLab's core repository features, accessible via any Git client or IDE with Azure DevOps integration (Visual Studio has particularly deep native support).

Branch policies

Branch policies for `main`:
  ✓ Require a minimum number of reviewers (e.g., 2)
  ✓ Check for linked work items
  ✓ Require successful build validation before completing a PR
  ✓ Require comment resolution before merging

Branch policies enforce quality gates directly at the branch level — a pull request targeting a protected branch can be configured to require passing builds, a minimum reviewer count, and linked work items before it's allowed to merge, similar in spirit to GitHub's branch protection rules, with Azure DevOps' own particular set of configurable checks.

Pull requests

PR #142: Add discount calculation to checkout
  Linked work item: #4521 (User Story: Support percentage discounts)
  Reviewers: 2 required, 1 approved
  Build validation: ✓ passed
  Status: Ready to complete

Azure Repos pull requests support inline comments, required reviewers, automatic build validation triggers, and (distinctively) tight integration with Azure Boards work items — a PR can be directly linked to the user story or bug it addresses, and completing the PR can automatically transition that work item's state.

3. Azure Pipelines: YAML Fundamentals

Core structure

trigger:
  branches:
    include: [main, develop]

pr:
  branches:
    include: [main]

pool:
  vmImage: 'ubuntu-latest'

variables:
  buildConfiguration: 'Release'

steps:
  - task: UseDotNet@2
    displayName: 'Install .NET SDK'
    inputs:
      version: '9.0.x'

  - script: dotnet restore
    displayName: 'Restore dependencies'

  - script: dotnet build --configuration $(buildConfiguration)
    displayName: 'Build'

  - script: dotnet test --configuration $(buildConfiguration) --logger trx --results-directory $(Agent.TempDirectory)
    displayName: 'Run tests'

  - task: PublishTestResults@2
    inputs:
      testResultsFormat: 'VSTest'
      testResultsFiles: '**/*.trx'

trigger — defines which branch pushes trigger a CI run (conceptually equivalent to GitHub Actions' on: push).
pr — defines pull-request-triggered validation, separate from the CI trigger.
pool — specifies which agent pool/VM image runs the pipeline (Microsoft-hosted or self-hosted, see Section 11).
task — a reusable, versioned unit of pipeline logic (like UseDotNet@2), analogous to a GitHub Action.
script — a raw shell command, the simplest way to run something without a dedicated task.

Tasks vs. scripts

- task: DotNetCoreCLI@2
  displayName: 'Build with the DotNetCoreCLI task'
  inputs:
    command: 'build'
    arguments: '--configuration Release'

- script: dotnet build --configuration Release
  displayName: 'Or just run it directly as a script'

Both approaches are common and valid — dedicated tasks (like DotNetCoreCLI@2) often provide richer input validation, cross-platform handling, and structured logging integration than an equivalent raw script, but a plain script step is simpler and more transparent for straightforward commands, mirroring the same "official action vs. raw shell command" choice available in GitHub Actions.

4. A Complete .NET CI/CD Pipeline

trigger:
  branches:
    include: [main]

pool:
  vmImage: 'ubuntu-latest'

variables:
  buildConfiguration: 'Release'

stages:
  - stage: Build
    jobs:
      - job: BuildAndTest
        steps:
          - task: UseDotNet@2
            inputs:
              version: '9.0.x'

          - script: dotnet restore
            displayName: 'Restore'

          - script: dotnet build --configuration $(buildConfiguration) --no-restore
            displayName: 'Build'

          - script: dotnet test --configuration $(buildConfiguration) --no-build --logger trx
            displayName: 'Test'

          - task: PublishTestResults@2
            condition: always()
            inputs:
              testResultsFormat: 'VSTest'
              testResultsFiles: '**/*.trx'

          - script: dotnet publish -c $(buildConfiguration) -o $(Build.ArtifactStagingDirectory)
            displayName: 'Publish'

          - task: PublishBuildArtifacts@1
            inputs:
              pathToPublish: '$(Build.ArtifactStagingDirectory)'
              artifactName: 'drop'

  - stage: DeployStaging
    dependsOn: Build
    condition: succeeded()
    jobs:
      - deployment: DeployToStaging
        environment: 'staging'
        strategy:
          runOnce:
            deploy:
              steps:
                - download: current
                  artifact: drop
                - task: AzureWebApp@1
                  inputs:
                    azureSubscription: 'my-service-connection'
                    appName: 'my-api-staging'
                    package: '$(Pipeline.Workspace)/drop/**/*.zip'

This mirrors the same build-then-deploy shape covered in this series' GitHub Actions guide: a Build stage that compiles, tests, and publishes an artifact, followed by a DeployStaging stage that only runs if Build succeeds, deploying the published artifact to an Azure Web App via a deployment job targeting a named environment (Section 6).

5. Variables, Variable Groups, and Secrets

Pipeline variables

variables:
  buildConfiguration: 'Release'
  dotnetVersion: '9.0.x'

- script: echo "Building with $(buildConfiguration) configuration"

Variables defined directly in the YAML are the simplest form — referenced with $(variableName) syntax throughout the pipeline.

Variable groups: shared, centrally-managed configuration

variables:
  - group: 'production-config'

A variable group (managed under Pipelines → Library) centralizes variables shared across multiple pipelines — a connection string, a service URL, feature flag defaults — so they're maintained in one place rather than duplicated across every pipeline file that needs them, and can be scoped with their own permissions independent of the pipeline YAML itself.

Secret variables

variables:
  - group: 'production-secrets'   # variables marked as "secret" in the Library UI are automatically masked in logs

Secrets are marked as such in the Azure DevOps UI (not stored in plain text in the YAML file itself) — their values are automatically redacted from pipeline logs, similar to GitHub Actions' secret masking behavior, and can be sourced from Azure DevOps' own encrypted storage or linked directly to an Azure Key Vault for centralized secret management across both the pipeline and the applications it deploys.

Linking directly to Azure Key Vault

- task: AzureKeyVault@2
  inputs:
    azureSubscription: 'my-service-connection'
    KeyVaultName: 'my-keyvault'
    SecretsFilter: '*'

This task pulls secrets directly from an Azure Key Vault at pipeline run-time, making them available as pipeline variables for that run — avoiding duplicating secret values between Key Vault (where the application likely also sources them) and Azure DevOps' own variable storage.

6. Multi-Stage Pipelines and Environments

Stages: sequencing build and multiple deployment targets

stages:
  - stage: Build
    jobs: [ /* ... */ ]

  - stage: DeployStaging
    dependsOn: Build
    jobs: [ /* ... */ ]

  - stage: DeployProduction
    dependsOn: DeployStaging
    condition: succeeded()
    jobs: [ /* ... */ ]

Stages are Azure Pipelines' top-level sequencing construct — conceptually similar to a chain of jobs across separate GitHub Actions workflows, but expressed within a single pipeline file, with dependsOn controlling execution order and condition controlling whether a stage proceeds.

Environments and approval gates

- stage: DeployProduction
  jobs:
    - deployment: DeployToProduction
      environment: 'production'   # references an Environment configured with its own approval checks
      strategy:
        runOnce:
          deploy:
            steps:
              - task: AzureWebApp@1
                inputs:
                  appName: 'my-api-prod'

An Azure DevOps Environment (configured under Pipelines → Environments) can require manual approval from designated approvers, restrict which branches may deploy to it, and enforce business-hours deployment windows — directly analogous to GitHub's environment protection rules covered in this series' GitHub Actions guide, giving a genuine human gate on production deployments without a separate external approval tool.

Deployment strategies

strategy:
  runOnce:
    deploy:
      steps: [ /* ... */ ]

# or, for a more gradual rollout:
strategy:
  canary:
    increments: [10, 20, 70]
    deploy:
      steps: [ /* ... */ ]

Azure Pipelines' deployment jobs support built-in strategies beyond a simple runOnce deploy — canary deployments (ramping traffic to the new version in increments) and rolling deployments are available as first-class YAML constructs, rather than needing to hand-roll the incremental rollout logic yourself.

7. Templates and Reusability

Step templates

# templates/build-steps.yml
parameters:
  - name: dotnetVersion
    type: string
    default: '9.0.x'

steps:
  - task: UseDotNet@2
    inputs:
      version: ${{ parameters.dotnetVersion }}
  - script: dotnet restore
  - script: dotnet build --configuration Release

# azure-pipelines.yml
steps:
  - template: templates/build-steps.yml
    parameters:
      dotnetVersion: '9.0.x'

Templates let you extract a reusable sequence of steps (or entire jobs/stages) into a separate file, parameterized and referenced from multiple pipeline definitions — the direct equivalent of GitHub Actions' composite actions and reusable workflows, serving the same goal of avoiding copy-pasted, slowly-diverging pipeline logic across many repositories.

Extending a base template

# templates/base-pipeline.yml
parameters:
  - name: environment
    type: string

jobs:
  - deployment: Deploy
    environment: ${{ parameters.environment }}
    strategy:
      runOnce:
        deploy:
          steps:
            - script: echo "Deploying to ${{ parameters.environment }}"

# azure-pipelines.yml
extends:
  template: templates/base-pipeline.yml
  parameters:
    environment: 'production'

extends goes a step further than a plain template reference, letting an organization define a mandatory base pipeline structure (often used to enforce required security scanning steps or compliance checks across every team's pipeline) that individual repositories customize only within the parameters the base template explicitly allows — a governance mechanism GitHub Actions' reusable workflows don't have a direct equivalent for.

8. Azure Boards

Work item types and hierarchy

Epic: Q3 Checkout Redesign
  └── Feature: Support percentage-based discounts
        └── User Story: As a customer, I can apply a discount code at checkout
              └── Task: Implement discount calculation logic
              └── Task: Add discount code input field to checkout UI
              └── Bug: Discount not applied when quantity > 10

Azure Boards organizes work into a configurable hierarchy — typically Epics, Features, User Stories/Product Backlog Items, Tasks, and Bugs — supporting both Scrum and Kanban-style process templates (Basic, Agile, Scrum, CMMI), selectable per project based on the team's preferred methodology.

Backlogs and sprints

Sprint 24 (Jul 14 - Jul 28)
  Committed: 32 story points
  ├── #4521 - Support percentage discounts (5 pts) - In Progress
  ├── #4530 - Fix checkout validation bug (2 pts) - Done
  └── #4535 - Add discount analytics dashboard (8 pts) - To Do

Sprints (iterations) organize work into time-boxed periods with capacity planning, burndown charts, and velocity tracking — the core Scrum ceremony support that's historically been one of Azure Boards' strongest differentiators against a pure CI/CD-focused tool.

Linking work items to code

Commit message: "Fix discount calculation #4521"

Referencing a work item ID in a commit message, branch name, or pull request automatically creates a traceable link — from a work item, you can see every commit, branch, build, and pull request associated with it, giving genuine end-to-end traceability from a business requirement through to the exact code and deployment that fulfilled it, a workflow that requires more manual stitching-together (via GitHub Issues + separate linking conventions) in a pure GitHub-based setup.

Kanban boards

To Do | In Progress | Code Review | Testing | Done
 #4535 | #4521        | #4530        |          | #4519

A visual, drag-and-drop board view of the current sprint or backlog, with configurable columns matching a team's actual workflow stages — functionally similar to a GitHub Projects board, with deeper native integration into the rest of Azure Boards' work item hierarchy and reporting.

9. Azure Artifacts

# Publishing a NuGet package to an Azure Artifacts feed
dotnet nuget push MyLibrary.1.0.0.nupkg --source "https://pkgs.dev.azure.com/myorg/_packaging/myfeed/nuget/v3/index.json" --api-key az

<!-- Consuming from the feed in another project -->
<PackageSource key="myfeed" value="https://pkgs.dev.azure.com/myorg/_packaging/myfeed/nuget/v3/index.json" />

Azure Artifacts hosts private package feeds — NuGet, npm, Maven, Python (pip), and a generic "universal packages" format — letting an organization publish and consume internal packages (a shared internal library, say) without needing a separate package registry product. Feeds support upstream sources, letting a private feed also proxy and cache packages from the public NuGet.org/npmjs.com registries, giving a single feed URL that resolves both internal and public packages, with the added benefit of caching public packages against upstream outages or a removed/yanked public package version.

10. Azure Test Plans

Test Suite: Checkout Flow
  Test Case #201: Apply valid discount code
    Steps:
      1. Add item to cart → Expected: Item appears in cart
      2. Enter valid discount code → Expected: Discount applied, total updated
      3. Complete checkout → Expected: Order confirmed with discounted total
    Status: Passed (last run: 2026-07-15)

  Test Case #202: Apply expired discount code
    Status: Failed — bug #4530 filed

Azure Test Plans manages structured manual and exploratory test cases — distinct from automated tests (which live in your actual codebase and run via Azure Pipelines) — giving QA teams a structured way to define test steps, track pass/fail status per test run, and automatically file a linked bug when a manual test fails. This is a genuinely distinctive piece of the Azure DevOps suite; GitHub's ecosystem has no direct first-party equivalent, relying instead on third-party test management tools or informal tracking via Issues for manual QA processes.

Linking automated test results

- task: PublishTestResults@2
  inputs:
    testResultsFormat: 'VSTest'
    testResultsFiles: '**/*.trx'

Automated test results published from a pipeline (as shown in Section 4) surface in Azure DevOps' test reporting alongside manual Test Plans results, giving one combined view of both automated and manual test coverage and outcomes for a given build.

11. Self-Hosted Agents

Why self-host

pool:
  name: 'MySelfHostedPool'

Microsoft-hosted agents (the default vmImage-based pool) are convenient and require no maintenance, but come with constraints: limited build minutes on free tiers, no persistent state between runs (every job starts from a completely fresh VM), and no access to resources behind a private network without additional configuration. Self-hosted agents — installed on your own VMs, on-prem servers, or containers — remove these constraints, at the cost of you now owning the agent's maintenance, patching, and scaling.

Setting up a self-hosted agent

./config.sh --url https://dev.azure.com/myorg --auth pat --token $PAT --pool MySelfHostedPool --agent myAgent1
./run.sh

A self-hosted agent registers itself with a specific agent pool, and pipelines that specify that pool's name will be scheduled onto it — common reasons to self-host include needing network access to an internal, non-internet-facing resource (an on-prem database, an internal API), needing specialized hardware (GPU-equipped build machines), or wanting persistent caching between runs without re-downloading dependencies every time (similar to the caching motivations covered in this series' GitHub Actions guide, but achieved here through the agent's own persistent filesystem rather than an explicit cache action).

12. Azure DevOps vs. GitHub Actions/GitHub

Aspect	Azure DevOps	GitHub Actions + GitHub
Source control	Azure Repos (Git or legacy TFVC)	GitHub repositories
CI/CD	Azure Pipelines (YAML or classic UI)	GitHub Actions (YAML only)
Work tracking	Azure Boards — deep Scrum/Kanban support, work item hierarchy	GitHub Issues + Projects — lighter-weight, less formal process support
Package feeds	Azure Artifacts (NuGet, npm, Maven, Python, universal)	GitHub Packages (similar format support)
Manual test management	Azure Test Plans — dedicated, structured tool	No first-party equivalent; typically a third-party tool
Marketplace/ecosystem	Smaller marketplace of tasks	Much larger community Marketplace of Actions
Governance (`extends` templates)	Strong — mandatory base templates enforceable org-wide	Reusable workflows exist, but less of a hard "mandatory extension" mechanism
Typical organization fit	Enterprises wanting integrated project management + CI/CD in one product, often with existing Microsoft/Azure DevOps investment	Teams prioritizing open-source ecosystem size, GitHub-native workflows, broader community tooling

They aren't mutually exclusive

A common, entirely supported setup: source code on GitHub, with Azure Pipelines still handling CI/CD (Azure Pipelines can build directly from a GitHub repository via a service connection), especially in organizations mid-migration between the two ecosystems, or specifically wanting Azure Pipelines' deployment strategy features or self-hosted agent flexibility while keeping GitHub's broader community/ecosystem benefits for the code itself.

Practical guidance

Already invested in Azure DevOps, need deep Scrum/Kanban work tracking integrated with CI/CD, or need Azure Test Plans' manual test management? → Azure DevOps remains a strong, cohesive choice.
Prioritizing the largest possible community/Marketplace ecosystem, already living in GitHub for source control, or building open-source software? → GitHub Actions is generally the more natural fit.
Neither choice locks you out of the other — many organizations mix and match based on which specific service (Repos vs. Pipelines vs. Boards) best fits a given team's actual need.

Quick Reference Table

Concept	Purpose
Azure Repos	Git-based source control with branch policies
Azure Pipelines	YAML-based CI/CD, stages/jobs/steps, deployment jobs
Variable groups	Centrally-managed, shared pipeline configuration
Environments	Approval gates and deployment history per target
Templates / `extends`	Reusable and (optionally) organization-mandated pipeline structure
Azure Boards	Work item hierarchy, sprints, Kanban boards
Azure Artifacts	Private NuGet/npm/Maven/Python package feeds with upstream proxying
Azure Test Plans	Structured manual/exploratory test case management
Self-hosted agents	Custom build infrastructure for network access, hardware, or persistent caching needs

Conclusion

Azure DevOps' distinguishing strength is breadth under one roof — source control, CI/CD, structured work tracking, package feeds, and manual test management, all sharing a permission model and cross-linking naturally (a commit referencing a work item, a pipeline run surfacing both automated and manual test results together). For organizations that want that level of integrated project management alongside their build and release automation — particularly ones already invested in the Microsoft ecosystem — it remains a genuinely strong, mature choice, with YAML pipelines today offering the same version-controlled, reviewable pipeline-as-code approach that GitHub Actions popularized.

The right choice between Azure DevOps and a GitHub-centric toolchain usually comes down to which surrounding ecosystem and process model fits a team better, not a meaningful gap in core CI/CD capability — both platforms can build, test, and deploy a .NET application reliably, and plenty of real organizations use pieces of both rather than treating it as an exclusive either/or decision.

Found this useful? Feel free to star the repo, open an issue with corrections, or share the pipeline template that saved your team from a dozen copy-pasted YAML files.

GitHub Actions: CI/CD Automation Built into GitHub

Rhuturaj Takle — Fri, 24 Jul 2026 15:56:56 +0000

GitHub Actions: CI/CD Automation Built into GitHub

A practical guide to GitHub Actions — the automation platform built directly into GitHub for CI/CD pipelines, covering workflow syntax, triggers, jobs and runners, secrets management, reusable workflows, matrix builds, and deployment patterns for .NET applications.

Introduction
Core Concepts
Workflow Syntax
Triggers
Jobs, Dependencies, and Runners
A Complete .NET CI Pipeline
Secrets and Variables
Matrix Builds
Caching Dependencies
Reusable Workflows and Composite Actions
Deployment Patterns
Environments and Approval Gates
Security Considerations
Quick Reference Table
Conclusion

Introduction

GitHub Actions is GitHub's built-in automation platform — workflows defined as YAML files living in your repository (.github/workflows/) that run in response to events: a push, a pull request, a schedule, or a manual trigger. It covers everything from a simple "run the tests on every PR" pipeline to a full multi-stage build, test, and deployment pipeline spanning multiple cloud providers.

name: CI
on: [push, pull_request]

jobs:
  build-and-test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-dotnet@v4
        with:
          dotnet-version: '9.0.x'
      - run: dotnet restore
      - run: dotnet build --no-restore
      - run: dotnet test --no-build

That's a complete, working CI pipeline — checkout the code, install .NET, restore, build, and test — triggered automatically on every push and pull request, with zero infrastructure to provision or maintain.

1. Core Concepts

Workflows, jobs, and steps

A workflow is a YAML file defining one or more automated processes, triggered by events.
A job is a set of steps that run together on the same runner (virtual machine or container).
A step is an individual task — running a shell command, or invoking a reusable action.
An action is a reusable, packaged unit of automation (like actions/checkout or actions/setup-dotnet), either from GitHub itself, the community (via the GitHub Marketplace), or your own repository.

.github/workflows/ci.yml   ← one workflow file
  └── job: build-and-test
        ├── step: checkout code
        ├── step: setup .NET
        ├── step: restore
        ├── step: build
        └── step: test

Runners

A runner is the machine (virtual or physical) that actually executes a job's steps. GitHub provides GitHub-hosted runners (ubuntu-latest, windows-latest, macos-latest) — fresh VMs provisioned per job, pre-loaded with common tooling and torn down afterward — or you can register self-hosted runners on your own infrastructure for specialized hardware, network access requirements, or cost control at high volume.

2. Workflow Syntax

Anatomy of a workflow file

name: CI Pipeline               # display name shown in the GitHub UI

on:                              # what triggers this workflow
  push:
    branches: [main]
  pull_request:
    branches: [main]

env:                              # environment variables available to every job
  DOTNET_VERSION: '9.0.x'

jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Setup .NET
        uses: actions/setup-dotnet@v4
        with:
          dotnet-version: ${{ env.DOTNET_VERSION }}

      - name: Restore dependencies
        run: dotnet restore

      - name: Build
        run: dotnet build --no-restore --configuration Release

      - name: Test
        run: dotnet test --no-build --configuration Release --logger trx

Expressions and contexts

- name: Print branch name
  run: echo "Building branch ${{ github.ref_name }}"

- name: Conditional step
  if: github.event_name == 'pull_request'
  run: echo "This only runs for pull requests"

GitHub Actions exposes several built-in contexts (github, env, secrets, matrix, steps, job, needs) accessible via ${{ }} expression syntax — github.ref_name (the branch/tag name), github.event_name (what triggered the run), github.sha (the commit SHA), and many more, letting workflow behavior adapt dynamically to the specific event that triggered it.

3. Triggers

Common event triggers

on:
  push:
    branches: [main, develop]
    paths: ['src/**', '!src/**/*.md']   # only trigger if these paths changed

  pull_request:
    branches: [main]

  schedule:
    - cron: '0 2 * * *'   # daily at 2:00 AM UTC

  workflow_dispatch:        # manual trigger, with optional inputs
    inputs:
      environment:
        description: 'Target environment'
        required: true
        type: choice
        options: [staging, production]

  release:
    types: [published]

push/pull_request — the most common triggers for CI, optionally scoped to specific branches or file paths (paths filtering avoids re-running a full pipeline for a documentation-only change, for instance).
schedule — cron-based triggers for periodic tasks (nightly builds, scheduled cleanup jobs, dependency update checks).
workflow_dispatch — enables a manual "Run workflow" button in the GitHub UI, optionally with typed inputs, useful for on-demand deployments or maintenance tasks.
release — triggers when a GitHub Release is published, a natural hook for a production deployment pipeline.

Reacting to other workflows

on:
  workflow_run:
    workflows: ["CI Pipeline"]
    types: [completed]
    branches: [main]

workflow_run lets one workflow trigger based on another workflow's completion — a common pattern for separating "build and test" from "deploy," so deployment only proceeds after CI has genuinely succeeded on the exact commit being deployed.

4. Jobs, Dependencies, and Runners

Running jobs in parallel (the default)

jobs:
  lint:
    runs-on: ubuntu-latest
    steps: [ /* ... */ ]

  test:
    runs-on: ubuntu-latest
    steps: [ /* ... */ ]

Jobs within a workflow run in parallel by default, each on its own fresh runner — lint and test above would execute simultaneously, not sequentially, unless you explicitly declare a dependency between them.

Sequencing jobs with `needs`

jobs:
  build:
    runs-on: ubuntu-latest
    steps: [ /* ... */ ]

  deploy:
    needs: build
    runs-on: ubuntu-latest
    steps: [ /* ... */ ]

needs establishes an explicit dependency — deploy waits for build to complete successfully before starting, and is skipped entirely if build fails, which is exactly the behavior you want for a pipeline where deployment should never proceed against a build that didn't pass.

Passing data between jobs

jobs:
  build:
    runs-on: ubuntu-latest
    outputs:
      version: ${{ steps.get_version.outputs.version }}
    steps:
      - id: get_version
        run: echo "version=1.2.3" >> "$GITHUB_OUTPUT"

  deploy:
    needs: build
    runs-on: ubuntu-latest
    steps:
      - run: echo "Deploying version ${{ needs.build.outputs.version }}"

Since each job runs on its own isolated runner (no shared filesystem or memory between them), passing data between jobs requires explicit outputs — a common pattern for computing a version number, build artifact path, or other value in one job and consuming it in a dependent job.

Choosing a runner

jobs:
  build-windows:
    runs-on: windows-latest   # for .NET Framework or Windows-specific builds
  build-linux:
    runs-on: ubuntu-latest    # cheaper, faster, and the default for most .NET Core/.NET 5+ workloads
  build-mac:
    runs-on: macos-latest     # for iOS/macOS-targeted builds (e.g., MAUI)

For most modern .NET applications (.NET 5 and later, cross-platform by default), ubuntu-latest is both the cheapest and fastest option among GitHub-hosted runners — reserve windows-latest for workloads with a genuine Windows-specific dependency, and macos-latest specifically for builds that need to produce iOS/macOS outputs.

5. A Complete .NET CI Pipeline

name: .NET CI

on:
  push:
    branches: [main]
  pull_request:
    branches: [main]

jobs:
  build-and-test:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout
        uses: actions/checkout@v4

      - name: Setup .NET
        uses: actions/setup-dotnet@v4
        with:
          dotnet-version: '9.0.x'

      - name: Restore dependencies
        run: dotnet restore

      - name: Build
        run: dotnet build --no-restore --configuration Release

      - name: Run unit tests
        run: dotnet test --no-build --configuration Release --logger trx --results-directory TestResults

      - name: Publish test results
        uses: dorny/test-reporter@v1
        if: always()
        with:
          name: Test Results
          path: 'TestResults/*.trx'
          reporter: dotnet-trx

      - name: Publish
        if: github.ref == 'refs/heads/main'
        run: dotnet publish -c Release -o ./publish

      - name: Upload build artifact
        if: github.ref == 'refs/heads/main'
        uses: actions/upload-artifact@v4
        with:
          name: app-package
          path: ./publish

This pipeline restores, builds, and tests on every push and pull request, publishes test results in a readable format even if tests fail (if: always() ensures this step runs regardless of prior step outcomes), and — only on the main branch — publishes the application and uploads it as a build artifact ready for a subsequent deployment job to pick up.

6. Secrets and Variables

Repository and organization secrets

steps:
  - name: Deploy to Azure
    run: az webapp deploy --name my-api --resource-group my-rg
    env:
      AZURE_CLIENT_SECRET: ${{ secrets.AZURE_CLIENT_SECRET }}

Secrets (configured under repository or organization Settings → Secrets and variables → Actions) are encrypted at rest, never displayed in logs (GitHub automatically redacts any secret value that appears in output), and accessed via the secrets context — never hardcode credentials directly in a workflow file, since that file is plain text, version-controlled, and often visible to anyone with read access to the repository.

Variables (non-secret configuration)

env:
  ASPNETCORE_ENVIRONMENT: ${{ vars.ENVIRONMENT_NAME }}

Variables (as opposed to secrets) are for non-sensitive configuration values that still benefit from being centrally managed rather than hardcoded in every workflow file — an environment name, a region, a feature flag default.

OIDC: avoiding long-lived cloud credentials entirely

permissions:
  id-token: write   # required for OIDC
  contents: read

steps:
  - name: Azure Login via OIDC
    uses: azure/login@v2
    with:
      client-id: ${{ secrets.AZURE_CLIENT_ID }}
      tenant-id: ${{ secrets.AZURE_TENANT_ID }}
      subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

Rather than storing a long-lived cloud service principal secret, GitHub Actions supports OpenID Connect (OIDC) federation with Azure, AWS, and GCP — the workflow requests a short-lived token that the cloud provider trusts based on a pre-configured federated identity relationship (tied to the specific repository/branch), eliminating the need to store, rotate, or risk leaking a long-lived cloud credential at all. This is the currently recommended approach for cloud deployments from GitHub Actions, a meaningful security improvement over storing a static secret.

7. Matrix Builds

jobs:
  test:
    runs-on: ${{ matrix.os }}
    strategy:
      matrix:
        os: [ubuntu-latest, windows-latest]
        dotnet-version: ['8.0.x', '9.0.x']
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-dotnet@v4
        with:
          dotnet-version: ${{ matrix.dotnet-version }}
      - run: dotnet test

A matrix runs the same job across every combination of the specified dimensions — the example above runs tests across 4 combinations (2 operating systems × 2 .NET versions) automatically, without duplicating the job definition four times. This is the standard pattern for confirming a library or application behaves correctly across multiple supported OS/runtime version combinations.

Excluding specific combinations

strategy:
  matrix:
    os: [ubuntu-latest, windows-latest, macos-latest]
    dotnet-version: ['8.0.x', '9.0.x']
    exclude:
      - os: macos-latest
        dotnet-version: '8.0.x'

exclude (and its counterpart include, for adding specific extra combinations beyond the full cross-product) lets you skip combinations that don't need testing, keeping matrix size and CI cost reasonable.

Fail-fast behavior

strategy:
  fail-fast: false   # let all matrix jobs run to completion, even if one fails
  matrix:
    os: [ubuntu-latest, windows-latest]

By default, a matrix cancels all remaining in-progress jobs the moment any one combination fails — fail-fast: false disables this, useful when you want a complete picture of exactly which combinations pass and fail, rather than stopping at the first failure.

8. Caching Dependencies

- name: Cache NuGet packages
  uses: actions/cache@v4
  with:
    path: ~/.nuget/packages
    key: ${{ runner.os }}-nuget-${{ hashFiles('**/*.csproj') }}
    restore-keys: |
      ${{ runner.os }}-nuget-

Since GitHub-hosted runners are fresh, ephemeral VMs for every job, dependencies (NuGet packages, npm modules, Docker layers) get re-downloaded from scratch on every run unless explicitly cached. actions/cache stores a directory keyed by a hash of relevant lock/project files — if the key matches an existing cache entry (meaning dependencies haven't changed), the cache is restored instead of re-downloading everything, often producing a substantial speedup for dependency-heavy builds.

Built-in caching via `setup-dotnet`

- uses: actions/setup-dotnet@v4
  with:
    dotnet-version: '9.0.x'
    cache: true
    cache-dependency-path: '**/packages.lock.json'

Recent versions of setup-dotnet support built-in dependency caching without needing a separate, manually configured actions/cache step — simpler to set up for the common case, though it requires a packages.lock.json file (generated via dotnet restore --use-lock-file or enabled via a project setting) to compute a reliable cache key.

9. Reusable Workflows and Composite Actions

Reusable workflows: share an entire pipeline

# .github/workflows/reusable-build.yml
name: Reusable Build
on:
  workflow_call:
    inputs:
      dotnet-version:
        required: true
        type: string
    secrets:
      nuget-api-key:
        required: false

jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-dotnet@v4
        with:
          dotnet-version: ${{ inputs.dotnet-version }}
      - run: dotnet build

# .github/workflows/ci.yml — calling the reusable workflow
jobs:
  build:
    uses: ./.github/workflows/reusable-build.yml
    with:
      dotnet-version: '9.0.x'
    secrets:
      nuget-api-key: ${{ secrets.NUGET_API_KEY }}

workflow_call lets one workflow file be invoked by others, with explicit typed inputs and secrets — the standard mechanism for sharing an entire multi-job pipeline definition across many repositories in an organization, avoiding copy-pasted, slowly-diverging pipeline YAML in every repo.

Composite actions: share a sequence of steps

# .github/actions/setup-and-restore/action.yml
name: 'Setup .NET and Restore'
runs:
  using: composite
  steps:
    - uses: actions/setup-dotnet@v4
      with:
        dotnet-version: '9.0.x'
    - run: dotnet restore
      shell: bash

# using it in a workflow
steps:
  - uses: actions/checkout@v4
  - uses: ./.github/actions/setup-and-restore

Composite actions bundle a reusable sequence of steps (rather than an entire job/workflow) into a single referenceable action — a good fit for a common setup pattern (like "checkout, setup .NET, restore") repeated across many jobs or workflows within the same repository.

10. Deployment Patterns

Deploying to Azure App Service

jobs:
  deploy:
    needs: build
    runs-on: ubuntu-latest
    steps:
      - uses: actions/download-artifact@v4
        with:
          name: app-package
          path: ./publish

      - uses: azure/login@v2
        with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

      - uses: azure/webapps-deploy@v3
        with:
          app-name: my-api
          package: ./publish

Deploying to AWS ECS

- uses: aws-actions/configure-aws-credentials@v4
  with:
    role-to-assume: arn:aws:iam::123456789:role/github-actions-deploy
    aws-region: us-east-1

- name: Build and push image
  run: |
    docker build -t product-api .
    aws ecr get-login-password | docker login --username AWS --password-stdin ${{ secrets.ECR_REGISTRY }}
    docker tag product-api:latest ${{ secrets.ECR_REGISTRY }}/product-api:${{ github.sha }}
    docker push ${{ secrets.ECR_REGISTRY }}/product-api:${{ github.sha }}

- name: Update ECS service
  run: aws ecs update-service --cluster my-cluster --service product-api-service --force-new-deployment

Deploying to a Kubernetes cluster (AKS)

- uses: azure/aks-set-context@v4
  with:
    resource-group: my-rg
    cluster-name: my-cluster

- run: kubectl set image deployment/product-api product-api=myregistry.azurecr.io/product-api:${{ github.sha }}

These follow directly from the deployment mechanics covered in this series' Azure Compute and AWS Compute guides — GitHub Actions is the automation layer triggering the same az webapp deploy, ECS service update, or kubectl commands you'd otherwise run by hand, but consistently, on every merge, without manual intervention.

11. Environments and Approval Gates

jobs:
  deploy-production:
    needs: build
    runs-on: ubuntu-latest
    environment:
      name: production
      url: https://myapp.com
    steps:
      - run: echo "Deploying to production"

A GitHub Environment (configured under repository Settings → Environments) can require manual approval from designated reviewers before a job targeting it proceeds, restrict which branches can deploy to it, and hold environment-specific secrets scoped only to that environment — a production environment might require a lead engineer's explicit approval, while a staging environment deploys automatically with no gate at all.

environment: production

This single line is what triggers GitHub to check the environment's configured protection rules — if approval is required, the job pauses at that point and waits for a reviewer to approve it directly in the GitHub UI before continuing, giving a genuine human gate on production deployments without needing a separate external tool.

12. Security Considerations

Pin actions to a specific version, ideally a commit SHA

# ✅ Safer: pinned to a specific commit SHA
- uses: actions/checkout@8f4b7f84864484a7bf31766abe9204da3cbe65b3

# Less safe: a mutable tag that could be repointed to different code later
- uses: actions/checkout@v4

Using a floating tag (@v4) trusts that the action's maintainer never republishes malicious code under that same tag — pinning to an exact commit SHA is the most secure option for security-sensitive pipelines, since a SHA reference can't be silently changed after the fact, though it does mean manually updating the SHA to pick up legitimate updates.

Least-privilege `GITHUB_TOKEN` permissions

permissions:
  contents: read
  pull-requests: write

The automatically-provided GITHUB_TOKEN defaults to fairly broad permissions in many repository configurations — explicitly declaring only the specific permissions a workflow actually needs (read-only repo content, write access to pull request comments, but nothing else) limits the blast radius if a workflow or one of its dependencies is ever compromised.

Be cautious with `pull_request_target` and untrusted forks

# pull_request_target runs with the base repo's permissions and secrets,
# even for PRs from forks — dangerous if it also checks out and runs the fork's code
on: pull_request_target

pull_request_target (unlike plain pull_request) runs with access to repository secrets even for pull requests originating from external forks — this is sometimes necessary (e.g., to comment on a PR from a fork), but combining it with checking out and executing the fork's own code is a well-known way to accidentally hand secrets to an untrusted contributor's code; if this combination is genuinely needed, the code that runs with secret access should be reviewed and controlled by the base repository, not blindly pulled from the fork.

Avoid injecting untrusted input directly into `run` commands

# ❌ Vulnerable to script injection via a maliciously crafted PR title
- run: echo "Building PR: ${{ github.event.pull_request.title }}"

# ✅ Safer: pass untrusted values as an environment variable instead
- run: echo "Building PR: $PR_TITLE"
  env:
    PR_TITLE: ${{ github.event.pull_request.title }}

Directly interpolating untrusted, user-controllable values (a PR title, an issue body, a branch name) into a run step's shell command is a genuine script-injection vector — a maliciously crafted PR title containing shell metacharacters could execute arbitrary commands in the runner. Passing the same value through an environment variable instead avoids this, since the shell no longer directly interpolates the untrusted string into the command itself.

Quick Reference Table

Concept	Purpose
Workflow / job / step / action	The nested structure of a GitHub Actions pipeline
`on:` triggers	What starts a workflow run (push, PR, schedule, manual, etc.)
`needs:`	Sequences jobs, waiting on a dependency's success
`runs-on:`	Selects the runner OS/environment for a job
`secrets` context	Encrypted, redacted-in-logs credential access
OIDC federation	Short-lived cloud credentials without storing long-lived secrets
`strategy: matrix`	Runs the same job across multiple parameter combinations
`actions/cache`	Persists dependencies between runs to speed up builds
`workflow_call`	Reusable, shareable entire workflow definitions
Composite action	Reusable, shareable sequence of steps
Environments	Approval gates, branch restrictions, and scoped secrets per deployment target
Pinning to a commit SHA	Protects against a compromised or repointed action tag

Conclusion

GitHub Actions' core value is proximity: your CI/CD pipeline lives in the same repository as your code, versioned and reviewed the same way, with no separate system to provision or maintain, and a vast marketplace of ready-made actions covering nearly every common automation need. From a five-line "build and test on every push" workflow to a full multi-environment deployment pipeline with approval gates and OIDC-federated cloud credentials, the underlying model stays the same: events trigger workflows, workflows run jobs on runners, and jobs execute steps — simple building blocks that compose into pipelines as sophisticated as a project actually needs.

The security practices matter as much as the pipeline mechanics — pin actions to trusted versions, scope GITHUB_TOKEN permissions tightly, prefer OIDC over long-lived cloud secrets, and be deliberate about how untrusted input (PR titles, fork contents) flows through a workflow — since a CI/CD pipeline with broad permissions and access to production secrets is exactly the kind of high-value target worth defending carefully, not an afterthought bolted onto the "just get the build green" goal.

Found this useful? Feel free to star the repo, open an issue with corrections, or share the caching trick that took your build from ten minutes down to two.

Database Migrations: Managing Schema Changes as Version-Controlled Code

Rhuturaj Takle — Thu, 23 Jul 2026 15:03:15 +0000

Database Migrations: Managing Schema Changes as Version-Controlled Code

A practical guide to database migrations — the discipline of evolving a database schema through version-controlled, repeatable scripts rather than manual changes, covering migration tools (EF Core, Flyway, Liquibase, DbUp), safe schema-change patterns, zero-downtime deployments, and rollback strategy.

Introduction
Why Migrations Exist
Migration Tools
Anatomy of a Migration
Safe Schema-Change Patterns
Zero-Downtime Migrations for Live Systems
Data Migrations vs. Schema Migrations
Rollback Strategy
Migrations in CI/CD
Team Workflow and Conflict Resolution
Common Pitfalls
Quick Reference Table
Conclusion

Introduction

A database migration is a version-controlled, ordered script that changes a database schema from one known state to the next — adding a column, creating a table, adding an index, backfilling data. Instead of connecting to production and running an ad-hoc ALTER TABLE by hand, migrations turn schema evolution into the same disciplined, reviewable, repeatable process as application code changes: committed to Git, reviewed in a pull request, and applied consistently across every environment in the same order.

-- V12__add_discount_percent_to_products.sql
ALTER TABLE products ADD COLUMN discount_percent NUMERIC(5,2);

That one file, checked into source control alongside a migration tool that knows it hasn't been applied yet, is the entire idea — the tool tracks which migrations have run, in what order, against which environment, so "what does this database's schema look like right now" is always answerable by reading the migration history rather than inspecting the live database and hoping nothing was changed out-of-band.

1. Why Migrations Exist

The problem with manual schema changes

Manually running ALTER TABLE statements against a database — even carefully — creates problems that compound over time:

No record of what changed and when. Six months later, nobody can say with confidence why a column exists, or whether a specific fix was actually applied to production.
Environment drift. Dev, staging, and production schemas quietly diverge as manual changes get applied inconsistently — a fix made directly in production but never replicated to staging, or vice versa.
No repeatability. Standing up a new environment (a new staging instance, a developer's local database, a disaster-recovery restore) means either restoring from a backup or manually replaying every change anyone can remember making.
No review process. A schema change that would benefit from a second pair of eyes (does this column need a default? will this index lock the table?) often just... happens, with no gate comparable to a pull request review.

What migrations provide

A single source of truth — the sequence of migration files is the schema's history, in order, matching exactly what's been applied to any given environment.
Reproducibility — a fresh database, brought up to date by replaying every migration in order, ends up in exactly the same schema state as any other environment that's done the same.
Reviewability — a migration is a diff, reviewable in a pull request exactly like an application code change, before it's ever run against a real database.
Environment consistency — the same migrations, applied in the same order, keep dev/staging/production from drifting apart silently.
Auditability — a Git history of every schema change, tied to a commit, an author, and (ideally) a linked ticket/PR explaining why.

The core principle: the schema is code

Just as Infrastructure as Code (covered elsewhere in this series) treats infrastructure definitions as version-controlled artifacts rather than manual console clicks, migrations treat the database schema the same way — a change to the schema is a change to the codebase, subject to the same review, testing, and deployment discipline as any other code change.

2. Migration Tools

Different ecosystems and teams reach for different tools, but they share the same underlying model: ordered, tracked, repeatable scripts.

EF Core Migrations (C#/.NET, model-first)

dotnet ef migrations add AddDiscountPercentToProducts
dotnet ef database update

EF Core generates migrations by diffing your current C# model against its record of the last-applied migration — the migration file is auto-generated C# code (translated to SQL at apply-time), tightly coupled to the ORM's own model. Covered in depth in this series' EF Core guide; included here for completeness since it's the most common migration tool for .NET-first teams specifically.

Flyway (polyglot, SQL-first)

sql/
  V1__create_products_table.sql
  V2__add_categories_table.sql
  V3__add_discount_percent_to_products.sql

flyway migrate

Flyway is a widely used, database-and-language-agnostic migration tool — migrations are plain, hand-written SQL files, named with a strict versioned convention (V{version}__{description}.sql), applied in version order and tracked in a flyway_schema_history table it manages in your database. Because migrations are raw SQL, Flyway works identically regardless of which application language/framework sits on top of the database, which makes it a common choice for polyglot organizations or teams that specifically want SQL-first, framework-independent migrations rather than an ORM-generated abstraction.

Liquibase (polyglot, format-flexible)

<changeSet id="3" author="ada">
    <addColumn tableName="products">
        <column name="discount_percent" type="numeric(5,2)"/>
    </addColumn>
</changeSet>

liquibase update

Liquibase is conceptually similar to Flyway but supports defining changes in XML, YAML, JSON, or plain SQL — its changelog format is somewhat more abstracted from raw SQL than Flyway's approach, which can make certain changes more portable across different database engines, at the cost of an extra layer of abstraction over the SQL that ultimately runs.

DbUp (.NET, SQL-first, lightweight)

var upgrader = DeployChanges.To
    .SqlDatabase(connectionString)
    .WithScriptsFromFileSystem("Scripts")
    .LogToConsole()
    .Build();

var result = upgrader.PerformUpgrade();

DbUp is a lightweight .NET library for running versioned SQL scripts against a database — a common choice for .NET teams who want the "plain SQL files, applied in order" model (like Flyway) but as a small embeddable library rather than a separate standalone tool with its own CLI, letting migration execution be triggered directly from a deployment pipeline or even application startup code.

Choosing a tool

Scenario	Reasonable choice
.NET application already using EF Core as its ORM	EF Core Migrations — keeps schema and model changes coupled and generated together
Polyglot organization, or a strong preference for hand-written SQL	Flyway or DbUp
Need for XML/YAML changelog format, or multi-database-engine portability	Liquibase
.NET team wanting SQL-first migrations without EF Core as the ORM	DbUp

None of these are mutually exclusive with the database itself — the tool choice is about how migrations are authored and tracked, not a database-specific decision, and all of the tools above work against SQL Server, PostgreSQL, MySQL, and most other mainstream relational databases.

3. Anatomy of a Migration

The schema history table

Every migration tool creates and manages its own tracking table in the target database:

-- Flyway's schema history table (simplified)
SELECT version, description, installed_on, success FROM flyway_schema_history ORDER BY installed_rank;

-- EF Core's migrations history table
SELECT MigrationId, ProductVersion FROM __EFMigrationsHistory ORDER BY MigrationId;

This table is the tool's source of truth for "what's already been applied here" — on each run, the tool compares the set of migration files it finds against this table and applies only what's missing, in order.

Up and down (forward and reverse)

// EF Core
protected override void Up(MigrationBuilder migrationBuilder) =>
    migrationBuilder.AddColumn<decimal>(name: "DiscountPercent", table: "Products", nullable: true);

protected override void Down(MigrationBuilder migrationBuilder) =>
    migrationBuilder.DropColumn(name: "DiscountPercent", table: "Products");

-- Flyway "undo" migration (a separate, optional file convention)
-- U3__add_discount_percent_to_products.sql
ALTER TABLE products DROP COLUMN discount_percent;

Most tools support (or at least conventionally encourage) pairing every forward migration with a way to reverse it — though as covered in Section 7, "can technically be reversed" and "should actually be reversed in production" are different questions, especially once a migration involves data, not just structure.

Idempotency and repeatable migrations

-- Flyway "repeatable" migration (re-applied whenever its content changes, not just once)
-- R__refresh_product_summary_view.sql
CREATE OR REPLACE VIEW product_summary AS
SELECT category_id, COUNT(*) AS product_count, AVG(price) AS avg_price
FROM products GROUP BY category_id;

Most schema-changing migrations are meant to run exactly once, ever — but some tools support a distinct "repeatable" migration type for things like views, stored procedures, or seed reference data that should be re-applied whenever their content changes, rather than tracked as a one-time historical step.

4. Safe Schema-Change Patterns

Some schema changes are safe to apply directly; others carry real risk to a live, in-use database and deserve more care.

Additive changes: generally safe

ALTER TABLE products ADD COLUMN discount_percent NUMERIC(5,2) NULL;
CREATE INDEX idx_products_category ON products (category_id);

Adding a new, nullable column, or adding a new index, generally doesn't break existing application code that doesn't know about it yet — this is the safest, most common category of schema change, and the reason "always prefer additive, backward-compatible changes" is a recurring theme across this series (it came up for REST API evolution and for Terraform/Bicep configuration changes too — the same underlying principle applies to schema evolution).

Adding a `NOT NULL` column to a table with existing rows: needs care

-- ❌ Fails immediately on a table with existing rows and no default
ALTER TABLE products ADD COLUMN sku VARCHAR(50) NOT NULL;

-- ✅ Safer, three-step approach
ALTER TABLE products ADD COLUMN sku VARCHAR(50) NULL;
UPDATE products SET sku = 'UNKNOWN-' || id WHERE sku IS NULL; -- backfill existing rows
ALTER TABLE products ALTER COLUMN sku SET NOT NULL;

Adding a NOT NULL column directly to a table that already has rows fails outright unless a default is provided (and even with a default, rewriting every existing row can be a slow, locking operation on a large table in some databases) — the safer pattern is add-nullable, backfill, then constrain, as three separate, individually reviewable steps.

Renaming a column: never do it directly in one migration for a live system

-- ❌ Breaks any application code still expecting the old column name, the instant this deploys
ALTER TABLE products RENAME COLUMN name TO product_name;

-- ✅ Expand/contract pattern instead
-- Migration 1: add the new column
ALTER TABLE products ADD COLUMN product_name VARCHAR(200);
UPDATE products SET product_name = name;
-- (application code updated to write to both columns, read from the new one)

-- Migration 2 (later, after all instances are confirmed running the new code): drop the old column
ALTER TABLE products DROP COLUMN name;

A direct rename is a breaking change for any application instance still running the old code that references the old column name — during a rolling deployment, it's entirely normal for old and new application code to run simultaneously for a period, and a hard rename breaks the old instances the moment the migration applies, not when the new code deploys. See Section 5 for this "expand/contract" pattern in full.

Changing a column's type: similarly risky

-- Can silently truncate or fail on existing data depending on the database and the specific type change
ALTER TABLE products ALTER COLUMN price TYPE INTEGER;

Narrowing a type (e.g., DECIMAL to INTEGER, or shrinking a VARCHAR length) risks data loss or outright failure if existing values don't fit — always inspect existing data for compatibility before applying a type-narrowing migration, and consider the same expand/contract approach (add a new correctly-typed column, migrate data, drop the old one) for anything beyond a clearly safe widening change.

Dropping a column or table: the point of no return

ALTER TABLE products DROP COLUMN legacy_field;

Once applied (and once any point-in-time backup window for the pre-drop state has passed), this data is genuinely gone. Treat drops as the highest-risk category of migration — confirm nothing still reads the column/table (including reporting queries, other services, or BI tools that might not be obvious from the application codebase alone), and consider a "deprecate first, drop later" waiting period rather than dropping in the same release that stops using something.

5. Zero-Downtime Migrations for Live Systems

The core challenge: old and new code run simultaneously during deployment

During any rolling deployment (see this series' ASP.NET Core and Azure/AWS compute guides for the deployment-slot and rolling-update mechanics), there's a window where some instances are running the old application code against the database, while others are already running the new code — a schema change needs to be compatible with both versions during that window, not just the new one.

The expand/contract pattern

This is the general-purpose solution to the rename/retype problem from Section 4, and it applies to most breaking schema changes:

Expand — add the new schema element (column, table) alongside the old one, without removing anything. Deploy this migration first, on its own.
Migrate — deploy application code that writes to both the old and new schema elements (or reads from the new one with a fallback to the old), and backfill any existing data into the new shape.
Verify — confirm every application instance is running the dual-write/new-read code, and that data is fully backfilled and consistent.
Contract — once confident nothing still depends on the old schema element, deploy a final migration that removes it.

Migration 1 (expand):    ADD COLUMN product_name
Deploy code v2:          writes to both `name` and `product_name`, reads from `product_name`
Backfill:                UPDATE products SET product_name = name WHERE product_name IS NULL
Verify:                  confirm all traffic is on code v2, data is consistent
Migration 2 (contract):  DROP COLUMN name

This is more steps and more deployments than a single direct rename, but it's the difference between a migration that's safe to apply to a live, continuously-serving production system and one that causes an outage (or subtle data corruption) during the rollout window.

Long-running migrations and locking

-- Can hold a long-lived lock on a large table, blocking reads/writes for the duration
ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending';

Some schema changes — adding a column with a default value to a very large table, rebuilding an index, adding a constraint that requires validating every existing row — can take a long time and, depending on the specific database engine and change, hold locks that block application traffic for the duration. Increasingly, modern versions of PostgreSQL and SQL Server have optimized many of these operations to avoid full-table rewrites/locks (adding a nullable column with a constant default, for instance, is a metadata-only change in modern PostgreSQL), but it's still worth checking the specific database engine's documented behavior for a given change on a large table before assuming it's instant, especially for older engine versions or less common operations like adding a NOT NULL constraint retroactively.

Feature flags as a complementary tool

For especially risky or complex schema transitions, pairing the expand/contract pattern with an application-level feature flag — controlling whether new code paths that depend on the new schema are actually active — gives an additional, fast rollback lever that doesn't require reverting a deployment or a migration, just flipping a flag, if something looks wrong during the transition.

6. Data Migrations vs. Schema Migrations

Schema migrations: structural changes

ALTER TABLE products ADD COLUMN category_id INT;
CREATE TABLE order_audit_log (id SERIAL PRIMARY KEY, order_id INT, changed_at TIMESTAMPTZ);

Changes to the shape of the data — tables, columns, indexes, constraints — without necessarily touching existing row values.

Data migrations: transforming existing data

UPDATE products SET category_id = (
    SELECT id FROM categories WHERE categories.name = products.category_name
);

Data migrations backfill, transform, or clean up the values within existing rows — often paired with a schema migration (add a column, then populate it) but a genuinely distinct concern with its own risks: a data migration can silently corrupt or lose information in a way a pure schema change generally can't.

Why data migrations deserve extra caution

They're often much slower than a schema change alone, especially on large tables — an UPDATE touching every row is a different order of magnitude of work than an ALTER TABLE ADD COLUMN.
They can't always be cleanly reversed — once a data transformation has run and the original values have been overwritten or discarded, there may be no way back short of a full backup restore.
They benefit from being batched on large tables, rather than one enormous UPDATE statement, to avoid extremely long-running transactions and excessive lock duration:

-- Batch the backfill in chunks rather than one massive UPDATE
UPDATE products SET category_id = /* ... */ WHERE id BETWEEN 1 AND 10000 AND category_id IS NULL;
-- repeat for the next range, and the next, checking progress between batches

Keep data migrations separate from schema migrations where practical

Bundling "add this column" and "populate every row's value for it" into a single migration file is convenient for small tables, but for large or critical tables, separating them into distinct migrations (or even distinct deployment steps entirely) gives more granular control, easier troubleshooting if the data transformation needs adjusting, and a natural checkpoint between "the schema changed" and "the data is fully migrated."

7. Rollback Strategy

Schema rollbacks are often genuinely harder than forward migrations

A Down migration that drops a newly-added column is technically simple to write — but if the application already wrote real data into that column before the rollback runs, reverting the schema change means discarding that data, which is rarely actually the desired outcome of a "rollback."

// This "successfully" rolls back the schema...
protected override void Down(MigrationBuilder migrationBuilder) =>
    migrationBuilder.DropColumn(name: "DiscountPercent", table: "Products");
// ...but any discount values already saved by users during the time the new code was live are now gone

Roll forward instead of rolling back, where possible

For this reason, many teams' practical rollback strategy for a problematic migration is to write and deploy a new, corrective forward migration rather than reverting to a prior schema state — "roll forward" preserves any data that accumulated in the interim and is often genuinely safer than attempting to reverse a migration that's already been running in production with real writes against it.

When a true rollback is the right call

The migration hasn't yet been applied to production, or has been applied but no meaningful data has been written against the new schema yet (a very short window, common right after a bad deploy is caught quickly).
The change is purely structural with no data implications (an index that turned out to be unnecessary, for instance).
A full point-in-time database restore is an acceptable option and the data loss window is understood and accepted.

Backups as the real safety net

Regardless of a migration tool's Down support, a recent, verified backup (see this series' SQL Server and PostgreSQL guides for backup strategy specifics) remains the actual safety net for a schema or data migration that goes seriously wrong — Down migrations are a convenience for straightforward, low-stakes reversals, not a substitute for a genuine disaster-recovery plan.

8. Migrations in CI/CD

Applying migrations as a distinct pipeline step

# GitHub Actions
- name: Apply database migrations
  run: dotnet ef database update --connection "${{ secrets.PROD_CONNECTION_STRING }}"

As covered in this series' Background Services and EF Core guides, running migrations automatically at every application instance's startup is risky for anything beyond small, low-traffic systems — with multiple instances starting concurrently during a rolling deployment, you risk several instances attempting to apply the same migration simultaneously. The more robust pattern is a single, dedicated migration step in the deployment pipeline, run once, before the new application version starts receiving traffic.

Migration ordering relative to code deployment

Given the expand/contract discipline from Section 5, the deployment order matters:

1. Deploy the "expand" migration (new schema element added, old one untouched)
2. Deploy new application code (writes to both, reads from new)
3. Run the data backfill
4. Verify
5. Deploy the "contract" migration (old schema element removed) — often in a later, separate release

Migrations generally need to run before the application code that depends on them is deployed (so the schema is ready when the new code starts expecting it), while destructive/contracting migrations should run only after confirming no running code still depends on what's being removed.

Testing migrations before they hit production

# Apply migrations against a fresh copy of the schema (or a recent production snapshot) in CI
dotnet ef database update --connection "$TEST_CONNECTION_STRING"

Running every pending migration against a test database (ideally seeded with a realistic data volume, not an empty schema) as part of CI catches a meaningful class of problems — a migration that works fine against an empty table but times out or locks unacceptably against a production-sized one — before they're discovered the hard way in production.

Approval gates for production schema changes

For higher-stakes environments, some teams add an explicit manual approval step between "migration reviewed and merged" and "migration actually applied to production" — similar in spirit to the terraform plan/what-if review pattern covered in this series' Terraform/Bicep guide, giving a human a last look at exactly what SQL is about to run against the production database.

9. Team Workflow and Conflict Resolution

Migration ordering conflicts

When two developers each create a migration independently from the same starting point, both might generate a migration intended to be "next" — merging both branches can produce two migrations that both claim the same version number/order, or migrations that conflict with each other's assumptions about the schema's current state.

# Developer A creates: 20260715120000_AddDiscountPercent.cs
# Developer B creates: 20260715130000_AddSkuColumn.cs (independently, same day)

Most tools order migrations by timestamp or an explicit sequence number generated at creation time, which usually resolves this automatically as long as migrations are generated close to when they're committed (not held in a long-lived branch and generated much earlier) — but it's still worth regenerating/rebasing a migration if a long time has passed between creating it locally and actually merging it, to catch any conflicting assumptions about the schema's current state.

Never edit an already-applied migration

// ❌ Don't modify a migration that's already been applied to any shared environment
// (dev, staging, or production) — its content is now part of the historical record
// other environments have already executed

Once a migration has been applied anywhere other than your own local, throwaway database, treat it as immutable — editing it means environments that already ran the old version are now out of sync with anyone who runs the edited version fresh, since the migration tool has no way to know the file's content changed after being marked as applied. If a migration needs correcting after being shared, write a new, corrective migration instead (the same "roll forward" principle from Section 7 applies here too).

Keeping local development databases in sync

dotnet ef database update  # brings a local dev database up to the latest migration

A quick, low-friction way for every developer to bring their local database schema up to date with the latest merged migrations is essential for a smooth team workflow — friction here (a developer working against a stale local schema, hitting confusing errors unrelated to their actual change) is a common, avoidable source of wasted time.

10. Common Pitfalls

Pitfall	Why it hurts	Better approach
Manual, undocumented changes made directly in production	Breaks the "migrations are the source of truth" model entirely, causes drift	Every schema change goes through a migration, no exceptions, including emergency hotfixes
Renaming/dropping columns directly in one step	Breaks old application instances still running during a rolling deployment	Use the expand/contract pattern (Section 5) for anything beyond additive changes
Editing an already-applied migration	Desyncs environments that already ran the original version	Write a new corrective migration instead
Running migrations automatically on every instance's startup	Risk of concurrent migration attempts during a rolling deployment	Run migrations as one distinct, single pipeline step before new instances start serving traffic
Bundling a massive data backfill into the same migration as a schema change	Long lock duration, hard to troubleshoot or resume if interrupted	Separate schema and data migrations; batch large data migrations
No testing of migrations against realistic data volume	A migration that's instant on an empty table can lock/timeout against a production-sized one	Test migrations in CI against a database seeded with a representative row count
Treating `Down` migrations as a full rollback safety net	Data written after the migration applied is lost on rollback	Prefer rolling forward with a corrective migration; rely on backups for genuine disaster recovery
No review process for schema changes	A risky change (a long-locking index build, a destructive drop) ships without a second pair of eyes	Review migrations in a pull request exactly like application code, before merging

Quick Reference Table

Concept	Purpose
Schema history table	The tool's record of which migrations have been applied, and in what order
`Up`/`Down` (or forward/undo)	Applying a change vs. reversing it
Additive change	Generally safe: new nullable column, new index, new table
Expand/contract	Multi-step pattern for safely renaming/removing schema elements on a live system
Data migration	Transforming existing row values, distinct from structural schema changes
Roll forward	Fixing a bad migration with a new corrective migration, rather than reverting
Idempotent/repeatable migration	Re-applied whenever its content changes (views, seed data), not a one-time step
CI migration testing	Catching locking/timeout issues against realistic data volume before production
Migration as a distinct pipeline step	Avoids concurrent-application races during a rolling deployment

Conclusion

Database migrations turn schema evolution from an ad-hoc, easily-drifted manual process into version-controlled, reviewable, repeatable code — the same discipline this series has advocated for application code, infrastructure (Terraform/Bicep), and everything in between, applied to the one part of a system that's often hardest to safely change after the fact: the shape of the data itself.

The tooling (EF Core, Flyway, Liquibase, DbUp) matters less than the underlying discipline: prefer additive, backward-compatible changes; use the expand/contract pattern for anything that would otherwise break code running during a rolling deployment; separate risky data transformations from simple structural changes; and treat "roll forward with a corrective migration" as the default recovery strategy, with real backups as the actual safety net for when something goes seriously wrong. Get those habits right, and schema evolution becomes a routine, low-drama part of shipping software — rather than the thing everyone's quietly afraid to touch.

Found this useful? Feel free to star the repo, open an issue with corrections, or share the rename-gone-wrong that taught you to respect the expand/contract pattern.

Dapper: High-Performance, Lightweight SQL for .NET

Rhuturaj Takle — Wed, 22 Jul 2026 15:07:40 +0000

Dapper: High-Performance, Lightweight SQL for .NET

A practical guide to Dapper — the micro-ORM that maps query results to C# objects with minimal overhead while leaving you in full control of the actual SQL, covering core querying, parameterization, multi-mapping, transactions, dynamic SQL patterns, and how it compares to EF Core.

Introduction
What Dapper Actually Is
Core Querying
Parameterization and SQL Injection Safety
Multi-Mapping Relationships
Multiple Result Sets
Executing Commands and Bulk Operations
Transactions
Dynamic SQL Patterns
Stored Procedures
Connection Management
Extending Dapper
Dapper vs. EF Core
Quick Reference Table
Conclusion

Introduction

Dapper is a micro-ORM — a lightweight library, not a full framework, that solves exactly one problem extremely well: mapping the results of a SQL query onto C# objects, with almost no overhead beyond what ADO.NET itself requires. It doesn't track changes, doesn't generate SQL from LINQ, doesn't manage migrations, and doesn't attempt to abstract away the database underneath you — you write the SQL, Dapper handles parameter binding and result mapping.

using var connection = new SqlConnection(connectionString);
var products = await connection.QueryAsync<Product>(
    "SELECT Id, Name, Price FROM Products WHERE CategoryId = @CategoryId",
    new { CategoryId = categoryId });

That's the entire pattern in one line: SQL text, a parameters object, and a strongly-typed result — no SqlCommand, no SqlDataReader loop, no manual property assignment. Dapper originated at Stack Overflow, built specifically because the team needed something faster than the full-featured ORMs of the time for their highest-traffic query paths, and it's remained popular for exactly that reason: when you already know the SQL you want to run and just need it executed efficiently and safely, Dapper gets out of the way.

1. What Dapper Actually Is

An extension library on `IDbConnection`

Dapper is implemented entirely as extension methods on ADO.NET's IDbConnection interface — there's no special connection type, no separate context object, no abstraction layer between you and the database provider you're already using (SqlConnection, NpgsqlConnection, SqliteConnection, etc.).

public static class SqlMapper
{
    public static Task<IEnumerable<T>> QueryAsync<T>(this IDbConnection cnn, string sql, object? param = null, ...);
    public static Task<int> ExecuteAsync(this IDbConnection cnn, string sql, object? param = null, ...);
}

This design choice is deliberate and significant: because Dapper works against the standard IDbConnection interface, it works with any ADO.NET provider without Dapper needing provider-specific code, and it composes naturally with existing ADO.NET knowledge rather than replacing it.

What Dapper does for you

Parameter binding — maps an anonymous object's properties (or a DynamicParameters instance) to SQL parameters safely.
Result mapping — maps each row of a SqlDataReader onto a C# object's properties by column name, using compiled IL generated at runtime (cached per query shape) rather than slow reflection on every row.
Type handling — sensible conversions between SQL types and .NET types, plus extensibility for custom type handling.

What Dapper deliberately does not do

No change tracking — you write the UPDATE statement yourself; there's no SaveChangesAsync().
No LINQ-to-SQL translation — every query is SQL text you write directly.
No migrations — schema management is entirely your responsibility (often paired with a dedicated migration tool like DbUp, Flyway, or even EF Core's migration system used purely for schema management alongside Dapper for data access).
No lazy loading, no navigation property magic — relationships are expressed via explicit joins and multi-mapping (Section 4), not automatic traversal.

This isn't Dapper being incomplete — it's Dapper being intentionally minimal, trading the conveniences of a full ORM for predictability, transparency, and speed.

2. Core Querying

`Query` and `QueryAsync`

public class ProductRepository
{
    private readonly string _connectionString;
    public ProductRepository(string connectionString) => _connectionString = connectionString;

    public async Task<IEnumerable<Product>> GetByCategoryAsync(int categoryId)
    {
        using var connection = new SqlConnection(_connectionString);
        return await connection.QueryAsync<Product>(
            "SELECT Id, Name, Price, CategoryId FROM Products WHERE CategoryId = @CategoryId",
            new { CategoryId = categoryId });
    }
}

Single-row queries

var product = await connection.QuerySingleOrDefaultAsync<Product>(
    "SELECT Id, Name, Price FROM Products WHERE Id = @Id", new { Id = id });

Method	Behavior when zero/multiple rows match
`QueryFirstAsync`	Returns the first row; throws if zero rows
`QueryFirstOrDefaultAsync`	Returns the first row, or `default` if zero rows; ignores extras
`QuerySingleAsync`	Throws unless exactly one row is returned
`QuerySingleOrDefaultAsync`	Returns `default` if zero rows, throws if more than one

Choosing the right one communicates intent: QuerySingleOrDefaultAsync for "there should be at most one match, and it's a bug if there's more than one" (like looking up by a unique ID) versus QueryFirstOrDefaultAsync for "there might be several matches and I only want one" (like the most recent record in an ordered query).

Scalar values

var count = await connection.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM Products WHERE CategoryId = @CategoryId", new { categoryId });

Mapping to anonymous types or `dynamic`

var results = await connection.QueryAsync("SELECT Name, Price FROM Products WHERE CategoryId = @CategoryId", new { categoryId });
foreach (var row in results)
{
    Console.WriteLine($"{row.Name}: {row.Price}"); // dynamic access, no class required
}

For quick, throwaway queries (a one-off report, an ad-hoc script) where defining a dedicated class feels like unnecessary ceremony, Dapper's non-generic Query/QueryAsync returns dynamic rows — convenient for exploration, though a concrete class is generally preferable for anything that's part of a maintained codebase, since dynamic loses compile-time checking entirely.

3. Parameterization and SQL Injection Safety

Always use parameters, never string concatenation

// ✅ Safe — Dapper parameterizes this correctly
var products = await connection.QueryAsync<Product>(
    "SELECT * FROM Products WHERE Name = @Name", new { Name = searchTerm });

// ❌ Vulnerable — never do this, regardless of the library
var products = await connection.QueryAsync<Product>(
    $"SELECT * FROM Products WHERE Name = '{searchTerm}'");

Because Dapper is a thin layer over raw SQL text, it doesn't protect you from SQL injection the way an ORM that generates all SQL for you inherently does — the protection comes entirely from consistently using parameterized queries (the anonymous object syntax above), never string interpolation or concatenation to build query text from user input. This is the single most important discipline when using Dapper (or any raw-SQL approach), and it's worth treating as a non-negotiable rule rather than a case-by-case judgment call.

`DynamicParameters` for more control

var parameters = new DynamicParameters();
parameters.Add("@CategoryId", categoryId);
parameters.Add("@MinPrice", minPrice, DbType.Decimal);
parameters.Add("@RowsAffected", dbType: DbType.Int32, direction: ParameterDirection.Output);

await connection.ExecuteAsync(
    "UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = @CategoryId AND Price >= @MinPrice; SET @RowsAffected = @@ROWCOUNT;",
    parameters);

int rowsAffected = parameters.Get<int>("@RowsAffected");

DynamicParameters is useful when you need explicit control over a parameter's DbType, need output parameters (common with stored procedures, see Section 9), or are building a parameter set programmatically rather than from a fixed anonymous object shape.

4. Multi-Mapping Relationships

Because Dapper doesn't understand navigation properties or automatic joins, mapping a SQL join's flattened result set back into a nested object graph is done explicitly via multi-mapping.

public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
    public Customer Customer { get; set; } = null!;
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
}

var sql = @"
    SELECT o.Id, o.OrderDate, c.Id, c.Name
    FROM Orders o
    JOIN Customers c ON c.Id = o.CustomerId
    WHERE o.Id = @OrderId";

var order = await connection.QueryAsync<Order, Customer, Order>(
    sql,
    (order, customer) => { order.Customer = customer; return order; },
    new { OrderId = orderId },
    splitOn: "Id" // tells Dapper where the Customer's columns begin in the flattened row
);

The splitOn parameter is the detail that trips people up most often — it tells Dapper which column name marks the boundary between the first object's columns and the second's in the flat row returned by the join. If both objects happen to have a column named Id (as above), that's exactly what you want to split on; if column names collide in a way that makes splitOn ambiguous, aliasing columns in the SQL itself (c.Id AS CustomerId) alongside an explicit splitOn: "CustomerId" resolves it cleanly.

One-to-many multi-mapping

var orderDictionary = new Dictionary<int, Order>();

var sql = @"
    SELECT o.Id, o.OrderDate, i.Id, i.ProductName, i.Quantity
    FROM Orders o
    JOIN OrderItems i ON i.OrderId = o.Id
    WHERE o.Id = @OrderId";

await connection.QueryAsync<Order, OrderItem, Order>(
    sql,
    (order, item) =>
    {
        if (!orderDictionary.TryGetValue(order.Id, out var existingOrder))
        {
            existingOrder = order;
            existingOrder.Items = [];
            orderDictionary.Add(existingOrder.Id, existingOrder);
        }
        existingOrder.Items.Add(item);
        return existingOrder;
    },
    new { OrderId = orderId },
    splitOn: "Id"
);

var result = orderDictionary.Values.Single();

This dictionary-accumulation pattern is the standard idiom for mapping a one-to-many join (one order, many order items) back into a single parent object with a populated collection — more manual than EF Core's automatic Include, but fully transparent about exactly what SQL runs and exactly how the mapping happens.

5. Multiple Result Sets

For a single round trip returning several distinct result sets (common when a single stored procedure or batched query needs to return, say, an order plus its line items as two separate SELECT statements), Dapper's QueryMultipleAsync handles it in one connection round trip:

var sql = @"
    SELECT * FROM Orders WHERE Id = @OrderId;
    SELECT * FROM OrderItems WHERE OrderId = @OrderId;";

using var multi = await connection.QueryMultipleAsync(sql, new { OrderId = orderId });

var order = await multi.ReadSingleAsync<Order>();
var items = (await multi.ReadAsync<OrderItem>()).ToList();
order.Items = items;

This avoids two separate round trips to the database for logically related data, while still keeping the mapping fully explicit and readable — a good middle ground between a single complex multi-mapped join and two entirely separate queries.

6. Executing Commands and Bulk Operations

Insert, update, delete

await connection.ExecuteAsync(
    "INSERT INTO Products (Name, Price, CategoryId) VALUES (@Name, @Price, @CategoryId)",
    new { Name = "Wireless Mouse", Price = 29.99m, CategoryId = 3 });

await connection.ExecuteAsync(
    "UPDATE Products SET Price = @Price WHERE Id = @Id",
    new { Id = 42, Price = 24.99m });

int rowsDeleted = await connection.ExecuteAsync(
    "DELETE FROM Products WHERE CategoryId = @CategoryId", new { CategoryId = categoryId });

ExecuteAsync returns the number of rows affected, which is often useful for confirming an update/delete actually matched something, without needing a separate existence check beforehand.

Getting a generated ID back after insert

var newId = await connection.ExecuteScalarAsync<int>(
    "INSERT INTO Products (Name, Price) VALUES (@Name, @Price); SELECT CAST(SCOPE_IDENTITY() AS INT);",
    new { Name = "Wireless Mouse", Price = 29.99m });

Since there's no automatic entity tracking to populate an Id property for you, retrieving a database-generated identity value requires explicitly including that in the SQL itself — SCOPE_IDENTITY() for SQL Server, RETURNING id for PostgreSQL, and so on, each database having its own idiom.

Bulk insert via a single parameterized call

var products = GetProductsToInsert(); // IEnumerable<Product>
await connection.ExecuteAsync(
    "INSERT INTO Products (Name, Price, CategoryId) VALUES (@Name, @Price, @CategoryId)",
    products);

Passing an IEnumerable<T> to ExecuteAsync runs the statement once per item — convenient and still far better than manually looping and building individual SqlCommand objects, but for genuinely large bulk-insert scenarios (thousands of rows or more), a dedicated bulk-copy mechanism (SqlBulkCopy for SQL Server, PostgreSQL's COPY command) will significantly outperform this per-row execution pattern, since it avoids the per-statement round-trip overhead entirely.

7. Transactions

using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
using var transaction = connection.BeginTransaction();

try
{
    await connection.ExecuteAsync(
        "UPDATE Accounts SET Balance = Balance - @Amount WHERE Id = @FromId",
        new { Amount = 100, FromId = fromAccountId }, transaction);

    await connection.ExecuteAsync(
        "UPDATE Accounts SET Balance = Balance + @Amount WHERE Id = @ToId",
        new { Amount = 100, ToId = toAccountId }, transaction);

    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Because Dapper is built directly on ADO.NET, transactions work exactly the way they always have in ADO.NET — a DbTransaction object, passed explicitly as an argument to each ExecuteAsync/QueryAsync call that should participate in it. There's no separate Dapper-specific transaction abstraction to learn; if you already know ADO.NET transactions, you already know Dapper transactions.

8. Dynamic SQL Patterns

Because Dapper doesn't build SQL for you, conditionally varying a query's shape (optional filters, dynamic sorting) is handled by conditionally building the SQL string itself, carefully, while still keeping all values parameterized.

Conditional filters

public async Task<IEnumerable<Product>> SearchAsync(string? category, decimal? minPrice, decimal? maxPrice)
{
    var conditions = new List<string>();
    var parameters = new DynamicParameters();

    if (category is not null)
    {
        conditions.Add("Category = @Category");
        parameters.Add("@Category", category);
    }
    if (minPrice is not null)
    {
        conditions.Add("Price >= @MinPrice");
        parameters.Add("@MinPrice", minPrice);
    }
    if (maxPrice is not null)
    {
        conditions.Add("Price <= @MaxPrice");
        parameters.Add("@MaxPrice", maxPrice);
    }

    var whereClause = conditions.Count > 0 ? "WHERE " + string.Join(" AND ", conditions) : "";
    var sql = $"SELECT * FROM Products {whereClause}";

    return await connection.QueryAsync<Product>(sql, parameters);
}

The critical discipline here: the conditionally-built parts are column/keyword names and structure, never raw values — every actual value still flows through a parameter (@Category, @MinPrice), never directly interpolated into the SQL string. This is what keeps dynamic SQL construction safe from injection even though the query text itself varies at runtime.

`IN` clauses with a variable number of values

var products = await connection.QueryAsync<Product>(
    "SELECT * FROM Products WHERE CategoryId IN @CategoryIds",
    new { CategoryIds = categoryIds }); // categoryIds: IEnumerable<int>

Dapper has built-in support for expanding a collection parameter into the correct number of IN (...) placeholders automatically — you don't need to hand-build a comma-separated list yourself, which is both more convenient and avoids a common, easy-to-get-wrong manual string-building mistake.

9. Stored Procedures

var results = await connection.QueryAsync<Product>(
    "GetProductsByCategory",
    new { CategoryId = categoryId },
    commandType: CommandType.StoredProcedure);

var parameters = new DynamicParameters();
parameters.Add("@CustomerId", customerId);
parameters.Add("@TotalOrders", dbType: DbType.Int32, direction: ParameterDirection.Output);

await connection.ExecuteAsync(
    "GetCustomerOrderSummary",
    parameters,
    commandType: CommandType.StoredProcedure);

int totalOrders = parameters.Get<int>("@TotalOrders");

Calling stored procedures is a natural fit for Dapper's model — you're already writing/managing the actual data-access logic explicitly, and a stored procedure is just another form of "SQL text to execute with parameters," output parameters included via DynamicParameters as shown above.

10. Connection Management

Dapper doesn't manage connection lifetime for you

// Each repository method typically opens and disposes its own connection
public async Task<Product?> GetByIdAsync(int id)
{
    using var connection = new SqlConnection(_connectionString);
    return await connection.QuerySingleOrDefaultAsync<Product>(
        "SELECT * FROM Products WHERE Id = @Id", new { Id = id });
}

Unlike EF Core's DbContext, which manages an underlying connection's lifecycle as part of its own scoped lifetime, Dapper operates directly on whatever IDbConnection you hand it — opening and disposing the connection is your responsibility, typically via a using statement scoped to each logical unit of work (as above), relying on ADO.NET's built-in connection pooling to make repeatedly opening/closing connections cheap in practice (the underlying physical connection is usually reused from the pool, not actually torn down and recreated each time).

A typical repository pattern

public class ProductRepository
{
    private readonly IDbConnectionFactory _connectionFactory;
    public ProductRepository(IDbConnectionFactory connectionFactory) => _connectionFactory = connectionFactory;

    public async Task<Product?> GetByIdAsync(int id)
    {
        using var connection = _connectionFactory.CreateConnection();
        return await connection.QuerySingleOrDefaultAsync<Product>(
            "SELECT * FROM Products WHERE Id = @Id", new { Id = id });
    }
}

public interface IDbConnectionFactory
{
    IDbConnection CreateConnection();
}

public class SqlConnectionFactory : IDbConnectionFactory
{
    private readonly string _connectionString;
    public SqlConnectionFactory(string connectionString) => _connectionString = connectionString;
    public IDbConnection CreateConnection() => new SqlConnection(_connectionString);
}

Wrapping connection creation behind a small factory interface (rather than instantiating SqlConnection directly throughout the codebase) makes it easier to swap providers or inject a test double, without adding meaningful overhead or complexity — a common, lightweight convention in Dapper-based codebases.

11. Extending Dapper

Custom type handlers

public class JsonTypeHandler<T> : SqlMapper.TypeHandler<T>
{
    public override void SetValue(IDbDataParameter parameter, T value) =>
        parameter.Value = JsonSerializer.Serialize(value);

    public override T Parse(object value) =>
        JsonSerializer.Deserialize<T>((string)value)!;
}

SqlMapper.AddTypeHandler(new JsonTypeHandler<ProductMetadata>());

Custom type handlers let Dapper correctly map types it doesn't understand natively — a common example being a column storing serialized JSON that should be automatically deserialized into a strongly-typed C# object on read, and serialized back on write.

Dapper.Contrib and other extensions

[Table("Products")]
public class Product
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
}

await connection.InsertAsync(product);
await connection.UpdateAsync(product);
await connection.DeleteAsync(product);
var product = await connection.GetAsync<Product>(42);

Dapper.Contrib is an official companion library adding basic CRUD convenience methods (Insert, Update, Delete, Get) generated from simple attribute-based conventions — useful for straightforward single-table CRUD where writing the SQL by hand feels like unnecessary repetition, while still keeping the rest of your data access as plain Dapper for anything more involved. It's optional, and many Dapper codebases skip it entirely in favor of writing every statement explicitly, valuing the transparency over the convenience.

12. Dapper vs. EF Core

Aspect	Dapper	EF Core
SQL control	You write every query explicitly	LINQ translated automatically; less direct control
Performance overhead	Minimal — closest to raw ADO.NET	Higher — change tracking, query translation, more abstraction
Change tracking	None — you write UPDATE statements yourself	Automatic — modify properties, call `SaveChangesAsync()`
Migrations	None built in — pair with a separate tool, or manage schema manually	Built-in, integrated migration system
Relationship mapping	Manual, explicit multi-mapping	Automatic via navigation properties and `Include`
Learning curve	Low if you already know SQL; you own more of the plumbing	Higher — more concepts (change tracking, loading strategies, LINQ translation quirks) to learn well
Best for	Performance-critical paths, complex reporting queries, teams that want full SQL control	The majority of typical CRUD-heavy application data access
Risk profile	SQL injection risk if parameterization discipline lapses; more manual code to get wrong	N+1 queries and unintended full-entity loads if loading strategies aren't used carefully

The common, pragmatic combination

As covered in this series' EF Core guide, a large share of production .NET codebases don't choose one exclusively — they use EF Core for the bulk of application data access (where its productivity and maintainability benefits are worth the overhead) and Dapper for a specific, deliberately identified set of high-volume or complex-reporting queries where raw SQL control and minimal overhead genuinely matter more than the conveniences EF Core provides. Both libraries can coexist against the same database and even the same underlying SqlConnection without conflict, since Dapper is just extension methods on the same ADO.NET types EF Core itself ultimately uses underneath.

Quick Reference Table

Concept	Purpose
`QueryAsync<T>`	Maps multiple rows to a strongly-typed collection
`QuerySingleOrDefaultAsync<T>`	Enforces "at most one match" semantics for lookups
`ExecuteAsync`	Runs INSERT/UPDATE/DELETE, returns affected row count
`ExecuteScalarAsync<T>`	Returns a single scalar value (count, generated ID, etc.)
`DynamicParameters`	Explicit parameter control, output parameters
Multi-mapping (`splitOn`)	Maps a flattened join result back into a nested object graph
`QueryMultipleAsync`	Reads several result sets from one round trip
Custom type handlers	Teaches Dapper to map types it doesn't understand natively
Dapper.Contrib	Optional attribute-based basic CRUD convenience layer
`IN @Collection`	Automatic expansion of a collection parameter into `IN (...)`

Conclusion

Dapper's entire value proposition is honesty about the tradeoff a full ORM makes: EF Core buys productivity and convenience by abstracting SQL away, at some cost in overhead and "magic" you need to understand to avoid its pitfalls; Dapper keeps you writing SQL directly, in exchange for speed, transparency, and the confidence that comes from knowing exactly what query is running, every time. Neither approach is more "correct" — they're suited to different priorities, and the most pragmatic production codebases often use both, applying each where its specific strengths matter most.

What Dapper demands from you that a full ORM partially handles automatically is discipline: consistent parameterization to avoid SQL injection, deliberate multi-mapping for relationships, and your own schema/migration strategy. In exchange, you get code that does exactly what it looks like it does — no hidden N+1 queries, no surprising client-evaluation fallbacks, no generated SQL you didn't anticipate — which is precisely why it remains the tool of choice whenever a query's performance and predictability matter more than the convenience of not writing SQL at all.

Found this useful? Feel free to star the repo, open an issue with corrections, or share the splitOn mistake that taught you to read the multi-mapping docs carefully.

EF Core: The .NET Object-Relational Mapper

Rhuturaj Takle — Tue, 21 Jul 2026 15:37:20 +0000

EF Core: The .NET Object-Relational Mapper

A practical guide to Entity Framework Core — the ORM that lets .NET developers work with databases using C# objects and LINQ instead of hand-written SQL, covering DbContext, migrations, querying, change tracking, relationships, performance tuning, and when to reach for something else.

Introduction
DbContext and DbSet
Modeling: Conventions, Data Annotations, and Fluent API
Migrations
Querying with LINQ
Change Tracking and SaveChanges
Relationships and Loading Strategies
Concurrency Control
Transactions
Performance Tuning
Raw SQL and Escape Hatches
Testing with EF Core
When to Reach for Something Else
Quick Reference Table
Conclusion

Introduction

Entity Framework Core (EF Core) is Microsoft's modern, cross-platform Object-Relational Mapper for .NET — it lets you define your data model as plain C# classes, query it with LINQ, and let EF Core translate that into SQL for whichever database provider you're targeting (SQL Server, PostgreSQL, SQLite, MySQL, Cosmos DB, and others).

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
}

public class AppDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
    protected override void OnConfiguring(DbContextOptionsBuilder options) =>
        options.UseSqlServer(connectionString);
}

var expensiveProducts = await dbContext.Products
    .Where(p => p.Price > 100)
    .OrderByDescending(p => p.Price)
    .ToListAsync();

That LINQ query gets translated into a parameterized SQL SELECT statement, and the results are automatically mapped back into Product objects — no manual SqlCommand, no manual row-to-object mapping. This guide covers the concepts that matter most for using EF Core correctly and efficiently: how it tracks changes, how relationships and loading work, where performance problems typically hide, and when a different tool (Dapper, raw SQL) is the better choice instead.

1. DbContext and DbSet

DbContext: the unit of work

DbContext represents a session with the database — it tracks entities you've loaded or added, translates LINQ queries into SQL, and coordinates saving changes back as a single unit of work.

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<Product> Products => Set<Product>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Customer> Customers => Set<Customer>();
}

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

Lifetime matters

AddDbContext registers AppDbContext with a scoped lifetime by default — one instance per HTTP request in a typical ASP.NET Core app. This is deliberate: DbContext is not thread-safe, and isn't meant to be shared across concurrent operations or held for longer than a single logical unit of work (see the Background Services guide in this series for the specific pitfall of capturing a scoped DbContext inside a singleton).

DbSet as a queryable entry point

DbSet<Product> Products => Set<Product>();

A DbSet<T> represents a table (or, for more complex mappings, a query) and implements IQueryable<T> — every LINQ query against it is translated to SQL lazily, only executing when the query is actually enumerated (via ToListAsync(), FirstOrDefaultAsync(), a foreach, etc.), not when the LINQ expression itself is built.

2. Modeling: Conventions, Data Annotations, and Fluent API

EF Core determines your database schema from your C# classes through three complementary mechanisms, layered in increasing precedence.

Conventions (the defaults)

public class Product
{
    public int Id { get; set; }              // convention: "Id" becomes the primary key automatically
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
    public int CategoryId { get; set; }       // convention: recognized as a foreign key to Category
    public Category Category { get; set; } = null!;
}

EF Core's conventions handle a surprising amount automatically — a property named Id (or {ClassName}Id) becomes the primary key, a property matching another entity's name plus Id is recognized as a foreign key, and so on.

Data annotations

public class Product
{
    public int Id { get; set; }

    [Required, MaxLength(200)]
    public string Name { get; set; } = "";

    [Column(TypeName = "decimal(10,2)")]
    public decimal Price { get; set; }

    [NotMapped]
    public string DisplayName => $"{Name} (${Price})"; // computed property, not persisted
}

Attributes directly on the model classes are convenient for simple, per-property configuration, but they mix persistence concerns into your domain model — many teams prefer keeping model classes clean and pushing this configuration into the Fluent API instead.

Fluent API

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>(entity =>
    {
        entity.Property(p => p.Name).IsRequired().HasMaxLength(200);
        entity.Property(p => p.Price).HasColumnType("decimal(10,2)");
        entity.HasIndex(p => p.CategoryId);
        entity.HasOne(p => p.Category)
              .WithMany(c => c.Products)
              .HasForeignKey(p => p.CategoryId)
              .OnDelete(DeleteBehavior.Restrict);
    });
}

The Fluent API is the most powerful and complete configuration mechanism — anything data annotations can express, Fluent API can too, plus configuration (like the delete behavior above) that annotations can't express at all. For larger models, splitting configuration into separate IEntityTypeConfiguration<T> classes keeps OnModelCreating from becoming an unmanageable wall of code:

public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.Property(p => p.Name).IsRequired().HasMaxLength(200);
        builder.HasIndex(p => p.CategoryId);
    }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
}

3. Migrations

Migrations are EF Core's mechanism for evolving your database schema alongside your C# model, keeping both under version control together.

Creating and applying a migration

dotnet ef migrations add AddProductDiscountField
dotnet ef database update

// Generated migration file (excerpt)
public partial class AddProductDiscountField : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<decimal>(
            name: "DiscountPercent",
            table: "Products",
            type: "decimal(5,2)",
            nullable: true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(name: "DiscountPercent", table: "Products");
    }
}

Every migration has an Up (apply the change) and Down (revert it) method — dotnet ef database update applies any pending migrations in order, and can also target a specific prior migration to roll back to.

Reviewing generated migrations before applying them

EF Core's migration generation is good but not infallible — it's worth actually reading a generated migration before running it against production, especially for anything beyond a simple additive column change (renaming a column, changing a type, adding a non-nullable column to a table with existing rows all deserve a second look, since the "obvious" auto-generated approach isn't always the safest one for existing data).

Applying migrations in production

// Programmatic approach — useful for automated deployment pipelines
using var scope = app.Services.CreateScope();
var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
await db.Database.MigrateAsync();

Running MigrateAsync() automatically at application startup is convenient for small projects but risky at scale — with multiple instances starting simultaneously (a rolling deployment), you can end up with concurrent migration attempts. A more robust pattern for production systems is running migrations as a distinct, single deployment step (a CI/CD pipeline stage, a one-off job) before the new application version starts serving traffic, rather than baking it into every instance's startup path.

Generating SQL scripts for review/DBA approval

dotnet ef migrations script --idempotent -o migration.sql

For environments where a DBA needs to review and manually apply schema changes (common in regulated or highly cautious production environments), generating an idempotent SQL script rather than relying on EF Core's runtime migration application gives a reviewable artifact that can go through the same change-approval process as any other production database change.

4. Querying with LINQ

Basic queries

var products = await dbContext.Products
    .Where(p => p.CategoryId == categoryId && p.Price < 100)
    .OrderBy(p => p.Name)
    .ToListAsync();

Projections (selecting only what you need)

var productSummaries = await dbContext.Products
    .Where(p => p.CategoryId == categoryId)
    .Select(p => new ProductSummaryDto { Name = p.Name, Price = p.Price })
    .ToListAsync();

Projecting into a DTO (rather than loading full entities and mapping afterward) lets EF Core generate a SELECT that only pulls the columns you actually need — a meaningful performance win for wide tables when you only need a couple of fields, and it also means the result isn't tracked by the change tracker at all (see Section 5), since it's not a mapped entity.

`AsNoTracking` for read-only queries

var products = await dbContext.Products
    .AsNoTracking()
    .Where(p => p.CategoryId == categoryId)
    .ToListAsync();

For any query whose results you won't modify and save back, AsNoTracking() skips the overhead of change tracking (Section 5) entirely — a simple, low-risk performance improvement that's easy to forget to apply consistently across read-heavy endpoints, and one of the first things worth checking when a read-only query path is slower than expected.

Aggregate and grouping queries

var revenueByCategory = await dbContext.Orders
    .SelectMany(o => o.Items)
    .GroupBy(i => i.Product.CategoryId)
    .Select(g => new { CategoryId = g.Key, TotalRevenue = g.Sum(i => i.Price * i.Quantity) })
    .ToListAsync();

EF Core translates GroupBy, aggregate functions, and most standard LINQ operators into equivalent SQL — but not every LINQ construct has a clean SQL translation; anything EF Core can't translate either throws at query time (in modern EF Core versions) or, in older/misconfigured setups, silently falls back to evaluating part of the query in memory (client evaluation), which can be a serious, easy-to-miss performance trap on large tables.

Split queries for multiple included collections

var orders = await dbContext.Orders
    .Include(o => o.Items)
    .Include(o => o.StatusHistory)
    .AsSplitQuery()
    .ToListAsync();

Including multiple collection navigations in a single query by default produces one large SQL query with multiple joins, which can multiply row counts (a "cartesian explosion") when more than one collection is included at once. AsSplitQuery() instead issues separate SQL queries per included collection — often meaningfully faster for this specific shape of query, at the cost of losing single-query atomicity (the split queries aren't guaranteed to reflect a perfectly consistent snapshot if data changes between them, though this is rarely a practical concern for typical read scenarios).

5. Change Tracking and SaveChanges

How change tracking works

var product = await dbContext.Products.FirstAsync(p => p.Id == 42);
product.Price = 24.99m; // no database call happens here
await dbContext.SaveChangesAsync(); // EF Core detects the change and issues an UPDATE

When you query an entity without AsNoTracking(), EF Core keeps a snapshot of its original values in the DbContext's change tracker. When SaveChangesAsync() is called, EF Core compares the entity's current values against that snapshot, and generates INSERT/UPDATE/DELETE statements only for what actually changed — you never write SQL for the update itself, just modify the object's properties.

Entity states

dbContext.Entry(product).State = EntityState.Modified; // rarely needed directly, but useful to understand

Every tracked entity has a state: Added, Unchanged, Modified, Deleted, or Detached — this state is what SaveChangesAsync() actually reads to decide what SQL to generate, and it's normally managed automatically by EF Core's own change detection rather than something you set by hand.

Batching changes into one `SaveChangesAsync()` call

dbContext.Products.Add(newProduct);
existingProduct.Price = 19.99m;
dbContext.Categories.Remove(oldCategory);

await dbContext.SaveChangesAsync(); // all three changes committed together, in one transaction

SaveChangesAsync() wraps everything tracked as changed since the last save into a single database transaction — all changes across multiple entities succeed or fail together, which is one of EF Core's most valuable, easy-to-take-for-granted correctness guarantees.

6. Relationships and Loading Strategies

Defining relationships

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; } = null!;
    public List<OrderItem> Items { get; set; } = [];
}

public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public Order Order { get; set; } = null!;
    public int ProductId { get; set; }
    public Product Product { get; set; } = null!;
    public int Quantity { get; set; }
}

Eager loading with `Include`

var order = await dbContext.Orders
    .Include(o => o.Items).ThenInclude(i => i.Product)
    .FirstOrDefaultAsync(o => o.Id == orderId);

Include (and ThenInclude for nested navigations) loads related data as part of the same query, up front — the right choice when you know you'll need the related data, avoiding the N+1 problem described next.

The N+1 problem (and lazy loading's role in it)

// Without Include, accessing a navigation property triggers a separate query per entity if lazy loading is enabled
var orders = await dbContext.Orders.ToListAsync();
foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name); // one additional query PER order, if lazy loading is on
}

This is the same N+1 problem covered in this series' GraphQL guide, manifesting in EF Core specifically through lazy loading — a feature that must be explicitly opted into (via the Microsoft.EntityFrameworkCore.Proxies package and UseLazyLoadingProxies()) and is generally considered something to use cautiously, since it makes this exact performance trap easy to hit accidentally: a loop that looks perfectly innocent in code silently issues dozens or hundreds of extra round trips to the database.

Explicit loading

var order = await dbContext.Orders.FirstAsync(o => o.Id == orderId);
await dbContext.Entry(order).Collection(o => o.Items).LoadAsync();

A middle ground between eager and lazy loading — you load the main entity first, then explicitly (and visibly, in code) load specific related data only when you actually need it, avoiding both the upfront cost of always eager-loading everything and the hidden-cost surprise of implicit lazy loading.

Recommended default

For most application code, eager loading (Include) combined with AsNoTracking() for read-only queries is the safest, most predictable default — it makes data access patterns explicit and visible in the query itself, rather than relying on lazy loading's implicit, easy-to-overlook extra round trips.

7. Concurrency Control

Optimistic concurrency with a row version/concurrency token

public class Product
{
    public int Id { get; set; }
    public decimal Price { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; } = null!;
}

try
{
    await dbContext.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    // Another transaction modified this row between when we loaded it and when we tried to save
    var databaseValues = await ex.Entries.Single().GetDatabaseValuesAsync();
    // reload, merge, or surface a conflict to the user, depending on the application's needs
}

EF Core includes the RowVersion (or another designated concurrency token column) in the WHERE clause of the generated UPDATE statement — if the row has changed since it was loaded, zero rows match the WHERE clause, EF Core detects this as a concurrency conflict, and throws DbUpdateConcurrencyException rather than silently overwriting someone else's concurrent change.

When to care about this

Optimistic concurrency tokens matter most for entities that are both frequently read-then-updated and genuinely subject to concurrent edits (a shared inventory count, a document multiple users might edit) — for data that's effectively only ever modified by one process/user at a time, the added complexity of concurrency-conflict handling usually isn't worth introducing.

8. Transactions

Implicit transactions via SaveChangesAsync

As covered in Section 5, a single SaveChangesAsync() call is already wrapped in an implicit transaction — for the common case of "make several related changes, then save them together," you don't need to manage a transaction explicitly at all.

Explicit transactions across multiple SaveChanges calls

using var transaction = await dbContext.Database.BeginTransactionAsync();
try
{
    order.Status = OrderStatus.Confirmed;
    await dbContext.SaveChangesAsync();

    await _inventoryService.ReserveStockAsync(order); // might call SaveChangesAsync() internally too

    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

An explicit transaction is needed when a logical operation spans multiple separate SaveChangesAsync() calls (often because it also involves calling into other services/repositories) and all of them need to succeed or fail together as one atomic unit.

9. Performance Tuning

Watch the generated SQL

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString).LogTo(Console.WriteLine, LogLevel.Information));

Logging the actual SQL EF Core generates (during development, not typically in production due to overhead and log volume) is often the fastest way to spot an unexpectedly expensive query — an accidental N+1 pattern, an unnecessary SELECT * where a projection would do, or a query that isn't translating as cleanly to SQL as you'd assume.

Compiled queries for extremely hot paths

private static readonly Func<AppDbContext, int, Task<Product?>> GetProductById =
    EF.CompileAsyncQuery((AppDbContext context, int id) =>
        context.Products.FirstOrDefault(p => p.Id == id));

var product = await GetProductById(dbContext, 42);

Compiled queries skip LINQ expression tree translation on every call (EF Core normally caches this internally too, so the benefit here is narrower than it might sound) — worth reaching for only in genuinely extreme-throughput, latency-sensitive paths after profiling shows query translation overhead actually matters, not as a default optimization applied everywhere.

Bulk operations: `ExecuteUpdate` and `ExecuteDelete`

await dbContext.Products
    .Where(p => p.CategoryId == discontinuedCategoryId)
    .ExecuteUpdateAsync(setters => setters.SetProperty(p => p.IsActive, false));

await dbContext.Products
    .Where(p => p.CategoryId == discontinuedCategoryId)
    .ExecuteDeleteAsync();

Introduced in EF Core 7, these translate directly into a single UPDATE/DELETE SQL statement affecting potentially many rows at once — dramatically more efficient than the traditional pattern of loading every matching entity into memory, modifying each one, and calling SaveChangesAsync(), especially for bulk operations affecting thousands of rows.

Avoid `SELECT *` by default: use projections

As covered in Section 4, projecting into a DTO rather than always loading full entities is one of the most consistently effective, low-effort performance improvements available — especially valuable for list/summary views that don't need every column of a wide entity.

Indexes still matter

EF Core generates the SQL, but it doesn't design your indexes for you — the indexing guidance covered in this series' SQL Server and PostgreSQL guides applies exactly the same way regardless of whether the SQL hitting the database came from hand-written T-SQL or EF Core's translation of a LINQ query.

10. Raw SQL and Escape Hatches

`FromSqlInterpolated` for read queries

var products = await dbContext.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE CategoryId = {categoryId} AND Price > {minPrice}")
    .ToListAsync();

FromSqlInterpolated (note: interpolated, not raw string concatenation) safely parameterizes the interpolated values, avoiding SQL injection while still letting you drop down to raw SQL for a query LINQ can't express cleanly, or where a hand-tuned query outperforms what EF Core's translator would generate.

`ExecuteSqlInterpolated` for commands

await dbContext.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = {categoryId}");

The equivalent escape hatch for non-query commands — again, safely parameterized despite the string-interpolation-like syntax.

Calling stored procedures

var results = await dbContext.Products
    .FromSqlInterpolated($"EXEC GetDiscountedProducts @CategoryId = {categoryId}")
    .ToListAsync();

For applications that lean on stored procedures for specific, performance-critical, or business-logic-encapsulating operations (as discussed in this series' SQL Server guide), EF Core can still map the results back into entities/DTOs cleanly.

11. Testing with EF Core

The in-memory provider: convenient, but not a substitute for the real thing

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
    .Options;
using var context = new AppDbContext(options);

EF Core's in-memory provider is fast and easy to set up for unit tests, but it's not a real relational database — it doesn't enforce foreign key constraints the same way, doesn't translate LINQ to SQL (so a query that would fail to translate against a real provider might silently "work" against the in-memory provider by evaluating in memory instead), and can behave differently around concurrency and transactions. Tests that pass against the in-memory provider are not a reliable guarantee the same code works against SQL Server/PostgreSQL.

SQLite as a more faithful lightweight alternative

var connection = new SqliteConnection("Filename=:memory:");
connection.Open();
var options = new DbContextOptionsBuilder<AppDbContext>().UseSqlite(connection).Options;

An in-memory SQLite database is a genuine relational database, enforcing real constraints and actually translating/executing SQL — a meaningfully more faithful substitute than the in-memory provider for integration-style tests, though SQLite's own SQL dialect and behavior still differ from SQL Server/PostgreSQL in some respects, so it's a step closer to production fidelity, not a perfect stand-in.

Testing against a real database (via containers)

For the highest-fidelity tests, spinning up the actual target database (SQL Server, PostgreSQL) in a Docker container as part of the test run — often via a library like Testcontainers — is increasingly the preferred approach for integration tests where correctness against the real provider's actual behavior matters, accepting the added test-run time and infrastructure as a reasonable tradeoff for that fidelity.

12. When to Reach for Something Else

EF Core is an excellent default for the majority of application data access, but it's not universally the right tool for every query.

Scenario	Better fit
Simple, well-understood CRUD across a moderate-complexity schema	EF Core — the productivity and maintainability wins are substantial
A small number of extremely performance-critical, high-volume queries	Dapper (or raw ADO.NET) for just those specific queries, EF Core for everything else
Complex reporting queries with heavy aggregation across many tables	Often better as hand-written SQL (via `FromSqlInterpolated` or a dedicated reporting query layer) than fighting LINQ's translation of a very complex query
Bulk data operations affecting millions of rows	`ExecuteUpdate`/`ExecuteDelete` where applicable, or a dedicated bulk-copy/ETL tool for very large-scale operations
A team that already deeply understands SQL and wants maximum control	Dapper — thinner abstraction, less "magic," fuller visibility into exactly what SQL runs

A common, pragmatic pattern (also mentioned in this series' SQL Server guide): EF Core for the majority of an application's data access, with Dapper reserved for a small, deliberately chosen set of hot-path or complex-reporting queries where the fine control and reduced overhead genuinely matter — not an all-or-nothing choice between the two.

Quick Reference Table

Concept	Purpose
`DbContext`	Unit of work — tracks changes, translates queries, coordinates saves
`DbSet<T>`	Queryable entry point representing a table
Migrations	Version-controlled, incremental schema evolution alongside the C# model
`AsNoTracking()`	Skips change tracking overhead for read-only queries
`Include`/`ThenInclude`	Eager-load related data in the same (or a split) query
Lazy loading	Implicit per-access loading — convenient but an easy source of N+1 queries
`DbUpdateConcurrencyException`	Signals an optimistic concurrency conflict on save
`ExecuteUpdate`/`ExecuteDelete`	Single-statement bulk operations without loading entities into memory
`FromSqlInterpolated`/`ExecuteSqlInterpolated`	Safe escape hatches to raw SQL when LINQ isn't the right fit
`AsSplitQuery()`	Avoids cartesian-explosion row multiplication with multiple included collections
In-memory provider vs. SQLite vs. containers	Increasing levels of test fidelity to the real target database

Conclusion

EF Core's core value proposition — write C# and LINQ, get correct, parameterized SQL and automatic change tracking in return — holds up well for the large majority of application data access, and it removes a huge amount of repetitive, error-prone boilerplate (manual mapping, hand-written INSERT/UPDATE statements, connection management) that raw ADO.NET requires. The places it demands real attention are consistent and well-understood: watch for N+1 queries around navigation properties, use AsNoTracking() deliberately for read-only paths, project into DTOs rather than always loading full entities, and don't assume LINQ has a clean, efficient SQL translation for every possible query shape without checking the generated SQL.

None of this makes EF Core the wrong choice — it makes it a tool whose sharp edges are well-documented and predictable once you know where to look, which is exactly what you want from something that sits directly between your application code and the database powering it.

Found this useful? Feel free to star the repo, open an issue with corrections, or share the N+1 query that taught you to always check the generated SQL.

Redis: In-Memory Data Store for Caching, Sessions, and Fast Access

Rhuturaj Takle — Mon, 20 Jul 2026 15:15:14 +0000

Redis: In-Memory Data Store for Caching, Sessions, and Fast Access

A practical guide to Redis — the in-memory data store used for caching, session storage, rate limiting, pub/sub messaging, and other latency-sensitive workloads, covering data structures, persistence, expiration, common patterns, high availability, and .NET integration.

Introduction
Why Redis Is Fast
Data Structures
Expiration and Eviction
Persistence
Common Patterns
Pub/Sub and Streams
Distributed Locking
High Availability and Scaling
.NET Integration
Caching Pitfalls
Redis vs. Alternatives
Quick Reference Table
Conclusion

Introduction

Redis (REmote DIctionary Server) is an in-memory data store that functions as a cache, message broker, and lightweight database — all built around simple, well-understood data structures (strings, hashes, lists, sets, sorted sets, and more) that map cleanly onto common application needs. Its defining characteristic is speed: because data lives in memory rather than on disk, most operations complete in well under a millisecond, which is precisely the property that makes it the default choice for caching, session storage, and any workload where database round-trip latency is a bottleneck.

var db = redis.GetDatabase();
await db.StringSetAsync("product:42", productJson, TimeSpan.FromMinutes(10));
var cached = await db.StringGetAsync("product:42");

That round trip — set a value with an expiration, read it back — is the single most common Redis usage pattern in production applications, and it's deliberately simple by design.

1. Why Redis Is Fast

In-memory by default

Unlike a relational or document database, where data primarily lives on disk and is cached in memory opportunistically, Redis inverts that relationship: data lives in memory as the primary copy, with disk persistence as an optional, secondary durability mechanism (Section 4). Reading and writing RAM is orders of magnitude faster than disk I/O, even against an SSD — this is the single biggest reason Redis operations are so much faster than a typical database round trip.

Single-threaded command execution

Redis's core command processing is (for the traditional, still-dominant configuration) single-threaded — every command executes atomically, one at a time, with no locking overhead between concurrent clients for a given command. This sounds like it should limit throughput, but in practice, avoiding lock contention and context-switching overhead makes single-threaded execution genuinely very fast for the kind of small, quick operations Redis is built for; multi-threading was added for I/O handling in later versions (6.0+) to help with network throughput at high connection counts, but command execution itself remains effectively single-threaded per data shard.

Simple, purpose-built data structures

Redis doesn't try to be a general-purpose query engine — its data structures (Section 2) are chosen specifically because their common operations (append to a list, increment a counter, add to a set) can be implemented with predictable, typically O(1) or O(log N) time complexity, rather than the more open-ended, sometimes unpredictable query planning a relational database's SQL engine has to do.

2. Data Structures

Redis's value isn't just "a fast key-value store" — it's a fast store for several genuinely useful data structures, each suited to different application problems.

Strings

SET user:42:name "Ada Lovelace"
GET user:42:name
INCR page:views          # atomic increment — useful for counters
EXPIRE user:42:name 3600  # set/refresh a TTL in seconds

The simplest structure — a key mapped to a single string (which can also hold serialized JSON, a number, or binary data). INCR/INCRBY provide atomic counters without a separate read-modify-write round trip.

Hashes

HSET user:42 name "Ada Lovelace" email "ada@example.com" loginCount 5
HGET user:42 email
HINCRBY user:42 loginCount 1
HGETALL user:42

A hash maps field names to values within a single key — a natural fit for representing an object (like a user profile) without serializing the whole thing to a JSON string, letting you read or update individual fields directly rather than the entire blob.

Lists

LPUSH recent-orders:user42 "order-1001"
LPUSH recent-orders:user42 "order-1002"
LRANGE recent-orders:user42 0 9   # get the 10 most recent
LTRIM recent-orders:user42 0 99  # keep only the most recent 100

An ordered collection, efficient for push/pop at either end — commonly used for activity feeds, recent-items lists, or as a simple queue (LPUSH/RPOP or RPUSH/LPOP).

Sets

SADD product:42:viewed-by "user1" "user2" "user3"
SISMEMBER product:42:viewed-by "user1"
SINTER product:42:viewed-by product:17:viewed-by   # users who viewed both products
SCARD product:42:viewed-by                          # count of members

An unordered collection of unique values, with efficient membership checks and set operations (union, intersection, difference) — useful for tagging, deduplication, and "users who did both X and Y" style queries.

Sorted sets

ZADD leaderboard 1500 "player1" 2200 "player2" 1800 "player3"
ZREVRANGE leaderboard 0 9 WITHSCORES   # top 10 by score, descending
ZRANK leaderboard "player1"             # player1's rank
ZINCRBY leaderboard 50 "player1"        # increment a score atomically

Every member has an associated numeric score, and Redis keeps the set ordered by score automatically — the canonical structure for leaderboards, priority queues, and any "top N by some ranking value" problem, with efficient range queries by rank or by score.

Streams

XADD orders:stream * orderId 1001 status "created"
XREAD COUNT 10 STREAMS orders:stream 0

An append-only log structure (conceptually similar to a simplified Kafka topic) supporting consumer groups for distributed processing — covered further in Section 6.

HyperLogLog and Bitmaps (specialized structures)

PFADD unique-visitors:2026-07-11 "user1" "user2" "user1"
PFCOUNT unique-visitors:2026-07-11   # approximate distinct count, ~0.81% error, tiny memory footprint

SETBIT user:42:daily-active 200 1    # mark day 200 as active
BITCOUNT user:42:daily-active        # count active days

HyperLogLog provides an approximate but extremely memory-efficient distinct-count (perfect for "unique visitors today" at massive scale where exact precision isn't worth the memory cost); bitmaps pack boolean flags into individual bits, useful for compact activity/attendance tracking across large populations.

3. Expiration and Eviction

Time-to-live (TTL)

SET session:abc123 "user-data" EX 1800   # expires in 30 minutes
TTL session:abc123                        # seconds remaining, or -1 if no expiry, -2 if key doesn't exist
PERSIST session:abc123                    # remove the expiration, make it permanent

TTLs are Redis's built-in mechanism for automatic cleanup — essential for caches (data should expire and be refreshed) and session stores (a session should end automatically after inactivity) without application code needing to run a separate cleanup job.

Eviction policies when memory fills up

Because Redis is fundamentally memory-bound, a policy for what happens when the configured memory limit is reached matters:

maxmemory 2gb
maxmemory-policy allkeys-lru

Policy	Behavior
`noeviction`	Reject writes once memory is full — safest for non-cache use cases where data loss is unacceptable
`allkeys-lru`	Evict the least-recently-used key across all keys — most common choice for pure caching workloads
`volatile-lru`	Evict least-recently-used, but only among keys that have a TTL set
`allkeys-random`	Evict a random key
`volatile-ttl`	Evict the key with the nearest expiration time first

For a workload using Redis purely as a cache, allkeys-lru (or the more recent, generally preferred allkeys-lfu, which tracks access frequency rather than just recency) is the typical choice — it lets Redis behave like a proper cache, automatically discarding the least valuable data under memory pressure rather than rejecting new writes or crashing.

4. Persistence

Redis is an in-memory store, but it's not necessarily volatile — it offers two mechanisms (usable independently or together) for persisting data to disk, in case the process restarts or crashes.

RDB (Redis Database) snapshots

save 900 1      # snapshot if at least 1 key changed in 900 seconds
save 300 10     # snapshot if at least 10 keys changed in 300 seconds

RDB takes periodic point-in-time snapshots of the entire dataset to disk — compact, fast to restart from, but any writes since the last snapshot are lost if the process crashes before the next one completes.

AOF (Append-Only File)

appendonly yes
appendfsync everysec   # fsync to disk roughly once per second

AOF logs every write operation to an append-only file, replayed on restart to reconstruct the dataset — offers much better durability (at most ~1 second of potential data loss with everysec, or none at all with the more conservative but slower always setting) at the cost of a larger file and slightly slower write throughput than RDB alone.

Choosing a persistence strategy

Pure cache, fully rebuildable from a source of truth (a database) — persistence may not matter at all; losing the cache on restart just means a temporary wave of cache misses, not data loss.
Session store or anything where losing recent writes matters — AOF (or RDB + AOF together, which is a common, robust combination) is worth the overhead.
Redis as a primary data store for genuinely important data (less common, but done for certain workloads) — both RDB and AOF, tuned conservatively, plus replication (Section 8), become essential rather than optional.

5. Common Patterns

Cache-aside (lazy loading)

public async Task<Product?> GetProductAsync(int id)
{
    var cacheKey = $"product:{id}";
    var cached = await _db.StringGetAsync(cacheKey);
    if (cached.HasValue)
        return JsonSerializer.Deserialize<Product>(cached!);

    var product = await _repository.GetByIdAsync(id); // cache miss — fetch from the source of truth
    if (product is not null)
        await _db.StringSetAsync(cacheKey, JsonSerializer.Serialize(product), TimeSpan.FromMinutes(10));

    return product;
}

This is the most common caching pattern by far: check the cache first, fall back to the real data source on a miss, and populate the cache for next time. Simple, and it self-heals — if Redis is cleared or a key expires, the next request just re-fetches from the source.

Write-through

public async Task UpdateProductAsync(Product product)
{
    await _repository.UpdateAsync(product);          // write to the source of truth
    var cacheKey = $"product:{product.Id}";
    await _db.StringSetAsync(cacheKey, JsonSerializer.Serialize(product), TimeSpan.FromMinutes(10)); // keep cache in sync
}

Updating the cache at the same time as the underlying data source keeps them in sync immediately, avoiding a stale-read window — at the cost of every write needing to touch both systems, and more complexity if the cache write fails after the database write succeeds.

Session storage

builder.Services.AddStackExchangeRedisCache(options =>
{
    options.Configuration = "localhost:6379";
});
builder.Services.AddSession();

Storing session state in Redis instead of in-process memory is what makes ASP.NET Core session state work correctly across multiple server instances (see the SignalR and Background Services guides in this series for the related "don't assume single-instance state" theme) — any instance behind a load balancer can read the same session data, since it lives centrally in Redis rather than in one specific server's memory.

Rate limiting

public async Task<bool> IsAllowedAsync(string clientId, int maxRequests, TimeSpan window)
{
    var key = $"ratelimit:{clientId}";
    var count = await _db.StringIncrementAsync(key);
    if (count == 1)
        await _db.KeyExpireAsync(key, window); // set the window's expiry only on the first request

    return count <= maxRequests;
}

This fixed-window rate limiter relies on Redis's atomic INCR — even under highly concurrent requests, the counter increments correctly without a race condition, since each INCR call is atomic at the Redis server itself, not something the client needs to coordinate.

Rank/leaderboard queries

ZADD game:leaderboard 12500 "player42"
ZREVRANK game:leaderboard "player42"         # this player's current rank
ZREVRANGE game:leaderboard 0 9 WITHSCORES     # top 10 players

As covered in Section 2, sorted sets make leaderboard-style features nearly trivial to implement correctly and efficiently, something that would require a more involved query (and likely an index) in a relational database for comparable performance at scale.

6. Pub/Sub and Streams

Pub/Sub: fire-and-forget messaging

var subscriber = redis.GetSubscriber();
await subscriber.SubscribeAsync(RedisChannel.Literal("notifications"), (channel, message) =>
{
    Console.WriteLine($"Received: {message}");
});

await subscriber.PublishAsync(RedisChannel.Literal("notifications"), "New order placed");

Redis Pub/Sub delivers a published message to every currently-subscribed client — but messages aren't persisted or queued: a subscriber that wasn't connected when a message was published simply never receives it. This makes Pub/Sub well-suited for ephemeral, best-effort notifications (like fanning out a SignalR message across multiple server instances via a backplane, as covered in this series' SignalR guide) but a poor fit for anything requiring guaranteed delivery.

Streams: durable, replayable messaging

XADD orders:stream * orderId 1001 status "created"
XGROUP CREATE orders:stream order-processors 0
XREADGROUP GROUP order-processors consumer1 COUNT 10 STREAMS orders:stream >
XACK orders:stream order-processors 1234567890-0

Unlike Pub/Sub, Redis Streams persist messages (an append-only log, much like Kafka conceptually) and support consumer groups — multiple consumers splitting the work of processing a stream, with acknowledgment (XACK) tracking what's been successfully processed, and the ability for a new consumer joining later to catch up on messages it missed. This makes Streams a genuinely durable, at-least-once delivery messaging option, positioned between Pub/Sub's fire-and-forget simplicity and a dedicated message broker's (Kafka, RabbitMQ, Azure Service Bus) full feature set.

7. Distributed Locking

The problem

When multiple application instances need to coordinate exclusive access to a resource (ensuring only one instance runs a particular scheduled job, or processes a specific piece of work at a time), a distributed lock — held in a shared system all instances can see — solves it, as covered more generally in this series' Background Services guide.

A basic Redis lock

var lockKey = "lock:nightly-report";
var lockValue = Guid.NewGuid().ToString(); // unique per lock attempt, used to safely release only your own lock

bool acquired = await _db.StringSetAsync(lockKey, lockValue, TimeSpan.FromMinutes(5), When.NotExists);
if (acquired)
{
    try
    {
        await RunNightlyReportAsync();
    }
    finally
    {
        // Release only if we still hold the lock (avoids releasing someone else's lock after our TTL expired)
        var script = "if redis.call('get', KEYS[1]) == ARGV[1] then return redis.call('del', KEYS[1]) else return 0 end";
        await _db.ScriptEvaluateAsync(script, new RedisKey[] { lockKey }, new RedisValue[] { lockValue });
    }
}

SET key value NX (set only if the key doesn't already exist) atomically acquires the lock; the Lua script on release ensures you only delete the lock if it's still the one you acquired (protecting against accidentally releasing a different instance's lock that was acquired after your original one expired).

Redlock: the more rigorous algorithm

For scenarios where lock correctness genuinely matters under adversarial conditions (network partitions, clock drift), Redis's creator proposed the Redlock algorithm — acquiring the lock across a majority of independent Redis instances rather than trusting a single instance. Redlock is somewhat contested in distributed systems circles (there's legitimate, publicly documented debate about the strength of its guarantees under certain failure conditions), so for most practical application-level locking needs (like the scheduled-job coordination example above), a single well-configured, highly-available Redis instance with the pattern shown above is often pragmatically sufficient — reach for a rigorously reviewed Redlock client library specifically when the cost of an occasional double-execution would be genuinely severe.

8. High Availability and Scaling

Replication

# On a replica
replicaof primary-host 6379

A Redis primary can have one or more replicas that continuously receive a stream of write commands, providing both read scaling (replicas can serve read traffic) and a standby copy of the data in case the primary fails.

Redis Sentinel: automated failover

Sentinel is a separate, lightweight process (typically run in a group of 3+ for quorum) that monitors a primary/replica setup and automatically promotes a replica to primary if the current primary becomes unreachable — clients connect through Sentinel (or are configured to discover the current primary via it) rather than hardcoding a single Redis address, so a failover doesn't require manual reconfiguration.

Redis Cluster: horizontal scaling

redis-cli --cluster create node1:6379 node2:6379 node3:6379 node4:6379 node5:6379 node6:6379 --cluster-replicas 1

For datasets or throughput needs beyond what a single Redis instance's memory/CPU can handle, Redis Cluster shards data across multiple nodes automatically, using a hash-slot mechanism (16,384 fixed hash slots, distributed across the cluster's nodes) to determine which node owns which keys — conceptually similar to the sharding approaches covered in this series' MongoDB/Cosmos DB guide, adapted to Redis's specific data model.

Managed Redis offerings

Azure Cache for Redis and Amazon ElastiCache for Redis provide fully managed Redis deployments handling replication, failover, and patching automatically — for most production workloads, a managed offering removes a meaningful amount of the operational complexity described in this section, similar to the tradeoff described for managed databases elsewhere in this series.

9. .NET Integration

StackExchange.Redis: the standard client

var redis = ConnectionMultiplexer.Connect("localhost:6379");
builder.Services.AddSingleton<IConnectionMultiplexer>(redis);

ConnectionMultiplexer is designed to be created once and shared for the lifetime of the application (typically registered as a singleton) — it manages an efficient pool of connections internally and multiplexes many concurrent commands over them, so creating a new one per request is a common and costly anti-pattern, not a safer default.

public class ProductCache
{
    private readonly IDatabase _db;
    public ProductCache(IConnectionMultiplexer redis) => _db = redis.GetDatabase();

    public async Task<Product?> GetAsync(int id)
    {
        var value = await _db.StringGetAsync($"product:{id}");
        return value.HasValue ? JsonSerializer.Deserialize<Product>(value!) : null;
    }
}

`IDistributedCache` abstraction

builder.Services.AddStackExchangeRedisCache(options =>
{
    options.Configuration = builder.Configuration.GetConnectionString("Redis");
    options.InstanceName = "MyApp:";
});

public class ProductService
{
    private readonly IDistributedCache _cache;
    public ProductService(IDistributedCache cache) => _cache = cache;

    public async Task<Product?> GetProductAsync(int id)
    {
        var cached = await _cache.GetStringAsync($"product:{id}");
        if (cached is not null)
            return JsonSerializer.Deserialize<Product>(cached);

        var product = await _repository.GetByIdAsync(id);
        if (product is not null)
        {
            await _cache.SetStringAsync($"product:{id}", JsonSerializer.Serialize(product),
                new DistributedCacheEntryOptions { AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(10) });
        }
        return product;
    }
}

IDistributedCache is ASP.NET Core's abstraction over distributed caching providers — coding against this interface (rather than directly against StackExchange.Redis's IDatabase) means swapping the underlying cache provider later doesn't require touching application code, at the cost of a somewhat narrower feature surface than Redis's full native API (no direct access to sorted sets, pub/sub, or other Redis-specific structures through this abstraction — those still require IConnectionMultiplexer directly).

`HybridCache` (newer .NET pattern)

.NET 9 introduced HybridCache, which layers a fast in-process memory cache in front of a distributed cache like Redis — reducing network round trips for very hot keys while still benefiting from Redis's shared, cross-instance consistency for everything else, and it also solves the "cache stampede" problem (many concurrent requests all missing the cache simultaneously and hammering the source of truth) by coordinating concurrent loads for the same key.

10. Caching Pitfalls

Pitfall	Why it hurts	Better approach
No expiration on cached data	Stale data served indefinitely, or unbounded memory growth	Always set a sensible TTL, even a generous one
Caching data that changes very frequently	Cache is constantly stale or provides little benefit	Cache more stable data, or use short TTLs/write-through for volatile data
Cache stampede (many requests miss simultaneously, all hit the source at once)	Sudden load spike on the underlying database exactly when it's most vulnerable	Use request coalescing (`HybridCache`, or a simple in-flight-request lock) so only one request refreshes the cache while others wait
Treating Redis as a guaranteed-durable primary datastore without configuring persistence	Data loss on restart if RDB/AOF isn't configured appropriately	Explicitly decide and configure a persistence strategy matching the data's actual durability needs (Section 4)
Using a new `ConnectionMultiplexer` per request	Connection overhead, exhausted connection limits under load	Register one `ConnectionMultiplexer` as a singleton for the app's lifetime
Storing very large values in a single key	Slower serialization/network transfer, potential impact on Redis single-threaded latency for that operation	Consider whether the value should be broken into a hash or multiple keys, or cached in a more targeted, smaller shape
No monitoring of memory usage/eviction rate	Silent degradation as increasingly relevant data gets evicted under memory pressure	Monitor `used_memory`, eviction counts, and hit/miss ratio; alert on sustained high eviction rates

11. Redis vs. Alternatives

	Redis	Memcached	In-process memory cache
Data structures	Rich (strings, hashes, lists, sets, sorted sets, streams)	Simple key-value only	Whatever .NET objects you want, natively
Persistence	Optional (RDB/AOF)	None — pure cache, data lost on restart always	N/A — tied to process lifetime
Shared across instances	Yes	Yes	No — each instance has its own separate cache
Pub/sub, streams, locking	Yes	No	No
Typical use	General-purpose cache, sessions, leaderboards, rate limiting, messaging	Simple, high-throughput pure caching with minimal features needed	Very hot, small, non-shared data (e.g., `HybridCache`'s local tier)

Redis's broader feature set (data structures beyond simple key-value, persistence options, pub/sub, streams) is why it's become the default choice over Memcached for most new projects, even when the immediate need is "just caching" — the additional capabilities are frequently useful later without needing to introduce a second system. An in-process memory cache remains valuable specifically for very hot, small, per-instance data where the overhead of a network round trip to Redis isn't worth paying, which is exactly the gap HybridCache's two-tier design is meant to fill.

Quick Reference Table

Concept	Purpose
Strings/`INCR`	Simple values, atomic counters
Hashes	Object-like data with independently updatable fields
Lists	Ordered collections, simple queues, recent-items feeds
Sets	Unique membership, set operations (union/intersect)
Sorted sets	Leaderboards, ranked/priority data
TTL/`EXPIRE`	Automatic key expiration for caches and sessions
`maxmemory-policy`	Eviction behavior once memory is full
RDB / AOF	Snapshot vs. append-log persistence strategies
Pub/Sub	Ephemeral, non-persisted fan-out messaging
Streams	Durable, replayable messaging with consumer groups
`SET ... NX`	Atomic basis for simple distributed locks
Sentinel	Automated primary/replica failover
Redis Cluster	Horizontal sharding across many nodes
`IDistributedCache` / `HybridCache`	.NET abstractions over Redis for caching

Conclusion

Redis's enduring popularity comes from a genuinely rare combination: it's extremely fast (in-memory, simple single-threaded command execution), but it's not just a fast key-value store — its rich set of purpose-built data structures turns problems that would otherwise need custom application logic (leaderboards, rate limiting, activity feeds, distributed locks) into a handful of well-understood, atomic commands. Layer in optional persistence, pub/sub and streams for messaging, and Sentinel/Cluster for high availability and scale, and it's easy to see why Redis has become the default answer to "we need this to be fast and shared across instances" for such a wide range of application needs.

The main discipline required to use it well isn't really about Redis itself — it's about being deliberate: choosing an appropriate TTL and eviction policy for cached data, deciding explicitly whether persistence matters for a given use case, and picking the right data structure for the actual access pattern rather than defaulting to strings and JSON serialization for everything, even when a hash, set, or sorted set would fit the problem far more naturally and efficiently.

Found this useful? Feel free to star the repo, open an issue with corrections, or share the caching bug that taught you to respect TTLs and cache-stampede protection.

Cosmos DB and MongoDB: NoSQL Databases for Flexible Schemas and Scale

Rhuturaj Takle — Sun, 19 Jul 2026 13:53:44 +0000

Cosmos DB and MongoDB: NoSQL Databases for Flexible Schemas and Scale

A practical guide to two of the most widely used NoSQL databases — Azure Cosmos DB and MongoDB — covering the document data model, schema flexibility, partitioning/sharding, consistency models, indexing, and how to choose between them (and against a relational database).

Introduction
Why NoSQL, and Why Document Databases Specifically
The Document Data Model
MongoDB
Azure Cosmos DB
Partitioning and Sharding
Consistency Models
Indexing
Data Modeling: Embedding vs. Referencing
Querying
.NET Integration
Cosmos DB vs. MongoDB vs. a Relational Database
Quick Reference Table
Conclusion

Introduction

Cosmos DB and MongoDB are both document databases — instead of rows in fixed-schema tables, they store data as flexible, JSON-like documents that can vary in shape from one record to the next, and both are built from the ground up for horizontal scale across many machines rather than scaling a single powerful server.

{
  "_id": "42",
  "name": "Ada Lovelace",
  "email": "ada@example.com",
  "addresses": [
    { "type": "home", "city": "London" },
    { "type": "work", "city": "Cambridge" }
  ],
  "preferences": { "newsletter": true, "theme": "dark" }
}

That single document — a customer with a variable number of addresses and an open-ended preferences object — would take a join across two or three normalized tables in a relational database. In a document database, it's one record, fetched in one read.

MongoDB is the open-source, self-hostable (or MongoDB Atlas-managed) document database that popularized this model at scale. Azure Cosmos DB is Microsoft's globally distributed, fully managed database service — natively a document database (its "Core (SQL) API"), but also capable of speaking MongoDB's own wire protocol, Cassandra's, Gremlin's (graph), and Azure Table Storage's, all through the same underlying engine.

1. Why NoSQL, and Why Document Databases Specifically

Schema flexibility

Relational databases require every row in a table to share the same columns (with NULL for anything not applicable). Document databases let each document have its own shape — useful when different records genuinely have different attributes (a product catalog spanning wildly different product types), or when the schema is expected to evolve frequently and you don't want a migration for every new optional field.

{ "type": "book", "title": "...", "author": "...", "isbn": "..." }
{ "type": "laptop", "brand": "...", "cpu": "...", "ramGb": 16 }

Both documents can live in the same collection/container without either needing columns the other doesn't use.

Horizontal scale by design

Where a relational database's default scaling story is "get a bigger server" (vertical scaling), document databases are architected from the start to shard data across many servers (horizontal scaling) — a single logical collection can span dozens or hundreds of physical machines, each holding a subset of the data, with the database routing queries to the right shard(s) automatically.

The tradeoff: less enforced structure, fewer built-in cross-document guarantees

The flexibility document databases provide is also their biggest risk if unmanaged — with no schema enforced by the database itself, inconsistent document shapes can accumulate unnoticed, and multi-document transactional guarantees (while now supported to varying degrees in both systems) are historically weaker and often deliberately avoided for performance reasons, pushing more correctness responsibility onto application-level data modeling discipline (Section 8) than a relational schema and foreign keys would.

2. The Document Data Model

Collections/containers instead of tables

MongoDB groups documents into collections within a database.
Cosmos DB groups documents into containers within a database.

Neither requires documents in the same collection/container to share a fixed schema — though in practice, most real applications keep documents within one collection reasonably consistent in shape, even without the database enforcing it, simply because application code needs to reason about them predictably.

`_id` as the primary key

{ "_id": ObjectId("507f1f77bcf86cd799439011"), "name": "Wireless Mouse" }

Both systems use _id as the document's unique identifier. MongoDB defaults to a generated ObjectId (a 12-byte value encoding a timestamp and other bits) if you don't supply one; Cosmos DB's Core (SQL) API accepts any string you choose as id, and requires it alongside a partition key (Section 5) for efficient routing.

Nested structures are native, not an afterthought

{
  "orderId": "1001",
  "customer": { "name": "Ada Lovelace", "email": "ada@example.com" },
  "items": [
    { "productId": "42", "quantity": 2, "price": 29.99 },
    { "productId": "17", "quantity": 1, "price": 89.99 }
  ],
  "total": 149.97
}

An entire order — customer info and line items included — lives as one document, retrievable in a single read with no joins, which is exactly the shape that tends to make document databases fast for read-heavy, aggregate-oriented access patterns (see Section 8 for when this is and isn't the right modeling choice).

3. MongoDB

Basic CRUD

db.products.insertOne({ name: "Wireless Mouse", price: 29.99, category: "electronics" });

db.products.find({ category: "electronics", price: { $lt: 50 } });

db.products.updateOne(
  { _id: ObjectId("...") },
  { $set: { price: 24.99 }, $push: { tags: "sale" } }
);

db.products.deleteOne({ _id: ObjectId("...") });

The aggregation pipeline

MongoDB's aggregation pipeline is its primary tool for anything beyond simple filtering — a sequence of stages, each transforming the documents flowing through it, conceptually similar to a LINQ method chain or a SQL query broken into explicit steps:

db.orders.aggregate([
  { $match: { status: "completed" } },
  { $unwind: "$items" },
  { $group: {
      _id: "$items.productId",
      totalRevenue: { $sum: { $multiply: ["$items.price", "$items.quantity"] } },
      unitsSold: { $sum: "$items.quantity" }
  }},
  { $sort: { totalRevenue: -1 } },
  { $limit: 10 }
]);

This computes top-10 products by revenue across all completed orders — $match filters, $unwind flattens the items array into one document per line item, $group aggregates by product, and $sort/$limit finish it off. The pipeline model makes complex, multi-step transformations explicit and composable, similar in spirit to a series of chained LINQ operators.

Multi-document transactions

const session = client.startSession();
session.startTransaction();
try {
  await accounts.updateOne({ _id: fromId }, { $inc: { balance: -100 } }, { session });
  await accounts.updateOne({ _id: toId }, { $inc: { balance: 100 } }, { session });
  await session.commitTransaction();
} catch (error) {
  await session.abortTransaction();
  throw error;
} finally {
  session.endSession();
}

MongoDB has supported full ACID multi-document transactions since version 4.0 (within a replica set) and across sharded clusters since 4.2 — a significant evolution from MongoDB's early reputation as offering only single-document atomicity, though multi-document transactions still carry a real performance cost relative to well-modeled single-document operations, and good data modeling (Section 8) that minimizes the need for them remains the preferred first choice where it fits the access pattern.

Deployment options

Self-hosted — run MongoDB yourself, on VMs, bare metal, or in containers/Kubernetes.
MongoDB Atlas — MongoDB's own fully managed, multi-cloud DBaaS (available on Azure, AWS, and GCP), handling provisioning, scaling, backups, and patching.

4. Azure Cosmos DB

A multi-model, globally distributed database service

Cosmos DB's defining architectural pitch is global distribution with configurable consistency, available transparently regardless of which API surface you use to talk to it.

CosmosClient client = new CosmosClient(connectionString);
Container container = client.GetContainer("StoreDb", "Products");

var product = new Product { Id = "42", Name = "Wireless Mouse", Price = 29.99m, CategoryId = "electronics" };
await container.CreateItemAsync(product, new PartitionKey(product.CategoryId));

var query = container.GetItemQueryIterator<Product>(
    new QueryDefinition("SELECT * FROM c WHERE c.categoryId = @category")
        .WithParameter("@category", "electronics"));

while (query.HasMoreResults)
{
    foreach (var item in await query.ReadNextAsync())
        Console.WriteLine(item.Name);
}

Multiple APIs, one underlying engine

API	What it looks like to clients
Core (SQL) API	Native document model, queried with a SQL-like dialect (see below)
API for MongoDB	Speaks the MongoDB wire protocol — many MongoDB drivers/tools work against it with minimal changes
API for Cassandra	Speaks the Cassandra Query Language (CQL) wire protocol
API for Gremlin	Graph queries via the Apache TinkerPop Gremlin traversal language
API for Table	Compatible with Azure Table Storage's API, with more throughput/global distribution options

This means an application already using MongoDB drivers can, in principle, point them at Cosmos DB's MongoDB API endpoint instead of a MongoDB server — though compatibility is close but not 100% identical to native MongoDB feature-for-feature, so this migration path needs real validation, not an assumption of a drop-in swap.

SQL-like query language (Core API)

SELECT c.name, c.price
FROM c
WHERE c.categoryId = "electronics" AND c.price < 50
ORDER BY c.price DESC

Despite the SQL-like syntax, this queries a schema-less document container, not a relational table — c refers to each document ("item") in the container, and the query engine navigates nested properties directly (c.address.city, for instance) without needing a join.

Global distribution

// Configuring multi-region writes in Bicep/ARM, conceptually:
// locations: [ { locationName: "East US", failoverPriority: 0 }, { locationName: "West Europe", failoverPriority: 1 } ]

A Cosmos DB account can be replicated to any number of Azure regions worldwide with a few clicks/lines of IaC, with multi-region writes available so applications in different regions can write locally with low latency, and Cosmos DB handles propagating and (per the chosen consistency model, Section 6) reconciling those writes globally.

Request Units (RUs): the throughput/cost currency

Cosmos DB doesn't bill by raw CPU/memory the way a VM-based database does — every operation (read, write, query) costs a measured number of Request Units, and you provision (or let autoscale handle) RU/s capacity that your workload's operations draw from:

az cosmosdb sql container create --account-name my-account --database-name StoreDb \
    --name Products --partition-key-path "/categoryId" --throughput 400

This RU-based cost model is genuinely distinctive versus most other databases — it's precise (you can see exactly how many RUs any given query consumed) but requires learning a new mental model for both performance tuning and cost estimation, since an inefficient query design (e.g., a cross-partition scan, Section 5) shows up directly as a higher RU cost, not just as "slow."

5. Partitioning and Sharding

MongoDB: sharding

sh.enableSharding("StoreDb");
sh.shardCollection("StoreDb.orders", { customerId: "hashed" });

MongoDB distributes a sharded collection across multiple shards based on a chosen shard key — a hashed shard key (as above) spreads writes evenly across shards, while a ranged shard key groups related values together (useful when range queries on the shard key are common), at the cost of potential "hot shard" issues if writes cluster around a narrow range of key values.

Cosmos DB: partitioning

--partition-key-path "/categoryId"

Every Cosmos DB container requires a partition key chosen at creation time (and, in modern Cosmos DB, changeable later via a partition key migration, though it's still a meaningful decision to get right upfront) — it determines how documents are distributed across physical partitions, and it's the single most consequential data modeling decision in a Cosmos DB design.

Choosing a good partition/shard key

A good key:

Distributes writes evenly — avoid a key with a small number of very common values (e.g., a status field with only 3 possible values) which concentrates load on a few partitions/shards.
Matches your most common query filter — queries that include the partition key can be routed directly to the relevant partition(s); queries that omit it become expensive cross-partition (fan-out) queries that touch every partition.
Has high enough cardinality — a key like customerId (many distinct values) usually distributes better than something coarse like region (few distinct values, each potentially very large).

-- Efficient: includes the partition key, routed to a single partition
SELECT * FROM c WHERE c.categoryId = "electronics" AND c.price < 50

-- Expensive: no partition key filter, fans out across every partition
SELECT * FROM c WHERE c.price < 50

Getting the partition key wrong is one of the most common and most expensive-to-fix mistakes in both systems — it's usually a foundational, largely one-time decision (though both systems have added tooling to ease repartitioning after the fact), so it's worth real design effort upfront rather than an afterthought.

6. Consistency Models

MongoDB: read concerns and write concerns

db.orders.find().readConcern("majority");   // reads data acknowledged by a majority of replica set members
db.orders.insertOne(doc, { writeConcern: { w: "majority" } }); // waits for majority acknowledgment before returning

MongoDB lets you tune consistency/durability per-operation via read concern (how consistent/durable the data you're reading must be) and write concern (how many replicas must acknowledge a write before it's considered successful) — trading latency against consistency/durability guarantees on a per-query basis rather than a single fixed database-wide setting.

Cosmos DB: five named consistency levels

Cosmos DB makes this tradeoff unusually explicit with five distinct, well-defined consistency levels, chosen at the account level (with the ability to override to a weaker level per-request):

Level	Guarantee	Typical use
Strong	Linearizable — reads always see the latest committed write	Financial/critical data where staleness is unacceptable, at the cost of higher latency, especially across regions
Bounded Staleness	Reads lag writes by at most K versions or T time	Global apps needing a predictable, bounded staleness guarantee
Session (default)	A single client session always sees its own writes ("read your own writes")	The most common practical choice — good UX guarantee without the cost of global strong consistency
Consistent Prefix	Reads never see out-of-order writes, but may be stale	Workloads where seeing a consistent history matters more than recency
Eventual	No ordering guarantee at all, lowest latency	Workloads that can tolerate any staleness in exchange for maximum performance

This spectrum — from the strictest, most expensive guarantee down to the loosest, cheapest one — is a direct, practical expression of the CAP theorem tradeoff that every distributed database has to make; Cosmos DB just makes the choice explicit and tunable rather than hiding it behind a single fixed default.

The common thread

Both databases embrace the same underlying reality: in a globally distributed system, strict consistency and low latency are in direct tension, and rather than picking one tradeoff for you, both give you the tools to choose deliberately, per workload or even per query, based on what that specific data actually needs.

7. Indexing

MongoDB

db.products.createIndex({ categoryId: 1, price: -1 }); // compound index
db.products.createIndex({ name: "text", description: "text" }); // text search index
db.orders.createIndex({ location: "2dsphere" }); // geospatial index

MongoDB indexes work much like relational database indexes conceptually — compound indexes for common multi-field filters, plus specialized index types for text search and geospatial queries.

Cosmos DB: automatic indexing by default

{
  "indexingPolicy": {
    "automatic": true,
    "includedPaths": [{ "path": "/*" }],
    "excludedPaths": [{ "path": "/description/*" }]
  }
}

Cosmos DB's most distinctive indexing behavior: every property of every document is indexed automatically by default, with no need to declare indexes explicitly the way you would in MongoDB or a relational database. This is convenient (queries on arbitrary fields work efficiently out of the box) but has a real cost — indexing more data than you actually query on increases write RU cost and storage. Excluding paths you never query on (like a large, rarely-filtered description field above) is a common, worthwhile optimization once query patterns are well understood, rather than leaving the default "index everything" policy in place indefinitely for a mature, high-volume container.

8. Data Modeling: Embedding vs. Referencing

This is the central data modeling decision in both databases, and it's fundamentally different from relational normalization.

Embedding: nest related data in one document

{
  "orderId": "1001",
  "customer": { "name": "Ada Lovelace", "email": "ada@example.com" },
  "items": [ { "productId": "42", "quantity": 2 } ]
}

Embed when: the nested data is almost always read together with the parent, doesn't grow unboundedly (an order's line items are naturally bounded; a product's all-time reviews are not), and doesn't need to be queried/updated independently very often.

Referencing: keep related data in a separate document, linked by ID

// orders collection
{ "orderId": "1001", "customerId": "42", "items": [{ "productId": "42", "quantity": 2 }] }

// customers collection
{ "customerId": "42", "name": "Ada Lovelace", "email": "ada@example.com" }

Reference when: the related data is large, grows unboundedly (a customer's complete order history), is shared/reused across many parent documents (many orders reference the same customer), or needs to be updated independently without touching every document that references it.

The core tradeoff

Embedding optimizes for read performance (fetch everything in one request) at the cost of some data duplication and update complexity (updating a customer's email means updating it in every embedded copy, if you'd embedded customer data into every order). Referencing optimizes for update simplicity and bounded document size at the cost of needing a second lookup (there's no server-side JOIN the way a relational database has one, though both systems offer limited lookup/join-like operations — MongoDB's $lookup aggregation stage, for instance).

There's no universally correct answer — the right choice depends entirely on the specific access pattern for that specific relationship, which is why document database schema design is often described as "designing around your queries" rather than designing around the data's inherent structure the way relational normalization does.

9. Querying

MongoDB query syntax

db.products.find(
  { category: "electronics", price: { $gte: 10, $lte: 100 } },
  { name: 1, price: 1, _id: 0 }
).sort({ price: -1 }).limit(20);

Cosmos DB SQL syntax

SELECT c.name, c.price
FROM c
WHERE c.category = "electronics" AND c.price BETWEEN 10 AND 100
ORDER BY c.price DESC
OFFSET 0 LIMIT 20

Both support rich filtering, projection, sorting, and pagination — the syntax differs (MongoDB's query documents vs. Cosmos DB's SQL-like dialect), but the underlying capability and performance characteristics (efficient when the partition/shard key is included, expensive when it isn't) are conceptually similar.

Change feeds: reacting to data changes

// Cosmos DB change feed processor
var processor = container.GetChangeFeedProcessorBuilder<Product>("productChanges", HandleChangesAsync)
    .WithInstanceName("processor1")
    .WithLeaseContainer(leaseContainer)
    .Build();
await processor.StartAsync();

// MongoDB change streams
const changeStream = db.collection('orders').watch();
changeStream.on('change', (change) => {
    console.log('Change detected:', change);
});

Both systems provide a way to subscribe to a real-time stream of document changes (inserts, updates, deletes) — useful for triggering downstream processing (updating a search index, invalidating a cache, sending a notification) without polling, conceptually similar to a database-level event stream feeding into the background-worker patterns covered elsewhere in this series.

10. .NET Integration

Cosmos DB SDK

builder.Services.AddSingleton(new CosmosClient(connectionString));

public class ProductRepository
{
    private readonly Container _container;
    public ProductRepository(CosmosClient client) =>
        _container = client.GetContainer("StoreDb", "Products");

    public async Task<Product?> GetByIdAsync(string id, string categoryId)
    {
        try
        {
            var response = await _container.ReadItemAsync<Product>(id, new PartitionKey(categoryId));
            return response.Resource;
        }
        catch (CosmosException ex) when (ex.StatusCode == HttpStatusCode.NotFound)
        {
            return null;
        }
    }
}

Note that a point read (ReadItemAsync, given both the id and partition key) is the cheapest, fastest possible operation against Cosmos DB — always prefer it over a query when you already know both values, rather than querying WHERE c.id = @id even though the result would be the same.

MongoDB .NET Driver

var client = new MongoClient(connectionString);
var database = client.GetDatabase("StoreDb");
var products = database.GetCollection<Product>("products");

var results = await products.Find(p => p.Category == "electronics" && p.Price < 50)
    .SortByDescending(p => p.Price)
    .Limit(20)
    .ToListAsync();

The official MongoDB .NET driver supports a strongly-typed LINQ-like query builder (as above) as well as raw BSON/JSON-style filter documents for more complex queries the typed builder doesn't express as cleanly.

Entity Framework Core providers

Both ecosystems have EF Core providers (Microsoft.EntityFrameworkCore.Cosmos and MongoDB's own EF Core provider), letting you use familiar DbContext/LINQ patterns against a document database — though it's worth knowing this is a leakier abstraction than EF Core over a relational database: partition key design, RU cost awareness, and embedding/referencing decisions don't go away just because the API looks familiar, and teams that treat a document database exactly like a relational one through EF Core often end up fighting the model rather than benefiting from it.

11. Cosmos DB vs. MongoDB vs. a Relational Database

Aspect	Azure Cosmos DB	MongoDB	Relational (SQL Server/PostgreSQL)
Schema	Flexible, per-document	Flexible, per-document	Fixed, enforced per-table
Scaling model	Horizontal by design, RU-based provisioning	Horizontal via sharding	Primarily vertical, with read replicas for read scaling
Global distribution	Native, first-class, multi-region writes	Possible (Atlas Global Clusters), less deeply native	Possible but more involved (geo-replication add-ons)
Consistency	Five explicit, tunable levels	Tunable via read/write concerns	Strong by default (ACID transactions)
Multi-document transactions	Supported within a partition natively; cross-partition more limited	Fully supported since 4.0/4.2	Full, mature ACID transaction support — the traditional strength
Query language	SQL-like (Core API), or native MongoDB/Cassandra/Gremlin via other APIs	MongoDB Query Language + aggregation pipeline	SQL
Best for	Globally distributed apps, variable/evolving schemas, Azure-native architectures	Flexible-schema apps, teams wanting portability across clouds/on-prem	Complex relationships, strong consistency needs, heavy reporting/joins

When NoSQL document databases are the better fit

Data is naturally document-shaped (a user profile, a product catalog with varied attributes, an order with nested line items) and rarely needs ad-hoc joins across unrelated entities.
Schema needs to evolve frequently without formal migrations blocking every deploy.
The application needs to scale horizontally to a degree a single relational server (even with read replicas) can't comfortably reach.
Global, multi-region low-latency access is a core requirement (Cosmos DB in particular is purpose-built for this).

When a relational database is still the better fit

Data has many genuine relationships that need to be queried flexibly and joined in ways that aren't known upfront (ad-hoc reporting, complex multi-entity queries).
Strong, straightforward multi-row/multi-table transactional guarantees are central to correctness (financial ledgers, inventory systems with strict consistency needs).
The team's existing tooling, reporting stack, and expertise are deeply relational, and the workload doesn't have a compelling reason to diverge from that.

Many real systems use both — a relational database for core transactional data with well-understood relationships, and a document database for specific subsystems (product catalogs, user profiles, event/activity logs, session state) where flexible schema or massive read scale genuinely matters more than relational rigor.

Quick Reference Table

Concept	Cosmos DB	MongoDB
Unit of storage	Item, in a container	Document, in a collection
Partitioning key	Chosen at container creation, determines physical distribution	Shard key, chosen when sharding a collection
Query language	SQL-like (Core API); also Mongo/Cassandra/Gremlin via other APIs	MongoDB Query Language + aggregation pipeline
Consistency	5 explicit levels: Strong → Bounded Staleness → Session → Consistent Prefix → Eventual	Tunable read/write concerns per operation
Billing model	Request Units (RU/s), provisioned or autoscale	Compute/storage-based (self-hosted) or Atlas tier-based
Default indexing	Every property indexed automatically	Explicit index creation required
Managed offering	Native Azure service	MongoDB Atlas (multi-cloud)
Change notifications	Change Feed	Change Streams

Conclusion

Cosmos DB and MongoDB both embrace the same core document-database philosophy — flexible schemas, horizontal scale by design, and modeling around access patterns rather than rigid normalization — but express it differently. Cosmos DB leans into globally distributed, multi-region, tunable-consistency architecture as a first-class, built-in concern with an unusually explicit RU-based cost model; MongoDB leans into a mature, widely adopted query and aggregation model with strong multi-cloud portability via Atlas.

Choosing between them (and between either and a relational database) comes down to the same question that recurs throughout this series: what does the actual access pattern and consistency requirement of this specific workload need, not which technology is generically "better." Document databases earn their complexity when data is naturally document-shaped and scale/schema-flexibility genuinely matter; a well-modeled relational database remains the right, simpler choice for a great deal of application data that doesn't actually need either of those things.

Found this useful? Feel free to star the repo, open an issue with corrections, or share the partition key decision you wish you'd gotten right the first time.

PostgreSQL: The Open-Source Database Known for Advanced Features and Reliability

Rhuturaj Takle — Sat, 18 Jul 2026 14:30:00 +0000

PostgreSQL: The Open-Source Database Known for Advanced Features and Reliability

A practical guide to PostgreSQL — the open-source object-relational database prized for standards compliance, extensibility, and rock-solid reliability, covering MVCC, advanced data types, indexing, extensions, replication, and how it compares to SQL Server.

Introduction
Why PostgreSQL's Reputation Is Well-Earned
MVCC and Transactions
Advanced Data Types
Indexing
Extensions
Full-Text Search
Query Performance Tuning
Replication and High Availability
VACUUM and Autovacuum
Security
PostgreSQL with .NET (Npgsql and EF Core)
PostgreSQL vs. SQL Server
Quick Reference Table
Conclusion

Introduction

PostgreSQL ("Postgres") is a free, open-source object-relational database that has spent over three decades building a reputation for strict standards compliance, exceptional reliability, and a genuinely extensible architecture that lets it grow new capabilities — JSON documents, geospatial data, full-text search — without becoming a different kind of database to operate. It's not "SQL Server's free cousin" so much as its own distinct design philosophy: correctness and extensibility first, with performance engineering built carefully around those constraints rather than the other way around.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT now()
);

SELECT name, price FROM products WHERE metadata @> '{"color": "red"}' ORDER BY price DESC;

That query — filtering directly on a JSON field with a native containment operator — hints at what makes PostgreSQL distinctive: relational rigor and NoSQL-style flexibility coexisting in the same engine, the same table, the same transaction.

1. Why PostgreSQL's Reputation Is Well-Earned

Standards compliance

PostgreSQL tracks the SQL standard closely and implements a large share of it faithfully — window functions, CTEs (including recursive ones), full JOIN syntax variety, and strict type checking that catches mistakes many other databases would silently coerce around. This matters in practice: SQL written against Postgres tends to be more portable to other standards-compliant systems, and fewer subtle behaviors surprise you later.

Reliability as a design principle

PostgreSQL's MVCC architecture (Section 2), write-ahead logging, and decades of conservative, correctness-first engineering have earned it a reputation for simply not losing or corrupting data under real-world failure conditions — crashes, power loss, concurrent load — which is precisely the property a database exists to guarantee.

Extensibility as a first-class feature

Unlike most databases where "advanced features" mean waiting for the vendor to ship them, PostgreSQL is built around an extension architecture that lets entirely new capabilities — geospatial types and operations (PostGIS), specialized indexing strategies, foreign data wrappers to query other databases as if they were local tables — be added without forking or replacing the core engine (see Section 5).

It's genuinely free and open

No licensing costs, no edition tiers gating core features behind a paywall — the full feature set is available regardless of deployment scale, which has made it the default choice for a huge share of new application development, especially outside the Microsoft ecosystem.

2. MVCC and Transactions

Multi-Version Concurrency Control

PostgreSQL's defining architectural choice is MVCC (Multi-Version Concurrency Control): instead of readers blocking writers (or vice versa) the way lock-based systems often do, every row can have multiple physical versions, and each transaction sees a consistent snapshot of the data as of when it started — readers never block writers, and writers never block readers, only concurrent writers to the same row contend with each other.

BEGIN;
SELECT * FROM accounts WHERE id = 1; -- sees a consistent snapshot
-- meanwhile, another transaction can freely UPDATE this same row without blocking this SELECT
COMMIT;

This is fundamentally different from how many lock-based systems behave by default, and it's a large part of why PostgreSQL handles mixed read/write workloads gracefully without the reader/writer contention that plagues naive locking-based designs.

Transaction isolation levels

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... statements ...
COMMIT;

Isolation level	Behavior in PostgreSQL
`READ UNCOMMITTED`	Treated identically to `READ COMMITTED` — Postgres has no true dirty-read behavior
`READ COMMITTED` (default)	Each statement sees a fresh snapshot as of when that statement began
`REPEATABLE READ`	The entire transaction sees one snapshot as of when the transaction began
`SERIALIZABLE`	Full serializable guarantees via Serializable Snapshot Isolation (SSI) — detects and aborts transactions that would violate true serial execution

Notably, READ UNCOMMITTED is accepted as valid syntax but behaves exactly like READ COMMITTED — PostgreSQL's MVCC design means a genuine "dirty read" (seeing another transaction's uncommitted changes) simply isn't possible in its architecture, regardless of the isolation level requested.

Handling serialization failures

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... conflicting concurrent transaction detected ...
-- ERROR: could not serialize access due to concurrent update
ROLLBACK;
-- application retries the transaction

At the SERIALIZABLE level, PostgreSQL may abort a transaction that would violate serializability even without an explicit conflicting write to the same row — application code using this isolation level needs retry logic for serialization failures, similar in spirit to handling deadlock victims in other databases.

3. Advanced Data Types

This is one of PostgreSQL's most distinctive strengths — a genuinely rich type system well beyond the basics most relational databases offer.

JSONB

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    payload JSONB NOT NULL
);

INSERT INTO events (payload) VALUES ('{"type": "click", "user_id": 42, "tags": ["mobile", "checkout"]}');

SELECT payload->>'type' AS event_type, payload->'user_id' AS user_id
FROM events
WHERE payload @> '{"type": "click"}'
  AND payload->'tags' ? 'checkout';

JSONB (binary JSON) stores JSON in a decomposed, indexable binary format — unlike the plain JSON type (which stores an exact text copy and re-parses on every access), JSONB supports efficient indexing (Section 4) and rich operators (@> containment, ? key existence, ->/->> field extraction) that make querying semi-structured data feel like a natural part of SQL rather than a bolted-on afterthought.

Arrays

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[]
);

INSERT INTO products (name, tags) VALUES ('Wireless Mouse', ARRAY['electronics', 'accessories', 'sale']);

SELECT * FROM products WHERE 'sale' = ANY(tags);
SELECT * FROM products WHERE tags && ARRAY['sale', 'clearance']; -- overlap operator

Native array columns avoid a separate join table for simple, small multi-valued attributes — genuinely useful for tags, categories, or small sets of related IDs where a full normalized join table would be overkill.

Range types

CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    during TSRANGE NOT NULL,
    EXCLUDE USING GIST (room_id WITH =, during WITH &&) -- prevents overlapping bookings for the same room
);

Range types (INT4RANGE, TSRANGE, DATERANGE, etc.) natively represent "from X to Y" values, with operators for overlap, containment, and adjacency — the EXCLUDE constraint above enforces "no two bookings for the same room can overlap in time" at the database level, something that would otherwise require awkward application-level locking or check logic.

Geospatial data (via PostGIS)

CREATE EXTENSION postgis;

CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name TEXT,
    location GEOGRAPHY(POINT)
);

SELECT name FROM stores
WHERE ST_DWithin(location, ST_MakePoint(-73.9857, 40.7484)::geography, 5000); -- within 5km

PostGIS turns PostgreSQL into a full-featured geospatial database — distance calculations, containment queries ("which stores are within this delivery zone polygon"), and spatial indexing, all through the same SQL interface as everything else.

`UUID`, `ENUM`, and composite types

CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    status order_status NOT NULL DEFAULT 'pending'
);

A native ENUM type enforces a fixed set of valid values at the schema level (rejecting anything outside the defined set), which is often a cleaner, more self-documenting alternative to a plain string column with application-level validation alone.

4. Indexing

B-tree: the default, and usually the right choice

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

B-tree indexes handle equality and range queries (=, <, >, BETWEEN, sorting) efficiently and are the right default for the overwhelming majority of indexing needs — PostgreSQL creates these automatically for primary keys and unique constraints.

GIN indexes for JSONB, arrays, and full-text search

CREATE INDEX idx_events_payload ON events USING GIN (payload);
CREATE INDEX idx_products_tags ON products USING GIN (tags);

GIN (Generalized Inverted Index) indexes are built for "does this container hold this value" queries — the @> containment and array overlap operators used above rely on GIN indexes to avoid scanning every row; without one, every JSONB/array containment query is a full table scan.

GiST indexes for geometric and range types

CREATE INDEX idx_bookings_during ON bookings USING GIST (during);

GiST (Generalized Search Tree) indexes support range types, geometric data (used heavily by PostGIS), and the EXCLUDE constraint pattern shown in Section 3 — a different indexing structure optimized for "overlap"/"nearest neighbor"-style queries that a B-tree can't express efficiently.

Partial indexes

CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

A partial index only covers rows matching a condition — if most queries against a status column only ever care about pending orders (a small fraction of the total table), indexing just that subset produces a much smaller, faster index than indexing every row regardless of status.

Expression indexes

CREATE INDEX idx_users_lower_email ON users (LOWER(email));

SELECT * FROM users WHERE LOWER(email) = 'ada@example.com'; -- uses the index

An index can be built on the result of an expression, not just a raw column — enabling case-insensitive lookups (or any other computed predicate) to still benefit from an index seek rather than falling back to a full scan, addressing the same "function wrapped around a column defeats the index" problem covered in the SQL Server guide's SARGable-predicates section.

5. Extensions

PostgreSQL's extension mechanism is a genuinely load-bearing architectural feature, not a bolt-on plugin system — many capabilities that would be core, monolithic features in other databases are implemented as optional extensions here.

CREATE EXTENSION IF NOT EXISTS pg_trgm;    -- trigram matching for fuzzy text search
CREATE EXTENSION IF NOT EXISTS postgis;    -- geospatial types and functions
CREATE EXTENSION IF NOT EXISTS pgcrypto;   -- cryptographic functions
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- query performance statistics
CREATE EXTENSION IF NOT EXISTS vector;     -- pgvector: vector similarity search for embeddings/AI workloads

Notable extensions worth knowing

pg_trgm — trigram-based fuzzy string matching, enabling % similarity searches and better LIKE/ILIKE index support.
pg_stat_statements — tracks execution statistics for every distinct query shape run against the server, invaluable for finding your slowest or most frequently executed queries without external tooling.
pgcrypto — cryptographic hashing and encryption functions directly in SQL.
pgvector — stores vector embeddings and performs similarity search (cosine distance, L2, inner product) directly in Postgres, letting AI/ML applications keep embeddings alongside their regular relational data rather than standing up a separate dedicated vector database.
postgres_fdw (foreign data wrapper) — query tables in a different Postgres database (or other systems entirely, via other FDWs) as if they were local tables, joining across data sources transparently.

This extension model is a large part of why PostgreSQL has kept pace with newer specialized database categories (document stores, vector databases, time-series databases) without fragmenting into a different product for each — often the extension approach means one operational database, one backup strategy, one set of credentials, instead of several specialized systems bolted together.

6. Full-Text Search

PostgreSQL includes a genuinely capable full-text search engine built in, without needing an external system like Elasticsearch for many common use cases.

ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || body);

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC;

to_tsvector converts text into a searchable, language-aware representation (handling stemming — "running" matches "run" — and stripping stop words like "the"/"a"), to_tsquery parses a search expression with boolean operators (&, |, !), and ts_rank scores results by relevance — a surprisingly complete search feature set for many applications' needs, especially combined with a generated column and trigger (or simply a GENERATED ALWAYS AS column in modern Postgres) to keep the search vector automatically in sync as content changes.

For search needs beyond what built-in full-text search covers well — faceted search UIs, typo-tolerant search-as-you-type, very large-scale text corpora — a dedicated search engine like Elasticsearch or a hosted equivalent is still the better fit, but it's worth confirming built-in full-text search is actually insufficient before adding that operational complexity.

7. Query Performance Tuning

`EXPLAIN ANALYZE`

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer_id on orders  (cost=0.29..8.31 rows=1 width=64) (actual time=0.021..0.023 rows=3 loops=1)
  Index Cond: (customer_id = 42)
Planning Time: 0.112 ms
Execution Time: 0.041 ms

EXPLAIN ANALYZE actually runs the query and shows both the planner's estimates and the real, measured execution numbers side by side — a large discrepancy between estimated and actual row counts is one of the most reliable signals that statistics are stale or a query needs restructuring.

Sequential scans vs. index scans

Like any relational database, a sequential scan (reading every row) is appropriate when a query genuinely needs most of the table, but is a red flag when a highly selective filter isn't using an available index — check that the relevant column is actually indexed, that the predicate is written in an index-usable form (Section 4's expression index note applies here too), and that statistics are current.

`ANALYZE` and statistics

ANALYZE orders;

Like SQL Server, PostgreSQL's query planner relies on column statistics (data distribution, cardinality estimates) to choose good execution plans — ANALYZE refreshes these, and autovacuum (Section 9) normally keeps them reasonably fresh automatically, but manually running ANALYZE after a large bulk load is a common, worthwhile step when a plan looks unexpectedly poor.

`pg_stat_statements` for finding slow queries

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This extension (Section 5) is usually the fastest path to "what's actually slow in production" — ranking queries by cumulative time spent, rather than relying on guesswork or waiting for a specific slow query to be reported.

Connection pooling

PostgreSQL's per-connection process model means each connection carries meaningful memory and process overhead — under high connection counts (common with connection-per-request web application patterns), a pooler like PgBouncer sitting between the application and PostgreSQL becomes important, multiplexing many client connections onto a smaller number of actual database connections rather than letting connection count scale linearly and unboundedly with application instance count.

8. Replication and High Availability

Streaming replication

# postgresql.conf on the primary
wal_level = replica
max_wal_senders = 5

# On the replica
primary_conninfo = 'host=primary-host port=5432 user=replicator'

PostgreSQL's built-in streaming replication continuously ships write-ahead log (WAL) changes from a primary to one or more replicas, which can serve read-only queries (offloading read traffic) and stand ready for failover if the primary goes down.

Synchronous vs. asynchronous replication

# On the primary
synchronous_standby_names = 'replica1'

Asynchronous replication (the default) — the primary commits immediately, replicas catch up shortly after; a small window of potential data loss exists if the primary fails before a replica catches up, but write latency is unaffected.
Synchronous replication — the primary waits for at least one replica to confirm receipt before considering a transaction committed, eliminating that data-loss window at the cost of added write latency.

Logical replication

CREATE PUBLICATION my_publication FOR TABLE orders, customers;
-- on the subscriber:
CREATE SUBSCRIPTION my_subscription CONNECTION 'host=primary dbname=storedb' PUBLICATION my_publication;

Unlike streaming (physical) replication, which replicates an entire database byte-for-byte, logical replication replicates specific tables at the row level — enabling selective replication, replication between different major PostgreSQL versions during an upgrade, or feeding specific tables into a separate analytics database without shipping the entire dataset.

Managed high availability

Self-managed HA typically layers a failover management tool (Patroni is the most widely used) on top of streaming replication to handle automatic leader election and failover — PostgreSQL's own replication is the data-shipping mechanism, but it doesn't include automatic failover orchestration out of the box the way some other systems' built-in HA features do. Managed cloud offerings (Azure Database for PostgreSQL, Amazon RDS/Aurora PostgreSQL) handle this orchestration for you, trading some configuration control for substantially reduced operational burden — much like the Azure SQL Database tradeoff described in the SQL Server guide.

9. VACUUM and Autovacuum

Why MVCC needs cleanup

Because MVCC (Section 2) keeps multiple row versions around rather than overwriting in place, an UPDATE or DELETE doesn't actually free space immediately — it marks the old row version as no longer visible to new transactions, but that space isn't reclaimed until VACUUM runs. Without regular vacuuming, table bloat accumulates: the table grows larger on disk than its live data would suggest, and query performance degrades as more dead rows have to be skipped over.

VACUUM orders;         -- reclaims space for reuse, doesn't return it to the OS
VACUUM FULL orders;    -- rewrites the table, actually shrinking disk usage — but takes an exclusive lock
VACUUM ANALYZE orders; -- reclaims space and refreshes statistics in one pass

Autovacuum

# postgresql.conf
autovacuum = on
autovacuum_vacuum_scale_factor = 0.1  -- trigger after ~10% of the table is dead rows

Autovacuum runs automatically in the background, and for most workloads its defaults are reasonable — but high-churn tables (very frequent updates/deletes on a large table) sometimes need tuned, more aggressive autovacuum settings specific to that table, since the default thresholds are calibrated for a general workload, not necessarily your busiest table's actual churn rate.

Transaction ID wraparound

PostgreSQL's MVCC implementation uses a finite transaction ID counter that wraps around eventually — VACUUM (specifically its "freezing" of old row versions) is also what prevents this wraparound from becoming a real problem. In a healthy, properly autovacuumed database this is entirely invisible; in a database where autovacuum has been disabled or is badly misconfigured, it's a genuine, serious operational risk worth knowing exists, even if it's rarely encountered in practice.

10. Security

Role-based access control

CREATE ROLE app_readonly;
GRANT CONNECT ON DATABASE storedb TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

CREATE ROLE app_user LOGIN PASSWORD 'StrongP@ssw0rd!';
GRANT app_readonly TO app_user;

PostgreSQL's role system unifies "users" and "groups" into one concept (ROLE) — roles can be granted to other roles, letting you build reusable permission sets (like app_readonly above) and assign them to individual login roles, rather than managing permissions on each user individually.

Row-Level Security

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant_id')::int);

Conceptually identical to SQL Server's Row-Level Security — the database engine itself filters which rows a query can see based on session context, enforced consistently regardless of which application or ad-hoc query touches the table.

`pgcrypto` for column-level encryption

CREATE EXTENSION pgcrypto;

INSERT INTO users (email, ssn_encrypted) VALUES ('ada@example.com', pgp_sym_encrypt('123-45-6789', 'encryption-key'));

SELECT pgp_sym_decrypt(ssn_encrypted, 'encryption-key') FROM users WHERE email = 'ada@example.com';

For sensitive columns needing encryption beyond disk-level encryption, pgcrypto provides symmetric and asymmetric encryption functions directly callable from SQL — key management (where the encryption key itself lives and how it's protected) is the harder problem here and typically involves a dedicated secrets manager rather than embedding the key in application config.

`SECURITY DEFINER` functions

CREATE FUNCTION get_customer_summary(customer_id INT)
RETURNS TABLE(name TEXT, total_spent NUMERIC)
SECURITY DEFINER
AS $$
    SELECT c.name, SUM(o.total) FROM customers c JOIN orders o ON o.customer_id = c.id
    WHERE c.id = customer_id GROUP BY c.name;
$$ LANGUAGE sql;

A SECURITY DEFINER function runs with the privileges of the function's owner rather than the caller — useful for giving a limited, controlled way to access data a role couldn't query directly, similar in spirit to using a view as a security boundary, but with more control over exactly what's exposed.

11. PostgreSQL with .NET (Npgsql and EF Core)

Npgsql: the ADO.NET driver

await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();

await using var command = new NpgsqlCommand("SELECT id, name, price FROM products WHERE category_id = @categoryId", connection);
command.Parameters.AddWithValue("categoryId", categoryId);

await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine(reader.GetString(1));
}

Npgsql is the standard, actively maintained ADO.NET driver for PostgreSQL — the foundation both raw ADO.NET code and EF Core's PostgreSQL provider build on.

Entity Framework Core with Npgsql.EntityFrameworkCore.PostgreSQL

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(connectionString));

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public Dictionary<string, string> Metadata { get; set; } = new(); // mapped to a jsonb column
}

modelBuilder.Entity<Product>()
    .Property(p => p.Metadata)
    .HasColumnType("jsonb");

The Npgsql EF Core provider maps many PostgreSQL-specific types directly — jsonb, arrays, ranges — so application code can work with natural .NET types (Dictionary<string,string>, List<T>, etc.) while EF Core handles the PostgreSQL-specific storage and querying underneath.

Using PostgreSQL-specific features from LINQ

var products = await dbContext.Products
    .Where(p => p.Tags.Contains("sale")) // translates to a PostgreSQL array containment check
    .ToListAsync();

The Npgsql provider translates a meaningful subset of PostgreSQL-specific operators (array containment, JSON operators, full-text search) into idiomatic LINQ where possible — for anything the provider doesn't translate, raw SQL via FromSqlRaw/FromSqlInterpolated remains available as an escape hatch.

12. PostgreSQL vs. SQL Server

Aspect	PostgreSQL	SQL Server
Licensing	Free, open-source, no edition tiers gating features	Free (Express/Developer) up to paid Enterprise tiers with feature differences
Concurrency model	MVCC — readers never block writers	Lock-based by default, with `READ COMMITTED SNAPSHOT`/`SNAPSHOT` isolation available for similar MVCC-style behavior
Extensibility	Deep — extensions add genuinely new capabilities (PostGIS, pgvector)	More limited — relies more on built-in feature releases from Microsoft
JSON support	Mature, indexable (`JSONB` + GIN indexes), rich operators	Functional (`JSON_VALUE`/`JSON_MODIFY`) but less deeply indexable than `JSONB`
Full-text search	Solid built-in support (`tsvector`/`tsquery`)	Built-in but generally considered less capable than PostgreSQL's
Platform	Cross-platform, Linux-first heritage	Cross-platform since SQL Server 2017, but Windows-first heritage
.NET tooling	Excellent via Npgsql, slightly less "first-party" feel than SQL Server's native integration	Deepest, most seamless .NET/Visual Studio integration (as the Microsoft-native choice)
Managed cloud options	Azure Database for PostgreSQL, Amazon RDS/Aurora PostgreSQL, many others	Azure SQL Database/Managed Instance, primarily an Azure/Microsoft-ecosystem story
Typical strengths	Extensibility, advanced data types, cost (free), standards compliance	Deep Windows/.NET tooling integration, enterprise support/SLAs, mature BI stack (SSRS/SSAS)

Practical guidance

Already deep in the Microsoft/.NET ecosystem, want the most seamless tooling, or need SQL Server-specific enterprise features (SSRS, SSAS, deep Windows AD integration)? → SQL Server remains a strong, well-supported choice.
Want to avoid licensing costs, need advanced data types or extensibility (geospatial, vector search, rich JSON), or are building on Linux/cross-platform infrastructure? → PostgreSQL is very often the better-fitting default for new projects, regardless of the application layer being .NET.
Neither answer is really wrong — both are mature, reliable, well-supported databases; the .NET ecosystem (via Npgsql and the EF Core provider) supports PostgreSQL just as fluently as SQL Server for the vast majority of application needs.

Quick Reference Table

Concept	Purpose
MVCC	Readers and writers don't block each other via row versioning
`JSONB`	Indexable, binary-stored JSON with rich query operators
Array types	Native multi-valued columns without a join table
Range types + `EXCLUDE`	Native "from X to Y" values with overlap-prevention constraints
GIN index	Efficient containment queries on JSONB, arrays, full-text search
GiST index	Range/geometric data, nearest-neighbor and overlap queries
Extensions	Load new capabilities (PostGIS, pgvector, pg_trgm) into the same engine
`EXPLAIN ANALYZE`	Compare estimated vs. actual execution plan behavior
`pg_stat_statements`	Ranks queries by cumulative execution time for tuning
Streaming replication	Physical, byte-for-byte replication for HA and read scaling
Logical replication	Row-level, selective replication (specific tables, cross-version)
`VACUUM`/autovacuum	Reclaims space from dead row versions, prevents table bloat
Row-Level Security	Engine-enforced per-row access filtering

Conclusion

PostgreSQL earns its reputation through a combination of genuinely rare traits: strict standards compliance that keeps SQL predictable and portable, an MVCC architecture that sidesteps a whole category of reader/writer contention by design, and an extension mechanism that lets it absorb entirely new categories of data and querying (geospatial, vector search, advanced full-text search) without becoming a different, more complex product to operate. Combined with being fully open-source and well-supported across every major cloud provider, it's become the default first choice for a large and growing share of new application development.

For .NET developers specifically, the tooling story (Npgsql, the EF Core provider) is mature enough that choosing PostgreSQL over SQL Server rarely means giving up developer productivity — the decision more often comes down to which advanced features and cost/licensing model best fit the application and organization, not a fundamental gap in .NET support.

Found this useful? Feel free to star the repo, open an issue with corrections, or share the extension that solved a problem you didn't expect your database to solve.

SQL Server: Microsoft's Relational Database for Structured Application Data

Rhuturaj Takle — Fri, 17 Jul 2026 14:29:38 +0000

SQL Server: Microsoft's Relational Database for Structured Application Data

A practical guide to Microsoft SQL Server — covering core architecture, T-SQL essentials, indexing, transactions and isolation levels, query performance tuning, high availability, security, and how it fits alongside modern .NET applications.

Introduction
Editions and Deployment Options
Core Architecture
T-SQL Essentials
Indexing
Transactions and Isolation Levels
Query Performance Tuning
Stored Procedures, Views, and Functions
High Availability and Disaster Recovery
Security
SQL Server with .NET (Entity Framework Core and Dapper)
Monitoring and Maintenance
Quick Reference Table
Conclusion

Introduction

SQL Server is Microsoft's relational database management system (RDBMS) — a mature, ACID-compliant engine for storing and querying structured data, tightly integrated with the .NET ecosystem and widely used for everything from small internal applications to large-scale, mission-critical enterprise systems. It's been continuously developed since the early 1990s, and modern versions run cross-platform on Windows, Linux, and in containers — no longer a Windows-only product.

CREATE TABLE Products (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(200) NOT NULL,
    Price DECIMAL(10,2) NOT NULL,
    CategoryId INT NOT NULL,
    CreatedAt DATETIME2 DEFAULT SYSUTCDATETIME()
);

SELECT Name, Price FROM Products WHERE CategoryId = 3 ORDER BY Price DESC;

This guide covers what matters most for building and operating applications on SQL Server: the language (T-SQL), the concepts that determine correctness and performance (indexes, transactions, isolation levels), and the operational concerns (HA/DR, security, monitoring) that separate a database that works from one that's production-ready.

1. Editions and Deployment Options

Edition/Option	Description	Typical use
Express	Free, with size/resource caps (10 GB database size limit)	Small apps, learning, lightweight production workloads
Developer	Free, full feature set, licensed for non-production use only	Development and testing
Standard	Core production features, capped resource limits	Small-to-mid production workloads
Enterprise	Full feature set, no resource caps, advanced HA/security features	Large-scale, mission-critical production systems
Azure SQL Database	Fully managed PaaS, per-database or elastic pool billing	Cloud-native apps wanting zero server management
Azure SQL Managed Instance	Fully managed PaaS with near-complete SQL Server surface-area compatibility	Lift-and-shift migrations needing full SQL Server feature parity
SQL Server on Linux/containers	Same engine, runs on Linux or in Docker	Cross-platform deployments, containerized environments

Choosing a deployment model

Self-managed SQL Server (on a VM or on-prem) gives full control but means you own patching, backups, and HA configuration yourself.
Azure SQL Database removes almost all operational overhead (patching, backups, HA are automatic) but has some feature differences from a full SQL Server instance (e.g., certain cross-database operations work differently).
Azure SQL Managed Instance is the middle ground — near-full SQL Server compatibility (SQL Agent, cross-database queries, linked servers) with much of the operational burden still managed by Azure.

For most new cloud-native applications, Azure SQL Database is the lowest-friction starting point; Managed Instance exists specifically for migrating existing SQL Server workloads that rely on instance-level features Azure SQL Database doesn't support.

2. Core Architecture

Databases, schemas, and objects

CREATE DATABASE StoreDb;
GO
USE StoreDb;
GO
CREATE SCHEMA Sales;
GO
CREATE TABLE Sales.Orders (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT NOT NULL,
    OrderDate DATETIME2 NOT NULL
);

A SQL Server instance can host multiple databases, each with its own schemas, tables, users, and permissions — schemas (Sales.Orders vs. dbo.Orders) provide a namespace for organizing objects and controlling permissions at a finer grain than the whole database.

Data pages and the transaction log

SQL Server stores data in fixed-size 8 KB pages, and every modification (insert, update, delete) is first written to the transaction log before the actual data pages are updated — this write-ahead logging is what makes crash recovery and transactional durability possible: if the server crashes mid-operation, the log has enough information to redo or undo incomplete transactions on restart.

Memory and the buffer pool

Frequently accessed data pages are cached in memory in the buffer pool, avoiding disk I/O for repeated reads — one of the biggest levers for query performance is ensuring the buffer pool has enough memory to hold your working set of frequently accessed data, since a cache miss means a comparatively slow disk read.

3. T-SQL Essentials

T-SQL (Transact-SQL) is Microsoft's extension of standard SQL, adding procedural constructs, error handling, and SQL-Server-specific functions on top of the ANSI SQL foundation.

Basic querying

SELECT p.Name, p.Price, c.Name AS CategoryName
FROM Products p
INNER JOIN Categories c ON p.CategoryId = c.Id
WHERE p.Price > 50
ORDER BY p.Price DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

OFFSET/FETCH is T-SQL's standard pagination syntax (added in SQL Server 2012), functionally similar to LIMIT/OFFSET in other database systems.

Common table expressions (CTEs)

WITH RecentOrders AS (
    SELECT CustomerId, COUNT(*) AS OrderCount, SUM(Total) AS TotalSpent
    FROM Orders
    WHERE OrderDate >= DATEADD(MONTH, -3, SYSUTCDATETIME())
    GROUP BY CustomerId
)
SELECT c.Name, ro.OrderCount, ro.TotalSpent
FROM RecentOrders ro
JOIN Customers c ON c.Id = ro.CustomerId
WHERE ro.TotalSpent > 1000;

CTEs improve readability for multi-step queries by naming intermediate result sets, and can also express recursive queries (e.g., traversing a hierarchical category tree):

WITH CategoryTree AS (
    SELECT Id, Name, ParentId, 0 AS Depth
    FROM Categories
    WHERE ParentId IS NULL
    UNION ALL
    SELECT c.Id, c.Name, c.ParentId, ct.Depth + 1
    FROM Categories c
    JOIN CategoryTree ct ON c.ParentId = ct.Id
)
SELECT * FROM CategoryTree ORDER BY Depth;

Window functions

SELECT
    Name,
    Price,
    CategoryId,
    RANK() OVER (PARTITION BY CategoryId ORDER BY Price DESC) AS PriceRank,
    AVG(Price) OVER (PARTITION BY CategoryId) AS CategoryAvgPrice
FROM Products;

Window functions (RANK, ROW_NUMBER, LAG/LEAD, aggregate functions with OVER) compute values across a set of related rows without collapsing them into a single grouped result the way GROUP BY does — essential for things like "rank each product within its category" or "compare this row to the previous row" without a self-join.

`MERGE` for upsert logic

MERGE INTO Inventory AS target
USING (SELECT @ProductId AS ProductId, @Quantity AS Quantity) AS source
ON target.ProductId = source.ProductId
WHEN MATCHED THEN
    UPDATE SET target.Quantity = target.Quantity + source.Quantity
WHEN NOT MATCHED THEN
    INSERT (ProductId, Quantity) VALUES (source.ProductId, source.Quantity);

MERGE combines insert/update/delete logic into one statement based on whether a matching row exists — useful for upsert patterns, though it has some well-documented edge-case quirks under concurrent execution, so many teams prefer explicit IF EXISTS ... UPDATE ELSE INSERT logic (or an UPDATE followed by a conditional INSERT on @@ROWCOUNT = 0) for simple upserts where correctness under concurrency matters most.

JSON support

SELECT Id, Name, JSON_VALUE(Metadata, '$.color') AS Color
FROM Products
WHERE JSON_VALUE(Metadata, '$.color') = 'red';

UPDATE Products SET Metadata = JSON_MODIFY(Metadata, '$.color', 'blue') WHERE Id = 42;

SQL Server can store and query JSON stored in NVARCHAR columns via JSON_VALUE, JSON_QUERY, and JSON_MODIFY — useful for semi-structured data that doesn't need its own relational schema, without needing a separate document database for that portion of your data model.

4. Indexing

Indexes are the single most impactful lever for query performance — and one of the easiest things to get subtly wrong.

Clustered vs. nonclustered indexes

-- The primary key typically creates a clustered index automatically
CREATE TABLE Orders (
    Id INT IDENTITY(1,1) PRIMARY KEY, -- clustered index on Id by default
    CustomerId INT NOT NULL,
    OrderDate DATETIME2 NOT NULL
);

-- A nonclustered index for a common query pattern
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId ON Orders (CustomerId) INCLUDE (OrderDate);

A clustered index determines the physical order data is stored on disk — there can only be one per table, since data can only be physically sorted one way.
A nonclustered index is a separate structure pointing back to the underlying data — a table can have many, each optimized for a different query pattern.

Covering indexes

The INCLUDE clause above adds OrderDate to the index's leaf level without making it part of the index key — this means a query filtering on CustomerId and selecting OrderDate can be satisfied entirely from the index itself, without a further lookup into the underlying table (a "covering index"), which is often meaningfully faster.

Composite index column order matters

CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date ON Orders (CustomerId, OrderDate);

This index efficiently supports queries filtering on CustomerId alone, or on CustomerId AND OrderDate together, but not queries filtering on OrderDate alone — the leading column of a composite index is what the query optimizer can use for a seek; put the column with the most selective, most commonly-filtered-alone value first.

Finding missing (and unused) indexes

-- Missing index suggestions based on actual query patterns SQL Server has observed
SELECT * FROM sys.dm_db_missing_index_details;

-- Indexes that exist but are rarely or never used (candidates for removal)
SELECT OBJECT_NAME(s.object_id) AS TableName, i.name AS IndexName, s.user_seeks, s.user_scans, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID();

Indexes aren't free — every additional index slows down INSERT/UPDATE/DELETE operations (since the index must be maintained alongside the data) and consumes storage, so periodically reviewing and removing indexes with high user_updates but near-zero user_seeks/user_scans is as important as adding indexes for slow queries.

5. Transactions and Isolation Levels

ACID and explicit transactions

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2;

IF @@ERROR = 0
    COMMIT TRANSACTION;
ELSE
    ROLLBACK TRANSACTION;

A transaction groups multiple statements so they either all succeed together or all fail together (atomicity) — essential any time a logical operation touches more than one row or table and partial completion would leave data inconsistent (like the balance transfer above).

Isolation levels

SQL Server supports several isolation levels that trade consistency guarantees against concurrency (how much operations can run in parallel without blocking each other):

Isolation level	Prevents dirty reads	Prevents non-repeatable reads	Prevents phantom reads	Notes
`READ UNCOMMITTED`	No	No	No	Reads uncommitted data from other transactions; rarely appropriate
`READ COMMITTED` (default)	Yes	No	No	SQL Server's default — reasonable balance for most workloads
`REPEATABLE READ`	Yes	Yes	No	Locks read rows for the transaction's duration
`SERIALIZABLE`	Yes	Yes	Yes	Strongest guarantee, most blocking/lowest concurrency
`SNAPSHOT`	Yes	Yes	Yes	Uses row versioning instead of locks — high consistency with less blocking

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT * FROM Orders WHERE CustomerId = 42; -- sees a consistent snapshot, not blocked by concurrent writers
COMMIT TRANSACTION;

READ COMMITTED SNAPSHOT (a database-level setting, distinct from the SNAPSHOT isolation level itself) is a commonly enabled option that changes the default READ COMMITTED behavior to use row versioning instead of locking for reads — reducing reader/writer blocking significantly for many typical OLTP workloads, at the cost of some additional tempdb usage for storing row versions.

Deadlocks

-- SQL Server automatically detects deadlocks and kills one transaction (the "victim") to resolve them
-- The victim's application code will see this error and should retry:
-- Msg 1205: Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim.

Application code that runs transactions against SQL Server should generally include retry logic for deadlock victim errors — a deadlock isn't necessarily a bug, it's an expected occasional outcome of concurrent access patterns, and the correct response is usually "retry the transaction," not "treat it as a fatal error."

6. Query Performance Tuning

Execution plans

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM Orders WHERE CustomerId = 42;

Viewing the execution plan (via SSMS's "Include Actual Execution Plan" or SET SHOWPLAN_XML ON) shows exactly how SQL Server intends to satisfy a query — whether it's using an index seek (fast, targeted) versus a table/index scan (reading far more data than strictly necessary), which is usually the first thing to check when a query is slower than expected.

Seeks vs. scans

Index seek — SQL Server navigates directly to the relevant rows using an index, touching only the rows that matter. Generally what you want for selective queries.
Index/table scan — SQL Server reads every row and filters afterward. Fine (even preferable) for queries that genuinely need most of the table, but a red flag for a query that should only need a handful of rows — usually indicates a missing or unusable index for that query's filter predicate.

Parameter sniffing

SQL Server caches a query's execution plan based on the parameter values used the first time it's compiled — if that first execution had unusually skewed parameter values (e.g., a CustomerId with a huge number of orders vs. a typical customer with few), the cached plan may be poorly suited to subsequent, more typical executions. Symptoms: a stored procedure that's fast most of the time but occasionally becomes dramatically slow with no code change. Mitigations include OPTION (RECOMPILE) for genuinely volatile query shapes, or OPTIMIZE FOR hints to guide the optimizer toward a representative parameter value.

`SARGable` predicates

-- ❌ Not SARGable — the function wrapped around the column prevents an index seek
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2026;

-- ✅ SARGable — the column itself is compared directly, allowing an index seek
SELECT * FROM Orders WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01';

"SARGable" (Search ARGument-able) means a predicate is written in a form the query optimizer can use with an index seek. Wrapping an indexed column in a function almost always defeats that index, forcing a scan instead — a small rewrite (as above) often produces a dramatic performance improvement with zero schema changes.

Statistics

UPDATE STATISTICS Orders;

The query optimizer relies on statistics — a sampled understanding of data distribution per column — to estimate row counts and choose a good execution plan. Statistics can become stale after large data changes (bulk loads, mass deletes), leading to poor plan choices; SQL Server updates these automatically under most circumstances, but manually updating statistics after a large data load is a common, worthwhile troubleshooting step when a query's plan looks unexpectedly bad.

7. Stored Procedures, Views, and Functions

Stored procedures

CREATE PROCEDURE GetOrdersByCustomer
    @CustomerId INT
AS
BEGIN
    SELECT Id, OrderDate, Total
    FROM Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC;
END;

EXEC GetOrdersByCustomer @CustomerId = 42;

Stored procedures encapsulate logic server-side, get plan caching benefits, and centralize data-access logic that multiple applications/services might need — though many modern applications favor keeping business logic in application code (C#) and using stored procedures more selectively, for genuinely data-intensive operations that benefit from running close to the data.

Views

CREATE VIEW ActiveCustomerOrders AS
SELECT o.Id, o.OrderDate, o.Total, c.Name AS CustomerName
FROM Orders o
JOIN Customers c ON c.Id = o.CustomerId
WHERE c.IsActive = 1;

Views simplify repeated complex joins into a reusable, named query, and can also serve as a security boundary — granting access to a view without granting direct access to the underlying tables.

Scalar and table-valued functions

CREATE FUNCTION GetCustomerLifetimeValue (@CustomerId INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @Total DECIMAL(10,2);
    SELECT @Total = SUM(Total) FROM Orders WHERE CustomerId = @CustomerId;
    RETURN ISNULL(@Total, 0);
END;

Caution: scalar functions like this one are frequently a hidden performance trap — when referenced in a SELECT over many rows, SQL Server may execute the function once per row rather than set-based, which can be dramatically slower than an equivalent join or a table-valued function (in modern SQL Server versions, "scalar UDF inlining" mitigates this in many common cases, but it's still worth verifying via the execution plan rather than assuming).

8. High Availability and Disaster Recovery

Always On Availability Groups

Always On Availability Groups provide automatic failover between a primary replica and one or more secondary replicas, keeping data synchronized via log shipping — if the primary fails, a secondary is automatically promoted, minimizing downtime for production workloads that can't tolerate extended outages.

CREATE AVAILABILITY GROUP MyAG
FOR DATABASE StoreDb
REPLICA ON
    'SQLNode1' WITH (ENDPOINT_URL = 'TCP://SQLNode1:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC),
    'SQLNode2' WITH (ENDPOINT_URL = 'TCP://SQLNode2:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC);

Secondary replicas can also serve read-only workloads (reporting queries, read-heavy dashboards), offloading that traffic from the primary — a common pattern for separating read and write load without a full CQRS architecture.

Backup strategy

BACKUP DATABASE StoreDb TO DISK = 'D:\Backups\StoreDb_Full.bak' WITH COMPRESSION;
BACKUP LOG StoreDb TO DISK = 'D:\Backups\StoreDb_Log.trn';

A standard production backup strategy layers three types:

Full backups (e.g., weekly) — a complete point-in-time copy.
Differential backups (e.g., daily) — everything changed since the last full backup, faster to restore than replaying every log since the full.
Transaction log backups (e.g., every 15 minutes) — enable point-in-time recovery to almost any moment, not just to the last full/differential backup.

The recovery point objective (RPO) — how much data loss is acceptable — directly determines how frequently you need log backups; the recovery time objective (RTO) — how quickly you must be back online — drives decisions about Availability Groups vs. simpler backup-and-restore-based recovery.

Azure SQL Database: HA built in

For Azure SQL Database, much of this is handled automatically — geo-redundant backups, automatic failover within a region, and (for higher service tiers) built-in zone or geo-replication — trading some configuration control for significantly reduced operational burden.

9. Security

Authentication modes

-- SQL Server login (username/password managed by SQL Server itself)
CREATE LOGIN AppUser WITH PASSWORD = 'StrongP@ssw0rd!';

-- Windows/Entra ID (Azure AD) authentication — generally preferred where available
CREATE LOGIN [DOMAIN\AppServiceAccount] FROM WINDOWS;

Windows Authentication (or Entra ID/Azure AD authentication for Azure SQL) is generally preferred over SQL logins where feasible — it avoids storing and rotating a separate password specifically for database access, and integrates with centralized identity management and auditing.

Principle of least privilege

CREATE USER AppUser FOR LOGIN AppUser;
GRANT SELECT, INSERT, UPDATE ON SCHEMA::Sales TO AppUser;
DENY DELETE ON Sales.Orders TO AppUser; -- explicit deny, even if a broader grant exists elsewhere

Application service accounts should have exactly the permissions their application needs — a reporting service account needs SELECT, not DELETE; an application that never truncates tables shouldn't have ALTER/DROP rights on production schemas, even if it's "just internal."

Protecting against SQL injection

-- ❌ Vulnerable: string concatenation
string query = $"SELECT * FROM Users WHERE Username = '{username}'";

-- ✅ Safe: parameterized query
var command = new SqlCommand("SELECT * FROM Users WHERE Username = @Username", connection);
command.Parameters.AddWithValue("@Username", username);

This is a defense that belongs primarily in application code (always use parameterized queries or an ORM that does so automatically — see Section 10), but it's worth stating explicitly here since SQL injection remains one of the most common and most damaging vulnerabilities in real-world applications, and it's entirely preventable with consistent parameterization discipline.

Transparent Data Encryption and Always Encrypted

-- TDE encrypts data at rest (the physical database files), transparent to queries
ALTER DATABASE StoreDb SET ENCRYPTION ON;

Transparent Data Encryption (TDE) encrypts the database files on disk, protecting against someone gaining access to the raw files (a stolen backup, a compromised storage volume) — but the data is decrypted for any authorized query, so it doesn't protect against a compromised database credential. Always Encrypted goes further, encrypting specific sensitive columns (e.g., SSNs, credit card numbers) such that even a database administrator with full server access can't see the plaintext — only the application, holding the appropriate encryption keys, can decrypt it.

Row-Level Security

CREATE FUNCTION SecurityPredicate(@TenantId INT)
RETURNS TABLE
AS RETURN SELECT 1 AS Result WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS INT);

CREATE SECURITY POLICY TenantFilter
ADD FILTER PREDICATE dbo.SecurityPredicate(TenantId) ON Orders;

Row-Level Security automatically filters which rows a query can see based on context (like the current tenant in a multi-tenant application) — enforced at the database engine level, so it applies consistently regardless of which application or ad-hoc query touches the table, rather than relying on every application query remembering to add a WHERE TenantId = ... clause itself.

10. SQL Server with .NET (Entity Framework Core and Dapper)

Entity Framework Core

public class AppDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .HasIndex(p => p.CategoryId);
    }
}

var products = await dbContext.Products
    .Where(p => p.Price > 50)
    .OrderByDescending(p => p.Price)
    .ToListAsync();

EF Core translates LINQ queries into T-SQL automatically, tracks entity changes, and manages migrations — a strong default for most application data access, especially where developer productivity and maintainability outweigh the need for hand-tuned SQL on every query.

Dapper for performance-critical paths

using var connection = new SqlConnection(connectionString);
var products = await connection.QueryAsync<Product>(
    "SELECT Id, Name, Price FROM Products WHERE CategoryId = @CategoryId",
    new { CategoryId = categoryId });

Dapper is a lightweight micro-ORM that maps query results to objects with minimal overhead, giving you direct control over the exact SQL executed — a common pairing is EF Core for the majority of an application's data access, with Dapper reserved for a small number of genuinely performance-critical, high-volume queries where the fine control over the exact SQL and reduced overhead matters.

Connection resiliency

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString, sqlOptions =>
        sqlOptions.EnableRetryOnFailure(maxRetryCount: 3, maxRetryDelay: TimeSpan.FromSeconds(5), errorNumbersToAdd: null)));

Transient connectivity issues (brief network blips, a database failover event) are common enough in production that automatic retry logic for transient SQL errors is a standard, low-effort resiliency improvement rather than an edge case to handle only if it comes up.

11. Monitoring and Maintenance

Dynamic Management Views (DMVs)

-- Currently running queries and their resource usage
SELECT r.session_id, r.status, r.cpu_time, r.total_elapsed_time, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t;

-- Top queries by cumulative CPU time since last restart
SELECT TOP 10 qs.total_worker_time, qs.execution_count, t.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY qs.total_worker_time DESC;

DMVs are SQL Server's built-in, always-available window into what the engine is actually doing right now — active queries, blocking chains, index usage, wait statistics — and are usually the first stop when diagnosing a live performance issue, well before reaching for external tooling.

Wait statistics

SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

Wait statistics show what queries are actually spending time waiting for — disk I/O, locks, CPU scheduling, network — which is often more diagnostic than raw CPU or query duration alone, since it points directly at the bottleneck category (storage, contention, compute) rather than just confirming that something is slow.

Index maintenance

ALTER INDEX ALL ON Orders REBUILD; -- for heavily fragmented indexes
ALTER INDEX IX_Orders_CustomerId ON Orders REORGANIZE; -- lighter-weight, for moderate fragmentation

Indexes fragment over time as data is inserted, updated, and deleted — periodic maintenance (rebuilding heavily fragmented indexes, reorganizing lightly fragmented ones) keeps query performance from degrading gradually and silently; most production environments schedule this as a recurring maintenance job rather than a manual, reactive task.

Quick Reference Table

Concept	Purpose
Clustered index	Determines physical row storage order; one per table
Nonclustered index (+ `INCLUDE`)	Additional lookup structures, can be "covering" to avoid table lookups
`READ COMMITTED SNAPSHOT`	Reduces reader/writer blocking via row versioning
Execution plan	Shows how SQL Server intends to satisfy a query — the first stop for tuning
SARGable predicates	Predicates written so an index seek is possible
Parameter sniffing	A cached plan poorly suited to atypical parameter values
Always On Availability Groups	Automatic failover + readable secondary replicas
Full/differential/log backups	Layered backup strategy enabling point-in-time recovery
TDE / Always Encrypted	Encryption at rest vs. encryption of specific sensitive columns end-to-end
Row-Level Security	Engine-enforced per-row access filtering (e.g., multi-tenancy)
DMVs	Built-in real-time visibility into queries, waits, and index usage

Conclusion

SQL Server rewards understanding a relatively small set of core mechanics deeply: how indexes actually get used (seeks vs. scans, covering indexes, column order), how isolation levels trade consistency for concurrency, and how to read an execution plan to find out what a query is actually doing rather than guessing. Layered on top of that foundation, HA/DR (Availability Groups, backup strategy) and security (least privilege, encryption, row-level security) turn a working database into a production-ready one.

Whether you're writing raw T-SQL, working through Entity Framework Core, or dropping to Dapper for a hot path, the underlying engine's behavior — how it plans queries, when it blocks, how it recovers from a crash — is the same, and understanding it is what separates code that merely works from code that keeps working reliably as data volume and concurrent load grow.

Found this useful? Feel free to star the repo, open an issue with corrections, or share the missing-index discovery that took a query from seconds to milliseconds.

Cloud Cost Optimization: A Practical Playbook

Rhuturaj Takle — Thu, 16 Jul 2026 16:08:45 +0000

Cloud Cost Optimization: A Practical Playbook

A practical guide to reducing cloud spend without hurting reliability or performance — covering right-sizing, autoscaling, reserved/spot capacity, storage tiering, monitoring and cost visibility, resource cleanup, and how to build cost-awareness into an engineering culture.

Introduction
Why Cloud Costs Get Out of Control
Right-Sizing Compute
Autoscaling Instead of Static Capacity
Pricing Models: On-Demand, Reserved, Savings Plans, and Spot
Storage Optimization
Networking and Data Transfer Costs
Serverless and Consumption-Based Savings
Monitoring, Budgets, and Cost Visibility
Resource Cleanup and Waste Elimination
Tagging and Cost Allocation
Building a Cost-Aware Engineering Culture (FinOps)
Quick Reference Table
Conclusion

Introduction

Cloud bills rarely spike from one dramatic mistake — they creep up from a hundred small, individually reasonable decisions: an oversized VM that made sense during a launch, a load balancer nobody deleted after decommissioning a service, snapshots accumulating quietly for two years. Cloud cost optimization isn't a one-time project; it's an ongoing discipline of matching what you're paying for to what you actually need, applied continuously as systems evolve.

This guide covers the concrete levers — right-sizing, autoscaling, commitment discounts, storage tiering, monitoring, and cleanup — along with the visibility and cultural practices (often called FinOps) that keep costs under control over time rather than requiring periodic, painful cleanup sprints.

1. Why Cloud Costs Get Out of Control

Common root causes

Provisioning for peak, permanently. Sizing a resource for the busiest moment it will ever see, then leaving it running at that size 24/7, even though peak load might occur for a few hours a month.
"We'll clean it up later." Test environments, proof-of-concept resources, and temporary infrastructure that outlive their purpose because nothing forces a decommissioning decision.
No ownership. When cost isn't visible to (or the responsibility of) the engineers actually creating resources, there's no natural feedback loop encouraging efficiency.
Paying on-demand rates for predictable, steady-state workloads that would qualify for a substantial discount under a reserved or committed-use pricing model.
Orphaned resources — a deleted VM's attached disk, an unattached public IP, a load balancer with no healthy targets behind it — that keep incurring charges long after the thing they supported is gone.
Data transfer and storage costs treated as an afterthought compared to the more visible compute line item, even though they can dominate a bill for data-heavy workloads.

The core principle

Cloud pricing is fundamentally about paying for what you provision, not what you use (with serverless/consumption models being the notable exception) — so the central discipline of cost optimization is continuously narrowing the gap between "what's provisioned" and "what's actually needed."

2. Right-Sizing Compute

The problem

A VM or container provisioned with 8 vCPUs and 32 GB of RAM that consistently runs at 15% CPU utilization is paying for capacity it never uses. This is the single most common and most impactful cost inefficiency in most cloud environments.

How to find it

Both major clouds provide built-in tooling that analyzes actual historical utilization and recommends a better-fitting size:

# Azure Advisor cost recommendations
az advisor recommendation list --category Cost

# AWS Compute Optimizer
aws compute-optimizer get-ec2-instance-recommendations --instance-arns arn:aws:ec2:...

These tools look at real CPU, memory, and network utilization over a meaningful window (typically 14+ days) and flag instances that are significantly over- or under-provisioned relative to their actual load — including "this workload would fit comfortably on a smaller/cheaper instance family" recommendations.

Practical right-sizing workflow

Measure first — pull actual CPU/memory/network utilization over at least two weeks (long enough to capture a normal usage cycle, including any weekly patterns).
Downsize incrementally — move one size tier down, monitor for a period, and only continue downsizing if the workload remains comfortably within capacity, including at peak.
Consider a different instance family, not just a smaller size in the same family — a compute-optimized instance running a memory-heavy workload (or vice versa) is often a bigger inefficiency than raw over-provisioning within the right family.
Re-check periodically — right-sizing isn't a one-time fix; workload characteristics change as an application evolves, so recommendations should be revisited on a recurring cadence (e.g., quarterly), not just once.

Containers: request/limit tuning

In Kubernetes specifically, resource requests set too high (relative to actual usage) waste cluster capacity even if nothing ever hits the limit — the cluster autoscaler and scheduler make placement decisions based on requests, not actual usage, so an inflated request reserves capacity that sits idle:

resources:
  requests:
    cpu: "250m"      # should reflect realistic typical usage, not a generous guess
    memory: "256Mi"
  limits:
    cpu: "500m"
    memory: "512Mi"

Tools like the Kubernetes Vertical Pod Autoscaler (in recommendation mode) can suggest better request/limit values based on observed pod-level utilization.

3. Autoscaling Instead of Static Capacity

Why static capacity is expensive by default

A fixed number of always-on instances sized for peak traffic means you're paying peak-level costs 24 hours a day, even during nights, weekends, or off-season lulls when actual load might be a fraction of peak.

Horizontal autoscaling

# Azure App Service autoscale rule
az monitor autoscale create --resource my-app-plan --resource-type Microsoft.Web/serverfarms \
    --min-count 2 --max-count 10 --count 2

# AWS ECS Application Auto Scaling (target tracking)
aws application-autoscaling put-scaling-policy --policy-name cpu-scaling \
    --scalable-dimension ecs:service:DesiredCount \
    --target-tracking-scaling-policy-configuration '{"TargetValue": 70.0}'

Autoscaling based on CPU, memory, queue depth, or custom application metrics means capacity tracks actual demand — you pay for roughly what you use, rather than a fixed amount sized for a peak that might occur only occasionally.

Scheduled scaling for predictable patterns

Many workloads have predictable low-traffic windows (nights, weekends, non-business-hours for internal tools) where scaling down on a schedule — rather than waiting for a reactive metric-based trigger — captures savings more aggressively and reliably:

az monitor autoscale profile create --resource my-app-plan \
    --schedule-timezone "Eastern Standard Time" \
    --recurrence-days Saturday Sunday \
    --min-count 1 --max-count 2 --count 1

A common pattern for non-production environments: shut down dev/test environments entirely outside business hours — a resource running 12 hours a day, 5 days a week instead of continuously is roughly a 65% reduction in runtime for that environment, with zero impact on anyone since nobody's using it overnight or on weekends anyway.

Scale-to-zero where genuinely possible

Serverless compute (Azure Functions Consumption plan, AWS Lambda) and some container platforms (Kubernetes with KEDA) can scale all the way to zero running instances when there's no work to do — the most complete form of "pay for what you use," appropriate for genuinely intermittent workloads (see Section 7).

4. Pricing Models: On-Demand, Reserved, Savings Plans, and Spot

Cloud providers offer several distinct commitment models, each trading flexibility for a discount off standard on-demand pricing.

Model	Discount vs. on-demand	Commitment	Best for
On-Demand	None (baseline)	None	Unpredictable, short-lived, or experimental workloads
Reserved Instances (RIs)	Often 30–60%+	1 or 3 years, specific instance type/region	Steady-state workloads with known, stable capacity needs
Savings Plans (AWS) / Reserved VM Instances flexibility (Azure)	Similar to RIs	1 or 3 years, commit to a $/hour spend rather than a specific instance	Steady spend, but with some flexibility in instance family/size
Spot Instances	Often 60–90%	None — can be reclaimed by the provider with short notice	Fault-tolerant, interruptible workloads (batch jobs, CI runners, stateless workers)

Reserved capacity: the highest-leverage lever for steady-state workloads

If a workload runs continuously and predictably (a production database, a baseline fleet of API servers that's rarely scaled below a certain size), committing to 1- or 3-year reserved capacity for that baseline is usually the single biggest cost lever available — often cutting that portion of the bill by half or more, in exchange for giving up some flexibility.

Practical approach: reserve capacity for your baseline, steady-state load, and let on-demand or autoscaling handle the variable portion above that baseline — don't reserve for peak capacity you only need occasionally.

Spot/preemptible instances for interruption-tolerant workloads

# Kubernetes node pool using spot instances (AKS example)
az aks nodepool add --cluster-name my-cluster --resource-group my-rg \
    --name spotpool --priority Spot --eviction-policy Delete \
    --spot-max-price -1 --enable-cluster-autoscaler --min-count 0 --max-count 10

Spot capacity can be reclaimed by the cloud provider on short notice (typically with a warning period of a couple of minutes), so it's only appropriate for workloads designed to tolerate interruption gracefully — CI/CD build agents, batch data processing, stateless worker pools behind a durable queue — never for stateful, latency-sensitive, or single-instance-critical workloads.

Don't over-commit

Reserved capacity is a bet on future usage staying roughly where it is today. Over-committing to reserved capacity for a workload that later shrinks (or gets migrated/decommissioned) locks in cost for capacity you no longer need — most cloud providers offer limited resale/exchange mechanisms for this, but it's far better to size commitments conservatively against your most confident, stable baseline rather than an optimistic growth projection.

5. Storage Optimization

Tiering: not all data needs to be instantly accessible

Cloud storage services offer multiple tiers trading access latency/frequency for cost:

Tier (typical naming)	Access pattern	Relative cost
Hot / Standard	Frequently accessed	Highest per-GB storage cost, cheapest per-access
Cool / Infrequent Access	Accessed monthly or less	Lower storage cost, higher per-access/retrieval cost
Cold / Archive	Rarely accessed, retrieval can take hours	Lowest storage cost, highest retrieval cost and latency

# Azure: move a blob to the Cool tier
az storage blob set-tier --account-name mystorageaccount --container-name logs --name old-log.txt --tier Cool

# AWS S3: lifecycle policy to transition objects automatically
aws s3api put-bucket-lifecycle-configuration --bucket my-bucket --lifecycle-configuration file://lifecycle.json

{
  "Rules": [
    {
      "ID": "ArchiveOldLogs",
      "Status": "Enabled",
      "Filter": { "Prefix": "logs/" },
      "Transitions": [
        { "Days": 30, "StorageClass": "STANDARD_IA" },
        { "Days": 90, "StorageClass": "GLACIER" }
      ]
    }
  ]
}

Lifecycle policies automate this tiering based on object age or last-access time, which matters because manually tiering storage doesn't scale — the whole point is that this happens automatically as data ages, with no ongoing manual effort.

Snapshot and backup accumulation

Automated backup and snapshot schedules are essential for reliability, but without a retention policy, they accumulate indefinitely — years of daily disk snapshots for a VM that was decommissioned long ago is a surprisingly common source of quiet, ongoing waste. Set explicit retention windows (e.g., "keep daily snapshots for 30 days, weekly for 6 months, then delete") rather than "keep everything forever."

Unattached and orphaned storage

Disks left behind after a VM is deleted, or storage volumes provisioned for a project that was later cancelled, keep billing indefinitely with nothing using them — see Section 9 for finding these systematically.

6. Networking and Data Transfer Costs

Data transfer is one of the most commonly underestimated line items — it doesn't show up prominently in a "compute vs. storage" mental model, but at scale it can rival or exceed both.

Cross-region and cross-AZ transfer

Moving data between regions, and in some providers even between availability zones within the same region, typically incurs a per-GB charge. Architecting so that chatty, high-volume communication between services stays within the same region/zone where possible avoids this cost entirely — this is a design consideration, not just a cost-review afterthought.

Egress to the public internet

Data leaving the cloud provider's network (e.g., serving large files or media directly from a VM or storage bucket) is typically the most expensive kind of transfer per GB. A CDN in front of frequently-accessed public content (images, videos, static assets) both improves latency for end users and is very often cheaper than serving that same content directly from origin storage/compute, since CDN edge pricing is usually lower than origin egress pricing at volume.

NAT Gateway costs (cloud-specific but common)

In both AWS and Azure, a NAT Gateway used for outbound internet access from private subnets bills per-hour and per-GB processed — a detail that's easy to overlook until a data-heavy workload behind one produces a surprisingly large line item. For high-volume outbound traffic, evaluate whether a more direct routing path (e.g., a VPC/VNet endpoint for a specific cloud service, avoiding the NAT Gateway for that traffic entirely) is available and cheaper.

7. Serverless and Consumption-Based Savings

For workloads with genuinely intermittent or unpredictable traffic, serverless compute (see the Azure and AWS compute guides) can be dramatically cheaper than always-on infrastructure, since you pay per-execution/consumption rather than for continuously provisioned capacity.

Always-on VM, 5% average utilization:  paying for 100% of the time, using 5% of it
Serverless function, same workload:    paying only for the ~5% of time actual work happens

The crossover point depends on the specific workload's traffic shape — a function invoked constantly at high, steady volume can end up costing more than an equivalent always-on service, while one invoked sporadically can be a small fraction of the always-on cost. The Azure/AWS compute guides in this series cover the cost-model tradeoffs (and the cold-start/latency tradeoffs that come with them) in more depth.

8. Monitoring, Budgets, and Cost Visibility

You can't optimize what you can't see — visibility is a prerequisite for every other technique in this guide, not an optional nice-to-have.

Native cost management tools

# Azure Cost Management — set a budget with an alert
az consumption budget create --budget-name monthly-budget --amount 5000 \
    --time-grain Monthly --category Cost \
    --notifications '{"alert-90-percent": {"enabled": true, "operator": "GreaterThan", "threshold": 90, "contactEmails": ["team@example.com"]}}'

# AWS Budgets
aws budgets create-budget --account-id 123456789 --budget file://budget.json

Both Azure Cost Management and AWS Cost Explorer provide breakdowns by service, resource group/account, tag, and time period, along with forecasting based on current trends — the starting point for spotting anomalies before they become a surprise at the end of the month.

Anomaly detection

Configure automated alerts for unusual spend patterns rather than relying on someone noticing a large bill after the fact:

aws ce create-anomaly-monitor --anomaly-monitor '{"MonitorName": "ServiceMonitor", "MonitorType": "DIMENSIONAL", "MonitorDimension": "SERVICE"}'

A sudden spike in a specific service's spend is often the earliest signal of a misconfiguration (an accidentally-oversized autoscale event, a runaway process generating excessive API calls, a forgotten test resource left running at scale) — catching it within days rather than at the next invoice matters.

Dashboards for engineering teams, not just finance

Cost visibility that lives only in a finance team's spreadsheet doesn't change engineering behavior. Surfacing per-service or per-team cost dashboards to the engineers who actually provision resources — ideally alongside the performance/reliability dashboards they already check regularly — closes the feedback loop between "I created this resource" and "here's what it costs," which is what actually drives sustained cost-conscious decisions over time.

9. Resource Cleanup and Waste Elimination

Common categories of waste

Unattached disks — left behind after a VM is deleted, still billing for storage.
Unassociated public IP addresses — reserved but not attached to any running resource.
Idle load balancers — provisioned but with no healthy backend targets behind them.
Old snapshots and AMIs/images beyond any reasonable retention need.
Non-production environments running 24/7 when they're only used during business hours.
Zombie resources from abandoned proof-of-concepts — the classic "we'll clean this up after the demo" that never gets cleaned up.

Finding waste systematically

# Azure: find unattached managed disks
az disk list --query "[?diskState=='Unattached'].{name:name, size:diskSizeGb}" -o table

# AWS: find unattached EBS volumes
aws ec2 describe-volumes --filters Name=status,Values=available --query "Volumes[*].{ID:VolumeId,Size:Size}"

Most cloud providers' native advisor/cost-recommendation tools (Azure Advisor, AWS Trusted Advisor, AWS Compute Optimizer) surface a meaningful share of this automatically — running a periodic review of these recommendations is far cheaper than a bespoke audit script for the most common waste categories.

Automating cleanup, not just detecting it

Detection alone doesn't reduce spend if nobody acts on it. Effective patterns:

Tag-based expiration — tag temporary/experimental resources with an explicit expiry date at creation time, and run an automated job that flags or deletes anything past its expiry.
Approval-gated auto-deletion for genuinely temporary environments (PR preview environments, ephemeral test infrastructure) — tear them down automatically when the associated PR/branch is closed, rather than relying on someone remembering to do it manually.
Scheduled non-production shutdown (see Section 3) applied automatically via a scheduled function or automation runbook, not as a manual, easily-forgotten step.

10. Tagging and Cost Allocation

Why tagging is foundational, not optional

Without consistent tagging, a cloud bill is an undifferentiated total — you know what you spent, but not which team, project, or environment drove it, which makes it nearly impossible to hold anyone accountable for their portion or to spot a specific team's inefficiency.

az resource tag --tags Environment=Production Team=Payments CostCenter=CC-1234 --ids <resource-id>

{
  "Tags": [
    { "Key": "Environment", "Value": "production" },
    { "Key": "Team", "Value": "payments" },
    { "Key": "CostCenter", "Value": "CC-1234" }
  ]
}

A minimal, effective tagging taxonomy

Tag	Purpose
`Environment`	dev/staging/production — enables environment-level cost breakdowns and non-prod shutdown automation
`Team` / `Owner`	Who's accountable for this resource's cost and lifecycle
`Project` / `CostCenter`	Enables chargeback/showback to the right budget
`ExpiryDate`	For temporary resources — enables automated cleanup

Enforcing tagging

Untagged resources are a common failure mode without enforcement — cloud policy tools (Azure Policy, AWS Organizations Service Control Policies + Config Rules) can require specific tags at resource-creation time, rejecting creation requests that omit them, rather than relying on tagging discipline as a voluntary best practice that inevitably erodes over time.

11. Building a Cost-Aware Engineering Culture (FinOps)

FinOps is the operating model — borrowed and formalized from the broader industry — for bringing financial accountability to the variable, engineer-driven spending model that cloud computing enables (as opposed to the fixed, centrally-planned capital expenditure model of on-premises hardware).

The core FinOps loop

Inform — give engineers real-time visibility into what their systems cost (Section 8).
Optimize — apply the technical levers in this guide (right-sizing, reserved capacity, cleanup, tiering).
Operate — build cost review into normal engineering rhythms (sprint reviews, architecture reviews, post-launch retrospectives), not as a separate, occasional finance-driven exercise.

Practical habits that compound over time

Include a cost estimate in architecture/design reviews, the same way you'd include a security or reliability review — cost is a first-class non-functional requirement, not an afterthought discovered at the first invoice.
Review reserved capacity and right-sizing recommendations on a recurring cadence (monthly or quarterly), not just once during an initial cost-cutting push.
Treat a rising cost trend as seriously as a rising error rate — both are signals something in the system needs attention, and both are easier to address early than after months of accumulation.
Celebrate and share cost wins, not just reliability or feature wins — this reinforces that cost-consciousness is a valued engineering skill, not solely a finance-team concern imposed on engineering.

Quick Reference Table

Lever	Typical savings potential	Effort to implement
Right-sizing over-provisioned compute	20–50% on affected resources	Low–Medium (measure, then resize)
Autoscaling instead of static peak capacity	Varies widely with traffic shape; often 30%+	Medium (requires metrics and testing)
Scheduled shutdown of non-prod environments	~50–65% of that environment's runtime cost	Low (automation script/scheduled job)
Reserved Instances/Savings Plans for steady-state baseline	30–60%+ vs. on-demand	Low (financial commitment decision)
Spot/preemptible for interruption-tolerant workloads	60–90% vs. on-demand	Medium (requires interruption-tolerant design)
Storage lifecycle tiering	Varies with data access patterns; often significant for large, aging datasets	Low (automated lifecycle policies)
Cleaning up orphaned resources	Usually modest per-item, but recurring and often surprisingly large in aggregate	Low–Medium (needs periodic, ideally automated review)
CDN for public content instead of direct egress	Often net savings plus better latency	Low–Medium
Tagging + cost allocation	Enables all other savings via accountability, not a direct saving itself	Medium (requires enforcement, not just convention)

Conclusion

Cloud cost optimization isn't a single switch to flip — it's the accumulated effect of matching provisioned capacity to actual need (right-sizing and autoscaling), paying the right price for predictable workloads (reserved capacity and spot instances), not paying for data sitting idle or in the wrong tier (storage lifecycle management), and not paying at all for things nobody's using anymore (systematic cleanup). None of it works without visibility — tagging, budgets, and dashboards that make cost as visible to engineers as performance or error rates — and none of it sticks without making cost-awareness a normal, ongoing part of how a team builds and operates systems, rather than an occasional, reactive cleanup exercise triggered by an alarming invoice.

The organizations that keep cloud costs under control long-term aren't the ones that ran one aggressive optimization sprint — they're the ones that built the habits and automation in this guide into their everyday engineering practice, so waste gets caught and addressed continuously rather than accumulating for a year and then requiring a painful, disruptive cleanup effort.

Found this useful? Feel free to star the repo, open an issue with corrections, or share the surprise line item on a cloud bill that taught you to respect tagging and cleanup automation.

A Small ASP.NET Core Improvement That Can Save Big Server Resources: `CancellationToken`

Rhuturaj Takle — Wed, 15 Jul 2026 15:22:09 +0000

This is a submission for DEV's Summer Bug Smash: Smash Stories powered by Sentry.

A Small ASP.NET Core Improvement That Can Save Big Server Resources: `CancellationToken`

🚀 The Improvement

During a recent review of our ASP.NET Core APIs, we noticed something that many teams—including ours—often overlook: we weren't passing CancellationToken through our API, service, and repository layers.

The application was working correctly. There were no errors, no failed requests, and nothing obviously broken.

However, from a scalability and resource-management perspective, there was room for improvement.

So we decided to implement CancellationToken support throughout the entire request pipeline.

🤔 Why Does `CancellationToken` Matter?

Whenever a client sends a request, there's always a chance they may:

Close the browser.
Refresh the page.
Lose network connectivity.
Cancel the request.
Hit a timeout.

Without a CancellationToken, the server has no way to know that the client is no longer waiting.

As a result, it may continue:

Executing database queries.
Calling external APIs.
Reading or writing files.
Performing CPU-intensive work.
Holding database connections until the operation completes.

The response is ultimately discarded because the client has already disconnected, but the server has still spent time and resources producing it.

With CancellationToken, ASP.NET Core automatically signals when a request has been cancelled. By passing that token through every async operation, your application can stop processing as early as possible and release resources immediately.

🔧 What We Changed

We updated every layer of our application to accept and propagate the same CancellationToken.

Instead of this:

public async Task<User> GetUserAsync(int id)
{
    return await _context.Users.FirstOrDefaultAsync(x => x.Id == id);
}

We now do this:

public async Task<User> GetUserAsync(int id, CancellationToken cancellationToken)
{
    return await _context.Users
        .FirstOrDefaultAsync(x => x.Id == id, cancellationToken);
}

And in the controller:

[HttpGet("{id}")]
public async Task<IActionResult> GetUser(
    int id,
    CancellationToken cancellationToken)
{
    var user = await _userService.GetUserAsync(id, cancellationToken);
    return Ok(user);
}

The same token is passed through controllers, services, repositories, and EF Core operations.

📈 Benefits

After implementing this improvement, our APIs now:

Stop processing abandoned requests automatically.
Avoid unnecessary database work.
Free database connections sooner.
Reduce CPU and memory usage under load.
Scale more efficiently during peak traffic.
Follow ASP.NET Core asynchronous programming best practices.

💡 Takeaway

This wasn't about fixing a broken feature—it was about making a working application more efficient and production-ready.

CancellationToken is easy to skip because everything appears to work without it. But in real-world applications handling thousands of requests, properly supporting request cancellation helps avoid wasted work and improves overall server health.

If your ASP.NET Core project doesn't consistently pass CancellationToken through async methods, it's worth considering. It's a small change that can have a meaningful impact on the performance and scalability of your application.

Terraform and Bicep: Infrastructure as Code for the Cloud

Rhuturaj Takle — Wed, 15 Jul 2026 15:04:56 +0000

Terraform and Bicep: Infrastructure as Code for the Cloud

A practical guide to Infrastructure as Code (IaC) using Terraform and Bicep — the two dominant tools for defining, provisioning, and managing cloud resources declaratively, covering core concepts, state management, modules, and how to choose between them.

Introduction
Why Infrastructure as Code
Bicep
Terraform
State Management
Modules and Reusability
Multi-Environment Patterns
CI/CD Integration
Terraform vs. Bicep
Common Pitfalls
Quick Reference Table
Conclusion

Introduction

Infrastructure as Code means defining cloud resources — virtual machines, databases, networks, container clusters — in text files that a tool can read and use to create, update, or destroy real infrastructure, rather than clicking through a cloud portal or running one-off CLI commands. The file becomes the source of truth: it's version-controlled, reviewable in a pull request, and reproducible across environments.

Bicep is Microsoft's domain-specific language for deploying Azure resources — a cleaner syntax layered directly on top of Azure Resource Manager (ARM) templates. Terraform, from HashiCorp, is cloud-agnostic — the same tool and language (HCL) can provision resources across Azure, AWS, GCP, and dozens of other providers, all from one workflow.

# Terraform
resource "azurerm_linux_web_app" "api" {
  name                = "my-api"
  resource_group_name = azurerm_resource_group.main.name
  location            = azurerm_resource_group.main.location
  service_plan_id     = azurerm_service_plan.main.id
}

// Bicep
resource api 'Microsoft.Web/sites@2023-12-01' = {
  name: 'my-api'
  location: resourceGroup().location
  properties: {
    serverFarmId: appServicePlan.id
  }
}

Both describe the same underlying idea: "this resource should exist, with these properties" — declared once, applied repeatedly and predictably.

1. Why Infrastructure as Code

The problem with manual provisioning

Clicking through a cloud console to create a VM, a database, and a network works fine once. It breaks down the moment you need to:

Recreate the exact same environment for staging, or after a disaster.
Know what changed and why, months later.
Review a proposed infrastructure change before it's applied, the way you'd review a code change.
Tear down and rebuild an entire environment reliably (e.g., ephemeral pull-request environments).

What IaC provides

Reproducibility — the same definition produces the same infrastructure, every time, in every environment.
Version control — infrastructure changes go through the same git diff, pull request, and review process as application code.
Declarative intent — you describe the desired end state; the tool figures out the steps to get there (create, update, or delete) rather than you scripting each step imperatively.
Drift detection — the tool can compare what's actually deployed against what's declared and flag discrepancies (someone manually changed a setting in the portal, for instance).
Auditability — a Git history of every infrastructure change, tied to a specific commit and author.

Declarative vs. imperative

# Imperative: describe the steps
az group create --name my-rg --location eastus
az appservice plan create --name my-plan --resource-group my-rg --sku B1
az webapp create --name my-api --resource-group my-rg --plan my-plan

# Declarative: describe the desired end state
resource "azurerm_resource_group" "main" {
  name     = "my-rg"
  location = "eastus"
}

resource "azurerm_service_plan" "main" {
  name                = "my-plan"
  resource_group_name = azurerm_resource_group.main.name
  location            = azurerm_resource_group.main.location
  sku_name            = "B1"
  os_type             = "Linux"
}

The declarative version doesn't care whether the resource group already exists, needs updating, or needs creating — the tool diffs the desired state against reality and figures out the necessary action itself.

2. Bicep

Bicep compiles down to standard ARM JSON templates but reads far more like a real programming language — no more deeply nested JSON, no more "[concat(...)]" string-function gymnastics.

A basic Bicep file

param location string = resourceGroup().location
param appServicePlanSku string = 'B1'
param appName string

resource appServicePlan 'Microsoft.Web/serverfarms@2023-12-01' = {
  name: '${appName}-plan'
  location: location
  sku: {
    name: appServicePlanSku
  }
}

resource webApp 'Microsoft.Web/sites@2023-12-01' = {
  name: appName
  location: location
  properties: {
    serverFarmId: appServicePlan.id
    siteConfig: {
      linuxFxVersion: 'DOTNETCORE|9.0'
    }
  }
}

output webAppUrl string = 'https://${webApp.properties.defaultHostName}'

az deployment group create --resource-group my-rg --template-file main.bicep --parameters appName=my-api

Key language features

Parameters and variables:

param environment string = 'dev'
var appName = 'myapp-${environment}'

Resource references (implicit dependency tracking):

resource webApp 'Microsoft.Web/sites@2023-12-01' = {
  name: appName
  properties: {
    serverFarmId: appServicePlan.id // referencing appServicePlan creates an automatic dependency
  }
}

Bicep infers deployment order from these references — you rarely need to declare an explicit dependsOn the way raw ARM templates often required.

Modules (composing reusable Bicep files):

module database 'modules/database.bicep' = {
  name: 'databaseDeployment'
  params: {
    serverName: 'my-sql-server'
    location: location
  }
}

Loops:

param regions array = ['eastus', 'westeurope', 'southeastasia']

resource storageAccounts 'Microsoft.Storage/storageAccounts@2023-01-01' = [for region in regions: {
  name: 'storage${region}'
  location: region
  sku: { name: 'Standard_LRS' }
  kind: 'StorageV2'
}]

Conditions:

resource cdn 'Microsoft.Cdn/profiles@2023-05-01' = if (environment == 'production') {
  name: 'my-cdn'
  location: 'global'
  sku: { name: 'Standard_Microsoft' }
}

What-if deployments

Bicep (via the Azure CLI) supports a what-if preview — showing exactly what would change without actually applying anything, similar in spirit to terraform plan:

az deployment group what-if --resource-group my-rg --template-file main.bicep --parameters appName=my-api

Best fit

Teams building exclusively on Azure who want a first-party, tightly integrated authoring experience — Bicep gets day-one support for new Azure resource types and API versions (since it compiles directly to ARM, the same mechanism the Azure portal itself uses), with strong IDE tooling (VS Code extension with live validation and IntelliSense).

3. Terraform

Terraform uses HCL (HashiCorp Configuration Language), and its defining characteristic is being provider-agnostic — the same core workflow and syntax apply whether you're provisioning Azure, AWS, GCP, Kubernetes, Datadog, or literally hundreds of other systems that publish a Terraform provider.

A basic Terraform configuration

terraform {
  required_providers {
    azurerm = {
      source  = "hashicorp/azurerm"
      version = "~> 3.0"
    }
  }
}

provider "azurerm" {
  features {}
}

resource "azurerm_resource_group" "main" {
  name     = "my-rg"
  location = "East US"
}

resource "azurerm_service_plan" "main" {
  name                = "my-plan"
  resource_group_name = azurerm_resource_group.main.name
  location            = azurerm_resource_group.main.location
  os_type             = "Linux"
  sku_name            = "B1"
}

resource "azurerm_linux_web_app" "api" {
  name                = "my-api"
  resource_group_name = azurerm_resource_group.main.name
  location            = azurerm_resource_group.main.location
  service_plan_id     = azurerm_service_plan.main.id

  site_config {
    application_stack {
      dotnet_version = "9.0"
    }
  }
}

output "web_app_url" {
  value = "https://${azurerm_linux_web_app.api.default_hostname}"
}

The core workflow

terraform init      # downloads providers and initializes the working directory
terraform plan       # shows what would change, without applying it
terraform apply      # applies the change, after confirmation
terraform destroy    # tears down everything Terraform manages in this configuration

terraform plan is one of Terraform's most valuable features in practice — it's a dry run showing exactly what will be created, changed, or destroyed, in a readable diff format, before anything actually happens:

Terraform will perform the following actions:

  # azurerm_linux_web_app.api will be created
  + resource "azurerm_linux_web_app" "api" {
      + name = "my-api"
      + ...
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Variables and outputs

variable "environment" {
  type    = string
  default = "dev"
}

variable "app_name" {
  type = string
}

resource "azurerm_resource_group" "main" {
  name     = "${var.app_name}-${var.environment}-rg"
  location = "East US"
}

terraform apply -var="app_name=my-api" -var="environment=production"

Loops and conditionals

variable "regions" {
  default = ["eastus", "westeurope", "southeastasia"]
}

resource "azurerm_storage_account" "storage" {
  for_each                 = toset(var.regions)
  name                      = "storage${each.value}"
  resource_group_name       = azurerm_resource_group.main.name
  location                  = each.value
  account_tier              = "Standard"
  account_replication_type  = "LRS"
}

resource "azurerm_cdn_profile" "cdn" {
  count               = var.environment == "production" ? 1 : 0
  name                = "my-cdn"
  resource_group_name = azurerm_resource_group.main.name
  location            = "global"
  sku                 = "Standard_Microsoft"
}

Multi-cloud in one configuration

provider "aws" {
  region = "us-east-1"
}

provider "azurerm" {
  features {}
}

resource "aws_s3_bucket" "logs" {
  bucket = "my-app-logs"
}

resource "azurerm_storage_account" "backup" {
  name                = "myappbackup"
  resource_group_name = azurerm_resource_group.main.name
  location            = "East US"
  account_tier        = "Standard"
  account_replication_type = "GRS"
}

This is Terraform's signature capability — a single tool, workflow, and state model spanning multiple clouds and even non-cloud systems (DNS providers, monitoring platforms, SaaS configuration) in one coherent configuration.

Best fit

Multi-cloud or hybrid environments, teams already invested in the broader Terraform/HashiCorp ecosystem, or organizations wanting one consistent IaC tool and workflow across every platform they use rather than a different tool per cloud.

4. State Management

This is the area where Terraform and Bicep differ most fundamentally.

Terraform: explicit, persisted state

Terraform maintains a state file (terraform.tfstate) — a JSON record of exactly what Terraform believes exists and its current configuration. Every plan and apply compares your .tf files against this state (and, to a lesser extent, against the real infrastructure) to compute what needs to change.

terraform {
  backend "azurerm" {
    resource_group_name  = "tfstate-rg"
    storage_account_name = "tfstatestorage"
    container_name       = "tfstate"
    key                  = "prod.terraform.tfstate"
  }
}

Storing state remotely (Azure Storage, AWS S3, Terraform Cloud, etc.) rather than as a local file is essential for any team environment — it enables state locking (preventing two people from running apply concurrently and corrupting the state) and gives everyone on the team a consistent view of what's actually deployed.

The state file is sensitive — it often contains resource attributes verbatim, including things that should be treated as secrets (connection strings, generated passwords). Treat it with the same care as any other credential store: encrypt it at rest, restrict access, and never commit it to source control.

Bicep: stateless, relies on Azure Resource Manager

Bicep doesn't maintain a separate state file at all — it relies entirely on Azure Resource Manager as the source of truth for what currently exists. Every deployment is compared directly against the live state of Azure itself.

This has real practical implications:

No state file to secure, lock, or corrupt — one whole category of Terraform operational concerns simply doesn't exist in Bicep.
No drift between "what Terraform thinks exists" and "what Azure actually has" — Bicep is always looking at the real thing.
The tradeoff: Bicep's "what currently exists" understanding is scoped entirely to Azure Resource Manager, so it has no concept of tracking non-Azure resources or arbitrary external systems the way Terraform's state and provider model can.

5. Modules and Reusability

Terraform modules

# modules/web-app/main.tf
variable "app_name" {}
variable "resource_group_name" {}
variable "location" {}

resource "azurerm_service_plan" "plan" {
  name                = "${var.app_name}-plan"
  resource_group_name = var.resource_group_name
  location            = var.location
  os_type             = "Linux"
  sku_name            = "B1"
}

resource "azurerm_linux_web_app" "app" {
  name                = var.app_name
  resource_group_name = var.resource_group_name
  location            = var.location
  service_plan_id     = azurerm_service_plan.plan.id
}

output "url" {
  value = azurerm_linux_web_app.app.default_hostname
}

# root main.tf
module "product_api" {
  source              = "./modules/web-app"
  app_name            = "product-api"
  resource_group_name = azurerm_resource_group.main.name
  location            = azurerm_resource_group.main.location
}

The Terraform Registry hosts a large public ecosystem of pre-built, community- and vendor-maintained modules for common patterns (a well-configured VPC, a production-ready Kubernetes cluster) that teams can consume directly instead of writing from scratch.

Bicep modules

// modules/web-app.bicep
param appName string
param location string

resource plan 'Microsoft.Web/serverfarms@2023-12-01' = {
  name: '${appName}-plan'
  location: location
  sku: { name: 'B1' }
}

resource app 'Microsoft.Web/sites@2023-12-01' = {
  name: appName
  location: location
  properties: { serverFarmId: plan.id }
}

output url string = app.properties.defaultHostName

// main.bicep
module productApi 'modules/web-app.bicep' = {
  name: 'productApiDeployment'
  params: {
    appName: 'product-api'
    location: resourceGroup().location
  }
}

Both ecosystems support publishing and versioning modules for reuse — Terraform via the public/private Registry, Bicep via Azure Container Registry-hosted module registries or, more recently, Bicep Registry support for public/private module sharing.

6. Multi-Environment Patterns

Terraform: workspaces or separate state per environment

terraform workspace new staging
terraform workspace new production
terraform workspace select staging
terraform apply -var-file="staging.tfvars"

Many teams prefer separate state files/directories per environment (rather than workspaces) for stronger isolation — accidental cross-environment changes are much harder when dev, staging, and production literally have separate state files, separate backend configurations, and often separate approval gates:

environments/
  dev/main.tf
  staging/main.tf
  production/main.tf
modules/
  web-app/

Bicep: parameter files per environment

// main.bicep — shared across all environments
param environment string
param appServicePlanSku string

// staging.bicepparam
using 'main.bicep'
param environment = 'staging'
param appServicePlanSku = 'S1'

// production.bicepparam
using 'main.bicep'
param environment = 'production'
param appServicePlanSku = 'P1v3'

az deployment group create --resource-group my-rg --template-file main.bicep --parameters production.bicepparam

Bicep leans on Azure subscriptions/resource groups as the natural environment boundary — since there's no separate state file, "which environment is this" is really just "which resource group/subscription am I deploying into," combined with a parameter file selecting environment-specific values.

7. CI/CD Integration

Terraform in a pipeline

# GitHub Actions
- uses: hashicorp/setup-terraform@v3
- run: terraform init
- run: terraform plan -out=tfplan
- run: terraform apply tfplan

A common, valuable pattern: run terraform plan automatically on every pull request (posting the plan output as a PR comment for human review), and only run terraform apply after merge to the main branch — infrastructure changes get the same review-before-merge discipline as application code.

Bicep in a pipeline

- uses: azure/arm-deploy@v2
  with:
    resourceGroupName: my-rg
    template: ./main.bicep
    parameters: ./production.bicepparam

# Preview equivalent to terraform plan, for PR review
az deployment group what-if --resource-group my-rg --template-file main.bicep --parameters production.bicepparam

Both tools support this "preview in PR, apply after merge" pattern — the details differ, but the underlying discipline (never apply infrastructure changes without a reviewed diff first) is identical and equally important for both.

8. Terraform vs. Bicep

Aspect	Bicep	Terraform
Cloud scope	Azure only	Multi-cloud (Azure, AWS, GCP, and hundreds more)
State management	None — relies on Azure Resource Manager directly	Explicit state file, requires a remote backend for teams
New Azure feature support	Immediate (same mechanism as ARM/the Azure portal)	Depends on the `azurerm` provider catching up, usually fast but not instant
Language	Bicep DSL (compiles to ARM JSON)	HCL, purpose-built, provider-agnostic
Ecosystem/modules	Growing, Bicep Registry, ARM template ecosystem	Very large — Terraform Registry, huge community module ecosystem
Preview changes	`az deployment ... what-if`	`terraform plan` (arguably the more mature, widely relied-upon version of this idea)
Learning curve	Lower if you already know Azure and ARM concepts	Slightly higher, but transferable across every cloud you'll ever touch
Vendor	Microsoft (first-party Azure tool)	HashiCorp (BSL-licensed as of Terraform 1.5.x, with OpenTofu as an open-source fork)

A practical rule of thumb

Exclusively on Azure, want the tightest integration with new Azure features, and don't need to manage non-Azure infrastructure? → Bicep.
Multi-cloud, hybrid, or want one consistent IaC tool and skill set across every platform (including non-cloud systems like DNS or monitoring configuration)? → Terraform.
Already deep in the Terraform ecosystem for other clouds and just need to add Azure resources? → Terraform, for consistency, even for an Azure-only workload.

9. Common Pitfalls

Pitfall	Why it hurts	Better approach
Committing Terraform state to source control	State often contains secrets in plaintext	Use a remote backend (Azure Storage, S3, Terraform Cloud) with encryption and access control
Manually changing resources in the cloud portal	Causes drift between IaC definition and reality, silently	Make all changes through the IaC tool; use drift detection (`terraform plan`, Bicep what-if) to catch exceptions
No plan/what-if review before applying	Changes get applied blind, including accidental deletions	Always run `plan`/`what-if` and review the diff, ideally as part of a PR
Hardcoding secrets in `.tf`/`.bicep` files	Secrets end up in version control history	Use a secrets manager (Azure Key Vault, AWS Secrets Manager) referenced at deploy time, not literal values in code
One giant configuration for everything	Slow plans, high blast radius for any single mistake, hard to reason about	Split into modules and separate state/deployments per logical boundary (network, data, app)
No locking on shared state	Concurrent applies can corrupt state or double-apply changes	Use a backend that supports state locking (Azure Storage with lease, S3 + DynamoDB, Terraform Cloud)

Quick Reference Table

Concept	Bicep	Terraform
`plan`/preview	`az deployment ... what-if`	`terraform plan`
Apply	`az deployment group create`	`terraform apply`
Reusable units	Modules (`.bicep` files)	Modules (directories with variables/outputs)
State	None — Azure Resource Manager is the source of truth	Explicit `.tfstate`, needs a remote backend for teams
Multi-cloud	No — Azure only	Yes — hundreds of providers
Parameter files	`.bicepparam` files	`.tfvars` files
Loops	`for` expressions	`count` / `for_each`
Conditions	`if` expressions on resources	`count = condition ? 1 : 0`

Conclusion

Terraform and Bicep both solve the same core problem — describing infrastructure declaratively so it's reproducible, reviewable, and version-controlled — but they make different bets about scope. Bicep bets on being the best possible tool for Azure specifically, with no separate state to manage and immediate support for new Azure capabilities. Terraform bets on being one consistent tool across every cloud and system you'll ever need to provision, at the cost of an explicit state file you're responsible for securing and managing.

Neither choice is wrong in isolation — an Azure-only shop loses little by choosing Bicep's simplicity, and a multi-cloud or platform-team context gains real, lasting value from Terraform's provider-agnostic consistency. What matters far more than which tool you pick is actually adopting the discipline both enable: infrastructure changes reviewed as diffs before they're applied, state (if any) treated as sensitive, and manual "just click it in the portal" changes eliminated as a habit.

Found this useful? Feel free to star the repo, open an issue with corrections, or share the state-file mishap that taught you to respect remote backends.

DEV Community: Rhuturaj Takle

Azure DevOps: Microsoft's Platform for Repos, Pipelines, Boards, and Deployments

Azure DevOps: Microsoft's Platform for Repos, Pipelines, Boards, and Deployments

Table of Contents

Introduction

1. The Five Services

2. Azure Repos

Git repositories with familiar workflows

Branch policies

Pull requests

3. Azure Pipelines: YAML Fundamentals

Core structure

Tasks vs. scripts

4. A Complete .NET CI/CD Pipeline

5. Variables, Variable Groups, and Secrets

Pipeline variables

Variable groups: shared, centrally-managed configuration

Secret variables

Linking directly to Azure Key Vault

6. Multi-Stage Pipelines and Environments

Stages: sequencing build and multiple deployment targets

Environments and approval gates

Deployment strategies

7. Templates and Reusability

Step templates

Extending a base template

8. Azure Boards

Work item types and hierarchy

Backlogs and sprints

Linking work items to code

Kanban boards

9. Azure Artifacts

10. Azure Test Plans

Linking automated test results

11. Self-Hosted Agents

Why self-host

Setting up a self-hosted agent

12. Azure DevOps vs. GitHub Actions/GitHub

They aren't mutually exclusive

Practical guidance

Quick Reference Table

Conclusion

GitHub Actions: CI/CD Automation Built into GitHub

GitHub Actions: CI/CD Automation Built into GitHub

Table of Contents

Introduction

1. Core Concepts

Workflows, jobs, and steps

Runners

2. Workflow Syntax

Anatomy of a workflow file

Expressions and contexts

3. Triggers

Common event triggers

Reacting to other workflows

4. Jobs, Dependencies, and Runners

Running jobs in parallel (the default)

Sequencing jobs with needs

Passing data between jobs

Choosing a runner

5. A Complete .NET CI Pipeline

6. Secrets and Variables

Repository and organization secrets

Variables (non-secret configuration)

OIDC: avoiding long-lived cloud credentials entirely

7. Matrix Builds

Excluding specific combinations

Fail-fast behavior

8. Caching Dependencies

Built-in caching via setup-dotnet

9. Reusable Workflows and Composite Actions

Reusable workflows: share an entire pipeline

Composite actions: share a sequence of steps

10. Deployment Patterns

Deploying to Azure App Service

Deploying to AWS ECS

Deploying to a Kubernetes cluster (AKS)

11. Environments and Approval Gates

12. Security Considerations

Pin actions to a specific version, ideally a commit SHA

Sequencing jobs with `needs`

Built-in caching via `setup-dotnet`

Least-privilege `GITHUB_TOKEN` permissions

Be cautious with `pull_request_target` and untrusted forks

Avoid injecting untrusted input directly into `run` commands

Adding a `NOT NULL` column to a table with existing rows: needs care

An extension library on `IDbConnection`

`Query` and `QueryAsync`

Mapping to anonymous types or `dynamic`