descrybe/docs/perf-notes.md

Performance notes (descrybe-v2 Go API)

Date: 2026-08-04

Stack: Go API (apps/api) + Postgres (goose migrations under apps/api/sql/schema).
Not the legacy Next.js / Drizzle repo (descrybe).

Goals

Bound round-trips and memory on hot paths for large catalogs (Local Demo Co scale): product lists, feed sync, export feeds.

Review vs legacy TS wins

Concern	Legacy TS (descrybe)	Go v2 (descrybe-v2)
Product list count + page	Parallel Promise.all	Added parallelCountAndList in internal/catalog
Slim list columns	Drizzle relation trim	Already selects list columns (not SELECT *) on HTTP list path
Feed sync re-parse every chunk	Bug in syncFeedChunk loop	Already OK — one download/parse per Sync, row callback + upsertChunk
XML/CSV export streaming	Added stream + disk cache	Already OK — streamExport / streamXML / streamCSV over pgx.Rows + flush
List indexes	Drizzle 0028_… MySQL	Added goose 018_list_hotpath_indexes.sql (Postgres)

Changes landed on v2

0. Scale small wins (2026-08-09)

• Keyset enforcement — normalizeProductListFilter caps product limit at 200 and rejects deep OFFSET (>5000) unless cursor/after_id is set.
• pgx pool jitter — DB_MAX_CONN_LIFETIME_JITTER (default 6m) + sizing docs in docs/ops-runtime.md.
• Sync concurrency cap — jobs.SyncSlots / ClampSyncWorkers (default 1, max 2) in cmd/worker for feed/Woo/Shopify claims.

1. Parallel product list (apps/api/internal/catalog/service.go)

• ListRawProducts, ListProcessedProducts, ListProcessedProductsDetailed run count and page queries concurrently via parallelCountAndList.
• Arg slices are copied so limit/offset appends do not race the count query.

2. Feed sync (apps/api/internal/feeds/sync.go + parse.go)

No code change required for the legacy re-parse bug:

• runSync loads the feed once, parses once (parseXMLItems / parseCSV with per-row callback).
• Upserts in upsertChunkSize (100) with job progress updates.
• Content-hash short-circuit skips parse when the feed body is unchanged.

3. Export streaming (apps/api/internal/feeds/export.go)

No code change required:

• Public and generate paths use streamExport → streamXML / streamCSV.
• Rows come from a single scoped SQL query (LIMIT capped by exportMaxProducts); writers flush every exportChunkHint (500) rows.
• Content is not buffered as a full XML string for the public stream path.

4. Indexes (goose 018_list_hotpath_indexes.sql)

• processed_products_company_updated_idx — (company_id, updated_at DESC)
• processed_products_company_status_updated_idx — (company_id, status, updated_at DESC)
• raw_products_company_updated_idx — (company_id, updated_at DESC)
• raw_products_company_processed_updated_idx — (company_id, is_processed, updated_at DESC)
• categories_company_parent_idx — (company_id, parent_unique_id)

Apply:

cd f:\laragon\www\_MY\descrybe-v2
$env:DATABASE_URL = "postgres://descrybe:descrybe@localhost:5433/descrybe?sslmode=disable"
.\scripts\migrate.ps1

Smoke (Local Demo Co)

Repeatable low-concurrency harness (healthz + v1 feeds/products list), RPS assumptions, and how to scale up: scripts/api-load-smoke/README.md. Default API base is http://127.0.0.1:28471 (HTTP_ADDR).

cd f:\laragon\www\_MY\descrybe-v2\scripts\api-load-smoke
go run .   # -c 2 -d 5s -rate 6; ~6 aggregate RPS

One-off Measure-Command (API key from docs/demo-user.md):

$h = @{ Authorization = "Bearer dk_demo_local_descrybe_test_key_v1" }
Measure-Command { Invoke-RestMethod -Headers $h "http://127.0.0.1:28471/api/v1/products?limit=25&offset=0" }
Measure-Command { Invoke-RestMethod -Headers $h "http://127.0.0.1:28471/api/v1/products?kind=raw&limit=25&offset=0" }

Restart the API process after deploying Go changes so handlers pick up parallelCountAndList.

API docs (/docs) performance (2026-08-04)

Viewer: RapiDoc (OSS web component; no Scalar cloud). Disk sizes:

Asset	Raw	Gzip
RapiDoc rapidoc-min.js	~843 KB	~213 KB
Scalar ESM (previous)	691 KB	~154 KB (+ lazy chunks; ~3.8 MB total vendor tree)
Scalar IIFE (older)	3661 KB	~1052 KB
OpenAPI YAML (v1_openapi.go)	~74 KB	~12 KB

Changes:

