PostgreSQL是被誉为"世界上最先进的开源数据库"的关系数据库管理系统——Instagram、Shopify、Discord和全球数千家初创公司的首选。本文介绍PostgreSQL是什么、其架构如何运作，以及如何用Python和Node.js实际连接使用。
需要企业数据解决方案？
自 2019 年起，AlgoData 为企业提供数据工程、分析与 AI 解决方案。
PostgreSQL是什么？
PostgreSQL（发音为"post-gres-Q-L"或简称"postgres"）是一款开源关系数据库管理系统（RDBMS），起源于1986年加州大学伯克利分校Michael Stonebraker教授主导的POSTGRES项目。1996年该项目更名为PostgreSQL，发展成为独立的开源社区。
PostgreSQL的突出之处在于一种罕见的结合：严格遵循SQL标准的同时，支持JSON/JSONB、数组、自定义数据类型，以及超过100个强大的扩展，例如PostGIS（地理空间数据）和pgvector（AI向量嵌入）。
PostgreSQL架构
当应用程序连接到PostgreSQL时，处理流程如下：
- 客户端向Postmaster（主管理进程）发送连接请求
- Postmaster为每个连接派生专属的后端进程
- 后端通过共享缓冲区（共享内存缓存）进行读写
- 数据持久化到磁盘上的数据文件
PostgreSQL使用每连接一个进程的模型（每个客户端对应一个OS进程），与MySQL使用线程的方式不同。这也是为什么在扩展到数千个并发连接时，PgBouncer等连接池工具变得至关重要。
ACID与MVCC
PostgreSQL通过MVCC（多版本并发控制）机制保证完整的ACID合规性：更新一行时，Postgres不是覆盖而是创建新版本——读取者看到旧版本，写入者创建新版本。结果：读不阻塞写，写不阻塞读。
用Python和Node.js连接PostgreSQL
Python使用psycopg2：
1import psycopg2
2
3conn = psycopg2.connect(
4 host="localhost",
5 port=5432,
6 dbname="mydb",
7 user="postgres",
8 password="secret"
9)
10cur = conn.cursor()
11
12# 使用参数化查询插入数据（防止SQL注入）
13cur.execute(
14 "INSERT INTO users (name, email) VALUES (%s, %s)",
15 ("Alice", "alice@example.com")
16)
17conn.commit()
18
19# 查询
20cur.execute("SELECT id, name FROM users WHERE active = %s", (True,))
21rows = cur.fetchall()
22for row in rows:
23 print(row) # (1, 'Alice')
24
25cur.close()
26conn.close()
Node.js使用pg：
1const { Pool } = require('pg');
2
3const pool = new Pool({
4 host: 'localhost',
5 port: 5432,
6 database: 'mydb',
7 user: 'postgres',
8 password: 'secret',
9});
10
11async function getActiveUsers() {
12 const result = await pool.query(
13 'SELECT id, name FROM users WHERE active = $1',
14 [true]
15 );
16 return result.rows; // [{ id: 1, name: 'Alice' }]
17}
psql命令行基础命令：
1psql -h localhost -U postgres -d mydb
2
3# 在psql中
4\dt -- 列出所有表
5\d users -- 查看users表结构
6\timing on -- 开启查询计时
PostgreSQL中的索引
PostgreSQL支持多种索引类型，最常用的：
| 类型 | 使用场景 | 示例 |
|---|---|---|
| B-tree | 默认，用于=、<、>、BETWEEN |
CREATE INDEX ON users(email) |
| GIN | 搜索JSONB、数组、全文索引 | CREATE INDEX ON posts USING GIN(tags) |
| 部分索引 | 只对行的子集建立索引 | CREATE INDEX ON orders(created_at) WHERE status = 'pending' |
1-- email上的B-tree索引
2CREATE INDEX idx_users_email ON users(email);
3
4-- 用于查询JSONB的GIN索引
5CREATE INDEX idx_products_meta ON products USING GIN(metadata);
6
7-- 未处理订单的部分索引
8CREATE INDEX idx_pending_orders ON orders(created_at)
9WHERE status = 'pending';
PostgreSQL vs MySQL — 何时选择哪个？
| 标准 | PostgreSQL | MySQL |
|---|---|---|
| JSON支持 | 带索引的JSONB | 基本JSON |
| 全文搜索 | 内置 | 有限 |
| 数据类型 | 丰富（数组、hstore、范围类型） | 基本 |
| 复制 | 逻辑复制+流复制 | Binary log |
| 许可证 | PostgreSQL（免费，商业可用） | GPL |
选择PostgreSQL：需要复杂查询、严格ACID合规或JSON混合方案时。选择MySQL：使用WordPress/LAMP技术栈或需要更广泛的PHP生态系统时。
实际应用案例
- Instagram：使用PostgreSQL管理整个社交图谱和媒体元数据
- Shopify：PostgreSQL作为数百万商家的主数据存储
- Stripe：PostgreSQL作为处理数十亿美元交易的支付系统的主数据库
