PostgreSQL Example

示例 01

表名 article

id 类型为整数, 不能为空, 主键, 唯一, 自增
datetime 类型为 TIMESTAMP, 不能为空
code 类型为 CHAR(6), 不能为空
source 类型为 VARCHAR(20), 不能为空
title 类型为 VARCHAR(100), 不能为空
url 类型为 TEXT, 不能为空

需求：

datetime, code, source, title, url 都需查询
code 和 url 组成唯一约束
source、title 和 url 经常需要使用 WHERE Like ‘%xxx%’ 搜索

根据 PostgreSQL 最佳实践，以及索引优化等，生成相关的建表语句以及建索引语句。

下面按 PostgreSQL 最佳实践 + 查询场景 + 索引可落地的思路，给你一套可直接用于生产的设计，并解释每一个关键决策。

一、字段类型与设计决策（先给结论）

字段	类型	原因
id	BIGINT GENERATED	自增主键，避免业务耦合
datetime	TIMESTAMP WITHOUT TIME ZONE	明确、可索引、通用
code	CHAR(6)	固定长度证券代码（合理）
source	VARCHAR(20)	来源长度有上限，LIKE 查询
title	VARCHAR(100)	标题长度有上限，LIKE 查询
url	TEXT	URL 长度不确定，LIKE 查询
唯一约束	(code, url)	防止重复文章

⚠️ LIKE ‘%xxx%’ 无法使用普通 B-Tree 索引，必须用 GIN + trigram

二、建表语句（推荐写法）

✅ PostgreSQL 10+ 推荐（IDENTITY）

CREATE TABLE article (

    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    datetime TIMESTAMP NOT NULL,

    code CHAR(6) NOT NULL,

    source VARCHAR(20) NOT NULL,

    title VARCHAR(100) NOT NULL,

    url TEXT NOT NULL,

    CONSTRAINT uq_article_code_url UNIQUE (code, url)

);

如果你更偏老版本，也可以用 BIGSERIAL，但 IDENTITY 是最佳实践

三、基础索引（必备）

1️⃣ datetime 查询 / 排序（非常常见）

CREATE INDEX idx_article_datetime
ON article (datetime DESC);

适用于：

WHERE datetime >= ...
ORDER BY datetime DESC

2️⃣ code 高频过滤

CREATE INDEX idx_article_code
ON article (code);

3️⃣ code + datetime 组合查询（推荐）

如果你经常这样查：

WHERE code = '000001'
ORDER BY datetime DESC

👉 复合索引比单列更优

CREATE INDEX idx_article_code_datetime
ON article (code, datetime DESC);

四、LIKE ‘%xxx%’ 查询的正确姿势（🔥重点）

❌ 普通索引无效

WHERE title LIKE '%xxx%'

B-Tree 完全用不上

五、启用 trigram 扩展（必须）

CREATE EXTENSION IF NOT EXISTS pg_trgm;

六、为 LIKE ‘%xxx%’ 建 GIN 索引（核心）

1️⃣ source 模糊搜索

CREATE INDEX idx_article_source_trgm
ON article
USING GIN (source gin_trgm_ops);

2️⃣ title 模糊搜索

CREATE INDEX idx_article_title_trgm
ON article
USING GIN (title gin_trgm_ops);

3️⃣ url 模糊搜索（可选，但你说“经常”）

CREATE INDEX idx_article_url_trgm
ON article
USING GIN (url gin_trgm_ops);

七、LIKE 查询将会真正走索引

EXPLAIN ANALYZE
SELECT *
FROM article
WHERE title LIKE '%芯片%';

你将看到：

Bitmap Index Scan on idx_article_title_trgm

🚀 性能提升 10～100 倍

八、如果经常三字段一起搜索（进阶）

例如：

WHERE source LIKE '%新浪%'
   OR title  LIKE '%芯片%'
   OR url    LIKE '%news%'

👉 可选方案：多列 GIN（权衡）

