Overview

Performance considerations

Learn what drives a pipeline's throughput and lag, and where to place agents and log brokers for the best results.

A pipeline is as fast as its slowest stage: reading the source, moving changes over the network, or writing the target. This page explains what drives each stage, and what you can change. The linked pages hold the details.

Capture mode

The capture mode sets the lowest lag a pipeline can reach, and how much work the source does for it:

DirectLog (SQL Server, Oracle and Sybase ASE) has lag in milliseconds. The log broker reads log files, and no query runs on the source to find changes.
Reconstruction (SQL Server and Sybase ASE) reads the log as it is written, and adds keyed reads of current rows for updates the log holds only partially.
PostgreSQL logical replication and MySQL binlog stream each change at commit. The server does the decoding.
SQL Server Polling and Oracle LogMiner have lag in seconds, and every read is query work on the source.
Native CDC (SQL Server and Azure SQL) lags by the CDC capture job's scan interval, and the source runs the capture and cleanup jobs.

See Choosing a capture mode for the full comparison.

Two source behaviors affect lag in every mode:

Large transactions. A change becomes visible to SQL Flow when its transaction commits. PostgreSQL, for example, decodes a transaction only at commit, so a very large one arrives at once.
Log volume. DirectLog and Reconstruction read the whole log of the database, including changes to tables the pipeline doesn't capture.

Agent placement

Capture. Put the capture agent close to the source. With DirectLog and Reconstruction, the log broker sends the agent the database's whole log, and for Sybase ASE Reconstruction also data pages, so a fast link between the agent and the database host matters most. The agent can run on the database host itself.
Apply. A target in another region or cloud slows apply, because every commit waits for a long network round trip. See Distributed acceleration for remote targets.
One agent. When the source and target are on the same network, one agent near both is the simplest and fastest setup.

Log brokers

A log broker only serves file bytes; the agent does the decoding, so the broker adds little load to the database host. If the broker stops, capture stops and lag grows. The pipeline restarts automatically, up to 5 times in 30 minutes before it is parked for an operator. A standby broker lets capture continue meanwhile.

Initial load

Load workers. An agent loads several tables, and parts of large tables, in parallel. SQLFLOW_LOAD_WORKERS sets how many workers run: by default the number of processors, at most 8, and from 1 to 64. Raise it when the target's writers spend most of their time waiting on commits; lower it when a load takes too many connections from the source or the target. See Sizing and tuning.
Loads that share resources. Loads of different pipelines run side by side. A load that must wait for another shows Queued, and a notice names the pipelines that share its agent, source or target. See Initial load: Loads that share resources.
Source reads. The load reads the source tables, so it adds read load to the source while it runs.

Target apply

How a target writes is the most common limit on throughput.

Collapse row versions (faster). On by default for SQL Server, Azure SQL, SQL database in Fabric, PostgreSQL and Oracle. SQL Flow applies changes in sets and writes only each row's final state. Tables where that isn't safe, for example ones with triggers, are applied row by row instead. Untick it only when a target must see every change.
Ordered targets. MySQL and Sybase ASE targets always apply changes one statement at a time, in source order, so the wizard doesn't offer Collapse row versions (faster) for them.
Atomic transaction limits. SQL Flow keeps each source transaction whole. On database targets, Maximum records per transaction (default 1,000,000) and Maximum transaction size (MiB) (default 512) set the largest source transaction the target accepts; apply stops above them rather than split the transaction.
Azure SQL log rate. An Azure SQL database caps its log write rate by service tier. The option applyAdaptiveResourceGovernance=true, set with the CLI, makes apply shrink its batches as the rate nears the cap. See Azure SQL: Adaptive resource governance.
Fabric Open Mirroring. SQL Flow writes one file per table every 5 seconds by default, set by publishIntervalMs, and the lag SQL Flow shows doesn't include Fabric's own ingestion.

See each target page for its options.

Distributed acceleration for remote targets

When the target is far from the source, in another region, data center or cloud, the distance between them can limit throughput more than either database does. Distributed acceleration splits the pipeline across two relay agents, so the long-distance link carries compressed changes, and the apply agent writes the target over its own local network. It's available for every source and capture mode.

