2

performance notes

fen edited this page 2026-09-09 19:15:44 -05:00

Table of Contents

• Performance notes (#32)
• Current behavior
• Measured latency (synthetic rows, scratch SQLite DB)
• Future design: server-side /api/search?q= (#32 remainder)

Performance notes (#32)

Background: history and saved pages filter client-side. Each load fetches the most recent rows from the list endpoint (limit=100 per query is the current client cap in static/table.js) and filters/sorts in the browser. This note records current behavior, measured latency, and the design for a future server-side search endpoint. Measurements only — no implementation in #4/#32.

Current behavior

• /api/public?limit=100&offset=0 and /api/mine?limit=100&offset=0 return up to 100 rows (the server clamps list limit at 100) (id, title, language, created_at, view_count, size, visibility, custom_slug, is_can). Content is NOT included — only LENGTH(content).
• The browser applies the search-box filter (title/language/id substring) and column sorting locally over the fetched window.
• Consequence: search only covers the fetched window (100 most recent rows). Older rows are invisible to search until paginated through, and each query ships ~4 KB of row metadata regardless of how few rows the user will look at.

Measured latency (synthetic rows, scratch SQLite DB)

Rows are synthetic pastes (~200 B content each, indexed like production: idx_pastes_visibility_created). Queried GET /api/public?limit=100 (modernc.org/sqlite, WAL, single connection — same as production).

Rows in table	Bulk insert	First query	Avg query (10 runs)	Payload
1,000	19 ms	1.0 ms	0.44 ms	~4.1 KB
5,000	95 ms	1.0 ms	0.98 ms	~4.1 KB
10,000	189 ms	2.0 ms	1.78 ms	~4.1 KB

Interpretation:

• The list query itself is cheap (< 2 ms at 10k rows); latency users perceive comes from network + browser rendering of 100 rows, not SQL.
• The current design scales fine to ~10k pastes. Beyond that, shipping 100 rows per keystroke-refresh cycle is wasteful and search coverage stays capped at the window.

Future design: server-side /api/search?q= (#32 remainder)

• Endpoint: GET /api/search?q=<term>&limit=25&offset=0.
• SQL: SELECT ... FROM pastes WHERE deleted_at IS NULL AND (expires_at IS NULL OR expires_at > ?) AND (title LIKE ? OR content LIKE ?) ORDER BY created_at DESC LIMIT ? OFFSET ? — term wrapped as %term%, escaped (%, _). Visibility scoping mirrors ListPublic/ListMine (public + viewer_id for the saved-page variant).
• Indexing: LIKE with a leading wildcard cannot use a B-tree index. Options, in order of effort:
  1. Accept a table scan — fine at ≤ ~50k rows (10k rows scanned in ~2 ms).
  2. Add an index on title for prefix search (q*) and keep %q% scan only as a fallback.
  3. SQLite FTS5 virtual table (CREATE VIRTUAL TABLE pastes_fts USING fts5(title, content)) for token search — best relevance, needs sync on insert/delete and a migration.
• Cans: search should cover can titles/descriptions too (UNION ALL with paste_cans, is_can=1), matching the #4 listing integration.
• Response shape: same row objects as /api/public (plus is_can) so table.js can render results without a second code path; the client filter becomes a server query when q is non-empty.