auto-data-snapshot: 2026-09-23T23:13:08 (1 data files) — scripts/migrate_010_search_trgm.sql

3de634868302983d0e2806b230d50b5e7075335d · 2026-09-23 23:13:29 -0700 · auto-commit-fleet

Files touched

A scripts/migrate_010_search_trgm.sql

Diff

commit 3de634868302983d0e2806b230d50b5e7075335d
Author: auto-commit-fleet <steve@designerwallcoverings.com>
Date:   Wed Sep 23 23:13:29 2026 -0700

    auto-data-snapshot: 2026-09-23T23:13:08 (1 data files) — scripts/migrate_010_search_trgm.sql
---
 scripts/migrate_010_search_trgm.sql | 28 ++++++++++++++++++++++++++++
 1 file changed, 28 insertions(+)

diff --git a/scripts/migrate_010_search_trgm.sql b/scripts/migrate_010_search_trgm.sql
new file mode 100644
index 0000000..5b2d44f
--- /dev/null
+++ b/scripts/migrate_010_search_trgm.sql
@@ -0,0 +1,28 @@
+-- migrate_010_search_trgm.sql — trigram GIN indexes for the public text search.
+--
+-- WHY: /api/search and /api/places filter with a LEADING-wildcard LIKE '%q%' on
+-- lower(name)/lower(address)/lower(description). A leading wildcard can't use a btree,
+-- so every search full-scanned the places table (34k rows and growing). The
+-- /api/places + /api/search COUNT(*) queries have no LIMIT, so they ALWAYS scanned
+-- the whole table; the list queries scanned too (and /api/search can't even
+-- short-circuit on a PK-ordered LIMIT because it ORDER BYs a computed rank).
+--
+-- With pg_trgm these `LOWER(col) LIKE '%q%'` predicates become Bitmap Index Scans.
+-- Verified on the dev DB: EXPLAIN went from a seq/index-filter scan of all rows to
+--   Bitmap Heap Scan -> BitmapOr(idx_places_name_trgm, _addr_trgm, _desc_trgm).
+-- The index expression `lower(col) gin_trgm_ops` matches the query's `LOWER(col)`
+-- exactly (an index on `col` alone would NOT be used for the case-folded predicate).
+--
+-- CORRECTNESS: indexes change only the PLAN, never the result set — search output is
+-- unchanged. NULL address/description are handled by GIN (a NULL simply isn't indexed
+-- and LIKE on NULL is not-true, same as before).
+--
+-- 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).
+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);

← 9afa84b cycle 14 docs: YOLO_NOTES ledger + GO-LIVE processorFee prer · back to Costa Rica · costa-rica: trigram GIN indexes for the search hot path + /a 3bd9aa2 →