0Pricing
PostgreSQL Performance & Query Optimization · 강의

로드 중 인덱스와 제약 조건 지연

대량 로드 전후로 인덱스와 제약 조건을 삭제하고 재구축하여 쓰기 증폭을 크게 줄이는 방법을 배웁니다.

로드 중 인덱스와 제약 조건 지연은(는) CoddyKit의 무료 PostgreSQL Performance & Query Optimization 강의입니다. 이것은 4개 중 2번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 PostgreSQL Performance & Query Optimization 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

이 강의의 일부는 아직 번역되지 않았으며 영어로 표시됩니다.

Why Bulk Loads Get Slow

When you load millions of rows into a table that already has indexes and constraints, PostgreSQL pays a hidden tax on every single row.

  • Each index must be updated (B-tree page splits, WAL writes).
  • Each foreign key triggers a lookup against the referenced table.
  • Each unique/check constraint is validated per-row.

This per-row work is called write amplification: one logical INSERT becomes many physical writes. The core optimization in this lesson is to defer that work — load the raw data first, then build indexes and validate constraints once, in bulk.

The Per-Index Cost

Maintaining a B-tree index during a load is not free. For each inserted row, PostgreSQL must walk the tree, find the leaf page, possibly split it, and log the change to WAL.

Building the same index after the data is present is far cheaper: PostgreSQL sorts all keys at once and writes dense, sequential pages. A table with 5 indexes loaded row-by-row does roughly 6x the write work versus loading the heap alone.

The takeaway: fewer indexes present during load = less amplification.

Pattern: Drop, Load, Rebuild

The classic ETL pattern for a table that will receive a large load is:

  • Drop the secondary indexes.
  • Load the data (COPY is fastest).
  • Rebuild the indexes in one pass.

Below is the skeleton. Note we keep the primary key for now and only drop secondary indexes that are not needed during the load itself.

DROP INDEX idx_orders_customer_id;
DROP INDEX idx_orders_created_at;

COPY orders FROM '/data/orders.csv' WITH (FORMAT csv, HEADER true);

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);

COPY Beats INSERT for Loading

Once indexes are out of the way, the loading method matters. COPY streams rows in a single command with minimal per-row overhead, while thousands of individual INSERT statements each pay parse, plan, and round-trip costs.

For ETL throughput, prefer COPY (or \copy from psql) over row-at-a-time inserts. If you must use INSERT, batch many rows per statement.

COPY staging_events (user_id, event_type, payload, created_at)
FROM '/data/events.csv'
WITH (FORMAT csv, HEADER true);

Deferring Foreign Key Validation

Foreign keys are validated per-row during a load, doing an index lookup on the parent table each time. You can avoid this by making the constraint NOT VALID first, loading, then validating in bulk.

ADD CONSTRAINT ... NOT VALID adds the FK without checking existing rows. New rows are still checked on insert, so to truly skip per-row work you drop and re-add after load, or load before adding the FK.

-- Add the FK without scanning existing rows
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id)
  NOT VALID;

-- Later, validate all rows in one bulk pass
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;

Why NOT VALID Then VALIDATE Helps

Adding a foreign key the normal way takes an ACCESS EXCLUSIVE lock and scans the whole table while blocking writes. The two-step approach splits this:

  • ADD ... NOT VALID is fast and takes a brief strong lock only to record the constraint.
  • VALIDATE CONSTRAINT scans the table under a weaker SHARE UPDATE EXCLUSIVE lock, allowing concurrent reads and writes.

For loads, this means you do the expensive validation once, after all data is present, rather than per-row.

DEFERRABLE Constraints Within a Transaction

PostgreSQL also supports DEFERRABLE constraints, which postpone checking until the end of a transaction (COMMIT). This is different from dropping a constraint: the check still runs, just later.

It is useful when rows arrive in an order that temporarily violates an FK or unique constraint (for example, child rows before parents within one transaction).

ALTER TABLE order_items
  ADD CONSTRAINT fk_items_order
  FOREIGN KEY (order_id) REFERENCES orders (id)
  DEFERRABLE INITIALLY DEFERRED;

