Part 012 — PostgreSQL Full-Text Search

Search terlihat sederhana dari sisi API:

GET /products?q=enterprise router
GET /quotes?search=customer approved renewal
GET /catalog/search?q=mobile plan

Tetapi search bukan sekadar LIKE '%keyword%'.

Search memiliki problem berbeda dari exact lookup:

• user mengetik kata tidak lengkap;
• urutan kata bisa berubah;
• kata umum harus diabaikan;
• stemming bisa menyamakan variasi kata;
• ranking harus masuk akal;
• search harus digabung dengan filter tenant/status/date;
• pagination harus stabil;
• result relevance harus bisa dijelaskan;
• index harus tetap efisien saat data membesar.

PostgreSQL menyediakan Full-Text Search atau FTS sebagai fitur native untuk mencari dokumen teks menggunakan tsvector, tsquery, dictionary, language configuration, ranking, highlighting, dan index GIN/GiST.

Untuk enterprise Java/JAX-RS system, PostgreSQL FTS berguna ketika:

• search masih berada dalam bounded domain;
• data harus konsisten dengan OLTP database;
• operational simplicity lebih penting daripada fitur search engine penuh;
• filtering relational sama pentingnya dengan text relevance;
• volume dan relevance requirement masih bisa ditangani PostgreSQL.

Namun FTS bukan pengganti universal untuk Elasticsearch/OpenSearch/Solr. Senior engineer harus tahu kapan PostgreSQL cukup dan kapan search workload sebaiknya keluar ke dedicated search platform.

1. Core mental model

Full-text search mengubah text menjadi struktur yang bisa dicari.

Raw text:
  "The customer ordered enterprise mobile services"

Parsing:
  customer, ordered, enterprise, mobile, services

Normalization/stemming:
  customer, order, enterpris, mobil, servic

tsvector:
  'custom':2 'enterpris':4 'mobil':5 'order':3 'servic':6

User query:
  "enterprise mobile"

Tsquery:
  'enterpris' & 'mobil'

Match:
  tsvector @@ tsquery

Mental model penting:

• tsvector adalah representasi dokumen yang sudah diproses;
• tsquery adalah representasi query user yang sudah diproses;
• operator @@ mengecek apakah vector cocok dengan query;
• ranking menghitung seberapa relevan dokumen;
• GIN/GiST index mempercepat matching;
• language configuration memengaruhi tokenization, stop words, stemming.

2. Kenapa bukan LIKE/ILIKE saja?

LIKE dan ILIKE berguna untuk pattern matching sederhana.

Contoh:

SELECT id, product_name
FROM product_offering
WHERE product_name ILIKE '%mobile%';

Masalah saat menjadi search feature:

• %keyword% sulit memakai B-tree index biasa;
• tidak memahami kata dasar/stemming;
• tidak memahami stop words;
• tidak punya ranking natural;
• tidak cocok untuk multi-field relevance;
• sulit membedakan exact phrase vs semua kata;
• performance memburuk saat data membesar.

ILIKE masih valid untuk:

• admin tool kecil;
• exact-ish contains search pada dataset kecil;
• prefix search terbatas;
• fallback sederhana;
• debugging query.

Untuk search produk/catalog/quote yang serius, FTS biasanya lebih tepat.

Catatan: extension pg_trgm bisa membantu similarity/substring search dan sering melengkapi FTS, tetapi itu topik extension/ecosystem lebih lanjut.

3. Basic FTS query

Contoh table:

