Feedback-ID: i76984098:Fastmail
Message-ID: <10bcc03d-fa3e-40b6-bdd2-cac0acd046f4@aklaver.com>
Date: Wed, 8 May 2024 09:28:01 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Unexpected data when subscribing to logical replication slot
To: Daniel McKenzie <daniel.mckenzie@curvedental.com>
Cc: pgsql-general@postgresql.org
References: <CACKLY6hwMnSjjipZ57TTz_FC9aWBkg7OpNLJ1tu+GvfEFc4hJA@mail.gmail.com>
 <8a534c5f-e400-4bb5-b39e-2017d259ff06@aklaver.com>
 <CACKLY6hYp+9W9xijXFh_UEpDuoo7bxg-A3deBKcEzEub3K+Kfg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CACKLY6hYp+9W9xijXFh_UEpDuoo7bxg-A3deBKcEzEub3K+Kfg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/10bcc03d-fa3e-40b6-bdd2-cac0acd046f4%40aklaver.com>
Precedence: bulk

On 5/8/24 08:24, Daniel McKenzie wrote:
> It's running both (in docker containers) and also quite a few more 
> docker containers running various .NET applications.

I think what you found is that the r7a.medium instance is not capable 
enough to do all that it is asked without introducing lag under load. 
Answering the questions posed by Tomas Vondra would help get to the 
actual cause of the lag.

In meantime my suspicion is this part:

"For example, when I use a psql terminal to update a user's last name 
from "Jones" to "Smith" then I would expect the enrichment query to find 
"Smith" but it will sometimes still find "Jones". It finds the old data 
perhaps 1 in 50 times."

If this is being run against the Postgres server my guess is that 
synchronous_commit=on is causing the commit on the server to wait for 
the WAL records to be flushed to disk and this is not happening in a 
timely manner in the '... 1 in 50 times' you mention. In that case you 
see the old values not the new committed values. This seems to be 
confirmed when you set synchronous_commit=off and don't see old values.
For completeness per:

https://www.postgresql.org/docs/current/wal-async-commit.html

"However, for short transactions this delay is a major component of the 
total transaction time. Selecting asynchronous commit mode means that 
the server returns success as soon as the transaction is logically 
completed, before the WAL records it generated have actually made their 
way to disk. This can provide a significant boost in throughput for 
small transactions.

Asynchronous commit introduces the risk of data loss. There is a short 
time window between the report of transaction completion to the client 
and the time that the transaction is truly committed (that is, it is 
guaranteed not to be lost if the server crashes).  ...
"

> 
> Daniel McKenzie
> Software Developer

-- 
Adrian Klaver
adrian.klaver@aklaver.com