---

什么是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是最佳選擇之一。