跳到主要内容

PostgreSQL 特性

问题​

PostgreSQL 相比 MySQL 有哪些独特特性？

答案​

PostgreSQL vs MySQL​

维度PostgreSQLMySQL
定位功能丰富的对象关系数据库简单高效的关系数据库
JSONB原生支持，可建索引JSON 类型，功能较弱
全文搜索内置 tsvector基础 FULLTEXT
CTE 性能优秀MySQL 8.0+ 支持
窗口函数完整支持MySQL 8.0+ 支持
扩展丰富（PostGIS、pgvector）插件较少
并发MVCC（无锁读）MVCC + 间隙锁

JSONB 类型​

jsonb.sql
-- 创建含 JSONB 列的表
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(200),
  metadata JSONB NOT NULL DEFAULT '{}'
);

-- 插入 JSON 数据
INSERT INTO products (name, metadata) VALUES
  ('iPhone', '{"color": "black", "storage": 256, "tags": ["phone", "apple"]}');

-- 查询 JSON 字段
SELECT name, metadata->>'color' AS color
FROM products
WHERE metadata->>'storage' = '256';

-- JSON 数组包含
SELECT * FROM products
WHERE metadata @> '{"tags": ["apple"]}';

-- 为 JSONB 建 GIN 索引
CREATE INDEX idx_metadata ON products USING GIN (metadata);

窗口函数​

window-functions.sql
-- 排名
SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

-- 累计求和
SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM orders;

-- 前后行对比
SELECT date, amount,
  LAG(amount, 1) OVER (ORDER BY date) AS prev_amount,
  amount - LAG(amount, 1) OVER (ORDER BY date) AS diff
FROM daily_sales;

CTE 公共表表达式​

cte.sql
-- 递归 CTE：树形结构查询
WITH RECURSIVE category_tree AS (
  -- 基础条件
  SELECT id, name, parent_id, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  
  UNION ALL
  
  -- 递归条件
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

常见面试问题​

Q1: 什么时候选 PostgreSQL？​

答案：

  • 需要 JSONB（半结构化数据）
  • 复杂查询多（窗口函数、CTE、子查询）
  • 需要 GIS 地理信息（PostGIS）
  • 需要向量搜索（pgvector）
  • 数据完整性要求高

Q2: JSONB 和 MongoDB 怎么选？​

答案：

  • PostgreSQL JSONB：关系型 + 文档的混合，既有 SQL 查询能力又支持灵活 JSON
  • MongoDB：纯文档数据库，大量文档操作、水平扩展更方便

少量 JSON 字段 → PostgreSQL；整体数据模型都是文档 → MongoDB。

Q3: PostgreSQL 的扩展有哪些常用的？​

答案：

扩展用途
pgvector向量搜索（AI 应用）
PostGIS地理信息系统
pg_trgm模糊搜索
timescaledb时序数据
pg_stat_statementsSQL 性能分析

相关链接​