PostgreSQL 表设计

好的表设计要同时解决三个问题：

数据是否正确：依靠类型、约束、主外键保证。
查询是否高效：围绕实际查询建立索引。
后期是否容易维护：命名清晰、关系合理、避免重复数据。

PostgreSQL 的表设计核心包括：字段、默认值、主键、外键、约束、索引、Schema 和分区。PostgreSQL 18 数据定义文档

一、表设计的正确顺序

建议按照以下顺序设计：

业务需求
  ↓
核心实体
  ↓
实体之间的关系
  ↓
字段与数据类型
  ↓
主键、外键和约束
  ↓
高频查询方式
  ↓
索引
  ↓
是否需要分区

不要一开始就考虑字段越多越好，也不要先建索引再考虑查询。

二、从业务实体拆分表

假设需要存储：

A股基本信息
公司介绍
每日行情
题材概念
股票与题材关系

可以拆分为：

表	作用	关系
`security`	股票基本信息	核心主表
`security_profile`	公司详细资料	与股票一对一
`daily_quote`	日线行情	与股票一对多
`concept`	题材概念	独立字典表
`security_concept`	股票与题材关系	多对多中间表

不要把所有信息都塞进一张 ashare 表，否则容易出现：

公司介绍被重复保存
行情数据与基本信息混杂
每次更新名称都要修改大量记录
题材只能用逗号分隔
查询和维护越来越困难

三、Schema 设计

可以先用 Schema 对业务对象分类：

CREATE SCHEMA IF NOT EXISTS market;

之后使用完整名称：

market.security
market.daily_quote
market.concept

较大的项目可以继续拆分：

market    股票和行情
finance   财务数据
news      新闻资讯
system    系统配置
audit     审计日志

Schema 类似数据库内部的命名空间，不需要为了每个小功能都创建一个 Schema。

四、命名规范

建议统一使用：

小写字母
单词之间使用下划线
不使用中文字段名
不使用 PostgreSQL 关键字
不使用带双引号的大小写名称

五、主键设计

每张业务表原则上都应有主键。主键具有：

唯一性
非空性
自动建立唯一 B-tree 索引

PostgreSQL 主键与约束

1. 自增整数主键

security_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

适合：

单体应用
内部系统
高频关联查询
SQLAlchemy 模型

一般推荐使用 bigint identity，避免以后受 integer 范围限制。

2. UUID 主键

PostgreSQL 18 可以使用时间有序的 UUIDv7：

id uuid PRIMARY KEY DEFAULT uuidv7()

适合：

分布式系统
对外暴露的资源ID
多个数据库之间合并数据
不希望暴露业务数据数量

普通业务系统并不需要为了“看起来高级”而全部使用 UUID。

3. 自然主键

例如直接使用股票代码：

code varchar(6) PRIMARY KEY

问题是股票代码本质上属于业务数据，可能涉及：

不同交易所
证券代码复用
股票、基金、债券代码体系
代码变更
数据源格式差异

更稳妥的设计是：

security_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
exchange_code varchar(8) NOT NULL,
code varchar(6) NOT NULL,
UNIQUE (exchange_code, code)

即：

security_id：数据库内部标识
(exchange_code, code)：业务唯一标识

六、股票基本信息表

CREATE TABLE market.security (
    security_id bigint
        GENERATED ALWAYS AS IDENTITY,

    exchange_code varchar(8) NOT NULL,
    code          varchar(6) NOT NULL,
    name          text NOT NULL,
    board         text,
    industry      text,

    is_st         boolean NOT NULL DEFAULT false,
    is_active     boolean NOT NULL DEFAULT true,

    listed_on     date,
    delisted_on   date,

    extra         jsonb NOT NULL DEFAULT '{}'::jsonb,

    created_at    timestamptz NOT NULL DEFAULT now(),
    updated_at    timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT pk_security
        PRIMARY KEY (security_id),

    CONSTRAINT uq_security_exchange_code
        UNIQUE (exchange_code, code),

    CONSTRAINT ck_security_code
        CHECK (code ~ '^[0-9]{6}$'),

    CONSTRAINT ck_security_name
        CHECK (btrim(name) <> ''),

    CONSTRAINT ck_security_listing_period
        CHECK (
            delisted_on IS NULL
            OR listed_on IS NULL
            OR delisted_on >= listed_on
        ),

    CONSTRAINT ck_security_extra_object
        CHECK (jsonb_typeof(extra) = 'object')
);

