AI 摘要：Optimizing Postgres table layout for maximum efficiency
powered by: https://github.com/foxgem/tsw
关键字：Postgres、数据对齐、表布局、索引优化、磁盘空间
概述：
本文探讨了 Postgres 中的数据对齐概念及其对表和索引大小的影响。作者解释了 Postgres 如何在磁盘上对齐数据，以确保更快的访问速度，但这可能会由于填充导致空间浪费。通过了解对齐规则并重新排列列顺序，可以优化表和索引，提高效率，减少磁盘使用量并提升性能。文章提供了实际示例和优化表布局的经验法则，并讨论了数据对齐在其他数据库（如 SQLite 和 MySQL）中的应用。
分节阅读：
引言：
• 在 Postgres 表中，列的顺序会显著影响存储和性能。
• 重新排列列顺序可以将表和索引大小减少 20%。
• 本文探讨了 Postgres 中的列对齐工作原理及如何优化表以提高效率。
计算行大小：
• Postgres 行的最小大小为 24 字节。
• 每个额外的列根据其数据类型增加空间。
• 由于数据对齐，列的顺序会影响行的大小。
数据对齐：
• Postgres 在磁盘上对齐数据，以确保更快的访问速度。
• 这种对齐可能会导致填充，从而增加行的大小。
• 在 pg_type 中，typalign 属性决定了每种数据类型的对齐边界。
视觉表示：
• 文章通过视觉图示展示了数据对齐对行大小的影响。
• 未对齐的列需要填充，导致空间浪费。
• 示例展示了对齐和未对齐列之间行大小的差异。
计算对齐边界：
• 在 pg_type 中，typalign 属性决定了每种数据类型的对齐边界。
• c = 字符对齐（无需对齐）。
• s = 短对齐（2 字节）。
• i = 整型对齐（4 字节）。
• d = 双精度对齐（8 字节）。
对索引的影响：
• 数据对齐也会影响索引，可能会增加磁盘使用量和内存消耗。
• 索引中未对齐的列会增大其大小。
• 保持索引对齐对于数据库性能和资源效率至关重要。
实际示例：
• 文章提供了两个表的实际示例，展示不同列顺序的效果。
• 对齐良好的表比未对齐的表使用更少的磁盘空间。
• 对于索引来说，情况也是如此，对齐良好的索引使用更少的磁盘空间和内存。
何时需要关注对齐：
• 数据对齐并非总是优先考虑的因素，尤其是对于初创公司而言。
• 但是，了解其潜在的优势并在可能时进行调整是重要的。
• 在开发过程中关注索引的对齐是一个好的策略。
经验法则：
• 一个通用的经验法则是按照数据类型大小降序定义列。
• 这将自然地对齐表并减少浪费的空间。
• 其他因素如基数和可读性可能比数据对齐更为重要。
关于 TOASTed 值的说明：
• 如果值太大，无法放入单个页面，TOASTed 值会存储在其他地方。
• 行中包含指向底层数据的指针。
• 对齐规则适用于指针，单字节指针无需对齐，而 4 字节指针需要 4 字节对齐。
这适用于其他数据库吗？：
• SQLite 不会填充或对齐列，优先考虑空间效率而非性能。
• MySQL 中的 NDB 表使用 4 字节对齐，会对小于 4 字节的列进行填充。
• MySQL 的 InnoDB 在某些特殊情况下可能会填充列，需要进一步研究。
注释：
• 正确对齐带来的性能提升主要在处理数据时，而非从磁盘检索数据时。
相关工具：
• 无
参考文献：
• MySQL NDB Storage Requirements
Article Link: https://r.ena.to/blog/optimizing-postgres-table-layout-for-maximum-efficiency/