纲要
PostgreSQL市场趋势与排名DB-Engines全球数据库排名分析Stack Overflow2025 开发者调查数据
- AI时代数据库角色的转变
- 从存储查询到一体化数据底座
- 多数据形态与多业务场景的支持
PostgreSQL核心能力与扩展生态- 关系数据库核心能力:
SQL、事务、MVCC、高可用 - 扩展生态:
PostGIS、全文检索、pgvector、Apache AGE、SQL/PGQ - 安全治理:权限、审计、行级安全
- 关系数据库核心能力:
- 学习路径与实践方向
- 环境搭建与基础操作
- 核心原理:
WAL、事务、索引、执行计划 - 实战场景:索引优化、慢查询诊断、备份恢复、高可用
- 课程定位与目标人群
PostgreSQL 的市场趋势
在数据库技术领域,DB-Engines 排名是衡量数据库流行度的重要指标。该排名综合了搜索趋势、技术问答、岗位需求、社交信号等多个维度的数据,每月更新一次。根据 2026 年 7 月的最新排名,PostgreSQL 已位列全球数据库总榜第四名,仅次于 Oracle、MySQL 和 Microsoft SQL Server。近年来,PostgreSQL 与第三名 SQL Server 的差距持续缩小,其生态势能、扩展能力以及开发者心智均在不断增强。
Stack Overflow 2025 年度开发者调查进一步印证了这一趋势。在该调查中,PostgreSQL 以 55.6% 的采用率成为全球开发者社区中使用最广泛的数据库。在专业开发者群体中,PostgreSQL 的使用比例达到 49.09%,超越了 MySQL(40.59%)。更值得注意的是,在使用 AI 的专业开发者中,PostgreSQL 的采用率高达 59.5%,位列所有数据库之首。PostgreSQL 已连续第三年成为“最受欢迎”和“最受喜爱”的数据库。
上述数据表明,PostgreSQL 已不再是传统认知中的小众开源数据库,而是正在成为全球开发者和企业广泛选择的核心数据基础设施。Oracle、MySQL、SQL Server 三足鼎立的传统格局正在被 PostgreSQL 的崛起所改写。
AI时代数据库角色的转变
数据库的角色正在经历深刻的变化。在传统应用场景中,数据库的核心职责是存储、查询、事务与稳定性——确保数据能够被正确地写入,在需要时能够被准确地读出,并保证数据的一致性。
然而,在 AI 时代,应用需要处理的数据形态远不止于传统的业务数据:
- 文档数据:非结构化的文本内容
- 向量数据:嵌入模型生成的向量表征
- 全文检索:对大量文本进行高效的搜索与匹配
- 空间信息:地理位置与空间关系数据
- 权限与审计:细粒度的安全治理与合规审计
AI 应用对数据库提出了更为严苛的要求。它所需要的不仅仅是一个数据库,而是一套能够支撑多种数据形态、多种查询方式、多种业务场景的一体化数据底座。
PostgreSQL 的核心能力与扩展生态
PostgreSQL 在这一背景下展现出独特的优势。它既是一款成熟稳定的关系型数据库,又具备极其强大的扩展能力,堪称 AI 时代的“六边形战士”。
关系数据库核心能力
作为一款拥有数十年历史的关系型数据库,PostgreSQL 具备完备的核心能力:
- SQL 标准支持:高度兼容 SQL 标准,支持复杂的查询语法
- 事务机制:完整的
ACID事务保证 - 索引:支持
B-tree、Hash、GiST、SP-GiST、GIN、BRIN等多种索引类型 - 约束:主键、外键、唯一约束、检查约束等
- MVCC:多版本并发控制,实现高并发读写
- 高可用:支持流复制、逻辑复制、故障转移等机制
扩展生态
PostgreSQL 的真正强大之处在于其扩展生态。通过扩展机制,PostgreSQL 可以在单一数据库中支持多种数据形态:
| 数据形态 | 扩展/能力 | 说明 |
|---|---|---|
| 空间数据 | PostGIS | 业界领先的地理空间扩展,支持空间索引与查询 |
| 全文检索 | 内置 tsvector/tsquery、pgsearch | 支持全文搜索与 BM25 相关度算法 |
| 向量检索 | pgvector | 支持向量存储与近似最近邻搜索,是 RAG 应用的首选 |
| 图检索 | Apache AGE、PostgreSQL 19 SQL/PGQ | 支持属性图查询与图模式匹配 |
| 时序数据 | TimescaleDB | 针对时间序列数据优化的扩展 |
在向量检索领域,pgvector 已成为 PostgreSQL 生态中的核心组件。它使得团队无需单独部署向量数据库即可在 PostgreSQL 中完成向量的存储与检索。围绕 pgvector,已形成包括 pgai、pg_vectorize 等在内的完整工具链。
在图检索方面,PostgreSQL 19 引入了对 SQL/PGQ(SQL Property Graph Queries)的原生支持。通过 CREATE PROPERTY GRAPH 和 GRAPH_TABLE 语法,开发者可以直接在关系型表上定义属性图并执行图查询,无需额外同步数据到独立的图数据库。
安全治理
PostgreSQL 还提供了一套完整的安全治理机制:
- 权限管理:基于角色的访问控制(
RBAC) - 审计:通过
pgAudit等扩展实现操作审计 - 行级安全:
RLS(Row Level Security)实现细粒度的行级访问控制 - 列级权限:对敏感列进行单独的权限控制
一体化数据底座的价值
PostgreSQL 的核心价值在于,它能够将结构化数据、全文检索、向量搜索、空间数据、图数据、安全治理和高可用等能力整合在同一个数据库中。
对于 AI 项目而言,落地的难点往往不在于模型本身,而在于:
- 数据如何进入系统
- 进入后如何进行多维度的检索与关联
- 如何保证数据的安全与合规
- 如何确保系统的稳定运行
PostgreSQL 在这些环节中均能发挥关键作用,成为 AI 应用的数据基础设施。
学习路径与实践方向
系统掌握 PostgreSQL 需要遵循一条从基础到原理、从原理到实践的学习路径。
环境搭建与基础操作
学习的第一步是完成 PostgreSQL 的部署与安装,理解配套工具的使用、常见参数配置以及目录结构。以下是基于 Ubuntu/Debian 的快速安装示例:
# 安装 PostgreSQL(以 Ubuntu 22.04 为例)
sudo apt update
sudo apt install postgresql postgresql-contrib
# 查看服务状态
sudo systemctl status postgresql
# 切换到 postgres 用户
sudo -i -u postgres
# 进入 psql 命令行
psql
# 查看版本
SELECT version();
-- 创建测试数据库
CREATE DATABASE testdb;
-- 连接到测试数据库
\c testdb
-- 创建表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入数据
INSERT INTO users (username) VALUES ('alice'), ('bob'), ('charlie');
-- 查询数据
SELECT * FROM users;
核心原理
在掌握基础操作后,需要深入理解 PostgreSQL 的核心运行原理:
- WAL(Write-Ahead Logging) :预写式日志机制,保证数据持久性与崩溃恢复能力
- 事务机制:
ACID特性的实现原理,隔离级别的含义与影响 - 索引原理:不同索引类型的数据结构与适用场景
- 执行计划分析:使用
EXPLAIN和EXPLAIN ANALYZE分析查询性能
-- 查看执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'alice';
-- 创建索引
CREATE INDEX idx_users_username ON users(username);
-- 再次查看执行计划,对比索引前后的变化
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'alice';
实战场景
实战场景是理论知识的最终落脚点,包括但不限于:
- 索引优化:根据查询模式设计合理的索引策略
- 慢查询诊断:通过
pg_stat_statements等工具定位性能瓶颈 - 备份恢复:
pg_dump、pg_basebackup的使用与恢复演练 - 高可用架构:流复制、
Patroni等高可用方案的部署与管理
-- 启用 pg_stat_statements 扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 查看最耗时的查询
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
入门技巧
一门系统性的 PostgreSQL 学习路径应当具备以下特征:
- 完整性:从环境搭建到架构原理,从事务索引到高可用,形成从入门到进阶的完整学习路径
- 实践驱动:关键能力配合实际的操作命令、案例与演示,确保学习者不仅“知道怎么做”,更“理解为什么这么做”
- 面向趋势:将向量检索、图查询等 AI 时代的前沿能力纳入主线,使学习者既掌握传统
PostgreSQL,也能理解其如何承接 AI 应用
以下三类人群适合快速入门:
- 具备基础计算机操作能力,希望在 AI 时代掌握
PostgreSQL的学习者 - 从事数据库开发、运维、调优的技术人员
- AI 应用开发者,需要理解向量数据库原理及智能问答背后的数据能力
API 速览
本节梳理 PostgreSQL 学习与使用过程中涉及的核心 API 与命令。
psql 元命令
psql 是 PostgreSQL 的交互式命令行工具,以下为常用元命令:
| 命令 | 说明 | 示例 |
|---|---|---|
\l | 列出所有数据库 | \l |
\c | 连接到指定数据库 | \c database_name |
\dt | 列出当前数据库的所有表 | \dt |
\d | 查看表结构 | \d table_name |
\du | 列出所有角色/用户 | \du |
\dx | 列出已安装的扩展 | \dx |
\q | 退出 psql | \q |
SQL 核心 DDL/DML
-- 创建数据库
CREATE DATABASE database_name;
-- 创建表
CREATE TABLE table_name (
column1 datatype CONSTRAINT,
column2 datatype,
...
);
-- 创建索引
CREATE INDEX index_name ON table_name (column_name);
CREATE INDEX idx_gin ON table_name USING GIN (column_name);
-- 创建扩展
CREATE EXTENSION extension_name;
-- 查询
SELECT columns FROM table_name WHERE condition ORDER BY column;
-- 执行计划分析
EXPLAIN [ANALYZE] [VERBOSE] SELECT ...;
备份与恢复命令
# 逻辑备份(pg_dump)
pg_dump -U username -d dbname -f backup.sql
# 逻辑恢复
psql -U username -d dbname < backup.sql
# 物理备份(pg_basebackup)
pg_basebackup -D /path/to/backup -Fp -P -U replication_user -h host -p port
高可用与复制
-- 查看复制状态
SELECT * FROM pg_stat_replication;
-- 查看 WAL 日志位置
SELECT pg_current_wal_lsn();
-- 创建发布(逻辑复制)
CREATE PUBLICATION pub_name FOR TABLE table_name;
-- 创建订阅
CREATE SUBSCRIPTION sub_name CONNECTION 'conninfo' PUBLICATION pub_name;
Demo 简单示例
以下是一个完整的 Node.js 示例,演示使用 pg 库连接 PostgreSQL,执行建表、插入、查询和索引优化操作。
运行说明
- 确保本地已安装
PostgreSQL并运行 - 创建测试数据库:
CREATE DATABASE demo_db; - 安装依赖:
npm install pg - 运行脚本:
node demo.js
代码示例
const { Client } = require('pg');
// 数据库连接配置
const config = {
host: 'localhost',
port: 5432,
database: 'demo_db',
user: 'postgres',
password: 'your_password',
};
const client = new Client(config);
async function runDemo() {
try {
await client.connect();
console.log('✅ 连接 PostgreSQL 成功');
// 1. 创建表
await client.query(`
CREATE TABLE IF NOT EXISTS products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price DECIMAL(10, 2),
stock INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`);
console.log('✅ 表创建成功');
// 2. 批量插入测试数据
const insertQuery = `
INSERT INTO products (name, category, price, stock)
VALUES ($1, $2, $3, $4)
`;
const testData = [
['Laptop Pro', 'Electronics', 1299.99, 50],
['Wireless Mouse', 'Electronics', 29.99, 200],
['Desk Chair', 'Furniture', 249.00, 30],
['Coffee Mug', 'Kitchen', 12.50, 500],
['Monitor 27"', 'Electronics', 349.00, 75],
];
for (const data of testData) {
await client.query(insertQuery, data);
}
console.log('✅ 测试数据插入成功');
// 3. 查询:无索引时的执行计划
console.log('\n📊 无索引查询计划:');
const explainResult = await client.query(
'EXPLAIN ANALYZE SELECT * FROM products WHERE category = $1',
['Electronics']
);
console.log(explainResult.rows.map(r => r['QUERY PLAN']).join('\n'));
// 4. 创建索引
await client.query('CREATE INDEX IF NOT EXISTS idx_products_category ON products(category)');
console.log('✅ 索引创建成功');
// 5. 查询:有索引后的执行计划
console.log('\n📊 有索引查询计划:');
const explainResultIndexed = await client.query(
'EXPLAIN ANALYZE SELECT * FROM products WHERE category = $1',
['Electronics']
);
console.log(explainResultIndexed.rows.map(r => r['QUERY PLAN']).join('\n'));
// 6. 聚合查询
const stats = await client.query(`
SELECT
category,
COUNT(*) as count,
AVG(price) as avg_price,
SUM(stock) as total_stock
FROM products
GROUP BY category
`);
console.log('\n📈 分类统计:');
console.table(stats.rows);
// 7. 使用 pg_stat_statements 查看查询统计(需预先启用扩展)
const statResult = await client.query(`
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
WHERE query LIKE '%products%'
ORDER BY total_time DESC
LIMIT 5
`);
if (statResult.rows.length > 0) {
console.log('\n📊 查询统计(pg_stat_statements):');
console.table(statResult.rows);
}
} catch (err) {
console.error('❌ 错误:', err);
} finally {
await client.end();
}
}
runDemo();
技术点总结
- 连接管理:使用
pg库的Client进行数据库连接与生命周期管理 - 参数化查询:使用
$1、$2占位符防止 SQL 注入 - 执行计划分析:通过
EXPLAIN ANALYZE观察索引对查询性能的影响 - 索引优化:演示
B-tree索引的创建与效果 - 性能监控:使用
pg_stat_statements进行查询性能统计
多语言示例
Go
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/lib/pq"
)
func main() {
connStr := "user=postgres password=your_password dbname=demo_db host=localhost port=5432 sslmode=disable"
db, err := sql.Open("postgres", connStr)
if err != nil {
log.Fatal(err)
}
defer db.Close()
// 建表
_, err = db.Exec(`
CREATE TABLE IF NOT EXISTS logs (
id SERIAL PRIMARY KEY,
level VARCHAR(20),
message TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`)
if err != nil {
log.Fatal(err)
}
// 插入
_, err = db.Exec(
"INSERT INTO logs (level, message) VALUES ($1, $2)",
"INFO", "Service started successfully",
)
if err != nil {
log.Fatal(err)
}
// 查询
rows, err := db.Query("SELECT id, level, message, created_at FROM logs ORDER BY id DESC LIMIT 10")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var id int
var level, message string
var createdAt string
rows.Scan(&id, &level, &message, &createdAt)
fmt.Printf("[%s] %s: %s\n", createdAt, level, message)
}
}
Python
import psycopg2
from psycopg2.extras import RealDictCursor
conn = psycopg2.connect(
host="localhost",
port=5432,
database="demo_db",
user="postgres",
password="your_password"
)
cur = conn.cursor(cursor_factory=RealDictCursor)
# 建表
cur.execute("""
CREATE TABLE IF NOT EXISTS events (
id SERIAL PRIMARY KEY,
event_type VARCHAR(50),
payload JSONB,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""")
# 插入
cur.execute(
"INSERT INTO events (event_type, payload) VALUES (%s, %s)",
("user_login", {"user_id": 1001, "ip": "192.168.1.1"})
)
conn.commit()
# 查询
cur.execute("SELECT * FROM events ORDER BY id DESC LIMIT 10")
for row in cur.fetchall():
print(row)
cur.close()
conn.close()
Java
import java.sql.*;
import java.util.Properties;
public class PostgresDemo {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/demo_db";
Properties props = new Properties();
props.setProperty("user", "postgres");
props.setProperty("password", "your_password");
try (Connection conn = DriverManager.getConnection(url, props);
Statement stmt = conn.createStatement()) {
// 建表
stmt.execute("""
CREATE TABLE IF NOT EXISTS metrics (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
value DOUBLE PRECISION,
recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""");
// 插入
PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO metrics (name, value) VALUES (?, ?)"
);
pstmt.setString(1, "cpu_usage");
pstmt.setDouble(2, 45.6);
pstmt.executeUpdate();
// 查询
ResultSet rs = stmt.executeQuery(
"SELECT * FROM metrics ORDER BY id DESC LIMIT 10"
);
while (rs.next()) {
System.out.printf("id=%d, name=%s, value=%.2f, at=%s%n",
rs.getInt("id"),
rs.getString("name"),
rs.getDouble("value"),
rs.getTimestamp("recorded_at")
);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
项目难点与解决方案
核心难点
- 多数据形态的统一管理:在同一个数据库中同时处理结构化数据、向量、全文检索、空间数据和图数据,需要理解不同扩展的适用场景与性能特征
- 性能调优的复杂性:
PostgreSQL的查询优化器、索引选择、WAL配置、内存参数(如shared_buffers、work_mem)之间存在复杂的相互影响,调优需要系统性的知识 - 高可用架构的搭建:流复制、逻辑复制、故障转移等机制的配置与运维需要深入理解
PostgreSQL的复制原理
解决方案
- 分层学习:从基础操作到核心原理,再到实战场景,逐层递进
- 实践驱动:每个知识点配合实际操作与案例演示,确保理解与落地
- 工具辅助:利用
pg_stat_statements、EXPLAIN、pgBadger等工具进行性能诊断与监控
广度
涵盖 PostgreSQL 的安装部署、核心原理(WAL、事务、索引、执行计划)、扩展生态(PostGIS、pgvector、全文检索、图查询)、安全治理、高可用等完整知识体系。
深度
深入 PostgreSQL 内核层面的工作机制,包括 MVCC 的实现、索引的内部结构、查询优化器的决策逻辑、WAL 的写入与恢复流程等。
复杂度
涉及单机部署、主从复制、逻辑复制、扩展安装与配置、性能调优等多个维度,需要综合运用系统运维、数据库原理、应用开发等多方面技能。
官方文档
- PostgreSQL 官方文档:https://www.postgresql.org/docs/
- PostgreSQL Wiki:https://wiki.postgresql.org/
- pgvector 官方仓库:https://github.com/pgvector/pgvector
- PostGIS 官方文档:https://postgis.net/documentation/
- Apache AGE 官方文档:https://age.apache.org/
参考链接
- DB-Engines 数据库排名:https://db-engines.com/en/ranking
- Stack Overflow 2025 开发者调查:https://survey.stackoverflow.co/2025/
- PostgreSQL 19 Beta 发布公告:https://www.postgresql.org/about/news/postgresql-19-beta-1-released-3027/
- SQL/PGQ 属性图查询介绍:https://www.postgresql.org/docs/current/ddl-property-graphs.html
润色纠正的前后对比
| 原文(机器翻译) | 修正后 | 说明 |
|---|---|---|
| postsqueeze / posgreeze / poseez | PostgreSQL | 统一修正为正确的产品名称 |
| DBengines | DB-Engines | 修正为正确的产品名称格式 |
| circlese rver | Microsoft SQL Server | 修正为正确的产品名称 |
| ststackoflow | Stack Overflow | 修正为正确的产品名称 |
| popossejes | PostGIS | 修正为正确的扩展名称 |
| testspector | tsvector/tsquery | 修正为正确的全文检索技术术语 |
| PGactor | pgvector | 修正为正确的向量扩展名称 |
| diskAN | pgvector 的 DISKANN 索引 | 修正为正确的索引类型描述 |
| AGE | Apache AGE | 修正为正确的图扩展名称 |
| propertygraph | SQL/PGQ 属性图 | 修正为正确的技术术语 |
| MCCLWAR | MVCC | 修正为正确的并发控制术语 |
| WAR | WAL(Write-Ahead Logging) | 修正为正确的日志机制术语 |
| “休息大家好,我是CC” | 删除 | 去除口语化开场白 |
| “老油条” | 删除 | 去除口语化表达 |
| “这门课程我觉得会是非常适合的学习路径” | 精简为客观陈述 | 去除个人感受与课程推广语气 |
总结
PostgreSQL 凭借其在 DB-Engines 排名中位列全球第四的强劲势头,以及 Stack Overflow 2025 年调查中 55.6% 的开发者采用率(AI 专业开发者中高达 59.5% ),已确立其作为全球数据基础设施的核心地位。在 AI 时代,数据库的角色从单纯的存储查询工具转变为一套支撑多数据形态、多查询方式、多业务场景的一体化数据底座。
PostgreSQL 通过其完备的关系数据库核心能力(SQL、事务、MVCC、高可用)与强大的扩展生态(PostGIS、pgvector、全文检索、Apache AGE 及 PostgreSQL 19 的 SQL/PGQ 原生图查询),将结构化数据、向量检索、空间数据、图数据与安全治理整合于一身。
系统掌握 PostgreSQL 需要遵循从环境搭建到核心原理(WAL、事务、索引、执行计划)、再到实战优化(索引优化、慢查询诊断、备份恢复、高可用)的完整学习路径。这一能力体系对于数据库开发运维人员与 AI 应用开发者均具有重要的实践价值。
758



被折叠的 条评论
为什么被折叠?



