• Home
  • Features
  • Pricing
  • Docs
  • Announcements
  • Sign In

cameri / nostream / 24634834378

19 Apr 2026 05:24PM UTC coverage: 66.443% (-4.7%) from 71.16%
24634834378

push

github

web-flow
feat: queries benchmarking and optimization  (#68) (#534)

* feat(db): add hot-path indexes and query benchmark (#68)

* docs: added changeset (#68)

* chore: addressed review comments (#68)

* fix: bug fixes in benchmark-queries (#68)

1021 of 1632 branches covered (62.56%)

Branch coverage included in aggregate %.

0 of 111 new or added lines in 1 file covered. (0.0%)

103 existing lines in 20 files now uncovered.

2549 of 3741 relevant lines covered (68.14%)

7.73 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

0.0
/src/scripts/benchmark-queries.ts
1
/**
2
 * Read-only benchmark for the hot query paths on `events` / `invoices`.
3
 *
4
 * Runs `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)` against canonical
5
 * query shapes used by the relay (REQ subscriptions, vanish checks, purge
6
 * scans, pending invoice polls) and reports the planner's choice and the
7
 * measured execution time so operators can validate index effectiveness
8
 * before and after applying the hot-path-indexes migration.
9
 *
10
 * Usage:
11
 *     npm run db:benchmark
12
 *     npm run db:benchmark -- --runs 5 --kind 1 --limit 500
13
 *
14
 * The script is read-only: it only issues EXPLAIN and SELECT statements.
15
 */
16

17
import { Knex } from 'knex'
18

NEW
19
import { EventKinds } from '../constants/base'
×
NEW
20
import { InvoiceStatus } from '../@types/invoice'
×
NEW
21
import { getMasterDbClient } from '../database/client'
×
22

23
type ExplainPlanNode = {
24
  'Node Type'?: string
25
  'Index Name'?: string
26
  'Relation Name'?: string
27
  'Actual Total Time'?: number
28
  'Actual Rows'?: number
29
  'Shared Hit Blocks'?: number
30
  'Shared Read Blocks'?: number
31
  'Plan Rows'?: number
32
  Plans?: ExplainPlanNode[]
33
}
34

35
type ExplainResult = {
36
  Plan: ExplainPlanNode
37
  'Execution Time': number
38
  'Planning Time': number
39
}
40

41
type BenchmarkCase = {
42
  name: string
43
  description: string
44
  skipIf?: (ctx: BenchContext) => string | undefined
45
  build: (ctx: BenchContext) => Knex.QueryBuilder | Knex.Raw
46
}
47

48
type BenchContext = {
49
  db: Knex
50
  samplePubkey?: Buffer
51
  eventCount: number
52
  invoiceCount: number
53
  kind: number
54
  limit: number
55
  horizonSeconds: number
56
}
57

58
type CliOptions = {
59
  runs: number
60
  kind: number
61
  limit: number
62
  horizonDays: number
63
}
64

65
function parseIntArg(raw: string | undefined, fallback: number, { min = Number.NEGATIVE_INFINITY } = {}): number {
×
66
  // Use Number.isFinite rather than falsy-coalescing so `0` is a valid input
67
  // (e.g. `--kind 0` selects SET_METADATA, which is a valid Nostr kind).
NEW
68
  const parsed = Number(raw)
×
NEW
69
  if (!Number.isFinite(parsed)) {
×
NEW
70
    return fallback
×
71
  }
NEW
72
  return Math.max(min, parsed)
×
73
}
74

75
function parseArgs(argv: string[]): CliOptions {
NEW
76
  const opts: CliOptions = {
×
77
    runs: 3,
78
    kind: EventKinds.TEXT_NOTE,
79
    limit: 500,
80
    horizonDays: 7,
81
  }
NEW
82
  for (let i = 0; i < argv.length; i++) {
×
NEW
83
    const arg = argv[i]
×
NEW
84
    const next = argv[i + 1]
×
NEW
85
    switch (arg) {
×
86
      case '--runs':
NEW
87
        opts.runs = parseIntArg(next, opts.runs, { min: 1 })
×
NEW
88
        i++
×
NEW
89
        break
×
90
      case '--kind':
NEW
91
        opts.kind = parseIntArg(next, opts.kind, { min: 0 })
×
NEW
92
        i++
×
NEW
93
        break
×
94
      case '--limit':
NEW
95
        opts.limit = parseIntArg(next, opts.limit, { min: 1 })
×
NEW
96
        i++
×
NEW
97
        break
×
98
      case '--horizon-days':
NEW
99
        opts.horizonDays = parseIntArg(next, opts.horizonDays, { min: 1 })
×
NEW
100
        i++
×
NEW
101
        break
×
102
      case '--help':
103
      case '-h':
NEW
104
        printUsage()
×
NEW
105
        process.exit(0)
×
106
    }
107
  }
NEW
108
  return opts
×
109
}
110

111
function printUsage(): void {
NEW
112
  console.log(
×
113
    [
114
      'Usage: npm run db:benchmark -- [options]',
115
      '',
116
      'Options:',
117
      '  --runs <n>           Execute each query N times (default 3).',
118
      '  --kind <kind>        Event kind for kind-based queries (default 1).',
119
      '  --limit <n>          LIMIT used in ordered queries (default 500).',
120
      '  --horizon-days <n>   Lookback window for time-range queries (default 7).',
121
      '  -h, --help           Show this message.',
122
    ].join('\n'),
123
  )
124
}
125

126
function walkPlan(node: ExplainPlanNode, visit: (n: ExplainPlanNode) => void): void {
NEW
127
  visit(node)
×
NEW
128
  if (node.Plans) {
×
NEW
129
    for (const child of node.Plans) {
×
NEW
130
      walkPlan(child, visit)
×
131
    }
132
  }
133
}
134

135
function summarizePlan(plan: ExplainPlanNode): { indexes: string[]; scans: string[] } {
NEW
136
  const indexes = new Set<string>()
×
NEW
137
  const scans = new Set<string>()
×
NEW
138
  walkPlan(plan, (node) => {
×
NEW
139
    if (node['Index Name']) {
×
NEW
140
      indexes.add(node['Index Name'])
×
141
    }
NEW
142
    if (node['Node Type']) {
×
NEW
143
      scans.add(node['Node Type'])
×
144
    }
145
  })
NEW
146
  return {
×
147
    indexes: Array.from(indexes),
148
    scans: Array.from(scans),
149
  }
150
}
151

152
async function explain(db: Knex, query: Knex.QueryBuilder | Knex.Raw): Promise<ExplainResult> {
153
  // Keep placeholders in Knex's `?` form so `db.raw(sql, bindings)` substitutes
154
  // them correctly — `.toNative()` rewrites them to `$1, $2, …`, which makes
155
  // Knex's binding check fail ("Expected N bindings, saw 0").
NEW
156
  const { sql, bindings } = query.toSQL()
×
157

NEW
158
  const { rows } = await db.raw<{ rows: { 'QUERY PLAN': ExplainResult[] }[] }>(
×
159
    `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) ${sql}`,
160
    bindings as readonly unknown[],
161
  )
NEW
162
  return rows[0]['QUERY PLAN'][0]
×
163
}
164

165
function formatDuration(ms: number): string {
NEW
166
  if (ms < 1) {
×
NEW
167
    return `${(ms * 1000).toFixed(0)} µs`
×
168
  }
NEW
169
  if (ms < 1000) {
×
NEW
170
    return `${ms.toFixed(2)} ms`
×
171
  }
NEW
172
  return `${(ms / 1000).toFixed(2)} s`
×
173
}
174

175
function buildCases(): BenchmarkCase[] {
NEW
176
  return [
×
177
    {
178
      name: 'REQ: authors + kinds ORDER BY created_at DESC',
179
      description:
180
        'NIP-01 REQ with a single pubkey filter + kind=TEXT_NOTE. Canonical per-author subscription; shape matches EventRepository.findByFilters and should hit events_active_pubkey_kind_created_at_idx.',
NEW
181
      skipIf: (ctx) => (ctx.samplePubkey ? undefined : 'no events rows found'),
×
182
      build: (ctx) =>
NEW
183
        ctx.db('events')
×
184
          .select('event_id', 'event_pubkey', 'event_kind', 'event_created_at')
185
          .where('event_pubkey', ctx.samplePubkey as Buffer)
186
          .whereIn('event_kind', [ctx.kind])
187
          .orderBy('event_created_at', 'desc')
188
          .orderBy('event_id', 'asc')
189
          .limit(ctx.limit),
190
    },
191
    {
192
      name: 'REQ: kind + created_at time range',
193
      description:
194
        'REQ with no authors but a time window and a kind. Matches findByFilters for the (kinds, since, until) case; exercises the (kind, created_at) access paths.',
195
      build: (ctx) => {
NEW
196
        const now = Math.floor(Date.now() / 1000)
×
NEW
197
        const since = now - ctx.horizonSeconds
×
NEW
198
        return ctx.db('events')
×
199
          .select('event_id')
200
          .where('event_kind', ctx.kind)
201
          .whereBetween('event_created_at', [since, now])
202
          .orderBy('event_created_at', 'desc')
203
          .orderBy('event_id', 'asc')
204
          .limit(ctx.limit)
205
      },
206
    },
207
    {
208
      name: 'hasActiveRequestToVanish (pubkey + kind=62 + not deleted)',
209
      description:
210
        'Exact query run on every inbound event via UserRepository.isVanished; latency here is a per-message tax. This is the only hot path that filters on deleted_at.',
NEW
211
      skipIf: (ctx) => (ctx.samplePubkey ? undefined : 'no events rows found'),
×
212
      build: (ctx) =>
NEW
213
        ctx.db('events')
×
214
          .select('event_id')
215
          .where('event_pubkey', ctx.samplePubkey as Buffer)
216
          .where('event_kind', EventKinds.REQUEST_TO_VANISH)
217
          .whereNull('deleted_at')
218
          .limit(1),
219
    },
220
    {
221
      name: 'Purge scan (soft-deleted rows)',
222
      description:
223
        'MaintenanceWorker retention sweep; hits events_deleted_at_partial_idx when present.',
224
      build: (ctx) =>
NEW
225
        ctx.db('events').select('event_id').whereNotNull('deleted_at').limit(ctx.limit),
×
226
    },
227
    {
228
      name: 'Purge scan (expired events)',
229
      description:
230
        'Retention sweep by expires_at; already served by the existing expires_at btree.',
231
      build: (ctx) => {
NEW
232
        const now = Math.floor(Date.now() / 1000)
×
NEW
233
        return ctx.db('events').select('event_id').where('expires_at', '<', now).limit(ctx.limit)
×
234
      },
235
    },
236
    {
237
      name: 'findPendingInvoices (status=pending ORDER BY created_at)',
238
      description:
239
        'Exact shape of InvoiceRepository.findPendingInvoices; hits invoices_pending_created_at_idx when present.',
NEW
240
      skipIf: (ctx) => (ctx.invoiceCount > 0 ? undefined : 'invoices table is empty'),
×
241
      build: (ctx) =>
NEW
242
        ctx
×
243
          .db('invoices')
244
          .select('id')
245
          .where('status', InvoiceStatus.PENDING)
246
          .orderBy('created_at', 'asc')
247
          .offset(0)
248
          .limit(ctx.limit),
249
    },
250
  ]
251
}
252

253
async function gatherContext(db: Knex, options: CliOptions): Promise<BenchContext> {
NEW
254
  const [{ count: eventCountText = '0' } = { count: '0' }] = await db('events').count('* as count')
×
NEW
255
  const [{ count: invoiceCountText = '0' } = { count: '0' }] = await db('invoices').count('* as count')
×
256
  // Pick any pubkey with rows — production REQ does not filter on deleted_at,
257
  // so the benchmark should not either.
NEW
258
  const sample = await db('events').select('event_pubkey').limit(1).first()
×
259

NEW
260
  return {
×
261
    db,
262
    samplePubkey: sample?.event_pubkey,
263
    eventCount: Number(eventCountText),
264
    invoiceCount: Number(invoiceCountText),
265
    kind: options.kind,
266
    limit: options.limit,
267
    horizonSeconds: options.horizonDays * 86400,
268
  }
269
}
270

271
function printHeader(ctx: BenchContext, options: CliOptions): void {
NEW
272
  console.log('Nostream query benchmark')
×
NEW
273
  console.log('------------------------')
×
NEW
274
  console.log(`events rows:          ${ctx.eventCount.toLocaleString()}`)
×
NEW
275
  console.log(`invoices rows:        ${ctx.invoiceCount.toLocaleString()}`)
×
NEW
276
  console.log(`sample pubkey:        ${ctx.samplePubkey ? ctx.samplePubkey.toString('hex').slice(0, 16) + '…' : '<none>'}`)
×
NEW
277
  console.log(`runs per query:       ${options.runs}`)
×
NEW
278
  console.log(`kind (REQ/time):      ${options.kind}`)
×
NEW
279
  console.log(`limit:                ${options.limit}`)
×
NEW
280
  console.log(`time horizon (days):  ${options.horizonDays}`)
×
NEW
281
  console.log('')
×
282
}
283

284
async function runCase(db: Knex, runs: number, testCase: BenchmarkCase, ctx: BenchContext): Promise<void> {
NEW
285
  const skip = testCase.skipIf?.(ctx)
×
NEW
286
  console.log(`• ${testCase.name}`)
×
NEW
287
  if (skip) {
×
NEW
288
    console.log(`  skipped: ${skip}`)
×
NEW
289
    console.log('')
×
NEW
290
    return
×
291
  }
NEW
292
  console.log(`  ${testCase.description}`)
×
293

NEW
294
  const timings: number[] = []
×
NEW
295
  let planningTime = 0
×
NEW
296
  let indexes: string[] = []
×
NEW
297
  let scans: string[] = []
×
NEW
298
  let rowsReturned = 0
×
299

NEW
300
  for (let i = 0; i < runs; i++) {
×
NEW
301
    const plan = await explain(db, testCase.build(ctx))
×
NEW
302
    timings.push(plan['Execution Time'])
×
NEW
303
    planningTime = plan['Planning Time']
×
NEW
304
    const summary = summarizePlan(plan.Plan)
×
NEW
305
    indexes = summary.indexes
×
NEW
306
    scans = summary.scans
×
NEW
307
    rowsReturned = plan.Plan['Actual Rows'] ?? 0
×
308
  }
309

NEW
310
  const min = Math.min(...timings)
×
NEW
311
  const max = Math.max(...timings)
×
NEW
312
  const avg = timings.reduce((a, b) => a + b, 0) / timings.length
×
313

NEW
314
  console.log(
×
315
    [
316
      `  exec (min/avg/max): ${formatDuration(min)} / ${formatDuration(avg)} / ${formatDuration(max)}`,
317
      `  planning:           ${formatDuration(planningTime)}`,
318
      `  rows returned:      ${rowsReturned.toLocaleString()}`,
319
      `  node types:         ${scans.join(', ') || '<n/a>'}`,
×
320
      `  indexes used:       ${indexes.length ? indexes.join(', ') : '<none — sequential scan>'}`,
×
321
    ].join('\n'),
322
  )
NEW
323
  console.log('')
×
324
}
325

326
async function main(): Promise<void> {
NEW
327
  const options = parseArgs(process.argv.slice(2))
×
NEW
328
  const db = getMasterDbClient()
×
329

NEW
330
  try {
×
NEW
331
    const ctx = await gatherContext(db, options)
×
NEW
332
    printHeader(ctx, options)
×
NEW
333
    for (const testCase of buildCases()) {
×
NEW
334
      await runCase(db, options.runs, testCase, ctx)
×
335
    }
336
  } finally {
NEW
337
    await db.destroy()
×
338
  }
339
}
340

NEW
341
main().catch((error) => {
×
NEW
342
  console.error('Benchmark failed:', error)
×
NEW
343
  process.exitCode = 1
×
344
})
STATUS · Troubleshooting · Open an Issue · Sales · Support · CAREERS · ENTERPRISE · START FREE · SCHEDULE DEMO
ANNOUNCEMENTS · TWITTER · TOS & SLA · Supported CI Services · What's a CI service? · Automated Testing

© 2026 Coveralls, Inc