垦荒学园

简体中文

English

简体中文

English

切换主题

PostgreSQL简介 ​

什么是PostgreSQL? ​

PostgreSQL是一个强大的、开源的对象关系数据库管理系统（ORDBMS）。它以其可靠性、数据完整性以及丰富的功能集而享有盛誉。PostgreSQL支持SQL（关系型）和JSON（非关系型）查询，以其处理复杂工作负载和大数据量的能力而著称。

主要特点 ​

PostgreSQL在数据库领域因其以下几个原因而脱颖而出：

  1. ACID合规性：PostgreSQL完全支持ACID（原子性、一致性、隔离性、持久性）属性，确保可靠的事务处理。

  2. 开源：在宽松的PostgreSQL许可证下，可以免费使用、修改和分发。

  3. 可扩展性：用户可以通过添加自定义函数、运算符、数据类型等来扩展PostgreSQL。

  4. 标准合规性：PostgreSQL实现了大部分SQL标准并提供了许多现代特性。

  5. 社区支持：一个充满活力的全球社区为其持续改进做出贡献。

PostgreSQL的历史 ​

起源 ​

PostgreSQL始于1986年加州大学伯克利分校的POSTGRES项目，由Michael Stonebraker教授领导。该项目旨在为传统关系数据库添加复杂数据类型和面向对象编程概念的支持。

演变 ​

  • 1986年：POSTGRES项目在加州大学伯克利分校启动
  • 1989年：版本1向外部用户发布
  • 1994年：发布POSTGRES95，添加了SQL支持
  • 1996年：更名为PostgreSQL以反映SQL支持
  • 1996年：发布版本6.0作为第一个PostgreSQL版本
  • 2000年代：重大改进包括MVCC、表继承等
  • 2010年代：JSON支持、全文搜索增强、并行查询
  • 2020年代：持续创新，提供更好的性能和云集成

当前状态 ​

PostgreSQL是目前最先进的开源数据库之一，与Oracle和Microsoft SQL Server等专有解决方案竞争。它被各种规模的公司使用，从初创公司到大型企业。

为什么选择PostgreSQL? ​

优势 ​

1. 可靠性和数据完整性 ​

PostgreSQL的MVCC（多版本并发控制）实现确保读取者永远不会阻塞写者，反之亦然，在并发工作负载下提供出色的性能。

sql
-- 演示事务隔离的示例
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

2. 功能丰富 ​

  • 支持复杂数据类型（数组、JSON、几何类型）
  • 高级索引选项（B-tree、Hash、GiST、GIN等）
  • 全文搜索功能
  • 原生JSON支持
  • 窗口函数
  • 公用表表达式（CTE）
  • 物化视图
  • 部分索引
  • 用户定义函数

3. 性能 ​

PostgreSQL提供出色的性能特性：

  • 查询优化器使用基于成本的优化
  • 支持并行查询执行
  • 高效的索引机制
  • 连接池支持
  • 分区功能
sql
-- 带窗口函数的复杂查询示例
SELECT 
    department_id,
    employee_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

4. 可扩展性 ​

PostgreSQL可以通过多种方式扩展：

  • 自定义数据类型
  • 自定义函数
  • 自定义运算符
  • 扩展如PostGIS、pg_cron、pg_partman
sql
-- 创建自定义函数的示例
CREATE OR REPLACE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN amount * 0.08;
END;
$$ LANGUAGE plpgsql;

5. 社区和生态系统 ​

  • 活跃的开发社区
  • 全面的文档
  • 众多扩展和工具
  • 强大的企业支持选项

使用场景 ​

PostgreSQL适用于广泛的应用场景：

  1. Web应用程序 - 动态网站的强大后端
  2. 地理信息系统（GIS） - PostGIS扩展提供强大的空间功能
  3. 金融系统 - ACID合规性确保关键事务的数据完整性
  4. 科学研究 - 处理大型数据集和复杂计算
  5. 数据仓库 - 非常适合分析工作负载和报告
  6. 移动应用程序 - 具有出色并发支持的可靠后端

PostgreSQL与其他数据库的比较 ​

