Transaction streaming¶

With logical replication, transactions are decoded concurrently on the publisherbut are not sent to subscribers until the transaction is committed. If thechanges exceed logical_decoding_work_mem (PostgreSQL 13 and later), they arespilled to disk. This means that particularly with large transactions, therewill be some delay before they reach subscribers, and may entail additional I/Oon the publisher.

Beginning with PostgreSQL 14, transactions can optionally be decoded and sent tosubscribers before they are committed on the publisher. The subscribers savethe incoming changes to a staging file (or set of files) and apply them whenthe transaction commits (or discard them if the transaction aborts). This makesit possible to apply transactions on on subscribers as soon as the transactioncommits.

This functionality is available from PostgreSQL 14 (including EPAS 14 andEDB Postgres Extended 14) and has been back-ported to EDB Postgres Extended 13.

BDR enhancements¶

PostgreSQL’s built-in transaction streaming, as described above, has the followinglimitations:

while it's no longer necessary to spill changes to disk on the publisher, changes must

be written to disk on each subscriber

if the transaction aborts, the work (changes received by each subscriber

and the associated storage I/O) will have been wasted

However, from version 3.7, BDR supports Parallel Apply, enabling multiple writerprocesses on each subscriber, which is leveraged to provide the following enhancements:

decoded transactions can be streamed directly to a writer on the subscriber

decoded transactions do not need to be stored on-disk on subscribers

no need to wait for the transaction to commit before starting to apply the

transaction on the subscriber

Caveats¶

```
parallel apply must be enabled
```

if the workload consists of many small and conflicting transactions, this can lead to

frequent deadlocks between writers

!!! Note * ect streaming to writer is still an experimental feature and must

be used with caution. For specifically, it may not work well withconflict resolutions since the commit timestamp of the streaming may notbe available (as the transaction may not have yet committed on theorigin).

Configuration¶

Transaction streaming is configured in two locations:

at node level, via the GUC [bdr.default_streaming_mode](configuration#transaction-streaming)

at group level, via the function [bdr.alter_node_group_config()](nodes#bdralter_node_group_config)

Node configuration using bdr.default_streaming_mode¶

Permitted values are:

```
`off`
```
```
`writer`
```
```
`file`
```
```
`auto`
```

Default value is auto.

Note that to become effective, a change to this setting requires a restart of thepglogical receiver process for each subscription, which can be achieved with a serverrestart.

If bdr.default_streaming_mode is set any other value than off, thesubscriber will request transaction streaming from the publisher. How this willbe provided may also depend on the group configuration setting; see section XXXbelow for details.

Group configuration using bdr.alter_node_group_config()¶

The parameter streaming_mode in function bdr.alter_node_group_config()can be used to set the group transaction streaming configuration.

Permitted values are:

```
`off`
```
```
`writer`
```
```
`file`
```
```
`auto`
```
```
`default`
```

Default value is default.

The value of the current setting is contained in the column node_group_streaming_modefrom the view bdr.node_group. The value returned isa single char type and the possible values are D (default), W (writer),F (file), A (auto) and O (off).

Configuration setting effects¶

Transaction streaming is controlled at subscriber level by the GUCbdr.default_streaming_mode. Unless set to off (which disables transactionstreaming), the subscriber will request transaction streaming.

If the publisher is capable of providing transaction streaming, it wilstream transactions whenever the transaction size exceeds the threshold set inlogical_decoding_work_mem. The publisher usually has no control over whetherthe transactions will be streamed to a file or to a writer. Except for somesituations (such as COPY), it might hint the subscriber to stream thetransaction to a writer (if possible).

The subscriber can stream transactions received from the publisher toeither a writer or a file. The decision is based on several factors:

if parallel apply is off (`num_writers` = 1), then it will be streamed to a file

(writer 0 is always reserved for non-streamed transactions)

```
if parallel apply is on, but all writers are already busy handling streamed
```
- transactions, then the new transaction will be streamed to a file. See
- [bdr.writers](monitoring.md#Monitoring BDR Writers) to check BDR
- writer status.

If streaming to a writer is possible (i.e. a free writer is available), then thedecision whether to stream the transaction to a writer or a file is based uponthe combination of group and node settings as per the following table:

Group	Node	Streamed to
off	(any)	(none)
(any)	off	(none)
writer	file	file
file	writer	file
default	writer	writer
default	file	file
default	auto	writer
auto	(any)	writer

Note that if the group configuration is set to auto, or the groupconfiguration is default and the node configuration is auto,then the transaction is streamed to a writer if and only if thepublisher has hinted that this should be done.

Currently the publisher will hint for the subscriber to stream to writerfor the following transaction types, which are known to be conflict freeand can be safely handled by the writer in any case:

```
`COPY`
```
```
`CREATE INDEX CONCURRENTLY`
```

Monitoring¶

Usage of transaction streaming can be monitored via the bdr.stat_subscriptionfunction on the subscriber node.

`nstream_writer` - number of transactions streamed to a writer

`nstream_file` - number of transactions streamed to file

`nstream_commit` - number of committed streamed transactions

`nstream_abort` - number of aborted streamed transactions

`nstream_start` - number of streamed transactions which were started

`nstream_stop` -  number of streamed transactions which were fully received