PostgreSQL 简介 什么是 PostgreSQLPostgreSQL 是一个功能强大的开源对象关系型数据库管理系统ORDBMS以其高扩展性和对 SQL 标准的高度兼容性而著称。自 1996 年发布以来PostgreSQL 在全球范围内被广泛应用于各种规模的应用程序中。 发展历程1986 年加州大学伯克利分校启动 POSTGRES 项目1996 年项目更名为 PostgreSQL正式支持 SQL1997 年发布 PostgreSQL 6.0引入多列索引、序列等特性2000 年代持续发展引入 WAL、表空间、时间点恢复等功能2010 年代JSON 支持、并行查询、逻辑复制等重大更新2020 年代分区表改进、JIT 编译、增量备份等性能优化 为什么选择 PostgreSQL 数据完整性严格的 ACID 事务支持 高性能多版本并发控制MVCC技术 高扩展性丰富的扩展和自定义功能 丰富的数据类型支持 JSON、数组、地理空间等 标准兼容高度符合 SQL 标准 开源免费无许可证费用社区活跃核心特性与优势 ACID 事务支持PostgreSQL 完全支持事务的 ACID 特性原子性Atomicity事务要么全部成功要么全部失败一致性Consistency数据库始终保持一致状态隔离性Isolation并发事务相互隔离持久性Durability已提交的事务永久保存 多版本并发控制MVCCMVCC 是 PostgreSQL 的核心技术之一-- 示例MVCC 工作原理 BEGIN; UPDATE users SET name 新名字 WHERE id 1; -- 此时其他事务仍能看到旧数据 COMMIT; -- 提交后新数据对所有事务可见 丰富的数据类型PostgreSQL 支持超过 40 种数据类型类型分类具体类型示例数值类型INTEGER, BIGINT, DECIMAL123,1234567890123456789字符类型VARCHAR, TEXT, CHARHello World日期时间TIMESTAMP, DATE, TIME2025-10-03 10:30:00布尔类型BOOLEANtrue,false数组类型INTEGER[], TEXT[]{1,2,3},{a,b,c}JSON 类型JSON, JSONB{name: 张三}地理空间POINT, POLYGONPOINT(116.3974, 39.9093) 扩展性PostgreSQL 允许用户创建自定义数据类型定义新的操作符编写自定义函数开发新的索引方法实现存储过程和触发器系统架构PostgreSQL 采用客户端/服务器架构主要组件包括️ 核心组件Postmaster 进程主进程负责启动和监控Backend 进程处理客户端连接和查询WAL Writer写前日志写入器Checkpointer检查点进程Background Writer后台写入器Autovacuum自动清理进程 内存结构Shared Buffers共享缓冲区WAL BuffersWAL 缓冲区Work Memory工作内存Maintenance Work Memory维护工作内存️ PostgreSQL 架构图PostgreSQL 服务器客户端应用连接层查询处理层存储层后台进程存储系统数据文件WAL 文件配置文件WAL WriterCheckpointerBackground WriterAutovacuumBuffer ManagerWAL ManagerStorage Manager查询解析器查询优化器执行器Postmaster 进程Backend 进程 1Backend 进程 2Backend 进程 NWeb 应用桌面应用移动应用 MVCC 工作原理图事务 1事务 2数据库事务 1 开始事务 2 开始事务 1 提交事务 2 再次查询BEGINUPDATE users SET name新名字 WHERE id1BEGINSELECT name FROM users WHERE id1返回旧值 旧名字COMMITSELECT name FROM users WHERE id1返回新值 新名字COMMIT事务 1事务 2数据库数据类型详解 数值类型-- 整数类型 CREATE TABLE numbers ( id SERIAL PRIMARY KEY, -- 自增整数 small_num SMALLINT, -- 2 字节整数 (-32768 到 32767) normal_num INTEGER, -- 4 字节整数 big_num BIGINT, -- 8 字节整数 decimal_num DECIMAL(10,2), -- 精确小数 float_num REAL, -- 单精度浮点数 double_num DOUBLE PRECISION -- 双精度浮点数 ); 字符类型-- 字符类型示例 CREATE TABLE text_examples ( id SERIAL PRIMARY KEY, fixed_char CHAR(10), -- 固定长度字符 var_char VARCHAR(255), -- 可变长度字符 unlimited_text TEXT, -- 无限制文本 name CITEXT -- 大小写不敏感文本 ); -- 插入数据 INSERT INTO text_examples (fixed_char, var_char, unlimited_text, name) VALUES (Hello, World, This is a long text..., JOHN DOE); 日期时间类型-- 日期时间类型 CREATE TABLE datetime_examples ( id SERIAL PRIMARY KEY, birth_date DATE, -- 日期 work_time TIME, -- 时间 created_at TIMESTAMP, -- 时间戳 updated_at TIMESTAMPTZ, -- 带时区的时间戳 duration INTERVAL -- 时间间隔 ); -- 插入数据 INSERT INTO datetime_examples (birth_date, work_time, created_at, updated_at, duration) VALUES (1990-01-01, 09:30:00, 2025-10-03 10:30:00, 2025-10-03 10:30:0008, 1 day 2 hours 30 minutes); 数组类型-- 数组类型示例 CREATE TABLE array_examples ( id SERIAL PRIMARY KEY, numbers INTEGER[], -- 整数数组 names TEXT[], -- 文本数组 matrix INTEGER[][] -- 二维数组 ); -- 插入数组数据 INSERT INTO array_examples (numbers, names, matrix) VALUES ({1,2,3,4,5}, {张三,李四,王五}, {{1,2},{3,4}}); -- 查询数组 SELECT * FROM array_examples WHERE 2 ANY(numbers); SELECT names[1] FROM array_examples; -- 获取第一个名字 JSON 类型-- JSON 类型示例 CREATE TABLE json_examples ( id SERIAL PRIMARY KEY, user_info JSON, -- JSON 类型 user_data JSONB -- 二进制 JSON 类型 ); -- 插入 JSON 数据 INSERT INTO json_examples (user_info, user_data) VALUES ({name: 张三, age: 25, city: 北京}, {name: 李四, age: 30, hobbies: [读书, 游泳]}); -- JSON 查询 SELECT user_data-name FROM json_examples; -- 获取名字 SELECT user_data-hobbies FROM json_examples; -- 获取爱好数组 SELECT * FROM json_examples WHERE user_data {age: 30}; -- 查询年龄为 30 的用户索引技术 索引类型PostgreSQL 支持多种索引类型1. B-Tree 索引默认-- 创建 B-Tree 索引 CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_users_name_age ON users(name, age); -- 复合索引2. Hash 索引-- 创建 Hash 索引仅支持等值查询 CREATE INDEX idx_users_id_hash ON users USING hash(id);3. GIN 索引通用倒排索引-- 用于数组和 JSON 数据 CREATE INDEX idx_users_tags_gin ON users USING gin(tags); CREATE INDEX idx_users_data_gin ON users USING gin(user_data);4. GiST 索引通用搜索树-- 用于地理空间数据 CREATE INDEX idx_locations_gist ON locations USING gist(coordinates);5. BRIN 索引块范围索引-- 用于大表的范围查询 CREATE INDEX idx_logs_brin ON logs USING brin(created_at); 索引类型对比图适用场景索引类型常规查询排序范围查询精确匹配等值查询全文搜索数组查询JSON 查询地理查询空间索引时间序列大表扫描B-Tree 索引默认索引范围查询Hash 索引等值查询内存友好GIN 索引倒排索引数组/JSONGiST 索引搜索树地理空间BRIN 索引块范围大表优化事务与并发控制 事务隔离级别PostgreSQL 支持四种事务隔离级别-- 设置事务隔离级别 BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 执行操作 COMMIT; -- 隔离级别说明 -- READ UNCOMMITTED: 读取未提交数据PostgreSQL 中实际为 READ COMMITTED -- READ COMMITTED: 读取已提交数据默认 -- REPEATABLE READ: 可重复读取 -- SERIALIZABLE: 串行化 事务隔离级别对比事务隔离级别READ UNCOMMITTED读取未提交READ COMMITTED读取已提交默认级别REPEATABLE READ可重复读取SERIALIZABLE串行化脏读 YES不可重复读 YES幻读 YES脏读 NO不可重复读 YES幻读 YES脏读 NO不可重复读 NO幻读 YES脏读 NO不可重复读 NO幻读 NO 锁机制-- 表级锁 LOCK TABLE users IN SHARE MODE; -- 共享锁 LOCK TABLE users IN EXCLUSIVE MODE; -- 排他锁 -- 行级锁自动 UPDATE users SET name 新名字 WHERE id 1; -- 自动加行级排他锁 -- 查看锁信息 SELECT * FROM pg_locks WHERE relation users::regclass;⚡ MVCC 示例-- 会话 1 BEGIN; UPDATE users SET balance balance - 100 WHERE id 1; -- 此时事务未提交 -- 会话 2同时执行 SELECT balance FROM users WHERE id 1; -- 仍看到旧值 -- 会话 1 COMMIT; -- 提交后会话 2 的下次查询将看到新值安装与配置 Windows 安装1. 下载安装包访问 PostgreSQL 官网 下载最新版本。2. 安装步骤运行安装程序选择安装路径设置超级用户密码选择端口默认 5432选择语言环境3. 验证安装# 检查 PostgreSQL 服务状态 sc query postgresql-x64-16 # 连接到数据库 psql -U postgres -h localhost -p 5432 Linux 安装Ubuntu/Debian# 更新包列表 sudo apt update # 安装 PostgreSQL sudo apt install postgresql postgresql-contrib # 启动服务 sudo systemctl start postgresql sudo systemctl enable postgresql # 切换到 postgres 用户 sudo -u postgres psqlCentOS/RHEL# 安装 PostgreSQL sudo yum install postgresql-server postgresql-contrib # 初始化数据库 sudo postgresql-setup initdb # 启动服务 sudo systemctl start postgresql sudo systemctl enable postgresql⚙️ 基本配置postgresql.conf 配置# 连接设置 listen_addresses * # 监听所有地址 port 5432 # 端口号 max_connections 100 # 最大连接数 # 内存设置 shared_buffers 256MB # 共享缓冲区 work_mem 4MB # 工作内存 maintenance_work_mem 64MB # 维护工作内存 # 日志设置 log_destination stderr # 日志目标 logging_collector on # 启用日志收集 log_directory log # 日志目录 log_filename postgresql-%Y-%m-%d_%H%M%S.log log_min_duration_statement 1000 # 记录慢查询毫秒 # 性能设置 random_page_cost 1.1 # 随机页面成本 effective_cache_size 1GB # 有效缓存大小pg_hba.conf 配置# 本地连接 local all all trust # IPv4 本地连接 host all all 127.0.0.1/32 md5 # IPv6 本地连接 host all all ::1/128 md5 # 网络连接 host all all 0.0.0.0/0 md5性能优化 查询优化1. 使用 EXPLAIN 分析查询-- 基本查询分析 EXPLAIN SELECT * FROM users WHERE email testexample.com; -- 详细分析包含实际执行时间 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT u.name, p.title FROM users u JOIN posts p ON u.id p.user_id WHERE u.active true;
