时间戳

首页 > 解决方案 > 保持“规范”表与“原始”源表一致

database - 保持“规范”表与“原始”源表一致

问题描述

我正在使用 PostgreSQL。例如，我有许多来自第三方数据的“原始”表

Table: exchange1_trades

id, trade_id, side,  base_asset, quote_asset, price, quantity
1,  123,      'buy', 'ETH',      'BTC',       0.02,  10

Table: exchange2_trades

id, asset, trade_id, change
1,  'ETH', 100,      -20
2,  'BTC', 100,      0.3

我的目标是将这些组合成一个一致的规范表：

Table: canonical_trades

id, exchange,    asset, change, trade_id, parent_table,       parent_id,  usd
1,  'exchange1', 'ETH',     10,      123, 'exchange1_trades', 1,          NULL
2,  'exchange1', 'BTC',   -0.2,      123, 'exchange1_trades', 1,          NULL
3,  'exchange2', 'ETH',    -20,      100, 'exchange2_trades', 1,          NULL
4,  'exchange2', 'BTC',    0.3,      100, 'exchange2_trades', 2,          NULL

该usd列稍后将由一些外部作业单独填充。

实现这一目标的最佳方法是什么？理想情况下，我希望：
（1）canonical_trades每当原始表添加/删除行时要更新的表
（2）约束以确保canonical_trades与原始表一致（例如，没有额外的行）
（3）能够添加索引到canonical_trades

我考虑过的一些方法：

(a) 只需定义canonical_trades为没有约束的表，并有一些查询或脚本来更新它以反映原始表。这很简单，但不能非常强烈地保证与原始表的一致性。

(b) 定义触发器以在原始表添加/删除条目时从规范表中添加/删除条目

(c) 定义一个父canonical_trades表，并有exchange1_base_canonical_trades从它继承的exchange1_quote_canonical_trades子表。exchange2_canonical_trades然后，每个子表都可以有自己的外键列来引用其父表。

(d) 定义视图exchange1_base_view, exchange1_quote_view，exchange2_view每个都包含相关行，然后形成规范交易exchange1_base_view UNION ALL exchange1_quote_view UNION ALL exchange2_view。这里的缺点是没有一种简单的方法来添加usd列 - 我需要一个单独的表，并且连接变得混乱。

标签： databasepostgresqldatabase-designtriggers

解决方案

你真的只有两个选择：

  1. 为规范表提供物理存储或；
  2. 从规范化表构建视图

您将选择什么取决于

  1. 什么更重要，更快INSERT或更快SELECT
  2. 您需要确切的最后一个值还是任何最后一个值，只要它正确且按顺序排列即可。
  3. 在规范表上发布的次数与向非规范表发布SELECT的次数相比INSERT
  4. 将在规范表上运行的查询类型（只读或读写）
  5. SELECT将在规范表上执行的查询的不同变体。

让我们一一考虑这个问题

  1. 如果您需要更快的语句响应时间INSERT，那么视图是首选选项，因为触发器将跨多个表操作，因此它会更慢。另一方面，如果SELECT需要更快（例如，您需要在规范表上做出决定并且需要快速完成），那么触发器是首选选项，因为数据将在桌子
  2. 尽管这听起来可能是一个奇怪的问题，允许读取陈旧数据，但您可以决定使用视图方法，但将其增强为materialized view在这种情况下，视图后面会有物理存储。可以在给定的时间段（例如 1 小时、1 天等）安排刷新。
  3. 如果和之间的比率很大SELECT（INSERT例如 1k 比 1、10k 比 1 等），那么数据库将受益于规范表后面的物理存储（这意味着使用触发器或物化视图）。
  4. 如果您需要修改规范表中的数据，则需要在其后面有一个物理存储。这意味着您将需要一个表或instead of insert触发器。您选择哪一个，取决于更新的频率和复杂性。
  5. 如果您有不同的SELECT语句变体，您将面临在规范表后面有索引以提高性能的需要。要拥有索引，您需要在规范表后面有物理存储。

所以总而言之：

如果您需要为表提供物理存储，根据使用陈旧数据的能力，您将决定使用触发器（无陈旧数据）或物化视图（陈旧数据）。如果您不需要物理存储，那么简单的视图就足够了。

注意，这种情况下的继承对您没有帮助，因为您有相同数据的多个版本，但在可能的情况下，继承只是组织物理数据的另一种方式，用于上述分析您可以将其视为拥有物理存储的一种方式。

推荐阅读