每当存在成长的机会时,扩展的最佳方式是找到一种在增量数据上重复流程的方法。
为了解释这一点,我们将被提到的表命名为“Tab”。
Tab
Number ID CreationTime
Index on creationtime column.
应用增量方法的关键是拥有一个单调递增的值。这里我们用'creationtime'来表示。
(a) 创建另一张表Tab_duplicate,其中包含一个额外的列'last_compute_timestamp'。
Tab_duplicate
Number ID Duplicate_count last_compute_timestamp
(b) 在“last_compute_timestamp”列上创建索引。
(c) 运行插入语句以查找重复记录并将其插入Tab_duplicate表中,并附带last_compute_timestamp。
(d) 对于重复执行:
- 安装pg_cron扩展(如果不存在),并自动执行此插入操作。https://github.com/citusdata/pg_cron
https://fatdba.com/2021/07/30/pg_cron-probably-the-best-way-to-schedule-jobs-within-postgresql-database/
或者
2. 使用shell脚本/Python脚本通过操作系统crontab在数据库上执行它。
由于last_compute_timestamp在每次迭代中都被记录并在下一次重复使用,因此它将是增量的并且始终很快。
演示:
步骤1:生产表
create table tab
(
id int,
number int,
creationtime timestamp
);
create index tab_id on tab(creationtime);
步骤2:复制捕获表,包括一个首次启动记录(第一次执行后可以删除)
create table tab_duplicate
(
id int,
number int,
duplicate_count int,
last_compute_timestamp timestamp);
create index tab_duplicate_idx on tab_duplicate(last_compute_timestamp);
insert into tab_duplicate values(0,0,0,current_timestamp);
步骤三: 将一些重复的条目输入到生产表中
insert into tab values(1,10,current_timestamp);
select pg_sleep(1);
insert into tab values(1,10,current_timestamp);
insert into tab values(1,10,current_timestamp);
select pg_sleep(1);
insert into tab values(2,20,current_timestamp);
select pg_sleep(1);
insert into tab values(2,20,current_timestamp);
select pg_sleep(1);
insert into tab values(3,30,current_timestamp);
insert into tab values(3,30,current_timestamp);
select pg_sleep(1);
insert into tab values(4,40,current_timestamp);
验证记录:
postgres=# select * from tab;
id | number | creationtime
1 | 10 | 2022-01-23 19:00:37.238865
1 | 10 | 2022-01-23 19:00:38.248574
1 | 10 | 2022-01-23 19:00:38.252622
2 | 20 | 2022-01-23 19:00:39.259584
2 | 20 | 2022-01-23 19:00:40.26655
3 | 30 | 2022-01-23 19:00:41.274673
3 | 30 | 2022-01-23 19:00:41.279298
4 | 40 | 2022-01-23 19:00:52.697257
(8 rows)
步骤4: 重复项已被捕获并验证。
INSERT INTO tab_duplicate
SELECT a.id,
a.number,
a.duplicate_count,
b.last_compute_timestamp
FROM (SELECT id,
number,
Count(*) duplicate_count
FROM tab,
(SELECT Max(last_compute_timestamp) lct
FROM tab_duplicate) max_date
WHERE creationtime > max_date.lct
GROUP BY id,
number) a,
(SELECT Max(creationtime) last_compute_timestamp
FROM tab,
(SELECT Max(last_compute_timestamp) lct
FROM tab_duplicate) max_date
WHERE creationtime > max_date.lct) b;
执行:
postgres=
postgres-
postgres-
postgres-
postgres-
postgres-
postgres(
postgres(
postgres(
postgres(
postgres(
postgres(
postgres(
postgres(
postgres-
postgres(
postgres(
postgres(
postgres(
INSERT 0 4
postgres=
验证:
postgres=# select * from tab_duplicate;
id | number | duplicate_count | last_compute_timestamp
0 | 0 | 0 | 2022-01-23 19:00:25.779671
3 | 30 | 2 | 2022-01-23 19:00:52.697257
1 | 10 | 3 | 2022-01-23 19:00:52.697257
4 | 40 | 1 | 2022-01-23 19:00:52.697257
2 | 20 | 2 | 2022-01-23 19:00:52.697257
(5 rows)
步骤5: 将更多的重复项插入到生产表中
insert into tab values(5,50,current_timestamp);
select pg_sleep(1);
insert into tab values(5,50,current_timestamp);
select pg_sleep(1);
insert into tab values(5,50,current_timestamp);
select pg_sleep(1);
insert into tab values(6,60,current_timestamp);
select pg_sleep(1);
insert into tab values(6,60,current_timestamp);
select pg_sleep(1);
步骤6: 执行相同的重复捕获SQL将仅捕获生产表中的增量记录。
INSERT INTO tab_duplicate
SELECT a.id,
a.number,
a.duplicate_count,
b.last_compute_timestamp
FROM (SELECT id,
number,
Count(*) duplicate_count
FROM tab,
(SELECT Max(last_compute_timestamp) lct
FROM tab_duplicate) max_date
WHERE creationtime > max_date.lct
GROUP BY id,
number) a,
(SELECT Max(creationtime) last_compute_timestamp
FROM tab,
(SELECT Max(last_compute_timestamp) lct
FROM tab_duplicate) max_date
WHERE creationtime > max_date.lct) b;
执行:
postgres=
postgres-
postgres-
postgres-
postgres-
postgres-
postgres(
postgres(
postgres(
postgres(
postgres(
postgres(
postgres(
postgres(
postgres-
postgres(
postgres(
postgres(
postgres(
INSERT 0 2
验证:
postgres=# select * from tab_duplicate;
id | number | duplicate_count | last_compute_timestamp
0 | 0 | 0 | 2022-01-23 19:00:25.779671
3 | 30 | 2 | 2022-01-23 19:00:52.697257
1 | 10 | 3 | 2022-01-23 19:00:52.697257
4 | 40 | 1 | 2022-01-23 19:00:52.697257
2 | 20 | 2 | 2022-01-23 19:00:52.697257
5 | 50 | 3 | 2022-01-23 19:02:37.884417
6 | 60 | 2 | 2022-01-23 19:02:37.884417
(7 rows)
这个重复捕获总是很快的,因为有两个原因:
它仅在您计划的最后一段时间的增量数据上工作。
扫描表以查找最大时间戳只发生在单列索引上(仅索引扫描)。
从执行计划中:
-> Index Only Scan Backward using tab_duplicate_idx on tab_duplicate tab_duplicate_2 (cost=0.15..77.76 rows=1692 width=8)
注意:如果您在表格tab_duplicate中有长时间的重复记录,您可以在定期时间内将记录去重,例如在一天结束时进行,这样会很快,因为TAB_DUPLICATE是一个聚合小表,而且该表对您的应用程序是离线的,而TAB是您的生产表,具有大量累积数据。
此外,在生产表上设置触发器也是可行的解决方案,但这会增加生产事务的开销,因为每次插入都需要执行触发器。