PostgreSQL 9.4版本的物化视图更新
PostgreSQL的9.4版本出来有一段时间了,也更新了很多内容,其中之一是比较感兴趣的物化视图的更新,对比原先的物化视图语法,新增
PostgreSQL的9.4版本出来有一段时间了,也更新了很多内容,其中之一是比较感兴趣的物化视图的更新,对比原先的物化视图语法,新增了一个CONCURRENTLY参数。
一、新语法:
二、数据准备:
[postgres@ ~]$ psql psql (9.4.1) Type "help" for help. postgres=# create table tbl_kenyon(id int,remark text); CREATE TABLE postgres=# insert into tbl_kenyon select generate_series(1,1000000),md5(random()::text); INSERT 0 1000000 postgres=# select * from tbl_kenyon limit 10; id | remark ----+---------------------------------- 1 | d4fc1c7440a4d1672028586c2bb76514 2 | 5c1590519fa47f02db2895146a5f62a4 3 | 1710ac4199746e9bfa188f1655d1f857 4 | 6cae64191c2bc309a4884301e77b26ad 5 | 813987a5c3af2d75bd0de6e288083b10 6 | c52baa42cda22c89719bfb59dde1f78b 7 | 491003337ea4e887c5ac24d174c691c6 8 | 455cdf32b170fcf2b450c0b974fbf310 9 | 43adb30aeb0a21ab35fdf97064ad1d21 10 | 97dc1adc5484244a077e87ef36ecfe09 (10 rows) --创建简单的物化视图 postgres=# create materialized view mv_tbl_kenyon as select * from tbl_kenyon ; SELECT 1000000 postgres=# \d+ List of relations Schema | Name | Type | Owner | Size | Description --------+---------------+-------------------+----------+-------+------------- public | mv_tbl_kenyon | materialized view | postgres | 65 MB | public | tbl_kenyon | table | postgres | 65 MB | (2 rows)三、测试用例:
--测试不带concurrently postgres=# insert into tbl_kenyon values(1000001,md5(random()::text)); INSERT 0 1 postgres=# select max(id) from mv_tbl_kenyon ; max --------- 1000000 (1 row) postgres=# \timing Timing is on. postgres=# refresh materialized view mv_tbl_kenyon ; REFRESH MATERIALIZED VIEW Time: 2056.460 ms --测试带concurrently,需要建一个唯一索引 postgres=# insert into tbl_kenyon values(1000002,md5(random()::text)); INSERT 0 1 Time: 9.434 ms postgres=# refresh materialized view concurrently mv_tbl_kenyon; ERROR: cannot refresh materialized view "public.mv_tbl_kenyon" concurrently HINT: Create a unique index with no WHERE clause on one or more columns of the materialized view. Time: 22109.877 ms postgres=# create unique index idx_ken on mv_tbl_kenyon(id); CREATE INDEX Time: 707.721 ms postgres=# select max(id) from mv_tbl_kenyon ; max --------- 1000001 (1 row) Time: 1.110 ms postgres=# begin; BEGIN postgres=# refresh materialized view concurrently mv_tbl_kenyon; REFRESH MATERIALIZED VIEW Time: 24674.739 ms --如果在refresh的时候,前面加个begin; --还能发现在开启的另外的session里面,是不会阻塞查询的,反之不加concurrently会阻塞 postgres=# select * from mv_tbl_kenyon limit 10; id | remark ----+---------------------------------- 1 | d4fc1c7440a4d1672028586c2bb76514 2 | 5c1590519fa47f02db2895146a5f62a4 3 | 1710ac4199746e9bfa188f1655d1f857 4 | 6cae64191c2bc309a4884301e77b26ad 5 | 813987a5c3af2d75bd0de6e288083b10 6 | c52baa42cda22c89719bfb59dde1f78b 7 | 491003337ea4e887c5ac24d174c691c6 8 | 455cdf32b170fcf2b450c0b974fbf310 9 | 43adb30aeb0a21ab35fdf97064ad1d21 10 | 97dc1adc5484244a077e87ef36ecfe09 (10 rows)四、源码
相关唯一索引的源码,在matview.c里面可以查看:
五、总结:
1.新版的物化视图新增了concurrently参数,可以使在刷新视图时不会锁住该物化视图的查询工作
2.该参数的原理和优缺点与索引的concurrently类似,以时间来换取查询锁,,刷新的速度会变得很慢
3.增量刷新的参数还没有,比较遗憾
------------------------------------华丽丽的分割线------------------------------------
CentOS 6.3环境下yum安装PostgreSQL 9.3
PostgreSQL缓存详述
Windows平台编译 PostgreSQL
Ubuntu下LAPP(Linux+Apache+PostgreSQL+PHP)环境的配置与安装
Ubuntu上的phppgAdmin安装及配置
CentOS平台下安装PostgreSQL9.3
PostgreSQL配置Streaming Replication集群
如何在CentOS 7/6.5/6.4 下安装PostgreSQL 9.3 与 phpPgAdmin
------------------------------------华丽丽的分割线------------------------------------
PostgreSQL 的详细介绍:请点这里
PostgreSQL 的下载地址:请点这里
本文永久更新链接地址:
上一篇: MYSQLcluster分布式筹建笔记
下一篇: Thinkphp修改密码的实现