# Database Indexing Strategy {#database-indexing-strategy}


Optimize query performance with proper indexing.


## Default Indexes {#default-indexes}


All entities include indexes on:

- `id` (Primary Key)
- `tenantId` (Multi-tenancy filter)
- `organizationId` (Organization filter)


## Common Index Patterns {#common-index-patterns}



### Single Column Index {#single-column-index}


```typescript
@MultiORMEntity("time_log")
export class TimeLog extends TenantOrganizationBaseEntity {
  @MultiORMColumn()
  @Index()
  employeeId: string;

  @MultiORMColumn()
  @Index()
  startedAt: Date;
}
```


### Composite Index {#composite-index}


```typescript
@Index(["tenantId", "organizationId", "employeeId"])
@MultiORMEntity("time_log")
export class TimeLog extends TenantOrganizationBaseEntity {}
```


### Unique Index {#unique-index}


```typescript
@Index(["email", "tenantId"], { unique: true })
@MultiORMEntity("user")
export class User extends TenantBaseEntity {}
```


## Recommended Indexes {#recommended-indexes}


| Table    | Columns                       | Type      | Purpose         |
| -------- | ----------------------------- | --------- | --------------- |
| time_log | (employeeId, startedAt)       | Composite | Time reports    |
| time_log | (projectId, startedAt)        | Composite | Project reports |
| task     | (projectId, status)           | Composite | Task lists      |
| task     | (title)                       | GIN/FTS   | Search          |
| employee | (organizationId, isActive)    | Composite | Employee lists  |
| invoice  | (organizationId, status)      | Composite | Invoice filters |
| contact  | (organizationId, contactType) | Composite | CRM queries     |


## Query Analysis {#query-analysis}


```sql
-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM time_log
WHERE employee_id = 'uuid'
  AND started_at BETWEEN '2025-01-01' AND '2025-01-31';
```


## Index Monitoring {#index-monitoring}


```sql
-- Unused indexes
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
```


## Related Pages {#related-pages}


- [Query Builder Patterns](https://sourcey-gauzy.pages.dev/database/query-builder/index.md) — complex queries
- [Performance Benchmarks](https://sourcey-gauzy.pages.dev/reference/performance-benchmarks/index.md) — performance
- [Database Schema](https://sourcey-gauzy.pages.dev/database/schema-overview/index.md) — schema