Postgresql

TL;DR: 本文介绍 PostgreSQL 的核心特性、安装配置、基本操作，以及 JSON 支持、全文搜索、窗口函数等高级功能。读完即可上手使用。

假设读者熟悉 SQL 基础，了解至少一种关系型数据库。

为什么选择 PostgreSQL Link to heading

PostgreSQL 是一个开源的对象关系型数据库系统，以稳定性、扩展性和标准合规著称。相比 MySQL 等竞品，它的核心优势在于：

严格的 ACID 合规性：事务隔离、崩溃恢复可靠
丰富的数据类型：原生支持数组、JSON/JSONB、几何类型、UUID 等
强大的扩展机制：支持自定义类型、函数、操作符，以及 PostGIS 等知名扩展
高级查询能力：CTE、窗口函数、物化视图、并行查询

根据 Stack Overflow 2024 开发者调查，PostgreSQL 已连续多年成为开发者"最想要的数据库"。

安装与配置 Link to heading

安装 Link to heading

macOS (Homebrew):

brew install postgresql@16
brew services start postgresql@16

Ubuntu/Debian:

sudo apt install postgresql-16
sudo systemctl start postgresql

Docker:

docker run --name pg-demo -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:16

连接数据库 Link to heading

psql -U postgres -h localhost

连接后创建测试数据库：

CREATE DATABASE demo_db;
\c demo_db

基本操作 Link to heading

创建表 Link to heading

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    settings JSONB DEFAULT '{}'
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    published_at TIMESTAMPTZ,
    view_count INT DEFAULT 0
);

插入与查询 Link to heading

INSERT INTO users (username, email) VALUES
    ('alice', 'alice@example.com'),
    ('bob', 'bob@example.com');

INSERT INTO posts (user_id, title, content, published_at) VALUES
    (1, 'PostgreSQL 入门', '这是一篇教程...', NOW()),
    (1, '高级 SQL 技巧', '窗口函数很有用...', NOW()),
    (2, '数据库优化', '索引是关键...', NOW());

常用查询：

-- JOIN 查询
SELECT u.username, p.title, p.published_at
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.published_at IS NOT NULL
ORDER BY p.published_at DESC;

-- 聚合统计
SELECT u.username, COUNT(p.id) AS post_count, SUM(p.view_count) AS total_views
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.username
HAVING COUNT(p.id) > 0;

索引 Link to heading

-- B-tree 索引（默认）
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- 部分索引（只索引已发布的文章）
CREATE INDEX idx_published ON posts(published_at) WHERE published_at IS NOT NULL;

-- 复合索引
CREATE INDEX idx_posts_user_published ON posts(user_id, published_at);

使用 EXPLAIN ANALYZE 验证索引效果：

EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 1;

高级功能概览 Link to heading

JSON/JSONB 支持 Link to heading

PostgreSQL 原生支持 JSON 存储和查询，兼具关系型与文档型数据库的优势：

-- 查询 JSON 字段
SELECT username, settings->>'theme' AS theme
FROM users
WHERE settings->>'theme' = 'dark';

-- 更新 JSON 字段
UPDATE users
SET settings = jsonb_set(settings, '{theme}', '"light"')
WHERE username = 'alice';

-- 在 JSON 字段上创建 GIN 索引
CREATE INDEX idx_users_settings ON users USING GIN (settings);

适合场景：动态配置、半结构化数据、快速迭代的原型阶段。

全文搜索 Link to heading

无需引入 Elasticsearch，PostgreSQL 内置全文搜索能力：

-- 创建搜索向量和索引
ALTER TABLE posts ADD COLUMN search_vector tsvector;

UPDATE posts SET search_vector =
    to_tsvector('chinese', COALESCE(title, '') || ' ' || COALESCE(content, ''));

CREATE INDEX idx_posts_search ON posts USING GIN (search_vector);

-- 全文搜索
SELECT title, content
FROM posts
WHERE search_vector @@ to_tsquery('chinese', '教程 & 技巧');

-- 带高亮的搜索
SELECT ts_headline('chinese', content, to_tsquery('chinese', '索引'),
    'StartSel = <b>, StopSel = </b>') AS highlighted
FROM posts
WHERE search_vector @@ to_tsquery('chinese', '索引');

对于中文搜索，建议安装 zhparser 扩展配合 pg_jieba 分词。

窗口函数 Link to heading

窗口函数在不改变结果集行数的情况下进行聚合计算：

-- 排名
SELECT title, view_count,
       RANK() OVER (ORDER BY view_count DESC) AS rank,
       ROW_NUMBER() OVER (ORDER BY published_at DESC) AS row_num
FROM posts;

-- 移动平均
SELECT published_at, view_count,
       AVG(view_count) OVER (ORDER BY published_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM posts;

-- 分区统计
SELECT username, title, view_count,
       SUM(view_count) OVER (PARTITION BY username) AS user_total_views
FROM posts p
JOIN users u ON p.user_id = u.id;

CTE 与递归查询 Link to heading

-- CTE 提高可读性
WITH active_users AS (
    SELECT id, username FROM users WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT au.username, COUNT(p.id) AS posts
FROM active_users au
JOIN posts p ON au.id = p.user_id
GROUP BY au.username;

-- 递归 CTE：处理树形结构
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 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;

物化视图 Link to heading

-- 创建物化视图
CREATE MATERIALIZED VIEW user_stats AS
SELECT u.id, u.username, COUNT(p.id) AS post_count, COALESCE(SUM(p.view_count), 0) AS total_views
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;

-- 刷新数据
REFRESH MATERIALIZED VIEW user_stats;

-- 并发刷新（不阻塞查询）
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;
-- 注意：需要 UNIQUE 索引

扩展：PostGIS Link to heading

空间数据支持是 PostgreSQL 的杀手级特性之一：

CREATE EXTENSION IF NOT EXISTS postgis;

-- 存储地理位置
CREATE TABLE places (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    location GEOMETRY(Point, 4326)
);

INSERT INTO places (name, location)
VALUES ('Beijing', ST_SetSRID(ST_MakePoint(116.40, 39.90), 4326));

-- 查找附近的位置（50km 内）
SELECT name FROM places
WHERE ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(116.40, 39.90), 4326),
    0.5
);

实用技巧 Link to heading

常用 psql 命令 Link to heading

\dt          -- 列出所有表
\d table     -- 查看表结构
\l           -- 列出所有数据库
\di          -- 列出所有索引
\du          -- 列出所有用户
\timing      -- 开启执行时间显示
\explain     -- 分析查询计划

性能调优要点 Link to heading

shared_buffers：建议设为系统内存的 25%
work_mem：控制排序和哈希操作的内存，默认 4MB 偏低
effective_cache_size：告诉优化器有多少内存可用于缓存
使用 pg_stat_statements 扩展定位慢查询

总结 Link to heading

PostgreSQL 的核心优势在于功能全面且稳定可靠。对于大多数应用场景，它既能满足传统关系型需求，又能通过 JSONB 处理半结构化数据，通过全文搜索替代专用搜索引擎，通过 PostGIS 处理空间数据。

选择建议：

新项目默认选 PostgreSQL，除非团队已有 MySQL 深厚积累
需要 GIS 能力时，PostGIS 是不二之选
高并发写入场景可配合连接池（PgBouncer）

延伸阅读 Link to heading

本文由 AI 生成