2 ms·
Does COPY FROM sidestep the WAL? My (perhaps incorrect) understanding is that pg writes such data to a table file then uses a small transaction to make it live.
by jonstewart 4y ago
Does COPY FROM sidestep the WAL? My (perhaps incorrect) understanding is that pg writes such data to a table file then uses a small transaction to make it live.
- lfittl 4y agoIt does not, except if you're COPYing into an unlogged table - for regular tables data that gets COPYed in gets WAL logged and is crash safe. In my understanding, as compared to a multi-row insert, a COPY statement is (1) a lot more efficient to parse/process, (2) uses a special bulk write strategy that avoids churn and large amount of dirty pages in shared buffers. As compared to multiple single row inserts another benefit is that you're writing a single transaction ID, instead of a lot of individual transaction IDs (causing less freezing work having to be done by autovacuum, etc.)
- anarazel 4y agoWe also batch the insertion into the table and the WAL logging, if possible. There's cases where that optimization has to be disabled though (presence of some types of triggers, for example).