Skip to main content

Posts

Featured

Automatically updating materialized views

Materialized views are convenient and efficient way to retrieve information from database. Unlike ordinary views, materialized views save the query result and provide faster access to the data. For example, user can create a simple materialized view containing the number of rows in a table:

 CREATE MATERIALIZED VIEW mv1 AS SELECT count(*) FROM pgbench_accounts;

Obtaining the number of rows using the materialized view mv1 is much faster than  directly accessing number of rows in pgbench_accounts.

test=# SELECT * from v1;
 count 
--------
 100000
(1 row)

Time: 0.810 ms
test=# SELECT count(*) FROM pgbench_accounts;
 count 
--------
 100000
(1 row)

Time: 13.945 ms

However if you delete a row from pgbench_accounts, the number of rows in mv1 is still unchanged. To reflect the change of the base table (in this case pgbench_accounts) , you need to recreate or refresh (this actually recreate the contents of materialize views from scratch), which may take long time.

To overcome the problem, SRA OSS is pr…

Latest Posts

When primary server is far away from standby server

Statement level load balancing

Shared Relation Cache

log_client_messages in Pgpool-II 4.0

Pgpool-II 4.0 released! (SCRAM authentication)

Even more fine load balancing control with Pgpool-II 4.0

statement_timeout changed in PostgreSQL 11

More load balancing fine control

Detecting "false" primary server of PostgreSQL

Celebrating 15th anniversary of Pgpool and next version number