什么是PostgreSQL？

PostgreSQL是一个功能强大的开源对象关系型数据库系统，拥有超过35年的活跃开发历史。它以其可靠性、功能完整性和性能著称，被业界誉为”最先进的开源数据库”。

核心特性

1. 标准兼容性

  • 完全符合SQL标准
  • 支持ACID事务
  • 支持复杂查询和子查询

2. 扩展性

  • 支持自定义数据类型
  • 支持自定义函数和操作符
  • 支持存储过程和触发器

3. 高级功能

  • JSON和JSONB支持
  • 全文搜索
  • 地理空间数据支持（PostGIS）
  • 分区表
  • 并行查询

4. 可靠性

  • 多版本并发控制（MVCC）
  • 时间点恢复（PITR）
  • 在线备份
  • 流复制

数据类型

1. 基本数据类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 数值类型
SMALLINT      -- 2字节整数
INTEGER       -- 4字节整数
BIGINT        -- 8字节整数
DECIMAL       -- 精确数值
REAL          -- 4字节浮点数
DOUBLE PRECISION -- 8字节浮点数

-- 字符类型
CHAR(n)       -- 定长字符串
VARCHAR(n)    -- 变长字符串
TEXT          -- 不限长度文本

-- 日期时间类型
DATE          -- 日期
TIME          -- 时间
TIMESTAMP     -- 日期和时间
INTERVAL      -- 时间间隔

2. 高级数据类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- JSON类型
JSON          -- JSON文本
JSONB         -- 二进制JSON，支持索引

-- 数组类型
INTEGER[]     -- 整数数组
TEXT[]        -- 文本数组

-- 几何类型
POINT         -- 点
LINE          -- 线
POLYGON       -- 多边形

-- 网络地址类型
INET          -- IPv4/IPv6地址
CIDR          -- 网络地址
MACADDR       -- MAC地址

基本操作

1. 数据库操作

1
2
3
4
5
6
7
8
-- 创建数据库
CREATE DATABASE mydb WITH OWNER postgres;

-- 删除数据库
DROP DATABASE mydb;

-- 连接数据库
\c mydb

2. 表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INTEGER CHECK (age >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(150);

-- 创建索引
CREATE INDEX idx_users_username ON users(username);
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 删除表
DROP TABLE users;

3. 数据操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 插入数据
INSERT INTO users (username, email, age) 
VALUES ('john_doe', 'john@example.com', 30);

-- 批量插入
INSERT INTO users (username, email, age) 
VALUES 
    ('alice', 'alice@example.com', 25),
    ('bob', 'bob@example.com', 35);

-- 查询数据
SELECT * FROM users WHERE age > 25;
SELECT username, email FROM users ORDER BY created_at DESC LIMIT 10;

-- 更新数据
UPDATE users SET age = 31 WHERE username = 'john_doe';

-- 删除数据
DELETE FROM users WHERE age < 18;

高级查询

1. 连接查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 内连接
SELECT u.username, p.title, p.content
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

-- 左连接
SELECT u.username, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.username;

-- 全连接
SELECT * FROM table1
FULL OUTER JOIN table2 ON table1.id = table2.id;

2. 子查询

1
2
3
4
5
6
7
8
9
10
11
12
-- 标量子查询
SELECT username, 
       (SELECT COUNT(*) FROM posts WHERE user_id = users.id) as post_count
FROM users;

-- 行子查询
SELECT * FROM users
WHERE (age, username) IN (SELECT age, username FROM users_backup);

-- 存在子查询
SELECT * FROM users
WHERE EXISTS (SELECT 1 FROM posts WHERE user_id = users.id);

3. 窗口函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 排名函数
SELECT username, age,
       RANK() OVER (ORDER BY age DESC) as age_rank,
       DENSE_RANK() OVER (ORDER BY age DESC) as dense_rank
FROM users;

-- 聚合窗口函数
SELECT username, age,
       AVG(age) OVER () as avg_age,
       SUM(age) OVER (ORDER BY id) as cumulative_age
FROM users;

-- 分区窗口函数
SELECT username, age, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY age) as dept_rank
FROM users;

JSON操作

1. JSON数据类型

1
2
3
4
5
6
7
8
9
10
-- 创建包含JSON的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB
);

-- 插入JSON数据
INSERT INTO products (name, attributes) 
VALUES ('Laptop', '{"brand": "Dell", "specs": {"cpu": "i7", "ram": "16GB"}}');

2. JSON查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查询JSON字段
SELECT * FROM products 
WHERE attributes->>'brand' = 'Dell';