关键设计：

股票代码使用 varchar(6)，不能使用整数，否则会丢失前导零。
名称使用 text，不建议使用定长 char(n)。
退市不等于删除，使用 is_active 或 delisted_on 标识。
经常查询的字段单独建列，不常用的扩展属性放入 jsonb。
交易所和代码组成业务唯一约束。

七、`NULL` 和默认值

1. `NULL` 的含义

NULL 表示：

未知
尚未获取
不适用
缺失

它不等于：

0
空字符串
false
空数组

例如公司行业未知，应保存：

industry = NULL

不要保存：

industry = ''
industry = '未知'

除非“未知”本身就是明确的业务分类。

2. 应尽量使用 `NOT NULL`

核心字段应声明：

code varchar(6) NOT NULL
name text NOT NULL
trade_date date NOT NULL

官方文档也建议，大多数表中的大部分字段通常应标记为 NOT NULL。

3. 默认值

is_active boolean NOT NULL DEFAULT true
created_at timestamptz NOT NULL DEFAULT now()
extra jsonb NOT NULL DEFAULT '{}'::jsonb

不要滥用默认值。

例如：

close_price numeric(18,4) DEFAULT 0

通常不合理，因为价格为零与尚未获得价格不是一回事。

4. `updated_at` 不会自动更新

下面的定义只在插入时生效：

updated_at timestamptz NOT NULL DEFAULT now()

执行 UPDATE 时不会自动变化。可以由应用程序更新：

UPDATE market.security
SET name = '新名称',
    updated_at = now()
WHERE security_id = 1;

也可以使用触发器：

CREATE OR REPLACE FUNCTION market.set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := clock_timestamp();
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_security_updated_at
BEFORE UPDATE ON market.security
FOR EACH ROW
EXECUTE FUNCTION market.set_updated_at();

八、约束设计

数据库约束比应用层验证更可靠，因为无论数据来自：

Python
SQLAlchemy
Excel 导入
SQL 脚本
其他服务

都必须遵守数据库约束。

1. `CHECK`

price numeric(18,4)
    CONSTRAINT ck_price_positive
    CHECK (price >= 0)

多字段检查：

CONSTRAINT ck_price_range
CHECK (high_price >= low_price)

需要注意：

CHECK (price > 0)

不能阻止 NULL，因为表达式遇到 NULL 时可能得到未知结果。因此如果不能为空，应同时声明：

price numeric(18,4) NOT NULL CHECK (price > 0)

CHECK 应只检查当前行，不能用它查询其他行或其他表。跨表一致性应该使用外键、唯一约束或触发器。

2. `UNIQUE`

单列唯一：

email text UNIQUE

多列组合唯一：

UNIQUE (security_id, trade_date)

组合唯一的含义是组合不能重复，而不是每一列单独唯一。

唯一约束会自动创建唯一 B-tree 索引，不需要再创建相同索引。

3. `UNIQUE NULLS NOT DISTINCT`

默认情况下，多个 NULL 不被认为相等，因此唯一列可以出现多个 NULL。

如果希望最多只能有一个 NULL：

external_code text UNIQUE NULLS NOT DISTINCT

九、每日行情表

