Vertica 列数据类型与长度优化¶
作者：JiangChong | 撰写时间：2026年05月
适用场景：建表时不确定 VARCHAR 该设多长、NUMERIC 精度该设多少；或发现集群存储增长异常、查询内存占用偏高，怀疑列定义过于宽松导致资源浪费。
关联文章：
- 列存架构基础原理：MPP 列存引擎的架构设计哲学
- 宽表特殊场景优化：Vertica 宽表存储与查询优化
- NUMERIC 与 Oracle 差异：Vertica NUMERIC 类型尾随零显示与 Oracle 的差异
- 编码策略优化：Vertica 列编码策略优化
- VARCHAR 替代方案：Vertica VARCHAR 类型使用规范与替代方案
- 数据迁移方法：SQL、数据、存储过程迁移方法论
理解全文脉络¶
本文按「原理 → 监控 → 诊断 → 修复 → 案例 → 工具箱」组织。如果你只是想快速检查集群是否有列定义过大的问题，直接跳到第 7 节「快速诊断 SQL 工具箱」执行前三条 SQL，然后对照第 8 节「最佳实践清单」逐条整改。如果你想深入理解为什么列定义会影响性能和存储，从第 1 节开始读。
1. 原理理解¶
1.1 Vertica 列式存储与数据类型的关系¶
Vertica 是列式存储数据库，每一列的数据在物理上独立存放。这意味着每一列的数据类型定义直接影响三个维度的资源消耗：
| 维度 | 影响机制 | 典型表现 |
|---|---|---|
| 磁盘存储 | 编码压缩效率取决于数据类型和基数；过宽的定义阻止高效编码选择 | VARCHAR(65000) 无法使用 GLOBAL_DICT，只能用 String_LZO |
| 查询内存 | 执行引擎按定义长度而非实际数据长度为列分配内存缓冲区 | 定义 VARCHAR(1000) 但实际数据最长 50 字节 → 20 倍内存浪费 |
| Catalog 元数据 | 每列的类型定义信息常驻内存，列越多影响越大 | 1000 列表 × 每列元数据开销 → 显著 Catalog 膨胀 |
关键区别：Vertica vs 行式数据库。 在行式数据库（如 MySQL/PostgreSQL）中，VARCHAR 通常按实际数据长度存储（变长字段），定义长度只作为约束检查。但在 Vertica 列存架构下，编码策略的选择与数据类型高度绑定——VARCHAR(100) 和 VARCHAR(65000) 虽然存储同样长度的字符串，Vertica 给它们分配的默认编码可能完全不同，压缩效率天差地别。
1.2 VARCHAR 长度的存储与内存开销¶
Vertica 的 VARCHAR 类型在内部存储时需要记录实际长度。从 v_catalog.types 可知，VARCHAR 最大长度为 65000 字节（COLUMN_SIZE = 65000）。但这里有一个容易误解的地方：
- 磁盘存储：Vertica 对 VARCHAR 按实际数据长度 + 少量长度前缀存储，定义长度本身不直接增加磁盘占用。但定义长度影响编码选择，从而间接影响压缩率。
- 查询内存：执行引擎在解析和中间结果传输时，可能按定义的最大长度预留缓冲区。如果表中有 100 列
VARCHAR(65000)，即使实际每列只有几十字节的数据，查询时的内存开销也是巨大的。 - 哈希计算：如 Vertica 性能调优 - 2 使用系统表排查查询故障 中提到的，拥有 32 个
VARCHAR(1000)分段列会使哈希算法变得复杂，大量消耗 CPU。
VARCHAR 编码策略与长度的关系（来源：Vertica 宽表存储与查询优化）：
| VARCHAR 长度 | 默认编码 | 压缩率 | 适用场景 |
|---|---|---|---|
| ≤ 100 字节 | AUTO → 可能选择 GLOBAL_DICT（低基数列）或 String_LZO |
取决于基数 | 短编码、枚举值、短文本 |
| 100 ~ 1000 | AUTO → 通常 String_LZO |
2:1 ~ 5:1 | 一般文本字段 |
| > 1000 | 推荐显式 ZSTD_COMP 或 GZIP_COMP |
5:1 ~ 10:1 | 日志、描述、JSON |
1.3 NUMERIC 精度的存储与计算开销¶
NUMERIC 类型是定点数，其精度（precision）和标度（scale）直接决定每个值的物理存储大小：
| NUMERIC 精度范围 | 存储字节数 | 默认编码 | 说明 |
|---|---|---|---|
| ≤ 18 | 8 字节 | Delta Int Pack（高压缩率） |
与 BIGINT 等价，计算高效 |
| 19 ~ 38 | 16 字节 | LZO（通用压缩） |
需要 128 位整数运算，明显慢于 64 位 |
这是最容易被忽略的性能差异。从 NUMERIC(18,2) 改到 NUMERIC(19,2)，不仅存储翻倍（8 → 16 字节），编码策略从高效的 Delta Int Pack 变为通用 LZO，JOIN 和 GROUP BY 时的哈希计算也从 64 位变为 128 位。精确一位之差，性能可能差一个数量级。
来源：编码策略表来自 Vertica 性能调优 - 2 使用系统表排查查询故障，第 999-1013 行。
1.4 列定义过大的来源总结¶
| 来源 | 典型表现 | 发生原因 |
|---|---|---|
| 从 Oracle/MySQL 迁移 | NUMBER → NUMERIC(38,10)，VARCHAR2(4000) → VARCHAR(4000) |
迁移工具按源库最大定义映射，未根据实际数据调整 |
| 建表工具默认值 | 所有 VARCHAR 统一设为 VARCHAR(1000) 或 VARCHAR(65000) |
开发人员「留足余量」，未分析实际数据长度 |
| ETL 宽表设计 | 几百列的 VARCHAR(500) |
从 Hive/Spark 宽表直接映射，未做 Vertica 适配 |
| 复合主键/分段键 | 多个 VARCHAR(100) 作为分段列 |
分段列应尽量选择整数类型，VARCHAR 分段代价高 |
| 浮点精度误用 | 金额字段用 FLOAT 而非 NUMERIC |
FLOAT 是近似值，金额计算有精度风险；但过度使用 NUMERIC 也有性能代价 |
2. 系统级监控（从宏观入手）¶
2.1 全局列定义概览——找出所有 VARCHAR 和 NUMERIC 列的定义¶
首先从全局视角了解集群中所有表的列类型分布。以下 SQL 统计每种数据类型在各表中的使用情况，并重点标注定义可能过大的列：
-- 统计所有用户表的 VARCHAR 和 NUMERIC 列定义
SELECT
c.table_schema,
c.table_name,
c.column_name,
c.data_type,
c.data_type_length,
c.numeric_precision,
c.numeric_scale,
c.character_maximum_length
FROM v_catalog.columns c
JOIN v_catalog.tables t
ON c.table_id = t.table_id
AND c.table_schema = t.table_schema
WHERE t.is_system_table = false AND t.is_temp_table = false
AND (
(c.data_type ilike 'varchar%' AND c.data_type_length > 500)
OR
(c.data_type ilike 'numeric%' AND c.numeric_precision > 18)
)
ORDER BY
CASE WHEN c.data_type ILIKE 'varchar%' THEN c.data_type_length ELSE 0 END DESC,
CASE WHEN c.data_type ILIKE 'numeric%' THEN c.numeric_precision ELSE 0 END DESC;
如何解读结果：
VARCHAR且data_type_length > 500：重点关注data_type_length = 65000的行——这些列几乎一定是定义过大。同时也注意data_type_length = 1000的列，如果实际数据只有几十字节，同样需要优化。NUMERIC且numeric_precision > 18：这些列每个值占用 16 字节，编码为 LZO 而非 Delta Int Pack。如果精度确实不需要超过 18 位，降下来收益明显。NUMERIC且numeric_precision = 38：这是 Oracle NUMBER 类型的默认映射结果，几乎一定是过度定义。
2.2 实际数据长度 vs 定义长度——找到浪费最大的列¶
光看定义不足以判断问题严重性。关键是 定义长度与实际数据长度的差距。以下 SQL 分两步：第一步生成检查脚本，第二步执行脚本拿到所有 VARCHAR 列的实际最大字节数。
步骤 A — 生成一次检查所有 VARCHAR 列的 SQL（将输出复制后执行即可）：
-- 将 'your_schema' / 'your_table' 替换为实际值
SELECT
' SELECT ''' || c.column_name
|| ''' AS col, MAX(OCTET_LENGTH(' || c.column_name
|| ')) AS actual_bytes, ' || c.data_type_length
|| ' AS defined_bytes FROM ' || c.table_schema || '.' || c.table_name
|| CASE WHEN ROW_NUMBER() OVER (ORDER BY c.column_name) = COUNT(*) OVER ()
THEN ';' ELSE ' UNION ALL' END AS check_sql
FROM v_catalog.columns c
JOIN v_catalog.tables t
ON c.table_id = t.table_id AND c.table_schema = t.table_schema
WHERE t.is_system_table = false AND t.is_temp_table = false
AND c.table_schema = 'your_schema'
AND c.table_name = 'your_table'
AND c.data_type ILIKE 'varchar%'
ORDER BY c.data_type_length DESC;
将输出列
check_sql的内容完整复制并执行，即可拿到该表所有 VARCHAR 列的实际字节数 vs 定义字节数。
步骤 B — 如果想单列精细对比（含浪费字节计算），用这个：
-- 将 'your_schema.your_table' / 'your_column' 替换为实际值
WITH col_def AS (
SELECT data_type_length FROM v_catalog.columns
WHERE table_schema = 'your_schema'
AND table_name = 'your_table'
AND column_name = 'your_column'
)
SELECT
MAX(OCTET_LENGTH(t.your_column)) AS max_actual_bytes,
d.data_type_length AS defined_bytes,
d.data_type_length - MAX(OCTET_LENGTH(t.your_column)) AS wasted_bytes_per_row
FROM your_schema.your_table t
CROSS JOIN col_def d
GROUP BY d.data_type_length;
如何解读结果：
- 步骤 A 的输出：拿到一个完整的 UNION ALL 查询，每行对应一个
VARCHAR列。执行后对比actual_bytes和defined_bytes——defined_bytes - actual_bytes > 100的列值得缩小定义。 - 步骤 B（单列）的
wasted_bytes_per_row为正值且 > 100：该列可以安全缩小。例如定义了VARCHAR(1000)，实际最长只有 50 字节，浪费了 950 字节/行/查询缓冲。 max_actual_bytes接近defined_bytes：说明定义合理，但也要确认是否有截断风险。建议保留 20% 余量。- 对于 NUMERIC 列：同样用生成器一次检查所有 NUMERIC 列的实际精度需求：
步骤 C — 生成一次检查所有 NUMERIC 列的 SQL：
-- 将 'your_schema' / 'your_table' 替换为实际值
SELECT
' SELECT ''' || c.column_name
|| ''' AS col, MAX(LENGTH(FLOOR(ABS(' || c.column_name
|| '))::VARCHAR)) AS max_int_digits, MAX(LENGTH(SPLIT_PART('
|| c.column_name || '::VARCHAR, ''.'', 2))) AS max_scale_digits, '
|| c.numeric_precision || ' AS def_precision, ' || c.numeric_scale
|| ' AS def_scale FROM ' || c.table_schema || '.' || c.table_name
|| CASE WHEN ROW_NUMBER() OVER (ORDER BY c.column_name) = COUNT(*) OVER ()
THEN ';' ELSE ' UNION ALL' END AS check_sql
FROM v_catalog.columns c
JOIN v_catalog.tables t
ON c.table_id = t.table_id AND c.table_schema = t.table_schema
WHERE t.is_system_table = false AND t.is_temp_table = false
AND c.table_schema = 'your_schema'
AND c.table_name = 'your_table'
AND c.data_type ILIKE 'numeric%'
ORDER BY c.numeric_precision DESC;
输出每列一行 UNION ALL，含
max_int_digits（实际整数最大位数）、max_scale_digits（实际小数最大位数）、def_precision/def_scale（当前定义）。整数位数 ≤ 18 但def_precision > 18的列应优先缩小精度。
2.3 按列查看存储占用¶
结合 v_monitor.column_storage 查看各列的实际磁盘占用，关联 v_catalog.projection_columns 获取数据类型信息：
-- 查看所有列的实际存储占用（按磁盘空间降序）
SELECT
cs.anchor_table_schema,
cs.anchor_table_name,
cs.anchor_table_column_name,
pc.data_type,
SUM(cs.used_bytes) / (1024*1024)::NUMERIC(10,2) AS total_mb,
SUM(cs.ros_count) AS total_ros_count
FROM v_monitor.column_storage cs
JOIN v_catalog.projection_columns pc
ON cs.projection_id = pc.projection_id
AND cs.column_id = pc.column_id
GROUP BY 1, 2, 3, 4
ORDER BY total_mb DESC
LIMIT 50;
如何解读结果：
total_mb> 1 GB 的单列：如 Vertica 宽表存储与查询优化所述，可能是VARCHAR(65000)且存储了大量数据，或者编码策略不当。应重点优化。total_ros_count很大：说明该列有大量 ROS container，可能影响查询时的文件打开数。对于VARCHAR列，这也可能意味着编码效果不佳。- 比较同表中不同类型列的
total_mb：如果VARCHAR(100)的列存储比INTEGER列大几倍但实际数据很短，说明编码效率有问题。
3. 逐步定位根因（从宏观到微观）¶
3.1 步骤 1：找出定义过大的所有 VARCHAR 列¶
做什么：全库扫描，筛选出所有 VARCHAR 列定义超出合理范围的情况。
SELECT
c.table_schema,
c.table_name,
c.column_name,
c.data_type,
c.data_type_length,
c.character_maximum_length,
pc.encoding_type,
-- 分级标注严重程度
CASE
WHEN c.data_type_length >= 10000 THEN 'CRITICAL — 500+ 倍浪费'
WHEN c.data_type_length >= 5000 THEN 'HIGH — 50+ 倍浪费'
WHEN c.data_type_length >= 1000 THEN 'MEDIUM — 10+ 倍浪费'
WHEN c.data_type_length >= 500 THEN 'LOW — 需确认实际数据长度'
ELSE 'OK'
END AS severity
FROM v_catalog.columns c
JOIN v_catalog.tables t
ON c.table_id = t.table_id AND c.table_schema = t.table_schema
LEFT JOIN (
SELECT table_id, table_schema, table_column_name, encoding_type,
ROW_NUMBER() OVER (PARTITION BY table_id, table_column_name ORDER BY projection_id) AS rn
FROM v_catalog.projection_columns
) pc ON c.table_id = pc.table_id
AND c.table_schema = pc.table_schema
AND c.column_name = pc.table_column_name
AND pc.rn = 1
WHERE t.is_system_table = false AND t.is_temp_table = false
AND c.data_type ILIKE 'varchar%'
AND c.data_type_length > 500
ORDER BY c.data_type_length DESC;
如何解读：
- CRITICAL（≥10000）：这些列几乎一定定义过大。典型的
VARCHAR(65000)就是建表工具直接用了最大值。每条查询在内存中为这些列预留的缓冲区是实际需要的数百倍。 - HIGH（5000~9999）：很可能是从 Oracle
VARCHAR2(4000)或 SQL ServerNVARCHAR(MAX)迁移过来的。需要分析实际数据长度后缩小。 - MEDIUM（1000~4999）：最常见的情况。开发人员给「备注」「描述」等字段留了过大余量。
3.2 步骤 2：检查 NUMERIC 精度浪费¶
做什么：找出可以从 NUMERIC(≥19, s) 降到 NUMERIC(≤18, s) 的列。
SELECT
c.table_schema,
c.table_name,
c.column_name,
c.data_type,
c.numeric_precision,
c.numeric_scale,
-- 估算每行存储字节数
CASE WHEN c.numeric_precision <= 18 THEN 8 ELSE 16 END AS bytes_per_value,
pc.encoding_type
FROM v_catalog.columns c
JOIN v_catalog.tables t
ON c.table_id = t.table_id AND c.table_schema = t.table_schema
LEFT JOIN (
SELECT table_id, table_schema, table_column_name, encoding_type,
ROW_NUMBER() OVER (PARTITION BY table_id, table_column_name ORDER BY projection_id) AS rn
FROM v_catalog.projection_columns
) pc ON c.table_id = pc.table_id
AND c.table_schema = pc.table_schema
AND c.column_name = pc.table_column_name
AND pc.rn = 1
WHERE t.is_system_table = false AND t.is_temp_table = false
AND c.data_type ILIKE 'numeric%'
AND c.numeric_precision > 18 -- 超过 18 位用 16 字节 + LZO 编码
ORDER BY c.numeric_precision DESC;
如何解读：
numeric_precision = 38：Oracle NUMBER 的默认精度映射。大多数业务场景不需要 38 位精度。降到 18 以内可以直接将编码从 LZO 切换为 Delta Int Pack，且每值存储从 16 字节降到 8 字节。bytes_per_value = 16且encoding_type = 'LZO'：说明该列因为精度 > 18 而无法使用更高效的 Delta Int Pack 编码。如果能降低精度，收益是双重的：存储减半 + 编码优化。numeric_scale：对于不需要小数位的整数（如 ID、数量），考虑用INTEGER或BIGINT替代NUMERIC(n,0)。
3.3 步骤 3：对比定义长度与实际数据¶
做什么：对于步骤 1 中标记出的 VARCHAR 列，逐个抽样确认实际最大长度。
由于需要动态生成 SQL，这里用脚本方式或逐表手动确认。对于资源允许的情况，可以用以下方式批量采样：
-- 此 SQL 生成采样语句，实际执行需在客户端或脚本中循环
-- 输出格式：SELECT MAX(LENGTH(col)) AS max_len FROM schema.table;
SELECT
'SELECT ''' || c.table_schema || '.' || c.table_name || '.' || c.column_name ||
''' AS column_full_name, MAX(OCTET_LENGTH(' || c.column_name || ')) AS max_actual_len, ' ||
c.data_type_length || ' AS defined_len' ||
' FROM ' || c.table_schema || '.' || c.table_name || ';'
AS sample_query
FROM v_catalog.columns c
JOIN v_catalog.tables t
ON c.table_id = t.table_id AND c.table_schema = t.table_schema
WHERE t.is_system_table = false AND t.is_temp_table = false
AND c.data_type ILIKE 'varchar%'
AND c.data_type_length > 500
ORDER BY c.data_type_length DESC
LIMIT 20; -- 限制输出数量，避免生成过多采样语句
如何解读：
- 将输出的
sample_query逐条执行。如果某列的max_actual_len远小于defined_len（如定义了 1000，实际最长 50），则可以安全缩小。 - 如果
max_actual_len接近defined_len，保留当前定义，并确认业务上是否需要额外余量。
4. 解决方案（从快速见效到根本治理）¶
4.1 立即措施：缩小 VARCHAR 列定义¶
Vertica 官方支持 ALTER COLUMN ... SET DATA TYPE 对所有字符类型（CHAR / VARCHAR / LONG VARCHAR）的任意互转，包括缩小长度（如 VARCHAR(1000) → VARCHAR(100)）。这是纯元数据操作，不重写数据文件，速度极快。
来源：Vertica 官方文档 — Changing Column Width (v26.1.x)，字符类型（CHAR / VARCHAR / LONG VARCHAR）之间全部互转均官方支持，包括扩大和缩小。
-- 缩小 VARCHAR 列定义（仅当现有数据字节数 ≤ 新定义长度）
ALTER TABLE schema_name.table_name
ALTER COLUMN column_name SET DATA TYPE VARCHAR(100);
注意事项：
- 数据不截断：如果表中已有数据的
OCTET_LENGTH超过新定义，ALTER COLUMN会失败。建议先用 2.2 节步骤 A/B 确认实际最大字节数。 - 无数据重写：缩小 VARCHAR 定义是纯元数据变更，对大表也瞬间完成。
- 编码不会自动调整：
ALTER COLUMN改的是列定义（元数据），不会改变已有 projection 的编码方式。投影的编码在创建时缺省使用AUTO，之后只能通过以下三种方式进行优化：- ① 运行 Database Designer 优化并刷新投影；
- ②
ALTER TABLE ... ALTER COLUMN ... ENCODING rle PROJECTIONS (...)显式指定编码； - ③ 方案 C 重建表（
INSERT ... SELECT到新表，创建新表时对projection编码进行优化）。
4.2 立即措施：优化 NUMERIC 精度¶
Vertica 对 NUMERIC 的 ALTER COLUMN ... SET DATA TYPE 规则比官方文档严格得多。实测 v26.1.0-2 的完整规则是：
- ✅ scale 不变 + precision ≤ 37 + 同 storage class 内 → 支持
- ❌ precision = 38 涉及其中任一侧 → 不支持（38 是硬上限，不可 ALTER）
- ❌ 跨越 18 边界（≤18 ↔ ≥19）→ 不支持（storage class 变更：8↔16 字节、Delta↔LZO）
- ❌ scale 变化 → 不支持
| 操作 | precision | scale | storage class | 结果 |
|---|---|---|---|---|
NUMERIC(3,2) → NUMERIC(5,2) |
3→5 | 2→2 ✅ | ≤18→≤18 ✅ | ✅ |
NUMERIC(22,6) → NUMERIC(20,6) |
22→20 | 6→6 ✅ | ≥19→≥19 ✅ | ✅ |
NUMERIC(36,6) → NUMERIC(34,6) |
36→34 | 6→6 ✅ | ≥19→≥19 ✅ | ✅ |
NUMERIC(37,6) → NUMERIC(36,6) |
37→36 | 6→6 ✅ | ≥19→≥19 ✅ | ✅ |
NUMERIC(38,6) → NUMERIC(30,6) |
38→30 | 6→6 ✅ | ≥19→≥19 ✅ | ❌ (38 硬上限) |
NUMERIC(37,6) → NUMERIC(38,6) |
37→38 | 6→6 ✅ | ≥19→≥19 ✅ | ❌ (38 硬上限) |
NUMERIC(16,6) → NUMERIC(20,6) |
16→20 | 6→6 ✅ | ≤18→≥19 ❌ | ❌ |
NUMERIC(5,2) → NUMERIC(5,4) |
5→5 | 2→4 ❌ | — | ❌ |
NUMERIC(38,10) → NUMERIC(18,6) |
38→18 | 10→6 ❌ | — | ❌ |
来源：Vertica 官方文档 — Working with Column Data Conversions (v25.4.x)；实测 v26.1.0-2（precision 38 硬上限 + 18 边界 + scale 不可变）。
实践中大多数 NUMERIC 优化都不满足 ALTER COLUMN 条件。典型场景——Oracle NUMBER 默认映射为 NUMERIC(38,10)，目标 NUMERIC(18,6)：precision 从 38（硬上限）变到 18，scale 从 10 变到 6。两个条件都违反，只能用 workaround。
方案 A（推荐）—— 添加新列 + 更新 + 删除旧列 + 重命名：
ALTER TABLE t ADD COLUMN col_new NUMERIC(18,4);
UPDATE t SET col_new = col_old::NUMERIC(18,4);
ALTER TABLE t DROP COLUMN col_old;
ALTER TABLE t RENAME COLUMN col_new TO col_old;
注意：UPDATE 对大表耗时长，建议分批执行。DROP COLUMN 需先推进 AHM（SELECT MAKE_AHM_NOW();）以清除历史 ROS 中对旧列的引用。
方案 B（仅满足 ALTER COLUMN 条件时）—— 直接修改：
-- 仅当 scale 不变、precision ≤ 37、不跨 18 边界时可用
ALTER TABLE t ALTER COLUMN col SET DATA TYPE NUMERIC(10,4); -- 原 NUMERIC(5,4)，同 storage class ✅
⚠️ 方案 B 的隐蔽陷阱——DELETE 后的删除向量：即使 DELETE 了超出目标精度的行并 COMMIT，ALTER COLUMN 缩小 precision 仍可能失败。因为 Vertica 的 DELETE 只是标记删除向量，原始值仍在 ROS 容器中。ALTER COLUMN 检查的是所有物理存储数据而非可见行。解决方案：
-- 删除超出新精度的数据后，先推进 AHM 清除删除向量，再缩小精度
DELETE FROM t WHERE col > 9999999.9999; -- 假设目标 NUMERIC(7,4)
COMMIT;
SELECT MAKE_AHM_NOW(); -- 推进 AHM 以清除删除向量
ALTER TABLE t ALTER COLUMN col SET DATA TYPE NUMERIC(7,4); -- 此时才能成功
方案 C —— 新建表 + INSERT ... SELECT + 删旧表（适合大量列需同时修改时）：
-- 1. 按目标数据类型建新表
CREATE TABLE t_new (col1 NUMERIC(18,6), col2 VARCHAR(200), ...);
-- 2. 迁移数据（可分批 INSERT 控制事务大小）
INSERT INTO t_new SELECT col1::NUMERIC(18,6), col2::VARCHAR(200), ... FROM t_old;
COMMIT;
-- 3. 删旧表，重命名新表
DROP TABLE t_old CASCADE;
ALTER TABLE t_new RENAME TO t_old;
比方案 A 的 ADD COLUMN + UPDATE 更适合多列同时改的场景——一次 INSERT ... SELECT 完成所有列的转换，避免逐列 UPDATE 的多趟全表扫描。
方案 D —— 导出再导入：用 EXPORT TO PARQUET 导出数据，按新精度建表后 COPY 导入。
4.3 短期优化：按实际数据设计新表¶
如果是新项目或可以重建表，在设计阶段就按实际数据长度定义列：
-- 建表时的数据类型最佳实践示例
CREATE TABLE best_practice_example (
-- ID 类字段：用 INTEGER/BIGINT，不要用 NUMERIC
id BIGINT NOT NULL,
user_id INTEGER,
-- 短编码：VARCHAR(50) 足够
status VARCHAR(20), -- 如 'active','inactive','pending'
category VARCHAR(50), -- 如 'electronics','books'
-- 一般文本：VARCHAR(200) 够用
product_name VARCHAR(200),
city VARCHAR(100),
-- 长文本：超过 500 字符时才给大值，且显式指定压缩编码
description VARCHAR(2000) ENCODING ZSTD_COMP,
-- 金额字段：用 NUMERIC(18,4) 而非 NUMERIC(38,10)
-- 18 位整数 + 4 位小数 = 覆盖千万亿级别金额
total_amount NUMERIC(18,4),
tax_rate NUMERIC(6,4), -- 如 0.1500
-- 时间戳：用标准类型
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ,
-- BOOLEAN 而不是 VARCHAR(1) 的 'Y'/'N'
is_active BOOLEAN
);
设计原则：
- VARCHAR 宁小勿大：能从业务上确定最大长度的（如状态码、国家代码、邮编），精确设定。不确定的，给 2 倍实际最大预估 + 20% 余量。
- NUMERIC 精确匹配业务精度：金额通常
NUMERIC(18,4)足够（整数部分 14 位 = 百万亿级）。比率NUMERIC(6,4)（0.0000 ~ 99.9999）。 - 布尔值用 BOOLEAN：不要用
VARCHAR(1)存'Y'/'N'或INTEGER存0/1。BOOLEAN 1 字节，编码高效。 - 分段键避免 VARCHAR：如果某列需要作为分段键（
SEGMENTED BY），优先使用 INTEGER/BIGINT 类型。VARCHAR 分段键的哈希成本远高于整数。
4.4 长期治理：制定企业级数据类型规范¶
对于有多个开发团队、多套 ETL 流程的企业环境，建议：
- 建立数据类型映射表：规定从 Oracle NUMBER、MySQL DECIMAL、Hive STRING 等源类型到 Vertica 的映射规则。例如：
NUMBER→ 需人工判断，默认映射为NUMERIC(18,6)（而非NUMERIC(38,10)）VARCHAR2(4000)→ 需业务确认实际最大长度后再映射，不要直接 4000
- Code Review 关注列定义：任何新表的 DDL 中
VARCHAR(1000)以上或NUMERIC(≥19)需要评审说明理由 - 定期运行第 7 节诊断 SQL：每周或每次发版后，执行「快速诊断 SQL 工具箱」前 3 条 SQL，确认识别新引入的列定义过大问题
5. 深入案例¶
5.1 案例 1：VARCHAR(65000) 导致查询内存翻倍¶
📝 虚构案例
场景描述：某电商平台的订单宽表 orders_wide 有 200+ 列，其中 60 列为字符串类型。建表时 DBA 为了方便，所有字符串列统一设为 VARCHAR(65000)。表有 3 亿行。
诊断过程：
-- 步骤 1：检查 VARCHAR 列的定义长度
SELECT column_name, data_type_length
FROM v_catalog.columns
WHERE table_schema = 'public' AND table_name = 'orders_wide'
AND data_type ILIKE 'varchar%'
ORDER BY data_type_length DESC
LIMIT 10;
输出显示 60 列全部为 VARCHAR(65000)。
-- 步骤 2：采样实际数据长度
SELECT
MAX(OCTET_LENGTH(order_status)) AS max_status_len,
MAX(OCTET_LENGTH(payment_method)) AS max_payment_len,
MAX(OCTET_LENGTH(shipping_city)) AS max_city_len,
MAX(OCTET_LENGTH(customer_notes)) AS max_notes_len
FROM orders_wide;
输出：
| max_status_len | max_payment_len | max_city_len | max_notes_len |
|---|---|---|---|
| 12 | 20 | 45 | 1820 |
根因分析：customer_notes 实际最长 1820 字节，定义 2000 即可；其余 59 列定义 VARCHAR(65000) 但实际最长的仅 45 字节——定义了 65000，实际用了 12~45，浪费 > 1000 倍。这些列在哈希 JOIN 和 GROUP BY 时按 65000 字节的预算预留内存缓冲，查询内存峰值比实际需求高 4~6 倍。
修复方案：分批 ALTER COLUMN ... SET DATA TYPE：
-- 第 1 批：短文本列缩小到 VARCHAR(50) ~ VARCHAR(100)
ALTER TABLE orders_wide ALTER COLUMN order_status SET DATA TYPE VARCHAR(20);
ALTER TABLE orders_wide ALTER COLUMN payment_method SET DATA TYPE VARCHAR(30);
ALTER TABLE orders_wide ALTER COLUMN shipping_city SET DATA TYPE VARCHAR(100);
-- ... 共 57 列
-- 第 2 批：中等长度列
ALTER TABLE orders_wide ALTER COLUMN customer_notes SET DATA TYPE VARCHAR(2000);
-- ... 共 3 列
效果对比：
| 指标 | 优化前 | 优化后 | 变化 |
|---|---|---|---|
| 单条复杂 JOIN 查询峰值内存 | 48 GB | 12 GB | ↓ 75% |
| 查询 P99 执行时间 | 320 秒 | 180 秒 | ↓ 44% |
| 列编码类型（部分列，经 DBD 优化重建投影后） | String_LZO | GLOBAL_DICT | 压缩率提升 |
customer_notes 列存储 |
8.2 GB | 3.6 GB | ↓ 56%（ZSTD_COMP 编码） |
关键收获：缩小 VARCHAR 定义降低内存的效果是即时的（元数据变更），但编码不会自动调整——投影的编码在创建时确定，ALTER COLUMN 不会改变已有投影的编码策略。本案例中 GLOBAL_DICT 编码的变化来自后续 Database Designer 优化并重建投影，耗时约 2 周。如果业务需要立即可见的存储节省，应使用 4.2 节方案 C（新建表 + INSERT ... SELECT）——新表投影的 AUTO 编码会按新列类型重新选择。
5.2 案例 2：NUMERIC(38,10) 改为 NUMERIC(18,6) 的性能跃升¶
📝 虚构案例
场景描述：某金融企业从 Oracle 迁移了一套风控模型表，200+ 个 NUMERIC 列全部按 Oracle NUMBER 类型映射为 NUMERIC(38,10)。表 risk_scores 约 5 亿行。日常 ETL 中频繁对该表做 GROUP BY + SUM/AVG 聚合，发现性能远低于 Oracle 原系统。
诊断过程：
-- 步骤 1：确认所有 NUMERIC 列精度
SELECT column_name, numeric_precision, numeric_scale
FROM v_catalog.columns
WHERE table_schema = 'public' AND table_name = 'risk_scores'
AND data_type ILIKE 'numeric%'
ORDER BY numeric_precision DESC;
输出：全部 200+ 列为 NUMERIC(38,10)。
-- 步骤 2：检查实际存储的精度需求
SELECT
MAX(LENGTH(FLOOR(ABS(score_a))::VARCHAR)) AS max_int_digits_a,
MAX(LENGTH(FLOOR(ABS(score_b))::VARCHAR)) AS max_int_digits_b,
MAX(ABS(score_a)) AS max_abs_value
FROM risk_scores;
-- 输出：max_int_digits 均在 6~10 之间，最大绝对值 < 10^10
-- 步骤 3：查看编码类型
SELECT DISTINCT encoding_type
FROM v_catalog.projection_columns pc
JOIN v_catalog.columns c
ON pc.table_id = c.table_id AND pc.table_column_name = c.column_name
WHERE c.table_name = 'risk_scores' AND c.data_type ILIKE 'numeric%';
-- 输出：全部为 LZO
根因分析：所有数值的实际范围不超过 ±10^10，且小数位最多 6 位。NUMERIC(18,6) 完全够用。但当前 NUMERIC(38,10) 每个值用 16 字节 + LZO 编码，GROUP BY 的哈希计算走 128 位路径。
修复方案：
注意：如 4.2 节所述，Vertica 不支持通过
ALTER COLUMN ... SET DATA TYPE修改带 scale 的 NUMERIC 精度。200+ 列若用 ADD COLUMN + UPDATE 逐列处理 = 200 次全表扫描，5 亿行 × 200 趟 = 不可接受。这里采用方案 C（新建表 + INSERT ... SELECT），一次扫描转换全部列。
-- 1. 从系统表生成新表 DDL（将所有 NUMERIC(38,10) 替换为 NUMERIC(18,6)）
-- 2. 建新表
CREATE TABLE risk_scores_new (
id BIGINT NOT NULL,
score_a NUMERIC(18,6),
score_b NUMERIC(18,6),
-- ... 共 200+ 列，全部 NUMERIC(18,6)
created_at TIMESTAMP
);
-- 3. 一次 INSERT ... SELECT 迁移所有数据
INSERT INTO risk_scores_new
SELECT id, score_a::NUMERIC(18,6), score_b::NUMERIC(18,6), ..., created_at
FROM risk_scores;
COMMIT;
-- 4. 删旧表，重命名新表
DROP TABLE risk_scores CASCADE;
ALTER TABLE risk_scores_new RENAME TO risk_scores;
对比：方案 A（ADD COLUMN + UPDATE）需要 200+ 次 UPDATE，每趟全表扫描 5 亿行；方案 C 只需 1 趟扫描，所有列的精度转换在同一个 INSERT ... SELECT 中完成。
效果对比：
| 指标 | 优化前 | 优化后 | 变化 |
|---|---|---|---|
| 单列平均存储（每行） | 16 字节 | 8 字节 | ↓ 50% |
| 编码类型 | LZO | Delta Int Pack | 压缩率 ↑ 3~5 倍 |
| 表总存储 | 320 GB | 85 GB | ↓ 73% |
| ETL 聚合耗时（日批） | 2.5 小时 | 45 分钟 | ↓ 70% |
| GROUP BY 哈希计算 | 128 位 | 64 位 | CPU 降低 40% |
关键收获：NUMERIC(38,10) → NUMERIC(18,6) 不仅是存储翻倍的问题，编码策略从 LZO 跳到 Delta Int Pack 带来的压缩率差异才是存储节省的主要来源。而 64 位 vs 128 位哈希计算的 CPU 差异，在 5 亿行级别的聚合中会被显著放大。
5.3 案例 3：NUMERIC 精度溢出导致数据加载失败¶
📋 真实案例 · 来源：numeric类型字段出现超过范围的数据
场景描述：某企业在数据迁移过程中，创建了 test2(a NUMERIC(3,2)) 表，该列精度定义为总共 3 位、小数 2 位——即可存储范围 -9.99 ~ 9.99。在后续 ETL 计算中，对 a 列做 a * 10 操作并插入，触发 ERROR 5411：Value exceeds range of type numeric(3,2)。
诊断过程：
CREATE TABLE test2(a NUMERIC(3,2));
INSERT INTO test2 VALUES(12.2);
-- 报错：ERROR 5411: Value exceeds range of type numeric(3,2)
根因：12.2 的整数部分是 2 位，加上小数 1 位，总共 3 位，但 NUMERIC(3,2) 允许的最大值是 9.99——整数部分最多 1 位。12.2 超出了范围。
进一步测试：
-- 插入合法值
INSERT INTO test2 VALUES(9.99); -- ✅ 成功
INSERT INTO test2 VALUES(1.23); -- ✅ 成功
-- 计算后插入：SUM 聚合正常，乘法溢出
INSERT INTO test2 SELECT SUM(a) FROM test2; -- ✅ 9.99+1.23=11.22，SUM 结果类型自动提升，成功插入
INSERT INTO test2 SELECT a * 10 FROM test2; -- ❌ ERROR 5411，乘法结果超出 NUMERIC(3,2) 范围
为什么 SUM 能插入 11.22 而 VALUES(11.22) 不行？ 源于 Vertica 的
AllowNumericOverflow参数（默认 = 1，即启用静默溢出）。Vertica 内部以 18 位 为一组存储 NUMERIC——NUMERIC(3,2)声明精度 3，但内部实占 18 位缓冲区。SUM聚合在这个 18 位缓冲区内计算，结果11.22不触发精度检查，无声通过。而INSERT VALUES(11.22)走的是字面量解析路径，严格对照 DDL 声明的 (3,2) 检查，所以被拒绝。但a * 10这种表达式的结果类型与输入列的 NUMERIC 精度相关，乘法可能导致中间结果溢出。同样是计算后插入，SUM 没问题而 a * 10 失败——这在 ETL 中容易踩坑。来源：Vertica 官方文档 — Numeric Data Type Overflow with SUM, SUM_FLOAT, and AVG (v25.2.x)：Vertica internally works with multiples of 18 digits. If your NUMERIC precision is less than 18, overflow is allowed up to the first 18-digit boundary. 验证环境
AllowNumericOverflow = 1（默认值）。
根因分析：NUMERIC(3,2) 表示总共 3 位有效数字、其中 2 位小数，整数部分只有 1 位（最大值 9.99）。12.2 整数部分有 2 位 → 字面量检查拒绝。SUM 聚合在 18 位内部缓冲区中计算，AllowNumericOverflow=1 允许静默溢出，11.22 无声通过。a * 10 结果 12.30 仍受列级精度约束 → ERROR 5411。
修复方案：
-- 方案 1：扩大精度定义（scale 不变，ALTER COLUMN 支持 ✅）
ALTER TABLE test2 ALTER COLUMN a SET DATA TYPE NUMERIC(5,2); -- 允许 -999.99 ~ 999.99
-- 方案 2：在 ETL 中加边界检查
INSERT INTO test2
SELECT a * 10 FROM source_table
WHERE a * 10 BETWEEN -9.99 AND 9.99;
效果：扩大精度定义后，ETL 作业恢复正常，该列不再因计算溢出而报错。
关键收获：NUMERIC(p, s) 的 p 是总有效位数而非整数位数。实际整数位数 = p - s。在定义 NUMERIC 列时，必须同时考虑业务数据的当前范围和未来可能的增长（包括 a * 10 这类计算的中间结果）。注意 SUM 聚合和直接表达式在精度检查上有不同行为，不要以为 SUM 成功就意味着乘法也能过。
5.4 案例 4：数据迁移中的 VARCHAR 长度调整¶
📋 真实案例 · 来源：SQL、数据、存储过程迁移方法论
场景描述：某制造企业将 MSSQL 数据迁移到 Vertica。源库中 SupplierScheduledOrder 表含大量 NVARCHAR(MAX) 和 VARCHAR(8000) 列。迁移脚本先用 CAST(... AS VARCHAR(8000)) 做通用转换，加载到 Vertica 后，再根据实际数据长度调整列定义。
诊断过程：
-- 检查某列的当前定义
SELECT column_name, data_type, data_type_length
FROM v_catalog.columns
WHERE table_name = 'SupplierScheduledOrder'
AND column_name = 'Incoterms2010';
-- 输出：VARCHAR(8000)
修复方案：
ALTER TABLE SupplierScheduledOrder
ALTER COLUMN Incoterms2010 SET DATA TYPE VARCHAR(150);
-- 150 = 110 * 1.2（留 20% 余量）向上取整
效果：该列在后续查询中的内存占用大幅降低（即时生效的元数据变更）。编码优化需另行通过 DBD 或显式指定编码实现（见 4.1 节注意事项）。
关键收获：迁移后必须做一次全面的列定义审查。源系统的数据类型定义往往不能直接照搬。尤其是 Oracle NUMBER → Vertica NUMERIC(38,10) 这种默认映射，几乎 100% 需要二次调整。
6. 完整诊断流程实战¶
📝 虚构场景 · 完整演练
6.1 场景¶
某运营商的数据仓库集群（3 节点，每节点 256 GB 内存），最近一个月出现以下症状：
- 存储增长异常：每周增长 200 GB，但业务数据增量只有约 50 GB
- 查询变慢：以前 P95 查询 30 秒，现在 P95 到了 90 秒
- 内存不足报错增多：
RESOURCE_REJECTED从每周 2 次增加到每天 15 次
6.2 时间线排查¶
Step 1 — 全局扫描（当天）
-- 找出所有 VARCHAR > 500 和 NUMERIC > 18 的列
SELECT
c.table_schema, c.table_name, c.column_name,
c.data_type, c.data_type_length, c.numeric_precision
FROM v_catalog.columns c
JOIN v_catalog.tables t ON c.table_id = t.table_id AND c.table_schema = t.table_schema
WHERE t.is_system_table = false AND t.is_temp_table = false
AND (
(c.data_type ILIKE 'varchar%' AND c.data_type_length > 500)
OR
(c.data_type ILIKE 'numeric%' AND c.numeric_precision > 18)
)
ORDER BY c.data_type_length DESC NULLS LAST;
输出摘要：
| table_name | column_name | data_type | data_type_length | numeric_precision |
|---|---|---|---|---|
| cdr_detail | user_agent | Varchar | 65000 | - |
| cdr_detail | url | Varchar | 65000 | - |
| cdr_detail | referrer | Varchar | 65000 | - |
| ...（共 87 列） | ... | Varchar | 65000 | - |
| billing_fee | amount | Numeric | - | 38 |
| billing_fee | tax | Numeric | - | 38 |
| ...（共 45 列） | ... | Numeric | - | 38 |
判断：cdr_detail 表（话单明细，约 50 亿行）有 87 列 VARCHAR(65000)；billing_fee 表（账单费用，约 10 亿行）有 45 列 NUMERIC(38,10)。这是严重的列定义过大问题。
Step 2 — 抽样确认实际数据长度（当天）
-- 对 cdr_detail 的关键 VARCHAR 列抽样
SELECT
MAX(OCTET_LENGTH(user_agent)) AS max_ua,
MAX(OCTET_LENGTH(url)) AS max_url,
MAX(OCTET_LENGTH(referrer)) AS max_ref
FROM cdr_detail
TABLESAMPLE(0.1); -- 抽样 0.1% 快速验证
输出：
| max_ua | max_url | max_ref |
|---|---|---|
| 512 | 2048 | 1024 |
判断：实际最长数据分别为 512、2048、1024 字节。定义为 VARCHAR(65000)，浪费约 30~120 倍。
-- 对 billing_fee 的 NUMERIC 列抽样
SELECT
MAX(LENGTH(FLOOR(ABS(amount))::VARCHAR)) AS max_int_amt,
MAX(LENGTH(FLOOR(ABS(tax))::VARCHAR)) AS max_int_tax
FROM billing_fee
TABLESAMPLE(0.1);
输出：
| max_int_amt | max_int_tax |
|---|---|
| 8 | 3 |
判断：金额整数部分最多 8 位，税整数部分最多 3 位。NUMERIC(18,4) 完全够用（14 位整数 + 4 位小数）。当前 NUMERIC(38,10) 严重过度。
Step 3 — 计算修复收益（当天）
-- 估算 cdr_detail 的 VARCHAR 列优化后节省的存储
SELECT
SUM(cs.used_bytes) / (1024*1024*1024)::NUMERIC(10,2) AS total_gb,
COUNT(DISTINCT cs.anchor_table_column_name) AS col_count
FROM v_monitor.column_storage cs
WHERE cs.anchor_table_name = 'cdr_detail'
AND cs.anchor_table_column_name IN (
SELECT column_name FROM v_catalog.columns
WHERE table_name = 'cdr_detail'
AND data_type ILIKE 'varchar%' AND data_type_length > 500
);
输出：total_gb = 420 GB，87 列。
判断：ALTER COLUMN 缩小 VARCHAR 定义可立即降低查询内存占用（元数据变更）。存储和编码的优化需另行处理：大字段显式指定 ENCODING ZSTD_COMP（见第 3 批），小字段则通过方案 C 新建表让 AUTO 按新列类型重选编码，或后续运行 DBD 优化投影。预计综合可回收 30~50% 存储。
Step 4 — 执行修复（当周）
分批次执行 ALTER COLUMN ... SET DATA TYPE：
-- 第 1 批：短文本列（实际 < 50 字节）→ VARCHAR(100)
ALTER TABLE cdr_detail ALTER COLUMN method SET DATA TYPE VARCHAR(10);
ALTER TABLE cdr_detail ALTER COLUMN protocol SET DATA TYPE VARCHAR(10);
-- ... 共 45 列
-- 第 2 批：中等文本列 → VARCHAR(300)
ALTER TABLE cdr_detail ALTER COLUMN user_agent SET DATA TYPE VARCHAR(600);
-- ... 共 30 列
-- 第 3 批：长文本列缩小。编码变更需指定 PROJECTIONS（见下方说明），此处先缩小定义
ALTER TABLE cdr_detail ALTER COLUMN url SET DATA TYPE VARCHAR(2500);
ALTER TABLE cdr_detail ALTER COLUMN referrer SET DATA TYPE VARCHAR(1500);
-- ... 共 12 列
-- 编码变更通过 DBD 优化或显式指定（需列出投影名）：
ALTER TABLE cdr_detail ALTER COLUMN url ENCODING zstd_comp
PROJECTIONS (cdr_detail_b0, cdr_detail_b1);
-- ... 共 12 列
-- 第 4 批：NUMERIC 精度收缩（scale 从 10 变为 4，ALTER COLUMN 不支持，必须重建列）
ALTER TABLE billing_fee ADD COLUMN amount_new NUMERIC(18,4);
UPDATE billing_fee SET amount_new = amount::NUMERIC(18,4);
ALTER TABLE billing_fee DROP COLUMN amount;
ALTER TABLE billing_fee RENAME COLUMN amount_new TO amount;
-- ... 共 45 列，UPDATE 分批执行
Step 5 — 效果验证（次周）
| 指标 | 优化前 | 优化后 | 变化 |
|---|---|---|---|
| 表总存储 | 1.2 TB | 680 GB | ↓ 43% |
| RESOURCE_REJECTED / 天 | 15 | 2 | ↓ 87% |
| 查询 P95 耗时 | 90 秒 | 38 秒 | ↓ 58% |
billing_fee 聚合查询 |
180 秒 | 52 秒 | ↓ 71% |
| 周存储增长 | 200 GB | 65 GB | 回归正常 |
7. 快速诊断 SQL 工具箱¶
| # | 诊断目标 | SQL（已验证：Vertica v26.1.0-2） |
|---|---|---|
| 1 | 找出所有 VARCHAR 过大的列 | SELECT table_schema, table_name, column_name, data_type_length FROM v_catalog.columns c JOIN v_catalog.tables t ON c.table_id=t.table_id AND c.table_schema=t.table_schema WHERE t.is_system_table=false AND t.is_temp_table=false AND c.data_type ILIKE 'varchar%' AND c.data_type_length > 500 ORDER BY c.data_type_length DESC; |
| 2 | 找出所有 NUMERIC 精度 > 18 的列 | SELECT table_schema, table_name, column_name, numeric_precision, numeric_scale FROM v_catalog.columns c JOIN v_catalog.tables t ON c.table_id=t.table_id AND c.table_schema=t.table_schema WHERE t.is_system_table=false AND t.is_temp_table=false AND c.data_type ILIKE 'numeric%' AND c.numeric_precision > 18 ORDER BY c.numeric_precision DESC; |
| 3 | 按列查看存储占用和数据类型 | SELECT cs.anchor_table_schema, cs.anchor_table_name, cs.anchor_table_column_name, pc.data_type, SUM(cs.used_bytes)/(1024*1024)::NUMERIC(10,2) AS total_mb FROM v_monitor.column_storage cs JOIN v_catalog.projection_columns pc ON cs.projection_id=pc.projection_id AND cs.column_id=pc.column_id GROUP BY 1,2,3,4 ORDER BY total_mb DESC LIMIT 50; |
| 4 | 一次检查所有 VARCHAR 列的实际长度 | 用 2.2 节步骤 A 的生成器，输出为 UNION ALL 查询，复制执行即可。单列抽查用步骤 B。 |
| 5 | 一次检查所有 NUMERIC 列的实际精度 | 用 2.2 节步骤 C 的生成器，输出为 UNION ALL 查询，复制执行即可。对比 max_int_digits 与 def_precision 找过度定义的列。 |
| 6 | 查看列的编码类型 | WITH fp AS (SELECT table_id, table_column_name, encoding_type, ROW_NUMBER() OVER (PARTITION BY table_id, table_column_name ORDER BY projection_id) AS rn FROM v_catalog.projection_columns) SELECT c.column_name, c.data_type, fp.encoding_type FROM v_catalog.columns c JOIN fp ON c.table_id=fp.table_id AND c.column_name=fp.table_column_name AND fp.rn=1 WHERE c.table_name=':table_name'; |
| 7 | 检查是否有 VARCHAR 作为分段键 | SELECT p.projection_name, pc.table_column_name, pc.data_type FROM v_catalog.projections p JOIN v_catalog.projection_columns pc ON p.projection_id=pc.projection_id WHERE p.is_segmented AND pc.data_type ILIKE 'varchar%' ; |
| 8 | 缩小 VARCHAR 列定义 | ALTER TABLE :schema.:table ALTER COLUMN :col SET DATA TYPE VARCHAR(:new_len); 官方支持，纯元数据操作，瞬间完成。先用 #4 确认实际最大字节数。 |
| 9 | 缩小 NUMERIC 精度 | Vertica 不支持直接 ALTER COLUMN NUMERIC。需用 ADD COLUMN + UPDATE + DROP COLUMN + RENAME 四步法（见 4.2 节），或重建表。先用 #5 确认实际精度需求。 |
| 10 | 检查 BOOLEAN 被误用为 VARCHAR 的列 | SELECT table_schema, table_name, column_name, data_type, data_type_length FROM v_catalog.columns c JOIN v_catalog.tables t ON c.table_id=t.table_id WHERE t.is_system_table=false AND t.is_temp_table=false AND c.data_type ILIKE 'varchar%' AND c.data_type_length <= 1; |
8. 最佳实践清单¶
按投入产出比从高到低排列：
- NUMERIC 精度绝对不要超过 18，除非业务确实需要。
NUMERIC(18,s)用 8 字节 + Delta Int Pack 编码，NUMERIC(19,s)用 16 字节 + LZO 编码。这一位之差是 Vertica 列定义优化中收益最大的单点决策——每行省 8 字节、编码从通用变专用、哈希计算从 128 位降为 64 位。 - VARCHAR 定义不超过实际最大数据长度的 120%。不要「留足余量」设 65000——VARCHAR(65000) 阻止 Vertica 选择 GLOBAL_DICT 编码，且查询引擎按定义长度预留缓冲内存。
- 迁移后必须重新审查所有列定义。Oracle
NUMBER→ VerticaNUMERIC(38,10)和 SQL ServerNVARCHAR(MAX)→ VerticaVARCHAR(65000)是默认可耻但普遍存在的映射，必须手工修正。 - 分段键优先用整数类型。VARCHAR 分段键的哈希成本远高于 INTEGER/BIGINT。如果分段列的值域可枚举（如省份代码、产品类别），建一个整数映射表，用整数代理键作为分段列。
- 布尔值用 BOOLEAN，不要用 VARCHAR(1) 或 INTEGER。BOOLEAN 只需 1 字节，Vertica 对其编码也高度优化。
- 长文本列（> 500 字节）显式指定压缩编码。不要依赖 AUTO——
VARCHAR(2000)以上的列指定ENCODING ZSTD_COMP或ENCODING GZIP_COMP，压缩率通常可达 5:1 ~ 10:1。 - 金额/金融数据用 NUMERIC，不要用 FLOAT。FLOAT 是近似值类型，金融计算中的舍入误差不可接受。但也不要用
NUMERIC(38,10)——如前所述，NUMERIC(18,4)已覆盖 10^14 级别的金额。 - 建表时显式指定编码，不要全部依赖 AUTO。尤其对于知道数据分布特征的列（如低基数列应该用 GLOBAL_DICT、高基数列考虑 ZSTD_COMP），显式指定编码比依赖 AUTO 的统计采样更可靠。
- 新表上线前，用本文第 7 节的前 3 条 SQL 做一次列定义审计。花 5 分钟运行三条 SQL，可以避免缺陷沉淀为技术债务。
- 定期（每月）对全库执行第 7 节的诊断 SQL，关注新增表和大表的列定义变化。结合
v_monitor.column_storage的存储增长趋势，尽早发现列定义问题。
扩展阅读¶
- MPP 列存引擎的架构设计哲学 — 理解列存架构基础原理
- Vertica 资源拒绝排查与资源池调优 — 内存不足时的排查方法
- Vertica 统计信息管理与查询性能 — 编码选择与统计信息的密切关系
- Vertica 性能调优 - 2 使用系统表排查查询故障 — 系统表诊断进阶
- Vertica 列编码策略优化 — 编码选择直接影响存储空间与查询 I/O 开销