Skip to main content
Septvean's Documents
Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Back to homepage
  1. Documents
  2. /
  3. Database
  4. /
  5. PostgreSQL
  6. /
  7. PostgreSQL 学习指南

PostgreSQL 学习指南

PostgreSQL 是一个功能完整的开源关系型数据库，适合：

  • Python、FastAPI、SQLAlchemy 后端项目
  • 股票行情和财务数据存储
  • TimescaleDB 时序数据库
  • JSON、全文检索、数据分析
  • 高并发事务系统

截至 2026 年 9 月，PostgreSQL 18 是当前稳定主版本，官方文档对应 18.6。PostgreSQL 18 官方文档

一、建议学习路线

第一阶段：数据库基础

  1. 数据库、Schema、表、行、列
  2. PostgreSQL 数据类型
  3. 表的创建、修改和删除
  4. INSERT、SELECT、UPDATE、DELETE
  5. 条件、排序、分页
  6. 聚合函数与分组
  7. 多表连接
  8. 子查询和公共表表达式

第二阶段：表设计

  1. 主键与外键
  2. 唯一约束、非空约束、检查约束
  3. 一对一、一对多、多对多
  4. 数据规范化
  5. 自增主键与 UUID
  6. 日期、金额、JSON 数据建模
  7. 分区表

第三阶段：高级查询

  1. 窗口函数
  2. CTE 与递归查询
  3. CASE
  4. DISTINCT ON
  5. LATERAL
  6. 数组与 jsonb
  7. 全文检索
  8. 视图与物化视图

第四阶段：事务与并发

  1. ACID
  2. BEGIN、COMMIT、ROLLBACK
  3. MVCC
  4. 事务隔离级别
  5. 行锁、表锁
  6. 死锁
  7. 乐观锁与悲观锁
  8. SELECT ... FOR UPDATE

第五阶段：性能优化

  1. B-tree、GIN、GiST、BRIN 索引
  2. 联合索引和覆盖索引
  3. EXPLAIN
  4. EXPLAIN ANALYZE
  5. 查询计划
  6. 慢 SQL 定位
  7. VACUUM、ANALYZE
  8. pg_stat_statements
  9. 表膨胀与索引膨胀
  10. 配置参数优化

第六阶段：运维管理

  1. 用户、角色与权限
  2. pg_hba.conf
  3. 备份与恢复
  4. WAL
  5. 流复制与逻辑复制
  6. 连接池
  7. 日志与监控
  8. 大版本升级
  9. 故障恢复
  10. TimescaleDB 扩展管理

二、核心对象关系 

PostgreSQL 实例
└── Database
    └── Schema
        ├── Table
        ├── View
        ├── Index
        ├── Sequence
        └── Function

连接 PostgreSQL 时，客户端首先连接某个数据库，然后访问该数据库内不同 Schema 中的对象。

三、第一张表

以 A 股股票信息为例：

CREATE TABLE stock (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code        varchar(6) NOT NULL UNIQUE,
    name        varchar(50) NOT NULL,
    market      varchar(20) NOT NULL,
    industry    varchar(100),
    is_st       boolean NOT NULL DEFAULT false,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT ck_stock_code
        CHECK (code ~ '^[0-9]{6}$')
);

这里包含：

  • 主键
  • 自动生成的 ID
  • 唯一约束
  • 非空约束
  • 默认值
  • 正则检查约束
  • 带时区时间类型

四、基本增删改查

插入 

INSERT INTO stock (code, name, market, industry)
VALUES ('600519', '贵州茅台', '主板', '白酒');

查询 

SELECT code, name, industry
FROM stock
WHERE code LIKE '60%'
  AND is_st = false
ORDER BY code;

更新 

UPDATE stock
SET industry = '食品饮料',
    updated_at = now()
WHERE code = '600519';

删除 

DELETE FROM stock
WHERE code = '600519';

生产环境执行 UPDATE 或 DELETE 前，建议先用同样的条件执行一次：

SELECT *
FROM stock
WHERE code = '600519';

五、事务 

BEGIN;

UPDATE account
SET balance = balance - 1000
WHERE id = 1;

UPDATE account
SET balance = balance + 1000
WHERE id = 2;

COMMIT;

出现异常时：

ROLLBACK;

事务解决的不是“SQL 能否执行”，而是“一组操作是否必须共同成功或共同失败”。

六、索引 

CREATE INDEX idx_stock_industry
ON stock (industry);

联合索引：

CREATE INDEX idx_stock_market_industry
ON stock (market, industry);

部分索引：

CREATE INDEX idx_stock_normal_code
ON stock (code)
WHERE is_st = false;

判断查询是否使用索引：

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM stock
WHERE code = '600519';

索引不是越多越好。它能提高读取速度，但会增加写入、存储和维护成本。

七、PostgreSQL 18 重要变化

PostgreSQL 18 的代表性改进包括：

  • 引入异步 I/O 子系统
  • 多列 B-tree 索引支持跳跃扫描
  • 新增时间有序的 uuidv7()
  • 支持虚拟生成列，并成为生成列默认方式
  • DML 的 RETURNING 支持 OLD 和 NEW
  • 支持时态主键、唯一约束和外键
  • pg_upgrade 可以保留优化器统计信息
  • 新建集群默认启用数据校验和
  • 增强 VACUUM、I/O 和 WAL 监控
  • 支持 OAuth 身份认证
  • MD5 密码认证被标记为弃用

详见 PostgreSQL 18 发布说明。

例如：

SELECT uuidv7();

UPDATE stock
SET name = '新名称'
WHERE code = '600519'
RETURNING OLD.name AS old_name,
          NEW.name AS new_name;

八、与你现有技术栈的关系

建议形成这一套结构：

FastAPI
   ↓
SQLAlchemy 2.0
   ↓
psycopg 3 / asyncpg
   ↓
PostgreSQL 18
   ↓
TimescaleDB

职责划分：

  • FastAPI：提供 HTTP API
  • SQLAlchemy 2.0：模型定义和数据库操作
  • Alembic：数据库迁移
  • psycopg 3 / asyncpg：数据库驱动
  • PostgreSQL 18：关系数据、事务和查询
  • TimescaleDB：行情等大规模时序数据

建议接下来按以下顺序深入：

  1. PostgreSQL 数据类型
  2. PostgreSQL 表设计
  3. PostgreSQL 增删改查
  4. PostgreSQL 多表查询
  5. PostgreSQL 索引
  6. PostgreSQL 事务与锁
  7. PostgreSQL 性能优化
  8. PostgreSQL 备份恢复
  9. PostgreSQL 18 + TimescaleDB
  10. PostgreSQL 18 + SQLAlchemy 2.0 + FastAPI