CREATE TABLE product_offering (
    id uuid PRIMARY KEY,
    tenant_id uuid NOT NULL,
    product_code text NOT NULL,
    product_name text NOT NULL,
    description text,
    status text NOT NULL,
    effective_from timestamptz NOT NULL,
    effective_to timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

Search sederhana:

SELECT id, product_code, product_name
FROM product_offering
WHERE to_tsvector('english', product_name || ' ' || coalesce(description, ''))
      @@ plainto_tsquery('english', 'enterprise mobile');

Ini bekerja, tetapi tidak optimal untuk production jika dihitung on the fly untuk setiap row.

Better: simpan tsvector sebagai generated column atau materialized/search column.

4. tsvector

tsvector adalah dokumen yang sudah diproses untuk search.

Contoh:

SELECT to_tsvector('english', 'Enterprise mobile services for business customers');

Hasil konseptual:

'busi':5 'custom':6 'enterpris':1 'mobil':2 'servic':3

Detail output bisa berbeda tergantung dictionary/configuration.

tsvector menyimpan lexeme dan posisi. Posisi membantu ranking dan phrase/proximity behaviour tertentu.

Dalam production schema, ada tiga pattern umum:

1. Hitung to_tsvector(...) saat query.
2. Simpan tsvector dalam generated column.
3. Simpan tsvector column dan update via trigger/application.

Pattern 1 — on-the-fly

WHERE to_tsvector('english', product_name || ' ' || coalesce(description, '')) @@ plainto_tsquery('english', $1)

Cocok untuk:

• prototype;
• dataset kecil;
• admin/internal tool;
• query jarang.

Tidak cocok untuk hot path besar karena PostgreSQL harus memproses text banyak row.

Pattern 2 — generated column

ALTER TABLE product_offering
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(product_code, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(product_name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'B')
) STORED;

CREATE INDEX idx_product_offering_search_vector
ON product_offering
USING gin (search_vector);

Cocok untuk:

• search column diturunkan dari columns dalam row yang sama;
• ingin menghindari trigger;
• search expression cukup deterministik;
• migration dan query ingin sederhana.

Pattern 3 — explicit column + trigger/application update

ALTER TABLE product_offering
ADD COLUMN search_vector tsvector;

CREATE INDEX idx_product_offering_search_vector
ON product_offering
USING gin (search_vector);

Update via app/trigger:

UPDATE product_offering
SET search_vector =
    setweight(to_tsvector('english', coalesce(product_code, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(product_name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'B')
WHERE id = $1;

Cocok jika:

• search document berasal dari banyak table;
• perlu denormalized search document;
• perlu custom refresh lifecycle;
• search model mirip read model.

Trade-off:

• harus menjaga consistency;
• trigger bisa hidden side effect;
• application update bisa lupa;
• backfill dan rebuild perlu runbook.

5. tsquery

tsquery adalah query search yang sudah diproses.

PostgreSQL menyediakan beberapa function penting.

to_tsquery

SELECT to_tsquery('english', 'enterprise & mobile');

Butuh syntax tsquery eksplisit. Cocok untuk advanced/internal query builder, bukan raw user input biasa.

plainto_tsquery

SELECT plainto_tsquery('english', 'enterprise mobile service');

Mengubah plain text menjadi query dengan operator AND antar term. Lebih aman untuk input sederhana.

phraseto_tsquery

SELECT phraseto_tsquery('english', 'enterprise mobile service');

Mencari urutan phrase secara lebih relevan.

websearch_to_tsquery

SELECT websearch_to_tsquery('english', '"enterprise mobile" OR broadband -legacy');

Mendukung syntax yang lebih familiar untuk user, seperti quote, OR, minus. Biasanya lebih friendly untuk search box.

Rule praktis:

6. Basic indexed search query

SELECT
    id,
    product_code,
    product_name,
    ts_rank(search_vector, websearch_to_tsquery('english', $1)) AS rank
FROM product_offering
WHERE tenant_id = $2
  AND status = 'ACTIVE'
  AND search_vector @@ websearch_to_tsquery('english', $1)
ORDER BY rank DESC, product_code ASC
LIMIT $3;

Important details:

• tenant/status tetap relational filter;
• FTS hanya untuk text matching;
• ranking dihitung setelah match;
• tie-breaker deterministic (product_code ASC) penting untuk pagination stability;
• limit wajib untuk search API;
• jangan return result tak terbatas.

7. Weighting

Tidak semua field punya bobot sama.

Dalam product search:

• product code match sangat penting;
• product name match penting;
• description match sedang;
• metadata/search keywords mungkin lebih rendah.

Gunakan setweight:

setweight(to_tsvector('english', coalesce(product_code, '')), 'A') ||
setweight(to_tsvector('english', coalesce(product_name, '')), 'A') ||
setweight(to_tsvector('english', coalesce(description, '')), 'B') ||
setweight(to_tsvector('english', coalesce(search_keywords, '')), 'C')

Bobot digunakan oleh ranking function seperti ts_rank atau ts_rank_cd.

Tanpa weighting, match di deskripsi panjang bisa mengalahkan match pada product code/name yang lebih penting secara bisnis.

8. Ranking

Ranking menjawab:

Dari dokumen yang match, mana yang paling relevan?

Contoh:

SELECT
    id,
    product_name,
    ts_rank(search_vector, websearch_to_tsquery('english', $1)) AS rank
FROM product_offering
WHERE search_vector @@ websearch_to_tsquery('english', $1)
ORDER BY rank DESC
LIMIT 20;

Ranking pitfalls:

• ranking computation bisa mahal;
• ranking semua match sebelum filter bisa lambat;
• dokumen panjang bisa bias;
• ranking tidak tahu business priority kecuali dimodelkan;
• ranking butuh tie-breaker.

Tambahkan business score jika perlu:

ORDER BY
    ts_rank(search_vector, websearch_to_tsquery('english', $1)) DESC,
    popularity_score DESC,
    product_code ASC

Tetapi hati-hati: business score bisa memaksa sort besar. Uji dengan EXPLAIN (ANALYZE, BUFFERS).

9. Highlighting

PostgreSQL menyediakan ts_headline untuk highlight snippet.

SELECT
    id,
    product_name,
    ts_headline(
        'english',
        coalesce(description, ''),
        websearch_to_tsquery('english', $1),
        'StartSel=<mark>, StopSel=</mark>'
    ) AS snippet
FROM product_offering
WHERE search_vector @@ websearch_to_tsquery('english', $1)
LIMIT 20;

Production concerns:

• escape output sesuai HTML context;
• jangan inject raw highlight ke frontend tanpa sanitization;
• highlighting bisa mahal untuk banyak rows;
• lakukan highlight hanya untuk top N result;
• snippet generation harus dipisah dari search matching jika performance perlu.

10. Language configuration

Language configuration memengaruhi:

• parser;
• dictionary;
• stop words;
• stemming;
• normalization.

Contoh:

to_tsvector('english', text_column)

Untuk data multilingual, problem menjadi lebih sulit:

• satu row bisa punya bahasa berbeda;
• user query bisa berbeda bahasa;
• stemming English tidak cocok untuk bahasa lain;
• stop words berbeda;
• ranking bisa bias.

Pattern:

Single language system

Gunakan config tetap:

search_vector generated always as (
  to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))
) stored

Per-row language

Simpan language code:

CREATE TABLE knowledge_article (
    id uuid PRIMARY KEY,
    tenant_id uuid NOT NULL,
    language_code text NOT NULL,
    title text NOT NULL,
    body text NOT NULL,
    search_vector tsvector
);

Update search_vector dengan config berdasarkan language. Ini lebih kompleks dan perlu whitelist config.

Multi-language simplification

Gunakan simple configuration jika stemming language-specific tidak diinginkan.

to_tsvector('simple', text_column)

Trade-off:

• lebih predictable untuk multi-language;
• kurang pintar untuk stemming;
• bisa menghasilkan result lebih noisy.

11. Stop words and stemming

Stop words adalah kata umum yang diabaikan, seperti “the”, “and”, “or” dalam English.

Stemming mengubah variasi kata menjadi root lexeme.

Contoh konseptual:

services, service, servicing -> servic
ordered, ordering -> order

Benefit:

• result lebih natural;
• user tidak harus mengetik bentuk kata yang persis.

Risk:

• domain term bisa distem secara tidak diinginkan;
• kode produk bisa rusak jika diproses sebagai bahasa natural;
• acronym bisa tidak cocok;
• part number/SKU lebih baik ditangani sebagai exact/prefix/trigram.

Dalam CPQ/catalog, text search sering harus menggabungkan:

• natural language search untuk name/description;
• exact search untuk product code/SKU;
• alias/synonym untuk business terms;
• filter relational untuk product family/status/tenant/effective date.

12. Searching product code vs natural text

Product code tidak selalu cocok untuk stemming.

Misalnya:

ENT-MOBILE-5G-PLAN
BIZ-FIBER-1000

Untuk product code, pertimbangkan:

• exact equality;
• prefix search;
• normalized code column;
• trigram search via pg_trgm jika fuzzy code search dibutuhkan;
• memasukkan code ke tsvector dengan weight A, tetapi tetap uji hasil.

Contoh hybrid:

SELECT id, product_code, product_name
FROM product_offering
WHERE tenant_id = $1
  AND status = 'ACTIVE'
  AND (
      product_code = $2
      OR product_code ILIKE $3
      OR search_vector @@ websearch_to_tsquery('english', $4)
  )
ORDER BY
  CASE WHEN product_code = $2 THEN 0 ELSE 1 END,
  ts_rank(search_vector, websearch_to_tsquery('english', $4)) DESC,
  product_code ASC
LIMIT 20;

Caution:

• OR query bisa membuat plan buruk;
• gunakan EXPLAIN ANALYZE;
• kadang lebih baik pisahkan exact code lookup dan text search menjadi dua query atau UNION ALL terkontrol.

13. FTS with filters

Search hampir selalu digabung dengan filter:

• tenant;
• status;
• product family;
• effective date;
• region;
• customer segment;
• permission/access control;
• catalog version.

Contoh:

SELECT
    id,
    product_code,
    product_name,
    ts_rank(search_vector, websearch_to_tsquery('english', $1)) AS rank
FROM product_offering
WHERE tenant_id = $2
  AND status = 'ACTIVE'
  AND effective_from <= now()
  AND (effective_to IS NULL OR effective_to > now())
  AND product_family = ANY($3)
  AND search_vector @@ websearch_to_tsquery('english', $1)
ORDER BY rank DESC, product_code ASC
LIMIT 50;

Indexing strategy harus mempertimbangkan kombinasi filter dan FTS.

Kemungkinan index:

CREATE INDEX idx_product_offering_search_gin
ON product_offering
USING gin (search_vector);

CREATE INDEX idx_product_offering_active_filter
ON product_offering (tenant_id, status, product_family, effective_from, effective_to);

Planner bisa memilih bitmap combine atau strategi lain tergantung data. Jangan asumsikan cukup hanya GIN index.

14. GIN vs GiST for FTS

PostgreSQL mendukung GIN dan GiST untuk FTS.

Secara praktis:

• GIN sering menjadi default choice untuk full-text search lookup;
• GiST bisa berguna untuk kasus tertentu tetapi umumnya lebih lossy dan membutuhkan recheck;
• GIN write overhead perlu dipertimbangkan;
• ranking tetap membutuhkan akses data/positions sesuai query.

Contoh GIN:

CREATE INDEX idx_product_offering_search_vector
ON product_offering
USING gin (search_vector);

Review question:

Apakah workload search lebih read-heavy atau write-heavy?
Apakah update text sangat sering?
Apakah index size acceptable?
Apakah latency write meningkat setelah GIN index?
Apakah query benar-benar memakai index?

15. Search pagination

Search pagination sulit karena ranking bisa berubah.

Naive offset:

ORDER BY rank DESC
LIMIT 20 OFFSET 10000;

Masalah:

• offset besar mahal;
• ranking bisa berubah antar request;
• result bisa duplicate/missing jika data berubah;
• sort besar bisa spill.

Untuk search, offset pagination masih sering dipakai untuk halaman awal, tetapi batasi depth.

Pattern lebih aman:

- limit page depth untuk search result;
- gunakan deterministic tie-breaker;
- cache search result id untuk session jika perlu;
- gunakan cursor berbasis rank + id untuk advanced case;
- gunakan external search engine jika deep pagination penting.

Contoh deterministic order:

ORDER BY rank DESC, product_code ASC, id ASC

Keyset dengan rank sulit karena rank float dan bisa berubah, tetapi bisa dilakukan hati-hati:

WHERE (
    rank < $lastRank
    OR (rank = $lastRank AND id > $lastId)
)

Dalam praktik, lebih sering ranking query dibungkus subquery/CTE untuk cursor logic. Uji plan dan correctness sebelum production.

16. Search API design in JAX-RS

Contoh endpoint:

GET /catalog/products/search?q=enterprise+mobile&family=CONNECTIVITY&limit=20

API concerns:

• minimum query length;
• maximum query length;
• allowed characters;
• timeout;
• result limit maximum;
• filter validation;
• sort options whitelist;
• tenant scoping mandatory;
• access control mandatory;
• empty query behavior;
• search audit/logging with PII caution.

Example service flow:

JAX-RS resource
  -> validate q length and filters
  -> resolve tenant/access scope
  -> normalize search text
  -> call MyBatis mapper with bind parameters
  -> enforce query timeout
  -> map result to response DTO
  -> include rank? usually no, unless debugging/internal

Do not expose raw tsquery syntax to public API unless intentionally designing advanced search.

17. MyBatis mapper for FTS

Good mapper example:

<select id="searchActiveProductOfferings" resultMap="ProductOfferingSearchResultMap">
  SELECT
      id,
      product_code,
      product_name,
      ts_rank(search_vector, websearch_to_tsquery('english', #{query})) AS rank
  FROM product_offering
  WHERE tenant_id = #{tenantId}
    AND status = 'ACTIVE'
    AND search_vector @@ websearch_to_tsquery('english', #{query})
  ORDER BY rank DESC, product_code ASC, id ASC
  LIMIT #{limit}
</select>

Notes:

• search string is bound via #{};
• config 'english' is static;
• tenant/status filter is explicit;
• deterministic tie-breaker exists;
• limit exists.

Risky mapper:

WHERE search_vector @@ to_tsquery('${language}', '${query}')

Problems:

• raw substitution;
• user controls tsquery syntax;
• language config injection risk;
• parse error possible;
• query semantics unpredictable.

Better if language dynamic:

• whitelist language in Java enum;
• map enum to static SQL fragment;
• keep query value parameterized;
• handle parse errors.

18. Handling parse errors and empty queries

websearch_to_tsquery is generally user-friendly, but search input can still produce edge cases:

• empty string;
• only stop words;
• very long input;
• weird punctuation;
• unsupported syntax if using to_tsquery;
• language-specific parser behavior.

Application policy:

If q is blank:
  return validation error or default browse result, not unbounded search.

If q length < minimum:
  ask for more characters or use prefix/trigram strategy intentionally.

If q becomes empty tsquery after stop words:
  return empty result or fallback strategy.

If q too long:
  reject with 400 or truncate by policy.

Do not let empty search become:

SELECT * FROM product_offering WHERE tenant_id = $1 LIMIT 100000;

19. FTS and ranking performance

Matching via GIN can be fast, but ranking can still be expensive.

Why?

• index finds candidate rows;
• ranking needs evaluate matched document;
• sorting by rank can require sorting many candidates;
• filters may reduce candidates before/after search depending plan;
• highlighting adds extra cost.

Optimization options:

• filter tenant/status/date before ranking logically;
• limit result set;
• avoid highlighting for all candidates;
• add business filters;
• use precomputed search_vector;
• avoid search documents that are too large;
• use EXPLAIN (ANALYZE, BUFFERS);
• consider external search engine for heavy relevance workloads.

Common smell:

Search query matches 500,000 rows.
API needs top 20.
Database spends most time ranking/sorting huge candidate set.

Fix may require better filters, better query semantics, narrower search document, or dedicated search infrastructure.

20. FTS with JSONB

Search document can include JSONB fields, but do this deliberately.

Example:

ALTER TABLE product_offering
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(product_code, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(product_name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(attributes ->> 'marketingName', '')), 'B')
) STORED;

Risks:

• JSONB key missing;
• JSON structure changes break search quality;
• generated expression may need migration;
• nested values might include noisy text;
• PII accidentally enters search index;
• search index becomes stale if trigger/application update misses JSONB changes.

If using JSONB in search, include it in JSONB schema governance.

21. FTS for quote/order search

Quote/order search usually differs from product search.

Possible search dimensions:

• quote number;
• customer name;
• account name;
• external reference;
• order number;
• product names inside quote;
• status;
• approval note;
• sales rep;
• integration id.

Not all should be FTS.

Do not join 8 OLTP tables on every search request if search is hot path. Consider a read model:

CREATE TABLE quote_search_document (
    quote_id uuid PRIMARY KEY,
    tenant_id uuid NOT NULL,
    quote_number text NOT NULL,
    status text NOT NULL,
    customer_id uuid NOT NULL,
    created_at timestamptz NOT NULL,
    search_vector tsvector NOT NULL,
    updated_at timestamptz NOT NULL
);

This table can be updated transactionally, via outbox/CDC, or batch refresh depending consistency requirement.

22. Search consistency and read models

Search can be:

Strongly consistent

Search document updated in same transaction as source data.

Pros:

• read-after-write consistency;
• simpler user expectation.

Cons:

• transaction becomes heavier;
• GIN index update in write path;
• more lock/write overhead.

Eventually consistent

Search document updated asynchronously.

Pros:

• write path lighter;
• can rebuild/reprocess;
• better isolation.

Cons:

• search result can lag;
• requires reconciliation;
• user may not see newly updated item immediately.

In CPQ/order systems, choose based on business requirement:

Is it acceptable if quote search lags by seconds?
Is product catalog search rebuilt after catalog publish?
Do approvals require immediate search visibility?
Can support tools tolerate eventual consistency?

23. When PostgreSQL FTS is enough

PostgreSQL FTS is often enough when:

• dataset is moderate;
• search is scoped by tenant/status/date;
• relevance requirement is simple;
• exact filtering is as important as text matching;
• operational simplicity matters;
• team wants fewer moving parts;
• data must stay transactionally close to OLTP;
• search result depth is limited;
• no complex faceting/autocomplete/synonym requirements.

Examples:

• admin/support search;
• product offering search inside bounded catalog;
• quote/order search with strong filters;
• knowledge article search with moderate volume;
• internal configuration search.

24. When external search engine is better

Consider Elasticsearch/OpenSearch/Solr or another dedicated search platform when:

• data volume very large;
• relevance tuning is business-critical;
• autocomplete/typeahead required;
• typo tolerance/fuzzy matching is important;
• faceted search is complex;
• deep pagination needed;
• multi-language analysis sophisticated;
• search workload should be isolated from OLTP;
• query patterns are search-engine-heavy;
• indexing pipeline can tolerate eventual consistency;
• observability/search analytics needed.

But external search adds:

• data synchronization;
• eventual consistency;
• reindexing complexity;
• schema mapping lifecycle;
• operational cost;
• security duplication;
• incident surface area;
• reconciliation need.

Senior trade-off:

PostgreSQL FTS reduces architecture complexity but has search feature limits. External search increases capability but adds distributed data consistency and operational burden.

25. Failure modes

Failure mode 1 — Search query full scans table

Cause:

• no search_vector column;
• no GIN index;
• expression not matching index;
• query computes to_tsvector on every row.

Detection:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

Look for sequential scan on large table and high buffer reads.

Failure mode 2 — Search result irrelevant

Cause:

• wrong language config;
• no weighting;
• noisy field included;
• product code treated like natural language;
• missing synonym/alias model;
• rank tie not deterministic.

Detection:

• compare query terms with ts_debug;
• inspect tsvector output;
• sample top results;
• collect user feedback.

Failure mode 3 — Search slow only for common terms

Cause:

• many candidate rows;
• ranking/sorting huge match set;
• missing filters;
• stop words/config issue.

Detection:

• count matches;
• compare common vs rare term plan;
• check sort/temp file;
• check rank computation time.

Failure mode 4 — GIN index write overhead

Cause:

• frequently updated text/search_vector;
• large search documents;
• high write workload;
• many GIN indexes.

Detection:

• write latency increases;
• WAL generation increases;
• index size grows;
• autovacuum pressure;
• pg_stat_user_indexes and relation size.

Failure mode 5 — Search index stale

Cause:

• explicit search_vector not updated;
• trigger disabled/buggy;
• async read model lag;
• failed CDC consumer;
• backfill incomplete.

Detection:

• row source data differs from search_vector;
• reconciliation query;
• lag metric;
• CDC/outbox status.

Failure mode 6 — Security/privacy leak

Cause:

• PII included in search_vector;
• search result exposes unauthorized rows;
• tenant filter missing;
• highlight/snippet exposes hidden field;
• logs capture raw query with sensitive terms.

Detection:

• security review;
• access-control tests;
• tenant isolation tests;
• log audit;
• support tool review.

26. Debugging workflow

Step 1 — Capture real SQL and parameters

From MyBatis/app logs, capture:

• final SQL shape;
• query string;
• tenant/status/date filters;
• limit/offset;
• sort;
• endpoint;
• timing.

Step 2 — Inspect query semantics

Check:

SELECT websearch_to_tsquery('english', $1);
SELECT to_tsvector('english', $sample_text);

Use ts_debug for deeper analysis:

SELECT *
FROM ts_debug('english', 'enterprise mobile services');

Step 3 — Explain plan

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

Look for:

• GIN index usage;
• bitmap index scan;
• row estimates;
• actual rows;
• sort cost;
• temp file usage;
• filter order;
• buffers read.

Step 4 — Check index and size

SELECT
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'product_offering';

Step 5 — Check result quality

Sample top N results and compare:

• exact code match;
• name match;
• description-only match;
• rank order;
• filtered rows;
• missing expected results.

Step 6 — Decide fix category

Possible fix:

• change query function;
• add/generated search_vector;
• add GIN index;
• add relational filters;
• adjust weighting;
• split exact code lookup from FTS;
• create read model;
• move to external search.

27. Testing strategy

Unit tests alone are not enough for FTS.

Test layers:

Query semantics test

• input query becomes expected tsquery;
• stop words behavior understood;
• phrase query works;
• weird punctuation handled;
• empty query handled.

Integration test

• fixture rows;
• expected result order;
• tenant isolation;
• status/date filters;
• highlighting safe;
• permission filter applied.

Performance test

• realistic row count;
• common and rare terms;
• high-cardinality and low-cardinality filters;
• ranking cost;
• pagination limit;
• concurrent search load.

Rebuild/reconciliation test

• search_vector generation correct;
• trigger/application update works;
• backfill can rebuild;
• async lag detected;
• stale index/read model repair path.

28. Migration strategy for FTS

Adding FTS to an existing large table needs care.

Unsafe:

ALTER TABLE product_offering
ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (...) STORED;

CREATE INDEX idx_product_offering_search_vector
ON product_offering USING gin (search_vector);

On large table, this can be expensive and lock-sensitive depending operation.

Safer approach may involve:

1. Add nullable search_vector column.
2. Deploy app that can tolerate null search_vector or still uses fallback.
3. Backfill in chunks.
4. Create index concurrently where supported and appropriate.
5. Validate coverage.
6. Switch query to indexed search.
7. Enforce NOT NULL/generation/update mechanism later.

Example concurrent index:

CREATE INDEX CONCURRENTLY idx_product_offering_search_vector
ON product_offering
USING gin (search_vector);

Remember:

• CREATE INDEX CONCURRENTLY has transaction restrictions;
• migration tool behavior must be verified;
• failure leaves invalid index that needs cleanup;
• runbook required for large production table.

29. Observability for search

Metrics/logs to track:

• search endpoint latency p50/p95/p99;
• query timeout rate;
• search result count distribution;
• empty result rate;
• top search terms, with privacy caution;
• slow FTS queries;
• GIN index size;
• index scan count;
• temp file usage;
• search_vector stale/rebuild lag;
• async read model lag;
• error count from tsquery parsing if using advanced syntax;
• tenant-level traffic skew.

Dashboards should answer:

Is search slow because of database execution, app mapping, network, or result rendering?
Is search slow for all queries or only common terms?
Did search latency change after catalog publish/backfill/index migration?
Is GIN index growing abnormally?
Is search load affecting OLTP latency?

30. Production design checklist

Before shipping FTS feature:

Query semantics

• What function is used: plainto_tsquery, websearch_to_tsquery, to_tsquery, or phraseto_tsquery?
• Is raw user input safely handled?
• Is minimum/maximum query length enforced?
• What happens for empty/stop-word-only query?
• Is language config correct?

Data model

• Is tsvector generated, trigger-managed, or application-managed?
• What fields are included?
• Are weights defined?
• Are product codes handled differently from natural text?
• Is PII excluded?

Indexing

• Is there a GIN/GiST index?
• Does EXPLAIN ANALYZE show index usage?
• Are relational filters indexed?
• Is index size acceptable?
• Is write overhead acceptable?

API

• Is tenant filter mandatory?
• Is authorization applied before result exposure?
• Is limit capped?
• Is ordering deterministic?
• Is deep pagination controlled?
• Are snippets/highlights escaped?

Operations

• Is there slow query visibility?
• Is there search endpoint dashboard?
• Is there rebuild/backfill runbook?
• Is search consistency expectation documented?
• Is fallback behavior defined?

31. Internal verification checklist

Cek hal-hal berikut di internal CSG/team, tanpa mengasumsikan detail internal yang belum tersedia:

Search usage

• Apakah PostgreSQL FTS sudah dipakai?
• Endpoint apa yang memakai search?
• Search dipakai untuk catalog, quote, order, support tool, atau admin screen?
• Apakah ada external search engine juga?
• Apakah search strong consistent atau eventually consistent?

Schema and index

• Apakah ada column tsvector?
• Apakah tsvector generated, trigger-managed, atau app-managed?
• Apakah ada GIN/GiST index?
• Apakah ada pg_trgm untuk complementary search?
• Apakah search document memasukkan JSONB fields?

Query and MyBatis

• Mapper mana yang membangun search query?
• Apakah memakai plainto_tsquery, websearch_to_tsquery, atau to_tsquery?
• Apakah query string dibind aman dengan #{}?
• Apakah ada ${} untuk language/query/order?
• Apakah tenant/status/date filters selalu ada?

Performance

• Apakah ada slow query untuk search?
• Apakah search query melakukan ranking/sort besar?
• Apakah GIN index digunakan?
• Apakah search workload memengaruhi OLTP?
• Apakah ada timeout policy?

Relevance

• Apakah business pernah komplain result tidak relevan?
• Apakah product code exact match diprioritaskan?
• Apakah weighting field jelas?
• Apakah multilingual search diperlukan?
• Apakah synonym/alias dibutuhkan?

Operations and safety

• Apakah search_vector bisa direbuild?
• Apakah ada reconciliation untuk stale search document?
• Apakah PII masuk search index?
• Apakah highlight/snippet aman?
• Apakah search logs mengandung sensitive query terms?

32. Summary mental model

PostgreSQL Full-Text Search adalah fitur native yang kuat untuk search berbasis teks dalam database relational.

Gunakan FTS ketika:

• search bounded;
• filter relational penting;
• operational simplicity penting;
• relevance requirement moderate;
• result depth terbatas;
• consistency dengan PostgreSQL dibutuhkan.

Jangan gunakan FTS secara naif ketika:

• query hanya to_tsvector(...) on the fly di tabel besar;
• tidak ada GIN index;
• search_vector stale;
• ranking/sorting jutaan candidate;
• tenant/security filter tidak wajib;
• PII masuk index tanpa governance;
• product code diproses seperti natural language tanpa testing.

Rule senior engineer:

Search adalah product behaviour, database workload, security boundary, dan operational concern sekaligus. Desain FTS yang baik harus menjelaskan semantics, relevance, index strategy, consistency, API limits, observability, dan fallback plan.

References

• PostgreSQL Documentation — Full Text Search: https://www.postgresql.org/docs/current/textsearch.html
• PostgreSQL Documentation — Controlling Text Search: https://www.postgresql.org/docs/current/textsearch-controls.html
• PostgreSQL Documentation — Text Search Functions and Operators: https://www.postgresql.org/docs/current/functions-textsearch.html
• PostgreSQL Documentation — Preferred Index Types for Text Search: https://www.postgresql.org/docs/current/textsearch-indexes.html
• PostgreSQL Documentation — pg_trgm: https://www.postgresql.org/docs/current/pgtrgm.html