Hacktoberfest 2026: los issues que los mantenedores marcaron para octubre, abiertos y aptos para principiantes. Explorar issues de Hacktoberfest

Add indexes for re-executor, spot-check and challenge lookups

Abierto Apto para principiantes
  • ¿Algo no está bien?
#68 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
2/5
Tiempo estimado
1-3 horas
Aptitud para principiantes
88/100
Tipo de issue
Nueva funcionalidad
Claridad
Bien especificado
Estado de actividad
Activo
Stack tecnológico
postgresql, typescript
Área
database, performance

Línea de trabajo

Empieza leyendo db/migrations/0002_indexes_and_fks.ts, en especial reexecutor_active_stake_idx, para copiar la forma sql de Kysely que usa un índice descendente. Crea db/migrations/0005_hot_indexes.ts con los tres índices con nombre y un down en orden inverso. Luego ejecuta pnpm db:migrate, pnpm --filter @verity/db migrate:down, pnpm db:migrate y pnpm typecheck, y revisa en pg_indexes las definiciones de los tres índices. Está terminado cuando up y down se vuelven a aplicar sin errores y ninguna migración anterior cambia.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

db enhancement good first issue trivial

Summary

Three frequently-run queries filter or sort on columns that no index leads with: re-executor lookups on replay and spot_check, and the challenge list ordered by opened_at. Add a migration that creates the missing indexes, with a working down.

Background

Postgres (managed with Kysely migrations in db/migrations/, see CONTRIBUTING.md, "Changing the database") is the backend's mirror of chain state (docs/ARCHITECTURE.md, "Database"). Re-executor workers poll GET /v1/reexecutors/:id/assignments every few seconds, and the dashboard's re-executor pages compute per-worker statistics, so these queries are on the hot path. Postgres does not create an index for a foreign key automatically.

Current behaviour

db/migrations/0002_indexes_and_fks.ts creates, among others, a unique index on replay (submission_id, reexecutor_id) and one on spot_check (submission_id, reexecutor_id). Both lead with submission_id, so they cannot serve a filter on reexecutor_id alone. Queries that do that:

  • backend/src/modules/reexecutors/routes.ts, the assignments poll: .where("replay.reexecutor_id", "=", params.id) with .where("replay.status", "=", "assigned") (around line 256), and .where("spot_check.reexecutor_id", "=", params.id) with .where("spot_check.result", "is", null) (around line 266).
  • statsByReexecutor() in the same file: .groupBy("replay.reexecutor_id") and, for one worker, .where("replay.reexecutor_id", "=", reexecutorId).
  • backend/src/modules/challenges/routes.ts, lines 39-40: .orderBy("challenge.opened_at", "desc").orderBy("challenge.id"). The only index on challenge that could help is the partial challenge_open_uq on submission_id, so the listing sorts the whole table.

Scenario: with many historical replay rows, every poll by every worker scans all of replay.

Expected behaviour

A new migration adds indexes so these lookups can use an index, and its down removes exactly what up created.

Proposed approach

  1. Create db/migrations/0005_hot_indexes.ts (the existing migrations are 0001 to 0004; if another migration has landed by the time you rebase, use the next free number, since Kysely runs files in name order and an applied migration must never be edited).
  2. In up, create:
    • replay_reexecutor_status_idx on replay (reexecutor_id, status).
    • spot_check_reexecutor_idx on spot_check (reexecutor_id).
    • challenge_opened_at_idx on challenge (opened_at desc, id), matching the query's order direction exactly. A descending column needs the raw sql form, as 0002 does for reexecutor_active_stake_idx.
  3. In down, drop the three indexes in reverse order.
  4. Do not use CONCURRENTLY: Kysely runs each migration inside a transaction on Postgres, where it is not allowed. The tables are small enough that a plain CREATE INDEX is fine.
  5. db/src/types.ts does not change because no columns change.

Files

  • db/migrations/0005_hot_indexes.ts (new): up creates the three indexes, down drops them.

Acceptance criteria

  • pnpm db:migrate on a database already at 0004 applies the new migration without errors.
  • The three indexes exist in pg_indexes with the definitions above.
  • pnpm --filter @verity/db migrate:down removes exactly those three indexes, and pnpm db:migrate applies them again.
  • No existing migration file is modified.
  • pnpm typecheck passes.

How to verify

pnpm --filter @verity/db local-postgres      # embedded Postgres on port 5433 (or use your own)
export DATABASE_URL=postgres://verity:[email protected]:5433/verity
pnpm db:migrate
pnpm --filter @verity/db migrate:down
pnpm db:migrate
pnpm typecheck

Then inspect with any Postgres client: select indexname, indexdef from pg_indexes where indexname in ('replay_reexecutor_status_idx','spot_check_reexecutor_idx','challenge_opened_at_idx');. Optionally run EXPLAIN on the assignments query against a table with a few thousand generated rows (do not commit the data). pnpm dev also runs migrations on start (scripts/dev.sh), so a clean pnpm dev start is a good final check.

Out of scope

  • Rewriting any query, adding columns, or changing db/src/types.ts.
  • Indexes for tables other than replay, spot_check and challenge.
  • Changing or dropping any index created in earlier migrations.

Notes

Trivial, single new file. The only merge hotspot is the migration number: any other issue that adds a migration also wants the next number, so whichever lands second renumbers its file.

Lenguaje dominante
TypeScript
Estrellas
0
Forks
1
Merge medio
2 min
PR fusionados (30 d)
1

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de DevQwinB/Verity

Todos los issues de DevQwinB/Verity

Issues similares

Más issues de TypeScript

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.