4 ms·
Author is setting PRAGMA synchronous="normal", meaning fsync is not issued as part of every write tx, but eventually. In order to make the comparison fair it sh
by tomasol 10mo ago
Author is setting PRAGMA synchronous="normal", meaning fsync is not issued as part of every write tx, but eventually. In order to make the comparison fair it should be set to "full".
- deleted 10mo ago[deleted]
- andersmurphy 10mo agoPRAGMA synchronous="normal" is fine if you are in WAL mode. The database cannot be corrupted by power loss unlike in journal mode. > The synchronous=NORMAL setting provides the best balance between performance and safety for most applications running in WAL mode. You lose durability across power lose with synchronous NORMAL in WAL mode, but that is not important for most applications. Transactions are still atomic, consistent, and isolated, which are the most important characteristics in most use cases.
- tomasol 10mo agofsync is the most expensive operation during a write. NORMAL mode means you don't care whether last ~100 ms of transactions before a process crash / VM restart are going to be persisted or not. My suggestion is either to use synchronous="full" or disable `synchronous_commit` on Postgres to avoid comparing apples to oranges. Edit: Also, the example indicates financial transactions. Can you explain why you need serializability but not durability?
- andersmurphy 10mo agoVery good point I've added an epilogue to the post with updated numbers Really shows the power of dynamic batching.
- cryptonector 10mo agoThank you for doing this update!
- cryptonector 10mo ago100%. TFA was NOT comparing apples to apples. Now that it's been updated the numbers do not look quite so fantastic. The version of PG used matters, too, since the latest adds async I/O support that greatly improves performance.
- andersmurphy 10mo agoSQLit went from 102545 -> 100405