DB index optimization recommendations (PostgreSQL vs SQLite)

[joeldickson]

Context

Reviewed latest main schema and query patterns in:

• TelemetryDbContext
• EfTelemetryRepository (filters, sorting, pagination, aggregations)

Current schema has several single-column indexes, which is good baseline. The app’s primary read patterns combine multiple filters + date range + descending time sort. This is where composite indexes should help most.

Why separate recommendations per DB engine?

Yes — PostgreSQL and SQLite should have different index strategy details.

• PostgreSQL can exploit richer features (partial indexes, INCLUDE, concurrent index build, better planner for complex composites).
• SQLite supports composites but has simpler planner behavior and fewer advanced index features; over-indexing can hurt writes/file size quickly.

---

PostgreSQL recommendations

1) BuildMetrics hot-path composite index

Most list/dashboard queries filter by environment/category/project/repo/branch/platform + date range and then sort by ReceivedAt DESC.

Recommend:

• (BuildCategory, ExecutionEnvironment, ReceivedAt DESC)
• (ProjectName, RepositoryName, Branch, Platform, ReceivedAt DESC)

Rationale: supports common equality filters first, then range/order on time.

2) TestRuns hot-path composite index

Similar filter pattern and sort behavior.

Recommend:

• (ExecutionEnvironment, ReceivedAt DESC)
• (ProjectName, RepositoryName, Branch, Platform, TestRunner, ReceivedAt DESC)

3) Partial indexes for skewed categories (optional)

Because API queries pin BuildCategory = 'API' and clientside queries pin BuildCategory = 'Clientside', partial indexes may outperform generic ones on large tables.

Recommend (optional):

• WHERE BuildCategory = 'API'
• WHERE BuildCategory = 'Clientside'

4) Covering index for paginated list endpoints (optional)

For GetBuildMetricsAsync/GetTestRunsAsync, consider INCLUDE columns used in projection to reduce heap fetches for top-N pages.

5) Maintenance

• Use CREATE INDEX CONCURRENTLY in production.
• Validate with EXPLAIN (ANALYZE, BUFFERS) on representative queries.

---

SQLite recommendations

1) Keep index set smaller and practical

Start with just 1–2 composites per table to balance read gains vs write cost.

Recommend:

• BuildMetrics(BuildCategory, ExecutionEnvironment, ReceivedAt)
• TestRuns(ExecutionEnvironment, ReceivedAt)
• TestRuns(ProjectName, RepositoryName, Branch, Platform, ReceivedAt)

SQLite can scan reverse order from ASC index for DESC queries in many cases, so explicit DESC index is often less critical.

2) Avoid too many overlapping indexes

Each insert path (AddBuildMetricAsync, AddTestRunAsync) is write-heavy; too many indexes will degrade ingest throughput.

3) Verify with query plans

Use EXPLAIN QUERY PLAN for:

• list endpoints with pagination
• summary endpoints with date filters

4) Consider PRAGMA tuning in non-prod local scenarios

For local-only/dev deployments, tune pragmas separately (journal mode/cache/synchronous) if needed, but keep default-safe settings for production-like envs.

---

Suggested rollout plan

1. Capture baseline query timings for key endpoints.
2. Add PostgreSQL composites first (and optional partial indexes for API/Clientside if table volume justifies).
3. Add minimal SQLite composites.
4. Re-measure and keep only indexes with measurable benefit.

Candidate endpoints to benchmark

• GetBuildMetricsAsync (paged + sorted)
• GetTestRunsAsync (paged + sorted)
• GetApiBuildSummaryAsync (category/date aggregation)
• GetClientsideBuildSummaryAsync (category/reload/date aggregation)
• GetTestRunSummaryAsync (date grouping + totals)