PostgreSQL调优数据仓库的最佳实践

Question

我发现很多线上和印刷指南都可以指导如何优化Postgres以适用于OLTP应用程序，但我没有发现任何特定于数据仓库应用程序的类似内容。由于工作负载类型存在很多差异，因此我相信在数据库管理和调优方面必须存在一些差异。

我自己的一些经验是：

- 从DDL方面来说，我更加自由地使用索引，因为我通常只需要担心一天内的插入，并且可以进行批量插入并重建索引。 - 我通常使用整数代理键来连接具有多个自然键的数据，以加快连接速度。 - 我通常会定义和维护非常全面的日期表，该表具有预构建的日期操作（财政日期与日历日期不同，财政年月、星期的起始日等），在选择语句和where语句中广泛使用它而不是使用函数。这通常有助于处理CPU密集型聚合查询。

我希望能找到关于内存管理和其他数据库设置的信息，但如果您了解Postgres基于数据仓库的任何有用最佳实践，我会很高兴听取您的意见。

- TheDarkSaint

---

这个问题并没有简单的答案。如果您想要了解如何进行PostgreSQL调优，我可以向您推荐以下一本书：http://www.packtpub.com/postgresql-90-high-performance/book（这里有一个免费章节可供参考）。 - Eelke

请告诉我们是否发现了一些有趣的信息。当我们在时间维度和事实表中将 bigint 更改为 smallint 时，我们获得了巨大的性能变化。 - Tomas Greif

我建议观看Josh Berkus的“5 Steps to PostgreSQL Performance”这个精彩演讲，链接为http://vimeo.com/9889075。这将回答你很多问题，或者让你接近自己找到答案。 - Will

Answer 1

我的经验（尽管在数据仓库方面规模相对较小）：

• 像您提到的那样，预聚合数据可以大幅减少需要读取的数据量，因此这是最重要的事情。
• 避免短写事务、子事务和保存点。这包括PL/pgSQL中的异常处理。这些操作会快速消耗可用的“事务ID”空间，并导致昂贵的“环绕”清理操作，需要重新编写整个表格。
• 我发现将表进行分区，以便每个单独的分区都可以适应内核高速缓存，有利于维护和迁移（如果您需要执行任何操作）。这意味着您只需要从磁盘上进行1次序列扫描就可以重新创建一个分区上的所有索引，而不是为每个索引进行一次扫描。
• 像Chris已经提到的那样，慷慨地使用work_mem和maintenance_work_mem；如果您的工作负载无法放入RAM中，则将更多临时数据保存在内存中可以通过更智能的查询计划（最重要的是HashAggregate）节省I/O和CPU时间。
• 如果您需要进行大量排序，则购买专用的SSD来存储临时文件可能会有所帮助。

- intgr

Answer 2

从内存管理的角度来看，你们最大的区别之一是，通常可以希望将工作OLTP集保留在内存中，而这在OLAP环境中并非如此。此外，往往联接的集合更大。这意味着更高的work_mem设置可能非常有帮助，并且如果表未规范化，则可以将work_mem推高一些。我不确定shared_buffers的建议是否会改变（我更喜欢从低处开始增加，并在每个步骤测试性能），但如果您要对任何大小的集进行报告，则必须增加work_mem。

- Chris Travers