Read consistency when using synchronous_commit=off
To improve commit performance we are planning to use
"synchronous_commit=off", with this if multiple clients are reading the
same data/row will they always get the latest updated data/row ? (clients
are using committed read and we are not using clustered environment, we
have just one instance of postgres serving local clients running on the
same machine).
For example if client1 updates a row then the updated value is available to
client2 immediately after the commit or there is a delay as commit is now
asynchronous ?
Hi,
all clients will get the latest version of the row (from RAM, that is). The only thing is that in case of server crash, not-yet-written-to-disk commits will be lost.
detailed explanation can be found here:
https://www.postgresql.org/docs/current/wal-async-commit.html
regards,
fabio pardi
Show quoted text
On 15/01/2019 11:58, pshadangi wrote:
To improve commit performance we are planning to use "synchronous_commit=off", with this if multiple clients are reading the same data/row will they always get the latest updated data/row ? (clients are using committed read and we are not using clustered environment, we have just one instance of postgres serving local clients running on the same machine).
For example if client1 updates a row then the updated value is available to client2 immediately after the commit or there is a delay as commit is now asynchronous ?
Hi,
After better thinking, I have to reply to myself since I m not entirely sure of my previous question. (I m digging into the docs, but i do not want to mislead you in the meanwhile)
If i recall correctly, written data is parked in WAL buffer before being synced to disk (to the transaction log).
I m not sure other clients are able to read from WAL buffer, therefore i m not sure the data is available to other clients at that specific point in time.
Maybe somebody else in the ML knows the details by heart?
regards,
fabio pardi
Show quoted text
On 15/01/2019 12:15, Fabio Pardi wrote:
Hi,
all clients will get the latest version of the row (from RAM, that is). The only thing is that in case of server crash, not-yet-written-to-disk commits will be lost.
detailed explanation can be found here:
https://www.postgresql.org/docs/current/wal-async-commit.html
regards,
fabio pardi
On 15/01/2019 11:58, pshadangi wrote:
To improve commit performance we are planning to use "synchronous_commit=off", with this if multiple clients are reading the same data/row will they always get the latest updated data/row ? (clients are using committed read and we are not using clustered environment, we have just one instance of postgres serving local clients running on the same machine).
For example if client1 updates a row then the updated value is available to client2 immediately after the commit or there is a delay as commit is now asynchronous ?
I m not sure other clients are able to read from WAL buffer, therefore
i m not sure the data is available to other clients at that specific
point in time.
No. On the standby the buffer cache has to be populated with the
updates before other client sessions can read it. AFAIK other client
sessions do not read WAL buffers.
That is why synchronous_commit=ON option is there.
After better thinking, I have to reply to myself since I m not entirely
sure of my previous question. (I m digging into the docs, but i do not want
to mislead you in the meanwhile)
Yes, I was not able to get some statement regarding this in the docs,
please let me know if you find something. Thanks.
No. On the standby the buffer cache has to be populated with the updates
before other client sessions can read it. AFAIK other client sessions do
not read WAL buffers.
That is why synchronous_commit=ON option is there.
We don't have standby instance, as I have mentioned we are using just one
instance of postgres serving local clients running on the same machine, do
you know in this case what is the behavior ?
On Tue, Jan 15, 2019 at 5:24 PM Ravi Krishna <srkrishna@fastmail.com> wrote:
Show quoted text
I m not sure other clients are able to read from WAL buffer, therefore i
m not sure the data is
available to other clients at that specific point in time.
No. On the standby the buffer cache has to be populated with the updates
before other client sessions can read it. AFAIK other client sessions do
not read WAL buffers.That is why synchronous_commit=ON option is there.
Sorry I misunderstood. The term "read consistency" is generally used
either in the context of isolation level or in the context of slaves.
We don't have standby instance, as I have mentioned we are using just
one instance of postgres serving local clients running on the same
machine, do you know in this case what is the behavior ?>
You are good. All transactions update buffer cache too, along with WAL
buffer and hence other sessions can immediately see the changes.
synchronous_commit=off will only reduce the fsync calls, which makes
them less crash safe, but the database consistency is not compromised.
Thanks Ravi for the clarification, we will go ahead with
"synchronous_commit=off".
On Wed, Jan 16, 2019 at 10:47 AM Ravi Krishna <srkrishna@fastmail.com>
wrote:
Show quoted text
Sorry I misunderstood. The term "read consistency" is generally used
either in the context of isolation level or in the context of slaves.We don't have standby instance, as I have mentioned we are using just one
instance of postgres serving local clients running on the same machine, do
you know in this case what is the behavior ?You are good. All transactions update buffer cache too, along with WAL
buffer and hence other sessions can immediately see the changes.
synchronous_commit=off will only reduce the fsync calls, which makes them
less crash safe, but the database consistency is not compromised.
A crash-unsafe database is for data you don't care about.
On 1/16/19 2:27 AM, pshadangi wrote:
Thanks Ravi for the clarification, we will go ahead with
"synchronous_commit=off".On Wed, Jan 16, 2019 at 10:47 AM Ravi Krishna <srkrishna@fastmail.com
<mailto:srkrishna@fastmail.com>> wrote:Sorry I misunderstood. The term "read consistency" is generally used
either in the context of isolation level or in the context of slaves.We don't have standby instance, as I have mentioned we are using just
one instance of postgres serving local clients running on the same
machine, do you know in this case what is the behavior ?You are good. All transactions update buffer cache too, along with
WAL buffer and hence other sessions can immediately see the changes.
synchronous_commit=off will only reduce the fsync calls, which makes
them less crash safe, but the database consistency is not compromised.
--
Angular momentum makes the world go 'round.