12.5. Logical Decoding Output Plugins: pgoutput

Alpha Version: Work in progress.

The primary role of an output plugin is to serialize the change data—which has been organized and reordered by the ReorderBuffer — into a format compatible with sending to a subscriber.

The specific tasks performed by the plugin include:

• Data Transformation and Filtering: The plugin performs a final validation of the binary data within the ReorderBuffer against the publication’s configuration. It determines whether the target table or specific actions (e.g., INSERT, TRUNCATE) are included in the subscription and evaluates row filters or column lists. Once irrelevant data is excluded, the plugin formats the remaining payload into the appropriate data types and structures (e.g., text or binary) dictated by the replication protocol.
• Message Generation: The plugin constructs a logical sequence of messages, such as BEGIN, INSERT/UPDATE/DELETE/TRUNCATE, and COMMIT. These messages are passed to the walsender process, which manages the network sending.

As the name implies, this mechanism is pluggable. Custom plugins can be implemented to support unique output formats or specialized internal processing logic. In standard PostgreSQL logical replication, the officially supported pgoutput is used as the default plugin.

The pgoutput message format consists of a series of tagged data packets. The following sections describe the structure of these messages.

12.5.1. TupleData (shared sub-message)

The TupleData structure is a common sub-message used within DML operations to represent tuple contents.

[num_cols: Int16]
  For each column:
    [kind: Byte1('n'|'u'|'t'|'b')]
    if kind = 't' or 'b':
      [length: Int32] [value: Byte*n*]

12.5.2. Transaction Control

12.5.2.1. Begin (‘B’)

Identifies the start of a transaction. It provides the transaction’s LSN and commit timestamp, allowing the subscriber to maintain chronological order.

[Byte1('B')] [final_lsn: Int64] [commit_timestamp: Int64] [txid: Int32]

12.5.2.2. Commit (‘C’)

Identifies the end of a transaction. Upon receiving this, the subscriber applies the accumulated changes locally as a single atomic unit.

[Byte1('C')] [flags: Int8] [commit_lsn: Int64] [end_lsn: Int64] [commit_timestamp: Int64]

12.5.3. DML

12.5.3.1. Origin (‘O’)

Used in setups involving multi-node replication. It informs the subscriber where the transaction originally occurred to prevent replication loops.

[Byte1('O')] [origin_lsn: Int64] [origin_name: String]

12.5.3.2. Relation (‘R’)

Provides the metadata for a specific table. This message is typically sent before the first DML message for a table in a session, mapping a unique ID to the table’s schema and columns.

[Byte1('R')] [rel_id: Int32] [namespace: String] [relname: String]
             [replica_identity: Int8] [num_columns: Int16]
  For each column:
    [flags: Int8] [name: String] [type_oid: Int32] [atttypmod: Int32]

12.5.3.3. Insert (‘I’)

Represents the insertion of a new tuple. It includes the target relation ID and the TupleData of the new tuple.

[Byte1('I')] [rel_id: Int32] [Byte1('N')] [new_tuple: TupleData]

12.5.3.4. Update (‘U’)

Represents an update to an existing tuple. Depending on the REPLICA IDENTITY configuration and whether the key columns changed, it may include the old tuple’s values to allow the subscriber to identify the correct tuple to modify.

-- REPLICA IDENTITY DEFAULT or INDEX (key columns changed):
[Byte1('U')] [rel_id: Int32] [Byte1('K')] [old_tuple: TupleData] [Byte1('N')] [new_tuple: TupleData]

-- REPLICA IDENTITY FULL:
[Byte1('U')] [rel_id: Int32] [Byte1('O')] [old_tuple: TupleData] [Byte1('N')] [new_tuple: TupleData]

-- REPLICA IDENTITY DEFAULT or INDEX (key columns NOT changed):
[Byte1('U')] [rel_id: Int32] [Byte1('N')] [new_tuple: TupleData]

12.5.3.5. Delete (‘D’)

Represents the deletion of a tuple. The subscriber uses the provided key or old tuple data to locate and remove the record.

-- REPLICA IDENTITY DEFAULT or INDEX:
[Byte1('D')] [rel_id: Int32] [Byte1('K')] [old_key_tuple: TupleData]

-- REPLICA IDENTITY FULL:
[Byte1('D')] [rel_id: Int32] [Byte1('O')] [old_tuple: TupleData]

12.5.3.6. Truncate (‘T’)

Represents a bulk removal of all tuples in one or more tables.

[Byte1('T')] [num_relations: Int32] [options: Int8]
             [rel_id: Int32] ...   -- repeated num_relations times

12.5.4. Stream Control

The following messages are utilized when the publisher sends data in segments to support the Streaming of Large Transactions.

For further details, refer to Section 12.6.3.

12.5.4.1. Stream Start (‘S’)

Identifies the beginning of a stream segment. This is used when a large, ongoing transaction is split into multiple segments for sending.

[Byte1('S')] [txid: Int32] [first_segment: Int8]

12.5.4.2. Stream Stop (‘E’)

Identifies the end of a stream segment.

[Byte1('E')]

12.5.4.3. Stream Commit (‘c’)

Identifies the commit of a streamed transaction. It contains similar information to a standard Commit (‘C’) but operates within the streaming context.

[Byte1('c')] [txid: Int32] [flags: Int8] [commit_lsn: Int64] [end_lsn: Int64] [commit_timestamp: Int64]

12.5.4.4. Stream Abort (‘A’)

Identifies the abort (rollback) of a streamed transaction. This is also used for the abort of subtransactions.

[Byte1('A')] [txid: Int32] [subxid: Int32] [abort_lsn: Int64]? [abort_timestamp: Int64]?