Skip to main content
expand on answer in response to comments.
Source Link
jjanes
  • 41.3k
  • 3
  • 40
  • 54

Write-heavy operations like COPYCOPY or INSERT INTO ... SELECT FROM will use a non-default buffer access strategy which intentionally causes the write-heavy process to write its own buffers.

This is so that, if you write a billion rows into a table, that table growth doesn't shove the entire rest of the database out of the cache.

IsEven if your question just based on curiosity, orwrite heavy operations do you think the current behavior is causingnot use a problem?buffer access strategy:

  1. There is no reason for checkpointing to be doing most of the writing unless our checkpoint_segments are set too small.

  2. If your bgwriter is not aggressive enough, it might not get enough data written and so the user backends will do it themselves. You could try to make bgwriter more aggressive. But ever since version 8.3.20 or so, the bgwriter is not very useful anyway, and having the backends do writes themselves is rarely a problem anymore.

Write-heavy operations like COPY will use a non-default buffer access strategy which intentionally causes the write-heavy process to write its own buffers.

This is so that, if you write a billion rows into a table, that table growth doesn't shove the entire rest of the database out of the cache.

Is your question just based on curiosity, or do you think the current behavior is causing a problem?

Write-heavy operations like COPY or INSERT INTO ... SELECT FROM will use a non-default buffer access strategy which intentionally causes the write-heavy process to write its own buffers.

This is so that, if you write a billion rows into a table, that table growth doesn't shove the entire rest of the database out of the cache.

Even if your write heavy operations do not use a buffer access strategy:

  1. There is no reason for checkpointing to be doing most of the writing unless our checkpoint_segments are set too small.

  2. If your bgwriter is not aggressive enough, it might not get enough data written and so the user backends will do it themselves. You could try to make bgwriter more aggressive. But ever since version 8.3.20 or so, the bgwriter is not very useful anyway, and having the backends do writes themselves is rarely a problem anymore.

Source Link
jjanes
  • 41.3k
  • 3
  • 40
  • 54

Write-heavy operations like COPY will use a non-default buffer access strategy which intentionally causes the write-heavy process to write its own buffers.

This is so that, if you write a billion rows into a table, that table growth doesn't shove the entire rest of the database out of the cache.

Is your question just based on curiosity, or do you think the current behavior is causing a problem?