Performance tuning

A migration's phases have different bottlenecks and respond to different knobs. The base copy is bound by the source read, the network, and the target write. Index builds are bound by the target's CPU and memory, and they happen after the data is in. In follow mode, source decoding, receive-spool writes, inline transformation, and target apply can each limit progress; measure the pipeline rather than assuming the target is the bottleneck.

Follow receive and apply

The bundled pgcopydb 0.18.15.gea2dc96 retains the receive batching from 0.18.10.gaadc4bf: SQLite transactions replace per-row and per-column commits, with SQLite synchronous=FULL unchanged. Fork PR #7 records the patch and these measurements, for one source transaction containing 20,000 four-column rows. These measurements predate the certified keepalive feedback in 0.18.13.g4873c18 and the bootstrap recovery in 0.18.15.gea2dc96; they measure neither version. The before/after runs used the same configuration, but not identical volume or cache state. Before values cover the row-only receive window; after values include the SQLite batch commit:

Storage	fsync calls, before to after	Time inside syncs	Receive window
Longhorn	100,831 to 3	314.08 s to 0.18 s	324.04 s to 0.98 s
Local NVMe	100,774 to 3	125.90 s to 0.04 s	133.67 s to 0.82 s

[!important] There is no retained commit-inclusive baseline trace. The conservative mixed-window ratios are about 330x on Longhorn and 164x on NVMe: receive-window speedups, not end-to-end migration speedups. These individual bursts do not establish sustained throughput or measure target WAL durability waits.

Apply confirms each target COMMIT before publishing data progress and uses synchronous_commit=on for each source transaction. That durability wait may raise latency for workloads with many small transactions; its cost is unmeasured. Batching removes the measured per-insert sync cost, not the need to rehearse catch-up under the intended workload. A backlog above the catch-up threshold cannot drain while source changes keep arriving faster than the whole pipeline can process them.

What the operator decides

spec.clone fields are optional, and a zero value means the operator decides. For most fields that means pgcopydb's own default. These five are the exceptions, because a default that suits an ad-hoc copy does not suit a migration.

Field	Unset behaviour	Why
runner.resources	4 CPUs, 4Gi, requests only	pgcopydb runs four table jobs by default, and a worker that requests less CPU than its own concurrency starves it.
clone.tableJobs	Follows the worker's CPU request, minimum 4	One knob instead of two: raise the worker and the copy concurrency follows.
clone.splitTablesLargerThan	512Mi	pgcopydb ships this disabled, which leaves one large table to one worker however many table jobs are running.
clone.splitMaxParts	8	Without a cap, a very large table fans out into hundreds of parts and catalog rows.
clone.useCopyBinary	true	Text COPY doubles bytea on the wire, and the worker relays every row, so the cost lands on both legs.

Everything else is pgcopydb's default: four index jobs, restore jobs following index jobs, four large-object jobs.

[!important] Raising spec.runner.resources.requests.cpu sizes the worker and the copy concurrency together, so there is no second field to keep in step.

Same-table concurrency

Same-table concurrency matters when a handful of tables hold most of the bytes. Without it, --table-jobs parallelises across tables only: the biggest table gets one worker, and the clone cannot finish before that one worker does. With it, a table past the threshold is split into parts that are handed to separate workers out of the same table-jobs pool.

The part count is the table size divided by the threshold, capped by splitMaxParts. Lowering the threshold makes more tables eligible and splits large ones further.

A table needs a single-column integer primary or unique key to be split on key ranges. Without one, pgcopydb falls back to splitting on ctid, which follows physical layout rather than key order, so each part is a separate scan over its own page range. skip: [ctidSplit] disables that fallback if the source cannot afford the extra scans.

[!warning] pgcopydb disables same-table concurrency when the source connection lands on a standby, and says nothing about it. A migration pointed at a read replica gets no splitting and no warning.

On a NOT NULL key, one of the parts is the NULL range and copies zero rows, so a cap of 8 yields 7 useful parts. Splitting also gives up the COPY FREEZE path for that table, which is usually a good trade for a table large enough to be worth splitting.

Index jobs, and the 1GB you cannot see

clone.indexJobs has no operator default, because its cost lands on a machine the operator cannot see.

Each index worker opens one target connection and immediately sets maintenance_work_mem to 1GB. pgcopydb hardcodes that and applies it per worker, overriding whatever the target itself is configured with. So the real cost of this number is roughly indexJobs GB of target memory, and pgcopydb's default of four asks for 4GB.

