PostgreSQLNotes

第 25 章：JSONB 与 GIS 实战

zjc 于 2026-01-25 发布

这是《PostgreSQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 本章用两个业务场景把类型、索引和扩展组合起来：事件 JSONB 分析和门店地理位置服务。

25.1 JSONB 事件表

CREATE TABLE app_events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_time timestamptz NOT NULL DEFAULT now(),
    event_name text NOT NULL,
    user_id bigint NOT NULL,
    payload jsonb NOT NULL
);

写入：

INSERT INTO app_events(event_time, event_name, user_id, payload)
VALUES (
    now(),
    'pay',
    1001,
    '{"city":"Shanghai","platform":"App","amount":99.50}'::jsonb
);

25.2 GIN 索引

CREATE INDEX idx_events_payload
ON app_events USING GIN (payload);

高效查询：

SELECT *
FROM app_events
WHERE payload @> '{"event_source":"app"}';

文本取值后比较会弱化索引：

SELECT *
FROM app_events
WHERE payload ->> 'city' = 'Shanghai';

可做表达式索引：

CREATE INDEX idx_events_city
ON app_events((payload ->> 'city'));

25.3 提升高频字段

ALTER TABLE app_events
ADD COLUMN city text
GENERATED ALWAYS AS (payload ->> 'city') STORED;

CREATE INDEX idx_events_city_time
ON app_events(city, event_time);

查询：

SELECT city, count(*)
FROM app_events
WHERE event_time >= now() - interval '1 day'
GROUP BY city;

25.4 JSONB 更新

UPDATE app_events
SET payload = payload || '{"status":"done"}'::jsonb
WHERE id = 1;

删除键：

UPDATE app_events
SET payload = payload - 'trace_id'
WHERE id = 1;

高频更新 JSONB 会产生死元组，应关注 VACUUM。

25.5 GIS 数据

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE shops (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    city text NOT NULL,
    location geography(Point, 4326) NOT NULL,
    service_range int NOT NULL DEFAULT 3000
);

插入：

INSERT INTO shops(name, city, location)
VALUES (
    'Cloud Coffee',
    'Shanghai',
    ST_SetSRID(ST_MakePoint(121.47, 31.23), 4326)::geography
);

25.6 空间索引

CREATE INDEX idx_shops_location
ON shops USING GIST (location);

附近门店：

SELECT
    id,
    name,
    ST_Distance(location, ST_SetSRID(ST_MakePoint(121.47, 31.23), 4326)::geography) AS distance
FROM shops
WHERE ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(121.47, 31.23), 4326)::geography,
    1000
)
ORDER BY distance
LIMIT 20;

25.7 地理边界

SELECT s.name
FROM shops s
JOIN city_boundary b ON ST_Contains(b.geom, s.location::geometry);

边界数据要确认坐标系、精度和来源授权。

25.8 性能要点

场景 优化
JSON 包含查询 GIN
JSON 高频字段 生成列 + B-Tree
附近查询 GiST + 范围过滤
边界包含 边界简化、空间索引
大范围统计 预聚合或分区

本章小结

JSONB 适合灵活事件结构，高频字段应提升为普通列或生成列；GIS 应使用 geography 存 WGS84 点位，并用 GiST 支撑距离和包含查询。

思考题

  1. @> 和 ->> 查询的索引行为有什么不同？
  2. 生成列什么时候适合 JSONB？
  3. geography 和 geometry 有什么差异？
  4. ST_DWithin 为什么要配合索引？
  5. JSONB 高频更新会带来什么运维问题？