目录

1. PostgreSQL 是什么？

PostgreSQL 是一个开源的对象关系型数据库，具备传统关系型数据库的严谨结构，同时吸收了面向对象的编程思想，直接在数据库层面提供对象的支持。

  • 开源：完全免费，社区活跃。
  • 对象关系型：支持表、行、列等传统结构，同时支持对象、继承、复杂数据类型等高级功能。
  • 功能丰富：每年发布一个新版本，持续增强功能和兼容性。
  • 开发者首选：近年来在 Stack Overflow 调查中超越 MySQL，成为最受开发者欢迎的数据库。

2. PostgreSQL 与 MySQL 的对比

特性 PostgreSQL MySQL
SQL 标准兼容性 更高，支持更多 SQL 标准特性 相对较低
事务支持 支持完整的事务，包括 DDL 语句 仅部分支持
索引类型 支持部分索引、函数索引、GIN/GiST 等 仅支持基本索引
延迟约束 支持可延迟约束 不支持
面向对象支持 支持用户自定义类型、继承等 不支持

3. PostgreSQL 安装与配置

安装步骤（Ubuntu）

sudo apt update
sudo apt install postgresql postgresql-contrib

配置远程连接

  1. 修改 postgresql.conf：

    listen_addresses = '*'

  2. 修改 pg_hba.conf：

    host    all             all             0.0.0.0/0               md5

  3. 重启服务：

    sudo systemctl restart postgresql

使用 DBever 连接 PostgreSQL

  • 创建连接
  • 选择 PostgreSQL
  • 填写主机 IP、端口（默认 5432）
  • 输入用户名和密码
  • 成功连接后，可看到数据库、Schema、表三层结构

4. PostgreSQL 的数据模型与类型系统

内置数据类型

  • 时间类型：DATE, TIMESTAMP, TIMESTAMPTZ（带时区）
  • 网络类型：CIDR（网段）、INET（IP）、MACADDR（MAC 地址）
  • 几何类型：点、线段、矩形、路径、圆形等
  • 数组类型：支持多维数组
  • JSON 类型：JSON、JSONB（二进制 JSON）

自定义类型

CREATE TYPE InproE AS (
    name TEXT,
    age INT,
    skills TEXT[]
);

CREATE TABLE InproEase (
    id SERIAL PRIMARY KEY,
    data InproE
);

INSERT INTO InproEase (data)
VALUES (
    ('John', 30, ARRAY['Java', 'Python'])::InproE
);

表继承

CREATE TABLE Employee (
    id SERIAL PRIMARY KEY,
    name TEXT,
    salary NUMERIC
);

CREATE TABLE Developer () INHERITS (Employee);

ALTER TABLE Developer ADD COLUMN languages TEXT[];

INSERT INTO Developer (name, salary, languages)
VALUES ('Alice', 8000, ARRAY['Java', 'Go']);

5. JSON 与文档型能力

JSON 数据类型

  • JSON：原生 JSON 格式，存储时保留原始格式
  • JSONB：二进制 JSON，存储效率高，支持索引

插入 JSON 数据

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO logs (data)
VALUES (
    '{"ip": "192.168.1.1", "status": 200, "request": "/api/users"}'::JSONB
);

JSON 查询与索引

-- 查询
SELECT * FROM logs WHERE data->>'ip' = '192.168.1.1';

-- 创建索引
CREATE INDEX idx_ip ON logs ((data->>'ip'));
CREATE INDEX idx_jsonb_gin ON logs USING GIN (data);

6. 全文检索能力

使用 tsvector 实现全文检索

ALTER TABLE documents ADD COLUMN content_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;

CREATE INDEX idx_content_vector ON documents USING GIN(content_vector);

-- 查询
SELECT * FROM documents
WHERE content_vector @@ to_tsquery('powerful');

使用插件 pg_trgm 支持中文

CREATE EXTENSION pg_trgm;

-- 创建索引
CREATE INDEX idx_content_trgm ON documents USING GIN (content gin_trgm_ops);

