数据库 Shadow Table for Postgres

hooopo · January 10, 2020 · Last by edwardzhou replied at November 10, 2020 · 11694 hits
Topic has been selected as the excellent topic by the admin.

Why

有时候,我们需要查看一条记录在过去某个时间点的状态,也就是一个 Slowly Changing Dimension 问题。然而,大部分 OLTP 系统数据模型设计天然忽略了历史记录的保存,直接删掉或者更新掉。但一些场景我们需要查看任意时间点记录的状态:

  • 审计或者安全需求
  • 实际业务需求,比如一个员工的薪资或者职位变更历史
  • 分析统计需求,数据仓库需要根据历史状态一些分析挖掘
  • 灾难恢复,开发人员上线了有 bug 的代码,错误的修改或删除了重要数据,需要恢复到正确状态

现有实现

Rails 里有 paranoia 和 Audited 等插件可以解决上面提出的部分需求。但有几个问题:

  • paranoia 这种软删插件,把 destroy 变成了 update,和其他需要 hook after_destroy 的插件会冲突。
  • paranoia 和 audited 只 hook 了应用层,只有针对单条 model 记录的操作才有效。如果开发人员写 SQL 来做一些操作就没有效果。
  • 除了应用层会有绕过 model 的 SQL,现实场景开发人员或 DBA 也会直接在 DB 上执行一些语句更新数据,这种场景 paranoia 和 audited 也是无能为力。

所以这个问题最佳的解决方案应该是从 DB 层解决。PG 现有的解决方案有 https://github.com/arkhipov/temporal_tables 等,但也存在一些问题。

理想中的方案

理想中的方案应该满足下面这些条件:

  1. 基于 DB 层,而非应用层,在任何场景下都不会漏掉数据
  2. 容易安装和使用,temporal_tables 不满足这一点,因为这东西依赖 C 扩展,在各种云服务环境下不能用
  3. 能够集成应用层信息,比如操作人,一些 DB 插件功能很全面,但不满足这一条,记录的只是 DB 层的操作账号,而非应用层的,对于 Rails 项目来说,其实都是同一个用户。
  4. 对 UI 和分析友好,一些方案把变更记录直接存在 json 里,使用起来其实需要很多额外的工作。比如 Rails 项目里,显示记录逻辑和显示历史变更逻辑难以复用。

Shadow Table with static copy

https://github.com/hooopo/shadow/blob/master/sql/shadow.sql

对于目标表 users,生成一个结构一致的 shadow 表 shadow.users,修改和更新直接回写到 users 表上,把被修改前的值写入到 shadow.users 表里。并且记录 session_user, current_query, operation time, operation type 等信息。下面演示一下:

创建 users 表:

create database test_shadow;
\c test_shadow;
create table users (id integer primary key, name varchar,  age integer default 20);
insert into users values (1, 'name1', 30);
insert into users values (2, 'name2', 35);
insert into users values (3, 'name3', 35);

select * from users;
 id | name  | age |             sys_period
----+-------+-----+------------------------------------
  1 | name1 |  30 | ["2020-01-10 14:57:20.756974+08",)
  2 | name2 |  35 | ["2020-01-10 14:57:20.756974+08",)
  3 | name3 |  35 | ["2020-01-10 14:57:20.756974+08",)

导入 shadow.sql:

\i ~/w/shadow/sql/shadow.sql

select shadow.setup('users', 'users');

-- 实际执行过程,给 users 表添加 sys_period 字段
INFO:  EXECUTE SQL: ALTER TABLE users
    ADD COLUMN sys_period tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, null);
-- 静态copy users 的表结构,去掉约束,保留默认值
INFO:  EXECUTE SQL: CREATE TABLE shadow.users (
      LIKE users INCLUDING DEFAULTS EXCLUDING CONSTRAINTS EXCLUDING INDEXES INCLUDING COMMENTS
    )
-- 创建一个trigger,在insert or update or delete 过程前
INFO:  EXECUTE SQL: CREATE TRIGGER zzz_users_shadow_trigger
      BEFORE INSERT OR UPDATE OR DELETE ON users
      FOR EACH ROW EXECUTE PROCEDURE shadow.versioning(
        'sys_period', 'shadow.users', true
      )

看下效果:

update users set name = 'hello' where id = 1;
delete from users where id = 2;

-- users 表还是按照原来的逻辑,完全无影响,只是会记录sys_period
select * from users;
 id | name  | age |             sys_period
----+-------+-----+------------------------------------
  3 | name3 |  35 | ["2020-01-10 14:57:20.756974+08",)
  1 | hello |  30 | ["2020-01-10 14:59:11.48809+08",)

 -- 上面一条删除一条更新语句之后,产生了两个历史记录,是被修改前的记录快照,并且有当时执行的sql语句。               
 select * from shadow.users;
