PostgreSQL深度解析：特性、概念、原理与实战示例

1
-- JSON/JSONB类型
2
CREATE TABLE products (
3
    id SERIAL PRIMARY KEY,
4
    name VARCHAR(100),
5
    attributes JSONB,
6
    metadata JSON
7
);
8

9
INSERT INTO products (name, attributes, metadata) VALUES (
10
    'Laptop',
11
    '{"brand": "Dell", "cpu": "Intel i7", "ram": "16GB", "storage": "512GB SSD"}',
12
    '{"warranty": "2 years", "color": "black"}'
13
);
14

15
-- 数组类型
16
CREATE TABLE users (
17
    id SERIAL PRIMARY KEY,
18
    name VARCHAR(100),
19
    tags TEXT[],
20
    scores INTEGER[]
21
);
22

23
INSERT INTO users (name, tags, scores) VALUES (
24
    'John Doe',
25
    ARRAY['developer', 'postgresql', 'python'],
26
    ARRAY[85, 92, 78]
27
);
28

29
-- 几何类型（需要PostGIS扩展）
30
CREATE EXTENSION IF NOT EXISTS postgis;
31

32
CREATE TABLE locations (
33
    id SERIAL PRIMARY KEY,
34
    name VARCHAR(100),
35
    point GEOMETRY(POINT, 4326),
36
    polygon GEOMETRY(POLYGON, 4326)
37
);

1
-- B-tree索引（默认）
2
CREATE INDEX idx_users_name ON users(name);
3

4
-- Hash索引（适用于等值查询）
5
CREATE INDEX idx_users_email_hash ON users USING hash(email);
6

7
-- GiST索引（适用于几何数据和全文搜索）
8
CREATE INDEX idx_locations_point_gist ON locations USING gist(point);
9

10
-- GIN索引（适用于数组和JSON）
11
CREATE INDEX idx_products_attributes_gin ON products USING gin(attributes);
12

13
-- BRIN索引（适用于大表的范围查询）
14
CREATE INDEX idx_logs_timestamp_brin ON logs USING brin(timestamp);

1
-- 事务示例
2
BEGIN;
3

4
-- 检查账户余额
5
SELECT balance FROM accounts WHERE id = 1;
6

7
-- 转账操作
8
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
9
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
10

11
-- 提交事务
12
COMMIT;
13

14
-- 使用保存点
15
BEGIN;
16
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
17
SAVEPOINT transfer_point;
18
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
19
-- 如果出错可以回滚到保存点
20
ROLLBACK TO transfer_point;
21
COMMIT;

1
-- 演示MVCC
2
-- 会话1
3
BEGIN;
4
UPDATE users SET name = 'Jane Doe' WHERE id = 1;
5
-- 此时其他会话仍能看到旧数据
6

7
-- 会话2
8
SELECT name FROM users WHERE id = 1; -- 返回旧数据
9

10
-- 会话1
11
COMMIT;
12

13
-- 会话2
14
SELECT name FROM users WHERE id = 1; -- 现在返回新数据

1
-- 创建表空间
2
CREATE TABLESPACE fast_disk LOCATION '/data/postgresql/fast';
3

4
-- 在表空间上创建表
5
CREATE TABLE large_table (
6
    id SERIAL PRIMARY KEY,
7
    data TEXT
8
) TABLESPACE fast_disk;
9

10
-- 查看表空间使用情况
11
SELECT
12
    schemaname,
13
    tablename,
14
    tablespace,
15
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
16
FROM pg_tables
17
WHERE tablespace IS NOT NULL;

1
-- 创建分区表
2
CREATE TABLE orders (
3
    id SERIAL,
4
    order_date DATE,
5
    customer_id INTEGER,
6
    amount DECIMAL(10,2)
7
) PARTITION BY RANGE (order_date);
8

9
-- 创建分区
10
CREATE TABLE orders_2024 PARTITION OF orders
11
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
12

13
CREATE TABLE orders_2025 PARTITION OF orders
14
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
15

16
-- 插入数据
17
INSERT INTO orders (order_date, customer_id, amount) VALUES
18
    ('2024-06-15', 1, 150.00),
19
    ('2025-03-20', 2, 300.00);

1
-- 查看表的物理存储信息
2
SELECT
3
    schemaname,
4
    tablename,
5
    attname,
6
    n_distinct,
7
    correlation
