1

since COPY command runs inside a single transaction, if its WAL data doesn't fit into wal_buffers? for example if we are bulk importing from CSV file.

1

As the documentation states:

There are two commonly used internal WAL functions: LogInsert and LogFlush. LogInsert is used to place a new record into the WAL buffers in shared memory. If there is no space for the new record, LogInsert will have to write (move to kernel cache) a few filled WAL buffers. This is undesirable because LogInsert is used on every database low level modification (for example, row insertion) at a time when an exclusive lock is held on affected data pages, so the operation needs to be as fast as possible. What is worse, writing WAL buffers might also force the creation of a new log segment, which takes even more time. Normally, WAL buffers should be written and flushed by a LogFlush request, which is made, for the most part, at transaction commit time to ensure that transaction records are flushed to permanent storage. On systems with high log output, LogFlush requests might not occur often enough to prevent LogInsert from having to do writes. On such systems one should increase the number of WAL buffers by modifying the configuration parameter wal_buffers.

Which means for me that in such a situation the system will need more time to complete the COPY because of the writes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.