投稿
  1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL 9.4: REFRESH MATERIALIZED VIEW 新增 CONCURRENTLY 参数

• 数据库 • 阅读 47

PostgreSQL 9.3 版本开始支持物化视图(MATERIALIZED VIEW), 但物化视图的使用局限较大; 9.4 版本在物化视图方面做了改进, 例如 9.4 版本在刷新视图时新增 CONCURRENTLY 参数, 刷新过程中不会影响物化视图的查询操作. 下面通过实验演示。

刷新物化视图语法

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
[ WITH [ NO ] DATA ]

创建物化视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
[pg94@db1 ~]$ psql francs francs
psql (9.4beta1)
Type  "help"  for help.
  
francs=> create table test_1 (id int4, name text, create_time timestamp(6) without time zone default clock_timestamp());
CREATE TABLE
  
francs=> insert into test_1(id,name)  select generate_series(1,3),generate_series(1,3)  ||  'a';
INSERT 0  3
  
francs=> CREATE MATERIALIZED VIEW mv_test_1 as  select  *  from test_1;
SELECT 3
  
francs=>  select  *  From mv_test_1;
id | name | create_time
----+------+----------------------------
 1  |  1a  |  2014-05-21  00:05:51.751824
 2  |  2a  |  2014-05-21  00:05:51.752527
 3  |  3a  |  2014-05-21  00:05:51.752545
(3 rows)

刷新物化视图

sesstion A 刷新物化视图

1
2
3
4
5
6
7
8
9
10
11
12
 
francs=>  begin;
BEGIN

francs=>  select pg_backend_pid();
pg_backend_pid
----------------
 15936
(1 row)
  
francs=> refresh materialized view mv_test_1;
REFRESH MATERIALIZED VIEW
francs=>  --注意: session A 未提交

session B 查看 pg_locks

1
2
3
4
5
6
7
8
 
francs=>  select pid,mode,relation,granted from pg_locks where relation='mv_test_1'::regclass;
 pid | mode | relation | granted
-------+---------------------+----------+---------
15936  |  AccessShareLock  | 16408  | t
15936  |  ShareLock  | 16408  | t
15936  |  ExclusiveLock  | 16408  | t
15936  |  AccessExclusiveLock  | 16408  | t
(4 rows)

备注 : session B 用来查看物化视图 mv_test_1 上的锁情况, 这里刷新物化视图时获取的是 “AccessExclusiveLock”锁。

session C 查询 mv_test_1

1
2
3
4
5
6
7
8
 
francs=>  select pg_backend_pid();
pg_backend_pid
----------------
 16125
(1 row)
  
francs=>  select  *  from mv_test_1 ;
..  --  此时 session C 处于等待状态.

session B 再次查看 pg_locks

1
2
3
4
5
6
7
8
9
 
francs=>  select pid,mode,relation,granted from pg_locks where relation='mv_test_1'::regclass;
 pid | mode | relation | granted
-------+---------------------+----------+---------
16125  |  AccessShareLock  | 16408  | f
15936  |  AccessShareLock  | 16408  | t
15936  |  ShareLock  | 16408  | t
15936  |  ExclusiveLock  | 16408  | t
15936  |  AccessExclusiveLock  | 16408  | t
(5 rows)

备注: 说明 session C 已经被 session A 阻塞了,因为 session A 获取的是 AccessExclusiveLock 锁。

在线刷新物化视图

接着测试 CONCURRENTLY 参数的在线刷新方式。

session A
在线刷新物化视图

1
2
3
 
francs=> refresh materialized view CONCURRENTLY mv_test_1 ;
ERROR: cannot refresh materialized view "francs.mv_test_1" concurrently
HINT: Create a UNIQUE index with  no WHERE clause on one or more columns of the materialized view.

备注: 说是要创建不带 where 条件的 unique 索引.

创建索引

1
2
 
francs=> create unique index idx_mv_test_1 on mv_test_1 using btree (id);
CREATE INDEX

开启事务

1
2
3
4
5
6
7
8
9
10
11
12
13
 
francs=>  begin;
BEGIN