8
FROM pg_stats
9
WHERE tablename = 'users';
10

11
-- 查看表的页面信息
12
SELECT
13
    relname,
14
    relpages,
15
    reltuples,
16
    relallvisible
17
FROM pg_class
18
WHERE relname = 'users';

1
-- 分析查询执行计划
2
EXPLAIN (ANALYZE, BUFFERS)
3
SELECT u.name, p.name
4
FROM users u
5
JOIN products p ON u.id = p.user_id
6
WHERE u.tags @> ARRAY['developer'];
7

8
-- 查看统计信息
9
SELECT
10
    schemaname,
11
    tablename,
12
    attname,
13
    n_distinct,
14
    correlation
15
FROM pg_stats
16
WHERE tablename = 'users';

1
-- 查看当前锁
2
SELECT
3
    l.pid,
4
    l.mode,
5
    l.granted,
6
    t.relname,
7
    a.usename
8
FROM pg_locks l
9
JOIN pg_class t ON l.relation = t.oid
10
JOIN pg_user a ON l.pid = a.usesysid
11
WHERE t.relname = 'users';
12

13
-- 行级锁示例
14
BEGIN;
15
SELECT * FROM users WHERE id = 1 FOR UPDATE;
16
-- 这会锁定id=1的行，其他事务无法修改
17
COMMIT;

1
-- 用户表
2
CREATE TABLE users (
3
    id SERIAL PRIMARY KEY,
4
    username VARCHAR(50) UNIQUE NOT NULL,
5
    email VARCHAR(100) UNIQUE NOT NULL,
6
    password_hash VARCHAR(255) NOT NULL,
7
    profile JSONB,
8
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
9
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
10
);
11

12
-- 商品表
13
CREATE TABLE products (
14
    id SERIAL PRIMARY KEY,
15
    name VARCHAR(200) NOT NULL,
16
    description TEXT,
17
    price DECIMAL(10,2) NOT NULL,
18
    stock_quantity INTEGER NOT NULL DEFAULT 0,
19
    category_id INTEGER,
20
    attributes JSONB,
21
    images TEXT[],
22
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
23
);
24

25
-- 订单表
26
CREATE TABLE orders (
27
    id SERIAL PRIMARY KEY,
28
    user_id INTEGER REFERENCES users(id),
29
    status VARCHAR(20) DEFAULT 'pending',
30
    total_amount DECIMAL(10,2) NOT NULL,
31
    shipping_address JSONB,
32
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
33
);
34

35
-- 订单详情表
36
CREATE TABLE order_items (
37
    id SERIAL PRIMARY KEY,
38
    order_id INTEGER REFERENCES orders(id),
39
    product_id INTEGER REFERENCES products(id),
40
    quantity INTEGER NOT NULL,
41
    unit_price DECIMAL(10,2) NOT NULL,
42
    total_price DECIMAL(10,2) NOT NULL
43
);
44

45
-- 创建索引
46
CREATE INDEX idx_products_category ON products(category_id);
47
CREATE INDEX idx_products_price ON products(price);
48
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
49
CREATE INDEX idx_orders_created_at ON orders(created_at);
50
CREATE INDEX idx_products_attributes_gin ON products USING gin(attributes);

1
-- 用户购买分析
2
WITH user_purchases AS (
3
    SELECT
4
        u.id,
5
        u.username,
6
        COUNT(o.id) as order_count,
7
        SUM(o.total_amount) as total_spent,
8
        AVG(o.total_amount) as avg_order_value
9
    FROM users u
10
    LEFT JOIN orders o ON u.id = o.user_id
11
    WHERE o.status = 'completed'
12
    GROUP BY u.id, u.username
13
)
14
SELECT
15
    username,
16
    order_count,
17
    total_spent,
18
    avg_order_value,
19
    CASE
20
        WHEN total_spent >= 1000 THEN 'VIP'
21
        WHEN total_spent >= 500 THEN 'Regular'
22
        ELSE 'New'
23
    END as customer_type
24
FROM user_purchases
25
ORDER BY total_spent DESC;
26

27
-- 商品销售分析
28
SELECT
29
    p.name,
30
    p.category_id,
31
    COUNT(oi.id) as times_sold,
32
    SUM(oi.quantity) as total_quantity,
33
    SUM(oi.total_price) as total_revenue,
34
    AVG(oi.unit_price) as avg_price
