PostgreSQL
PostgreSQL 是一个功能强大的开源对象关系数据库系统，具有强大的数据完整性和扩展性。
特点
- 完全支持 ACID
- 支持复杂查询和高级特性
- 丰富的数据类型（JSON、数组、地理信息等）
- 强大的索引功能
- 支持并发控制
安装
macOS (使用 Homebrew)
brew install postgresql@14
brew services start postgresql@14
基本命令
# 创建数据库
createdb mydb
# 连接数据库
psql mydb
# 查看数据库列表
\l
# 查看表列表
\dt
# 查看表结构
\d table_name
常用 SQL 示例
1. 表操作
-- 创建表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
profile JSONB
);
-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_profile ON users USING GIN (profile);
-- 添加列
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
-- 删除表
DROP TABLE IF EXISTS users;
2. 数据操作
-- 插入数据
INSERT INTO users (username, email, profile)
VALUES
('john_doe', 'john@example.com', '{"age": 30, "city": "New York"}'),
('jane_doe', 'jane@example.com', '{"age": 25, "city": "London"}');
-- 更新数据
UPDATE users
SET profile = profile || '{"verified": true}'::jsonb
WHERE username = 'john_doe';
-- 删除数据
DELETE FROM users WHERE username = 'john_doe';
3. 高级查询
-- JSON 查询
SELECT * FROM users
WHERE profile->>'city' = 'New York';
-- 数组操作
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title TEXT,
tags TEXT[]
);
INSERT INTO posts (title, tags)
VALUES ('PostgreSQL Guide', ARRAY['database', 'postgresql', 'guide']);
-- 数组查询
SELECT * FROM posts WHERE 'postgresql' = ANY(tags);
-- 全文搜索
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT,
content TEXT,
search_vector TSVECTOR
);
CREATE INDEX articles_search_idx ON articles USING GIN(search_vector);
-- 更新搜索向量
UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || content);
-- 搜索
SELECT title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & database');
4. 事务处理
-- 开始事务
BEGIN;
-- 转账操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 提交事务
COMMIT;
-- 如果出错则回滚
ROLLBACK;
5. 视图和物化视图
-- 创建视图
CREATE VIEW active_users AS
SELECT * FROM users
WHERE last_login > NOW() - INTERVAL '30 days';
-- 创建物化视图
CREATE MATERIALIZED VIEW monthly_stats AS
SELECT
date_trunc('month', created_at) as month,
COUNT(*) as user_count
FROM users
GROUP BY 1
WITH DATA;
-- 刷新物化视图
REFRESH MATERIALIZED VIEW monthly_stats;
Node.js 示例
const { Pool } = require('pg');
const pool = new Pool({
user: 'dbuser',
host: 'localhost',
database: 'mydb',
password: 'password',
port: 5432,
});
// 异步查询示例
async function getUserById(id) {
try {
const { rows } = await pool.query(
'SELECT * FROM users WHERE id = $1',
[id]
);
return rows[0];
} catch (err) {
console.error('Error executing query', err.stack);
throw err;
}
}
// 事务示例
async function transferMoney(fromId, toId, amount) {
const client = await pool.connect();
try {
await client.query('BEGIN');
await client.query(
'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
[amount, fromId]
);
await client.query(
'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
[amount, toId]
);
await client.query('COMMIT');
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
}
性能优化技巧
- 索引优化
-- 创建部分索引
CREATE INDEX idx_users_active ON users(last_login)
WHERE status = 'active';
-- 创建复合索引
CREATE INDEX idx_users_name_email ON users(username, email);
- 查询优化
-- 使用 EXPLAIN ANALYZE 分析查询
EXPLAIN ANALYZE
SELECT * FROM users
WHERE email LIKE '%@example.com';
-- 使用 LIMIT 和 OFFSET 进行分页
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;
- 定期维护
-- 更新统计信息
ANALYZE users;
-- 清理表
VACUUM ANALYZE users;
备份和恢复
# 备份数据库
pg_dump dbname > backup.sql
# 恢复数据库
psql dbname < backup.sql
# 导出特定表
pg_dump -t tablename dbname > table_backup.sql
常见问题解决
- 连接数过多
-- 查看当前连 接
SELECT * FROM pg_stat_activity;
-- 终止空闲连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle';
- 死锁处理
-- 查看锁
SELECT * FROM pg_locks;
-- 终止特定查询
SELECT pg_cancel_backend(pid);
- 性能监控
-- 查看慢查询
SELECT * FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;