VIBE PROJECTING
登录

PostgreSQL

技术栈
数据库
postgresqlrdbmssqlrelational-databaseopen-sourceadvanced

概览

PostgreSQL 是全球最强大的开源对象关系型数据库系统（ORDBMS），由加州大学伯克利分校发起，经 30+ 年发展，以功能完备性、标准兼容性和扩展性著称。支持复杂查询、窗口函数、CTE、JSON/JSONB、全文搜索、地理空间数据（PostGIS）、自定义类型等高级特性，被 Apple、Spotify、Instagram 等广泛采用，是学术研究和企业级应用的首选数据库。

安装

1. 环境准备

  • 操作系统：Linux (Ubuntu 20.04+/CentOS 7+)、macOS 12+、Windows 10+
  • 硬件要求：最低 512MB RAM（推荐 2GB+），1GB 磁盘空间
  • 端口：默认 5432，确保防火墙开放
  • 用户：安装后自动创建 postgres 系统用户

2. 安装命令

Ubuntu/Debian

sudo apt update
sudo apt install postgresql postgresql-contrib -y
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 切换到 postgres 用户设置密码
sudo -u postgres psql
ALTER USER postgres PASSWORD '新密码';
\q

macOS (Homebrew)

brew install postgresql@16
brew services start postgresql@16
createuser -s postgres  # 创建超级用户

Windows

# Choco
choco install postgresql

# 或下载官方安装器 https://www.postgresql.org/download/windows/

Docker（推荐开发环境）

docker run --name pg-dev -e POSTGRES_PASSWORD=pg123 \
  -p 5432:5432 -v ~/pg-data:/var/lib/postgresql/data \
  -d postgres:16-alpine

验证安装

psql -U postgres -h localhost
# 或 Docker 方式：
docker exec -it pg-dev psql -U postgres

3. 常见安装问题

Q: peer authentication failed (Linux)
编辑 /etc/postgresql/16/main/pg_hba.conf，将：

local   all   postgres   peer

改为：

local   all   postgres   md5

然后重启：sudo systemctl restart postgresql

Q: Docker 端口冲突

docker run --name pg-dev -p 5433:5432 \  # 映射到 5433
  -e POSTGRES_PASSWORD=pg123 -d postgres:16-alpine

Q: macOS Homebrew 多版本共存

brew install postgresql@14 postgresql@16
# 使用具体版本的 service：
brew services start postgresql@16

示例

目标

通过 psql 命令行和 Python 驱动完成 PostgreSQL 数据库创建、表设计、CRUD 操作，体验 PostgreSQL 独有的 JSONB 和窗口函数特性。

环境准备

# 确保 PostgreSQL 已运行
psql -U postgres -h localhost

第一步：psql 命令行操作

-- 创建数据库
CREATE DATABASE hello_pg;

-- 连接（命令行：\c hello_pg）

-- 创建图书表
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100),
    price DECIMAL(6,2),
    tags JSONB DEFAULT '[]',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入数据（含 JSONB）
INSERT INTO books (title, author, price, tags) VALUES
    ('深入浅出PostgreSQL', '张三', 79.00, '["数据库","SQL","入门"]'),
    ('高性能PostgreSQL', '李四', 99.00, '["数据库","性能优化","进阶"]'),
    ('PostgreSQL实战', '王五', 69.00, '["数据库","实践","SQL"]');

-- JSONB 查询：查找 tags 包含"进阶"的书
SELECT title, author FROM books
WHERE tags @> '["进阶"]'::jsonb;

-- 窗口函数：按价格排名
SELECT title, price,
    RANK() OVER (ORDER BY price DESC) AS rank
FROM books;

第二步：Python + psycopg2

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    dbname="hello_pg",
    user="postgres",
    password="pg123"
)
cur = conn.cursor()

# 创建表
cur.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id SERIAL PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        score INT,
        grade CHAR(1) GENERATED ALWAYS AS (
            CASE
                WHEN score >= 90 THEN 'A'
                WHEN score >= 80 THEN 'B'
                WHEN score >= 70 THEN 'C'
                ELSE 'F'
            END
        ) STORED
    )