-- 查询嵌套JSON
SELECT * FROM products 
WHERE attributes->'specs'->>'cpu' = 'i7';

-- 使用JSON操作符
SELECT name, attributes->'specs'->>'ram' as ram
FROM products
WHERE attributes @> '{"brand": "Dell"}';

-- JSON数组查询
SELECT * FROM products
WHERE attributes->'tags' ? 'gaming';

3. JSON索引

1
2
3
4
5
-- 创建GIN索引
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- 创建特定路径的索引
CREATE INDEX idx_products_brand ON products ((attributes->>'brand'));

全文搜索

1. 创建全文索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    search_vector tsvector
);

-- 创建全文索引
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- 更新搜索向量
UPDATE articles 
SET search_vector = to_tsvector('english', title || ' ' || content);

2. 全文搜索查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 基本全文搜索
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'database & postgresql');

-- 排名搜索结果
SELECT title, 
       ts_rank(search_vector, to_tsquery('english', 'postgresql')) as rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql')
ORDER BY rank DESC;

-- 高亮搜索结果
SELECT title, 
       ts_headline('english', content, to_tsquery('postgresql')) as highlighted
FROM articles
WHERE search_vector @@ to_tsquery('postgresql');

存储过程

1. 创建存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建函数
CREATE OR REPLACE FUNCTION get_user_posts(user_id INTEGER)
RETURNS TABLE(post_title VARCHAR, post_date TIMESTAMP) AS $$
BEGIN
    RETURN QUERY
    SELECT p.title, p.created_at
    FROM posts p
    WHERE p.user_id = get_user_posts.user_id
    ORDER BY p.created_at DESC;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT * FROM get_user_posts(1);

2. 带参数的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 创建带参数的函数
CREATE OR REPLACE FUNCTION create_user(
    p_username VARCHAR,
    p_email VARCHAR,
    p_age INTEGER
) RETURNS INTEGER AS $$
DECLARE
    new_id INTEGER;
BEGIN
    INSERT INTO users (username, email, age)
    VALUES (p_username, p_email, p_age)
    RETURNING id INTO new_id;
    
    RETURN new_id;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT create_user('newuser', 'new@example.com', 25);

触发器

1. 创建触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

2. 审计触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 创建审计表
CREATE TABLE user_audit (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    action VARCHAR(10),
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建审计触发器
CREATE OR REPLACE FUNCTION audit_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO user_audit (user_id, action, old_data)
        VALUES (OLD.id, 'DELETE', row_to_json(OLD)::jsonb);
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO user_audit (user_id, action, old_data, new_data)
        VALUES (OLD.id, 'UPDATE', row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb);
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO user_audit (user_id, action, new_data)
        VALUES (NEW.id, 'INSERT', row_to_json(NEW)::jsonb);
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER user_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_user_changes();

视图

1. 创建视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 创建简单视图
CREATE VIEW active_users AS
SELECT id, username, email, age
FROM users
WHERE status = 'active';

-- 创建复杂视图
CREATE VIEW user_statistics AS
SELECT 
    u.id,
    u.username,
    u.email,
    COUNT(p.id) as post_count,
    MAX(p.created_at) as last_post_date
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username, u.email;

2. 物化视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建物化视图
CREATE MATERIALIZED VIEW monthly_stats AS
SELECT 
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as user_count,
    AVG(age) as avg_age
FROM users
GROUP BY DATE_TRUNC('month', created_at);

-- 刷新物化视图
REFRESH MATERIALIZED VIEW monthly_stats;

-- 创建索引
CREATE INDEX idx_monthly_stats_month ON monthly_stats (month);

分区表

1. 范围分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建分区表
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE,
    amount DECIMAL(10,2),
    customer_id INTEGER
) PARTITION BY RANGE (sale_date);

-- 创建分区
CREATE TABLE sales_2024_q1 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE sales_2024_q2 PARTITION OF sales
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

2. 列表分区

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建列表分区表
CREATE TABLE customers (
    id SERIAL,
    name VARCHAR(100),
    region VARCHAR(50)
) PARTITION BY LIST (region);

-- 创建分区
CREATE TABLE customers_north PARTITION OF customers
    FOR VALUES IN ('North', 'Northeast', 'Northwest');

CREATE TABLE customers_south PARTITION OF customers
    FOR VALUES IN ('South', 'Southeast', 'Southwest');

并发控制

1. 事务

1
2
3
4
5
6
7
8
9
10
11
12
-- 开始事务
BEGIN;

-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

