My Docs

Appearance

PostgreSQL 对象操作 ​

本文档涵盖 PostgreSQL 中表约束、默认值、触发器、表空间、视图、索引、物化视图等核心对象的操作与最佳实践。

1. 表 — 约束 ​

约束用于保证数据的完整性和一致性，是数据库设计的核心。

主键约束 (PRIMARY KEY) ​

sql
-- 单列主键
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username TEXT NOT NULL
);

-- 复合主键
CREATE TABLE order_items (
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (order_id, product_id)
);

-- 后期添加主键
ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (id);

唯一约束 (UNIQUE) ​

sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username TEXT UNIQUE,             -- 列级定义
    email TEXT NOT NULL,
    phone TEXT,
    UNIQUE (email)                    -- 表级定义
);

-- 多列联合唯一
ALTER TABLE users ADD CONSTRAINT uq_email_phone UNIQUE (email, phone);

-- 部分唯一（只对活跃用户保证唯一）
CREATE UNIQUE INDEX uq_active_username ON users (username) WHERE status = 'active';

-- NULLS NOT DISTINCT（PG 15+）：NULL 也算重复
ALTER TABLE users ADD CONSTRAINT uq_phone UNIQUE NULLS NOT DISTINCT (phone);

非空约束 (NOT NULL) ​

sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL
);

-- 添加/移除非空约束
ALTER TABLE products ALTER COLUMN name SET NOT NULL;
ALTER TABLE products ALTER COLUMN name DROP NOT NULL;

检查约束 (CHECK) ​

sql
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER CHECK (age >= 18 AND age <= 120),
    salary NUMERIC(12,2) CHECK (salary > 0),
    start_date DATE,
    end_date DATE,
    -- 多列检查约束
    CONSTRAINT chk_date_range CHECK (end_date IS NULL OR end_date > start_date)
);

-- 后期添加（NOT VALID 跳过已有数据校验，提升速度）
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary >= 3000) NOT VALID;
-- 验证已有数据
ALTER TABLE employees VALIDATE CONSTRAINT chk_salary;

-- 删除检查约束
ALTER TABLE employees DROP CONSTRAINT chk_salary;

外键约束 (FOREIGN KEY) ​

sql
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    dept_id INTEGER,
    manager_id INTEGER,
    -- 外键引用
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    -- 自引用外键
    CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees(id)
        ON DELETE SET NULL
);

外键动作 ​

动作说明
CASCADE级联删除/更新子行
SET NULL将子行外键列设为 NULL
SET DEFAULT将子行外键列设为默认值
RESTRICT如果存在子行则拒绝操作（立即检查）
NO ACTION默认行为，同 RESTRICT 但延迟到事务结束检查
sql
-- 延迟检查外键（在事务提交时才校验）
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
        DEFERRABLE INITIALLY DEFERRED
);

-- 在事务中临时改变约束检查时机
BEGIN;
SET CONSTRAINTS fk_user IMMEDIATE;  -- 改为立即检查
-- ...
COMMIT;

排除约束 (EXCLUDE) ​

