PostgreSQL 远不止是一个关系型数据库。它的高级特性让它在很多场景下可以替代专门的 MongoDB（JSON）、Elasticsearch（全文搜索）、甚至 Redis（键值存储）。

JSON 与 JSONB

PostgreSQL 从 9.2 开始支持 JSON，9.4 引入了性能优异的 JSONB。

JSONB 的核心优势

  • JSONB 是二进制格式，去掉了空格和键的顺序
  • 支持索引（GIN 索引），查询速度显著快于 JSON
  • 支持高效的操作符（->、 ->>、 @>、 ?、 ?|、 ?&）
-- 创建 JSONB 列
CREATE TABLE products_json (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB  -- 可变属性的最佳容器
);

INSERT INTO products_json (name, attributes) VALUES
    ('笔记本电脑', '{"brand": "联想", "ram": "16GB", "storage": "512GB SSD", "color": "银灰", "weight": 1.4}'),
    ('手机', '{"brand": "华为", "ram": "8GB", "storage": "256GB", "color": "黑色", "screen": "6.1英寸", "battery": "4500mAh"}'),
    ('耳机', '{"brand": "索尼", "type": "降噪", "wireless": true, "battery": "30h"}'),
    ('运动鞋', '{"brand": "耐克", "size": 42, "color": "白色", "material": "网面"}'),
    ('智能手表', '{"brand": "苹果", "series": 9, "waterproof": true, "gps": true}'),
    ('平板电脑', '{"brand": "苹果", "model": "iPad Air", "storage": "256GB", "color": "深空灰"}'),
    ('咖啡机', '{"brand": "德龙", "type": "全自动", "capacity": "1.8L", "pressure": "15bar"}');

JSONB 查询操作符

-- ->  返回 JSON（保留键）
SELECT name, attributes -> 'brand' AS brand FROM products_json;

-- ->> 返回文本
SELECT name, attributes ->> 'brand' AS brand_name FROM products_json;

-- 多级嵌套访问
SELECT name, attributes #>> '{specs, weight}' AS weight FROM products_json;

-- 包含检查（@>）
SELECT * FROM products_json 
WHERE attributes @> '{"brand": "苹果"}';

-- 存在检查（?）
SELECT * FROM products_json 
WHERE attributes ? 'wireless';  -- 包含 wireless 键

-- 键的匹配（?| 任一存在, ?& 全部存在）
SELECT * FROM products_json 
WHERE attributes ?| ARRAY['wireless', 'gps'];  -- 有 wireless 或 gps

JSONB GIN 索 引

-- 创建 GIN 索引加速 JSONB 查询
CREATE INDEX idx_products_json_attrs ON products_json USING GIN (attributes);

-- 索引加速的查询模式
EXPLAIN ANALYZE SELECT * FROM products_json 
WHERE attributes @> '{"brand": "联想"}';

JSONB 修改

-- jsonb_set：修改键值
UPDATE products_json
SET attributes = jsonb_set(attributes, '{price}', '5999', true)
WHERE name = '笔记本电脑';

-- jsonb_insert：插入键
UPDATE products_json
SET attributes = jsonb_insert(attributes, '{release_year}', '2025', true)
WHERE name = '笔记本电脑';

-- 删除键（-）
UPDATE products_json
SET attributes = attributes - 'weight'
WHERE name = '笔记本电脑';

全文搜索（Full Text Search）

PostgreSQL 的全文搜索能力接近 Elasticsearch，对中小规模应用完全够用。

-- 创建搜索数据
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    tags TEXT[],
    tsvector_content TSVECTOR  -- 预计算搜索向量
);

INSERT INTO articles (title, content, tags) VALUES
    ('PostgreSQL 基础入门', 'PostgreSQL 是一个强大的开源关系型数据库管理系统，具有完善的 ACID 支持。', ARRAY['数据库', '入门']),
    ('SQL 优化指南', '合理的索引和查询优化能显著提升数据库性能。百万级数据的查询优化需要理解执行计划。', ARRAY['SQL', '性能']),
    ('数据备份策略', '定期备份是数据安全的重要保障。推荐使用 pg_dump 进行逻辑备份。', ARRAY['运维', '安全']),
    ('窗口函数详解', '窗口函数是 PostgreSQL 中最强大的分析功能之一，可以在不改变行数的情况下进行复杂计算。', ARRAY['进阶', '分析']),
    ('JSONB 的妙用', 'PostgreSQL 的 JSONB 支持高效的 JSON 数据存储和查询，在需要灵活 Schema的场景中非常有用。', ARRAY['高级', 'JSON']);

创建 tsvector

-- 手动更新 tsvector 列
UPDATE articles SET tsvector_content = 
    to_tsvector('simple', content);

-- 查看分词结果
SELECT id, title, tsvector_content FROM articles;

搜索语法

-- 基本全文搜索（使用 to_tsquery）
SELECT id, title, 
       ts_rank(tsvector_content, query) AS relevance
