---

什么是PostgreSQL？

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

核心特性

1. 标准兼容性

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

2. 扩展性

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

3. 高级功能

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

4. 可靠性

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

数据类型

1. 基本数据类型

-- 数值类型
SMALLINT      -- 2字节整数
INTEGER       -- 4字节整数
BIGINT        -- 8字节整数
DECIMAL       -- 精确数值
REAL          -- 4字节浮点数
DOUBLE PRECISION -- 8字节浮点数-- 字符类型
CHAR(n)       -- 定长字符串
VARCHAR(n)    -- 变长字符串
TEXT          -- 不限长度文本-- 日期时间类型
DATE          -- 日期
TIME          -- 时间
TIMESTAMP     -- 日期和时间
INTERVAL      -- 时间间隔

2. 高级数据类型

-- JSON类型
JSON          -- JSON文本
JSONB         -- 二进制JSON，支持索引-- 数组类型
INTEGER[]     -- 整数数组
TEXT[]        -- 文本数组-- 几何类型
POINT         -- 点
LINE          -- 线
POLYGON       -- 多边形-- 网络地址类型
INET          -- IPv4/IPv6地址
CIDR          -- 网络地址
MACADDR       -- MAC地址

基本操作

1. 数据库操作

-- 创建数据库
CREATE DATABASE mydb WITH OWNER postgres;-- 删除数据库
DROP DATABASE mydb;-- 连接数据库
\c mydb

2. 表操作

-- 创建表
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. 数据操作

-- 插入数据
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. 连接查询

-- 内连接
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. 子查询

-- 标量子查询
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. 窗口函数

-- 排名函数
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数据类型

-- 创建包含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查询

-- 查询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索引

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

全文搜索

1. 创建全文索引

-- 创建表
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. 全文搜索查询

-- 基本全文搜索
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. 创建存储过程

-- 创建函数
CREATE OR REPLACE FUNCTION get_user_posts(user_id INTEGER)
RETURNS TABLE(post_title VARCHAR, post_date TIMESTAMP) AS $$
BEGINRETURN QUERYSELECT p.title, p.created_atFROM posts pWHERE p.user_id = get_user_posts.user_idORDER BY p.created_at DESC;
END;
$$ LANGUAGE plpgsql;-- 调用函数
SELECT * FROM get_user_posts(1);

2. 带参数的存储过程

-- 创建带参数的函数
CREATE OR REPLACE FUNCTION create_user(p_username VARCHAR,p_email VARCHAR,p_age INTEGER
) RETURNS INTEGER AS $$
DECLAREnew_id INTEGER;
BEGININSERT 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. 创建触发器

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGINNEW.updated_at = CURRENT_TIMESTAMP;RETURN NEW;
END;
$$ LANGUAGE plpgsql;-- 创建触发器
CREATE TRIGGER update_users_updated_atBEFORE UPDATE ON usersFOR EACH ROWEXECUTE FUNCTION update_updated_at();

2. 审计触发器

-- 创建审计表
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 $$
BEGINIF TG_OP = 'DELETE' THENINSERT INTO user_audit (user_id, action, old_data)VALUES (OLD.id, 'DELETE', row_to_json(OLD)::jsonb);RETURN OLD;ELSIF TG_OP = 'UPDATE' THENINSERT 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' THENINSERT 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_triggerAFTER INSERT OR UPDATE OR DELETE ON usersFOR EACH ROWEXECUTE FUNCTION audit_user_changes();

视图

1. 创建视图

-- 创建简单视图
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. 物化视图

-- 创建物化视图
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. 范围分区

-- 创建分区表
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 salesFOR VALUES FROM ('2024-01-01') TO ('2024-04-01');CREATE TABLE sales_2024_q2 PARTITION OF salesFOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

2. 列表分区

-- 创建列表分区表
CREATE TABLE customers (id SERIAL,name VARCHAR(100),region VARCHAR(50)
) PARTITION BY LIST (region);-- 创建分区
CREATE TABLE customers_north PARTITION OF customersFOR VALUES IN ('North', 'Northeast', 'Northwest');CREATE TABLE customers_south PARTITION OF customersFOR VALUES IN ('South', 'Southeast', 'Southwest');

并发控制

1. 事务

-- 开始事务
BEGIN;-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;-- 提交事务
COMMIT;-- 回滚事务
ROLLBACK;

2. 锁

-- 显式锁
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. 索引优化

-- 创建复合索引
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. 查询优化

-- 使用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. 配置优化

-- 内存配置
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. 逻辑备份

# 备份整个数据库
pg_dump mydb > backup.sql# 备份特定表
pg_dump -t users -t posts mydb > backup.sql# 压缩备份
pg_dump mydb | gzip > backup.sql.gz

2. 恢复

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

3. 物理备份

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

复制

1. 主从复制

# 主服务器配置
wal_level = replica
max_wal_senders = 3
wal_keep_segments = 64# 从服务器配置
primary_conninfo = 'host=master_ip port=5432 user=replicator password=secret'

2. 逻辑复制

-- 主服务器
CREATE PUBLICATION my_publication FOR TABLE users, posts;-- 从服务器
CREATE SUBSCRIPTION my_subscriptionCONNECTION 'host=master_ip port=5432 dbname=mydb user=replicator password=secret'PUBLICATION my_publication;

与Python集成

使用psycopg2

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

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是最佳选择之一。