PostgreSQL与MySQL的比较 ​

功能PostgreSQLMySQL
ACID合规性完全部分
子查询优秀良好
JOIN性能优秀良好
JSON支持优秀良好
全文搜索原生插件
学习曲线中等简单
扩展生态系统广泛有限

PostgreSQL与Oracle的比较 ​

功能PostgreSQLOracle
成本免费昂贵
许可证开源专有
性能相当优秀
功能全面全面
社区庞大有限
云支持优秀优秀

PostgreSQL架构 ​

客户端-服务器模型 ​

PostgreSQL使用客户端-服务器架构，其中服务器进程管理数据库文件，接受来自客户端应用程序的连接，并执行数据库操作。

组件 ​

1. PostgreSQL服务器 ​

管理数据库文件和处理客户端连接的主要服务器进程。

2. 后端进程 ​

为每个客户端连接创建一个单独的进程，处理它们的特定请求。

3. 共享内存 ​

用于共享数据结构，如缓冲区缓存和事务日志。

4. 预写日志 ​

确保数据持久性并支持崩溃恢复。

5. 数据目录 ​

包含所有数据库文件、配置文件和事务日志。

存储结构 ​

PostgreSQL将数据存储在表空间中，每个表空间包含：

  • 表（堆文件）
  • 索引
  • 序列
  • 大对象

安装PostgreSQL ​

Windows安装 ​

  1. 从postgresql.org/download/windows下载安装程序
  2. 以管理员权限运行安装程序
  3. 按照安装向导操作
  4. 选择组件（pgAdmin、Stack Builder等）
  5. 为postgres用户设置密码
  6. 选择端口（默认：5432）
  7. 完成安装

macOS安装 ​

使用Homebrew：

bash
# 安装PostgreSQL
brew install postgresql

# 启动PostgreSQL服务
brew services start postgresql

# 创建数据库
createdb mydatabase

Linux安装 ​

Ubuntu/Debian：

bash
# 安装PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib

# 启动PostgreSQL服务
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 访问PostgreSQL提示符
sudo -u postgres psql

验证 ​

sql
-- 检查PostgreSQL版本
SELECT version();

-- 检查当前数据库
SELECT current_database();

-- 列出所有数据库
\l

PostgreSQL入门 ​

连接到PostgreSQL ​

使用psql命令行工具：

bash
# 连接到默认数据库
psql -U postgres

# 连接到特定数据库
psql -U postgres -d database_name

# 连接到远程服务器
psql -h hostname -U postgres -d database_name

您的第一个PostgreSQL命令 ​

sql
-- 创建数据库
CREATE DATABASE myapp;

-- 连接到数据库
\c myapp

-- 创建简单表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入一些数据
INSERT INTO users (name, email) VALUES 
    ('张三', 'zhangsan@example.com'),
    ('李四', 'lisi@example.com');

-- 查询数据
SELECT * FROM users;

-- 更新数据
UPDATE users SET email = 'zhangsan.new@example.com' WHERE name = '张三';

-- 删除数据
DELETE FROM users WHERE name = '李四';

常用术语和概念 ​

数据库 ​

用于存储相关数据和模式对象的容器。

模式 ​

数据库中的命名空间，包含表、视图、索引等数据库对象。

表 ​

按行和列组织的相关数据集合。

行 ​

表中的单条记录。

列 ​

单一数据类型的数据的垂直排列。

主键 ​

唯一标识每一行的一列或多列组合。

外键 ​

引用另一个表中主键的一列或多列组合。

索引 ​

提高数据检索操作速度的数据结构。

视图 ​

基于SQL查询结果的虚拟表。

事务 ​

作为单个逻辑工作单元执行的一系列操作。

小结 ​

PostgreSQL是一个功能强大、特性丰富且可靠的数据库管理系统。其开源性质、强大的社区支持和丰富的功能集使其成为各种应用的绝佳选择。

在本教程中，您将学习从基本数据库操作到索引、事务和性能优化等高级功能的所有内容。完成本教程后，您将具备有效设计、实现和管理PostgreSQL数据库的技能。