Two setups compared. Without relay, one agent at the source site captures, spools and applies: it sends each apply batch across the WAN to the target at the target site and waits for the commit answer to come back. With relay, a capture agent at the source site sends compressed containers across the WAN, over mutual TLS on TCP 7420, to an apply agent at the target site, which returns durable acks and writes the target with local round trips. A note says the arrows show where data and replies go, not which side connects: the capture agent dials the apply agent.

Without a relay, each commit on a remote target waits at least one WAN round trip, so throughput falls as the round-trip time grows, whatever the bandwidth or the target's capacity. Moving the one agent to the target's site doesn't help: the agent then reads the source across the same link, and with DirectLog and Reconstruction that means the database's whole log. See The long-distance penalty.

What relay changes

Local apply. The apply agent commits to the target over the target site's network. The target's credentials go only to the apply agent.
A full link. The capture agent compresses changes and keeps many packets in flight, so it pays the WAN round trip once for many changes. It compresses harder while it's catching up, and initial loads are compressed too.
Large values are slower. Large values cross the link separately, at about one round trip per MiB, so tables with many large values relay more slowly.
Nothing lost on a broken link. While the link is down, capture continues into the capture agent's spool, and on reconnect the capture agent resends what the apply agent hasn't stored.

See How relay works.

When to use relay

Use relay when the target is in another region, data center or cloud, when loads or catch-up must cross a slow or metered link, or when the target's credentials must stay inside its network. See When to use relay.

Size the relay

Bandwidth. The link must carry the change volume after compression, with headroom to catch up after an outage or during an initial load. On a running pipeline, open Details under the metrics and compare wire:, the rate on the link, with the link's capacity. wire: doesn't count large values, so leave room for them too. See Bandwidth.
Capture agent disk. While the link is down, the capture agent's spool grows as it does for a target that is down. Size its disk and the source log retention for the longest outage you want to ride out.
CPU. The capture agent decodes and compresses, harder while the relay is behind. The apply agent decompresses and applies. See Size the agents.
Shared apply agents. One apply agent can serve several pipelines on one port. Relays have no rate limit, so one pipeline catching up can use the whole link. Size the link for the pipelines that may catch up at the same time.
Network. Open TCP port 7420, unless you change it, on the apply agent's host. See Open the relay port.

For a complete setup, see On-premises SQL Server → Azure SQL over a WAN with relay agents.

Large values

Reads by key. Where the log can't supply a large value, the agent reads it from the source table by key: SQL Server Polling and Reconstruction, and DirectLog before SQL Server 2022 except for xml. Each read is a query on the source.
Oracle history. Oracle DirectLog and LogMiner read some large values as of the commit, which needs undo history and adds queries.
Message limits. Kafka accepts messages up to 1,000,000 bytes and Google Pub/Sub up to 10,000,000 bytes. A larger change stops the pipeline.
Disk. The agent keeps large values on disk, not in memory, so plan disk space for the large values in flight.

See Large values and LOBs.

Agent resources

Disk. The spool holds every change that a target hasn't committed. It grows while a target is slow or down, by the source's change volume, up to a limit of at most 60 percent of the free space on its volume. At that limit, a SQL Server DirectLog capture that holds the source log waits. In every other case the spool drops its oldest changes, and a target that still needed them fails and needs a reload. See Lag, spool and log retention: When the spool reaches its limit.
Memory. The agent sizes itself from the memory its host or container reports. Cap it with --memory-limit on a shared machine. A pipeline that the agent's memory can't hold is refused at start. See Agents.
CPU. The capture agent decodes the log. For relay agents, see Size the relay.

Lag and the source log

A SQL Server DirectLog pipeline holds the source log until the target commits, so the log grows while apply is behind. Other sources need only the log that capture hasn't saved in the spool yet: a target that is behind reads its changes from the spool. A PostgreSQL replication slot keeps WAL until SQL Flow consumes it, also while the pipeline is paused. Watch the Log retention card and the source log alerts. See Source log retention and Lag, spool and log retention.

If this was useful,
our newsletter will be too.

Get monthly insights on data engineering, AI, and building critical infrastructure - direct from the Spectral Core team and CEO Damir Bulic.