Best Practices for Logical Decoding in Large Transactions and Massive Subtransaction Scenarios

What Is Logical Decoding?

Logical decoding is a core capability of RDS for PostgreSQL. It decodes physical write-ahead logs (WALs) into logical change events (such as INSERT, UPDATE, and DELETE), making it widely applicable for data synchronization, backup, and recovery.

The performance of logical decoding in RDS for PostgreSQL is heavily dependent on transaction behavior. Slow decoding speed or persistently high latency typically occurs when a large transaction contains a massive number of subtransactions, resulting in high resource utilization and a sharp drop in processing efficiency.

This section outlines the operational guardrails and best practices for logical decoding. You must strictly follow the requirements regarding transaction size, subtransaction usage, and configuration settings, and apply the recommended practices, such as transaction splitting, parameter tuning, and monitoring, to effectively prevent decoding bottlenecks and ensure stable, efficient logical decoding.

Why Do Large Transactions and Massive Subtransactions Slow Down Logical Decoding?

• Concepts of large transactions and massive subtransactions
  • Large transaction: In PostgreSQL versions earlier than 13, a transaction is considered large if it modifies more than 4,096 rows. In PostgreSQL 13 and later, a transaction is considered large if its WAL volume exceeds the value of logical_decoding_work_mem (64 MB by default). In RDS for PostgreSQL, any transaction that performs extensive data operations (for example, batch insert/update/delete exceeding 100,000 rows) and takes more than 5 seconds to complete is also treated as a large transaction.
  • Massive subtransactions: This refers to a high volume of subtransactions (typically exceeding 50) nested or concurrently executed within a large transaction. Subtransactions include explicit subtransactions (nested BEGIN/COMMIT) and implicit subtransactions (automatically created by triggers or stored procedures). In PostgreSQL, the EXCEPTION block in a PL/pgSQL function or stored procedure is the most common source of implicit subtransactions.
• Root causes of slow logical decoding

  Logical decoding involves reading WAL logs, parsing transaction changes, and generating logical events. Large transactions combined with massive subtransactions create bottlenecks across memory, I/O, and decoding logic.

  • Memory spills and disk I/O spikes: RDS for PostgreSQL uses logical_decoding_work_mem to control the maximum temporary memory (64 MB by default) available for logical decoding. When a large transaction contains massive subtransactions, the total change volume exceeds this threshold. The decoding process is forced to spill excess data to temporary disk files under the $PGDATA/pg_replslot/{slot_name} directory. Frequent disk reads and writes drastically increase the I/O load and slow down decoding.
  • Sharp increase in decoding complexity: Logical decoding must fully parse the start, changes, and commit/rollback of every transaction. Massive subtransactions increase context-switching overhead because each subtransaction's start, changes, and end must be parsed individually and the hierarchical relationship between subtransactions and the parent transaction must be maintained. Therefore, the decoding logic becomes highly complex, leading to a sharp decrease in processing efficiency.
  • High batch processing pressure at transaction commit: Logical decoding delivers change events to the output plugin (such as pgoutput) only after the entire transaction commits. When a large transaction contains massive subtransactions, all changes are processed in a single batch at commit time, causing a sudden spike in decoding pressure and noticeable latency.
  • Severe resource contention: The decoding process competes with the main database process and background processes (such as WAL writing and checkpoint operations) for CPUs, memory, and disk I/Os. Large transactions combined with massive subtransactions consume substantial resources, leaving insufficient resources for decoding and further slowing down the processing.

Operational Guardrails for Logical Decoding

To prevent slow logical decoding caused by large transactions and massive subtransactions, you must strictly adhere to the following operational guardrails when using RDS for PostgreSQL logical decoding. The core principle is: Prohibit nesting massive subtransactions within large transactions, and control both the transaction scale and the total subtransaction count.

• Transaction size guardrails
  • The total volume of data manipulations (INSERT, UPDATE, and DELETE) within a single transaction must not exceed 100,000 rows. If a large amount of data needs to be processed, split the workload into small batches, with each batch containing fewer than 10,000 rows.
  • The execution time of a single transaction should not exceed 5 seconds and should be kept under 1 second for core business scenarios. This prevents long-term resource occupation and reduces decoding wait time.
  • The volume of WAL records generated by a transaction must not exceed 80% of the logical_decoding_work_mem value to prevent triggering disk overflows.
• Subtransaction usage guardrails
  • Prohibit large transactions from nesting massive subtransactions. If the parent transaction is a large transaction, it must not contain any subtransactions (explicit or implicit). If subtransactions are required, ensure the parent transaction is a small transaction and the total number of subtransactions must not exceed 50.
  • Avoid implicit subtransactions. Avoid design patterns that automatically create subtransactions in triggers or stored procedures. If they are unavoidable, simplify the logic and reduce both the number and nesting depth of subtransactions.
  • Avoid subtransaction abuse. Avoid creating an independent subtransaction for each individual data operation, as this can cause the number of subtransactions to surge (for example, frequently creating subtransactions inside a loop).
