Contact search is CPU-bound and degrades linearly with project size

[pausan]

Description

GET /contacts?search= performs a case-insensitive substring match (email ILIKE '%term%') that no existing index can serve, so PostgreSQL evaluates the pattern against every contact row in the project on every request.

Three compounding causes:

1. ILIKE '%term%' is not sargable. The indexes on contacts are (projectId, email) unique, (projectId), (projectId, subscribed), (snoozedUntil) and a GIN on data. None can satisfy a leading-wildcard case-insensitive match, so every row in the project is scanned and case-folded — CPU-bound work, not I/O.

2. No index covers the sort. ORDER BY "createdAt" DESC, id DESC has no supporting index, so the LIMIT 21 cannot early-exit via an ordered index walk. The full match set is materialised and top-N sorted. This also affects the unfiltered contact list — the page every user lands on.

3. The first page also runs an uncapped COUNT. ContactService.ts:119 computes total on every first page. Same predicate, no LIMIT, so no early exit is possible. Because changing the search resets pagination to page 1, every debounced keystroke runs both queries.

Search is debounced but has no minimum length on the contacts page, so a single character (a) matches nearly every contact — the worst case for both the scan and the sort.

This is a pre-existing endpoint degrading with data growth, not a regression from a specific release.

To Reproduce

Prerequisite: a project with a large contact count. The effect is proportional to contacts-per-project and is not observable on a small dataset. Measurements below used 2,000,000 contacts in one project (2.4M rows total across tenants).

Path A — Contacts page search (primary, highest frequency)

1. Log in and open Contacts (/contacts).
2. Open browser DevTools → Network, filter to contacts.
3. Type a single common character into the "Search by email…" box — e.g. a.
4. Observe the GET /contacts?limit=…&search=a request.

Observed: the request takes hundreds of ms to multiple seconds, and server CPU spikes for its duration. Note this page enforces no minimum search length, so one character is enough to trigger it.

5. Try a common substring instead — e.g. gmail.com. Same behaviour, worst case.
6. Clear the search box entirely. The unfiltered list request is also slow, since no index covers the default createdAt DESC ordering.

Path B — Command palette

1. Press ⌘K / Ctrl+K from anywhere in the dashboard.
2. Type any single character.
3. Network tab shows GET /contacts?search=<char>&limit=5.

Fires at 1 character with a 200 ms debounce — the shortest of the three surfaces.

Path C — Segment contact picker

1. Go to Segments → create a new one or open an existing static segment (/segments/new or /segments/[id]).
2. Use the "Add contacts" picker in search mode.
3. Type into "Type an email to search…".
4. Network tab shows GET /contacts?limit=20&search=….

Path D — Bulk action over a search result

1. On /contacts, enter a search term matching many contacts (e.g. gmail.com).
2. Select all rows, then choose "select all matching".
3. Trigger a bulk subscribe / unsubscribe / delete.

This sends a query-mode selector, and bulk-contact-processor.ts:106 re-runs the same uncapped COUNT server-side before iterating. Once per job rather than per keystroke, but against the same unindexable predicate.

Path E — Direct API (no UI)

time curl -s -H "Authorization: Bearer $SECRET_KEY" \
  "$API_URI/contacts?limit=20&search=gmail.com" -o /dev/null

Confirming the cause at the database level

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, data, subscribed, "projectId", "createdAt", "updatedAt"
FROM contacts
WHERE "projectId" = '<project-id>' AND email ILIKE '%gmail.com%'
ORDER BY "createdAt" DESC, id DESC
LIMIT 21 OFFSET 0;

Expect a Seq Scan (or full index scan) with a large Rows Removed by Filter, plus a top-N heapsort.

Expected behavior

Contact search returns the first page well under the project's stated target of < 200 ms for read operations (CLAUDE.md, "Scale & Performance Requirements"), and cost should not scale linearly with the total number of contacts in the project.

Specifically:

• The substring filter should be index-served (e.g. a pg_trgm GIN index), not a per-row scan.
• The default createdAt DESC ordering should be an ordered index walk with early exit at the page limit, rather than a full sort — this applies to the unfiltered list too.
• The first-page total should not require an uncapped scan of every matching row.

Environment

Please select the option that applies:

• Deployment type:

  • Hosted
  • Self-hosted

If self-hosted, please provide relevant details (Docker, Kubernetes, bare metal, etc.):

Reproduced against a synthetic dataset on the schema at the commit below, using the stock (unmodified) query path:

• PostgreSQL 16.15 (Debian), Docker, en_US.utf8 collation
• shared_buffers=2GB, work_mem=64MB, max_parallel_workers_per_gather=2
• 2.4M contacts rows, 2.0M of them in the queried project
• Warm cache; medians of 5 runs

