数据仓库的PostgreSQL :近实时ETL /数据提取的最佳方法

Question

背景：

我有一个PostgreSQL (v8.3)数据库，它为OLTP进行了很大的优化。

我需要在半实时的基础上从它中提取数据(有些人肯定会问半实时意味着什么，答案是尽可能频繁的，但我将是务实的，就像我们希望每15分钟一次那样)，并将其输入数据仓库。

多少数据？在高峰时刻，我们讨论的是每分钟约80至100 k行，达到OLTP一侧，非峰值时这将显著下降到15-20k。最频繁更新的行是每行64字节，但有各种表等，因此数据非常多样化，每一行最多可达4000字节。OLTP是活动的24x5.5。

最佳解？

从我能拼凑起来的东西来看，最实际的解决方案如下：

• 创建一个触发器，将所有DML活动写入旋转CSV日志文件
• ，执行所需的任何转换，
• 使用本机DW数据泵工具有效地将转换后的CSV泵到DW

中。

为什么采用这种方法？

• 触发器允许有选择的表成为目标，而不是系统范围的+输出是可配置的(即可配置到CSV中)，并且相对容易编写和部署。SLONY使用类似的方法，开销是acceptable
• CSV --简单而快速的transform
• Easy --将CSV注入DW

考虑的替代品.

• 使用本机日志记录(http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html)。这方面的问题是，相对于我所需的内容，它看起来非常冗长，解析和转换也有点棘手。然而，它可能会更快，因为我认为与触发器相比，它的开销更小。当然，这将使管理更容易，因为它是在系统范围内，但同样，我不需要一些表(有些是用于持久存储的JMS消息，我不想直接log)
• Querying数据通过一个ETL工具，如塔伦德，并将它注入DW .问题是OLTP模式需要经过调整才能支持这一点，并且有许多负面的side-effects
• Using (经过调整/黑客攻击的SLONY - SLONY可以很好地记录和迁移到从服务器上的更改)，所以概念框架已经存在，但是所提议的解决方案似乎更容易，并且cleaner
• Using WAL

。

以前有人这样做过吗？想分享你的想法吗？

Answer 1

假设您感兴趣的表有(或可以用)一个唯一的、索引的、顺序的键，那么只向文件发出带有输出的SELECT ... FROM table ... WHERE key > :last_max_key就可以得到更好的值，其中last_max_key是上次提取的最后一个键值(如果第一次提取的话是0)。这种增量、解耦的方法避免了在插入数据路径(无论是自定义触发器还是修改的Slony)中引入触发器延迟，并且取决于您的设置，随着CPU数量的增加，扩展得更好(但是，如果您还必须跟踪，并且顺序键是由您添加的，那么UPDATE语句应该将键列设置为NULL，这样它就会得到一个新的值并由下一个提取得到。如果没有触发器，您将无法跟踪** DELETE**s** )。)这就是你提到塔伦德时的想法吗？

除非您不能在上实现解决方案，否则我不会使用日志记录工具；日志记录很可能涉及锁定开销，以确保日志行是按顺序写入的，并且在多个后端写入日志时不相互重叠/覆盖(检查Postgres源代码)。锁定开销可能不是灾难性的，但是如果您可以使用增量SELECT替代方案，您可以不使用它。此外，语句日志记录会淹没任何有用的警告或错误消息，并且解析本身不会是瞬时的。

除非您愿意解析WALs (包括事务状态跟踪，并准备好每次升级Postgres时重写代码)，否则我也不一定使用WALs --也就是说，除非您INSERT/UPDATE/DELETE有可用的额外硬件，否则您可以将WALs发送到另一台机器以提取(在第二台机器上，可以无耻地使用触发器 --甚至语句日志--因为无论发生什么事情都不会影响主计算机上的性能。)请注意，性能方面(在主计算机上)，除非您可以将日志写入SAN，否则在运行增量SELECT时，将WALs发送到另一台机器会带来类似的性能问题(主要是在打击文件系统缓存方面)。

Answer 2

如果您可以想到一个“校验和表”，其中只包含id和“校验和”，您不仅可以快速选择新记录，还可以更改和删除记录。

校验和可以是您喜欢的crc32校验和函数。

Answer 3

PostgreSQL中新的ON冲突子句改变了我进行许多更新的方式。我将新的数据(基于row_update_timestamp)拉到一个临时表中，然后在一个SQL语句中插入到目标表中，同时进行冲突更新。如果目标表是分区的，那么您需要跳过几圈(即直接命中分区表)。ETL可以在加载临时表(很可能)或ON冲突SQL (如果是琐碎的)时发生。与其他"UPSERT“系统相比(更新，插入如果零行等)这表明速度有了很大的提高。在我们特定的DW环境中，我们不需要/不想容纳删除。看看关于冲突的文档-它给甲骨文的合并一个运行它的钱！