← back to Costa Rica

costa-rica: trigram GIN indexes for the search hot path + /api/places 1-char-q guard (Cody gate, cycle 15) — TK-10346

3bd9aa2c246acb4591b39f2f54cbd6ff25f81f0f · 2026-09-23 23:16:25 -0700 · Steve

SQL-correctness review. NO correctness bugs found: admin/build stats use independent
scalar subqueries (no double-count JOIN), all queries are parameterized (no
injection), the booking overlap query is already indexed (idx_bookings_place_status_checkin
+ a gist EXCLUDE). The one real, MEASURED issue was search performance.

/api/search + /api/places filter with a leading-wildcard LIKE '%q%' on
lower(name)/lower(address)/lower(description) — unindexable by btree, so every search
full-scanned the 34,285-row (growing) places table; the no-LIMIT COUNT(*) queries
always did, and /api/search can't short-circuit (it ORDER BYs a computed rank).

Fix: scripts/migrate_010_search_trgm.sql — pg_trgm + three GIN trigram indexes on
lower(name|address|description). Measured on dev (Cody independently benchmarked):
the count query went from a ~30ms parallel seq scan to a ~1.5ms Bitmap Index Scan
(BitmapOr of the three trgm indexes) — a ~20x win on the two hottest public reads,
for ~11.6MB index storage. Index expression lower(col) gin_trgm_ops matches the
query's LOWER(col) LIKE exactly (all call sites .toLowerCase() the pattern in JS).

Cody gate — FIX FIRST, both applied:
1. The migration recommended CONCURRENTLY in a comment but shipped plain CREATE
   INDEX (a copy-paste footgun: a plain build SHARE-locks the live places table,
   ~620ms today, worse as it grows). Changed to CREATE INDEX CONCURRENTLY (safe:
   apply-migrations.sh runs file 010 outside a transaction) + an invalid-index
   recovery note. Also added the benchmarked write-cost justification (~44µs/row GIN
   overhead, immaterial vs the network-bound one-row-per-fetch scraper writes;
   hacienda-enricher never touches these columns).