sql
-- 需要 btree_gist 扩展
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 会议室预订：同一房间时间不能重叠
CREATE TABLE meeting_rooms (
    id SERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL,
    during TSRANGE NOT NULL,
    booked_by TEXT NOT NULL,
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

INSERT INTO meeting_rooms (room_id, during, booked_by)
VALUES (1, '[2024-06-01 09:00, 2024-06-01 10:00)', 'Alice');  -- OK

INSERT INTO meeting_rooms (room_id, during, booked_by)
VALUES (1, '[2024-06-01 09:30, 2024-06-01 11:00)', 'Bob');    -- ERROR: 冲突

约束管理 ​

sql
-- 查看表的所有约束
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'employees'::regclass;
-- contype: p=主键, u=唯一, c=检查, f=外键, x=排除

-- 重命名约束
ALTER TABLE employees RENAME CONSTRAINT chk_date_range TO chk_dates;

-- 删除约束
ALTER TABLE employees DROP CONSTRAINT IF EXISTS chk_dates CASCADE;

2. 表 — 默认值 ​

基本默认值 ​

sql
CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'draft',
    view_count INTEGER DEFAULT 0,
    is_published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入时省略有默认值的列
INSERT INTO articles (title, content) VALUES ('Hello', 'World');
-- status='draft', view_count=0, is_published=false, created_at=当前时间

-- 显式使用 DEFAULT 关键字
INSERT INTO articles (title, content, status) VALUES ('Test', 'Body', DEFAULT);

修改默认值 ​

sql
-- 设置默认值
ALTER TABLE articles ALTER COLUMN status SET DEFAULT 'pending';

-- 删除默认值
ALTER TABLE articles ALTER COLUMN status DROP DEFAULT;

-- 默认值不影响已有数据，只影响新插入的行

序列与自增 ​

sql
-- SERIAL 方式（旧式，本质是序列 + 默认值）
CREATE TABLE t1 (id SERIAL PRIMARY KEY);
-- 等价于:
-- CREATE SEQUENCE t1_id_seq;
-- CREATE TABLE t1 (id INTEGER DEFAULT nextval('t1_id_seq') NOT NULL PRIMARY KEY);

-- IDENTITY 方式（PG 10+，推荐）
CREATE TABLE t2 (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT
);

-- GENERATED ALWAYS：禁止手动指定值（除非 OVERRIDING SYSTEM VALUE）
INSERT INTO t2 (name) VALUES ('Alice');  -- id 自动生成
-- INSERT INTO t2 (id, name) VALUES (100, 'Bob');  -- ERROR

INSERT INTO t2 (id, name) OVERRIDING SYSTEM VALUE VALUES (100, 'Bob');  -- OK

-- GENERATED BY DEFAULT：允许手动指定值
CREATE TABLE t3 (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT
);
INSERT INTO t3 (id, name) VALUES (100, 'Bob');  -- OK

-- 修改 IDENTITY 属性
ALTER TABLE t2 ALTER COLUMN id SET GENERATED BY DEFAULT;
ALTER TABLE t2 ALTER COLUMN id RESTART WITH 1000;
ALTER TABLE t2 ALTER COLUMN id SET INCREMENT BY 10;

生成列（PG 12+） ​

sql
-- STORED 生成列：值在插入/更新时自动计算并物理存储
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10,2) NOT NULL,
    tax_rate NUMERIC(5,4) NOT NULL DEFAULT 0.08,
    total NUMERIC(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED,
    name TEXT NOT NULL,
    name_lower TEXT GENERATED ALWAYS AS (lower(name)) STORED
);

INSERT INTO products (price, tax_rate, name) VALUES (100, 0.08, 'Widget');
SELECT total FROM products;  -- 108.00（自动计算）

-- 生成列不能被直接赋值
-- UPDATE products SET total = 200;  -- ERROR

-- 生成列可以创建索引
CREATE INDEX idx_products_total ON products (total);

序列操作 ​

sql
-- 创建独立序列
CREATE SEQUENCE order_seq START 1000 INCREMENT 1 MINVALUE 1000 MAXVALUE 999999;

-- 使用序列
SELECT nextval('order_seq');     -- 获取下一个值
SELECT currval('order_seq');     -- 获取当前值（当前会话需先调 nextval）
SELECT setval('order_seq', 2000);         -- 重置到 2000
SELECT setval('order_seq', 2000, FALSE);  -- 下次 nextval 返回 2000

-- 查看序列状态
SELECT last_value, is_called FROM order_seq;

-- 修改序列
ALTER SEQUENCE order_seq RESTART WITH 5000;
ALTER SEQUENCE order_seq INCREMENT BY 5;
ALTER SEQUENCE order_seq OWNED BY orders.id;  -- 绑定到列（随表删除）

3. 表 — 触发器 ​

触发器在特定事件发生时自动执行自定义函数。

触发器函数 ​

sql
-- 触发器函数必须返回 TRIGGER 类型
-- 可用特殊变量：NEW（新行）、OLD（旧行）、TG_OP（操作类型）等

-- 示例：自动更新 updated_at
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

行级触发器 (FOR EACH ROW) ​

sql
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- BEFORE UPDATE 触发器：修改即将写入的数据
CREATE TRIGGER trg_articles_update
    BEFORE UPDATE ON articles
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

-- 测试
INSERT INTO articles (title) VALUES ('Hello');
-- 等一会儿...
UPDATE articles SET title = 'World' WHERE id = 1;
SELECT updated_at FROM articles WHERE id = 1;  -- 时间已更新

审计日志触发器 ​

