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.

IamIC
  • 155
  • 10

1 Answers1

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.

Craig Ringer
  • 3,188
  • 1
  • 16
  • 17
  • 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 '14 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. – Craig Ringer Jun 19 '14 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 '14 at 07: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. – Craig Ringer Jun 20 '14 at 09:33