PostgreSQL 数据库与表

数据库与表是PostgreSQL中最基本的存储单元，理解和掌握数据库与表的创建、管理和维护是DBA的核心技能。本文将详细介绍PostgreSQL数据库与表的设计、创建、管理和优化等内容，贴合实际生产运维场景。

数据库管理

数据库概述

PostgreSQL数据库是表、索引、视图等数据库对象的集合，每个数据库都是独立的命名空间，拥有自己的系统表和用户表。

数据库创建

基本语法

sql

CREATE DATABASE database_name
    [ [ WITH ] ( 
        [ OWNER [=] user_name ]
        [ TEMPLATE [=] template ]
        [ ENCODING [=] encoding ]
        [ LOCALE [=] locale ]
        [ LC_COLLATE [=] lc_collate ]
        [ LC_CTYPE [=] lc_ctype ]
        [ TABLESPACE [=] tablespace_name ]
        [ ALLOW_CONNECTIONS [=] allowconn ]
        [ CONNECTION LIMIT [=] connlimit ]
        [ IS_TEMPLATE [=] istemplate ]
    ) ];

实际示例

sql

-- 创建基本数据库
CREATE DATABASE mydb;

-- 创建带有参数的数据库
CREATE DATABASE mydb_prod
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = 100;

数据库查看

sql

-- 查看所有数据库
\l

-- 查看数据库详细信息
\l+

-- 使用SQL查询数据库信息
SELECT datname, datowner, encoding, datcollate, datctype 
FROM pg_database;

-- 查看当前连接的数据库
SELECT current_database();

数据库连接

bash

# 使用psql连接数据库
psql -d database_name -U username -h host -p port

# 在psql中切换数据库
\c database_name

数据库修改

sql

-- 修改数据库所有者
ALTER DATABASE mydb OWNER TO new_owner;

-- 修改数据库名称
ALTER DATABASE mydb RENAME TO new_dbname;

-- 修改数据库连接限制
ALTER DATABASE mydb CONNECTION LIMIT 200;

-- 修改数据库默认表空间
ALTER DATABASE mydb SET TABLESPACE new_tablespace;

数据库删除

sql

-- 删除数据库（需断开所有连接）
DROP DATABASE IF EXISTS mydb;

-- 强制删除数据库（PostgreSQL 13+）
DROP DATABASE IF EXISTS mydb WITH (FORCE);

数据库模板

PostgreSQL使用模板数据库创建新数据库，默认有两个模板数据库：

template0：原始模板，包含基本的系统表，不包含用户创建的对象
template1：默认模板，可以包含自定义对象，新数据库默认从template1创建

sql

-- 从template0创建数据库
CREATE DATABASE mydb WITH TEMPLATE template0;

-- 修改template1
CREATE TABLE template1.mytable (id INT);
-- 后续创建的数据库都会包含mytable表

表设计与创建

表设计原则

遵循数据库设计范式：
- 第一范式（1NF）：确保每列原子性
- 第二范式（2NF）：消除部分依赖
- 第三范式（3NF）：消除传递依赖
- 适当反范式：为了性能考虑，可以适当冗余数据
选择合适的数据类型：
- 优先使用更具体的数据类型
- 避免使用TEXT存储小文本
- 合理使用数字类型，避免不必要的精度
- 日期时间类型选择合适的精度
设计合适的约束：
- 主键约束：唯一标识行
- 外键约束：维护表间关系
- 唯一约束：确保列值唯一
- 检查约束：验证数据有效性
- 非空约束：避免NULL值导致的问题
考虑性能因素：
- 合理设计索引
- 考虑表分区策略
- 避免过度设计复杂表结构
- 考虑数据增长趋势

表创建语法

sql

CREATE TABLE table_name (
    column_name data_type [CONSTRAINT constraint_name] constraint_type,
    ...
    [TABLESPACE tablespace_name]
    [WITH (storage_parameter = value [, ... ])]
    [ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP}]
    [TABLESPACE tablespace_name]
);

实际示例

sql

-- 创建用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$')
);