FROM articles, 
     to_tsquery('simple', '备份 & 安全') AS query
WHERE tsvector_content @@ query
ORDER BY relevance DESC;

-- 带前缀搜索
SELECT * FROM articles
WHERE tsvector_content @@ to_tsquery('索引:*');

-- 短语搜索（精确匹配）
SELECT *
FROM articles
WHERE content @@ phraseto_tsquery('simple', '数据库 性能');

自动更新 tsvector

-- 用触发器自动维护 tsvector
CREATE OR REPLACE FUNCTION update_articles_tsvector()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.tsvector_content := to_tsvector('simple', 
        COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.content, ''));
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_articles_tsvector
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW
    EXECUTE FUNCTION update_articles_tsvector();

GIN 索引加速全文搜索

CREATE INDEX idx_articles_fts ON articles USING GIN (tsvector_content);

-- 测试搜索性能
EXPLAIN ANALYZE
SELECT title, ts_rank(tsvector_content, query) AS score
FROM articles, to_tsquery('simple', '数据库 & 性能') query
WHERE tsvector_content @@ query
ORDER BY score DESC;

扩展（Extensions）

PostgreSQL 的扩展机制是它强大的灵魂。以下是最常用的扩展：

启用扩展

-- 查看已安装的扩展
SELECT * FROM pg_available_extensions ORDER BY name;

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS extension_name;

-- 查看已启用的扩展
SELECT * FROM pg_extension;

常用扩展

-- pg_stat_statements：查询性能监控
CREATE EXTENSION pg_stat_statements;
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

-- uuid-ossp：生成 UUID
CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();

-- pgcrypto：加密函数
CREATE EXTENSION pgcrypto;
SELECT crypt('mypassword', gen_salt('bf'));  -- bcrypt 哈希
SELECT digest('mydata', 'sha256');  -- SHA-256

-- hstore：键值存储
CREATE EXTENSION hstore;
CREATE TABLE kv_store (data hstore);
INSERT INTO kv_store VALUES ('key1=>value1, key2=>value2, key3=>value3');
SELECT * FROM kv_store WHERE data ? 'key2';

-- unaccent：去掉重音符号（配合全文搜索）
CREATE EXTENSION unaccent;
SELECT unaccent('Café');  -- 返回 'Cafe'

-- PostGIS：地理空间扩展
-- CREATE EXTENSION postgis;  -- 需要单独安装

扩展管理

-- 查看扩展版本
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

-- 更新扩展
ALTER EXTENSION pg_stat_statements UPDATE TO '1.10';

-- 查看扩展的依赖对象
SELECT * FROM pg_depend 
WHERE refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_extension')
  AND refobjid = (SELECT oid FROM pg_extension WHERE extname = 'hstore');

分区表

当单表数据量达到亿级时，分区表是必备工具。

-- 创建范围分区表
CREATE TABLE orders_partitioned (
    order_id SERIAL,
    customer_id INT,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10,2),
    status VARCHAR(10)
) PARTITION BY RANGE (order_date);

-- 创建分区
CREATE TABLE orders_2025_q1 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE orders_2025_q2 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

-- 插入数据自动路由到对应分区
INSERT INTO orders_partitioned (customer_id, order_date, total_amount, status)
VALUES (1, '2025-03-15', 1000, '已完成');  -- 自动进入 q1 分区

-- 查询优化器自动剪枝
EXPLAIN ANALYZE SELECT * FROM orders_partitioned
WHERE order_date >= '2025-05-01';  -- 只扫描 q2 分区

外部数据包装（FDW）

PostgreSQL 可以查询其他数据库（PostgreSQL/MySQL/SQLite 等）的数据，像查本地表一样：

-- 创建 postgres_fdw 扩展
CREATE EXTENSION postgres_fdw;

-- 创建远程服务器连接定义
CREATE SERVER remote_pg 
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'remote_host', port '5432', dbname 'remote_db');

-- 创建用户映射
CREATE USER MAPPING FOR current_user  
    SERVER remote_pg
    OPTIONS (user 'remote_user', password 'remote_password');

-- 创建外部表
CREATE FOREIGN TABLE ft_remote_orders (
    order_id INT,
    customer_name TEXT,
    total_amount NUMERIC
) SERVER remote_pg
OPTIONS (schema_name 'public', table_name 'orders');

-- 查询外部数据
SELECT * FROM ft_remote_orders LIMIT 100;

小结

PostgreSQL 的高级特性让它成为真正的全能型数据库。JSONB 让你在需要灵活 Schema时不必切换到 NoSQL，全文搜索覆盖了大部分文本搜索需求，丰富的扩展生态提供了各种专业功能。结合分区表、FDW 等企业级特性，PostgreSQL 可以胜任从几百 MB 的嵌入式应用到数百 TB 的数据仓库场景。

下一篇文章（最后一篇）将结合前面所学，完成一个完整的电商数据库设计实战项目。

Summary: JSONB 查询与索引、全文搜索、分区表、FDW 与扩展生态。