Re:Everything You Know About MongoDB Is Wrong
by pritambaral on Thursday November 26, 2020 @03:21AM
(#25218023)
PG fsyncs on every commit. By default.
It will let you delay commits — if you ask it to — so it may batch multiple commits together on a single fsync, but it will NOT report to you that it has saved data durably when it hasn't. Because this is a safe operation, PG will let you do this easily: even on a per-session or per-transaction basis.
Similarly, you can ASK it to commit eagerly without waiting for fsync, again on a per-session or per-transaction basis. But it will never turn on by default, and the documentation of this option clearly calls out the risk: "an operating system or database crash might result in some recent allegedly-committed transactions being lost"
Or more too: you can ask it to turn off fsync (and all other kinds of sync) entirely and risk total database inconsistency — say, because you are running it on a filesystem that guarantees consistency, like ZFS — but it strongly recommends against it just on the off-chance that some over-eager user carelessly turns it off. Because this is generally not a safe thing to do, PG will NOT let you do this easily: you MUST change the server config on disk and restart the cluster.
I don't know about MySQL, but I'd be greatly surprised if they did anything like what MongoDB used to do. There's a reason MongoDB got that reputation, in a world where MySQL — and its surprising data-eating behaviours — already were popular.
It will let you delay commits — if you ask it to — so it may batch multiple commits together on a single fsync, but it will NOT report to you that it has saved data durably when it hasn't. Because this is a safe operation, PG will let you do this easily: even on a per-session or per-transaction basis.
Similarly, you can ASK it to commit eagerly without waiting for fsync, again on a per-session or per-transaction basis. But it will never turn on by default, and the documentation of this option clearly calls out the risk: "an operating system or database crash might result in some recent allegedly-committed transactions being lost"
Or more too: you can ask it to turn off fsync (and all other kinds of sync) entirely and risk total database inconsistency — say, because you are running it on a filesystem that guarantees consistency, like ZFS — but it strongly recommends against it just on the off-chance that some over-eager user carelessly turns it off. Because this is generally not a safe thing to do, PG will NOT let you do this easily: you MUST change the server config on disk and restart the cluster.
I don't know about MySQL, but I'd be greatly surprised if they did anything like what MongoDB used to do. There's a reason MongoDB got that reputation, in a world where MySQL — and its surprising data-eating behaviours — already were popular.