Scenario
A COPY FROM or COPY TO is running on a large table — pg_stat_progress_copy (PostgreSQL 14+) shows bytes and tuples processed. Diagnose it -- Available since PostgreSQL 14: SELECT p.pid, p.datname, n.nspname || '.' || c.relname…
Investigation Path
A COPY FROM or COPY TO is running on a large table — pg_stat_progress_copy (PostgreSQL 14+) shows bytes and tuples processed.
Diagnose it
-- Available since PostgreSQL 14:
SELECT p.pid,
p.datname,
n.nspname || '.' || c.relname AS table_name,
p.command, -- 'COPY FROM' or 'COPY TO'
p.type, -- 'pipe', 'file', 'program', or 'callback'
pg_size_pretty(p.bytes_processed) AS bytes_processed,
pg_size_pretty(p.bytes_total) AS bytes_total,
round(
100.0 * p.bytes_processed
/ NULLIF(p.bytes_total, 0),
1) AS pct_done,
p.tuples_processed,
p.tuples_excluded,
p.tuples_skipped
FROM pg_stat_progress_copy p
LEFT JOIN pg_class c ON c.oid = p.relid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace;Note: bytes_total is only populated when copying from a
regular file (not stdin or a pipe) because the file size is known in advance. For pipe input,
track progress via tuples_processed instead.
Why it happens
Large COPY operations generate significant WAL (one WAL tuple/">record per row for logged tables).
They also hold the table-level lock required for the target table. For very large loads,
consider using COPY to an UNLOGGED table first, then converting it, to reduce
WAL overhead — though this sacrifices crash safety during the load (guidance).
How to fix it
If a COPY is running slower than expected, check I/O wait events for the session in
pg_stat_activity. Ensure the source file or pipe is not the bottleneck.
For parallel loads, split the file and use multiple COPY sessions into separate
partitions if the table is partitioned.
Prevent it next time
For large regular bulk loads, schedule them during low-traffic windows or use a staging
table approach: load into a separate table, then insert from it with controlled batching
to limit lock contention and WAL pressure on the primary.
Related & next steps
Career Impact
This scenario builds production judgment and operational confidence under pressure.