[immibis]

Postgres claims to have some kind of commit batching, but I couldn't figure out how to turn it on.

I wanted to scrub a table by processing each row, but without holding locks, so I wanted to commit every few hundred rows, but with only ACI and not D, since I could just run the process again. I don't think Postgres supports this feature. It also seemed to be calling fsync much more than once per transaction.

[azlev]

commit_delay

https://www.postgresql.org/docs/current/runtime-config-wal.h...

[anarazel]

> It also seemed to be calling fsync much more than once per transaction.

If it's called many more times than once per transaction the likely reason is that wal_buffers is sized small. Whenever generated WAL exceeds wal_buffers, postgres flushes the WAL, so it does not have to reopen the file later. At that point you already gotten most benefits from batching too.

Edit: A second reason is that data pages need to be written out due to cache pressure or such, and that requires the WAL to be flushed first.

[morningsam]

Looking through the options listed under "Non-Durable Settings", [1] I guess synchronous_commit = off fits the bill?

[1]: https://www.postgresql.org/docs/current/non-durability.html

[hardwaresofton]

Nope, Other commenter noted it:

https://www.postgresql.org/docs/current/runtime-config-wal.h...

Don't use synchronous_commit = off is durability ~= 0 (i.e. "I hope the write made it to disk")

[sgarland]

Maybe I don’t understand what you’re trying to do, but you can directly control how frequently commits occur.

    BEGIN
    INSERT … —- batch of N size
    COMMIT AND CHAIN
    INSERT …

[PaulDavisThe1st]

Chance of Postgres commit mapping 1:1 onto posix fsync or equivalent: slim.

[anarazel]

Without parallelism, each commit will be at least one fdatasync (or fsync, O_SYNC/O_DSYNC write, depending on configuration). With parallelism, concurrent transaction might be flushed together, reducing the total number of fsyncs.