CREATE INDEX idx_article_search_trgm
ON article
USING GIN (
    source gin_trgm_ops,
    title  gin_trgm_ops,
    url    gin_trgm_ops
);

⚠️ 注意：

索引体积会明显变大
写入成本增加
如果查询字段经常一起出现 -> 值得

九、性能 & 运维最佳实践建议（非常重要）

1️⃣ CHAR(6) 查询记得 TRIM（如果来源不规范）

WHERE code = '000001'

确保插入时已标准化，否则 CHAR 会补空格

2️⃣ 定期维护（GIN 索引必做）

VACUUM (ANALYZE) article;

高写入量系统建议：

autovacuum_vacuum_scale_factor = 0.05

3️⃣ 不要对 TEXT / VARCHAR 纠结性能

PostgreSQL 中：

TEXT 与 VARCHAR 性能完全一致

十、完整 SQL 汇总（可直接执行）

-- 导出数据
-- id 是自增主键，并无实际的意义，忽略
BEGIN;
SET LOCAL DateStyle = 'ISO, YMD';
\copy article (datetime, code, source, title, url) TO '/tmp/article.csv' WITH (FORMAT csv, HEADER true);
COMMIT;

-- 删除表
DROP TABLE IF EXISTS article CASCADE;

-- 创建扩展
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 创建表
CREATE TABLE article (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    datetime TIMESTAMP NOT NULL,
    code CHAR(6) NOT NULL,
    source VARCHAR(20) NOT NULL,
    title VARCHAR(100) NOT NULL,
    url TEXT NOT NULL,
    CONSTRAINT uq_article_code_url UNIQUE (code, url)
);

-- 提高统计精度
ALTER TABLE article
ALTER COLUMN source SET STATISTICS 1000;

ALTER TABLE article
ALTER COLUMN title SET STATISTICS 1000;

ALTER TABLE article
ALTER COLUMN url SET STATISTICS 1000;

-- 创建索引

CREATE INDEX idx_article_datetime
ON article (datetime DESC);

CREATE INDEX idx_article_code
ON article (code);

CREATE INDEX idx_article_code_datetime
ON article (code, datetime DESC);

-- DROP INDEX idx_article_source_trgm;

CREATE INDEX idx_article_source_trgm
ON article USING GIN (source gin_trgm_ops);

-- DROP INDEX idx_article_title_trgm;

CREATE INDEX idx_article_title_trgm
ON article USING GIN (title gin_trgm_ops);

-- DROP INDEX idx_article_url_trgm;

CREATE INDEX idx_article_url_trgm
ON article USING GIN (url gin_trgm_ops);

-- 导入数据
BEGIN;
SET LOCAL DateStyle = 'ISO, YMD';
\copy article (datetime, code, source, title, url) FROM '/tmp/article.csv' WITH (FORMAT csv, HEADER true);
COMMIT;

-- 检查序列
SELECT
    last_value,
    increment_by
FROM
    pg_sequences
WHERE
    schemaname = 'public'
    AND sequencename = 'article_id_seq';

-- 回收
-- VACUUM article;
-- VACUUM ANALYZE article;

-- 分析表
-- ANALYZE article;

url 使用 GIN 索引前后 EXPLAIN ANALYZE 对比

-- 统计行数

SELECT COUNT(*) FROM article;

-- 10852

-- 索引前

EXPLAIN ANALYZE SELECT * FROM article WHERE url LIKE '%weixin%';

-- Seq Scan on article  (cost=0.00..411.45 rows=1050 width=168) (actual time=0.021..2.771 rows=1021 loops=1)
--   Filter: (url ~~ '%weixin%'::text)
--   Rows Removed by Filter: 9831
-- Planning Time: 0.108 ms
-- Execution Time: 2.828 ms

-- 索引后

EXPLAIN ANALYZE SELECT * FROM article WHERE url LIKE '%weixin%';

