PostgreSQL

PostgreSQL 简介

PostgreSQL（简称 Postgres）是一个开源的 关系型数据库管理系统（RDBMS），由 PostgreSQL 全球开发组 维护，以其 强大的功能、高扩展性和标准兼容性 著称。PostgreSQL 支持 SQL 标准 并提供了 JSON、地理空间、全文搜索 等高级特性，适用于 企业级应用、大数据分析、地理信息系统（GIS） 等场景。其核心优势包括 事务安全（ACID）、多版本并发控制（MVCC）和丰富的扩展生态，被誉为 “世界上最先进的开源数据库”。

核心特性

1. 标准兼容与高级 SQL 支持
   • 兼容 SQL:2016 标准，支持 窗口函数（Window Functions）、CTE（WITH 子句）
   • 复杂查询优化（如 并行查询、JIT 编译）
2. 多模型数据库能力
   • 关系型数据：完整的表、视图、外键约束
   • JSON/JSONB：原生支持文档存储和查询
   • 地理空间数据：通过 PostGIS 扩展实现 GIS 功能
3. 高并发与事务安全
   • MVCC（多版本并发控制）：读写操作互不阻塞
   • 事务隔离级别：支持读已提交（Read Committed）、可序列化（Serializable）等
4. 可扩展性
   • 自定义 存储过程（PL/pgSQL）、函数、运算符
   • 支持 分表（Partitioning）、逻辑复制（Logical Replication）
5. 高可用与容灾
   • 主从复制（Streaming Replication）：支持同步/异步模式
   • PgBouncer：连接池管理，提升并发性能
6. 安全与合规
   • 角色权限管理（RBAC）
   • 数据加密（TDE） 和 审计日志

核心概念

典型应用场景

1. 企业级应用
   • ERP、CRM 等复杂业务系统（如 SAP、Oracle 替代方案）
2. 地理信息系统（GIS）
   • 地图服务、位置数据分析（结合 PostGIS）
3. 数据分析与仓库
   • 大规模 ETL 处理、实时报表（列存储扩展 cstore_fdw）
4. Web 应用后端
   • 支持 Django、Ruby on Rails 等框架的 ORM
5. 时序数据
   • 物联网设备监控（TimescaleDB 扩展）

快速入门示例

1. 安装与启动

# Ubuntu/Debian
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

# 登录
sudo -u postgres psql

2. 基础 SQL 操作

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

-- 插入数据
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');

-- 查询
SELECT * FROM users WHERE name LIKE 'A%';

-- 创建索引
CREATE INDEX idx_name ON users(name);

3. JSON 功能示例

-- 存储 JSON
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    details JSONB
);

INSERT INTO products (details) VALUES 
    ('{"name": "Laptop", "price": 999.99, "tags": ["electronics", "sale"]}');

-- JSON 查询
SELECT details->>'name' FROM products WHERE details @> '{"tags": ["sale"]}';

4. PostGIS 扩展

-- 启用扩展
CREATE EXTENSION postgis;

-- 存储地理坐标
CREATE TABLE places (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    location GEOGRAPHY(POINT)
);

-- 查询附近地点
SELECT name FROM places 
WHERE ST_Distance(location, ST_MakePoint(-74.0, 40.7)) < 1000;

性能优化建议

1. 索引策略
   • 对高频查询字段创建 B-Tree 索引
   • JSONB 数据使用 GIN 索引 加速嵌套查询
2. 分区表
   • 按时间范围分区大表：
     CREATE TABLE logs (
         log_date DATE,
         message TEXT
     ) PARTITION BY RANGE (log_date);

3. 配置调优
   • 调整 shared_buffers（建议内存的 25%）和 work_mem
   • 启用 parallel_query 加速复杂查询
4. 监控工具
   • 使用 pg_stat_activity 查看当前查询
   • EXPLAIN ANALYZE 分析执行计划