1. RapiDoc vendor — scripts/copy-rapidoc-ui.mjs copies dist/rapidoc-min.js and writes .gz. Single file, no cloud registry/agent chunks.
2. hooks.server.ts — long-lived Cache-Control for /vendor/rapidoc/*; serves precompressed .gz when Accept-Encoding: gzip.
3. /docs page — type=module load, modulepreload, early prefetchOpenApiSpec + loadSpec(blobUrl) (no double fetch), theme via attribute (no remount).
4. OpenAPI handler — gzip body when requested (Vary: Accept-Encoding); coordinates with sibling Cache-Control / ETag / 304 work.

Verify: node apps/web/scripts/copy-rapidoc-ui.mjs, then load /docs and confirm Network shows ~843 KB (or ~213 KB gzip) for rapidoc-min.js and Content-Encoding: gzip on /api/v1/openapi.yaml.

Benchmark / smoke (this run)

API was up at http://127.0.0.1:8080. Goose 018 applied (indexes live immediately). Go parallel list is in source — restart cmd/api to load it.

Local Demo Co via demo API key (3 probes each; cold then warm):

Probe	total	page	ms (3 runs)	avg
GET /api/v1/products?limit=25&offset=0 (processed)	4337	25	90,19,10	~40
GET /api/v1/products?limit=25&offset=1000	4337	25	14,10,9	~11
GET /api/v1/products?kind=raw&limit=25&offset=0	23775	25	12,8,8	~9
GET /api/v1/products?kind=raw&limit=25&offset=1000	23775	25	35,29,26	~30

go build ./... OK. Tests: ./internal/catalog ./internal/feeds ./internal/httpapi OK.

Million-SKU backlog (prioritized)

Operator/dev map from web research → concrete paths. Status notes reflect current tree (2026-08-04); do not treat “partial” as done at 1M SKU.

P0 — do first (blocks million-SKU)

1. Keyset pagination — replace deep OFFSET with (updated_at, id) / (created_at, id) cursors.
   • Enforced (2026-08-09): product lists reject offset > 5000 without cursor/after_id (MaxOffsetWithoutCursor); product page limit capped at 200 (MaxProductPageLimit) in normalizeProductListFilter.
   • API: apps/api/internal/catalog/service.go + cursor.go, handlers expose next_cursor / next_after_id.
   • Web: apps/web/src/lib/list.ts (pageOffset), apps/web/src/lib/components/products/ProductPagination.svelte — still prefer cursor over deep offset.
2. Set-based feed upserts — today upsertChunk uses pgx.Batch of per-row INSERT/UPDATE (apps/api/internal/feeds/sync.go). Move to set-based INSERT … SELECT / UNNEST (or COPY + merge) for chunk apply.
3. Kill JSONB ILIKE (full cast) — CAST(mapped_data/raw_data AS text) ILIKE is already avoided; search uses keyed ->>'…' + gtin/feed ILIKE in appendRawProductFilters (apps/api/internal/catalog/service.go). Remaining P0: measure leading-wildcard ILIKE; add pg_trgm/expression indexes or constrain search to prefix/FTS before 1M.
4. Stream feed download + parse — downloadFeed still io.ReadAll into a size-capped buffer (apps/api/internal/feeds/download.go); wired via loadFeedSource (source.go) into Sync (sync.go). Goal: stream HTTP → parser without holding the full body when possible.
5. AI Batch API + content-hash skip — Hash-skip landed (HashEnhanceInput + field_sources.enhance_input_hash in apps/api/internal/processing; loaded in processOne before RunSteps / ConsumeCredits). Sync Completer / CompleteWithOptions is still per-SKU. OpenAI Batch deferred: needs a job-level coordinator (JSONL submit -> batch_id -> reconcile -> debit) that does not fit the current sync completer interface; ~50% OpenAI discount remains a follow-up.
6. Tenant-leading indexes — composites landed in 018_list_hotpath_indexes.sql / 020_raw_list_created_indexes.sql, but single-column leftovers from apps/api/sql/schema/002_catalog.sql remain (e.g. raw_products_company_id_idx, raw_products_gtin_idx, raw_products_feed_id_idx, raw_products_processing_status_idx, processed_products_company_id_idx, processed_products_product_id_idx). Drop or replace with (company_id, …) leading keys where planners still pick the narrow indexes.

P1 — next (fairness, scale edges, ops)

• Per-tenant job fairness — apps/api/internal/jobs/river.go (+ processing claim path in pipeline.go): avoid one company starving the queue.
• Export beyond 50k — exportMaxProducts = 50000 in apps/api/internal/feeds/export.go; raise via keyset/chunked export or a read model (see P2).
• CSV import batching — apps/api/internal/catalog/import_csv.go + httpapi/catalog_import_handlers.go: per-row Exec today; batch/COPY.
• Covering indexes — extend list/export SELECT lists to match index INCLUDE / composite keys (build on 018/020).
• Observability — request/query timing on list, sync, process, export; slow-query + job duration dashboards.

P2 — later (architecture)

• Partition plan — raw_products / processed_products by company_id (or time) once single-tenant size dominates vacuum/autovacuum.
• Export read model — denormalized export rows so public/generate streams avoid heavy joins at read time.
• Stream XML via encoding/xml Token writer if payload construction grows heavy.
• Optional NDJSON disk cache for multi-worker resume of a single sync job (in-process Sync already avoids re-parse).