-- Bitmap Heap Scan on article  (cost=18.31..306.45 rows=1051 width=168) (actual time=0.302..1.095 rows=1021 loops=1)
--   Recheck Cond: (url ~~ '%weixin%'::text)
--   Heap Blocks: exact=233
--   ->  Bitmap Index Scan on idx_article_url_trgm  (cost=0.00..18.05 rows=1051 width=0) (actual time=0.248..0.248 rows=1021 loops=1)
--         Index Cond: (url ~~ '%weixin%'::text)
-- Planning Time: 1.743 ms
-- Execution Time: 1.192 ms

以上输出显示了 PostgreSQL 对相同查询使用两种执行计划的对比，这里详细分析和总结优化要点。

一、查询目标

SELECT * FROM article WHERE url LIKE '%weixin%';

查询条件是 LIKE ‘%xxx%’
PostgreSQL 需要匹配 任意位置的子串，普通 B-Tree 索引无法加速，需要 trigram（pg_trgm）索引。

二、执行计划对比

1️⃣ Seq Scan（顺序扫描）

Seq Scan on article  (cost=0.00..411.45 rows=1050 width=168)
Filter: (url ~~ '%weixin%'::text)
Rows Removed by Filter: 9831
Execution Time: 2.828 ms

分析：

Seq Scan：逐行扫描整个表
Rows Removed by Filter: 9831 -> 表示表中大部分行不满足条件
Execution Time 2.828 ms -> 对于这个表量（~11k 行），还算快
优点：不依赖索引，开销低，适合小表或高选择性查询
缺点：表变大时成本线性增长

2️⃣ Bitmap Heap Scan + trgm index

Bitmap Heap Scan on article  (cost=18.31..306.45 rows=1051 width=168)
Recheck Cond: (url ~~ '%weixin%'::text)
Heap Blocks: exact=233
->  Bitmap Index Scan on idx_article_url_trgm
Index Cond: (url ~~ '%weixin%'::text)
Execution Time: 1.192 ms

分析：

使用了 pg_trgm 索引（idx_article_url_trgm）
Bitmap Index Scan -> 先用索引找出可能匹配的行
Bitmap Heap Scan -> 再访问实际表行
Rows matched ≈ 1021 -> 和 Seq Scan 一致
Execution Time 1.192 ms -> 比 Seq Scan 快约 2x

三、结论

特性	Seq Scan	Bitmap Heap Scan + trgm
使用索引	❌	✅
适合场景	小表 / 查询高选择性	LIKE ‘%xxx%’ / 表大
扫描成本	O(n)	O(匹配行 + bitmap)
执行时间	较慢	快 2~3 倍

四、优化建议

保持 pg_trgm 索引

CREATE INDEX idx_article_url_trgm ON article USING gin (url gin_trgm_ops);

支持 %xxx% 模糊匹配
对大表效果明显

选择性考虑条件
- 如果匹配行很多，Bitmap Heap Scan 会访问大量行
- 可以结合其他过滤条件减少匹配量
避免常规 B-Tree 索引
- B-Tree 索引只支持 LIKE ‘xxx%’（前缀匹配）
- 对 %xxx% 无效
定期 VACUUM ANALYZE
- 保证查询规划器能正确估算行数，选择最佳计划

五、总结

对小表，Seq Scan 足够
对大表、LIKE ‘%xxx%’ 查询，pg_trgm + GIN/GIN_TRGM 索引显著提升性能
Bitmap Heap Scan + trgm 索引是最佳实践

示例 02

表名 telegraph

id 类型为整数, 不能为空, 主键, 唯一, 不自增
datetime 类型为 TIMESTAMP, 不能为空
code 类型为 CHAR(6), 不能为空
title 类型为 VARCHAR(100), 不能为空
content 类型为 TEXT, 不能为空

需求：

datetime, code, title 都需查询，content 不需要查询
title 经常需要使用 WHERE Like ‘%xxx%’ 搜索

实践：

