15.7 实战：`pg36_shop` 商品混合检索 PoC

frozen inputs
  -> deterministic schema/index/ranking
  -> exact quality golden
  -> separate ANN probe
  -> application privilege failure
  -> reset guards
  -> exact reset
  -> rebuild and re-review

[pg36-admin]
host=/path/to/socket-or-host
port=5432
dbname=pg36_shop
user=postgres

chmod 600 /path/to/pg_service.conf
export PGSERVICEFILE=/path/to/pg_service.conf
export PGSERVICE=pg36-admin

database = pg36_shop
writable instance
PostgreSQL major = 14..18
session user = superuser
can SET ROLE pg36_owner
ch04-v1 physical model exists
pg36_app = constrained non-superuser LOGIN
shop_ch14 marker is exact
pg_trgm = 1.6 in shop_ch14 with exact marker
vector = 0.8.4 in shop_ch14 with exact marker

static/labs/ch15/
├── frozen-corpus.csv
├── frozen-queries.csv
├── frozen-judgments.csv
├── fixture.sql
├── fixture-manifest.json
├── setup.sql
├── ranking-views.sql
├── verify.sql
└── ...

corpus:
  17 rows
  sha256=7136d6f1705e560c5d564407926b44c455cd59b3883cd640f4f51599806a90c8

queries:
  8 rows
  sha256=4b54b0ee322bf52649b5c682d468ea024ae301d8cac40d63aaaed908c9d8a45d

judgments:
  24 rows
  sha256=657ddc4d82f9b42af589fd64a6326407d5cfb6536b2f2c3c28862279eca41438

loader:
  sha256=2548978f3652f816452f22aed0d560a0d0263234a2f62a8fb77d4009c5545787

./static/labs/ch15/task.sh setup

CREATE TABLE shop_ch15.product_search (
  product_id bigint PRIMARY KEY,
  sku text NOT NULL UNIQUE,
  category text NOT NULL,
  active boolean NOT NULL,
  title text NOT NULL,
  description text NOT NULL,
  embedding shop_ch14.vector(4) NOT NULL,
  embedding_model text NOT NULL,
  search_document tsvector
    GENERATED ALWAYS AS (...) STORED
);

CREATE TABLE shop_ch15.eval_query (...);
CREATE TABLE shop_ch15.relevance_judgment (...);
CREATE TABLE shop_ch15.fixture_meta (...);

product_search_fts_idx              GIN pg_catalog.tsvector_ops
product_search_title_trgm_idx       GIN shop_ch14.gin_trgm_ops
product_search_embedding_hnsw_idx   HNSW shop_ch14.vector_l2_ops
product_search_filter_idx           partial B-tree WHERE active

lexical_ranking
fuzzy_ranking
vector_exact_ranking
hybrid_rrf_ranking
all_ranking
quality_per_query
quality_summary

status=fixture-ready
products=17
queries=8
judgments=24

PG36_EVIDENCE_DIR="$PWD/evidence/ch15-cycle" \
  ./static/labs/ch15/task.sh evaluate

cmp frozen-corpus.csv evidence/corpus.csv
cmp frozen-queries.csv evidence/queries.csv
cmp frozen-judgments.csv evidence/judgments.csv

psql "service=pg36-admin" \
  -f static/labs/ch15/fts-analysis.sql

fuzzy third = product 14 / Digital Coffee Scale / unjudged
vector first = product 5 / Home Espresso Machine / grade 1
hybrid       = 4,6,5 / all judged relevant

fuzzy puts grade-3 product 1 first
hybrid puts grade-2 product 3 first

SELECT *
FROM shop_ch15.quality_summary
ORDER BY strategy;

psql "service=pg36-admin" \
  -f static/labs/ch15/fts-plan.sql

psql "service=pg36-admin" \
  -f static/labs/ch15/trigram-plan.sql

psql "service=pg36-admin" \
  -f static/labs/ch15/vector-exact-plan.sql

psql "service=pg36-admin" \
  -f static/labs/ch15/vector-hnsw-plan.sql

psql "service=pg36-admin" \
  -f static/labs/ch15/vector-filtered-plan.sql

FTS      Bitmap Index Scan product_search_fts_idx
trigram  Bitmap Index Scan product_search_title_trgm_idx
exact    Seq Scan + Sort
HNSW     Index Scan product_search_embedding_hnsw_idx
filtered HNSW Index Scan + Filter

SELECT
  index_name,
  access_method,
  operator_class,
  is_valid,
  is_ready,
  is_live
FROM (... index-catalog.sql ...);

psql "service=pg36-admin" \
  --csv \
  -f static/labs/ch15/ann-compare.sql

