skip to content

In PostgreSQL, how would you load a 50-million-row census CSV quickly and safely, from staging table through indexes to ANALYZE?

level: seniorimportance: must knowfreq 55%

answer

  1. a table that skips WAL
  2. same transaction as CREATE
  3. indexes after data, not before
  4. foreign keys queue trigger events
  5. statistics before the first query

basics

~20 s

COPY the file into an UNLOGGED staging table created in the same transaction, validate and move rows with set-based SQL into a target without secondary indexes or foreign keys, build those afterwards, then ANALYZE before queries run.

solid answer

~40 s

I would `CREATE UNLOGGED TABLE census_staging` and `COPY` into it in the same transaction: unlogged means no WAL for 50 million raw rows, at the price that a crash truncates the table, which is fine because the load can be rerun. From there I validate in SQL and move rows with one `INSERT ... SELECT` into `census_person`, created bare. Indexes and the primary key come afterwards, because one sorted build beats 50 million incremental insertions, and foreign keys come last, because checking each row queues a trigger event that can exhaust memory. A larger `maintenance_work_mem` for those builds and a larger `max_wal_size` against frequent checkpoints help. Finally `ANALYZE census_person`, since otherwise the first queries are planned without statistics, and drop the staging table.

code

sql · 22 lines
sql
BEGIN;
CREATE UNLOGGED TABLE census_staging (
    person_id bigint, household_id bigint, region_code text,
    birth_date date, income numeric(12,2)
);
-- the client streams the CSV
COPY census_staging FROM STDIN
  WITH (FORMAT csv, HEADER, ON_ERROR ignore, REJECT_LIMIT 5000);
COMMIT;

CREATE TABLE census_person (LIKE census_staging);
INSERT INTO census_person
SELECT * FROM census_staging
WHERE person_id IS NOT NULL AND birth_date <= current_date;

SET maintenance_work_mem = '1GB';
ALTER TABLE census_person ADD PRIMARY KEY (person_id);
CREATE INDEX census_person_household_idx ON census_person (household_id);
ALTER TABLE census_person ADD FOREIGN KEY (household_id)
  REFERENCES census_household (household_id);
ANALYZE census_person;
DROP TABLE census_staging;

go deeper

for a junior

Recall the order of a bulk load: COPY into a staging table, move clean rows, build indexes, then ANALYZE.

for a middle

Explain why unlogged staging skips WAL, why one index build beats per-row maintenance, and why a new table needs ANALYZE before queries.

for a senior

Show the production judgement: crash and standby consequences of unlogged staging, the foreign-key trigger queue, SET LOGGED's rewrite, and when dropping indexes on a live table is unacceptable.

for a principal

Weigh load speed against safety and availability: what may be lost and rerun, how long the table is unindexed or unconstrained, and who is affected while it is.