-- 导出数据
BEGIN;
SET LOCAL DateStyle = 'ISO, YMD';
\copy telegraph TO '/tmp/telegraph.csv' WITH (FORMAT csv, HEADER true);
COMMIT;

-- 删除表
DROP TABLE IF EXISTS telegraph CASCADE;

-- 创建扩展
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 创建表
CREATE TABLE telegraph (
    id BIGINT PRIMARY KEY,
    datetime TIMESTAMP NOT NULL,
    code CHAR(6) NOT NULL,
    title VARCHAR(100) NOT NULL,
    content TEXT NOT NULL
);

-- 提高统计精度
ALTER TABLE telegraph
ALTER COLUMN title SET STATISTICS 1000;

-- 创建索引

CREATE INDEX idx_telegraph_datetime
ON telegraph (datetime DESC);

CREATE INDEX idx_telegraph_code
ON telegraph (code);

CREATE INDEX idx_telegraph_code_datetime
ON telegraph (code, datetime DESC);

-- DROP INDEX idx_telegraph_title_trgm;

CREATE INDEX idx_telegraph_title_trgm
ON telegraph USING GIN (title gin_trgm_ops);

-- 导入数据
BEGIN;
SET LOCAL DateStyle = 'ISO, YMD';
\copy telegraph FROM '/tmp/telegraph.csv' WITH (FORMAT csv, HEADER true);
COMMIT;

-- 回收
VACUUM telegraph;
VACUUM ANALYZE telegraph;

-- 分析表
ANALYZE telegraph;

示例 03

表名 ashare

code 类型为字符串, 长度 6, 不能为空, 主键, 唯一, 不自增
name 类型为字符串, 长度 6, 不能为空, 唯一, 索引

实践：

-- 删除表
DROP TABLE IF EXISTS ashare CASCADE;

-- 创建扩展
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 创建表
CREATE TABLE ashare (
    code CHAR(6) PRIMARY KEY,
    name VARCHAR(6) NOT NULL
);

-- 创建索引

CREATE INDEX idx_ashare_name
ON ashare (name);

示例 04

表名 trading_day

date 类型为日期, 主键

实践：

-- 删除表
DROP TABLE IF EXISTS trading_day CASCADE;

-- 创建表
CREATE TABLE trading_day (
    date DATE PRIMARY KEY
);

PostgreSQL 的 DATE 字段类型专门用于存储不带时间（即没有时分秒）的日期数据，占用 4 字节存储空间，范围涵盖公元前 4713 年到公元 5874897 年。

其标准格式为 ‘YYYY-MM-DD’，如 ‘2023-10-27’，常用于生日、注册日期等仅需日期的场景。

以下是关于 PostgreSQL DATE 类型的详细说明：

核心特性

存储内容：仅包含年、月、日，不包含时、分、秒或时区信息。
存储空间：4 字节。
格式：默认和建议的输入/输出格式为 YYYY-MM-DD。
取值范围：公元前 4713 年到公元 5874897 年。

使用示例

创建表

CREATE TABLE users (
    name VARCHAR(50),
    birthday DATE
);

插入数据

INSERT INTO users (name, birthday) VALUES ('张三', '1990-01-01');

日期操作：DATE 类型支持加减操作，例如查询一周后的日期：

SELECT CURRENT_DATE + INTERVAL '7 days';

与其他日期/时间类型对比

TIMESTAMP：包含日期和时间（YYYY-MM-DD HH:MM:SS）。
TIME：仅包含一天内的时间（HH:MM:SS）。
INTERVAL：存储时间间隔。

注意事项

插入 DATE 数据时，建议将值用单引号括起来作为字符串处理。

如果需要存储时区信息，应使用 TIMESTAMP WITH TIME ZONE 类型，而不是 DATE。

PostgreSQL 也支持更灵活的输入格式，如 19970101 或 Jan-1-1997，但 ‘YYYY-MM-DD’ 是最稳妥的标准。