-[ RECORD 1 ]-------+------------------------------------------------------------------
id                  | 1
name                | name1
age                 | 30
sys_period          | ["2020-01-10 14:57:20.756974+08","2020-01-10 14:59:11.48809+08")
op                  | U
op_query            | update users set name = 'hello' where id = 1;
db_session_user     | hooopo
app_session_user_id | (null)
-[ RECORD 2 ]-------+------------------------------------------------------------------
id                  | 2
name                | name2
age                 | 35
sys_period          | ["2020-01-10 14:57:20.756974+08","2020-01-10 14:59:28.137144+08")
op                  | D
op_query            | delete from users where id = 2;
db_session_user     | hooopo
app_session_user_id | (null)

这个简单的 demo 已经满足了上面提到的 4 个要求,唯一不足的地方是,创建历史表的时候使用的是静态复制了目标表的结构,目标表之后添加修改或者删除字段,需要开发者自己去维护 shadow 表的结构和目标表一致。一个解决办法是使用 event trigger,PG 在 DDL 语句也可以使用 trigger,可以在修改目标表之后去刷新 shadow 表,但实际执行的 DDL 语句 pg_ddl_command 在非 C 扩展环境无法取得,所以这个方案在不使用 C 扩展的前提下就只能到这里了。

Shadow Table with json

https://github.com/hooopo/shadow/blob/master/sql/shadow_jsonb.sql

如果不想处理 shadow 表和目标表的结构同步,可以使用 json 这种 schemaless 的结构来存储历史变更,甚至还可以避免去处理一些不兼容的类型修改等问题,比如把一个字段从 char(10) 改成了 char(5),第一种方案需要把已经存进去的长度大于 5 的从 shadow 表里移除,才能保证结构同步成功。

下面演示 json 的效果:

create database test_shadow_jsonb;
\c test_shadow_jsonb;
create table users (id integer primary key, name varchar,  age integer default 20);
insert into users values (1, 'name1', 30);
insert into users values (2, 'name2', 35);
insert into users values (3, 'name3', 35);

select * from users;
 id | name  | age |             sys_period
----+-------+-----+------------------------------------
  1 | name1 |  30 | ["2020-01-10 14:57:20.756974+08",)
  2 | name2 |  35 | ["2020-01-10 14:57:20.756974+08",)
  3 | name3 |  35 | ["2020-01-10 14:57:20.756974+08",)

导入 shadow_jsonb.sql

\i ~/w/shadow/sql/shadow_jsonb.sql

select shadow.setup_jsonb('users', 'users');
INFO:  EXECUTE SQL: ALTER TABLE users
    ADD COLUMN sys_period tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, null);
INFO:  EXECUTE SQL: CREATE TABLE shadow.users ()

-- 需要目标表有一个主键,如果不是id可指定
INFO:  EXECUTE SQL: CREATE TRIGGER zzz_users_shadow_trigger
      BEFORE INSERT OR UPDATE OR DELETE ON users
      FOR EACH ROW EXECUTE PROCEDURE shadow.versioning(
        'sys_period', 'shadow.users', 'id', true
      )
 setup_jsonb

 -- shadow.users 的结构
 \d shadow.users
                                                     Table "shadow.users"
       Column        |       Type        | Collation | Nullable |                           Default
---------------------+-------------------+-----------+----------+--------------------------------------------------------------
 id                  | character varying |           |          |
 shadow_data         | jsonb             |           |          | '{}'::jsonb
 op                  | character(1)      |           |          | 'U'::bpchar
 op_query            | character varying |           |          |
 db_session_user     | character varying |           |          |
 sys_period          | tstzrange         |           | not null | tstzrange(CURRENT_TIMESTAMP, NULL::timestamp with time zone)
 app_session_user_id | character varying |           |          |
Indexes:
    "users_id_idx" btree (id)

看一下效果:

update users set name = 'hello' where id = 1;
delete from users where id = 2;

select * from shadow.users;
-[ RECORD 1 ]-------+------------------------------------------------------------------
id                  | 1
shadow_data         | {"id": 1, "age": 30, "name": "name1"}
op                  | U
op_query            | update users set name = 'hello' where id = 1;
db_session_user     | hooopo
sys_period          | ["2020-01-10 15:35:37.185797+08","2020-01-10 15:40:29.22283+08")
app_session_user_id | (null)
-[ RECORD 2 ]-------+------------------------------------------------------------------
id                  | 2
shadow_data         | {"id": 2, "age": 35, "name": "name2"}
op                  | D
op_query            | delete from users where id = 2;
db_session_user     | hooopo
sys_period          | ["2020-01-10 15:35:37.185797+08","2020-01-10 15:40:30.265188+08")
app_session_user_id | (null)

app_session_user_id 字段是用来保存应用层的用户信息,比如 Rails 里的 current_user.id

可以在 Rails before_action 里:

ActiveRecord::Base.connection.execute("select set_config('app.session_user_id', '#{current_user&.id}', false);")

相关文档:https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE

当然,这个方案不满足上面提到的第四条,因为 users 表和 shadow.users 表是不同结构的,对于显示来说,需要写两遍处理逻辑。一个可能的解决方案是,通过 AR 的 attributes 可以把 shadow_data 塞进去,模拟出和 users model 统一的接口,待尝试。

Shadow Table with updatable view

这个方案操作起来挺复杂的,主要解决了第一种方案里复制结构带来的手工维护问题。还是基于方案 1,既然静态复制结构需要维护,那么其实可以使用 PG 的表继承来产生一个和目标表完全一致的表结构。

create table shadow.users_v2(op char(1)) inherits (users);

但是带来一个新的问题:select * from users 的时候,shadow.users 里的数据也被查出来了,这个是继承的特性。

如果只查父表,可以使用 only 关键词: select * from only users,这样查出来的就是只有 users 表的数据。

所以我们可以产生一个 view:

create view only_users as (select * from only users)
class User < AR
  self.table_name = 'only_users'
end

更新呢?从 PG 9.3 开始,view 是 updatable 的:https://paquier.xyz/postgresql-2/postgres-9-3-feature-highlight-auto-updatable-views/
但有限制:

  • The view must have exactly one entry in its FROM list, which must be a table or another updatable view.
  • The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
  • The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.
  • The view’s select list must not contain any aggregates, window functions, or set-returning functions.

上面的 shadow.users_v2 满足这些条件,所以更新问题也解决了。唯一的不足是,开发者还是可以绕过 AR 的定义去直接拼 SQL 写成 select * from users。

所以,各个方案都不是那么完美,如果你的表结构很稳定,你可以选择方案 1,如果你不关心 view 层展示,你可以选择方案 2,如果你乐于踩坑,可以试试方案 3。

虽然还不是很完美,但替代 paranoia + Audited 还是挺不错的。

jasl mark as excellent topic. 10 Jan 16:23

https://github.com/paper-trail-gem/paper_trail paper_trail 这个是基于 model 的,感觉如果去追踪每一条历史的话还是可以,也可以记录是谁去改变了 但是他也有个问题,因为采用的多态记录,对于主键不是 int 类型也会出问题。

用过 paper_trail,还不错。

4 Floor has deleted

https://github.com/jhawthorn/discard#why-not-paranoia-or-acts_as_paranoid

跑个题,软删除的需求最好选择 discard,paranoia 侵入性太强了

方案三可以参考 https://github.com/ifad/chronomodel,应用层信息集成可能需要改

@Rei 基于 model 的方案主要问题是 callback 容易绕过

@coderliu 这个会触发 model 的 after destroy 吗 @novtopro 类似了,不过我觉得方案三得对原理非常熟悉才能玩的转 上面没提到,那个 view 还得刷新,因为 view 生成的时候会把 select *变成静态的,里面引用的表结构变了的话,是不能动态更新到 view 的,需要 create or replace view 一下

其实我感觉如果只是记录一些关键信息 https://github.com/palkan/logidze 这种方案就很好了。理想方案中除了不太适合分析外,其他的都满足。https://github.com/ifad/chronomodel 其实考虑到了视图、触发器的更新,但是如何与应用层信息集成没细看,好像没有提到。

实际操作人只能在应用层获取,我认为 paper_trail 这个轮子就很合适

说起查询历史状态让我想起曾经在一个不大供应链系统上尝试过 Event Sourcing ,挺好玩的, 存储的全是事件,通过视图可以查询任意时间的状态,因为状态是通过事件计算出来的。

不过,当依赖外部系统的数据 (比如微服务架构下,依赖用户服务时), 就需要在中间件将事件按来源和时间排序了。

Reply to acaby

es 其实最大的优势是编程模型,对于历史状态的回溯不如上面的方法,因为存的是状态,而不是事件,事件需要再计算才能得到状态,挺麻烦的,而且会受代码逻辑影响。

Reply to novtopro

其实 truncate 也可以 hook 掉

https://github.com/sirixdb/sirix

这个相关吗?来自外行人士的请教😀

Reply to luft

解决的问题类似

能否抽成 migrate gem,迁移自动生成对应的 shadow table,可选记录 action 和 changed_attributes? Awesome🐮 🍺

temporal table Teradata 差不多七八年前就原生支持了,用户不太喜欢用,概念太复杂。 event sourcing 的话为啥不直接去搞 wal log....... 触发器对性能有点影响 对于 pgsql 来说只要你不 vacuum 旧数据一直是存在的,当然要拿出来还有很多工作要做,我猜有插件。

event sourcing 怎么样

Reply to lithium4010

Event Sourcing 主要是用记账式实现业务过程,Audit Trail 支持 属于天生附带技能。

You need to Sign in before reply, if you don't have an account, please Sign up first.