sql
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    operation TEXT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_by TEXT DEFAULT current_user,
    changed_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation, new_data)
        VALUES (TG_TABLE_NAME, 'INSERT', to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, new_data)
        VALUES (TG_TABLE_NAME, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, old_data)
        VALUES (TG_TABLE_NAME, 'DELETE', to_jsonb(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 为表绑定审计触发器
CREATE TRIGGER trg_users_audit
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger_func();

条件触发器 ​

sql
-- WHEN 子句：只有满足条件才触发
CREATE TRIGGER trg_salary_change
    AFTER UPDATE OF salary ON employees
    FOR EACH ROW
    WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
    EXECUTE FUNCTION audit_trigger_func();

-- 只在特定列被修改时触发
CREATE TRIGGER trg_status_change
    BEFORE UPDATE OF status ON orders
    FOR EACH ROW
    EXECUTE FUNCTION handle_status_change();

语句级触发器 (FOR EACH STATEMENT) ​

sql
-- 不管影响多少行，只触发一次
CREATE OR REPLACE FUNCTION log_bulk_operation()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE '% 操作在 % 上执行', TG_OP, TG_TABLE_NAME;
    RETURN NULL;  -- 语句级 AFTER 触发器返回值被忽略
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_bulk_log
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH STATEMENT
    EXECUTE FUNCTION log_bulk_operation();

INSTEAD OF 触发器（用于视图） ​

sql
CREATE VIEW active_users AS
    SELECT id, username, email FROM users WHERE status = 'active';

CREATE OR REPLACE FUNCTION active_users_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO users (username, email, status)
    VALUES (NEW.username, NEW.email, 'active');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_active_users_insert
    INSTEAD OF INSERT ON active_users
    FOR EACH ROW
    EXECUTE FUNCTION active_users_insert();

-- 现在可以向视图插入数据了
INSERT INTO active_users (username, email) VALUES ('alice', 'alice@test.com');

转换触发器（PG 12+） ​

sql
-- TRUNCATE 事件触发器
CREATE TRIGGER trg_prevent_truncate
    BEFORE TRUNCATE ON critical_data
    FOR EACH STATEMENT
    EXECUTE FUNCTION prevent_truncate_func();

触发器管理 ​

sql
-- 查看表上的触发器
SELECT tgname, tgtype, tgenabled,
       pg_get_triggerdef(oid) AS definition
FROM pg_trigger
WHERE tgrelid = 'articles'::regclass AND NOT tgisinternal;

-- 启用/禁用触发器
ALTER TABLE articles DISABLE TRIGGER trg_articles_update;
ALTER TABLE articles ENABLE TRIGGER trg_articles_update;
ALTER TABLE articles DISABLE TRIGGER ALL;   -- 禁用所有
ALTER TABLE articles ENABLE TRIGGER ALL;    -- 启用所有

-- 删除触发器
DROP TRIGGER IF EXISTS trg_articles_update ON articles;

-- 触发器执行顺序
-- 1. BEFORE 语句级
-- 2. BEFORE 行级（按名称字母序）
-- 3. 实际操作
-- 4. AFTER 行级（按名称字母序）
-- 5. AFTER 语句级

4. 表空间 ​

表空间允许将数据库对象存储在不同的物理位置（磁盘/分区）。

默认表空间 ​

sql
-- PostgreSQL 默认有两个表空间
-- pg_default：默认数据存储位置（$PGDATA/base）
-- pg_global：共享系统目录（$PGDATA/global）

-- 查看所有表空间
SELECT spcname, pg_tablespace_location(oid), pg_size_pretty(pg_tablespace_size(spcname))
FROM pg_tablespace;

-- 查看当前默认表空间
SHOW default_tablespace;

创建表空间 ​

sql
-- 需要超级用户权限，目录必须存在且为空
-- 先在操作系统层面创建目录
-- mkdir -p /data/fast_ssd
-- chown postgres:postgres /data/fast_ssd

CREATE TABLESPACE fast_ssd LOCATION '/data/fast_ssd';
CREATE TABLESPACE archive_hdd LOCATION '/data/archive_hdd';

-- 带参数
CREATE TABLESPACE fast_ssd
    LOCATION '/data/fast_ssd'
    WITH (seq_page_cost = 0.5, random_page_cost = 1.0);  -- SSD 调优参数

使用表空间 ​

sql
-- 创建表时指定表空间
CREATE TABLE hot_data (
    id SERIAL PRIMARY KEY,
    data JSONB
) TABLESPACE fast_ssd;

-- 创建索引时指定表空间
CREATE INDEX idx_hot_data ON hot_data (id) TABLESPACE fast_ssd;

-- 移动表到其他表空间
ALTER TABLE hot_data SET TABLESPACE archive_hdd;

-- 移动索引
ALTER INDEX idx_hot_data SET TABLESPACE archive_hdd;

-- 设置数据库默认表空间
ALTER DATABASE mydb SET TABLESPACE fast_ssd;

-- 设置会话级默认表空间
SET default_tablespace = 'fast_ssd';

-- 批量移动整个表空间中的对象
ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE fast_ssd;
ALTER INDEX ALL IN TABLESPACE pg_default SET TABLESPACE fast_ssd;
ALTER MATERIALIZED VIEW ALL IN TABLESPACE pg_default SET TABLESPACE fast_ssd;

查看表空间使用情况 ​

sql
-- 查看对象所在的表空间
SELECT tablename, tablespace
FROM pg_tables
WHERE schemaname = 'public';

-- 查看各表空间大小
SELECT spcname,
       pg_size_pretty(pg_tablespace_size(spcname)) AS size
FROM pg_tablespace;

-- 查看某个表的物理位置
SELECT pg_relation_filepath('hot_data');

5. 解决表空间问题 ​

表空间迁移 ​

当磁盘空间不足时，需要将数据迁移到新表空间：

sql
-- 步骤一：创建新表空间
CREATE TABLESPACE new_space LOCATION '/data/new_disk';

-- 步骤二：移动表（会锁表，注意业务影响）
ALTER TABLE large_table SET TABLESPACE new_space;

-- 步骤三：移动关联索引
ALTER INDEX large_table_pkey SET TABLESPACE new_space;
ALTER INDEX idx_large_table_col SET TABLESPACE new_space;

-- 步骤四：验证
SELECT pg_relation_filepath('large_table');

表膨胀与空间回收 ​

sql
-- 查看表的实际大小与膨胀率
SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size,
    n_dead_tup,
    n_live_tup,
    CASE WHEN n_live_tup > 0
        THEN round(n_dead_tup::NUMERIC / n_live_tup * 100, 2)
        ELSE 0
    END AS dead_ratio_pct
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- VACUUM：回收死元组空间（不归还操作系统）
VACUUM articles;
VACUUM VERBOSE articles;  -- 显示详细信息

-- VACUUM FULL：重建表，归还磁盘空间（会锁表！）
VACUUM FULL articles;

-- ANALYZE：更新统计信息
ANALYZE articles;

-- VACUUM + ANALYZE（常规维护推荐）
VACUUM ANALYZE articles;

自动 VACUUM 调优 ​

sql
-- 查看 autovacuum 配置
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;        -- 默认 50
SHOW autovacuum_vacuum_scale_factor;     -- 默认 0.2
-- 触发条件：dead_tuples > threshold + scale_factor * live_tuples

-- 对特定表调优 autovacuum
ALTER TABLE hot_data SET (
    autovacuum_vacuum_threshold = 100,
    autovacuum_vacuum_scale_factor = 0.05,  -- 5% 死元组就触发
    autovacuum_analyze_threshold = 50,
    autovacuum_analyze_scale_factor = 0.02
);

-- 禁用特定表的 autovacuum（不推荐，除非有特殊需求）
ALTER TABLE temp_data SET (autovacuum_enabled = false);

6. 表空间重复存储 ​

表分区 ​

当表过大时，使用分区可以将数据分散到不同表空间：

sql
-- 范围分区
CREATE TABLE logs (
    id BIGSERIAL,
    message TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- 每个分区可以放在不同表空间
CREATE TABLE logs_2024_q1 PARTITION OF logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01')
    TABLESPACE fast_ssd;

CREATE TABLE logs_2024_q2 PARTITION OF logs
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01')
    TABLESPACE fast_ssd;

CREATE TABLE logs_2023 PARTITION OF logs
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
    TABLESPACE archive_hdd;  -- 历史数据放慢速磁盘

-- 默认分区（兜底）
CREATE TABLE logs_default PARTITION OF logs DEFAULT;

列表分区 ​

sql
CREATE TABLE orders (
    id BIGSERIAL,
    region TEXT NOT NULL,
    amount NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY LIST (region);

CREATE TABLE orders_asia PARTITION OF orders
    FOR VALUES IN ('CN', 'JP', 'KR', 'TW') TABLESPACE ts_asia;
CREATE TABLE orders_europe PARTITION OF orders
    FOR VALUES IN ('DE', 'FR', 'UK', 'IT') TABLESPACE ts_europe;
CREATE TABLE orders_america PARTITION OF orders
    FOR VALUES IN ('US', 'CA', 'BR', 'MX') TABLESPACE ts_america;

哈希分区 ​

sql
CREATE TABLE sessions (
    id UUID DEFAULT gen_random_uuid(),
    user_id INTEGER NOT NULL,
    data JSONB
) PARTITION BY HASH (id);

CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

分区管理 ​

sql
-- 卸载分区（保留数据，脱离父表）
ALTER TABLE logs DETACH PARTITION logs_2023;
-- PG 14+：并发卸载，不锁父表
ALTER TABLE logs DETACH PARTITION logs_2023 CONCURRENTLY;

-- 挂载分区
ALTER TABLE logs ATTACH PARTITION logs_2023
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- 查看分区信息
SELECT inhrelid::regclass AS partition_name,
       pg_get_expr(relpartbound, inhrelid) AS bound
FROM pg_inherits
JOIN pg_class ON pg_class.oid = inhrelid
WHERE inhparent = 'logs'::regclass;

7. 视图 ​

普通视图 ​

视图是存储的查询，不保存数据，每次查询时实时执行。

sql
-- 创建视图
CREATE VIEW active_users AS
    SELECT id, username, email, created_at
    FROM users
    WHERE status = 'active';

-- 使用视图
SELECT * FROM active_users WHERE username LIKE 'a%';

-- 复杂视图
CREATE VIEW order_summary AS
    SELECT
        u.username,
        count(o.id) AS order_count,
        sum(o.amount) AS total_amount,
        avg(o.amount)::NUMERIC(10,2) AS avg_amount,
        max(o.created_at) AS last_order_at
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.username;

SELECT * FROM order_summary ORDER BY total_amount DESC;

替换与修改视图 ​

sql
-- 替换视图（列定义必须兼容：新列只能追加在末尾）
CREATE OR REPLACE VIEW active_users AS
    SELECT id, username, email, created_at, age
    FROM users
    WHERE status = 'active';

-- 重命名视图
ALTER VIEW active_users RENAME TO v_active_users;

-- 修改视图所有者
ALTER VIEW v_active_users OWNER TO app_user;

-- 删除视图
DROP VIEW IF EXISTS v_active_users CASCADE;

可更新视图 ​

sql
-- 简单视图自动可更新（单表、无聚合、无 DISTINCT、无 GROUP BY 等）
CREATE VIEW draft_articles AS
    SELECT id, title, content, created_at
    FROM articles
    WHERE status = 'draft';

-- 直接对视图进行 DML
INSERT INTO draft_articles (title, content) VALUES ('New Post', 'Content...');
UPDATE draft_articles SET title = 'Updated' WHERE id = 1;
DELETE FROM draft_articles WHERE id = 1;

-- 注意：INSERT 不会自动设置 status='draft'，需要 CHECK OPTION

WITH CHECK OPTION ​

sql
-- LOCAL CHECK：只检查当前视图条件
CREATE VIEW draft_articles AS
    SELECT id, title, content, status
    FROM articles
    WHERE status = 'draft'
    WITH LOCAL CHECK OPTION;

-- 以下会报错，因为 status 不是 'draft'
-- INSERT INTO draft_articles (title, content, status) VALUES ('Test', 'Body', 'published');

-- CASCADED CHECK（默认）：检查当前视图及所有基础视图的条件
CREATE VIEW recent_draft_articles AS
    SELECT * FROM draft_articles
    WHERE created_at > NOW() - INTERVAL '30 days'
    WITH CASCADED CHECK OPTION;

安全视图 ​

sql
-- security_barrier：防止通过用户自定义函数绕过视图过滤条件
CREATE VIEW user_own_data WITH (security_barrier = true) AS
    SELECT * FROM sensitive_data
    WHERE owner_id = current_user_id();

-- 安全调用者（PG 15+）
-- security_invoker = true：以查询者身份检查权限而非视图所有者
CREATE VIEW shared_data WITH (security_invoker = true) AS
    SELECT * FROM restricted_table;

8. 索引应用 ​

B-Tree 索引（默认） ​

sql
-- 适用于：等值、范围、排序、LIKE 前缀匹配
CREATE INDEX idx_users_email ON users (email);

-- 多列索引（遵循最左前缀原则）
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_username ON users (username);

-- LIKE 前缀匹配需要特殊操作符类
CREATE INDEX idx_users_name_pattern ON users (username varchar_pattern_ops);
-- 支持：username LIKE 'abc%'
-- 不支持：username LIKE '%abc'

部分索引 ​

sql
-- 只索引满足条件的行，减小索引体积
CREATE INDEX idx_active_orders ON orders (created_at)
    WHERE status = 'active';

-- 只有查询包含 WHERE status = 'active' 时才使用此索引
EXPLAIN SELECT * FROM orders WHERE status = 'active' AND created_at > '2024-01-01';

表达式索引 ​

sql
-- 对表达式建索引
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- 查询时也要用相同表达式
SELECT * FROM users WHERE lower(email) = 'alice@test.com';

-- JSON 字段索引
CREATE INDEX idx_data_type ON events ((data ->> 'type'));
SELECT * FROM events WHERE data ->> 'type' = 'click';

GIN 索引 ​

sql
-- 适用于：数组、JSONB、全文搜索、多值列
-- JSONB
CREATE INDEX idx_events_data ON events USING GIN (data);
SELECT * FROM events WHERE data @> '{"type":"click"}';

-- 数组
CREATE INDEX idx_tags ON posts USING GIN (tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];

-- 全文搜索
CREATE INDEX idx_articles_fts ON articles USING GIN (to_tsvector('english', content));
SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & tutorial');

GiST 索引 ​

sql
-- 适用于：范围类型、几何类型、IP 地址、全文搜索
CREATE INDEX idx_reservations_during ON reservations USING GiST (during);
SELECT * FROM reservations WHERE during && '[2024-06-01, 2024-06-10)'::TSRANGE;

-- IP 地址
CREATE INDEX idx_logs_ip ON access_logs USING GiST (client_ip inet_ops);
SELECT * FROM access_logs WHERE client_ip << inet '192.168.1.0/24';

BRIN 索引 ​

sql
-- 适用于：物理排序良好的大表（如时间序列数据）
-- 极小的索引体积，适合 TB 级别的表
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

-- 指定每个范围的页数（越小精度越高，索引越大）
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at) WITH (pages_per_range = 32);

覆盖索引 (INCLUDE) ​

sql
-- PG 11+：在索引中包含额外列，避免回表
CREATE INDEX idx_orders_user_include ON orders (user_id) INCLUDE (amount, status);

-- 以下查询可以仅通过索引返回结果（Index Only Scan）
SELECT amount, status FROM orders WHERE user_id = 100;

并发创建与维护 ​

sql
-- 并发创建索引（不锁表，但耗时更长）
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- 并发删除索引
DROP INDEX CONCURRENTLY IF EXISTS idx_users_email;

-- 重建索引（修复索引膨胀）
REINDEX INDEX idx_users_email;
REINDEX TABLE users;
REINDEX INDEX CONCURRENTLY idx_users_email;  -- PG 12+ 并发重建

-- 查看索引使用情况
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan;

-- 查找未使用的索引
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public';

EXPLAIN 分析 ​

sql
-- 查看查询计划
EXPLAIN SELECT * FROM users WHERE email = 'alice@test.com';

-- 实际执行并查看计划（包含实际时间和行数）
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'alice@test.com';

-- 关键指标
-- Seq Scan：全表扫描（无索引或优化器认为更快）
-- Index Scan：索引扫描 + 回表
-- Index Only Scan：仅索引扫描（覆盖索引）
-- Bitmap Index Scan：位图索引扫描（多条件合并）
-- cost=0.00..10.25：启动成本..总成本
-- rows=1：估计行数
-- actual time=0.015..0.016：实际时间
-- Buffers: shared hit=3：缓存命中3页

9. 物化视图 — 介绍与基本操作 ​

物化视图将查询结果物理存储，适合复杂聚合查询且数据更新不频繁的场景。

创建物化视图 ​

sql
-- 基本创建
CREATE MATERIALIZED VIEW mv_order_summary AS
    SELECT
        u.id AS user_id,
        u.username,
        count(o.id) AS order_count,
        coalesce(sum(o.amount), 0) AS total_amount,
        coalesce(avg(o.amount), 0)::NUMERIC(10,2) AS avg_amount,
        max(o.created_at) AS last_order_at
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.username;

-- 创建时不填充数据（延迟到首次 REFRESH）
CREATE MATERIALIZED VIEW mv_stats AS
    SELECT count(*) AS total, avg(score) AS avg_score FROM students
    WITH NO DATA;  -- 查询前必须先 REFRESH

使用物化视图 ​

sql
-- 查询（与普通表一样）
SELECT * FROM mv_order_summary WHERE total_amount > 10000 ORDER BY total_amount DESC;

-- 可以创建索引
CREATE UNIQUE INDEX idx_mv_order_user ON mv_order_summary (user_id);
CREATE INDEX idx_mv_order_amount ON mv_order_summary (total_amount);

-- 查看物化视图信息
SELECT matviewname, ispopulated
FROM pg_matviews
WHERE schemaname = 'public';

-- 查看物化视图定义
SELECT pg_get_viewdef('mv_order_summary'::regclass);

物化视图 vs 普通视图 ​

特性普通视图物化视图
数据存储不存储，每次实时查询物理存储查询结果
查询速度取决于底层查询复杂度直接读取存储数据，极快
数据实时性实时需要手动刷新
支持索引不支持支持
占用空间无占用磁盘空间
适用场景简化查询、权限控制复杂报表、聚合统计、数据仓库

10. 物化视图 — 全量更新与增量更新 ​

全量刷新 ​

sql
-- 完全替换物化视图中的数据
REFRESH MATERIALIZED VIEW mv_order_summary;

-- 刷新期间物化视图会被锁，不可查询
-- 使用 CONCURRENTLY 可以在刷新期间继续查询（需要 UNIQUE 索引）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_summary;
-- 前提：mv_order_summary 上必须有唯一索引

-- CONCURRENTLY 原理：
-- 1. 执行查询生成新数据到临时空间
-- 2. 对比新旧数据的差异
-- 3. 只更新变化的行
-- 4. 整个过程中物化视图仍可查询（读旧数据）

定时刷新方案 ​

sql
-- 方案一：使用 pg_cron 扩展（推荐）
CREATE EXTENSION pg_cron;

-- 每小时刷新一次
SELECT cron.schedule('refresh_mv_orders', '0 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_summary');

-- 每天凌晨3点刷新
SELECT cron.schedule('refresh_mv_stats', '0 3 * * *',
    'REFRESH MATERIALIZED VIEW mv_stats');

-- 查看定时任务
SELECT * FROM cron.job;

-- 删除定时任务
SELECT cron.unschedule('refresh_mv_orders');
bash
# 方案二：使用系统 crontab
# 每30分钟刷新一次
*/30 * * * * psql -d mydb -c "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_summary;"

增量更新策略 ​

PostgreSQL 原生不支持真正的增量刷新。以下是常用的模拟方案：

方案一：触发器 + 汇总表 ​

sql
-- 创建汇总表替代物化视图
CREATE TABLE summary_order_stats (
    user_id BIGINT PRIMARY KEY,
    order_count INTEGER DEFAULT 0,
    total_amount NUMERIC(12,2) DEFAULT 0,
    last_order_at TIMESTAMPTZ
);

-- 触发器自动更新汇总表
CREATE OR REPLACE FUNCTION update_order_stats()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO summary_order_stats (user_id, order_count, total_amount, last_order_at)
        VALUES (NEW.user_id, 1, NEW.amount, NEW.created_at)
        ON CONFLICT (user_id) DO UPDATE SET
            order_count = summary_order_stats.order_count + 1,
            total_amount = summary_order_stats.total_amount + NEW.amount,
            last_order_at = GREATEST(summary_order_stats.last_order_at, NEW.created_at);
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE summary_order_stats SET
            order_count = order_count - 1,
            total_amount = total_amount - OLD.amount
        WHERE user_id = OLD.user_id;
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE summary_order_stats SET
            total_amount = total_amount - OLD.amount + NEW.amount,
            last_order_at = GREATEST(last_order_at, NEW.created_at)
        WHERE user_id = NEW.user_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_stats
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION update_order_stats();

方案二：增量时间戳刷新 ​

sql
-- 记录上次刷新时间，只处理增量数据
CREATE TABLE refresh_log (
    view_name TEXT PRIMARY KEY,
    last_refresh TIMESTAMPTZ
);

-- 增量刷新函数
CREATE OR REPLACE FUNCTION incremental_refresh_orders()
RETURNS void AS $$
DECLARE
    v_last_refresh TIMESTAMPTZ;
BEGIN
    -- 获取上次刷新时间
    SELECT last_refresh INTO v_last_refresh
    FROM refresh_log WHERE view_name = 'mv_order_summary';

    IF v_last_refresh IS NULL THEN
        v_last_refresh := '-infinity';
    END IF;

    -- 只更新有变化的用户
    WITH changed_users AS (
        SELECT DISTINCT user_id FROM orders WHERE created_at > v_last_refresh
    )
    -- 删除旧数据
    DELETE FROM mv_order_summary_table
    WHERE user_id IN (SELECT user_id FROM changed_users);

    -- 插入新数据
    INSERT INTO mv_order_summary_table
    SELECT u.id, u.username, count(o.id), coalesce(sum(o.amount), 0),
           coalesce(avg(o.amount), 0)::NUMERIC(10,2), max(o.created_at)
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id IN (SELECT user_id FROM changed_users)
    GROUP BY u.id, u.username;

    -- 更新刷新时间
    INSERT INTO refresh_log (view_name, last_refresh)
    VALUES ('mv_order_summary', NOW())
    ON CONFLICT (view_name) DO UPDATE SET last_refresh = NOW();
END;
$$ LANGUAGE plpgsql;

方案三：使用 CONCURRENTLY 的差异更新 ​

sql
-- REFRESH CONCURRENTLY 本身就是一种「增量」策略
-- 它会比较新旧数据，只更新变化的行
-- 前提是必须有 UNIQUE 索引

-- 步骤
-- 1. 确保有唯一索引
CREATE UNIQUE INDEX idx_mv_user_id ON mv_order_summary (user_id);

-- 2. 并发刷新（内部自动做差异对比）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_summary;

-- 优点：简单可靠
-- 缺点：仍然要执行完整查询，只是写入阶段是增量的

物化视图最佳实践 ​

sql
-- 1. 始终为物化视图创建索引
CREATE MATERIALIZED VIEW mv_daily_sales AS
    SELECT date_trunc('day', created_at) AS sale_date,
           count(*) AS order_count,
           sum(amount) AS revenue
    FROM orders
    GROUP BY 1;

CREATE UNIQUE INDEX idx_mv_daily_sales_date ON mv_daily_sales (sale_date);
CREATE INDEX idx_mv_daily_sales_revenue ON mv_daily_sales (revenue);

-- 2. 监控物化视图大小
SELECT matviewname,
       pg_size_pretty(pg_total_relation_size(matviewname::regclass)) AS total_size
FROM pg_matviews
WHERE schemaname = 'public';

-- 3. 记录刷新日志
CREATE TABLE mv_refresh_history (
    id SERIAL PRIMARY KEY,
    view_name TEXT NOT NULL,
    refresh_start TIMESTAMPTZ NOT NULL,
    refresh_end TIMESTAMPTZ,
    duration INTERVAL GENERATED ALWAYS AS (refresh_end - refresh_start) STORED,
    success BOOLEAN DEFAULT TRUE,
    error_message TEXT
);

-- 4. 带日志的刷新函数
CREATE OR REPLACE FUNCTION refresh_mv_with_log(p_view TEXT)
RETURNS void AS $$
DECLARE
    v_start TIMESTAMPTZ := clock_timestamp();
BEGIN
    EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %I', p_view);
    INSERT INTO mv_refresh_history (view_name, refresh_start, refresh_end)
    VALUES (p_view, v_start, clock_timestamp());
EXCEPTION WHEN OTHERS THEN
    INSERT INTO mv_refresh_history (view_name, refresh_start, refresh_end, success, error_message)
    VALUES (p_view, v_start, clock_timestamp(), FALSE, SQLERRM);
    RAISE;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT refresh_mv_with_log('mv_order_summary');

小结

  • 约束保证数据完整性，合理使用主键、唯一、检查、外键和排除约束
  • 默认值和生成列减少应用层逻辑，推荐使用 IDENTITY 代替 SERIAL
  • 触发器实现自动化（审计、计算、校验），注意性能影响
  • 表空间实现冷热数据分离，配合分区表效果最佳
  • 视图简化查询和权限管理，注意 CHECK OPTION 和 security_barrier
  • 索引是性能优化核心，选对类型（B-Tree / GIN / GiST / BRIN）至关重要
  • 物化视图加速复杂查询，通过 CONCURRENTLY 刷新减少锁影响