数据库 Shadow Table for Postgres

hooopo · 2020年01月10日 · 最后由 qq2729877005 回复于 2020年01月20日 · 2248 次阅读
本帖已被设为精华帖!

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 将本帖设为了精华贴 01月10日 16:23

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

用过 paper_trail,还不错。

4楼 已删除

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 ,挺好玩的, 存储的全是事件,通过视图可以查询任意时间的状态,因为状态是通过事件计算出来的。

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

acaby 回复

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

VincentJiang 回复

并不是

novtopro 回复

其实 truncate 也可以 hook 掉

https://github.com/sirixdb/sirix

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

luft 回复

解决的问题类似

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

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册