2. 锁

1
2
3
4
5
6
7
8
9
10
-- 显式锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 共享锁
SELECT * FROM users WHERE id = 1 FOR SHARE;

-- 咨询锁
SELECT pg_advisory_lock(12345);
-- 执行操作
SELECT pg_advisory_unlock(12345);

性能优化

1. 索引优化

1
2
3
4
5
6
7
8
9
10
11
-- 创建复合索引
CREATE INDEX idx_users_name_age ON users(username, age);

-- 部分索引
CREATE INDEX idx_active_users ON users(username) WHERE status = 'active';

-- 表达式索引
CREATE INDEX idx_lower_username ON users (lower(username));

-- 覆盖索引
CREATE INDEX idx_users_covering ON users(username, email, age);

2. 查询优化

1
2
3
4
5
6
7
8
9
10
11
-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE username = 'john';

-- 使用EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

-- 使用CTE优化复杂查询
WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE age > 25;

3. 配置优化

1
2
3
4
5
6
7
8
9
10
-- 内存配置
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB

-- 并发配置
max_connections = 100
max_worker_processes = 8
max_parallel_workers = 8

备份与恢复

1. 逻辑备份

1
2
3
4
5
6
7
8
# 备份整个数据库
pg_dump mydb > backup.sql

# 备份特定表
pg_dump -t users -t posts mydb > backup.sql

# 压缩备份
pg_dump mydb | gzip > backup.sql.gz

2. 恢复

1
2
3
4
5
# 恢复数据库
psql mydb < backup.sql

# 从压缩文件恢复
gunzip -c backup.sql.gz | psql mydb

3. 物理备份

1
2
# 使用pg_basebackup
pg_basebackup -D /backup/data -Ft -z -P

复制

1. 主从复制

1
2
3
4
5
6
7
# 主服务器配置
wal_level = replica
max_wal_senders = 3
wal_keep_segments = 64

# 从服务器配置
primary_conninfo = 'host=master_ip port=5432 user=replicator password=secret'

2. 逻辑复制

1
2
3
4
5
6
7
-- 主服务器
CREATE PUBLICATION my_publication FOR TABLE users, posts;

-- 从服务器
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=master_ip port=5432 dbname=mydb user=replicator password=secret'
    PUBLICATION my_publication;

与Python集成

使用psycopg2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
import psycopg2
import psycopg2.extras

# 连接数据库
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="postgres",
    password="password"
)

# 创建游标
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)

# 执行查询
cur.execute("SELECT * FROM users WHERE age > %s", (25,))
users = cur.fetchall()

# 插入数据
cur.execute(
    "INSERT INTO users (username, email, age) VALUES (%s, %s, %s) RETURNING id",
    ("newuser", "new@example.com", 30)
)
user_id = cur.fetchone()['id']

# 提交事务
conn.commit()

# 关闭连接
cur.close()
conn.close()

使用SQLAlchemy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
from sqlalchemy import create_engine, Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 创建引擎
engine = create_engine('postgresql://postgres:password@localhost/mydb')

# 创建基类
Base = declarative_base()

# 定义模型
class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True)
    email = Column(String(100), unique=True)
    age = Column(Integer)
    created_at = Column(DateTime)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 查询
users = session.query(User).filter(User.age > 25).all()

# 添加
new_user = User(username='test', email='test@example.com', age=30)
session.add(new_user)
session.commit()

常见应用场景

  1. 企业级应用：ERP、CRM系统
  2. 金融系统：银行、保险、证券
  3. 电商平台：商品管理、订单系统
  4. 内容管理：CMS、博客系统
  5. 数据分析：数据仓库、报表系统

优缺点分析

优点

  • 功能完整，符合标准
  • 扩展性强
  • 性能优秀
  • 社区活跃
  • 开源免费

缺点

  • 配置复杂
  • 内存占用较高
  • 学习曲线陡峭
  • 某些操作性能不如MySQL

学习资源推荐

  • 官方文档：https://www.postgresql.org/docs/
  • 在线教程：PostgreSQL Tutorial
  • 书籍：《PostgreSQL实战》、《PostgreSQL技术内幕》
  • 实践项目：企业级CRM系统、数据分析平台

总结

PostgreSQL作为企业级关系型数据库，凭借其强大的功能、优秀的性能和良好的扩展性，成为企业级应用的首选。无论是复杂的事务处理、数据分析还是地理空间应用，PostgreSQL都能提供完整的解决方案。对于需要高可靠性和功能完整性的企业应用，PostgreSQL是最佳选择之一。