2. /api/places had NO min-length floor on q (unlike /api/search's length>=2) — a
   1-char ?q=a full-scanned and returned ~the whole directory. Added a >=2 floor
   (verified via dev EXPLAIN: a short q now builds the plain PK-index listing, no
   scan).

NOTE (future cycle): inline server.js routes (~20 of them, incl. /api/places) have
no test harness — server.js listens on import and doesn't export the app. The q-guard
is verified by dev EXPLAIN, not a route unit test; a follow-up should export the app
(guard app.listen behind require.main === module) to make these routes testable.

Extension + indexes applied to the dev DB (reversible: DROP INDEX/EXTENSION). Prod
CREATE EXTENSION pg_trgm needs superuser -> Steve-gated (documented in the migration).
Suite unchanged at 185/185 (indexes don't affect app behavior/results).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01TouFkmUGKHtwqpZwgReVic

Files touched

• M scripts/migrate_010_search_trgm.sql
• M server.js

Diff

commit 3bd9aa2c246acb4591b39f2f54cbd6ff25f81f0f
Author: Steve <steve@designerwallcoverings.com>
Date:   Wed Sep 23 23:16:25 2026 -0700

    costa-rica: trigram GIN indexes for the search hot path + /api/places 1-char-q guard (Cody gate, cycle 15) — TK-10346
    
    SQL-correctness review. NO correctness bugs found: admin/build stats use independent
    scalar subqueries (no double-count JOIN), all queries are parameterized (no
    injection), the booking overlap query is already indexed (idx_bookings_place_status_checkin
    + a gist EXCLUDE). The one real, MEASURED issue was search performance.
    
    /api/search + /api/places filter with a leading-wildcard LIKE '%q%' on
    lower(name)/lower(address)/lower(description) — unindexable by btree, so every search
    full-scanned the 34,285-row (growing) places table; the no-LIMIT COUNT(*) queries
    always did, and /api/search can't short-circuit (it ORDER BYs a computed rank).
    
    Fix: scripts/migrate_010_search_trgm.sql — pg_trgm + three GIN trigram indexes on
    lower(name|address|description). Measured on dev (Cody independently benchmarked):
    the count query went from a ~30ms parallel seq scan to a ~1.5ms Bitmap Index Scan
    (BitmapOr of the three trgm indexes) — a ~20x win on the two hottest public reads,
    for ~11.6MB index storage. Index expression lower(col) gin_trgm_ops matches the
    query's LOWER(col) LIKE exactly (all call sites .toLowerCase() the pattern in JS).
    
    Cody gate — FIX FIRST, both applied:
    1. The migration recommended CONCURRENTLY in a comment but shipped plain CREATE
       INDEX (a copy-paste footgun: a plain build SHARE-locks the live places table,
       ~620ms today, worse as it grows). Changed to CREATE INDEX CONCURRENTLY (safe:
       apply-migrations.sh runs file 010 outside a transaction) + an invalid-index
       recovery note. Also added the benchmarked write-cost justification (~44µs/row GIN
       overhead, immaterial vs the network-bound one-row-per-fetch scraper writes;
       hacienda-enricher never touches these columns).
    2. /api/places had NO min-length floor on q (unlike /api/search's length>=2) — a
       1-char ?q=a full-scanned and returned ~the whole directory. Added a >=2 floor
       (verified via dev EXPLAIN: a short q now builds the plain PK-index listing, no
       scan).
    
    NOTE (future cycle): inline server.js routes (~20 of them, incl. /api/places) have
    no test harness — server.js listens on import and doesn't export the app. The q-guard
    is verified by dev EXPLAIN, not a route unit test; a follow-up should export the app
    (guard app.listen behind require.main === module) to make these routes testable.
    
    Extension + indexes applied to the dev DB (reversible: DROP INDEX/EXTENSION). Prod
    CREATE EXTENSION pg_trgm needs superuser -> Steve-gated (documented in the migration).
    Suite unchanged at 185/185 (indexes don't affect app behavior/results).
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_01TouFkmUGKHtwqpZwgReVic
---
 scripts/migrate_010_search_trgm.sql | 25 +++++++++++++++++++------
 server.js                           |  7 ++++++-
 2 files changed, 25 insertions(+), 7 deletions(-)

diff --git a/scripts/migrate_010_search_trgm.sql b/scripts/migrate_010_search_trgm.sql
index 5b2d44f..d178ac6 100644
--- a/scripts/migrate_010_search_trgm.sql
+++ b/scripts/migrate_010_search_trgm.sql
@@ -17,12 +17,25 @@
 -- unchanged. NULL address/description are handled by GIN (a NULL simply isn't indexed
 -- and LIKE on NULL is not-true, same as before).
 --
+-- WRITE COST (benchmarked, immaterial): a GIN trgm write adds ~44µs/row vs a plain
+-- update (measured: a full 34k-row UPDATE of all 3 columns = 4.31s vs 2.83s). The
+-- ingest scrapers that touch name/address/description (upsertPlace) write ONE row per
+-- HTTP fetch, sleep-throttled, network-bound at 100s of ms/row — the ~44µs GIN cost is
+-- lost in the noise. hacienda-enricher only writes hacienda_* columns, so it never
+-- touches these indexes at all (HOT-eligible updates). So the write overhead is real
+-- but negligible for this codebase's actual write pattern.
+--
+-- CONCURRENTLY: built with CREATE INDEX CONCURRENTLY so the prod build does NOT take a
+-- SHARE lock that blocks writes to the live `places` table while the scrapers run.
+-- apply-migrations.sh runs this file OUTSIDE a transaction (only 004/008/009 are wrapped),
+-- so CONCURRENTLY is safe through the existing runner. If a CONCURRENTLY build is
+-- interrupted it leaves an INVALID index — recover with:
+--   DROP INDEX CONCURRENTLY IF EXISTS idx_places_<col>_trgm;  -- then re-run this file.
+--
 -- PROD-APPLY (Steve-gated): CREATE EXTENSION pg_trgm requires SUPERUSER / rds_superuser.
--- On Kamatera prod, run the CREATE EXTENSION as the superuser first, then the indexes
--- (which are safe, additive, and CONCURRENTLY-able if built on a live table — consider
--- CREATE INDEX CONCURRENTLY for the prod build to avoid a write lock on places).
+-- On Kamatera prod, run the CREATE EXTENSION as the superuser first, then this file.
 CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
-CREATE INDEX IF NOT EXISTS idx_places_name_trgm ON places USING gin (lower(name)        gin_trgm_ops);
-CREATE INDEX IF NOT EXISTS idx_places_addr_trgm ON places USING gin (lower(address)     gin_trgm_ops);
-CREATE INDEX IF NOT EXISTS idx_places_desc_trgm ON places USING gin (lower(description) gin_trgm_ops);
+CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_places_name_trgm ON places USING gin (lower(name)        gin_trgm_ops);
+CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_places_addr_trgm ON places USING gin (lower(address)     gin_trgm_ops);
+CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_places_desc_trgm ON places USING gin (lower(description) gin_trgm_ops);
diff --git a/server.js b/server.js
index b1f1328..34036e4 100644
--- a/server.js
+++ b/server.js
@@ -388,7 +388,12 @@ app.get('/api/places', async (req, res) => {
       args.push(req.query.region);
       where.push(`r.slug = $${args.length}`);
     }
-    if (req.query.q) {
+    // Only apply the text filter for q >= 2 chars. A 1-char q can't use the trigram
+    // index (pg_trgm needs >= 3 chars) and its leading-wildcard LIKE would full-scan
+    // the whole places table and return ~the entire directory as a "search result"
+    // (matches /api/search's own length>=2 floor). A too-short q is ignored -> the
+    // normal paginated listing, no scan. (Cody gate, cycle 15.)
+    if (req.query.q && String(req.query.q).trim().length >= 2) {
       args.push(`%${req.query.q.toLowerCase()}%`);
       where.push(`(LOWER(p.name) LIKE $${args.length} OR LOWER(p.description) LIKE $${args.length} OR LOWER(p.address) LIKE $${args.length})`);
     }

← 3de6348 auto-data-snapshot: 2026-09-23T23:13:08 (1 data files) — scr  ·  back to Costa Rica  ·  cycle 15 docs: YOLO_NOTES ledger + GO-LIVE pg_trgm prerequis f899e1c →