## What still costs time once you use COPY `COPY` already removes per-statement overhead: one command, one parse, a tight insert loop. On 50 million rows the remaining costs are elsewhere: **WAL** for every inserted row, **index maintenance** for every row and every index, **foreign-key checks** queued per row, **checkpoints** forced by the WAL volume, and a **planner with no statistics** afterwards. A good recipe removes or defers each one. (Batching plain `INSERT`s is a different technique, covered in the SQL batching topic.) ## Step 1: an UNLOGGED staging table | Staging option | Writes WAL | After a crash | On standbys | Autovacuum | |---|---|---|---|---| | ordinary table | yes | intact | replicated | yes | | `UNLOGGED` table | no | **truncated** | not replicated | yes | | `TEMPORARY` table | no | gone (session-local) | no | **never**: analyze it yourself | An **unlogged** table writes no WAL for its data, so the raw load is much cheaper. The price is in the manual's own words: it is **not crash-safe**. After a crash or unclean shutdown it is automatically truncated, and its contents never reach standbys (querying it there fails with `cannot access temporary or unlogged relations during recovery`). Indexes on it are unlogged too. For staging that is acceptable: the source file still exists, so a crash means rerunning the load, not losing data. A temporary table would also work, but it is visible only to the loading session and autovacuum cannot touch it. ## Step 2: COPY in the same transaction as CREATE Create the staging table and `COPY` into it inside one transaction. If anything fails, the table and its partly written files disappear together instead of leaving half a load behind. Two options fit here: - `ON_ERROR ignore` with a `REJECT_LIMIT` (18), so a few malformed values do not cost the whole run. - `FREEZE`, which writes the rows already frozen because the table is new in this transaction (covered in its own question). If a `COPY` does fail, its inserted rows remain as dead space; `TRUNCATE` the staging table before retrying rather than reloading on top. ## Step 3: validate, then move with one statement Check the staged rows with SQL (duplicate `person_id`s, impossible birth dates, unknown `household_id`s) and move the accepted ones into the real table with **one `INSERT ... SELECT`**. The real table is logged, so this is where WAL is paid, once, for clean data only. ## Step 4: indexes and constraints after the data The manual's advice for a freshly created table is to load first and index afterwards: 1. Create `census_person` with columns only: no secondary indexes, no foreign keys, ideally no primary key yet. 2. Run the `INSERT ... SELECT`. 3. Add the primary key and build secondary indexes. **One sorted build over existing data is faster than inserting each of 50 million rows into each index.** A session-level `maintenance_work_mem` increase helps these builds; it does little for `COPY` itself. 4. Add foreign keys last. Each row inserted into a table that already has a foreign key **queues a pending AFTER trigger event** for its check, and the manual warns that millions of them can overflow memory, causing swapping or outright failure. Adding the key afterwards checks all rows in one pass. The trade-off is error checking: a duplicate key now surfaces when the primary key is added, at the end, not at the offending row. On an **existing live table**, dropping indexes also hurts every other user and dropping a unique index removes the uniqueness guarantee while it is gone, so the drop-and-rebuild trick is mainly for new or offline tables. ## Step 5: ANALYZE before anyone queries it A new table has **no planner statistics** until something analyzes it. Autovacuum will eventually, once its change thresholds are crossed, but queries that arrive first are planned from table size and default guesses. Run `ANALYZE census_person` as the last step of the load; reading the statistics it produces is the statistics topic's subject. ## What can go wrong - **`ALTER TABLE ... SET LOGGED` as a shortcut.** It rewrites the whole table under an `ACCESS EXCLUSIVE` lock and, at the default `wal_level` of `replica`, writes it into WAL anyway: it defers the WAL cost rather than avoiding it. - **A crash mid-load** empties the unlogged staging table; rerun from the file. - **Checkpoint storms**: the WAL volume of the final insert triggers checkpoints more often than `checkpoint_timeout`; temporarily raising `max_wal_size` reduces them. - **Explicit key values**: loading `person_id` values into an identity column does not advance its sequence, so reset it afterwards.

  • Why not COPY into an unlogged census_person and then run ALTER TABLE ... SET LOGGED on it?
    `SET LOGGED` rewrites the whole table under an `ACCESS EXCLUSIVE` lock, and unless `wal_level` is `minimal` the new copy is written to WAL anyway, so it defers the WAL cost rather than avoiding it. It can suit a table nobody reads yet, but a separate staging table keeps raw and validated data apart.
  • Why does the PostgreSQL manual call dropping foreign keys before a huge load necessary, not just faster?
    Each row inserted into a table with a foreign key queues an AFTER trigger event to check the reference. Across tens of millions of rows that pending-event queue can outgrow memory, causing swapping or outright failure. Adding the constraint afterwards checks every row in one pass; the alternative is splitting the load into smaller transactions.
  • When is an UNLOGGED staging table the wrong choice in PostgreSQL?
    When the staged data must survive a crash or be read on a standby. Unlogged tables are truncated after a crash or unclean shutdown and their contents are not replicated. If the source file cannot be read again, or a replica must see the staged rows, stage into an ordinary logged table and pay the WAL.

An UNLOGGED staging table is a loading dock: crates are unloaded without being entered in the warehouse ledger, inspected there, and only the accepted ones are shelved and recorded. If the dock floods overnight, everything on it is lost and you unload the truck again, which is why the dock must never hold the only copy.

  • An UNLOGGED table keeps its rows after a crash and loses only recent changes.
  • Creating all indexes before COPY is faster because rows arrive already sorted.
  • ALTER TABLE ... SET LOGGED flips a flag instantly without rewriting the table.
  • Autovacuum analyzes a freshly loaded table immediately, so a manual ANALYZE is never needed.
  • Raising maintenance_work_mem is what makes COPY itself faster.