BEGIN;
  -- insert children and parents in any order;
  -- FK is checked only at COMMIT
COMMIT;

Deferred Check vs Dropped Constraint

Be clear on the trade-off:

  • DEFERRABLE INITIALLY DEFERRED still validates every row, just at COMMIT instead of at INSERT. It fixes ordering problems but does not remove the validation cost.
  • Drop / re-add (or NOT VALID + VALIDATE) removes per-row work entirely and revalidates in one efficient scan.

For maximum throughput on huge loads, dropping and rebuilding wins. For correctness with tricky insert ordering, deferrable is the right tool.

Tuning the Index Rebuild

Rebuilding indexes after a load is itself a sort-heavy operation. Two settings make it much faster for the session running the load:

  • maintenance_work_mem — more memory means fewer external sort merges when building indexes.
  • max_parallel_maintenance_workers — lets a single CREATE INDEX use multiple CPUs.

Raise these for the load session, then build the indexes.

SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);

A Complete ETL Sequence

Putting it together for a large incremental load into an existing table, a robust order of operations is:

  • Drop secondary indexes.
  • Drop or disable expensive foreign keys.
  • Raise maintenance_work_mem.
  • Load via COPY.
  • Rebuild indexes.
  • Re-add FKs and VALIDATE.
  • Run ANALYZE so the planner has fresh statistics.
ALTER TABLE orders DROP CONSTRAINT fk_orders_customer;
DROP INDEX idx_orders_created_at;

SET maintenance_work_mem = '1GB';
COPY orders FROM '/data/orders.csv' WITH (FORMAT csv, HEADER true);

CREATE INDEX idx_orders_created_at ON orders (created_at);
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id);

ANALYZE orders;

Don't Forget ANALYZE

After a big load, the table statistics the planner relies on are stale — it may still think the table is tiny. That leads to bad plans (sequential scans where an index would win, or wrong join orders).

Always run ANALYZE (or VACUUM ANALYZE) on freshly loaded tables before running queries against them. Rebuilding indexes does not update planner statistics; only ANALYZE does.

ANALYZE orders;
-- or to also reclaim space and freeze:
VACUUM ANALYZE orders;

Quick Check

Test your understanding of the throughput trade-offs.

Recap

Key takeaways for deferring indexes and constraints during bulk loads:

  • Live indexes and constraints cause write amplification — one INSERT becomes many physical writes.
  • The winning pattern is drop, load, rebuild: remove secondary indexes and FKs, load with COPY, then recreate them in one pass.
  • ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT moves FK checking out of the per-row path and into a single bulk scan under a lighter lock.
  • DEFERRABLE INITIALLY DEFERRED only postpones checks to COMMIT — it fixes insert-ordering issues but does not eliminate validation cost.
  • Raise maintenance_work_mem and max_parallel_maintenance_workers to speed up the rebuild.
  • Always finish with ANALYZE so the planner sees the new data.

자주 묻는 질문

“로드 중 인덱스와 제약 조건 지연” 강의는 무료인가요?

네 — “로드 중 인덱스와 제약 조건 지연” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 PostgreSQL Performance & Query Optimization 강의 전체를 잠금 해제할 수 있습니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

“로드 중 인덱스와 제약 조건 지연”에서 뭘 배우나요?

대량 로드 전후로 인덱스와 제약 조건을 삭제하고 재구축하여 쓰기 증폭을 크게 줄이는 방법을 배웁니다. 브라우저에서 직접 실행하는 실습 코드로 PostgreSQL Performance & Query Optimization을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

PostgreSQL Performance & Query Optimization을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 PostgreSQL Performance & Query Optimization은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 2번째 강의입니다.

“로드 중 인덱스와 제약 조건 지연” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 PostgreSQL Performance & Query Optimization 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 PostgreSQL Performance & Query Optimization 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

  1. COPY와 다중 행 INSERT 처리량 비교
  2. 로드 중 인덱스와 제약 조건 지연
  3. 수집을 위한 WAL과 체크포인트 조정
  4. ON CONFLICT를 활용한 대규모 업서트
← PostgreSQL Performance & Query Optimization(으)로 돌아가기