• Logical decoding configuration guardrails
  • Ensure that wal_level is set to logical, which is a prerequisite for logical decoding. Otherwise, logical replication slots cannot be created and decoding will fail.
  • Adjust logical_decoding_work_mem appropriately based on your workload. The default value is 64 (unit: MB). You may increase it to 256–512 (unit: MB) if your system handles occasional large transactions. Ensure that the server memory is sufficient to prevent conflicts with other memory parameters.
  • Ensure that the value of max_replication_slots is greater than the number of logical replication slots in use to avoid decoding failures or delays caused by insufficient slot capacity.
• Business scenario guardrails
  • In scenarios such as batch data import or full-table update/deletion, transactions must be split to avoid generating large transactions. Do not use subtransactions in these scenarios.
  • In high-concurrency workloads (such as e-commerce order processing or real-time data ingestion), restrict the number of operations per transaction to avoid frequent creation of large transactions, which negatively affects decoding performance.
  • When using logical decoding for DRS synchronization tasks, ensure that the downstream consumption speed matches the upstream decoding speed. Otherwise, downstream consumption lag will cause WAL accumulation, making the decoding bottleneck worse.

Troubleshooting Logical Decoding Issues

1. Log in to the RDS console.
2. On the Instances page, click the instance name to go to the Summary page.
3. In the navigation pane, choose Sessions under DBA Assistant.
4. On the Sessions page, select the criterion Process Type to display this column in the session list and locate the walsender process type. If the process's CPU utilization consistently remains above 90%, the instance is highly likely experiencing decoding bottlenecks caused by large transactions or massive subtransactions. For more details, see Managing Real-Time Sessions.
   Figure 1 Real-time sessions
   
5. Query the pg_replication_slots view to check whether replication slots are active and whether WAL accumulation exists (indicated by a severely delayed restart_lsn).

   SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn, pg_size_pretty(pg_wal_lsn_diff(b, a.restart_lsn)) AS slot_latency FROM pg_get_replication_slots() AS a, pg_current_wal_lsn() AS b;

6. In PostgreSQL 14 or later, use the pg_stat_replication_slots view to check spill_count (number of transactions spilled to disk) and spill_bytes (total bytes spilled). This helps determine if frequent disk overflows occur.

   SELECT s.slot_name,s.active,st.spill_count,st.spill_bytes FROM pg_replication_slots s JOIN pg_stat_replication_slots st ON s.slot_name = st.slot_name WHERE s.active = 't';

7. If large transactions combined with massive subtransactions are detected, terminate the problematic transactions immediately. Split the workload into smaller transactions, adjust parameters, and optimize business logic. For details, see Best Practices for Logical Decoding.

Best Practices for Logical Decoding

• Transaction Optimization Practices
  • Splitting large transactions: Break large batch operations into small batches and execute them iteratively. The following is an example:

    -- Original large transaction (not recommended)
    BEGIN;
    UPDATE large_table SET column = 'new_value'; -- 10 million rows are affected.
    COMMIT;
    -- Small transactions after splitting (recommended)
    FOR i FROM 1 TO 100 LOOP
    BEGIN;
    UPDATE large_table SET column = 'new_value'
    WHERE id BETWEEN (i-1)10000 + 1 AND i10000; -- 10,000 rows per batch
    COMMIT;
    END LOOP;

  • Optimizing subtransactions: If multiple independent subtransactions exist, merge them into a single transaction when possible to reduce the subtransaction count.
  • Avoiding long-running transactions: Remove unnecessary operations inside transactions, such as external API calls and long waits. Ensure that transactions commit quickly and do not remain active for extended periods.
• Decoding Performance Optimization Practices
  • Parameter optimization
    • logical_decoding_work_mem: Increase the value of this parameter (for example, to 256–512 in the unit of MB) based on the available server memory to reduce disk overflows. logical_decoding_work_mem controls the memory available to each logical replication worker. If your instance runs multiple logical replication workers, each worker can use up to this limit independently. Set the parameter to an appropriate value to prevent out-of-memory (OOM) situations.
    • WAL-related parameters: Increase the values of wal_buffers and max_wal_size. This reduces the WAL switching frequency and improves the log reading efficiency of the decoding process.
  • Replication slot management

    Clean up unused replication slots: Unused replication slots continuously occupy resources, causing WAL accumulation. Clean them up periodically.

    1. Find unused replication slots.

       SELECT * FROM pg_replication_slots WHERE active='f';

    2. Drop the replication slots if you are sure that they are no longer needed.

       SELECT pg_drop_replication_slot('slotname');

  • Resource isolation: If the RDS for PostgreSQL instance is heavily loaded, upgrade instance specifications or isolate decoding-related resources (for example, deploying decoding tasks separately) to reduce resource contention. Non-failover replication slots are deleted after configuration changes. Ensure that you convert them to failover slots before initiating the changes. For details, see Failover Slot for Logical Subscriptions.

FAQ