Size it against the target: its core count, minus what the COPY workers are already using there, and no more than its memory can carry at 1GB each. On a small target, four is already too many, and lowering it to 2 makes the index phase faster.

clone.restoreJobs follows indexJobs unless you set it. Set it separately when they differ: pg_restore is a separate process and never receives that GUC, so it is not bound by the same memory ceiling.

Table jobs and the connections they cost

Each table job holds one source connection and one target connection. pgcopydb also sizes its VACUUM ANALYZE pool from the same number and runs it alongside the copy, so N table jobs can mean up to 2N concurrent backends on the target.

Check the target's max_connections before raising anything. The worst case is roughly tableJobs * 2 + indexJobs + largeObjectsJobs + 1: table jobs twice over because of the vacuum pool, then one per index worker, one per large-object worker, and one for its metadata pass. The source sees the same table and large-object connections, without the index and vacuum ones. skip: [vacuum] removes the vacuum half if the target will be analysed separately after cutover.

clone.largeObjectsJobs costs four connection pairs by default, whether or not there is anything to move through them: see largeObjectsJobs.

Binary COPY

clone.useCopyBinary sends COPY WITH (FORMAT BINARY), and it is on by default. Text-format COPY encodes bytea as hex, two wire bytes per data byte, and the worker relays every row between source and target, so the cost is paid on both legs. On a database whose bytes are mostly bytea, this is a large saving; on one that is mostly narrow rows, it is close to nothing.

It is safe to leave on because the choice is made per table, not once for the migration. pgcopydb checks every column of a table against the source catalog and falls back to text COPY for that table when any column's binary encoding is not safe, logging a notice when it does. Set useCopyBinary: false to force text everywhere.

Knobs left at pgcopydb's default, and why

largeObjectsJobs

Stays at pgcopydb's four, because there is no value that is right for both kinds of database and the operator cannot tell which one it is pointed at.

Large objects live in pg_largeobject and are copied by a pool of their own, separate from the table COPY pipeline: one metadata worker plus N data workers, each holding a source connection and a target connection. So the default costs four connection pairs on both servers whether or not there is anything to move through them, and on a database with no large objects that is eight connections doing nothing. Drop it to 1 and a genuinely blob-heavy database loses most of its parallelism in the one phase that needed it.

Ask the source which case you are in:

SELECT count(*), pg_size_pretty(sum(pg_column_size(data))) FROM pg_largeobject;

• No rows: use skip: [largeObjects]. The pool is then not created at all, which beats setting the job count to 1 because it also skips the metadata pass.
• A handful, or a few MB: set largeObjectsJobs: 1. The copy is short either way and the connections are better spent elsewhere.
• Many, or a large total: leave the default, and raise it if the large-object phase is visibly the tail of your migration.

[!note] Large objects are not the same thing as bytea. A column of type bytea, however big, is ordinary table data and is copied by the table workers. Only lo_* and oid-referenced objects go through this pool, so most databases want skip: [largeObjects] rather than a job count.

restoreJobs

Follows indexJobs unless you set it, which is pgcopydb's behaviour and not the operator's. Lowering indexJobs to protect the target's memory silently lowers the restore parallelism too, even though pg_restore runs as a separate process and never receives the 1GB maintenance_work_mem that constrains index jobs. Whether that is a problem depends on whether the target is short of memory or short of cores, which again is not something the operator can see. Set both explicitly when they should differ.

Tuning the target

These are worth changing on a target that is being loaded, and safe to leave in place on one that becomes production.

max_wal_size is the one that matters most. A bulk load reaches it long before checkpoint_timeout, and every checkpoint restarts full-page-image writing for every page touched afterwards. Size it against the volume, not as a flat number: WAL lives inside PGDATA, so a value comfortable on a large volume fills a small one and stops the server with a low-disk condition.

maintenance_work_mem on the server is largely moot during the index phase, because pgcopydb overrides it per index worker as described above. It still applies to anything you build yourself afterwards.

shared_buffers and effective_cache_size matter on both sides. checkpoint_timeout only binds when the load is slower than max_wal_size divided by it, so on a fast load it is inert.

Two settings are not worth changing:

synchronous_commit = off buys close to nothing during a base copy, because pgcopydb copies a whole table, or one split part, in a single transaction. There is one commit per table, not per row. Follow-mode transactions may be small and frequent, but the bundled runner explicitly sets synchronous_commit=on for SQLite apply, overriding a server default of off to confirm target WAL durability before publishing progress.

full_page_writes = off is not safe on a target that becomes production. CloudNativePG defaults wal_log_hints to on, which WAL-logs hint-bit full-page images even without data checksums; enabling checksums independently requires that logging.

The VACUUM tail

pgcopydb runs VACUUM ANALYZE per table alongside the copy, with a worker pool sized from tableJobs. A table's vacuum cannot start until that table's own copy finishes, and the largest table finishes last. So the end of a clone routinely narrows to a single VACUUM ANALYZE on the biggest table, running alone while every other worker sits idle.

On the e2e fixture, where one table holds 73% of the bytes, that tail measured roughly a third of the clone's wall clock: 19404ms with it against 13253ms without, in a clean pair with a fresh target database per arm. The operator reports it as the Finalizing phase because the target has stopped growing, so every size-derived estimate reads as finished while real work continues. Conditions and reasons has the full phase table and what runs inside each one.

Skipping the vacuum recovers that time:

spec:
  clone:
    skip: [vacuum]

[!important] The target is then left without fresh statistics. The planner will use whatever it had, which on a freshly restored database is nothing, so the first real queries after cutover can choose badly. Run ANALYZE on the target yourself before pointing traffic at it: one bulk ANALYZE beats per-table vacuums competing with the copy.

The operator does not skip it by default, because a target that silently lacks statistics is invisible until a query plan goes wrong in production.

What the defaults are worth, measured

Two source shapes on the same cluster, both cloned with binary COPY and the vacuum skipped, rates dividing source database size by wall clock.

A production-shaped database, 15 tables totalling 1010MB with the largest at 31% of the bytes and none past the split threshold, so every stream comes from tableJobs:

tableJobs	wall clock	rate
2	7548 ms	139.7 MiB/s
4	4996 ms	211.1 MiB/s
8	5299 ms	199.0 MiB/s
16	5181 ms	203.5 MiB/s

Throughput scales to four jobs and then flattens, which is where the default sits.

The one-dominant-table shape, 2909MB with a single TOASTed table holding 73% of it, is the harder case and runs at roughly half the rate. There tableJobs cannot help, because a table is one COPY stream unless pgcopydb splits it, and splitting further slows it down:

split threshold	max parts	rate
512Mi (default)	8 (default)	102.4 MiB/s
256MB	16	80.7 MiB/s
128MB	32	57.2 MiB/s

More parts means more writers contending on one relation, and that costs more than the added parallelism returns. Leave both at their defaults.

The ceiling sits where it does because of round-trip latency. A copy worker mid-COPY sits at 7-12% CPU and moves roughly 14kB per socket round trip, one round trip at a time. Each stream is bound by that latency, not by CPU, disk or network, so throughput is the number of streams multiplied by what one stream sustains. Adding streams therefore helps until the servers saturate, and adding jobs with no streams to fill does nothing.

Measuring

Two clones of identical data on the same cluster minutes apart measured 666s and 399s. That is a 67% spread from thin provisioning alone: the first run writes into freshly allocated blocks, the second overwrites blocks that already exist. So a single before-and-after pair proves nothing. Give every arm a freshly provisioned target volume, run at least three, and report the median and the range.

Watch the divisor too. Dividing the source database size by wall clock overstates throughput by roughly 16%, because a relation's size counts its indexes, page and tuple headers, alignment padding and free space, and a COPY stream carries none of them. One run moved 5078 MB against a 5886 MiB source. pgcopydb's own summary, printed at the end of every clone, is the accurate source: its COPY (cumulative) row gives both the bytes and the time.

The operator exports pgcopydb_migration_start_time_seconds and pgcopydb_migration_completion_time_seconds. Subtracting them gives the duration, which is the number to compare. pgcopydb_migration_clone_copied_bytes and pgcopydb_migration_source_database_size_bytes give you a rate to divide it into. Monitoring has the full metric reference.

For per-table detail, run pgcopydb list progress --summary --json against a finished Migration's work PVC (<name>-work) from a short-lived pod that mounts it. Never run it with kubectl exec into a running worker: every pgcopydb invocation writes to the catalog, and one landing while a worker is mid-cursor kills that worker. It reports per-step and per-table timings, which is how you find which table was slow. On stock 0.18, skip it when the Migration used filters: list progress overwrites the stored filtering and poisons later resume (see the upstream drafts). The bundled runner fixes that filter corruption, but the restriction against concurrent catalog access still applies.