francs=>  select pg_backend_pid();
pg_backend_pid
----------------
 15936
(1 row)
  
francs=> refresh materialized view CONCURRENTLY mv_test_1 ;
REFRESH MATERIALIZED VIEW

francs=>  --注意: session A 未提交

session B 查看 pg_locks

1
2
3
4
5
6
7
 
francs=>  select pid,mode,relation,granted from pg_locks where relation='mv_test_1'::regclass;
 pid | mode | relation | granted
-------+------------------+----------+---------
15936  |  AccessShareLock | 16408  | t
15936  |  RowExclusiveLock  | 16408  | t
15936  |  ExclusiveLock | 16408  | t
(3 rows)

备注:可见在线刷新方式获取的是行锁 RowExclusiveLock 。

session C 查询 mv_test_1

1
2
3
4
5
6
7
 
francs=>  select  *  from mv_test_1 ;
id | name | create_time
----+------+----------------------------
 1  |  1a  |  2014-05-21  00:05:51.751824
 2  |  2a  |  2014-05-21  00:05:51.752527
 3  |  3a  |  2014-05-21  00:05:51.752545
(3 rows)

备注: session C 查询正在刷新的物化视图正常。

性能比较

插入 100 万数据

1
2
3
4
5
6
7
8
9
10
11
 
francs=> truncate test_1;
TRUNCATE TABLE
  
francs=> insert into test_1(id,name)  select generate_series(1,1000000),generate_series(1,1000000)  ||  'a';
INSERT 0  1000000
  
francs=> /dt+ test_1
List of relations
Schema  | Name |  Type |  Owner |  Size |  Description
--------+--------+-------+--------+-------+-------------
francs | test_1 | table | francs |  46 MB |

普通刷新

1
2
3
4
5
6
7
 
francs=> refresh materialized view mv_test_1;
REFRESH MATERIALIZED VIEW
Time:  6813.920 ms
  
francs=> refresh materialized view mv_test_1;
REFRESH MATERIALIZED VIEW
Time:  5137.114 ms

备注: 大概花了 5 秒左右。

在线刷新

1
2
3
4
5
6
7
 
francs=> refresh materialized view CONCURRENTLY mv_test_1;
REFRESH MATERIALIZED VIEW
Time:  35975.159 ms
  
francs=> refresh materialized view CONCURRENTLY mv_test_1;
REFRESH MATERIALIZED VIEW
Time:  37304.300 ms

备注:在线刷新花了 35 秒左右, 耗时为普通刷新方式的 7 倍左右。

再次理解 NO DATA 选项

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
francs=> /dm+ mv_test_1
List of relations
Schema  |  Name |  Type |  Owner |  Size |  Description
--------+-----------+-------------------+--------+-------+-------------
francs | mv_test_1 | materialized view | francs |  46 MB |
(1 row)
  
francs=> refresh materialized view mv_test_1 with  no data;
REFRESH MATERIALIZED VIEW
  
francs=> /dm+ mv_test_1
List of relations
Schema  |  Name |  Type |  Owner | Size |  Description
--------+-----------+-------------------+--------+------------+-------------
francs | mv_test_1 | materialized view | francs |  8192 bytes |
(1 row)

备注: no data 选项其实是清空物化视图, 之后物化视图无法查看。

验证:再次查看

1
2
3
 
francs=>  select  *  from mv_test_1 limit 1;
ERROR: materialized view "mv_test_1" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.

总结

  1. 在线刷新方式(CONCURRENTLY)在刷新物化视图过程中不会阻塞查询操作;
  2. 在线刷方式性能比普通方式慢很多, 从这里的测试标题来看, 慢了7 倍左右;
  3. 在线刷新方式要求物化视图上至少有一个 unique 索引, 并且这个索引不能是表达式索引或带 where 条件的部分索引。

参考

原创文章，作者：1402239773，如若转载，请注明出处：https://blog.ytso.com/tech/database/238059.html

赞 (0)
0 0
上一篇 2022年1月30日 08:12
下一篇 2022年1月30日 08:13

相关推荐

发表回复

登录后才能评论