35
FROM products p
36
LEFT JOIN order_items oi ON p.id = oi.product_id
37
LEFT JOIN orders o ON oi.order_id = o.id
38
WHERE o.status = 'completed'
39
GROUP BY p.id, p.name, p.category_id
40
ORDER BY total_revenue DESC;
41

42
-- 时间序列分析
43
SELECT
44
    DATE_TRUNC('month', created_at) as month,
45
    COUNT(*) as order_count,
46
    SUM(total_amount) as monthly_revenue,
47
    AVG(total_amount) as avg_order_value
48
FROM orders
49
WHERE status = 'completed'
50
GROUP BY DATE_TRUNC('month', created_at)
51
ORDER BY month;

1
-- 创建物化视图
2
CREATE MATERIALIZED VIEW product_sales_summary AS
3
SELECT
4
    p.id,
5
    p.name,
6
    p.category_id,
7
    COUNT(oi.id) as times_sold,
8
    SUM(oi.quantity) as total_quantity,
9
    SUM(oi.total_price) as total_revenue
10
FROM products p
11
LEFT JOIN order_items oi ON p.id = oi.product_id
12
LEFT JOIN orders o ON oi.order_id = o.id
13
WHERE o.status = 'completed'
14
GROUP BY p.id, p.name, p.category_id;
15

16
-- 创建唯一索引
17
CREATE UNIQUE INDEX idx_product_sales_summary_id ON product_sales_summary(id);
18

19
-- 刷新物化视图
20
REFRESH MATERIALIZED VIEW product_sales_summary;
21

22
-- 使用窗口函数进行排名
23
SELECT
24
    name,
25
    total_revenue,
26
    RANK() OVER (ORDER BY total_revenue DESC) as revenue_rank,
27
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY total_revenue DESC) as category_rank
28
FROM product_sales_summary;

1
-- 全文搜索
2
CREATE INDEX idx_products_name_search ON products USING gin(to_tsvector('english', name || ' ' || description));
3

4
SELECT name, description, ts_rank(to_tsvector('english', name || ' ' || description), query) as rank
5
FROM products, to_tsquery('english', 'laptop & fast') query
6
WHERE to_tsvector('english', name || ' ' || description) @@ query
7
ORDER BY rank DESC;
8

9
-- 递归查询（组织架构）
10
CREATE TABLE employees (
11
    id SERIAL PRIMARY KEY,
12
    name VARCHAR(100),
13
    manager_id INTEGER REFERENCES employees(id)
14
);
15

16
WITH RECURSIVE org_tree AS (
17
    -- 基础查询：找到根节点
18
    SELECT id, name, manager_id, 1 as level, ARRAY[id] as path
19
    FROM employees
20
    WHERE manager_id IS NULL
21

22
    UNION ALL
23

24
    -- 递归查询：找到子节点
25
    SELECT e.id, e.name, e.manager_id, ot.level + 1, ot.path || e.id
26
    FROM employees e
27
    JOIN org_tree ot ON e.manager_id = ot.id
28
)
29
SELECT
30
    level,
31
    REPEAT('  ', level - 1) || name as hierarchy,
32
    path
33
FROM org_tree
34
ORDER BY path;

1
-- 查看当前配置
2
SHOW shared_buffers;
3
SHOW effective_cache_size;
4
SHOW work_mem;
5
SHOW maintenance_work_mem;
6

7
-- 推荐的配置参数（postgresql.conf）
8
-- shared_buffers = 25% of RAM
9
-- effective_cache_size = 75% of RAM
10
-- work_mem = 4MB (根据并发连接数调整)
11
-- maintenance_work_mem = 256MB
12
-- checkpoint_completion_target = 0.9
13
-- wal_buffers = 16MB
14
-- default_statistics_target = 100

1
-- 查看数据库大小
2
SELECT
3
    pg_database.datname,
4
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
5
FROM pg_database;
6

7
-- 查看表大小
8
SELECT
9
    schemaname,
10
    tablename,
11
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
12
FROM pg_tables
13
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
14

15
-- 查看索引使用情况
16
SELECT
17
    schemaname,
18
    tablename,
19
    indexname,
20
    idx_scan,
21
    idx_tup_read,
22
    idx_tup_fetch
23
FROM pg_stat_user_indexes
24
ORDER BY idx_scan DESC;
25

26
-- 清理和分析
27
VACUUM ANALYZE users;
28
REINDEX TABLE users;