3 ms·
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 withou
by immibis 1y ago
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.
- sgarland 1y agoMaybe 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 1y agoChance of Postgres commit mapping 1:1 onto posix fsync or equivalent: slim.
- anarazel 1y agoWithout 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.
- azlev 1y agocommit_delay https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-COMMIT-DELAY https://www.postgresql.org/docs/current/runtime-config-wal.h...
- morningsam 1y agoLooking 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 https://www.postgresql.org/docs/current/non-durability.html
- hardwaresofton 1y agoNope, Other commenter noted it: https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-COMMIT-DELAY 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")
- anarazel 1y ago> 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.