-- 创建订单表，带有外键
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    shipping_address TEXT NOT NULL,
    payment_method VARCHAR(50) NOT NULL
);

-- 创建带有存储参数的表
CREATE TABLE large_table (
    id SERIAL PRIMARY KEY,
    data TEXT
) WITH (
    fillfactor = 70,
    autovacuum_enabled = true,
    toast.autovacuum_enabled = true
);

表查看

sql

-- 查看当前数据库中的所有表
\dt

-- 查看表的详细信息
\dt+

-- 查看表结构
\d table_name

-- 使用SQL查询表信息
SELECT table_name, column_name, data_type 
FROM information_schema.columns 
WHERE table_schema = 'public' 
ORDER BY table_name, ordinal_position;

表管理

表修改

sql

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 修改列类型
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(30);

-- 修改列默认值
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT FALSE;

-- 添加约束
ALTER TABLE users ADD CONSTRAINT users_phone_unique UNIQUE (phone);

-- 删除约束
ALTER TABLE users DROP CONSTRAINT users_phone_unique;

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 修改表名
ALTER TABLE users RENAME TO customers;

-- 修改列名
ALTER TABLE customers RENAME COLUMN username TO user_name;

表删除

sql

-- 删除表
DROP TABLE IF EXISTS old_table;

-- 级联删除（删除所有依赖对象）
DROP TABLE IF EXISTS table_name CASCADE;

-- 限制删除（如果有依赖对象则失败）
DROP TABLE IF EXISTS table_name RESTRICT;

表截断

sql

-- 截断表（清空表数据，保留表结构）
TRUNCATE TABLE table_name;

-- 截断多个表
TRUNCATE TABLE table1, table2;

-- 级联截断（截断所有依赖表）
TRUNCATE TABLE table_name CASCADE;

-- 重置序列
TRUNCATE TABLE table_name RESTART IDENTITY;

表复制

sql

-- 复制表结构（不包含数据和约束）
CREATE TABLE new_table AS SELECT * FROM existing_table WHERE 1=0;

-- 复制表结构和数据
CREATE TABLE new_table AS SELECT * FROM existing_table;

-- 复制表结构、数据和约束
CREATE TABLE new_table LIKE existing_table INCLUDING ALL;
INSERT INTO new_table SELECT * FROM existing_table;

表维护

统计信息更新

sql

-- 更新单个表的统计信息
ANALYZE table_name;

-- 更新所有表的统计信息
ANALYZE;

-- 详细分析（显示进度）
ANALYZE VERBOSE table_name;

-- 只分析特定列
ANALYZE table_name (column1, column2);

VACUUM操作

sql

-- 普通VACUUM（回收空间，不锁定表）
VACUUM table_name;

-- 详细VACUUM
VACUUM VERBOSE table_name;

-- VACUUM并更新统计信息
VACUUM ANALYZE table_name;

-- 完全VACUUM（重写表，需要排他锁）
VACUUM FULL table_name;

-- 强制VACUUM
VACUUM FORCE table_name;

表空间管理

sql

-- 移动表到新表空间
ALTER TABLE table_name SET TABLESPACE new_tablespace;

-- 移动索引到新表空间
ALTER INDEX index_name SET TABLESPACE new_tablespace;

-- 查看表所在的表空间
SELECT table_name, tablespace_name 
FROM information_schema.tables 
WHERE table_schema = 'public';

表膨胀检查

sql

-- 安装pgstattuple扩展
CREATE EXTENSION pgstattuple;

-- 检查表膨胀情况
SELECT * FROM pgstattuple('table_name');

-- 检查索引膨胀情况
SELECT * FROM pgstatindex('index_name');

-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

-- 查看数据库大小
SELECT pg_size_pretty(pg_database_size(current_database()));

数据操作

插入数据

sql

-- 插入单行数据
INSERT INTO users (username, email, password_hash) 
VALUES ('john_doe', 'john@example.com', 'hashed_password');

-- 插入多行数据
INSERT INTO users (username, email, password_hash) 
VALUES 
    ('jane_smith', 'jane@example.com', 'hashed_password1'),
    ('bob_johnson', 'bob@example.com', 'hashed_password2');