-- 查询
SELECT * FROM documents WHERE content % '引擎';

7. 向量数据库能力（RAG 系统）

安装 pgvector 插件

sudo apt install postgresql-vector-16

使用向量数据库

CREATE EXTENSION vector;

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    embedding VECTOR(1024)
);

INSERT INTO items (embedding)
VALUES ('[1,2,3]'), ('[4,5,6]');

-- 查询相似向量
SELECT * FROM items
ORDER BY embedding <-> '[1,2,3]'
LIMIT 1;

搭建 RAG 系统（Python 示例）

from langchain_postgres import PGVector
from langchain_openai import OpenAIEmbeddings

embeddings = OpenAIEmbeddings(model="text-embedding-ada-002")

vectorstore = PGVector(
    connection_string="postgresql://user:password@host:5432/db",
    collection_name="docs"
)

# 添加文档
vectorstore.add_texts(["猫在吃糖里", "鸭子在吃糖里", "新鲜的苹果在市场里"])

# 查询
results = vectorstore.similarity_search_with_score("可爱动物在水里", k=10)

8. 定时任务与缓存能力

使用 pg_cron 创建定时任务

CREATE EXTENSION pg_cron;

-- 每天凌晨 3 点执行
SELECT cron.schedule('0 3 * * *', $$
    INSERT INTO logs_archive SELECT * FROM logs;
    DELETE FROM logs;
$$);

-- 修改任务
SELECT cron.unschedule('job_name');
SELECT cron.schedule('job_name', '*/1 * * * *', $$ ... $$);

使用 UNLOGGED 表实现缓存

CREATE UNLOGGED TABLE cache (
    key TEXT PRIMARY KEY,
    value TEXT
);

-- 调整共享缓存
SET LOCAL statement_timeout = '30s';
SET LOCAL shared_buffers = '2GB';

9. RESTful API 与 GraphQL

使用 PostgREST 生成 REST API

# 下载并解压 PostgREST
./postgrest --config config.conf

配置文件 config.conf：

db-uri = "postgres://user:pass@localhost:5432/db"
db-schema = "public"
db-anon-role = "anonymous"

使用 GraphiQL 生成 GraphQL API

CREATE EXTENSION graphqldb;

-- 查询
query {
  allItems {
    nodes {
      id
      embedding
    }
  }
}

10. 图数据库与时间序列数据库能力

图数据库（使用 AGE）

CREATE EXTENSION age;
LOAD 'age';

SELECT create_graph('social');

-- 创建节点
SELECT * FROM cypher('social', $$
    CREATE (:Person {name: 'Alice'})
$$);

-- 创建关系
SELECT * FROM cypher('social', $$
    MATCH (a:Person {name: 'Alice'}), (b:Person {name: 'Bob'})
    CREATE (a)-[:FRIEND]->(b)
$$);

时间序列数据库（使用 TimescaleDB）

CREATE EXTENSION timescaledb;

-- 创建 hypertable
SELECT create_hypertable('sensor_data', 'time');

-- 插入时间序列数据
INSERT INTO sensor_data (time, value)
VALUES ('2023-01-01 00:00:00', 100), ('2023-01-01 00:01:00', 101);

11. 总结

PostgreSQL 不再是一个简单的 SQL 数据库，它已经演变为一个功能强大、高度可扩展的后端聚合平台。其优势包括：

  • 对象关系模型：支持继承、自定义类型等高级特性。
  • 文档型能力：通过 JSONB 实现灵活数据结构。
  • 全文检索：内置 tsvector 和插件支持中文搜索。
  • 向量数据库：结合 pgvector 支持 RAG 系统。
  • 定时任务与缓存：使用 pg_cron 和 UNLOGGED 表。
  • API 生成：通过 PostgREST 快速构建 RESTful API。
  • 图与时间序列扩展：集成 AGE 和 TimescaleDB。

更详细的PostgreSQL教程请参考 菜鸟教程