CREATE TABLE market.daily_quote (
    security_id bigint NOT NULL,
    trade_date  date NOT NULL,

    open_price  numeric(18,4) NOT NULL,
    high_price  numeric(18,4) NOT NULL,
    low_price   numeric(18,4) NOT NULL,
    close_price numeric(18,4) NOT NULL,
    pre_close   numeric(18,4),

    volume      bigint NOT NULL,
    amount      numeric(24,2) NOT NULL,

    -- 统一保存为小数，例如 0.105 表示 10.5%
    change_rate numeric(12,8),
    turnover    numeric(12,8),

    created_at  timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT pk_daily_quote
        PRIMARY KEY (security_id, trade_date),

    CONSTRAINT fk_daily_quote_security
        FOREIGN KEY (security_id)
        REFERENCES market.security (security_id)
        ON DELETE RESTRICT,

    CONSTRAINT ck_daily_quote_price_nonnegative
        CHECK (
            open_price >= 0
            AND high_price >= 0
            AND low_price >= 0
            AND close_price >= 0
            AND (pre_close IS NULL OR pre_close >= 0)
        ),

    CONSTRAINT ck_daily_quote_high
        CHECK (
            high_price >= open_price
            AND high_price >= low_price
            AND high_price >= close_price
        ),

    CONSTRAINT ck_daily_quote_low
        CHECK (
            low_price <= open_price
            AND low_price <= high_price
            AND low_price <= close_price
        ),

    CONSTRAINT ck_daily_quote_volume
        CHECK (volume >= 0),

    CONSTRAINT ck_daily_quote_amount
        CHECK (amount >= 0)
);

这里不需要额外的自增 id：

id bigint GENERATED ALWAYS AS IDENTITY

因为一只股票在一个交易日只有一条日线记录：

PRIMARY KEY (security_id, trade_date)

已经能准确标识每一行。

十、外键设计

外键用于防止“孤儿数据”。

FOREIGN KEY (security_id)
REFERENCES market.security (security_id)

它保证行情表中的 security_id 必须存在于股票主表。

删除策略

策略	含义	适用场景
`RESTRICT`	有子记录时禁止删除	股票与历史行情
`NO ACTION`	默认策略，在约束检查时验证	一般业务
`CASCADE`	父记录删除时删除子记录	完全依附的明细
`SET NULL`	父记录删除时外键设为NULL	可选关系
`SET DEFAULT`	改为默认值	较少使用

股票退市时不应该删除所有历史行情，因此：

ON DELETE RESTRICT

比 ON DELETE CASCADE 更合适。

外键不会自动为引用方字段创建普通索引，因此经常联表或删除父记录时，应为子表外键列建立合适索引。不过本例的主键：

PRIMARY KEY (security_id, trade_date)

已经能支持以 security_id 开头的查询，不需要再重复建立：

CREATE INDEX ON market.daily_quote (security_id);

十一、一对一关系

公司详细资料可以单独保存：

CREATE TABLE market.security_profile (
    security_id      bigint PRIMARY KEY,
    company_feature  text,
    primary_business text,
    product_type     text,
    topic_points     text,
    raw_data         jsonb,

    updated_at       timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT fk_security_profile_security
        FOREIGN KEY (security_id)
        REFERENCES market.security (security_id)
        ON DELETE CASCADE
);

security_id 同时是：

主键
外键

这就限制了每只股票最多只有一条公司资料记录。

这种拆分适合大段文本，因为股票列表查询通常不需要同时读取主营业务和题材要点。

十二、多对多关系

一只股票可以属于多个题材，一个题材也可以包含多只股票，因此是多对多关系。

题材表

CREATE TABLE market.concept (
    concept_id bigint
        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    name       text NOT NULL,
    category   text,
    description text,

    CONSTRAINT uq_concept_name
        UNIQUE (name),

    CONSTRAINT ck_concept_name
        CHECK (btrim(name) <> '')
);

股票与题材中间表

