Postgresql – Replication testing is a Materialized view Refresh equivalent of continious update

load-testingperformance-testingpostgresqlreplication

In my database I have the following a materialized view named materialized_view_test and the data are being feeded from 3 tables:

select 
 *
from
 a_table join b_table on b_table.a_id=a_table.id
 join c_table on b_table.c_id=c_table.id

As you can see the b_table is a pivot table for a_table and c_table. Also my database for testing purpoces has a master-slave replication with the slave to be on a hot stanby replication mode.

I try to stress my database in order to investigate which configuration on the following parameters is the optimal:

max_standby_streaming_delay
max_standby_streaming_delay
hot_standby_feedback

So I continiously run the following queries on my database over an infinite loop (for convenience lets name the test as test1):

  1. Set 1:
DELETE FROM b_table where a_id=12 and c_id=33
REFRESH MATERIALIZED VIEW materialized_view_test;
VACUUM (VERBOSE) materialized_view_test
  1. Set 2:
INSERT INTO b_table(a_id,b_id) VALUES (12,33);
REFRESH MATERIALIZED VIEW materialized_view_test;
VACUUM (VERBOSE) materialized_view_test

And I toggle between 2 sets into my database by checking a counter whether is odd or even, using a custom script. Afterwads, I read from the materialized view in a purpocely non-optimal written query in order to cause a load:

select * FROM (
  SELECT * FROM (
   SELECT *, pg_sleep(5) FROM b_table join b_table as b_table12 on btable.b_id=b_table12.b_id
   ) as dummy
) as dummy2

But in my database I rearely do any deletes. So I though if I was continiously toggling a boolean flag over an infinite loop in one of my tables (for convenience lets name the test as test2):

UPDATE a_table set some_flag = NOT some_flag where a_id=12;
VACUUM (VERBOSE) materialized_view_test;

And then continiously sellecting over it in a infinite loop:

select * FROM (
  SELECT * FROM (
   SELECT *, pg_sleep(5) FROM a_table join a_table as b_table12 on btable.a_id=b_table12.a_id
   ) as dummy
) as dummy2

So does in my situation the test1 can have same results and effects as running into test2 or I need both tests in order to test my replication schema.

Best Answer

For most practical purposes, UPDATE is pretty much the same as DELETE + INSERT in PostgreSQL. The difference between your tests is that test1 refreshes the materialized view twice and runs one more VACUUM.

Refreshing a materialized view is something entirely different. It will effectively drop the materialized view and re-create it from scratch. If you use REFRESH MATERIALIZED VIEW CONCURRENTLY, you will see activity similar to INSERT and DELETE.

  • max_standby_archive_delay does not apply to streaming replication at all.

  • max_standby_streaming_delay governs if there will be a delay in applying data changes on the standby server in the case of replication conflicts.

    Both your tests will eventually generate replication conflicts as soon as autovacuum removes old row versions from a_table or b_table, but only if hot_standby_feedback = off. If hot_standby_feedback = on, you will not see a replication conflict until a random autovacuum run truncates empty pages off the end of one of the tables, which requires a short ACCESS EXCLUSIVE lock.

    You could reliably trigger a replication conflict by selecting from the materialized view on the standby while it is being refreshed on the primary, because that takes an ACCESS EXCLUSIVE lock on the materialized view.

  • hot_standby_feedback: If set to on, then if either of your tests runs long enough while the query on the standby is running, you will slowly see the tables grow and get bloated, because old row versions won't get removed on the primary.

    If set to off you will get replication conflicts as soon as one of the tables is vacuumed.

Related Question