Search and Indexing

-- Every *_to_tsquery function is shorthand for a tsquery you could write by
-- hand. Printing the result is the fastest way to see what a user's text did.

SELECT websearch_to_tsquery('english', 'cheap "red shoes" -suede');
--  'cheap' & 'red' <-> 'shoe' & !'suede'

SELECT plainto_tsquery('english', 'red running shoes');
--  'red' & 'run' & 'shoe'          -- every word ANDed, punctuation dropped

SELECT phraseto_tsquery('english', 'red shoes');
--  'red' <-> 'shoe'                -- adjacency, not just co-occurrence

-- The longhand of that last one, written directly as a tsquery:
SELECT 'red' <-> 'shoe' :: tsquery;

-- And the operators the shorthands compile into: & and, | or, ! not,
-- <-> followed by, <2> two positions apart, :* prefix match. These live in
-- tsquery TEXT syntax, so the whole expression goes inside the literal; the
-- SQL-level tsquery operators are spelled &&, || and !! instead.
SELECT to_tsvector('english', 'Red running shoes, size 9')
       @@ 'red & run:* & !sandal' :: tsquery;   -- true

-- Not indexable: the leading wildcard rules out the B-tree.
EXPLAIN ANALYZE SELECT id FROM articles WHERE body ILIKE '%shoe%';
--  Seq Scan on articles  (rows=1200000) ... Execution Time: 4100 ms

-- A trigram index makes substring matching indexable:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX articles_body_trgm ON articles USING GIN (body gin_trgm_ops);

EXPLAIN ANALYZE SELECT id FROM articles WHERE body ILIKE '%shoe%';
--  Bitmap Index Scan on articles_body_trgm ... Execution Time: 38 ms

-- But it is still not search. No stemming: this finds "shoe" and "shoes",
-- never "footwear"; it matches "shoehorn" and "horseshoe" just as happily;
-- and it returns no relevance, so there is nothing to ORDER BY.
-- Trigrams are the right tool for fuzzy name matching and typo tolerance:
SELECT name, similarity(name, 'jonh smith') AS s
  FROM customers WHERE name % 'jonh smith' ORDER BY s DESC LIMIT 10;

import { Client } from "@elastic/elasticsearch"; // 9.x
const es = new Client({ node: process.env.ES_URL });

// Driven by CDC or an outbox topic, never by a dual write from the request
// handler: the point is that the index update cannot be lost if the second
// write fails, because it is replayable from the stream.
export async function indexBatch(events) {
  const operations = events.flatMap((e) => [
    {
      index: {
        _index: "articles",
        _id: String(e.id),
        // External versioning makes the write idempotent AND order-proof:
        // a replayed or late event with a lower version is rejected with 409
        // instead of overwriting newer data. Use the row's updated_at as
        // epoch millis, or a monotonic revision column.
        version: e.version,
        version_type: "external",
      },
    },
    { title: e.title, body: e.body, updated_at: e.updated_at },
  ]);

  // Client 8 and later name this parameter `operations`; version 7 called the
  // same array `body`. The newline-delimited payload it builds is identical.
  const res = await es.bulk({ operations, refresh: false });

  // bulk returns 200 even when individual items failed. Not checking this is
  // the most common cause of an index that is quietly missing documents.
  if (res.errors) {
    const failed = res.items.filter((i) => i.index?.error &&
                                           i.index.status !== 409);
    if (failed.length) throw new Error(JSON.stringify(failed.slice(0, 3)));
  }
}