Beginning with BDR 3.7 a new feature called Parallel Apply was added whereby the receiver has a writer group instead of a single writer. This means that all writers in the group can apply transactions in parallel while preserving the commit order from the source. This article explains the differences in the Parallel Apply settings and requirements among the BDR versions and how to enable Parallel Apply.
Parallel Apply is not available on BDR 3.6.
On BDR 3.7, Parallel Apply is available only for EE (Enterprise Edition), but disabled by default. In order to enable it, it's required to specify a number higher than 1 in both settings, for example:
pglogical.max_writers_per_subscription = 2
pglogical.writers_per_subscription = 2
Setting pglogical.max_writers_per_subscription determines system-wide what's the maximum number of writers per subscription, while setting pglogical.writers_per_subscription applies the specified number of writers for all existing subscriptions.
On PGD 4 and newer, Parallel Apply is available on all Postgres flavors (community PostgreSQL, EDB Postgres Extended and EPAS). These are the default settings (note how the name has changed from pglogical to bdr):
bdr.max_writers_per_subscription = 8
bdr.writers_per_subscription = 2
Which means that Parallel Apply is enabled with 2 writers per subscription, with a maximum of 8. Changing the maximum is still possible (although not recommended to go above 8 writers per subscription), but it requires a Postgres restart.
One caveat, though, is that Parallel Apply on BDR 4 is not compatible with Group Commit, so one of them should remain disabled.
On all nodes:
ALTER SYSTEM SET pglogical.max_writers_per_subscription = 2;
ALTER SYSTEM SET pglogical.writers_per_subscription = 2;
Note that, as explained above, Parallel Apply on BDR 3.7 works only on the EE (Enterprise Edition), which means that it requires EDB Postgres Extended, formerly known as 2ndQPostgres, or EPAS. If you try to enable Parallel Apply on BDR 3.7 SE (Standard Edition), you get this:
bdrdb=# ALTER SYSTEM SET pglogical.max_writers_per_subscription = 2;
NOTICE: skipped replication for captured DDL command "ALTER SYSTEM" in replication sets (bdrgroup): this statement type is not replicated by BDR
ERROR: 2 is outside the valid range for parameter "pglogical.max_writers_per_subscription" (1 .. 1)
Which means that it's not possible to change pglogical.max_writers_per_subscription on SE, and hence it's not possible to enable Parallel Apply on SE.
On EE, changing pglogical.max_writers_per_subscription requires a Postgres restart.
After restarting, the settings are now reflecting 2 writers per subscription.
bdrdb=# SHOW pglogical.max_writers_per_subscription;
pglogical.max_writers_per_subscription
2
(1 row)
bdrdb=# SHOW pglogical.writers_per_subscription;
pglogical.writers_per_subscription
2
(1 row)
On a 3-node BDR 3.7 cluster, for example on node1, check bdr.workers, you will see that there are 2 subscriptions, 2 receivers but 4 writers:
bdrdb=# SELECT * FROM bdr.workers ORDER BY 3, 4;
worker_pid | worker_role | worker_role_name | worker_subid | worker_commit_timestamp | worker_local_timestamp
4190 | 5 | bdr autopart 1148549230 | | |
4189 | 5 | bdr consensus 1148549230 | | |
4186 | 1 | manager | | |
4229 | 2 | receiver | 148744716 | 2021-12-08 06:11:20.44671+00 | 2021-12-08 06:11:20.447951+00
4265 | 2 | receiver | 2183595119 | 2021-12-08 06:11:20.446699+00 | 2021-12-08 06:11:20.447794+00
4230 | 3 | writer | 148744716 | |
4231 | 3 | writer | 148744716 | |
4266 | 3 | writer | 2183595119 | |
4267 | 3 | writer | 2183595119 | |
(9 rows)
After Postgres is restarted, it's still possible to change pglogical.writers_per_subscription without a restart, but then all subscriptions need to be bounced on all nodes. For example:
- Adjust
pglogical.writers_per_subscriptionto the desired value:
ALTER SYSTEM SET pglogical.writers_per_subscription = 2;
- Reload the configuration value without restarting:
SELECT pg_reload_conf();
- Disable and Enable again all subscriptions:
SELECT pglogical.alter_subscription_disable(sub_name)
FROM pglogical.subscription;
SELECT pglogical.alter_subscription_enable(sub_name)
FROM pglogical.subscription;
Changing pglogical.writers_per_subscription, the BDR group will remain with node_group_num_writers = -1, meaning that it's inheriting the value of pglogical.writers_per_subscription:
bdrdb=# SELECT node_group_name, node_group_num_writers FROM bdr.node_group;
node_group_name | node_group_num_writers
bdrgroup | -1
(1 row)
As long as pglogical.max_writers_per_subscription is already set, it's alternatively possible to change the default number of writers for the group:
SELECT bdr.alter_node_group_config(
node_group_name := 'bdrgroup',
num_writers := 2
);
However this doesn't apply for existing subscriptions, it only applies for new subscriptions, i.e., new nodes being joined to the group.
Another point is that the num_writers in the parameter above can't be higher than pglogical.max_writers_per_subscription.
Changing pglogical.writers_per_subscription, the pglogical subscription will remain with sub_num_writers = -1, meaning that it's inheriting the value of pglogical.writers_per_subscription:
bdrdb=# SELECT sub_name, sub_num_writers FROM pglogical.subscription;
sub_name | sub_num_writers
bdr_bdrdb_bdrgroup_node2_node1 | -1
(1 row)
As long as pglogical.max_writers_per_subscription is already set, it's alternatively possible to change the number of writers for an existing subscription by using the following:
SELECT pglogical.alter_subscription_num_writers(
subscription_name := 'bdr_bdrdb_bdrgroup_node2_node1',
num_writers := 2,
immediate := true
);
The bdr_bdrdb_bdrgroup_node2_node1 above is the name of a subscription you can confirm from the bdr.subscription_summary view.
Another point is that the num_writers in the parameter above can't be higher than pglogical.max_writers_per_subscription.
As explained above, on PGD 4 and newer, Parallel Apply is already enabled by default:
bdr.max_writers_per_subscription = 8
bdr.writers_per_subscription = 2
The same concepts from BDR 3.7 apply for BDR 4, except:
1- There is no pglogical anymore, so the settings and metadata have changed from pglogical to bdr;
2- There is no function pglogical.alter_subscription_num_writers(), but it's still possible to change the number of writers for a specific subscription, without needing a Postgres restart, like this:
SELECT bdr.alter_subscription_disable
('bdr_bdrdb_bdrgroup_node2_node1');
UPDATE bdr.subscription
SET num_writers = 4
WHERE sub_name = 'bdr_bdrdb_bdrgroup_node2_node1';
SELECT bdr.alter_subscription_enable
('bdr_bdrdb_bdrgroup_node2_node1');
The above recipe changes the number of writers of a specific subscription to 4, overriding the current setting bdr.writers_per_subscription = 2, as long as the number of writers specified is not higher than bdr.max_writers_per_subscription.
Changing bdr.max_writers_per_subscription still requires a Postgres restart.