Skip to main content

Employee Database Indexing and Query Patterns

Summary​

The current model has 17 confirmed indexes. They support tenant/code uniqueness, common Employee status/department queries, child joins, history ordering, audit lookup, and outbox processing. The source does not establish performance guarantees.

Audience​

  • Backend developers and QA engineers
  • Solution architects reviewing query fit

Reference Content​

Important index groups​

GroupConfirmed indexesNotesSource
Employee root3Unique tenant/code; tenant/status; tenant/departmentInfrastructure/EmployeeDbContext.cs
Organization2Unique tenant/code for Department and Designationsame
Child joins8One Employee-ID index for each child objectinitial migration
Timeline/audit2Tenant/time indexes for timeline and auditcontext plus second migration
Outbox2Unique event identity; status/time processingcontext

There is no database unique index for Employee email. Email uniqueness is checked by repository/application logic. No dedicated index exists for Employee designation or reporting-manager references, and no search-specific text index is configured.

Query patterns​

  • Search starts from tenant-filtered Employees.AsNoTracking().
  • Free-text filters use case-normalized containment over display name, employee code, and email.
  • Additional filters cover name, code, email, department, designation, manager, status, activity, skill, location, and employment type.
  • The provider counts the filtered set, applies normalized sorting, then uses offset pagination with Skip and Take.
  • Supported sorting covers display name, employee code, status, joining date, and update time.
  • Profile retrieval eager-loads all eight child collections.
  • Timeline reads are no-tracking and newest-first.
  • Department/designation list queries are no-tracking projections ordered by name or level/name.

Query-fit limitations​

The current indexes directly support only a subset of search filters and sorts. Case transformation and substring predicates may not use ordinary indexes efficiently. No benchmark, query-plan baseline, full-text index, keyset pagination, or performance service-level guarantee was found. Optimization decisions require measured production-safe evidence.

Source References​

  • microservices/src/employee-service/Infrastructure/EmployeeDbContext.cs
  • microservices/src/employee-service/Infrastructure/PostgresEmployeeSearchProvider.cs
  • microservices/src/employee-service/Infrastructure/Persistence.cs
  • microservices/src/employee-service/Application/Queries/EmployeeApplicationQueries.cs
  • microservices/src/employee-service/Infrastructure/Migrations/20260701173627_InitialEmployeeSchema.cs
  • microservices/src/employee-service/Infrastructure/Migrations/20260701181407_AddTimelineEventFields.cs

See Also​

Keywords​

  • Composite indexes
  • Employee search
  • No-tracking queries

Revision Information​

  • Status: Draft
  • Last reviewed: 2026-07-15
  • Review cycle: Quarterly