CREATE TABLE market.security_concept (
    security_id bigint NOT NULL,
    concept_id  bigint NOT NULL,

    source      text,
    added_on    date,
    updated_at  timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT pk_security_concept
        PRIMARY KEY (security_id, concept_id),

    CONSTRAINT fk_security_concept_security
        FOREIGN KEY (security_id)
        REFERENCES market.security (security_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_security_concept_concept
        FOREIGN KEY (concept_id)
        REFERENCES market.concept (concept_id)
        ON DELETE CASCADE
);

查询某只股票的题材时，主键索引已经有效：

WHERE security_id = ?

查询某个题材包含哪些股票时，应增加反向索引：

CREATE INDEX idx_security_concept_concept_security
ON market.security_concept (concept_id, security_id);

不要用下面这种方式保存题材：

concepts text
-- '机器人,人工智能,算力,国企'

逗号分隔字符串无法可靠执行：

外键约束
精确匹配
去重
题材改名
高效索引
题材属性扩展

十三、索引设计

索引应来自查询，而不是来自字段。

先确定高频查询：

-- 查询一只股票的历史行情
WHERE security_id = ?
ORDER BY trade_date DESC;

-- 查询某个交易日全部股票
WHERE trade_date = ?;

-- 查询日期范围
WHERE trade_date BETWEEN ? AND ?;

已有主键：

PRIMARY KEY (security_id, trade_date)

它适合第一类查询。

为了支持按交易日查询全部股票，可以增加：

CREATE INDEX idx_daily_quote_trade_date_security
ON market.daily_quote (trade_date, security_id);

如果经常只读取收盘价和涨跌幅，可以考虑覆盖索引：

CREATE INDEX idx_daily_quote_trade_date_cover
ON market.daily_quote (trade_date, security_id)
INCLUDE (close_price, change_rate);

是否使用 INCLUDE 需要根据实际查询计划决定，因为它会增大索引。

索引设计原则：

等值条件通常放在联合索引前面
范围和排序字段通常放在后面
主键和唯一约束已自动创建索引
避免建立重复索引
外键引用方根据查询补充索引
jsonb、数组全文查询通常使用 GIN
上线后用 EXPLAIN (ANALYZE, BUFFERS) 验证

PostgreSQL 18 索引文档

十四、规范化与反规范化

规范化

一份事实原则上只保存一次。

例如行业名称不应该在每条行情数据中重复：

daily_quote
code
name
industry
trade_date
close_price

更合理的是：

security
    security_id
    code
    name
    industry

daily_quote
    security_id
    trade_date
    close_price

优点：

减少重复
避免更新不一致
降低存储开销
业务关系更清晰

反规范化

为了性能，可以有意识地保存少量重复数据，例如：

日报统计结果
板块每日涨跌统计
预计算技术指标
物化视图
缓存表

但必须明确：

哪份数据是源数据？
重复数据如何刷新？
更新失败如何修复？
是否允许短暂不一致？

规范化解决正确性，反规范化解决特定性能问题。没有明确性能证据时，先保持规范化。

十五、普通列与 JSONB 的边界

适合普通列的数据：

股票代码
名称
交易所
交易日期
收盘价
行业
是否ST

适合 jsonb 的数据：

不同数据源返回的扩展字段
结构不稳定的原始接口数据
很少参与关联和排序的附加属性

不推荐：

CREATE TABLE stock (
    data jsonb
);

然后把代码、名称、行业、行情全部放进 data。

这会削弱类型检查、外键、唯一约束和常规索引能力。

十六、生成列

PostgreSQL 18 支持虚拟生成列和存储生成列，虚拟生成列为默认方式。

例如计算涨跌额：

CREATE TABLE market.quote_example (
    pre_close    numeric(18,4) NOT NULL,
    close_price  numeric(18,4) NOT NULL,

    change_amount numeric(18,4)
        GENERATED ALWAYS AS (
            close_price - pre_close
        ) STORED
);

不能直接插入生成列：

INSERT INTO market.quote_example (
    pre_close,
    close_price
)
VALUES (10.00, 10.50);

适合生成列的内容：

同一行字段之间的确定性计算
始终可以由原始字段重新计算的数据
需要统一计算规则的数据

不适合：

依赖其他表的数据
依赖当前时间的数据
经常修改计算逻辑的数据

PostgreSQL 18 生成列

十七、分区表设计

当行情表增长到数千万甚至数亿条时，可以考虑按日期范围分区。

CREATE TABLE market.tick_quote (
    security_id bigint NOT NULL,
    recorded_at timestamptz NOT NULL,
    price       numeric(18,4) NOT NULL,
    volume      bigint NOT NULL,

    PRIMARY KEY (security_id, recorded_at)
)
PARTITION BY RANGE (recorded_at);

创建月分区：

CREATE TABLE market.tick_quote_2026_09
PARTITION OF market.tick_quote
FOR VALUES FROM ('2026-09-01 00:00:00+00')
         TO   ('2026-10-01 00:00:00+00');

分区适合：

数据量非常大
查询通常带日期范围
需要快速删除历史数据
需要按月归档
单表维护成本明显增加

不要因为表可能变大就提前过度分区。分区会增加：

表数量
运维复杂度
分区创建和清理任务
唯一约束限制
SQL 和迁移复杂度

分区表的主键或唯一约束通常必须包含所有分区键列。本例的主键包含 recorded_at，符合要求。PostgreSQL 表分区文档

你的分钟行情、逐笔行情更适合使用 TimescaleDB hypertable；股票基本信息、题材、行业等关系数据继续使用普通 PostgreSQL 表。

十八、当前 `ashare` 表的改进方向

如果当前表使用：

code char(6)
name char(...)

建议新表优先改为：

code varchar(6)
name text

原因：

char(n) 会填充尾部空格
Python 读取后可能需要额外处理
名称长度并不固定
varchar(6) 更适合股票代码的业务约束

已有表可以转换：

ALTER TABLE ashare
ALTER COLUMN code TYPE varchar(6)
USING btrim(code);

ALTER TABLE ashare
ALTER COLUMN name TYPE text
USING btrim(name);

正式执行前需要检查：

是否存在超过6位的代码
是否存在重复代码
是否有视图、索引、外键依赖
修改大表是否会长时间锁表
应用程序是否依赖原字段类型

十九、常见错误

1. 股票代码使用整数

code integer

会把：

变成：

应使用：

code varchar(6)

2. 所有字段都允许 `NULL`

核心字段应使用 NOT NULL。

3. 用零代替未知值

close_price DEFAULT 0

零价格和未知价格不是一回事。

4. 一张表保存所有业务

基本信息、行情、题材、财务数据应根据实体关系拆分。

5. 用逗号保存多值关系

题材、行业、标签应使用关联表，或在简单场景中使用数组。

6. 为每个字段建立索引

索引会增加插入、更新、存储和维护成本。

7. 外键全部使用级联删除

历史行情、订单、财务记录等重要数据不应因误删主记录而全部消失。

8. 认为 `DEFAULT now()` 会自动更新时间

它只负责插入默认值，更新需要应用程序或触发器处理。

9. 金额和股价使用浮点数

精确业务数据应使用 numeric。

10. 把 `jsonb` 当成万能容器

稳定、核心、经常查询的属性应使用普通列。

二十、表设计检查清单

创建一张表前，检查以下问题：

每一行代表什么？
主键是什么？
是否存在稳定的业务唯一键？
哪些字段必须 NOT NULL？
哪些字段需要默认值？
金额是否使用 numeric？
代码是否需要保留前导零？
时间应该使用 date、timestamp 还是 timestamptz？
是否需要唯一约束？
是否需要检查约束？
是否存在一对一、一对多或多对多关系？
删除父记录时子记录应该怎么处理？
哪些查询最频繁？
现有主键或唯一索引是否已经覆盖查询？
是否真的需要 jsonb？
是否真的达到需要分区的规模？
是否记录 created_at 和 updated_at？
数据变化是否应该删除，还是保留历史状态？

表设计的核心原则可以概括为：

先用数据类型表达数据，再用约束保护数据，最后用索引服务查询。表结构负责正确性，索引负责速度，应用程序负责业务流程。