简介

PostgreSQL（简称 Postgres）是一款开源、功能强大的对象关系型数据库（ORDBMS），以稳定性、标准兼容性和扩展性著称。相比 MySQL，它在复杂查询、事务一致性、JSON 支持、地理空间（PostGIS）等场景下表现更出色，被广泛用作互联网公司的核心数据库。

特点：

• 完全开源，BSD-like 协议，可商用
• 支持完整的 ACID 事务、MVCC 并发控制
• 支持丰富的数据类型：JSON/JSONB、数组、范围、UUID、几何类型
• 强大的扩展机制：PostGIS、TimescaleDB、pgvector 等
• 支持窗口函数、CTE、物化视图、全文检索
• 跨平台，社区活跃

安装

macOS

推荐使用 Homebrew：

brew install postgresql@16
brew services start postgresql@16

或使用 Postgres.app，开箱即用，附带菜单栏管理。

Docker

最简单的方式：

docker run --name pg \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  -d postgres:16

验证

psql -h localhost -U postgres -d mydb

基础概念

概念	说明
Cluster	一个 PostgreSQL 实例，可包含多个数据库
Database	数据库，包含 schema
Schema	命名空间，默认是 public
Role	用户与权限的统一抽象，可登录的 role 即用户
Tablespace	物理存储位置

常用 SQL

库与表

-- 创建数据库
CREATE DATABASE blog ENCODING 'UTF8';

-- 切换数据库（psql 内）
\c blog

-- 创建表
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(64) NOT NULL,
    email       VARCHAR(128) UNIQUE NOT NULL,
    profile     JSONB,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_profile ON users USING GIN (profile);

增删改查

INSERT INTO users (name, email, profile)
VALUES ('ZhenYu', 'zhenyu@example.com', '{"city": "Hangzhou", "lang": ["zh", "en"]}');

SELECT id, name, profile->>'city' AS city
FROM users
WHERE profile @> '{"lang": ["zh"]}';

UPDATE users SET profile = profile || '{"vip": true}' WHERE id = 1;

DELETE FROM users WHERE id = 1;

事务

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

CTE 与窗口函数

WITH ranked AS (
    SELECT
        name,
        score,
        RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rk
    FROM students
)
SELECT * FROM ranked WHERE rk <= 3;

JSONB

PostgreSQL 的 JSONB 是其相比 MySQL 的一大杀手锏：二进制存储、支持索引、查询性能高。

-- 包含
SELECT * FROM users WHERE profile @> '{"vip": true}';

-- 路径查询
SELECT profile #>> '{address, city}' FROM users;

-- 修改
UPDATE users SET profile = jsonb_set(profile, '{age}', '30');

psql 常用命令

命令	说明
\l	列出所有数据库
\c dbname	切换数据库
\dt	列出所有表
\d tablename	查看表结构
\du	列出所有用户
\df	列出函数
\timing	开启执行耗时统计
\q	退出

用户与权限

-- 创建用户
CREATE ROLE app WITH LOGIN PASSWORD 'secret';

-- 授权
GRANT CONNECT ON DATABASE blog TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app;

备份与恢复

# 备份单库
pg_dump -h localhost -U postgres blog > blog.sql

# 自定义格式（推荐，可并行恢复）
pg_dump -Fc -h localhost -U postgres blog -f blog.dump

# 恢复
psql -U postgres -d blog < blog.sql
pg_restore -U postgres -d blog blog.dump

常用扩展

扩展	用途
PostGIS	地理空间数据处理，WebGIS 必备
pgvector	向量检索，AI/RAG 场景核心扩展
TimescaleDB	时序数据库
pg_trgm	模糊匹配、相似度搜索
uuid-ossp	UUID 生成

启用扩展：

CREATE EXTENSION IF NOT EXISTS pgvector;

与 MySQL 的对比

维度	PostgreSQL	MySQL
类型系统	丰富（JSONB、数组、范围、几何）	基础
事务	完整 ACID，DDL 也支持事务	DDL 不支持事务
并发	MVCC，读不阻塞写	InnoDB MVCC
复杂查询	优化器强，CTE/窗口函数完整	较弱
扩展性	扩展机制丰富	较弱
上手难度	略陡	简单
生态	越来越主流	传统主流

经验法则：业务复杂、数据建模重的场景选 PostgreSQL；CRUD 为主、运维成熟的场景 MySQL 也够用。

客户端工具

• pgAdmin — 官方图形客户端
• DBeaver — 通用数据库 GUI
• TablePlus — macOS 上颜值在线
• DataGrip — JetBrains 全家桶

参考资料

• 官方文档
• PostgreSQL 中文文档
• Awesome Postgres

贡献者: Ra1n

上一页MySQL

下一页MongoDB