-- 从查询结果插入数据
INSERT INTO new_users 
SELECT * FROM old_users WHERE created_at > '2023-01-01';

更新数据

sql

-- 更新单行数据
UPDATE users 
SET full_name = 'John Doe', updated_at = CURRENT_TIMESTAMP 
WHERE id = 1;

-- 更新多行数据
UPDATE users 
SET is_active = false 
WHERE last_login < '2023-01-01';

-- 使用FROM子句更新
UPDATE orders o 
SET status = 'shipped' 
FROM users u 
WHERE o.user_id = u.id AND u.username = 'john_doe';

删除数据

sql

-- 删除单行数据
DELETE FROM users WHERE id = 1;

-- 删除多行数据
DELETE FROM users WHERE is_active = false;

-- 使用USING子句删除
DELETE FROM orders o 
USING users u 
WHERE o.user_id = u.id AND u.username = 'john_doe';

查询数据

sql

-- 基本查询
SELECT * FROM users;

-- 条件查询
SELECT * FROM users WHERE is_active = true;

-- 排序查询
SELECT * FROM users ORDER BY created_at DESC;

-- 限制查询结果
SELECT * FROM users LIMIT 10 OFFSET 20;

-- 分组查询
SELECT status, COUNT(*) FROM orders GROUP BY status;

-- 连接查询
SELECT u.username, o.order_date, o.total_amount 
FROM users u 
JOIN orders o ON u.id = o.user_id;

约束管理

约束类型

主键约束：
sql
```
ALTER TABLE users ADD PRIMARY KEY (id);
```

外键约束：

sql

ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

唯一约束：
sql
```
ALTER TABLE users ADD UNIQUE (email);
```

检查约束：

sql

ALTER TABLE products ADD CHECK (price > 0);

非空约束：

sql

ALTER TABLE users ALTER COLUMN username SET NOT NULL;

约束查看

sql

-- 查看表的约束
\d+ table_name

-- 使用SQL查询约束
SELECT 
    constraint_name, constraint_type, column_name 
FROM 
    information_schema.constraint_column_usage 
WHERE 
    table_name = 'table_name';

约束禁用与启用

sql

-- 禁用外键约束
ALTER TABLE orders DISABLE TRIGGER ALL;

-- 启用外键约束
ALTER TABLE orders ENABLE TRIGGER ALL;

-- 验证约束
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

序列管理

序列创建

sql

-- 创建序列
CREATE SEQUENCE my_sequence
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

-- 使用序列
CREATE TABLE my_table (
    id INTEGER DEFAULT nextval('my_sequence'),
    name VARCHAR(50)
);

序列操作

sql

-- 获取序列当前值
SELECT currval('my_sequence');

-- 获取序列下一个值
SELECT nextval('my_sequence');

-- 设置序列值
SELECT setval('my_sequence', 100);

-- 重置序列
ALTER SEQUENCE my_sequence RESTART WITH 1;

-- 序列与SERIAL的关系
-- SERIAL类型自动创建序列，序列名为table_name_column_name_seq
SELECT nextval('users_id_seq');

分区表

分区表创建

sql

-- 创建分区表（范围分区）
CREATE TABLE sales (
    id SERIAL,
    product_id INTEGER NOT NULL,
    sale_date DATE NOT NULL,
    amount NUMERIC(10, 2) NOT NULL
) PARTITION BY RANGE (sale_date);

-- 创建分区
CREATE TABLE sales_2023_01 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

CREATE TABLE sales_2023_02 PARTITION OF sales
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

分区表操作

sql

-- 插入数据（自动路由到对应分区）
INSERT INTO sales (product_id, sale_date, amount) 
VALUES (1, '2023-01-15', 100.00);

-- 查询数据（自动扫描相关分区）
SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';

-- 添加新分区
CREATE TABLE sales_2023_03 PARTITION OF sales
    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');

-- 删除旧分区
DROP TABLE sales_2023_01;

最佳实践

数据库管理最佳实践