exact_ids,ann_ids,recall_at_3
"7,8,9","7,8,9",1.000000

psql "service=pg36-admin user=pg36_app" \
  -f static/labs/ch15/app-query.sql

UPDATE shop_ch15.product_search
SET title = 'unauthorized mutation'
WHERE product_id = 1;

psql exit=3
SQLSTATE=42501
permission denied for table product_search

FTS:
  pg_catalog.english
  stored weighted tsvector
  title A / description B
  GIN

fuzzy:
  lower(title)
  similarity + word_similarity
  GIN candidate predicate
  production must add a qualified threshold

vector:
  versioned model identity
  L2
  exact for quality golden
  HNSW only as measured serving candidate

fusion:
  equal-weight RRF
  k=60
  source depth=4
  final depth=3

bf92a6ad0f60dc3e125b39dbf67bf4d6c5e50275192bd01a7ca4c50d142f822e

release=1.3-proposal
fixture=ch15-search-v1
embedding_model=pg36-handcrafted-topic-4d-v1
products=17
active_products=16
queries=8
judgments=24
business_checksum=c637abf09edba88b7793f91201a57c34

manifest.txt
setup.txt
corpus.csv
queries.csv
judgments.csv
document-catalog.csv
fts-analysis.csv
index-catalog.csv
size-catalog.csv
security-catalog.csv
quality-summary.csv
quality-detail.csv
ranking-results.csv
ann-compare.csv
fts-plan.txt
trigram-plan.txt
vector-exact-plan.txt
vector-hnsw-plan.txt
vector-filtered-plan.txt
app-query.csv
app-write.{exit,stdout,stderr}
final-state.csv
verify.txt
review.txt

latency/throughput under representative concurrency
heap/GIN/HNSW size at target scale
bulk and concurrent build duration
steady write/WAL cost
autovacuum/reindex cost
replica replay/failover
clean backup restore
real model quality and generation cost

PG36_RESET_TOKEN=RESET_CH15_SEARCH_LAB \
PG36_RESET_TARGET=pg36_shop/shop_ch15 \
PG36_EVIDENCE_DIR="$PWD/evidence/ch15-reset" \
  ./static/labs/ch15/task.sh reset

P3660 invalid action token
P3661 invalid target
P3662 identity/inventory collision
P3663 active workers

quality views
-> ranking views
-> relevance/query/product/meta tables
-> shop_ch15 schema

remaining_schema=0
preserved_extensions=pg_trgm:1.6,vector:0.8.4

stop/read-switch vector path
export and verify vectors/model identity
remove async generation traffic
drop/rebuild indexes online
observe fallback quality/capacity
retain rollback window
then remove no-longer-needed objects/packages

rows and dimensions
document/vector width
cache state
hardware/storage
concurrency
filters and selectivity
candidate K
index params
quality/recall target
write workload
network/application path

checklist:evidence

dataset:
  products: ...
  active_ratio: ...
  dimensions: ...
  update_rate: ...
query:
  segments: ...
  filters/selectivity: ...
  top_k: ...
quality:
  min_recall_at_k: ...
  min_ndcg_at_k: ...
  max_segment_regression: ...
ann:
  min_exact_relative_recall: ...
latency:
  p50: ...
  p95: ...
  p99: ...
capacity:
  peak_qps: ...
  write_rate: ...
operations:
  max_build_window: ...
  max_replica_lag: ...
  restore_rto/rpo: ...
cost:
  monthly_model_budget: ...

inventory commit and rendered config
package resolution for target OS/PG major
control/library hashes on every node
pg_extension catalog in target database
primary and replica search query
monitoring dashboards and alerts
planned switchover and failback
backup and clean restore
extension/model/index upgrade rehearsal

evidence_dir="$(mktemp -d /tmp/pg36-ch15-evidence.XXXXXX)"

PG36_EVIDENCE_DIR="$evidence_dir" \
  ./static/labs/ch15/task.sh all

cycle-1 collect + review
-> wrong token reset must fail P3660
-> wrong target reset must fail P3661
-> active worker reset must fail P3663
-> exact reset
-> verify extensions preserved
-> cycle-2 collect + review

status=ok
fixture=frozen-byte-identical
quality=precision+recall+mrr+ndcg
ranking=fts+trigram+exact-vector+rrf
ann=q06-exact-vs-hnsw-recall-1.000000
guards=P3660+P3661+P3663
extensions=ch14-preserved
pigsty_l1=not-run
release_candidate_checksum=bf92a6ad0f60dc3e125b39dbf67bf4d6c5e50275192bd01a7ca4c50d142f822e