0

I refer to 2 documentation pages on PostgreSQL's site: #1 and #2. From these, we see the transaction log is flushed to disk 5 times per second (wal_writer_delay = 200ms), and we also see the RDBMS won't report a transaction as successful until it has been committed to the log file since synchronous_commit = on by default.

This would give us a theoretical peak of 5 reportable successfully writes per second if each is done in its own transaction, which is often the case. The reason being the log is not written to disk faster than that.

Yet we see speed in the thousands per second.

How does this work?

To be clear, I'm talking about each write being in its own transaction (the default usage). I know why bulk inserts don't apply to this question.

1 Answer 1

1

Commits will queue up and all flush when the WAL gets written.

So you'll get big groups of commits writing 5 times a second.

4
  • This part I know. But I'm questioning why it's reporting back as "safely saved" at a higher rate than that when it's not possible, as far as I see.
    – IamIC
    Jun 19, 2014 at 18:53
  • Measure the delay between issuing the commit command and having it return. It'll average half your wal writer delay and be fairly evenly distributed. Because commits are queuing. Your mistake is assuming that only one record can be written when the wal writer wakes up. That is not the case. Jun 19, 2014 at 23:44
  • I know thousands can be written when the WAL writer wakes up. If I run a loop of inserts from say C# or Delphi, it will return several thousand times per second. This is the part I don't get.
    – IamIC
    Jun 20, 2014 at 7:36
  • See prev comment. Measure how long each commit takes. Should be avg wal writer delay / 2. If not show your self contained runnable test code, full pg config and test run logs. Jun 20, 2014 at 9:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .