[GENERAL] Synchronous replication and read consistency
Does sync replication guarantee that any inserted data on primary is immediately visible for read on standbys with no lag. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Synchronous replication and read consistency
Kevin Grittner schrieb am 29.07.2015 um 23:10: No, it means that if the primary is hit by a meteor and you promote the standby, the data will not have been lost. The time between the successful return of the commit on the primary and the time at which the change becomes visible on the standby is normally quite small; you may have trouble running into a case where you notice it, but it can happen. It's actually not that hard to run into. We encountered this when we were running unit tests against a master/slave setup with pgPool: http://postgresql.nabble.com/Synchronous-replication-pgPool-not-all-transactions-immediately-visible-on-standby-tp5820275.html Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Synchronous replication and read consistency
Not necessarily. There has been discussion of adding a new mode which will delay the commit on the primary until it is visible on a synchronous standby, but I don't recall where that left off. Joshua: THis essentially contradicts your statement to me. On Wed, Jul 29, 2015 at 5:10 PM, Kevin Grittner kgri...@ymail.com wrote: Ravi Krishna sravikrish...@gmail.com wrote: As per this: http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION When requesting synchronous replication, each commit of a write transaction will wait until confirmation is received that the commit has been written to the transaction log on disk of both the primary and standby server. Does it mean that, on the standby, when PG writes the transaction log on the disk, it also updates the data buffers to make the transaction visible for all sessions. No, it means that if the primary is hit by a meteor and you promote the standby, the data will not have been lost. The time between the successful return of the commit on the primary and the time at which the change becomes visible on the standby is normally quite small; you may have trouble running into a case where you notice it, but it can happen. Eg: On the primary A big transaction committed Now if I issue a select on the primary looking for the transaction I committed above, I will get what I want. Will I get the same result if instead of primary I issue the select on the standby. Not necessarily. There has been discussion of adding a new mode which will delay the commit on the primary until it is visible on a synchronous standby, but I don't recall where that left off. One of the issues is that with the current guarantee you need multiple replicas to prevent a failure of a standby from stalling the primary indefinitely, and you don't have an easy way to know *which* replica succeeded in persisting the transaction without doing a lot of work. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Synchronous replication and read consistency
On 07/29/2015 02:27 PM, Ravi Krishna wrote: Not necessarily. There has been discussion of adding a new mode which will delay the commit on the primary until it is visible on a synchronous standby, but I don't recall where that left off. Joshua: THis essentially contradicts your statement to me. I would trust Kevin with this particular information. What he wrote was new to me as well. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Synchronous replication and read consistency
Ravi Krishna sravikrish...@gmail.com wrote: As per this: http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION When requesting synchronous replication, each commit of a write transaction will wait until confirmation is received that the commit has been written to the transaction log on disk of both the primary and standby server. Does it mean that, on the standby, when PG writes the transaction log on the disk, it also updates the data buffers to make the transaction visible for all sessions. No, it means that if the primary is hit by a meteor and you promote the standby, the data will not have been lost. The time between the successful return of the commit on the primary and the time at which the change becomes visible on the standby is normally quite small; you may have trouble running into a case where you notice it, but it can happen. Eg: On the primary A big transaction committed Now if I issue a select on the primary looking for the transaction I committed above, I will get what I want. Will I get the same result if instead of primary I issue the select on the standby. Not necessarily. There has been discussion of adding a new mode which will delay the commit on the primary until it is visible on a synchronous standby, but I don't recall where that left off. One of the issues is that with the current guarantee you need multiple replicas to prevent a failure of a standby from stalling the primary indefinitely, and you don't have an easy way to know *which* replica succeeded in persisting the transaction without doing a lot of work. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Synchronous replication and read consistency
Chris/Joshua I would like to know more details. As per this: http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION When requesting synchronous replication, each commit of a write transaction will wait until confirmation is received that the commit has been written to the transaction log on disk of both the primary and standby server. Ah, sorry I misread sync replication as streaming replication... Does it mean that, on the standby, when PG writes the transaction log on the disk, it also updates the data buffers to make the transaction visible for all sessions. Eg: On the primary A big transaction committed Now if I issue a select on the primary looking for the transaction I committed above, I will get what I want. Will I get the same result if instead of primary I issue the select on the standby. Hope it is clear. Synchronous replication is slower by nature. It will slow down the master as well because each commit has to wait for a standby to ack it. The answer to your question is still yes, you will get the same result on the standby. You will actually see less lag than with normal streaming replication in the sense that the standby lagging several transactions behind due to a commit/write burst on the master is not possible anymore. This is of course at the expense of master-performance. Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Synchronous replication and read consistency
Does sync replication guarantee that any inserted data on primary is immediately visible for read on standbys with no lag. Basically yes. Of course there is *some* latency, at the very least from the network. If I run a process on a standby machine that displays a value every 0.1 sec and update the value on the master, I see the standby updating with a lag that feels less than 0.2 sec or so. You might have lag, however, in situations where you have so much write into the master that the network or standby is not able to catch up. After the write burst is over, the stanby will catch up as it quickly as possible, though. Also, you use the word consistency, that would be something else... Of course you always get consistent data, lag or not. This is Postgres after all :) Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Synchronous replication and read consistency
Chris/Joshua I would like to know more details. As per this: http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION When requesting synchronous replication, each commit of a write transaction will wait until confirmation is received that the commit has been written to the transaction log on disk of both the primary and standby server. Does it mean that, on the standby, when PG writes the transaction log on the disk, it also updates the data buffers to make the transaction visible for all sessions. Eg: On the primary A big transaction committed Now if I issue a select on the primary looking for the transaction I committed above, I will get what I want. Will I get the same result if instead of primary I issue the select on the standby. Hope it is clear. On Wed, Jul 29, 2015 at 2:20 PM, Chris Mair ch...@1006.org wrote: Does sync replication guarantee that any inserted data on primary is immediately visible for read on standbys with no lag. Basically yes. Of course there is *some* latency, at the very least from the network. If I run a process on a standby machine that displays a value every 0.1 sec and update the value on the master, I see the standby updating with a lag that feels less than 0.2 sec or so. You might have lag, however, in situations where you have so much write into the master that the network or standby is not able to catch up. After the write burst is over, the stanby will catch up as it quickly as possible, though. Also, you use the word consistency, that would be something else... Of course you always get consistent data, lag or not. This is Postgres after all :) Bye, Chris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
