pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTransaction Wraparound Help-Understanding
5+ messages / 5 participants
[nested] [flat]
* Transaction Wraparound Help-Understanding
@ 2020-07-13 14:40 Jerome Talbert <jtalbert@passur.com>
2020-07-13 14:55 ` Re: Transaction Wraparound Help-Understanding Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Jerome Talbert @ 2020-07-13 14:40 UTC (permalink / raw)
To: PostgreSQL Admin List <pgsql-admin@lists.postgresql.org>
Hello,
I'm trying to understand why autovacuum runs to prevent wraparound on
tables that should be static and don't change. For example, we have tables,
in one of our databases, that get created every day. Those tables are then
actively updated throughout the day. Once a new day begins, a new table is
created for that days data. The previous days table is no longer changed or
updated and used for historical queries only.
So, the way I understand transaction wrap around, I would think the only
table needing vacuuming to prevent wraparound, would be the active table?
But, I also notice the older
tables getting vacuumed to prevent wrap around as well. Can someone help me
understand this?
Here is an example:
30551 tracks None local 2.6 0.1 31.81M 0.00B
01:51.58 N N active autovacuum: VACUUM
public.fused_xyz20191222 (to prevent wraparound)
34151 tracks None local 5.5 0.1 29.96M 2.61K
01:13.31 N N active autovacuum: VACUUM
public.fused_xyz20191119 (to prevent wraparound)
34613 tracks None local 2.3 0.1 29.67M 0.00B
01:07.83 N N active autovacuum: VACUUM
public.fused_xyz20191128 (to prevent wraparound)
39686 tracks None local 2.9 0.0 29.67M 0.00B
00:09.00 N N active autovacuum: VACUUM
public.fused_xyz20200112 (to prevent wraparound)
The tables listed above are static at this point, but as you can see, they
are actively being vacuumed to prevent wraparound.
Thanks,
Jay
--
Jerome Talbert
Senior IT Infrastructure Engineer
PASSUR Aerospace, Inc.
O: 407-205-0688
C: 407-619-9868
jtalbert@passur.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Transaction Wraparound Help-Understanding
2020-07-13 14:40 Transaction Wraparound Help-Understanding Jerome Talbert <jtalbert@passur.com>
@ 2020-07-13 14:55 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2020-07-13 14:55 UTC (permalink / raw)
To: Jerome Talbert <jtalbert@passur.com>; PostgreSQL Admin List <pgsql-admin@lists.postgresql.org>
On Mon, 2020-07-13 at 10:40 -0400, Jerome Talbert wrote:
> I'm trying to understand why autovacuum runs to prevent wraparound on tables that should
> be static and don't change. For example, we have tables, in one of our databases, that
> get created every day. Those tables are then actively updated throughout the day.
> Once a new day begins, a new table is created for that days data. The previous days
> table is no longer changed or updated and used for historical queries only.
>
> So, the way I understand transaction wrap around, I would think the only table needing
> vacuuming to prevent wraparound, would be the active table? But, I also notice the older
> tables getting vacuumed to prevent wrap around as well. Can someone help me understand this?
Every table row that is not frozen contains transaction IDs that determine if it is
visible or not. So even an insert-only static table hast te be vacuumed once,
unless it has been loaded with COPY (FREEZE).
You can do that yourself with VACUUM (FREEZE) or wait for the anti-wraparound vacuum.
Once that has happened, the table will still reveive anti-wraparound vacuum runs,
but these won't do anything.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Synchronous Replication: Where is data visible first?
@ 2024-01-11 06:00 P C <puravc@gmail.com>
2024-01-11 06:43 ` Re: Synchronous Replication: Where is data visible first? David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: P C @ 2024-01-11 06:00 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello,
While discussing with the development team, an interesting question came
up: in a synchronous streaming replication scenario, with
synchronous_commit = remote_apply, will the change be first visible on
Standby (replica)? Primary will wait till the change is applied and
committed on standby, and hence logically this looks to be correct. But I
couldn't find this mentioned explicitly anywhere and hence seeking comments
from the community.
Assuming that the answer is yes, potentially the same can happen even with
synchronous_commit = remote_write | ON because on the way back to Primary,
if the network is slow, there is a chance that Standby may complete
applying the change and make it visible to the users.
Would sincerely appreciate if someone can share a documentation/blog link
that explains this, if there is one.
Many Thanks!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Synchronous Replication: Where is data visible first?
2024-01-11 06:00 Synchronous Replication: Where is data visible first? P C <puravc@gmail.com>
@ 2024-01-11 06:43 ` David G. Johnston <david.g.johnston@gmail.com>
2024-01-12 22:43 ` Re: Synchronous Replication: Where is data visible first? Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2024-01-11 06:43 UTC (permalink / raw)
To: P C <puravc@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Wednesday, January 10, 2024, P C <puravc@gmail.com> wrote:
> Hello,
>
> While discussing with the development team, an interesting question came
> up: in a synchronous streaming replication scenario, with
> synchronous_commit = remote_apply, will the change be first visible on
> Standby (replica)? Primary will wait till the change is applied and
> committed on standby, and hence logically this looks to be correct. But I
> couldn't find this mentioned explicitly anywhere and hence seeking comments
> from the community.
>
IIUC, no, the standbys can never reflect a newer state than what would be
seen on the primary. The state of a given transaction, including most
importantly the locking surrounding it, exists first on the primary and
then is replicated to the secondary. Either the locking will prevent a
dirty read or the dirty read will see the unconfirmed but committed data on
the primary. The locking will be removed on the primary strictly before
the standbys.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Synchronous Replication: Where is data visible first?
2024-01-11 06:00 Synchronous Replication: Where is data visible first? P C <puravc@gmail.com>
2024-01-11 06:43 ` Re: Synchronous Replication: Where is data visible first? David G. Johnston <david.g.johnston@gmail.com>
@ 2024-01-12 22:43 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2024-01-12 22:43 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: P C <puravc@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Wed, Jan 10, 2024 at 11:43:50PM -0700, David G. Johnston wrote:
> On Wednesday, January 10, 2024, P C <puravc@gmail.com> wrote:
>
> Hello,
>
> While discussing with the development team, an interesting question came
> up: in a synchronous streaming replication scenario, with
> synchronous_commit = remote_apply, will the change be first visible on
> Standby (replica)? Primary will wait till the change is applied and
> committed on standby, and hence logically this looks to be correct. But I
> couldn't find this mentioned explicitly anywhere and hence seeking comments
> from the community.
>
>
> IIUC, no, the standbys can never reflect a newer state than what would be seen
> on the primary. The state of a given transaction, including most importantly
> the locking surrounding it, exists first on the primary and then is replicated
> to the secondary. Either the locking will prevent a dirty read or the dirty
> read will see the unconfirmed but committed data on the primary. The locking
> will be removed on the primary strictly before the standbys.
Uh, my research contradicts that, see:
https://momjian.us/main/blogs/pgblog/2020.html#June_3_2020
1 Write commit record to the write-ahead log
2 Flush the write-ahead log to durable storage
3 Update the pg_xact (slide 57)
4 Transfer to replicas
-> 5 Mark the commit as visible to other sessions (ProcArrayEndTransaction() updates PGPROC)
6 Communicate commit to the client
While the transaction will be visible on the primary to the creating
transaction session, it might not be visible to other sessions before it
appears on the replica.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-01-12 22:43 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-13 14:40 Transaction Wraparound Help-Understanding Jerome Talbert <jtalbert@passur.com>
2020-07-13 14:55 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-01-11 06:00 Synchronous Replication: Where is data visible first? P C <puravc@gmail.com>
2024-01-11 06:43 ` Re: Synchronous Replication: Where is data visible first? David G. Johnston <david.g.johnston@gmail.com>
2024-01-12 22:43 ` Re: Synchronous Replication: Where is data visible first? Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox