Split View: 대용량 데이터 처리 완전 가이드: COPY, 청크 배치, 그리고 되돌릴 수 있는 작업

The Complete Guide to Bulk Data Processing: COPY, Chunked Batches, and Reversible Operations

• Introduction
• 1. Three Ways Bulk Operations Fail
• 2. Loading — COPY versus INSERT
• 3. The Initial-Load Procedure
• 4. Loading That Tolerates Bad Rows
• 5. Chunking Bulk UPDATE and DELETE
• 6. Cleanup After a Delete
• 7. Designing a Safe Backfill
• 8. Building In Reversibility
• Quiz: Test Your Understanding
• Conclusion
• References
• Further Reading

Introduction

Bulk data operations are a textbook example of an area where a script that ran perfectly fine in development ends up taking production down. A job that finished in 30 seconds against a million rows does not simply take 300 times as long against 300 million rows — it does not scale that predictably at all. Past some particular point, the way the system behaves fundamentally changes, not just its speed. WAL volume explodes and checkpoints start piling up on top of each other, dead rows accumulate until the table visibly bloats, a single transaction runs so long that VACUUM itself stalls out entirely, and replication lag quietly opens up behind the scenes.

This post works through those breaking points in order, one at a time: loading, updating, deleting, and backfilling. For each of these operations, it covers what actually becomes the bottleneck, what procedure the PostgreSQL documentation itself recommends, and how you roll it back cleanly when it fails partway through.

The reference engine throughout is PostgreSQL 18, and every option and every default value quoted in this post was confirmed directly against the PostgreSQL 18 documentation.

1. Three Ways Bulk Operations Fail

Let's start with the failure types themselves, since the entire response strategy branches out from here, depending on which one you are actually facing.

First, a single transaction ends up simply too big. Update 300 million rows inside one single transaction, and that transaction stays open for hours on end. The entire time it is open, dead-row reclamation is blocked across the whole database, not just the one table, and if a rollback happens for any reason, all of those hours of work are thrown away wholesale, with nothing to show for them. Fail partway through, and you have no choice but to start over completely from scratch.

Second, resource consumption spikes suddenly rather than growing gradually. A bulk write generates an enormous volume of WAL in a short window, and every single time WAL exceeds max_wal_size (default 1GB), a checkpoint fires immediately. When checkpoints come in a rush like this, pushing all those dirty pages out to disk saturates I/O, and latency on ordinary production queries spikes right alongside it, even on tables the bulk job never touched.

Third, cleanup gets left over long after the job itself is done. A bulk delete or update creates exactly as many dead rows as the number of rows it touched. The delete itself may be finished, but the table stays exactly the same size it was before, and a sequential scan still has to read just as many pages as it did before the delete. There is a real gap, sometimes measured in days, between the moment the job reports "done" and the moment the system has actually returned to normal.

The response to these three failure modes, respectively, is chunking, tuning resource parameters and throttling the pace of work, and planning your VACUUM in advance rather than reacting to it.

2. Loading — COPY versus INSERT

The PostgreSQL documentation's "Populating a Database" chapter lays out the standard, documented procedure for bulk loading, step by step. The first two items on that list matter more than all the rest combined.

Turn off autocommit and wrap the entire load in a single transaction. As the documentation explains it, PostgreSQL performs a fair amount of internal bookkeeping work for every individually committed row, so turning off autocommit for the duration of the load cuts that overhead down substantially.

Use COPY instead of individual INSERT statements. In the documentation's own words, COPY is specifically optimized for bulk loading and incurs "much less overhead" than issuing many separate INSERT statements one after another. A single COPY command does not even require you to turn off autocommit in the first place, since it is already one statement. If COPY genuinely is not an option for your situation, the documented fallback is to prepare an INSERT with PREPARE and then repeat EXECUTE against it, which avoids repeating the parsing and planning overhead on every single row.

Here are COPY's main options, along with their defaults exactly as documented.

# 파일에서 서버로: psql의 \copy는 클라이언트 파일을 읽는다
psql -d appdb -c "\copy orders_staging FROM 'orders.csv' WITH (FORMAT csv, HEADER true)"

-- read directly from the server's file system (requires server privileges)
COPY orders_staging FROM '/var/lib/pgsql/import/orders.csv'
  WITH (FORMAT csv, HEADER true);

-- read from a program's output
COPY orders_staging FROM PROGRAM 'zcat /var/lib/pgsql/import/orders.csv.gz'
  WITH (FORMAT csv, HEADER true);

HEADER can also take the special value MATCH. In that case, the header names in the file must exactly match the table's own column names, which prevents the silent-corruption accident where a CSV with its columns reordered loads without a single complaint from the database. You should always use HEADER MATCH for any file you receive from an outside source, without exception.

3. The Initial-Load Procedure

This is the exact order the documentation recommends for bulk-loading data into an empty or brand-new table.

Step 1 — create indexes only afterward, not before. In the documentation's own words: "create the table, bulk load using COPY, then create any indexes needed. Creating an index on pre-existing data is quicker than updating it incrementally as each row is loaded." Even when you are only adding a large amount of new data to a table that already holds existing data, dropping the index first, loading, and then recreating the index afterward can end up being faster overall — with the important caveat that performance for other users of that table suffers in the meantime, while the index is missing.

Step 2 — create foreign key constraints only afterward as well. The documentation specifically warns that this may not be a mere optimization you can skip if you are in a hurry. It notes that "it is much more efficient to check a foreign key constraint in bulk than row by row," and further warns that when loading millions of rows with the constraint already in place, "the queue of pending trigger events would grow much larger than available memory, leading to intolerable swapping, or worse, a complete failure" of the load itself.

Step 3 — temporarily raise maintenance_work_mem for the duration of the load. Its default value is 64MB. According to the documentation, this setting is the memory budget used by maintenance operations such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY; raising it temporarily during a bulk load speeds up both index creation and foreign-key addition afterward. It does not, however, speed up COPY itself in any way.

Step 4 — temporarily raise max_wal_size as well. Its default value is 1GB. As the documentation explains, bulk loading triggers checkpoints considerably more often than normal steady-state traffic does, and raising this value reduces how many checkpoints are actually needed over the course of the load.

Step 5 — run ANALYZE the moment you are done loading. The documentation says plainly that "after a bulk load, run ANALYZE (or VACUUM ANALYZE) so the planner has up-to-date statistics," and warns that without fresh statistics the planner can end up picking genuinely bad plans against the new data. Autovacuum may eventually run this automatically if it is enabled, but if the table needs to start taking queries immediately after the load finishes, running it yourself right away is the safer bet.

-- initial-load template
SET maintenance_work_mem = '2GB';   -- session-scoped

BEGIN;
CREATE TABLE orders_new (LIKE orders INCLUDING DEFAULTS);
COPY orders_new FROM '/import/orders.csv' WITH (FORMAT csv, HEADER match);
COMMIT;

CREATE INDEX idx_orders_new_tenant ON orders_new (tenant_id, created_at DESC);
ALTER TABLE orders_new ADD PRIMARY KEY (id);
ANALYZE orders_new;

RESET maintenance_work_mem;

Warning: The documentation also describes a way to turn off WAL archiving and streaming replication entirely, by dropping wal_level to minimal, archive_mode to off, and max_wal_senders to 0. But that same documentation immediately attaches a serious caveat to it: this change requires a full server restart to take effect, and it renders any previous base backups unusable for archive recovery and for standby servers, which can lead directly to real data loss if you are not careful. Skip this entirely on a production database. This particular piece of advice applies only to a brand-new cluster that is still being built from scratch, with nothing depending on it yet.

COPY FREEZE is likewise an option meant only for the initial load, not for ongoing use. Per the documentation, the target table must have been created or truncated within the current subtransaction, there must be no open cursors anywhere, the transaction must not be holding on to an older snapshot, and it cannot be used on partitioned tables or on foreign tables. And, as the documentation states quite plainly, the moment the load succeeds, the data becomes visible to every other session immediately — which openly violates the usual MVCC visibility rules that apply everywhere else.

4. Loading That Tolerates Bad Rows

A file you receive from an outside source will always contain some number of broken rows somewhere in it — this is not a possibility to plan around, it is a certainty. The default behavior is for the entire load to fail outright on the very first error it hits, because the documented default for ON_ERROR is stop.

Switch it to ignore instead, and the load simply discards whichever row failed and continues on with the rest of the file. Per the documentation, this value only applies to COPY FROM when the format is text or csv — it has no effect on binary format.

COPY orders_staging FROM '/import/orders.csv'
  WITH (FORMAT csv, HEADER match,
        ON_ERROR ignore,
        LOG_VERBOSITY verbose,
        REJECT_LIMIT 1000);

LOG_VERBOSITY takes one of default, verbose, or silent, and controls how much detail gets logged when ON_ERROR is set to ignore. REJECT_LIMIT is the maximum number of errors you are willing to tolerate before the whole load gives up; per the documentation it must be used together with ON_ERROR=ignore and it must be a positive integer. Omit this clause entirely, and there is no limit at all on the error count — every single piece of bad data in the file gets silently skipped, no matter how much of it there is.

Always specify REJECT_LIMIT explicitly, every time. Without a limit in place, even a file whose encoding is completely and utterly wrong from the first byte will still come back reporting "load succeeded, 0 rows," with nothing in the output to tell you anything went wrong at all.

A meaningfully safer pattern altogether is to use a staging table that receives every single column as plain text, with no type conversion happening during the load itself.

CREATE UNLOGGED TABLE orders_raw (
  id_txt text, tenant_txt text, amount_txt text, created_txt text
);

COPY orders_raw FROM '/import/orders.csv' WITH (FORMAT csv, HEADER match);

-- do validation and conversion in SQL, and leave bad rows in place to investigate
INSERT INTO orders (id, tenant_id, total_amount, created_at)
SELECT id_txt::bigint, tenant_txt::bigint,
       amount_txt::numeric, created_txt::timestamptz
FROM orders_raw
WHERE id_txt ~ '^[0-9]+$'
  AND amount_txt ~ '^[0-9]+(\.[0-9]+)?$';

An UNLOGGED table writes almost no WAL at all, which is exactly what makes it such a good fit for a staging table. That said, its contents vanish completely the moment there is a crash, and they never propagate to replicas in the first place. Use an UNLOGGED table only for data you can genuinely afford to throw away entirely and reload from the source if you have to.

5. Chunking Bulk UPDATE and DELETE

Section 1 already covered, in detail, why you should never update 300 million rows in a single statement. Chunking is the standard, well-established response to that problem.

A sound chunking design has four separate requirements, and skipping any one of them tends to cause trouble later.

Requirement 1 — each individual chunk must be its own independent transaction. That way, if the job fails partway through the run, whatever has already been processed stays done and does not need to be redone.

Requirement 2 — the whole job must be resumable from where it left off. Record exactly how far you have gotten as you go, and pick the job back up from that recorded point the moment it restarts, rather than starting over.

Requirement 3 — you must be able to see real progress while the job is running. For a job that runs for hours at a stretch, if nobody can answer the simple question "how much is left to go," the job is not really operable in any practical sense.

Requirement 4 — the pace of the job must be adjustable while it runs. If production latency spikes because of the job, someone needs to be able to increase the wait time between chunks and bring the load back down, without stopping the job entirely.

Splitting the work by key range is by far the most robust approach available. Do not use OFFSET for this — it gets progressively slower the further into the table you go, since the database still has to walk past all the skipped rows every single time.

-- record progress in a table
CREATE TABLE backfill_progress (
  job_name   text PRIMARY KEY,
  last_id    bigint NOT NULL DEFAULT 0,
  updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO backfill_progress (job_name) VALUES ('orders_channel_backfill')
ON CONFLICT DO NOTHING;

-- one chunk: the application or a scheduler calls this block repeatedly
BEGIN;
SET LOCAL statement_timeout = '60s';
SET LOCAL lock_timeout = '3s';

WITH bounds AS (
  SELECT last_id FROM backfill_progress
  WHERE job_name = 'orders_channel_backfill'
  FOR UPDATE
),
target AS (
  SELECT o.id
  FROM orders o, bounds b
  WHERE o.id > b.last_id
    AND o.channel IS NULL
  ORDER BY o.id
  LIMIT 5000
),
updated AS (
  UPDATE orders o
     SET channel = 'WEB'
   WHERE o.id IN (SELECT id FROM target)
  RETURNING o.id
)
UPDATE backfill_progress
   SET last_id = COALESCE((SELECT max(id) FROM updated), last_id),
       updated_at = now()
 WHERE job_name = 'orders_channel_backfill'
RETURNING last_id;

COMMIT;

The criterion for sizing a single chunk correctly is simply whether that one chunk finishes within one second, measured end to end. Take any longer than that, and lock hold time grows large enough to start visibly affecting production traffic on the same table. Measure it in practice and tune the size from there, rather than guessing at a number up front. Setting SET LOCAL lock_timeout matters just as much — if a chunk ends up taking a long time simply because it is waiting on a lock held by something else, that wait is itself production latency, whether or not the chunk's own work was fast.

Bulk deletes follow this exact same chunked structure. But deletes very often have a meaningfully better option available to them that updates do not. If the table happens to be partitioned, DROP TABLE or DETACH PARTITION is overwhelmingly the better choice, by a wide margin. And if you are deleting most of the rows in the table rather than a small slice of it, it is generally faster to copy just the rows you are keeping into a brand-new table and rename it into place, rather than deleting everything else out of the original.

6. Cleanup After a Delete

DELETE does not actually erase a row from disk. It only marks that row as dead. Reclaiming the space that dead row occupied is VACUUM's job, not DELETE's.

Exactly when autovacuum decides to run is governed by a threshold formula, not by a fixed schedule. Per the PostgreSQL 18 documentation, the default for autovacuum_vacuum_threshold is 50 tuples, and the default for autovacuum_vacuum_scale_factor is 0.2 — in other words, 20% of the table's rows have to have changed before it triggers. PostgreSQL 18 newly adds autovacuum_vacuum_max_threshold on top of that formula, defaulting to 100 million tuples; this upper cap means vacuum does not get postponed indefinitely even on genuinely very large tables, where the 20% figure alone would otherwise put it off for far too long.

Right after a bulk delete finishes, it is generally better to run VACUUM yourself, immediately, rather than waiting for autovacuum to eventually get around to it.

-- clean up indexes in parallel too, and update statistics
VACUUM (ANALYZE, VERBOSE, PARALLEL 4) orders;

The catch, and it is a significant one, is that the space a VACUUM reclaims does not actually go back to the operating system. A plain VACUUM only marks the space that dead rows used to occupy as reusable for future rows in that same table; the table file's size on disk stays exactly the same as it was (empty pages sitting at the very end of the table are the one narrow exception to this, and those genuinely do get returned to the filesystem).

Warning: Actually getting that disk space back for good requires VACUUM FULL, but that command takes a full ACCESS EXCLUSIVE lock on the entire target table for as long as it runs. Even ordinary reads are blocked completely during that window, and because the command rewrites the whole table from scratch into a new file, it also needs extra free disk space equal to the table's entire original size, on top of what the table already uses. The PostgreSQL documentation itself states this quite directly: "for this reason, administrators should generally try to use standard VACUUM and avoid VACUUM FULL." CLUSTER takes that exact same exclusive lock, for the same reasons. If you genuinely need this kind of space reclamation while the service stays live, evaluate an external tool such as pg_repack instead, but be sure to check that tool's own documentation carefully for its behavior and its constraints before relying on it in production.

The practical conclusion here is straightforward: partition any table that undergoes bulk deletes on a regular, recurring basis. Detach the whole partition instead of deleting from it, and you get no dead rows, no VACUUM to run, and no bloat to clean up afterward. This alone is one of the most practical, concrete reasons to adopt partitioning in the first place, independent of any query-performance argument.

7. Designing a Safe Backfill

A backfill, at its core, is the work of "applying a new rule to data that already exists in the system." Populating a brand-new column for existing rows, correcting values that were stored incorrectly in the past, and generating a normalized column to support a new index all fall squarely under this umbrella.

There are five design principles worth following, every time.

Principle 1 — cut off new data under the old rule first, before anything else. Before you ever start the backfill itself, deploy the application change so that it writes under the new rule going forward. Otherwise, new data keeps arriving under the old rule the entire time the backfill is running in the background, and the backfill effectively never finishes, chasing a moving target.

Principle 2 — make the whole job idempotent by construction. Running the exact same chunk twice, whether on purpose or by accident, must always produce the exact same result. Add a condition such as WHERE channel IS NULL so that rows already processed are simply left alone on a second pass, and idempotency then follows naturally, without any extra bookkeeping.

Principle 3 — watch load and adjust. Pull the inter-chunk wait time out as a setting, so you can adjust it in real time while watching replication lag and production latency. Keep a particularly close eye on replication lag.

-- check standby replication lag (run on the primary)
SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

Principle 4 — record progress and an estimated completion time. Logging the count processed and the timestamp in a backfill_progress table lets you calculate the time remaining.

Principle 5 — make validation part of the job. The completion condition isn't "done" — it's "done, and verified."

-- verify the backfill is complete: is the unprocessed count zero
SELECT count(*) AS remaining FROM orders WHERE channel IS NULL;

-- does the value distribution match expectations
SELECT channel, count(*) FROM orders GROUP BY 1 ORDER BY 2 DESC;

8. Building In Reversibility

Rolling back a bulk operation is never simply a matter of running "the same statement in reverse." Chunks that have already committed do not come back just because you roll back some later transaction — each committed chunk is already permanent the instant it commits.

There are three safety nets that get used in practice, again and again.

First, keep the original values around somewhere. If the backfill overwrites existing values, preserve the old ones in a separate table or in a new column before you touch anything. You are deliberately paying a storage cost in order to buy back the ability to revert later if something goes wrong. Clean that backup up only once the job is completely finished and has been validated.

-- a rollback snapshot: store only the old values of the rows you're about to change
CREATE UNLOGGED TABLE orders_channel_backup AS
SELECT id, channel FROM orders WHERE channel IS NOT NULL;

Second, run a dry run first. Check the affected row count and a sample without actually updating anything. If you're checking with EXPLAIN ANALYZE, wrap it in a transaction and roll it back, as the documentation recommends.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET channel = 'WEB' WHERE channel IS NULL AND id BETWEEN 1 AND 5000;
ROLLBACK;

The documentation calls this pattern out explicitly: "Keep in mind that the statement is actually executed when the ANALYZE option is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual."

Third, rehearse at the same scale in staging. Taking the time you measured against a million rows and multiplying by 300 is usually wrong, because index-update cost and cache hit rate change non-linearly with scale. Measure against data that's at least the same order of magnitude.

Choosing when to run the job is also part of the safety net. Low-traffic hours are attractive, but you have to weigh that fewer people are around to respond at night too. If you've designed the job to be reversible, running it while more people are around may actually be the better call.

Quiz: Test Your Understanding

CREATE TABLE archive_new (LIKE archive INCLUDING ALL);
INSERT INTO archive_new SELECT * FROM archive WHERE created_at >= '2026-01-01';
-- swap during a short locked window
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE archive RENAME TO archive_old;
ALTER TABLE archive_new RENAME TO archive;
COMMIT;

SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

Conclusion

The principles for bulk data operations come down to three sentences: Don't do it all at once. Make it reversible. Treat post-job cleanup as part of the job.

The third deserves special emphasis. The moment a delete finishes and the moment the system is actually back to normal are two different moments. Reclaiming dead rows, refreshing statistics, and cleaning up indexes are all part of the job. Write "DELETE: 3 hours, VACUUM: 1 day" into the plan up front, and you won't be caught off guard later.

And if bulk deletes keep recurring, that's a signal to look at partitioning. Detaching a partition creates no dead rows and needs no VACUUM.

If you need CSV conversion, use the CSV/JSON Converter; if you need large volumes of test data, try the Mock Data Generator.

References

• PostgreSQL 18, Populating a Database: https://www.postgresql.org/docs/18/populate.html (retrieved 2026-08-15)
• PostgreSQL 18, COPY: https://www.postgresql.org/docs/18/sql-copy.html (retrieved 2026-08-15)
• PostgreSQL 18, Routine Vacuuming: https://www.postgresql.org/docs/18/routine-vacuuming.html (retrieved 2026-08-15)
• PostgreSQL 18, Automatic Vacuuming: https://www.postgresql.org/docs/18/runtime-config-autovacuum.html (retrieved 2026-08-15)
• PostgreSQL 18, Resource Consumption: https://www.postgresql.org/docs/18/runtime-config-resource.html (retrieved 2026-08-15)
• PostgreSQL 18, Write Ahead Log: https://www.postgresql.org/docs/18/runtime-config-wal.html (retrieved 2026-08-15)
• PostgreSQL 18, EXPLAIN: https://www.postgresql.org/docs/18/sql-explain.html (retrieved 2026-08-15)

Further Reading

• Previous: The Complete Guide to Database Caching Strategy — it's all invalidation
• Next: The Complete Guide to Data Modeling — from logical model to physical model
• The Complete Guide to Partitioning and Sharding — turning a bulk delete into a DROP
• PostgreSQL VACUUM and MVCC Internals — how dead rows pile up
• CSV/JSON Converter — convert files for loading
• Mock Data Generator — generate large volumes of test data