PostgreSQL
约 966 字大约 3 分钟
PostgreSQL数据库
2026-04-08
简介
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 也够用。