The dataset is synthetic (generated addresses, ~45% gmail.com to give a realistic low-selectivity term). Absolute numbers are specific to that corpus; the shape of the problem is what matters.

Version verification

⚠️ Important:
Self-hosted issues must be reproduced on the latest commit.
Issues without a confirmed commit SHA may be closed without investigation.

• I am using the hosted version

If self-hosted:

• I have confirmed this issue still exists on the latest commit (not the latest tag)

  • Commit SHA tested: 1df7bfb3a5c6bb5bf99301daa5dda1e8a80d2655

This is the current tip of next at the time of filing.

Logs / Error output

No errors are produced — the endpoint returns HTTP 200. The symptom is latency and CPU consumption. Query plans from the reproduction:

-- Page query: parallel seq scan + top-N heapsort over the whole project
Limit  (cost=141055.21..141057.66 rows=21 width=160) (actual time=381.828..392.770 rows=21 loops=1)
  Buffers: shared hit=123941
  ->  Gather Merge  (actual time=377.461..388.402 rows=21 loops=1)
        Workers Planned: 2  Workers Launched: 2
        ->  Sort  (actual time=364.756..364.759 rows=16 loops=3)
              Sort Key: "createdAt" DESC, id DESC
              Sort Method: top-N heapsort  Memory: 34kB
              ->  Parallel Seq Scan on contacts  (actual time=27.147..364.602 rows=71 loops=3)
                    Filter: ((email ~~* '%elena.pons%'::text) AND ("projectId" = 'proj_big'::text))
                    Rows Removed by Filter: 866572
Planning Time: 0.744 ms

-- Count query: index-only scan, but no LIMIT so it can never early-exit
Aggregate  (cost=42848.08..42848.09 rows=1) (actual time=331.001..336.554 rows=1 loops=1)
  Buffers: shared hit=946403
  ->  Gather  (actual time=97.377..336.536 rows=212 loops=1)
        Workers Planned: 2  Workers Launched: 2
        ->  Parallel Index Only Scan using "contacts_projectId_email_key" on contacts
                    (actual time=95.398..328.952 rows=71 loops=3)
              Index Cond: ("projectId" = 'proj_big'::text)
              Filter: (email ~~* '%elena.pons%'::text)
              Rows Removed by Filter: 666596
              Heap Fetches: 0
Execution Time: 336.592 ms

Measured medians (5 runs each, EXPLAIN ANALYZE execution time, 2M contacts in the project, warm cache):

search term	matches	page query	count query
gmail.com	900k (45%)	448 ms	902 ms
ez (2 chars)	320k (16%)	368 ms	776 ms
nguyen	40k (2%)	364 ms	406 ms
martinez	40k (2%)	362 ms	345 ms
elena.pons	212 (0.01%)	363 ms	360 ms
zzqx	0	340 ms	344 ms
(no search — default list)	—	213 ms	—

Cost is roughly flat regardless of how many rows actually match — including a term matching nothing — which is consistent with a full scan rather than a lookup.

Screenshots / Recordings / Additional context

Relevant code (line numbers at 1df7bfb):

• apps/api/src/services/ContactService.ts:88-89 — contains + mode: 'insensitive' → ILIKE
• apps/api/src/services/ContactService.ts:102-103 — orderBy [{createdAt}, {id}], no covering index
• apps/api/src/services/ContactService.ts:119 — uncapped first-page count()
• apps/api/src/jobs/bulk-contact-processor.ts:35,106 — same predicate in the bulk path
• apps/api/src/controllers/Contacts.ts:75 — endpoint
• apps/web/src/pages/contacts/index.tsx:179-185 — 350 ms debounce, no minimum length
• apps/web/src/components/CommandPalette.tsx:161 — 200 ms debounce, fires at 1 char
• apps/web/src/components/ContactPicker.tsx:83 — 300 ms debounce, fires at 1 char

Also affected by the same missing index (not search-specific): the unfiltered contact list, and SegmentService.buildStringFieldCondition email filters, which run over every contact in a project during segment evaluation.

Not affected: CSV import and API contact creation. Those use exact lookups (findByEmail / upsert) against the (projectId, email) btree.

Note on possible fixes: a pg_trgm GIN index on email addresses the substring filter, but only for terms selective enough to be worth a bitmap — it does nothing for common terms like gmail.com, where the planner correctly declines it. An index on ("projectId", "createdAt" DESC, id DESC) is what turns the page query into an early-exit ordered walk, and is the larger lever for the list itself. The uncapped COUNT is not fixable by indexing alone. Happy to open a PR if the maintainers would like one.