PostgreSQL JSONB 与全文搜索

JSONB 列

-- 创建包含 JSONB 的表
CREATE TABLE products (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  metadata    JSONB NOT NULL DEFAULT '{}',
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- 为快速 JSONB 查询创建 GIN 索引
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);

-- 插入 JSONB 数据
INSERT INTO products (name, metadata) VALUES
  ('Sony WH-1000XM5', '{"brand": "Sony", "category": "headphones", "color": "black", "features": ["ANC", "30h battery"], "price": 399}'),
  ('Apple AirPods Pro', '{"brand": "Apple", "category": "earbuds", "features": ["ANC", "adaptive transparency"], "price": 249}');

JSONB 查询

-- 包含操作符 @>
SELECT name, metadata
FROM products
WHERE metadata @> '{"brand": "Sony"}';

-- 字段访问 ->>（返回文本）
SELECT name, metadata->>'brand' AS brand, (metadata->>'price')::numeric AS price
FROM products
WHERE (metadata->>'price')::numeric < 300;

-- 数组包含 ?&（包含所有键）或 ?|（包含任一键）
SELECT name
FROM products
WHERE metadata->'features' ? 'ANC';  -- 具有 ANC 功能

-- 嵌套访问
SELECT metadata->'specs'->>'weight' FROM products;

-- 对 JSONB 进行聚合
SELECT
  metadata->>'brand' AS brand,
  COUNT(*) as product_count,
  AVG((metadata->>'price')::numeric) AS avg_price
FROM products
WHERE metadata ? 'price'
GROUP BY metadata->>'brand';

JSONB 更新

-- 添加/更新字段
UPDATE products
SET metadata = metadata || '{"in_stock": true}'
WHERE id = '...';

-- 删除字段
UPDATE products
SET metadata = metadata - 'discontinued_field'
WHERE id = '...';

-- 更新嵌套字段
UPDATE products
SET metadata = jsonb_set(metadata, '{specs, weight}', '"250g"')
WHERE id = '...';

-- 追加到数组
UPDATE products
SET metadata = jsonb_set(metadata, '{features}', (metadata->'features') || '["Multipoint"]')
WHERE id = '...';

全文搜索

-- 添加 tsvector 列用于搜索
ALTER TABLE products
ADD COLUMN search_vector TSVECTOR
GENERATED ALWAYS AS (
  to_tsvector('english',
    coalesce(name, '') || ' ' ||
    coalesce(metadata->>'description', '') || ' ' ||
    coalesce(metadata->>'brand', '') || ' ' ||
    coalesce(metadata->>'category', '')
  )
) STORED;

-- 为快速搜索创建 GIN 索引
CREATE INDEX idx_products_search ON products USING GIN(search_vector);

-- 全文搜索
SELECT name, metadata->>'brand' AS brand,
       ts_rank(search_vector, query) AS relevance
FROM products,
     to_tsquery('english', 'noise & cancelling & headphones') query
WHERE search_vector @@ query
ORDER BY relevance DESC;

-- 面向 Web 的搜索（处理部分单词）
SELECT name FROM products
WHERE search_vector @@ websearch_to_tsquery('english', 'noise cancelling headphones')
ORDER BY ts_rank(search_vector, websearch_to_tsquery('english', 'noise cancelling headphones')) DESC;

高亮搜索结果

SELECT
  name,
  ts_headline(
    'english',
    name || ' ' || coalesce(metadata->>'description', ''),
    query,
    'StartSel=<mark>, StopSel=</mark>, MaxWords=30'
  ) AS highlighted
FROM products,
     websearch_to_tsquery('english', 'wireless headphones') query
WHERE search_vector @@ query
LIMIT 10;

何时 PostgreSQL 搜索足够

使用 PostgreSQL FTS 当：
  - 文档数 < 1000 万
  - 搜索只是众多功能之一
  - 数据已在 PostgreSQL 中
  - 简单的关键词搜索即可满足
  - 团队熟悉 SQL

使用 Elasticsearch 当：
  - 文档数 > 1000 万或需要复杂分析
  - 高级功能：模糊搜索、同义词、大规模自动补全
  - 多个数据源一起索引
  - 实时流式分析
  - 搜索是主要产品功能

对于大多数应用，PostgreSQL 的内置全文搜索已经足够强大，并且消除了 Elasticsearch 的运维复杂性。