""")

# 插入
cur.execute(
    "INSERT INTO students (name, score) VALUES (%s, %s), (%s, %s)",
    ("小明", 92, "小红", 85)
)
conn.commit()

# 查询
cur.execute("SELECT * FROM students ORDER BY score DESC")
for row in cur.fetchall():
    print(f"{row[1]}: {row[2]}分 → {row[3]}")

cur.close()
conn.close()

预期输出

# psql JSONB 查询
 title       | author
-------------+-------
 高性能PostgreSQL | 李四

# 窗口函数
 title              | price | rank
--------------------+-------+------
 高性能PostgreSQL   | 99.00 |    1
 深入浅出PostgreSQL | 79.00 |    2
 PostgreSQL实战     | 69.00 |    3

# Python 生成列
小明: 92分 → A
小红: 85分 → B

教程

1. 什么是 PostgreSQL？

PostgreSQL 常被称为"最先进的开源数据库"。与 MySQL 相比，它更严格遵循 SQL 标准，同时支持对象关系型特性（表继承、自定义类型、JSON 原生操作等）。

为什么选择 PostgreSQL？

  • ACID 完全兼容：事务可靠性优于 MySQL 的 MyISAM
  • JSONB：JSON 可建索引，兼具关系型和文档型优势
  • 窗口函数：高级数据分析不可或缺
  • PostGIS：地理空间数据的事实标准
  • 自定义扩展：支持 Python/R/JavaScript 写函数

2. Schema 设计实战

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    content TEXT,
    tags VARCHAR(50)[] DEFAULT '{}',        -- 数组字段
    metadata JSONB DEFAULT '{}',            -- 灵活 JSON
    published BOOLEAN DEFAULT false,
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- GIN 索引加速 JSONB 和数组查询
CREATE INDEX idx_posts_tags ON blog_posts USING GIN(tags);
CREATE INDEX idx_posts_metadata ON blog_posts USING GIN(metadata);

3. JSONB 高级操作

-- 插入含 JSON 元数据
INSERT INTO blog_posts (title, slug, content, tags, metadata)
VALUES (
    'PostgreSQL 入门指南',
    'pg-intro-2024',
    'PostgreSQL 是全球最先进的开源数据库...',
    ARRAY['PostgreSQL', 'SQL', '数据库'],
    '{"read_time": 15, "difficulty": "beginner", "series": "DB Basics"}'
);

-- JSONB 字段查询
SELECT title FROM blog_posts
WHERE metadata @> '{"difficulty": "beginner"}';

-- JSONB 提取
SELECT
    title,
    metadata->>'read_time' AS read_minutes,
    metadata->>'difficulty' AS level
FROM blog_posts;

-- JSONB 更新
UPDATE blog_posts
SET metadata = metadata || '{"updated": true, "version": 2}'::jsonb
WHERE id = 1;

4. 窗口函数数据排名

-- 模拟学生分数
CREATE TABLE scores (student VARCHAR(20), subject VARCHAR(20), score INT);
INSERT INTO scores VALUES
    ('小明', '语文', 88), ('小明', '数学', 92), ('小明', '英语', 85),
    ('小红', '语文', 95), ('小红', '数学', 89), ('小红', '英语', 91),
    ('小刚', '语文', 76), ('小刚', '数学', 82), ('小刚', '英语', 78);

-- RANK：各科排名（同分同排名，后续跳跃）
SELECT student, subject, score,
    RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rank
FROM scores;

-- 累积分布
SELECT student,
    SUM(score) AS total,
    PERCENT_RANK() OVER (ORDER BY SUM(score)) AS pct
FROM scores
GROUP BY student;

5. CTE 递归查询（组织架构树）

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'CEO', NULL), (2, 'CTO', 1), (3, 'CFO', 1),
    (4, '工程师A', 2), (5, '工程师B', 2), (6, '会计A', 3);

-- 递归查询所有下属
WITH RECURSIVE hierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE id = 1  -- CEO
    UNION ALL
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT repeat('  ', level-1) || name AS org_chart
FROM hierarchy;

6. 性能优化

-- 查看慢查询
-- 在 postgresql.conf 中设置：log_min_duration_statement = 1000

-- 分析查询计划
EXPLAIN ANALYZE
SELECT * FROM blog_posts WHERE tags @> ARRAY['PostgreSQL'];

-- 常用维护
VACUUM ANALYZE;           -- 回收空间 + 更新统计
REINDEX DATABASE hello_pg; -- 重建索引

思考题

  1. PostgreSQL 的 JSONB 和 JSON 类型有什么区别？何时用哪个？
  2. CTE 递归查询 vs 应用层循环，各有什么优劣？
  3. VACUUM 为什么在 PostgreSQL 中如此重要？

推荐资源