Performance: unindexed lookups in IsOnWhatsapp (LIKE '%…%') and Message (key->>'id') saturate CPU under concurrent number checks (v2.3.7) #2753

Environment

• evoapicloud/evolution-api:v2.3.7, Docker Swarm
• PostgreSQL 14, Prisma, 4 vCPU host, connection_limit=80
• ~413 instances (62 open, the rest connecting/close)

Problem

Bursts of number checks (/chat/whatsappNumbers, sendText) produce up to 80 simultaneous identical queries on "IsOnWhatsapp". All 80 pool connections are busy on CPU (no wait events), host CPU hits 99% user, and unrelated requests stall: fetchInstances goes from ~1 s to 20–33 s and Instance status updates wait 20+ s. It happened 9 times in a single working day.

The query (captured with log_min_duration_statement):

SELECT "id","remoteJid","jidOptions","lid","createdAt","updatedAt"
FROM "public"."IsOnWhatsapp"
WHERE ("jidOptions"::text LIKE $1 OR "jidOptions"::text LIKE $2 OR "remoteJid" = $3)
LIMIT $4 OFFSET $5
-- $1 = '%5511999990000@s.whatsapp.net%'
-- $2 = '%551199990000@s.whatsapp.net%'
-- $3 = '551199990000@s.whatsapp.net'
-- $4 = 1, $5 = 0

jidOptions has no index, so every call is a sequential scan.

Measurements (table: ~420k rows, ~78 MB)

The trigram index helps, but it stays at ~85 ms per lookup because the common suffix @s.whatsapp.net is present in every row, so the GIN scan reads ~1,650 buffers per pattern. 80 concurrent lookups × 85 ms is still ~7 s of CPU on 4 vCPUs.

In v2.3.7 the onWhatsApp cache check runs with Promise.allSettled over all numbers, so one request with N numbers runs N of these queries at once, unbounded.

Second hot spot: Message

The messages.update path does:

SELECT ... FROM "Message" WHERE "instanceId" = $1 AND "key"->>'id' = $2 LIMIT 1

There is no index for this, so it is a seq scan: 26.5 s cold / 1.5 s warm in the worst case I measured. After

CREATE INDEX CONCURRENTLY ON "Message" ("instanceId", (("key"->>'id')));

(621 MB) the same lookup takes 0.09 ms.

Suggestions

1. Ship the Message expression index in a migration (Prisma schema can't express it, so a raw SQL migration).
2. For IsOnWhatsapp: look up by exact remoteJid IN (...) first (unique index) and only fall back to the LIKE search on a miss, or store the normalized number variants in an indexed column instead of a comma-separated jidOptions searched with a leading wildcard.
3. Bound the concurrency of the onWhatsApp cache lookups (e.g. p-limit with 5–10) instead of unbounded Promise.allSettled.

Happy to share more EXPLAIN (ANALYZE, BUFFERS) output.