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 还是挺不错的。