PostgreSQL中空smallint[]数组与独立表的存储占用对比咨询

空数组存储大小的澄清 #

PostgreSQL中smallint[]的存储开销分两种情况：

• NULL数组：仅占用4字节的指针空间（多数平台下），几乎无冗余。
• 空数组（'{}'::smallint[]）：包含varlena类型的4字节长度头，加上空数组结构的对齐开销，实际占用约24-32字节，和ChatGPT的说法一致。你测试出140字节的差异，大概率是混淆了NULL与空数组，或是pgAdmin显示的是整行对齐后的总大小（包含其他字段的对齐 padding），而非单个字段的真实开销。用pg_column_size()可以精确计算单个字段的存储：

  SELECT pg_column_size(NULL::smallint[]), pg_column_size('{}'::smallint[]);
  

两种方案的细节对比 #

方案a：主表新增三个smallint[]字段 #

• 实现优势：无需额外表和关联查询，代码逻辑简单直接，开发维护成本低。
• 存储注意点：若两个字段多数时候设为NULL（而非空数组），单条记录仅额外增加8字节（2个NULL指针），冗余可忽略；若强制存空数组，则每条记录额外占用约48-64字节，百万级数据会产生数十GB的冗余。

方案b：新建关联表存储数组 #

• 推荐表结构：

  CREATE TABLE main_array_ext (
      main_id INT PRIMARY KEY REFERENCES main_table(id),
      array_type SMALLINT NOT NULL, -- 标记对应主表的数组类型（1/2/3）
      array_data SMALLINT[] NOT NULL
  );
  CREATE INDEX idx_main_array_type ON main_array_ext(main_id, array_type);
  

• 存储优势：仅当主表记录有有效数组数据时才生成关联行，完全避免空值/空数组的冗余存储，适合数据量较大的场景。
• 性能注意点：查询时需通过JOIN关联，给main_id和array_type加联合索引后，关联性能几乎无损耗；若需频繁获取主表+数组数据，可考虑用LEFT JOIN保证结果完整性。

决策建议 #

• 若主表数据量小于100万，优先选方案a，将两个低频字段设为NULL，兼顾开发效率与存储合理性。
• 若主表数据量超过100万，或未来数据量会快速增长，选方案b更划算，长期存储成本更低，扩展性更好。
• 务必优先使用NULL而非空数组来表示无数据的情况，这是减少PostgreSQL可变类型字段冗余的关键。

内容的提问来源于stack exchange，提问作者Andreas M.