合理规划数据库：
- 根据业务需求创建数据库
- 避免单数据库过大
- 合理设置连接限制
定期备份：
- 实施完整备份策略
- 定期测试恢复流程
- 存储备份到安全位置
监控数据库：
- 监控数据库大小
- 监控连接数
- 监控查询性能

表设计最佳实践

合理设计表结构：
- 遵循数据库设计范式
- 选择合适的数据类型
- 适当添加约束
优化表性能：
- 合理设计索引
- 考虑表分区
- 定期更新统计信息
- 定期执行VACUUM
命名规范：
- 使用有意义的表名和列名
- 采用一致的命名风格（如snake_case）
- 避免使用保留字

数据操作最佳实践

批量操作：
- 批量插入数据，减少网络开销
- 使用COPY命令导入大量数据
- 避免循环单条插入
查询优化：
- 只查询需要的列
- 避免SELECT *
- 使用索引优化查询
- 合理使用连接
事务管理：
- 保持事务简短
- 避免长事务锁定表
- 合理设置事务隔离级别

案例分析：大表优化

背景：某电商平台的订单表包含超过1亿行数据，查询性能下降明显。

分析：

全表扫描时间过长
索引大小过大
插入性能下降
备份和恢复时间过长

优化措施：

实施分区表：
- 按订单日期进行范围分区，每个月一个分区
- 将历史数据分区存储在低成本存储上
- 查询时只扫描相关分区
优化索引：
- 保留常用查询的索引
- 删除无用的索引
- 为分区表创建本地索引
数据归档：
- 将超过2年的历史数据归档到外部表
- 减少主表数据量
批量操作优化：
- 使用COPY命令导入数据
- 批量更新和删除操作

结果：

查询性能提高10倍
插入性能提高5倍
备份时间从4小时减少到30分钟
存储空间减少60%

总结

PostgreSQL数据库与表的管理是DBA日常工作的核心内容，包括数据库创建、表设计、数据操作、维护和优化等。合理的数据库和表设计能够提高系统性能，降低维护成本，确保数据的安全性和可靠性。

在实际生产环境中，DBA需要根据业务需求和系统资源情况，合理规划数据库结构，优化表设计，实施有效的维护策略，确保数据库系统的高性能和可靠性。同时，要关注数据增长趋势，及时调整存储策略和优化方案，以适应不断变化的业务需求。

PostgreSQL 数据库与表 ​

数据库管理 ​

数据库概述 ​

数据库创建 ​

基本语法 ​

实际示例 ​

数据库查看 ​

数据库连接 ​

数据库修改 ​

数据库删除 ​

数据库模板 ​

表设计与创建 ​

表设计原则 ​

表创建语法 ​

实际示例 ​

表查看 ​

表管理 ​

表修改 ​

表删除 ​

表截断 ​

表复制 ​

表维护 ​

统计信息更新 ​

VACUUM操作 ​

表空间管理 ​

表膨胀检查 ​

数据操作 ​

插入数据 ​

更新数据 ​

删除数据 ​

查询数据 ​

约束管理 ​

约束类型 ​

约束查看 ​

约束禁用与启用 ​

序列管理 ​

序列创建 ​

序列操作 ​

分区表 ​

分区表创建 ​

分区表操作 ​

最佳实践 ​

数据库管理最佳实践 ​

表设计最佳实践 ​

数据操作最佳实践 ​

案例分析：大表优化 ​

总结 ​

PostgreSQL 数据库与表

数据库管理

数据库概述

数据库创建

基本语法

实际示例

数据库查看

数据库连接

数据库修改

数据库删除

数据库模板

表设计与创建

表设计原则

表创建语法

实际示例

表查看

表管理

表修改

表删除

表截断

表复制

表维护

统计信息更新

VACUUM操作

表空间管理

表膨胀检查

数据操作

插入数据

更新数据

删除数据

查询数据

约束管理

约束类型

约束查看

约束禁用与启用

序列管理

序列创建

序列操作

分区表

分区表创建

分区表操作

最佳实践

数据库管理最佳实践

表设计最佳实践

数据操作最佳实践

案例分析：大表优化

总结