1
0:0:0,06 --> 0:0:0,59999996
Nik: Hello, hello.
2
0:0:0,59999996 --> 0:0:1,92
This is Postgres.FM.
3
0:0:1,92 --> 0:0:3,84
My name is Nik, PostgresAI.
4
0:0:3,84 --> 0:0:7,3199997
And as usual with me, Michael pgMustard.
5
0:0:7,68 --> 0:0:8,5
Hi, Michael.
6
0:0:9,48 --> 0:0:10,42
Michael: Hello, Nik.
7
0:0:10,9 --> 0:0:13,82
Nik: I proposed to discuss hot_standby_feedback.
8
0:0:14,599999 --> 0:0:18,04
And that's interesting because
I have 4 customers with this problem
9
0:0:18,04 --> 0:0:22,26
right now, different on different
platforms, right?
10
0:0:22,26 --> 0:0:22,66
Yeah.
11
0:0:22,66 --> 0:0:25,56
On Supabase on RDS and something
else.
12
0:0:25,56 --> 0:0:30,72
It's very interesting how problems
sometimes are very silent
13
0:0:30,86 --> 0:0:33,98
until suddenly they come from multiple
directions.
14
0:0:34,04 --> 0:0:34,54
Right.
15
0:0:34,64 --> 0:0:37,94
I don't know why it's happening,
right, but it's just happening.
16
0:0:38,739998 --> 0:0:41,86
Michael: All 4, are they all suffering
from the same direction?
17
0:0:42,559998 --> 0:0:43,059998
Yeah.
18
0:0:43,22 --> 0:0:44,02
Okay, interesting.
19
0:0:45,06 --> 0:0:48,4
Nik: So all of them are complaining
about read replicas lagging.
20
0:0:50,58 --> 0:0:55,38
And it's all because platforms,
most platforms keep hot_standby_feedback
21
0:0:55,38 --> 0:0:59,48
off by default, like Postgres
itself has it off by default.
22
0:1:1,56 --> 0:1:8,38
While my honest opinion, like strong
opinion, I will say, is
23
0:1:8,38 --> 0:1:10,88
that you cannot have read replicas
with off.
24
0:1:11,4 --> 0:1:18,28
For general OLTP case with many
users, And I say this after a
25
0:1:18,28 --> 0:1:19,26
lot of experience.
26
0:1:19,94 --> 0:1:26,0
I built 3 social networks with
each 1 of them achieved many million
27
0:1:26,0 --> 0:1:29,88
users and 1 plus DAU daily active
users.
28
0:1:30,56 --> 0:1:32,84
And we needed to scale reads of
course, right?
29
0:1:32,84 --> 0:1:34,58
So we needed read replicas.
30
0:1:35,58 --> 0:1:37,24
1st 1 I built in 2006.
31
0:1:38,92 --> 0:1:42,84
And also I helped many companies
who also needed to scale reads.
32
0:1:43,52 --> 0:1:46,5
My honest opinion, you cannot have
read replica with hot_standby_feedback
33
0:1:46,5 --> 0:1:49,58
off, just because
it will start lagging.
34
0:1:50,22 --> 0:1:51,6
But this is how it is.
35
0:1:52,36 --> 0:1:56,14
All people need to rediscover it
because defaults are...
36
0:1:56,4 --> 0:1:58,14
This is our favorite topic, right?
37
0:1:58,14 --> 0:2:1,9
Defaults are terrible in many aspects.
38
0:2:3,1 --> 0:2:6,46
Michael: Well, yeah, I'm actually
not sure I fully agree on this
39
0:2:6,46 --> 0:2:6,96
1 yet.
40
0:2:6,96 --> 0:2:7,8
Maybe I will by the end.
41
0:2:7,8 --> 0:2:8,76
Nik: You are not alone.
42
0:2:9,96 --> 0:2:11,44
Michael: Yeah, I'm not sure.
43
0:2:11,94 --> 0:2:16,9
It's a really difficult 1, I think,
this 1 in terms of like risks
44
0:2:16,92 --> 0:2:20,44
versus, yeah, when things bite
you.
45
0:2:20,44 --> 0:2:22,54
But let's, let's, maybe let's go
back to basics.
46
0:2:22,54 --> 0:2:23,42
Like what's the…
47
0:2:23,42 --> 0:2:24,38
Nik: Yeah, yeah, yeah.
48
0:2:24,38 --> 0:2:28,94
So we should discuss, and I just
threw it in the, at the very
49
0:2:28,94 --> 0:2:30,6
beginning to set my goal.
50
0:2:30,6 --> 0:2:35,58
I'm going to discuss it in detail
and explain my way of thinking.
51
0:2:36,1 --> 0:2:39,92
But just separately, I'm just wondering,
are there people who
52
0:2:39,92 --> 0:2:46,0
really scaled reads on really large
web or mobile apps and had
53
0:2:46,0 --> 0:2:49,64
it off because I just don't understand
how it's, how it can be
54
0:2:49,64 --> 0:2:52,28
possible unless you, you are okay.
55
0:2:52,28 --> 0:2:54,86
Your application is okay with lagging
replicas.
56
0:2:54,96 --> 0:3:0,32
Sometimes it's so if you serve
traffic to some users and some
57
0:3:0,32 --> 0:3:3,9
stale information, like lagging
some seconds or maybe minutes
58
0:3:3,9 --> 0:3:4,62
is fine.
59
0:3:5,02 --> 0:3:9,12
I can imagine that you basically
show some news for example or
60
0:3:9,12 --> 0:3:10,52
blog posts or something.
61
0:3:10,68 --> 0:3:15,58
But in general, modern applications
like social media applications,
62
0:3:15,82 --> 0:3:20,24
e-commerce and so on Where you
want to scale reads to asynchronous
63
0:3:20,38 --> 0:3:23,3
replicas, of course we talk about
asynchronous replicas here.
64
0:3:24,12 --> 0:3:27,42
Then we do need hot_standby_feedback
to be on.
65
0:3:28,52 --> 0:3:30,4
Anyway, let's start from the basics.
66
0:3:30,44 --> 0:3:34,62
We had several episodes where we
covered basics already.
67
0:3:34,64 --> 0:3:36,56
I just wanted to have a quick recap.
68
0:3:37,2 --> 0:3:40,3
1st of all, my favorite thing,
if some people don't realize,
69
0:3:40,38 --> 0:3:42,88
there are hidden columns, xmin,
xmax.
70
0:3:43,38 --> 0:3:47,52
xmin is a transaction ID which
gave birth to this tuple.
71
0:3:47,54 --> 0:3:49,4
Tuple is a row version, right?
72
0:3:49,74 --> 0:3:53,76
And there is a concept of xmin horizon,
and we had a separate
73
0:3:53,76 --> 0:4:0,22
episode about it, which defines
global for whole that database.
74
0:4:0,28 --> 0:4:6,74
Global horizon, which says this
is the oldest transaction ID that
75
0:4:6,74 --> 0:4:11,18
defines data snapshot, which is
still needed to some transactions,
76
0:4:12,26 --> 0:4:13,04
some clients.
77
0:4:13,1 --> 0:4:13,6
Right.
78
0:4:14,06 --> 0:4:17,62
And it means, And this is global,
a single value basically.
79
0:4:17,74 --> 0:4:21,54
There are many values coming from
different sources, but horizon
80
0:4:21,66 --> 0:4:24,26
is absolute minimum of all of them.
81
0:4:25,52 --> 0:4:30,06
And it defines the garbage collection
behavior, vacuum behavior,
82
0:4:30,06 --> 0:4:30,56
right?
83
0:4:30,78 --> 0:4:36,98
Because when DELETE or UPDATE or
canceled INSERT happens, dead
84
0:4:36,98 --> 0:4:38,52
tuples are produced, right?
85
0:4:38,52 --> 0:4:41,18
And the job is not done fully.
86
0:4:41,2 --> 0:4:43,76
The remaining of the job is garbage
collection.
87
0:4:44,06 --> 0:4:46,08
Dead tuple must be deleted later.
88
0:4:46,86 --> 0:4:52,84
And if this dead tuple belongs
to a transaction, which is newer
89
0:4:53,16 --> 0:4:55,38
than our xmin horizon, Vacuum cannot
delete it.
90
0:4:55,38 --> 0:4:56,48
And this is simple logic.
91
0:4:56,48 --> 0:4:57,22
It's global.
92
0:4:57,44 --> 0:4:59,88
Some people think it's maybe per
table, but it's not.
93
0:4:59,88 --> 0:5:0,56
It's global.
94
0:5:1,4 --> 0:5:1,9
Michael: Yeah.
95
0:5:2,04 --> 0:5:6,1
And this is all very simple on
a single node.
96
0:5:6,1 --> 0:5:10,24
Just if you're only worried about
having a primary database or
97
0:5:10,24 --> 0:5:16,26
maybe only an HA replica, no, it's
nice and simple, right?
98
0:5:16,26 --> 0:5:19,68
Like we just hold on to the very
versions that might be needed
99
0:5:19,68 --> 0:5:20,58
by the oldest.
100
0:5:22,24 --> 0:5:27,48
Query or whatever the process might
need it and then once that's
101
0:5:27,48 --> 0:5:27,98
finished.
102
0:5:28,48 --> 0:5:31,72
We do the cleanup or vacuum does
it right
103
0:5:31,72 --> 0:5:33,84
Nik: And here we need to dive into
some details.
104
0:5:33,84 --> 0:5:37,4
So yeah, let's 1st think about
only the primary and single node
105
0:5:37,44 --> 0:5:40,1
situation where usually people
start.
106
0:5:40,52 --> 0:5:45,24
You have, there are 5 reasons,
5 sources and our monitoring covers
107
0:5:45,24 --> 0:5:45,74
them.
108
0:5:45,78 --> 0:5:48,36
And we discussed it in the xmin horizon
episode.
109
0:5:48,94 --> 0:5:51,68
5 reasons to block xmin horizon.
110
0:5:52,2 --> 0:5:56,08
Normally if all transactions are
fast, they finish promptly,
111
0:5:57,56 --> 0:6:1,88
no replication is used, neither
logical nor physical, and you
112
0:6:1,88 --> 0:6:4,14
don't use prepared transactions.
113
0:6:5,64 --> 0:6:10,46
In this case, xmin horizon flows
naturally, and you can calculate
114
0:6:10,68 --> 0:6:14,54
age of xmin horizon, meaning that
how many transaction IDs, real
115
0:6:14,54 --> 0:6:19,2
transaction IDs passed since this
horizon and ideally it's very
116
0:6:19,2 --> 0:6:19,54
low.
117
0:6:19,54 --> 0:6:21,36
Say thousands is quite low.
118
0:6:21,88 --> 0:6:23,32
10, 000 is quite low.
119
0:6:23,32 --> 0:6:25,06
1 million it's already noticeable.
120
0:6:25,14 --> 0:6:25,64
Right.
121
0:6:26,38 --> 0:6:29,8
And we know, we all know that 1
billion is already basically
122
0:6:29,8 --> 0:6:35,82
0.5 of capacity we have because
if the whole capacity is 2.1
123
0:6:35,98 --> 0:6:37,2
billion roughly, right?
124
0:6:37,7 --> 0:6:45,14
We have 4-byte integer transaction
IDs, and 0.5 of that space
125
0:6:45,56 --> 0:6:50,92
is our future, 0.5 of it is our
past, and we need freezing vacuum.
126
0:6:51,04 --> 0:6:58,04
1 of its jobs is to freeze tuples
saying that don't look at this
127
0:6:58,04 --> 0:7:1,74
xmin, it looks like from future,
but it's from the past, right?
128
0:7:2,22 --> 0:7:9,02
And if we don't freeze during 2.1,
transaction ID, if our xmin horizon
129
0:7:9,84 --> 0:7:13,22
is that old, age of it is that
old.
130
0:7:13,22 --> 0:7:15,32
We can use function age, actually,
right?
131
0:7:15,58 --> 0:7:19,06
In this case, it means that we're
going to be in big trouble
132
0:7:19,06 --> 0:7:22,48
and database will be not accessible
because transaction ID wraparound
133
0:7:22,72 --> 0:7:23,5
will happen.
134
0:7:24,24 --> 0:7:27,8
This is the biggest danger actually
of xmin horizon being blocked
135
0:7:27,8 --> 0:7:28,88
for a long time.
136
0:7:29,44 --> 0:7:33,48
And In the case of single node
of the primary, everything is
137
0:7:33,48 --> 0:7:39,18
relatively simple, but I noticed
many people don't realize nuances
138
0:7:39,44 --> 0:7:39,94
here.
139
0:7:40,24 --> 0:7:45,48
Remember this year I started saying
that the warning, let's avoid
140
0:7:45,48 --> 0:7:48,84
long running transactions is quite
inaccurate.
141
0:7:49,64 --> 0:7:50,14
Right.
142
0:7:50,14 --> 0:7:52,44
Remember this, we're talking about
this.
143
0:7:52,44 --> 0:7:56,1
And I noticed actually, even AI
is making a lot of mistakes.
144
0:7:56,12 --> 0:8:2,1
For example, with GPT-6 Astra,
I decided to renew graphics in
145
0:8:2,1 --> 0:8:6,3
PGSimCity and also make it
more entertainable and have some
146
0:8:6,56 --> 0:8:10,14
bad scenarios to be reproduced
and so you could see what happens.
147
0:8:10,6 --> 0:8:14,54
And it wrote a very simple thing
and I see it quite often.
148
0:8:15,24 --> 0:8:20,54
It said somebody typed BEGIN and
left transaction open for a
149
0:8:20,54 --> 0:8:22,16
day and we're in trouble.
150
0:8:22,2 --> 0:8:25,46
But it's not simple BEGIN is not
a trouble.
151
0:8:25,68 --> 0:8:29,82
Because by default we have read
committed transaction isolation.
152
0:8:30,4 --> 0:8:35,84
Which means that after each statement
snapshot is free to go.
153
0:8:35,88 --> 0:8:38,1
Basically we don't block xmin for
ever.
154
0:8:38,1 --> 0:8:41,68
So if we think about transaction
at default transaction isolation
155
0:8:41,68 --> 0:8:46,28
level, if it consists of multiple
statements, while each statement
156
0:8:46,28 --> 0:8:49,98
lasts, of course we need that snapshot
and we block xmin horizon.
157
0:8:50,2 --> 0:8:51,8
We need to work with that data.
158
0:8:51,88 --> 0:8:53,96
Some SELECT is running on some
huge table.
159
0:8:53,96 --> 0:8:57,04
Yeah, it lasts for long and we
need that data.
160
0:8:57,04 --> 0:9:1,98
But once this SELECT finishes,
Even if transaction is still open,
161
0:9:2,9 --> 0:9:4,4
that snapshot is not needed.
162
0:9:4,4 --> 0:9:7,54
And basically we allow xmin horizon
to shift.
163
0:9:9,06 --> 0:9:13,38
And Simple BEGIN doesn't hold any
problem, any snapshot at all,
164
0:9:13,38 --> 0:9:13,88
right?
165
0:9:15,28 --> 0:9:17,02
At default isolation level.
166
0:9:18,22 --> 0:9:22,2
So yeah, it's very inaccurate and
that's why saying, oh, long
167
0:9:22,2 --> 0:9:24,34
running transactions are dangerous.
168
0:9:24,78 --> 0:9:26,54
It's too rough.
169
0:9:26,76 --> 0:9:31,2
It's not a precise statement because
not every long running transaction
170
0:9:31,34 --> 0:9:31,84
is.
171
0:9:31,84 --> 0:9:32,34
Yes.
172
0:9:33,72 --> 0:9:37,54
Michael: Some are of course, but
not quite all right.
173
0:9:37,54 --> 0:9:39,52
There are lots of cases where they're
not.
174
0:9:40,28 --> 0:9:40,52
Yeah, sure.
175
0:9:40,52 --> 0:9:41,02
Nik: Right.
176
0:9:41,14 --> 0:9:44,04
Of course, we don't talk about
locks here because there are 2
177
0:9:44,04 --> 0:9:49,78
topics, locks and xmin horizon
blockage, 2 dangers of long-running
178
0:9:49,78 --> 0:9:50,28
transactions.
179
0:9:50,5 --> 0:9:56,42
So we need to distinguish read
committed transactions and transactions
180
0:9:56,54 --> 0:10:1,86
running at higher isolation level,
repeatable read and serializable.
181
0:10:3,18 --> 0:10:6,68
And repeatable read is pretty common
because this is how pg_dump
182
0:10:6,68 --> 0:10:7,18
works.
183
0:10:7,9 --> 0:10:11,94
So every time you run pg_dump, even
if it's not doing, of course
184
0:10:11,94 --> 0:10:14,96
it's doing something always, right,
but it captures snapshot
185
0:10:14,96 --> 0:10:18,56
in the very beginning of transaction,
And even if you dump a
186
0:10:18,56 --> 0:10:22,26
lot of tables, you select from
them, the pg_dump selects from
187
0:10:22,26 --> 0:10:25,12
them, it shifts to the next table.
188
0:10:25,52 --> 0:10:29,6
Snapshot is still the same as it
was defined at the very beginning
189
0:10:29,6 --> 0:10:30,36
of transaction.
190
0:10:30,64 --> 0:10:34,64
It means if you start a transaction
at repeatable read, that's
191
0:10:34,64 --> 0:10:36,26
enough to be harmful.
192
0:10:36,98 --> 0:10:40,3
You just say START TRANSACTION
or BEGIN at repeatable isolation
193
0:10:40,38 --> 0:10:40,76
level.
194
0:10:40,76 --> 0:10:42,14
I always forget syntax.
195
0:10:43,52 --> 0:10:46,74
You don't need even to run any
queries inside it.
196
0:10:46,74 --> 0:10:48,02
It's already harmful.
197
0:10:48,74 --> 0:10:49,44
It holds.
198
0:10:49,54 --> 0:10:52,54
Michael: You say harmful, it starts
to hold the xmin horizon,
199
0:10:52,54 --> 0:10:53,04
right?
200
0:10:53,68 --> 0:10:55,12
It affects, yeah.
201
0:10:55,32 --> 0:10:57,74
Short holds are not expected, right?
202
0:10:58,18 --> 0:11:0,68
The reason it's being held is for
a good reason.
203
0:11:1,08 --> 0:11:6,88
If you're doing a dump, you want
probably everything, not just
204
0:11:6,88 --> 0:11:10,24
consistency, but you want the data
at that point in time, and
205
0:11:10,24 --> 0:11:14,12
if it's changed, then you want,
like you still need those, you
206
0:11:14,12 --> 0:11:16,12
can't have those rows being cleaned
up.
207
0:11:16,16 --> 0:11:18,06
Nik: So you don't need to pay…
IM – This is how consistency is
208
0:11:18,06 --> 0:11:18,94
defined, right?
209
0:11:18,94 --> 0:11:19,36
TG –
210
0:11:19,36 --> 0:11:20,15
Michael: Yeah, yeah.
211
0:11:20,15 --> 0:11:20,36
IM
212
0:11:20,36 --> 0:11:23,36
Nik: – Because if you read from
2 tables from different points
213
0:11:23,36 --> 0:11:25,36
of time, this is inconsistent dump.
214
0:11:25,46 --> 0:11:26,82
So we don't want that.
215
0:11:27,1 --> 0:11:29,12
That's why we need a repeatable read.
216
0:11:29,34 --> 0:11:30,64
Michael: But you say it's harmful.
217
0:11:30,66 --> 0:11:34,74
It just means we're holding on
to a bit of extra data maybe bloating
218
0:11:34,74 --> 0:11:39,44
some indexes a bit like there's
a an amount of that we should
219
0:11:39,44 --> 0:11:42,18
always expect when we're running
Postgres right like it there's
220
0:11:42,18 --> 0:11:45,48
a certain amount of that will always
be natural we try and minimize
221
0:11:45,48 --> 0:11:47,68
it and we try not to let it get
excessive.
222
0:11:49,46 --> 0:11:54,52
But then if that goes on for too
long, it gets into that boundary
223
0:11:54,52 --> 0:11:57,76
where it is maybe excessive or
causing other issues.
224
0:11:57,88 --> 0:11:58,98
Is that what we're trying to say?
225
0:11:58,98 --> 0:12:3,84
So like harmful, yes, but like
it, it scales with the amount
226
0:12:3,84 --> 0:12:8,86
of time or the maybe like time
multiplied by the amount of churn
227
0:12:8,86 --> 0:12:9,52
in that time.
228
0:12:9,52 --> 0:12:9,84
Nik: Yeah.
229
0:12:9,84 --> 0:12:9,96
Yeah.
230
0:12:9,96 --> 0:12:10,24
Yeah.
231
0:12:10,24 --> 0:12:10,74
Database.
232
0:12:11,74 --> 0:12:16,52
I usually say you need like, it's
really hard to understand age
233
0:12:16,52 --> 0:12:20,4
and number of transactions, although
it's more representative
234
0:12:20,66 --> 0:12:22,16
metric in this context.
235
0:12:22,66 --> 0:12:26,16
If you say like long transaction
running like 3 hours during
236
0:12:26,16 --> 0:12:31,02
busy time on like at high traffic
is not the same as during weekend
237
0:12:31,02 --> 0:12:31,96
with low traffic, right?
238
0:12:31,96 --> 0:12:35,42
Because it might be even nothing
happened during 3 hours at weekend.
239
0:12:35,42 --> 0:12:40,04
Sometimes we see some applications
with very acute spikes of
240
0:12:40,04 --> 0:12:40,98
traffic, right?
241
0:12:41,12 --> 0:12:46,06
But, but so it's, there is no like
very clear connection between
242
0:12:46,06 --> 0:12:50,36
time and I, I name it XID consumption
or XID growth rate.
243
0:12:50,74 --> 0:12:55,9
So how fast real transaction ID
grows because this is what defines
244
0:12:56,1 --> 0:12:57,82
this xmin horizon age.
245
0:12:57,94 --> 0:13:0,56
And 1 of the contributors by the
way is savepoints, sub-transactions.
246
0:13:0,86 --> 0:13:5,28
If you use them a lot in 1 transaction,
you consume many real
247
0:13:5,28 --> 0:13:9,14
transaction IDs, because every
savepoint gets allocation of
248
0:13:9,14 --> 0:13:10,62
XID, real XID.
249
0:13:11,0 --> 0:13:15,8
So this is 1 of the dangers I identified,
that savepoints lead
250
0:13:15,8 --> 0:13:17,62
to higher consumption of XIDs.
251
0:13:18,68 --> 0:13:22,66
So yeah, and yeah, if it, of course,
like if we block a vacuum,
252
0:13:22,66 --> 0:13:26,82
it also, we need to be very accurate
with language here because
253
0:13:27,18 --> 0:13:30,52
I realized also when we say vacuum
is blocked, some people think
254
0:13:30,7 --> 0:13:32,28
it's blocked, it like fails.
255
0:13:32,78 --> 0:13:36,92
I even saw, so I have a new tool
to be released soon called PGBS
256
0:13:37,02 --> 0:13:41,5
detector, which is going to, and
we started using it internally
257
0:13:41,54 --> 0:13:46,48
to identify bullshit in our write-ups
and I saw RDS article from
258
0:13:46,48 --> 0:13:50,12
2019 and this was the only article
talking about hot_standby_feedback
259
0:13:50,12 --> 0:13:51,44
from RDS.
260
0:13:52,24 --> 0:13:53,08
Nothing else.
261
0:13:53,48 --> 0:13:56,92
So I tried to understand why they
keep default off and what's
262
0:13:56,92 --> 0:13:58,38
the reasoning behind it.
263
0:13:58,52 --> 0:14:4,14
So I saw that in that article,
this like very confusing statement,
264
0:14:4,28 --> 0:14:9,9
like vacuum is blocked and then
error like canceled statement,
265
0:14:10,2 --> 0:14:11,98
like it was vacuum statement canceled.
266
0:14:11,98 --> 0:14:17,38
No, when we say xmin horizon blockage
leads to affects vacuum
267
0:14:17,38 --> 0:14:22,48
negatively, It's not like vacuum
fails or cannot work at all.
268
0:14:22,48 --> 0:14:23,44
It works partially.
269
0:14:23,76 --> 0:14:29,24
It cleans up the dead tuples which
became dead before xmin horizon.
270
0:14:30,06 --> 0:14:34,24
And it just keeps and reports it
in the logs, tuples which it
271
0:14:34,24 --> 0:14:37,96
cannot clean as dead, already dead
but cannot be cleaned up yet.
272
0:14:37,96 --> 0:14:38,8
I don't remember.
273
0:14:38,8 --> 0:14:40,24
It cannot be removed, cannot be
deleted.
274
0:14:40,24 --> 0:14:41,34
I don't remember exactly.
275
0:14:42,04 --> 0:14:45,06
But this is what's useful to see
because you can see for each
276
0:14:45,06 --> 0:14:49,64
table, a vacuum visited, autovacuum
visited, you can see what
277
0:14:49,64 --> 0:14:51,74
is the damage for this.
278
0:14:52,08 --> 0:14:56,52
You can also run VACUUM VERBOSE
and VERBOSE will report such
279
0:14:56,52 --> 0:15:0,18
tuples which are dead, but cannot
be deleted because it's xmin horizon.
280
0:15:0,28 --> 0:15:2,72
It actually also reports xmin horizon.
281
0:15:3,34 --> 0:15:7,44
It just prints the transaction
ID and what's the age of it, which
282
0:15:7,44 --> 0:15:8,74
is very useful as well.
283
0:15:8,74 --> 0:15:11,4
So that's why autovacuum logging
should be enabled.
284
0:15:12,38 --> 0:15:16,22
And maybe not 0, which means all
occurrences of autovacuum,
285
0:15:16,32 --> 0:15:21,06
but maybe at least above a few
seconds, 10 seconds or 1 second,
286
0:15:21,34 --> 0:15:26,92
if you're concerned about some
very often executions, which are
287
0:15:26,92 --> 0:15:27,74
very fast.
288
0:15:27,8 --> 0:15:31,3
So anyway, autovacuum logs are
quite useful in this area.
289
0:15:31,88 --> 0:15:32,72
What's next?
290
0:15:33,26 --> 0:15:36,1
Michael: I think we've not really
even discussed what hot_stand
291
0:15:36,22 --> 0:15:37,58
by_feedback does.
292
0:15:37,68 --> 0:15:43,32
We've talked about the retention
of dead tuples while like something's
293
0:15:43,32 --> 0:15:48,26
happening, but hot_standby_feedback
comes in because it's something
294
0:15:48,26 --> 0:15:49,9
happening not on the primary, right?
295
0:15:49,9 --> 0:15:50,28
Like it's
296
0:15:50,28 --> 0:15:52,12
Nik: something happening on your
replica.
297
0:15:52,12 --> 0:15:53,54
Yeah, it's conflicts.
298
0:15:53,8 --> 0:15:57,54
So if you think how replication
works, physical replication,
299
0:15:57,56 --> 0:16:0,06
it replays WAL like it was recovery.
300
0:16:0,06 --> 0:16:3,48
It was built on top of recovery
from crashes, right?
301
0:16:3,82 --> 0:16:6,96
And the hot_standby_feedback is
just constantly replaying WAL
302
0:16:6,96 --> 0:16:8,3
like it was a recovery.
303
0:16:8,88 --> 0:16:13,34
That's why a function to check
is it primary or standby is called
304
0:16:13,34 --> 0:16:15,36
pg_is_in_recovery, which is confusing.
305
0:16:15,54 --> 0:16:16,04
Right?
306
0:16:16,68 --> 0:16:17,54
And it replays.
307
0:16:17,54 --> 0:16:18,16
WAL, good.
308
0:16:18,16 --> 0:16:22,58
But what if we have some query
ongoing, selecting from a huge
309
0:16:22,58 --> 0:16:24,06
table, for example, reporting,
right?
310
0:16:24,06 --> 0:16:24,96
It's pretty common.
311
0:16:24,96 --> 0:16:28,48
Let's offload heavy reporting queries
to read replicas.
312
0:16:29,24 --> 0:16:34,52
If it's running there and WAL has
instruction to clean up dead
313
0:16:34,66 --> 0:16:37,5
tuples which on the primary already
dead but we still reading
314
0:16:37,5 --> 0:16:38,0
them.
315
0:16:38,72 --> 0:16:42,76
This is 1 of conflicts and this
is snapshot conflict of replication
316
0:16:42,78 --> 0:16:47,54
conflicts with a query or query
or transaction conflicts with
317
0:16:47,54 --> 0:16:50,26
this WAL data right It cannot be
replayed.
318
0:16:50,94 --> 0:16:56,4
So depending on the type of physical
standby, 1 of 2 settings
319
0:16:56,68 --> 0:16:59,78
are used to decide what to do.
320
0:16:59,86 --> 0:17:1,72
So what to do is obvious.
321
0:17:2,08 --> 0:17:3,54
Replication is paused.
322
0:17:4,2 --> 0:17:5,26
It cannot be replayed.
323
0:17:7,42 --> 0:17:8,6
This is default option.
324
0:17:9,82 --> 0:17:13,04
So we wait until the end of this
transaction.
325
0:17:14,1 --> 0:17:15,78
Michael: And then we continue replaying.
326
0:17:17,22 --> 0:17:19,88
Nik: We wait until xmin horizon
shifts.
327
0:17:20,58 --> 0:17:22,54
If it shifts, we can replay it.
328
0:17:23,36 --> 0:17:26,86
So you can see it manually in pg_stat_
activity backend_xmin
329
0:17:27,26 --> 0:17:27,76
column.
330
0:17:28,14 --> 0:17:35,44
And also it's, yeah, So backend_
xmin is for each backend, you
331
0:17:35,44 --> 0:17:41,12
can see this local horizon for
each backend and minimum of them
332
0:17:41,12 --> 0:17:43,16
is our horizon for this node.
333
0:17:44,02 --> 0:17:46,74
It waits, it waits not forever.
334
0:17:47,62 --> 0:17:53,82
There is a setting I believe it's
called max_standby_streaming_
335
0:17:54,48 --> 0:17:54,98
delay.
336
0:17:55,86 --> 0:17:56,36
Yes,
337
0:17:56,74 --> 0:17:59,54
Michael: there's max_standby_streaming_
delay and max_standby_
338
0:17:59,54 --> 0:18:0,54
archive_delay.
339
0:18:0,72 --> 0:18:2,84
Nik: I will never remember all
GUCs.
340
0:18:2,84 --> 0:18:3,34
Yeah.
341
0:18:3,46 --> 0:18:6,76
So yeah, there are 2, 1 for streaming,
1 for WAL replay.
342
0:18:6,86 --> 0:18:10,74
Replicas which are consuming WALs
from archive, like S3 archive
343
0:18:10,74 --> 0:18:13,4
or something, according to restore_
command.
344
0:18:13,58 --> 0:18:17,44
And, but more common because they
have smaller lags is streaming
345
0:18:17,44 --> 0:18:22,36
replication, which connects to
the primary, unless we use cascade
346
0:18:22,36 --> 0:18:26,6
replication, and just streams the
WALs and applies them.
347
0:18:26,82 --> 0:18:29,98
And this is when we use max_standby_
streaming_delay.
348
0:18:30,86 --> 0:18:32,72
By default, I think it's 30 seconds.
349
0:18:33,54 --> 0:18:35,42
It waits a maximum of 30 seconds.
350
0:18:36,22 --> 0:18:42,04
After this, Postgres says replication
is more important and just
351
0:18:42,04 --> 0:18:43,72
cancels the query.
352
0:18:44,54 --> 0:18:47,76
And the 1st thing that happens,
people don't like queries being
353
0:18:47,76 --> 0:18:48,48
canceled, right?
354
0:18:48,48 --> 0:18:51,24
Or important reports.
355
0:18:51,6 --> 0:18:53,94
Michael: Especially sometimes long
ones, like if it's a long
356
0:18:53,94 --> 0:18:57,44
reporting query, you've already
waited 10 minutes for it.
357
0:18:57,44 --> 0:18:57,66
Nik: Right.
358
0:18:57,66 --> 0:19:1,26
And that's very visible because
reporting usually is close to
359
0:19:1,26 --> 0:19:3,74
decision makers, So they start
complaining very fast.
360
0:19:3,74 --> 0:19:5,64
Like, why report was not delivered?
361
0:19:5,64 --> 0:19:7,68
Okay, we have conflicts.
362
0:19:7,7 --> 0:19:8,2
Canceled.
363
0:19:8,46 --> 0:19:13,08
Okay, let's increase this to 3
minutes, 30 minutes, 3 hours a
364
0:19:13,08 --> 0:19:13,58
day.
365
0:19:14,44 --> 0:19:15,34
And it's okay.
366
0:19:15,9 --> 0:19:16,4
Yeah.
367
0:19:16,94 --> 0:19:18,96
At extreme, you can set minus 1.
368
0:19:19,62 --> 0:19:23,98
And so replication will wait until,
369
0:19:24,28 --> 0:19:24,78
Michael: yeah.
370
0:19:25,32 --> 0:19:26,04
So what happens now?
371
0:19:26,04 --> 0:19:28,38
And this whole time, I think it's
really important to stress,
372
0:19:28,38 --> 0:19:33,28
this whole time anything newer
than that conflict is not getting
373
0:19:33,28 --> 0:19:35,4
replayed on the replica.
374
0:19:35,46 --> 0:19:39,96
So no, any new inserts, any updates,
any deletes, nothing's coming
375
0:19:39,96 --> 0:19:40,46
through
376
0:19:40,96 --> 0:19:42,44
Nik: until that query finishes.
377
0:19:42,44 --> 0:19:43,055
Yeah, it's global.
378
0:19:43,055 --> 0:19:43,84
Horizon was global.
379
0:19:43,94 --> 0:19:46,42
Replication is just single process
actually.
380
0:19:46,72 --> 0:19:49,58
You can see a startup process in
top or ps.
381
0:19:50,02 --> 0:19:51,94
And this is what applies changes.
382
0:19:52,2 --> 0:19:55,76
By the way, changes might be received
already because the replication
383
0:19:55,76 --> 0:19:56,5
has stages.
384
0:19:56,68 --> 0:20:0,06
They can be flushed to local pg_wal
directory, but they just
385
0:20:0,06 --> 0:20:3,82
cannot be applied because somebody
needs very old, all the data,
386
0:20:4,0 --> 0:20:6,86
which we already want to clean
up.
387
0:20:7,8 --> 0:20:8,3
Right.
388
0:20:8,8 --> 0:20:11,92
So what happens next?
389
0:20:12,5 --> 0:20:13,54
Reports work.
390
0:20:13,62 --> 0:20:17,64
For example, we set 3 hours, All
reports last no more than 1
391
0:20:17,64 --> 0:20:18,12
hour.
392
0:20:18,12 --> 0:20:18,98
We are good.
393
0:20:19,74 --> 0:20:25,06
But then, usually what happens,
some users started to complain.
394
0:20:25,24 --> 0:20:26,1
Stale data.
395
0:20:27,74 --> 0:20:30,96
And if your load balancer is not
smart enough, If you have a
396
0:20:30,96 --> 0:20:35,44
smart load balancer, by the way,
you mentioned Postgres 19 has
397
0:20:35,44 --> 0:20:37,24
a wait for LSN feature, right?
398
0:20:37,24 --> 0:20:39,3
We probably should talk about it
separately.
399
0:20:39,44 --> 0:20:41,66
I have a lot to talk about this.
400
0:20:41,76 --> 0:20:45,42
But smart load balancing understands
that some replica is lagging
401
0:20:45,9 --> 0:20:47,28
and stops using it.
402
0:20:48,7 --> 0:20:52,44
Just because we don't want to deliver
stale data to our clients.
403
0:20:52,66 --> 0:20:57,1
But if you have pretty basic load
balancing and some either queries
404
0:20:57,34 --> 0:21:2,72
or transactions went to read replica,
It takes some time because
405
0:21:3,48 --> 0:21:7,08
usually who suffers is not decision
makers like management of
406
0:21:7,08 --> 0:21:9,72
a company wanting reports, but
some users.
407
0:21:9,88 --> 0:21:14,64
And they also usually not every
user is willing to waste energy
408
0:21:14,64 --> 0:21:16,16
to report problems, right?
409
0:21:16,16 --> 0:21:18,3
It takes some time usually, that's
a problem.
410
0:21:18,82 --> 0:21:21,66
Michael: It takes time and also
you have to rule out other issues
411
0:21:21,66 --> 0:21:22,2
1st, right?
412
0:21:22,2 --> 0:21:24,44
Like you have to make sure it's
not on your side.
413
0:21:24,44 --> 0:21:27,58
Like there's just because something
seems like it's not working
414
0:21:27,64 --> 0:21:30,36
doesn't you don't automatically
assume it's a service.
415
0:21:30,9 --> 0:21:33,7
Nik: Yeah And what I'm describing
is a very common situation,
416
0:21:34,24 --> 0:21:39,66
which I went through in 2006 or
7 and just thought it's solved.
417
0:21:39,8 --> 0:21:43,58
But due to defaults, especially
defaults on managed Postgres
418
0:21:43,58 --> 0:21:48,2
platforms, which keep hot_standby_feedback
off, This is what's happening.
419
0:21:48,48 --> 0:21:54,4
So people increase max_standby_
streaming_delay and then they
420
0:21:54,4 --> 0:21:58,22
just say, okay, we have replication
lags.
421
0:21:58,22 --> 0:22:1,34
And they start thinking why, because
it's not obvious why.
422
0:22:1,56 --> 0:22:6,26
It's not really not obvious, because
yeah, it's just lags and
423
0:22:6,26 --> 0:22:6,94
so on.
424
0:22:7,66 --> 0:22:12,52
Michael: But so should we switch,
should we move to then why
425
0:22:12,52 --> 0:22:16,08
we now have hot_standby_feedback
and what it does?
426
0:22:16,1 --> 0:22:20,2
Nik: So hot_standby_feedback reports
xmin horizon observed on
427
0:22:20,2 --> 0:22:21,38
replica to the primary.
428
0:22:21,82 --> 0:22:27,02
So the primary can involve it in
calculation of the final xmin horizon
429
0:22:27,44 --> 0:22:32,12
used by Vacuum, deciding what can
be cleaned safely, what still
430
0:22:32,12 --> 0:22:33,22
cannot be cleaned.
431
0:22:33,68 --> 0:22:40,58
So if you had 1 hour query on the
primary, which led to bloat
432
0:22:41,2 --> 0:22:45,02
sometimes, right, because xmin
horizon again, global, it
433
0:22:45,02 --> 0:22:45,74
was bloat.
434
0:22:45,86 --> 0:22:50,4
And then you offloaded to read
replica without hot_standby_feedback,
435
0:22:50,4 --> 0:22:54,3
you have lags up to 1 hour because
your queries are 1 hour.
436
0:22:54,52 --> 0:22:56,02
And it's terrible lag, right?
437
0:22:57,04 --> 0:23:2,8
Or when you switch to hot_standby_feedback
to on, you have the problem
438
0:23:2,8 --> 0:23:6,14
like the horizon being reported
and you have the similar situation
439
0:23:6,22 --> 0:23:8,8
as it was executed on the primary
itself.
440
0:23:9,34 --> 0:23:10,02
Matthew 14.
441
0:23:11,12 --> 0:23:14,82
Michael: And I think the critical
thing to mention is the chance
442
0:23:14,82 --> 0:23:18,56
of you then getting conflicts are
massively reduced because the
443
0:23:18,56 --> 0:23:23,8
primary won't have cleaned up those
dead tuples and therefore
444
0:23:24,18 --> 0:23:28,94
won't send those conflicts through
to the replica until it's
445
0:23:28,94 --> 0:23:30,32
finished what it was doing.
446
0:23:30,42 --> 0:23:33,92
So it avoids that case we were
just talking about in most cases.
447
0:23:34,02 --> 0:23:35,54
Nik: That's why lag doesn't happen.
448
0:23:35,54 --> 0:23:37,18
So conflict leads to lag.
449
0:23:37,54 --> 0:23:39,38
Conflict leads to 2 things.
450
0:23:39,38 --> 0:23:43,08
1st, lag of replication, and when
it's too much lag, then cancelling
451
0:23:43,22 --> 0:23:44,62
the source of the problem.
452
0:23:44,64 --> 0:23:45,14
Query.
453
0:23:45,72 --> 0:23:46,22
Right?
454
0:23:46,7 --> 0:23:49,32
So, don't block xmin horizon Progress.
455
0:23:49,66 --> 0:23:51,2
So, you are right.
456
0:23:51,22 --> 0:23:55,64
I like that you used reduce number
of conflicts because it doesn't.
457
0:23:55,64 --> 0:23:56,14
Yeah.
458
0:23:56,82 --> 0:24:0,02
For example, we talked about the
conflict when we need to clean
459
0:24:0,02 --> 0:24:4,46
up, but tuples still needed there,
Like this is a snapshot conflict,
460
0:24:4,46 --> 0:24:6,36
but there might be also a conflict.
461
0:24:6,38 --> 0:24:9,44
When we change schema, we need
ACCESS EXCLUSIVE lock.
462
0:24:9,52 --> 0:24:13,46
And the mistake is to keep this
ACCESS EXCLUSIVE lock for long.
463
0:24:15,06 --> 0:24:18,52
And on the primary, it leads to
many problems.
464
0:24:18,52 --> 0:24:21,02
We know like some selects even
will wait.
465
0:24:21,02 --> 0:24:23,34
It's ACCESS EXCLUSIVE lock, blocks
even selects.
466
0:24:23,68 --> 0:24:24,48
Schema change.
467
0:24:24,48 --> 0:24:28,52
So if you open transaction, edit
a column very briefly, and then
468
0:24:28,52 --> 0:24:30,26
start to read something from somewhere.
469
0:24:30,48 --> 0:24:32,68
You keep the lock until very end
of transactions.
470
0:24:32,68 --> 0:24:35,96
We discussed many times lock cannot
be released midway.
471
0:24:36,02 --> 0:24:37,54
It's released only at the end.
472
0:24:37,54 --> 0:24:42,38
If you do something else, this
lock is held and nobody can work
473
0:24:42,38 --> 0:24:43,3
with that table.
474
0:24:43,32 --> 0:24:46,74
But on the replica also, if you
keep this lock on the primary.
475
0:24:47,08 --> 0:24:52,44
WAL comes saying lock and again,
lag will happen.
476
0:24:52,44 --> 0:24:54,94
And hot_standby_feedback doesn't
solve this at all.
477
0:24:55,76 --> 0:24:56,66
This should be clear.
478
0:24:56,66 --> 0:24:58,48
So yeah, you're very good warning.
479
0:24:58,48 --> 0:24:59,06
I liked it.
480
0:24:59,06 --> 0:25:0,52
It's not solving fully.
481
0:25:0,8 --> 0:25:3,7
Even more, I learned it only recently.
482
0:25:4,64 --> 0:25:9,64
So if you usually use standard
replicas, these days I used streaming
483
0:25:9,64 --> 0:25:11,62
replication plus replication slots.
484
0:25:11,68 --> 0:25:15,36
Replication slots were created
later than streaming replication.
485
0:25:15,36 --> 0:25:16,9
It's like additional level.
486
0:25:17,36 --> 0:25:21,7
And you can use streaming replication
without slots.
487
0:25:21,7 --> 0:25:26,26
Usually people think replication
slots are protection from WALs
488
0:25:26,58 --> 0:25:28,16
being deleted from the primary.
489
0:25:28,44 --> 0:25:31,78
The replica lags too much, it cannot
converge anymore, right?
490
0:25:31,92 --> 0:25:35,38
It's solved usually if you have
good backups in S3, object storage,
491
0:25:35,38 --> 0:25:39,02
you can configure restore_command
on the replica and it will
492
0:25:39,02 --> 0:25:40,84
just take WALs from there, no
problem.
493
0:25:40,84 --> 0:25:42,64
So slots can be like mitigated.
494
0:25:42,9 --> 0:25:45,26
Also slots are convenient for observability.
495
0:25:45,72 --> 0:25:49,12
But what I also learned, imagine
we have streaming replication
496
0:25:49,12 --> 0:25:49,96
without slot.
497
0:25:50,28 --> 0:25:55,24
Slots have xmin horizon reporting
right in pg_replication_slots,
498
0:25:55,24 --> 0:25:57,52
you can see it, xmin, column.
499
0:25:57,98 --> 0:26:1,1
But if you don't have slot, streaming
replication, It works all
500
0:26:1,1 --> 0:26:1,6
good.
501
0:26:1,72 --> 0:26:5,1
But then some network issue, replica
disconnected.
502
0:26:5,46 --> 0:26:8,68
On the primary, for example, vacuum
deleted tuples, reconnection
503
0:26:8,82 --> 0:26:12,48
happened, vacuum already deleted
tuples and WAL is delivered,
504
0:26:12,54 --> 0:26:13,68
tuples are deleted.
505
0:26:14,16 --> 0:26:18,28
And despite hot_standby_feedback
being on, we have situation like
506
0:26:18,28 --> 0:26:19,26
it was off.
507
0:26:20,28 --> 0:26:23,1
This was an interesting finding
for me last week when I dived
508
0:26:23,1 --> 0:26:28,14
into this topic deeper, using our
new tool for experiments, reproduced
509
0:26:28,14 --> 0:26:30,56
a lot of failure scenarios, including
this 1.
510
0:26:30,72 --> 0:26:31,76
So it's interesting.
511
0:26:32,08 --> 0:26:35,28
It means that slots are useful
in many different cases also,
512
0:26:35,28 --> 0:26:36,84
like in sensors, right?
513
0:26:37,54 --> 0:26:42,34
So yeah, hot_standby_feedback
on drastically reduce the chance
514
0:26:42,34 --> 0:26:44,56
that you will have lag on standby.
515
0:26:45,06 --> 0:26:48,68
And this is what we want for the
replicas because we want them
516
0:26:48,68 --> 0:26:51,16
to be up to date as much as possible.
517
0:26:51,98 --> 0:26:53,9
Michael: But it also does 1 other
thing that I think is quite
518
0:26:53,9 --> 0:26:54,4
important.
519
0:26:54,94 --> 0:26:58,4
It should also drastically reduce
the number of cancellations
520
0:26:58,68 --> 0:27:1,12
you get if you have these long
running queries.
521
0:27:1,4 --> 0:27:4,58
Let's say longer than 30 seconds
or whatever you set that timing
522
0:27:4,64 --> 0:27:9,44
to, it should reduce those getting
cancelled because the conflicts
523
0:27:9,44 --> 0:27:10,32
aren't going through.
524
0:27:10,32 --> 0:27:11,76
Because you're not getting conflicts
in the
525
0:27:11,76 --> 0:27:12,7
Nik: 1st place, right?
526
0:27:13,5 --> 0:27:13,9742
Right.
527
0:27:13,9742 --> 0:27:18,74
Cancellation is a result of delay,
which is a result of conflict.
528
0:27:18,8 --> 0:27:23,7
So conflict, delay or replication
lag, and then cancellation
529
0:27:23,82 --> 0:27:25,26
when it's too high.
530
0:27:25,96 --> 0:27:27,6
Value of lag is too high.
531
0:27:28,34 --> 0:27:33,14
So that's why I think read replicas
should have hot_standby_feedback
532
0:27:33,14 --> 0:27:37,52
on and reasoning that it will be
worse because they will lead
533
0:27:37,52 --> 0:27:38,04
to bloat.
534
0:27:38,04 --> 0:27:40,6
This is just how Postgres works.
535
0:27:40,6 --> 0:27:44,24
It was usually people start with
all queries going to primary,
536
0:27:44,24 --> 0:27:46,16
then they offload queries to replica.
537
0:27:46,16 --> 0:27:49,28
It's just a mistake to expect that
those queries won't affect
538
0:27:49,28 --> 0:27:50,14
vacuum behavior.
539
0:27:51,14 --> 0:27:51,88
That's it.
540
0:27:51,9 --> 0:27:54,26
Of course, you can live with off.
541
0:27:55,16 --> 0:27:56,54
Actually, 2 points here.
542
0:27:56,54 --> 0:27:58,12
1st point is quite interesting.
543
0:27:59,06 --> 0:28:7,06
So we want to minimize damage of
long-running transactions which
544
0:28:7,06 --> 0:28:10,8
block xmin horizon progress, and
how can we do it?
545
0:28:10,8 --> 0:28:16,56
We can set transaction_timeout,
both on primary and all read
546
0:28:16,56 --> 0:28:17,06
replicas.
547
0:28:17,32 --> 0:28:20,96
For example, if we set it to 3
hours, that's it.
548
0:28:21,1 --> 0:28:22,7
No more 3 hours of damage.
549
0:28:22,74 --> 0:28:25,02
But as we discussed, damage is
relative.
550
0:28:25,14 --> 0:28:28,76
3 hours, we don't know how many
transactions happened, right?
551
0:28:29,2 --> 0:28:33,34
I think actually it would make
sense, it might make sense to
552
0:28:33,34 --> 0:28:35,2
have all timeouts.
553
0:28:35,46 --> 0:28:38,4
We have transaction_timeout, we
have statement_timeout, and
554
0:28:38,4 --> 0:28:39,9
idle_in_transaction_session_timeout.
555
0:28:40,64 --> 0:28:43,22
transaction_timeout limits whole
transaction, which is good.
556
0:28:43,26 --> 0:28:47,86
But I think it might make sense
either to have all these 3 measured
557
0:28:47,9 --> 0:28:52,32
in not in milliseconds or seconds,
but in a transaction count.
558
0:28:52,86 --> 0:28:56,5
Imagine transaction_timeout, but
count of transactions, right?
559
0:28:56,74 --> 0:29:1,72
Or adjust hot_standby_feedback so
it would be not like on and
560
0:29:1,72 --> 0:29:5,72
off, but some threshold after which
we cancel.
561
0:29:6,28 --> 0:29:9,14
So there is definitely room for
improvement here.
562
0:29:9,14 --> 0:29:13,92
And if you look, my AI told me
other database systems ship settings
563
0:29:14,22 --> 0:29:14,94
for replicas.
564
0:29:15,54 --> 0:29:18,96
Postgres ships 2 extremes, on and
off.
565
0:29:20,14 --> 0:29:23,86
But with transaction_timeout, at
least measured in seconds, right?
566
0:29:23,86 --> 0:29:27,62
It's indirect, but it's already
good enough in many cases, which
567
0:29:27,62 --> 0:29:29,42
was implemented with Postgres 17.
568
0:29:30,14 --> 0:29:32,22
So on old Postgres it's not available.
569
0:29:32,78 --> 0:29:33,9
But it's good enough.
570
0:29:33,9 --> 0:29:37,62
My recommendation, on and 3 hours
transaction_timeout.
571
0:29:39,0 --> 0:29:42,42
So nobody could leave transaction
open which blocks xmin horizon
572
0:29:42,5 --> 0:29:44,08
progress for a very long time.
573
0:29:44,16 --> 0:29:46,96
But you need to do it on the primary
as well, right?
574
0:29:46,96 --> 0:29:49,14
Because who knows what happens
there.
575
0:29:49,28 --> 0:29:50,88
It's not no difference here.
576
0:29:51,74 --> 0:29:57,22
Michael: I think the big difference
is people not offloading
577
0:29:57,36 --> 0:30:0,06
just like OLTP traffic to a replica.
578
0:30:0,06 --> 0:30:3,82
I think it's people think that
I've got a replica, I can send
579
0:30:3,82 --> 0:30:7,2
my reporting or analytics queries
there or I can give a data
580
0:30:7,2 --> 0:30:11,08
team access to that and it doesn't
matter, it protects the primary.
581
0:30:11,08 --> 0:30:16,32
I think the key learning from this
setting is That's not true.
582
0:30:17,54 --> 0:30:22,02
You have to be, it can still affect
the primary and therefore
583
0:30:22,02 --> 0:30:25,7
you have to factor that in on almost
like an architectural level.
584
0:30:25,76 --> 0:30:28,78
Should you even be running those
reporting queries there if it
585
0:30:28,78 --> 0:30:32,84
on an OLTP, like an important OLTP
system or should you not do
586
0:30:32,84 --> 0:30:33,34
that?
587
0:30:33,4 --> 0:30:35,7
I think it raises those important
questions.
588
0:30:36,42 --> 0:30:36,92
Nik: Yeah.
589
0:30:37,66 --> 0:30:41,68
For reporting replicas, maybe you
should keep it off, but the
590
0:30:41,68 --> 0:30:45,46
consequences are very often replication
lags.
591
0:30:45,86 --> 0:30:49,24
And I even say that it makes like
basically this replication
592
0:30:49,28 --> 0:30:54,92
replica, like single user, like
you take around very long query,
593
0:30:54,92 --> 0:30:57,8
all other users like suffer and
cannot use it anymore.
594
0:30:57,8 --> 0:30:59,64
Like they say, oh, it's very old
data.
595
0:30:59,64 --> 0:31:0,8
We need fresh data.
596
0:31:1,28 --> 0:31:6,26
So like you become very like expensive
user, but maybe it's fine
597
0:31:6,26 --> 0:31:10,64
in some cases, like maybe it's
better to do that and then catch
598
0:31:10,64 --> 0:31:11,14
up.
599
0:31:11,64 --> 0:31:13,3
I don't know, but it's quite expensive.
600
0:31:13,94 --> 0:31:17,78
It's quite expensive to have a
whole node for reports, which,
601
0:31:17,84 --> 0:31:19,84
and this node is lagging and catches
up.
602
0:31:19,84 --> 0:31:20,42
I don't know.
603
0:31:20,42 --> 0:31:23,68
Michael: Like it's, but some, some
people do like shipping it
604
0:31:23,68 --> 0:31:24,5
to like ClickHouse.
605
0:31:24,6 --> 0:31:26,46
We did whole episodes on this,
didn't we?
606
0:31:26,46 --> 0:31:28,76
And I think there's some interesting
alternatives.
607
0:31:29,34 --> 0:31:31,8
Nik: But this is not really a replica,
it's an analytical replica,
608
0:31:31,8 --> 0:31:31,96
right?
609
0:31:31,96 --> 0:31:36,94
Or if you have different storage,
there are some, not Postgres,
610
0:31:36,94 --> 0:31:40,02
but some alternative to Postgres,
which stores data differently
611
0:31:40,12 --> 0:31:42,14
and executes queries much faster.
612
0:31:42,66 --> 0:31:46,56
In this case, maybe hot_standby_feedback
on is still good because
613
0:31:46,56 --> 0:31:48,82
negative effect to vacuum will
be low, right?
614
0:31:49,24 --> 0:31:52,08
I can imagine some cases where
off is good.
615
0:31:52,08 --> 0:31:54,06
For example, if it's a delayed
replica.
616
0:31:55,2 --> 0:31:59,54
Some people keep 8 hours, 12 hours
delayed replica, which replays
617
0:31:59,54 --> 0:32:4,84
WAL with delay 10, 12 hours to
be able to very quickly restore
618
0:32:5,38 --> 0:32:7,86
point-in-time recovery, to perform
point-in-time recovery.
619
0:32:8,14 --> 0:32:11,78
I think this recipe is quite outdated
because now we have snapshots
620
0:32:11,82 --> 0:32:15,66
and if even with lazy load, it's
quite fast to provision multi-terabyte
621
0:32:15,88 --> 0:32:18,06
databases from cloud snapshots.
622
0:32:18,9 --> 0:32:22,36
But if you use like delayed replica,
of course you don't want
623
0:32:22,36 --> 0:32:23,16
hot_standby_feedback.
624
0:32:23,16 --> 0:32:25,68
I actually think it's not possible
to use it there because hot_stand
625
0:32:25,68 --> 0:32:28,3
by_feedback works only with streaming
replication, right?
626
0:32:28,925 --> 0:32:36,5
So with archive replicas which
work using restore_command, replaying
627
0:32:36,5 --> 0:32:38,5
WALs from archive or from some place.
628
0:32:39,24 --> 0:32:41,18
hot_standby_feedback cannot be applied
there.
629
0:32:41,76 --> 0:32:45,48
And you cannot see them in replication
slots, so these replicas
630
0:32:45,48 --> 0:32:46,02
are invisible.
631
0:32:46,02 --> 0:32:48,54
And this is how delayed replicas
should work.
632
0:32:48,62 --> 0:32:51,88
And in this case, you'll be dealing
with the same mechanics,
633
0:32:52,0 --> 0:32:59,35
but defined by max_standby, not
streaming, but what the other
634
0:32:59,35 --> 0:32:59,84
alternative?
635
0:32:59,84 --> 0:33:0,34
Shipping.
636
0:33:1,08 --> 0:33:5,46
Archive delay, max_standby_archive_
delay.
637
0:33:5,46 --> 0:33:5,96
Right.
638
0:33:6,46 --> 0:33:10,3
So I think this is, this covers
quite well this topic.
639
0:33:10,4 --> 0:33:14,88
I think Postgres has opportunity
to, to improve things drastically,
640
0:33:15,04 --> 0:33:16,68
like for better control.
641
0:33:17,38 --> 0:33:22,34
I wish like I had a capability
to define the damage, not in seconds,
642
0:33:22,34 --> 0:33:28,4
but precise, like maximum, like
100000 transaction IDs, yeah.
643
0:33:29,18 --> 0:33:34,28
And if it happens, if somebody
exceeds it, I want to cancel that
644
0:33:34,28 --> 0:33:34,78
1.
645
0:33:34,92 --> 0:33:38,66
And for reporting, the most standard
approach for large databases,
646
0:33:38,86 --> 0:33:43,1
I agree with you, it remains another
database system and you
647
0:33:43,1 --> 0:33:47,46
need to create some pipeline, like
a logical replication pipeline
648
0:33:47,56 --> 0:33:51,36
to ship data to ClickHouse or anything
else like Snowflake.
649
0:33:51,82 --> 0:33:52,32
Michael: Yeah.
650
0:33:52,42 --> 0:33:52,66
Yeah.
651
0:33:52,66 --> 0:33:53,86
We could monitor for that.
652
0:33:53,86 --> 0:33:55,22
Like you can do that.
653
0:33:55,32 --> 0:33:56,98
You can build that yourself, right?
654
0:33:56,98 --> 0:34:2,5
If you monitor for things holding
back the xmin horizon and you
655
0:34:2,5 --> 0:34:5,78
can check the age in like transaction
IDs.
656
0:34:5,86 --> 0:34:9,92
So that is that's already possible
today right but we just have
657
0:34:9,92 --> 0:34:12,74
to do it ourselves rather than
a configuration parameter.
658
0:34:14,24 --> 0:34:19,86
Nik: Yeah so yeah you are talking
about some automation which
659
0:34:19,86 --> 0:34:23,14
will be outside Postgres but it
will demonstrate all 5 reasons
660
0:34:23,14 --> 0:34:25,96
for xmin horizon being blocked,
identify them and then cancel
661
0:34:25,96 --> 0:34:26,46
them.
662
0:34:26,54 --> 0:34:27,14
Then 5.
663
0:34:27,74 --> 0:34:28,24
Michael: Yeah.
664
0:34:28,66 --> 0:34:29,16
Nik: Yeah.
665
0:34:29,2 --> 0:34:32,88
You like you're talking about self-driving
ideas, but it shouldn't
666
0:34:32,88 --> 0:34:34,12
be in Postgres in my opinion.
667
0:34:34,12 --> 0:34:35,78
This is, this is the wrong example.
668
0:34:36,02 --> 0:34:40,68
This logic is too basic, too fundamental
to be, it's possible
669
0:34:40,68 --> 0:34:41,78
to implement outside.
670
0:34:42,34 --> 0:34:42,84
Definitely.
671
0:34:42,9 --> 0:34:46,56
I remember actually many times
I implemented like something in
672
0:34:46,56 --> 0:34:50,02
pg_cron or regular cron, like
canceling long running transactions,
673
0:34:50,02 --> 0:34:51,68
which are like offensive.
674
0:34:52,72 --> 0:34:54,72
Looking at backend_xmin
and so on.
675
0:34:54,72 --> 0:34:56,02
Yeah, we did it actually.
676
0:34:56,14 --> 0:34:59,0
And this is like duct taping, right?
677
0:34:59,64 --> 0:35:0,46
Michael: Yeah, sure.
678
0:35:0,92 --> 0:35:5,58
Nik: This is not, but I'm talking
like, I just feel, I feel the
679
0:35:5,58 --> 0:35:10,02
need in Postgres itself could be
improved, maybe some listener
680
0:35:10,9 --> 0:35:12,28
should propose some patches.
681
0:35:12,88 --> 0:35:15,8
Michael: I think it would be tough
to, I think this is a really
682
0:35:15,8 --> 0:35:18,14
tough 1 because it's a genuine
trade off.
683
0:35:18,24 --> 0:35:22,72
I don't feel like there's a right
solution and it's going to
684
0:35:22,72 --> 0:35:27,84
depend on what you're using your
replica for and maybe there's
685
0:35:27,84 --> 0:35:32,9
like a case that 95% of people
want and like therefore we should
686
0:35:32,9 --> 0:35:33,98
just change it.
687
0:35:34,04 --> 0:35:37,8
But it feels to me like some people
do prefer the trade-off of
688
0:35:37,8 --> 0:35:40,38
having it on and some people prefer
the trade-off of having it
689
0:35:40,38 --> 0:35:40,88
off.
690
0:35:41,18 --> 0:35:45,36
And it's maybe tricky, gonna be
hard to find something everybody's
691
0:35:45,48 --> 0:35:46,56
actually happy with.
692
0:35:47,12 --> 0:35:51,04
Nik: Yeah, actually, I think for,
yeah, this is, yeah, I think
693
0:35:51,04 --> 0:35:54,18
to leave the choice on the user
who provisions read replica,
694
0:35:54,24 --> 0:35:55,52
it is also a good idea.
695
0:35:57,72 --> 0:36:1,16
By the way, I've read, I saw many
hackers proposed to switch
696
0:36:1,16 --> 0:36:3,3
default in Postgres itself to on.
697
0:36:3,64 --> 0:36:7,0
There was such opinion, very strong
1, multiple, very well-known
698
0:36:7,02 --> 0:36:7,86
hackers proposed it.
699
0:36:7,86 --> 0:36:11,32
But then it was a discussion, it
was opposition to it.
700
0:36:11,32 --> 0:36:15,06
And by the way, at that time,
transaction_timeout didn't exist.
701
0:36:15,06 --> 0:36:17,06
So maybe now it should be reconsidered.
702
0:36:17,44 --> 0:36:17,9
Yeah.
703
0:36:17,9 --> 0:36:19,18
It was before Postgres 17.
704
0:36:19,82 --> 0:36:24,78
And the reason I remember, which
like hits in my mind brightly,
705
0:36:25,08 --> 0:36:30,22
that it was named that problems
with lags and conflicts canceled,
706
0:36:30,66 --> 0:36:33,62
canceled like lags and canceled
queries on replica.
707
0:36:34,06 --> 0:36:34,56
Yes.
708
0:36:34,94 --> 0:36:36,24
Very obvious to you.
709
0:36:36,26 --> 0:36:39,1
And it's better to discover what's
happening, understand and
710
0:36:39,1 --> 0:36:43,32
then make decision based on your
situation and experience rather
711
0:36:43,32 --> 0:36:47,7
than you don't notice or bloat.
712
0:36:48,32 --> 0:36:48,84
Bloat on
713
0:36:48,84 --> 0:36:50,88
Michael: the primary building up
slowly.
714
0:36:51,04 --> 0:36:54,02
Nik: But at the same time, the
same problem exists on the primary
715
0:36:54,02 --> 0:36:54,52
itself.
716
0:36:54,64 --> 0:36:56,14
Absolutely the same, right?
717
0:36:57,18 --> 0:36:59,42
Michael: And I think you can make
the opposite argument.
718
0:37:0,92 --> 0:37:2,88
Bloat is quieter.
719
0:37:3,22 --> 0:37:5,6
The reason you don't notice bloat
is because maybe it's not as
720
0:37:5,6 --> 0:37:5,92
bad.
721
0:37:5,92 --> 0:37:7,86
Nik: Yeah, we should control bloat
anyway.
722
0:37:8,08 --> 0:37:10,1
We should control bloat anyway,
right?
723
0:37:10,2 --> 0:37:13,48
And on the primary it happens exactly
in the same mechanism,
724
0:37:13,52 --> 0:37:17,6
so why should we make different
approach, apply different approach
725
0:37:17,6 --> 0:37:18,66
to replica.
726
0:37:18,74 --> 0:37:20,9
And bloat control is a whole topic
anyway, right?
727
0:37:20,9 --> 0:37:23,72
If you want to improve that experience,
it should be done on
728
0:37:23,72 --> 0:37:24,7
the primary 1st.
729
0:37:25,42 --> 0:37:29,14
Michael: And if we care about that
from a Postgres tuning perspective,
730
0:37:29,2 --> 0:37:32,36
we should probably be changing
the defaults of the various auto
731
0:37:32,36 --> 0:37:33,62
vacuum settings 1st.
732
0:37:33,64 --> 0:37:37,04
Nik: You know what, I like how
I'm pulling you into discussion
733
0:37:37,12 --> 0:37:37,74
and hackers.
734
0:37:37,84 --> 0:37:40,46
You should think and write your
opinion next time.
735
0:37:41,6 --> 0:37:43,24
Michael: Yeah, I'll try and get
braver.
736
0:37:44,2 --> 0:37:46,66
Nik: Yeah, it's a rabbit hole,
I know.
737
0:37:48,28 --> 0:37:48,78
Yeah.
738
0:37:49,7 --> 0:37:51,74
Michael: Anyway, this was great
and helpful.
739
0:37:51,74 --> 0:37:56,5
And I think I agree with you that
it feels like the vast majority
740
0:37:56,82 --> 0:38:1,02
of OLTP replicas where you're just
offloading a huge number of
741
0:38:1,02 --> 0:38:4,7
really quick queries, hot_stand
by_feedback On makes so much more
742
0:38:4,7 --> 0:38:6,22
sense to me than Off.
743
0:38:6,62 --> 0:38:10,28
But I can't agree that like all
read replicas should have it
744
0:38:10,28 --> 0:38:10,46
off.
745
0:38:10,46 --> 0:38:14,06
Like I do think there are these
people using it, probably still
746
0:38:14,06 --> 0:38:18,16
it's a good architectural choice
as these analytics replicas
747
0:38:18,48 --> 0:38:21,0
and I think you're right that it
makes sense to keep those off
748
0:38:21,0 --> 0:38:23,86
and it's nice that you can do it
on a per replica basis like
749
0:38:23,86 --> 0:38:26,48
it doesn't have to be the same
for all of your replicas which
750
0:38:26,48 --> 0:38:27,14
is cool.
751
0:38:27,44 --> 0:38:27,94
Yeah.
752
0:38:28,7 --> 0:38:29,2
Good.
753
0:38:29,38 --> 0:38:29,88
Awesome.
754
0:38:29,96 --> 0:38:30,78
Anything else?
755
0:38:31,1 --> 0:38:31,6
Nik: No.
756
0:38:31,88 --> 0:38:35,94
Just don't let your replica to
lag too often.
757
0:38:36,22 --> 0:38:37,04
Michael: Nice 1, Nik.
758
0:38:37,04 --> 0:38:37,9
Thanks so much.
0:0:0,06 --> 0:0:0,59999996
Nik: Hello, hello.
2
0:0:0,59999996 --> 0:0:1,92
This is Postgres.FM.
3
0:0:1,92 --> 0:0:3,84
My name is Nik, PostgresAI.
4
0:0:3,84 --> 0:0:7,3199997
And as usual with me, Michael pgMustard.
5
0:0:7,68 --> 0:0:8,5
Hi, Michael.
6
0:0:9,48 --> 0:0:10,42
Michael: Hello, Nik.
7
0:0:10,9 --> 0:0:13,82
Nik: I proposed to discuss hot_standby_feedback.
8
0:0:14,599999 --> 0:0:18,04
And that's interesting because
I have 4 customers with this problem
9
0:0:18,04 --> 0:0:22,26
right now, different on different
platforms, right?
10
0:0:22,26 --> 0:0:22,66
Yeah.
11
0:0:22,66 --> 0:0:25,56
On Supabase on RDS and something
else.
12
0:0:25,56 --> 0:0:30,72
It's very interesting how problems
sometimes are very silent
13
0:0:30,86 --> 0:0:33,98
until suddenly they come from multiple
directions.
14
0:0:34,04 --> 0:0:34,54
Right.
15
0:0:34,64 --> 0:0:37,94
I don't know why it's happening,
right, but it's just happening.
16
0:0:38,739998 --> 0:0:41,86
Michael: All 4, are they all suffering
from the same direction?
17
0:0:42,559998 --> 0:0:43,059998
Yeah.
18
0:0:43,22 --> 0:0:44,02
Okay, interesting.
19
0:0:45,06 --> 0:0:48,4
Nik: So all of them are complaining
about read replicas lagging.
20
0:0:50,58 --> 0:0:55,38
And it's all because platforms,
most platforms keep hot_standby_feedback
21
0:0:55,38 --> 0:0:59,48
off by default, like Postgres
itself has it off by default.
22
0:1:1,56 --> 0:1:8,38
While my honest opinion, like strong
opinion, I will say, is
23
0:1:8,38 --> 0:1:10,88
that you cannot have read replicas
with off.
24
0:1:11,4 --> 0:1:18,28
For general OLTP case with many
users, And I say this after a
25
0:1:18,28 --> 0:1:19,26
lot of experience.
26
0:1:19,94 --> 0:1:26,0
I built 3 social networks with
each 1 of them achieved many million
27
0:1:26,0 --> 0:1:29,88
users and 1 plus DAU daily active
users.
28
0:1:30,56 --> 0:1:32,84
And we needed to scale reads of
course, right?
29
0:1:32,84 --> 0:1:34,58
So we needed read replicas.
30
0:1:35,58 --> 0:1:37,24
1st 1 I built in 2006.
31
0:1:38,92 --> 0:1:42,84
And also I helped many companies
who also needed to scale reads.
32
0:1:43,52 --> 0:1:46,5
My honest opinion, you cannot have
read replica with hot_standby_feedback
33
0:1:46,5 --> 0:1:49,58
off, just because
it will start lagging.
34
0:1:50,22 --> 0:1:51,6
But this is how it is.
35
0:1:52,36 --> 0:1:56,14
All people need to rediscover it
because defaults are...
36
0:1:56,4 --> 0:1:58,14
This is our favorite topic, right?
37
0:1:58,14 --> 0:2:1,9
Defaults are terrible in many aspects.
38
0:2:3,1 --> 0:2:6,46
Michael: Well, yeah, I'm actually
not sure I fully agree on this
39
0:2:6,46 --> 0:2:6,96
1 yet.
40
0:2:6,96 --> 0:2:7,8
Maybe I will by the end.
41
0:2:7,8 --> 0:2:8,76
Nik: You are not alone.
42
0:2:9,96 --> 0:2:11,44
Michael: Yeah, I'm not sure.
43
0:2:11,94 --> 0:2:16,9
It's a really difficult 1, I think,
this 1 in terms of like risks
44
0:2:16,92 --> 0:2:20,44
versus, yeah, when things bite
you.
45
0:2:20,44 --> 0:2:22,54
But let's, let's, maybe let's go
back to basics.
46
0:2:22,54 --> 0:2:23,42
Like what's the…
47
0:2:23,42 --> 0:2:24,38
Nik: Yeah, yeah, yeah.
48
0:2:24,38 --> 0:2:28,94
So we should discuss, and I just
threw it in the, at the very
49
0:2:28,94 --> 0:2:30,6
beginning to set my goal.
50
0:2:30,6 --> 0:2:35,58
I'm going to discuss it in detail
and explain my way of thinking.
51
0:2:36,1 --> 0:2:39,92
But just separately, I'm just wondering,
are there people who
52
0:2:39,92 --> 0:2:46,0
really scaled reads on really large
web or mobile apps and had
53
0:2:46,0 --> 0:2:49,64
it off because I just don't understand
how it's, how it can be
54
0:2:49,64 --> 0:2:52,28
possible unless you, you are okay.
55
0:2:52,28 --> 0:2:54,86
Your application is okay with lagging
replicas.
56
0:2:54,96 --> 0:3:0,32
Sometimes it's so if you serve
traffic to some users and some
57
0:3:0,32 --> 0:3:3,9
stale information, like lagging
some seconds or maybe minutes
58
0:3:3,9 --> 0:3:4,62
is fine.
59
0:3:5,02 --> 0:3:9,12
I can imagine that you basically
show some news for example or
60
0:3:9,12 --> 0:3:10,52
blog posts or something.
61
0:3:10,68 --> 0:3:15,58
But in general, modern applications
like social media applications,
62
0:3:15,82 --> 0:3:20,24
e-commerce and so on Where you
want to scale reads to asynchronous
63
0:3:20,38 --> 0:3:23,3
replicas, of course we talk about
asynchronous replicas here.
64
0:3:24,12 --> 0:3:27,42
Then we do need hot_standby_feedback
to be on.
65
0:3:28,52 --> 0:3:30,4
Anyway, let's start from the basics.
66
0:3:30,44 --> 0:3:34,62
We had several episodes where we
covered basics already.
67
0:3:34,64 --> 0:3:36,56
I just wanted to have a quick recap.
68
0:3:37,2 --> 0:3:40,3
1st of all, my favorite thing,
if some people don't realize,
69
0:3:40,38 --> 0:3:42,88
there are hidden columns, xmin,
xmax.
70
0:3:43,38 --> 0:3:47,52
xmin is a transaction ID which
gave birth to this tuple.
71
0:3:47,54 --> 0:3:49,4
Tuple is a row version, right?
72
0:3:49,74 --> 0:3:53,76
And there is a concept of xmin horizon,
and we had a separate
73
0:3:53,76 --> 0:4:0,22
episode about it, which defines
global for whole that database.
74
0:4:0,28 --> 0:4:6,74
Global horizon, which says this
is the oldest transaction ID that
75
0:4:6,74 --> 0:4:11,18
defines data snapshot, which is
still needed to some transactions,
76
0:4:12,26 --> 0:4:13,04
some clients.
77
0:4:13,1 --> 0:4:13,6
Right.
78
0:4:14,06 --> 0:4:17,62
And it means, And this is global,
a single value basically.
79
0:4:17,74 --> 0:4:21,54
There are many values coming from
different sources, but horizon
80
0:4:21,66 --> 0:4:24,26
is absolute minimum of all of them.
81
0:4:25,52 --> 0:4:30,06
And it defines the garbage collection
behavior, vacuum behavior,
82
0:4:30,06 --> 0:4:30,56
right?
83
0:4:30,78 --> 0:4:36,98
Because when DELETE or UPDATE or
canceled INSERT happens, dead
84
0:4:36,98 --> 0:4:38,52
tuples are produced, right?
85
0:4:38,52 --> 0:4:41,18
And the job is not done fully.
86
0:4:41,2 --> 0:4:43,76
The remaining of the job is garbage
collection.
87
0:4:44,06 --> 0:4:46,08
Dead tuple must be deleted later.
88
0:4:46,86 --> 0:4:52,84
And if this dead tuple belongs
to a transaction, which is newer
89
0:4:53,16 --> 0:4:55,38
than our xmin horizon, Vacuum cannot
delete it.
90
0:4:55,38 --> 0:4:56,48
And this is simple logic.
91
0:4:56,48 --> 0:4:57,22
It's global.
92
0:4:57,44 --> 0:4:59,88
Some people think it's maybe per
table, but it's not.
93
0:4:59,88 --> 0:5:0,56
It's global.
94
0:5:1,4 --> 0:5:1,9
Michael: Yeah.
95
0:5:2,04 --> 0:5:6,1
And this is all very simple on
a single node.
96
0:5:6,1 --> 0:5:10,24
Just if you're only worried about
having a primary database or
97
0:5:10,24 --> 0:5:16,26
maybe only an HA replica, no, it's
nice and simple, right?
98
0:5:16,26 --> 0:5:19,68
Like we just hold on to the very
versions that might be needed
99
0:5:19,68 --> 0:5:20,58
by the oldest.
100
0:5:22,24 --> 0:5:27,48
Query or whatever the process might
need it and then once that's
101
0:5:27,48 --> 0:5:27,98
finished.
102
0:5:28,48 --> 0:5:31,72
We do the cleanup or vacuum does
it right
103
0:5:31,72 --> 0:5:33,84
Nik: And here we need to dive into
some details.
104
0:5:33,84 --> 0:5:37,4
So yeah, let's 1st think about
only the primary and single node
105
0:5:37,44 --> 0:5:40,1
situation where usually people
start.
106
0:5:40,52 --> 0:5:45,24
You have, there are 5 reasons,
5 sources and our monitoring covers
107
0:5:45,24 --> 0:5:45,74
them.
108
0:5:45,78 --> 0:5:48,36
And we discussed it in the xmin horizon
episode.
109
0:5:48,94 --> 0:5:51,68
5 reasons to block xmin horizon.
110
0:5:52,2 --> 0:5:56,08
Normally if all transactions are
fast, they finish promptly,
111
0:5:57,56 --> 0:6:1,88
no replication is used, neither
logical nor physical, and you
112
0:6:1,88 --> 0:6:4,14
don't use prepared transactions.
113
0:6:5,64 --> 0:6:10,46
In this case, xmin horizon flows
naturally, and you can calculate
114
0:6:10,68 --> 0:6:14,54
age of xmin horizon, meaning that
how many transaction IDs, real
115
0:6:14,54 --> 0:6:19,2
transaction IDs passed since this
horizon and ideally it's very
116
0:6:19,2 --> 0:6:19,54
low.
117
0:6:19,54 --> 0:6:21,36
Say thousands is quite low.
118
0:6:21,88 --> 0:6:23,32
10, 000 is quite low.
119
0:6:23,32 --> 0:6:25,06
1 million it's already noticeable.
120
0:6:25,14 --> 0:6:25,64
Right.
121
0:6:26,38 --> 0:6:29,8
And we know, we all know that 1
billion is already basically
122
0:6:29,8 --> 0:6:35,82
0.5 of capacity we have because
if the whole capacity is 2.1
123
0:6:35,98 --> 0:6:37,2
billion roughly, right?
124
0:6:37,7 --> 0:6:45,14
We have 4-byte integer transaction
IDs, and 0.5 of that space
125
0:6:45,56 --> 0:6:50,92
is our future, 0.5 of it is our
past, and we need freezing vacuum.
126
0:6:51,04 --> 0:6:58,04
1 of its jobs is to freeze tuples
saying that don't look at this
127
0:6:58,04 --> 0:7:1,74
xmin, it looks like from future,
but it's from the past, right?
128
0:7:2,22 --> 0:7:9,02
And if we don't freeze during 2.1,
transaction ID, if our xmin horizon
129
0:7:9,84 --> 0:7:13,22
is that old, age of it is that
old.
130
0:7:13,22 --> 0:7:15,32
We can use function age, actually,
right?
131
0:7:15,58 --> 0:7:19,06
In this case, it means that we're
going to be in big trouble
132
0:7:19,06 --> 0:7:22,48
and database will be not accessible
because transaction ID wraparound
133
0:7:22,72 --> 0:7:23,5
will happen.
134
0:7:24,24 --> 0:7:27,8
This is the biggest danger actually
of xmin horizon being blocked
135
0:7:27,8 --> 0:7:28,88
for a long time.
136
0:7:29,44 --> 0:7:33,48
And In the case of single node
of the primary, everything is
137
0:7:33,48 --> 0:7:39,18
relatively simple, but I noticed
many people don't realize nuances
138
0:7:39,44 --> 0:7:39,94
here.
139
0:7:40,24 --> 0:7:45,48
Remember this year I started saying
that the warning, let's avoid
140
0:7:45,48 --> 0:7:48,84
long running transactions is quite
inaccurate.
141
0:7:49,64 --> 0:7:50,14
Right.
142
0:7:50,14 --> 0:7:52,44
Remember this, we're talking about
this.
143
0:7:52,44 --> 0:7:56,1
And I noticed actually, even AI
is making a lot of mistakes.
144
0:7:56,12 --> 0:8:2,1
For example, with GPT-6 Astra,
I decided to renew graphics in
145
0:8:2,1 --> 0:8:6,3
PGSimCity and also make it
more entertainable and have some
146
0:8:6,56 --> 0:8:10,14
bad scenarios to be reproduced
and so you could see what happens.
147
0:8:10,6 --> 0:8:14,54
And it wrote a very simple thing
and I see it quite often.
148
0:8:15,24 --> 0:8:20,54
It said somebody typed BEGIN and
left transaction open for a
149
0:8:20,54 --> 0:8:22,16
day and we're in trouble.
150
0:8:22,2 --> 0:8:25,46
But it's not simple BEGIN is not
a trouble.
151
0:8:25,68 --> 0:8:29,82
Because by default we have read
committed transaction isolation.
152
0:8:30,4 --> 0:8:35,84
Which means that after each statement
snapshot is free to go.
153
0:8:35,88 --> 0:8:38,1
Basically we don't block xmin for
ever.
154
0:8:38,1 --> 0:8:41,68
So if we think about transaction
at default transaction isolation
155
0:8:41,68 --> 0:8:46,28
level, if it consists of multiple
statements, while each statement
156
0:8:46,28 --> 0:8:49,98
lasts, of course we need that snapshot
and we block xmin horizon.
157
0:8:50,2 --> 0:8:51,8
We need to work with that data.
158
0:8:51,88 --> 0:8:53,96
Some SELECT is running on some
huge table.
159
0:8:53,96 --> 0:8:57,04
Yeah, it lasts for long and we
need that data.
160
0:8:57,04 --> 0:9:1,98
But once this SELECT finishes,
Even if transaction is still open,
161
0:9:2,9 --> 0:9:4,4
that snapshot is not needed.
162
0:9:4,4 --> 0:9:7,54
And basically we allow xmin horizon
to shift.
163
0:9:9,06 --> 0:9:13,38
And Simple BEGIN doesn't hold any
problem, any snapshot at all,
164
0:9:13,38 --> 0:9:13,88
right?
165
0:9:15,28 --> 0:9:17,02
At default isolation level.
166
0:9:18,22 --> 0:9:22,2
So yeah, it's very inaccurate and
that's why saying, oh, long
167
0:9:22,2 --> 0:9:24,34
running transactions are dangerous.
168
0:9:24,78 --> 0:9:26,54
It's too rough.
169
0:9:26,76 --> 0:9:31,2
It's not a precise statement because
not every long running transaction
170
0:9:31,34 --> 0:9:31,84
is.
171
0:9:31,84 --> 0:9:32,34
Yes.
172
0:9:33,72 --> 0:9:37,54
Michael: Some are of course, but
not quite all right.
173
0:9:37,54 --> 0:9:39,52
There are lots of cases where they're
not.
174
0:9:40,28 --> 0:9:40,52
Yeah, sure.
175
0:9:40,52 --> 0:9:41,02
Nik: Right.
176
0:9:41,14 --> 0:9:44,04
Of course, we don't talk about
locks here because there are 2
177
0:9:44,04 --> 0:9:49,78
topics, locks and xmin horizon
blockage, 2 dangers of long-running
178
0:9:49,78 --> 0:9:50,28
transactions.
179
0:9:50,5 --> 0:9:56,42
So we need to distinguish read
committed transactions and transactions
180
0:9:56,54 --> 0:10:1,86
running at higher isolation level,
repeatable read and serializable.
181
0:10:3,18 --> 0:10:6,68
And repeatable read is pretty common
because this is how pg_dump
182
0:10:6,68 --> 0:10:7,18
works.
183
0:10:7,9 --> 0:10:11,94
So every time you run pg_dump, even
if it's not doing, of course
184
0:10:11,94 --> 0:10:14,96
it's doing something always, right,
but it captures snapshot
185
0:10:14,96 --> 0:10:18,56
in the very beginning of transaction,
And even if you dump a
186
0:10:18,56 --> 0:10:22,26
lot of tables, you select from
them, the pg_dump selects from
187
0:10:22,26 --> 0:10:25,12
them, it shifts to the next table.
188
0:10:25,52 --> 0:10:29,6
Snapshot is still the same as it
was defined at the very beginning
189
0:10:29,6 --> 0:10:30,36
of transaction.
190
0:10:30,64 --> 0:10:34,64
It means if you start a transaction
at repeatable read, that's
191
0:10:34,64 --> 0:10:36,26
enough to be harmful.
192
0:10:36,98 --> 0:10:40,3
You just say START TRANSACTION
or BEGIN at repeatable isolation
193
0:10:40,38 --> 0:10:40,76
level.
194
0:10:40,76 --> 0:10:42,14
I always forget syntax.
195
0:10:43,52 --> 0:10:46,74
You don't need even to run any
queries inside it.
196
0:10:46,74 --> 0:10:48,02
It's already harmful.
197
0:10:48,74 --> 0:10:49,44
It holds.
198
0:10:49,54 --> 0:10:52,54
Michael: You say harmful, it starts
to hold the xmin horizon,
199
0:10:52,54 --> 0:10:53,04
right?
200
0:10:53,68 --> 0:10:55,12
It affects, yeah.
201
0:10:55,32 --> 0:10:57,74
Short holds are not expected, right?
202
0:10:58,18 --> 0:11:0,68
The reason it's being held is for
a good reason.
203
0:11:1,08 --> 0:11:6,88
If you're doing a dump, you want
probably everything, not just
204
0:11:6,88 --> 0:11:10,24
consistency, but you want the data
at that point in time, and
205
0:11:10,24 --> 0:11:14,12
if it's changed, then you want,
like you still need those, you
206
0:11:14,12 --> 0:11:16,12
can't have those rows being cleaned
up.
207
0:11:16,16 --> 0:11:18,06
Nik: So you don't need to pay…
IM – This is how consistency is
208
0:11:18,06 --> 0:11:18,94
defined, right?
209
0:11:18,94 --> 0:11:19,36
TG –
210
0:11:19,36 --> 0:11:20,15
Michael: Yeah, yeah.
211
0:11:20,15 --> 0:11:20,36
IM
212
0:11:20,36 --> 0:11:23,36
Nik: – Because if you read from
2 tables from different points
213
0:11:23,36 --> 0:11:25,36
of time, this is inconsistent dump.
214
0:11:25,46 --> 0:11:26,82
So we don't want that.
215
0:11:27,1 --> 0:11:29,12
That's why we need a repeatable read.
216
0:11:29,34 --> 0:11:30,64
Michael: But you say it's harmful.
217
0:11:30,66 --> 0:11:34,74
It just means we're holding on
to a bit of extra data maybe bloating
218
0:11:34,74 --> 0:11:39,44
some indexes a bit like there's
a an amount of that we should
219
0:11:39,44 --> 0:11:42,18
always expect when we're running
Postgres right like it there's
220
0:11:42,18 --> 0:11:45,48
a certain amount of that will always
be natural we try and minimize
221
0:11:45,48 --> 0:11:47,68
it and we try not to let it get
excessive.
222
0:11:49,46 --> 0:11:54,52
But then if that goes on for too
long, it gets into that boundary
223
0:11:54,52 --> 0:11:57,76
where it is maybe excessive or
causing other issues.
224
0:11:57,88 --> 0:11:58,98
Is that what we're trying to say?
225
0:11:58,98 --> 0:12:3,84
So like harmful, yes, but like
it, it scales with the amount
226
0:12:3,84 --> 0:12:8,86
of time or the maybe like time
multiplied by the amount of churn
227
0:12:8,86 --> 0:12:9,52
in that time.
228
0:12:9,52 --> 0:12:9,84
Nik: Yeah.
229
0:12:9,84 --> 0:12:9,96
Yeah.
230
0:12:9,96 --> 0:12:10,24
Yeah.
231
0:12:10,24 --> 0:12:10,74
Database.
232
0:12:11,74 --> 0:12:16,52
I usually say you need like, it's
really hard to understand age
233
0:12:16,52 --> 0:12:20,4
and number of transactions, although
it's more representative
234
0:12:20,66 --> 0:12:22,16
metric in this context.
235
0:12:22,66 --> 0:12:26,16
If you say like long transaction
running like 3 hours during
236
0:12:26,16 --> 0:12:31,02
busy time on like at high traffic
is not the same as during weekend
237
0:12:31,02 --> 0:12:31,96
with low traffic, right?
238
0:12:31,96 --> 0:12:35,42
Because it might be even nothing
happened during 3 hours at weekend.
239
0:12:35,42 --> 0:12:40,04
Sometimes we see some applications
with very acute spikes of
240
0:12:40,04 --> 0:12:40,98
traffic, right?
241
0:12:41,12 --> 0:12:46,06
But, but so it's, there is no like
very clear connection between
242
0:12:46,06 --> 0:12:50,36
time and I, I name it XID consumption
or XID growth rate.
243
0:12:50,74 --> 0:12:55,9
So how fast real transaction ID
grows because this is what defines
244
0:12:56,1 --> 0:12:57,82
this xmin horizon age.
245
0:12:57,94 --> 0:13:0,56
And 1 of the contributors by the
way is savepoints, sub-transactions.
246
0:13:0,86 --> 0:13:5,28
If you use them a lot in 1 transaction,
you consume many real
247
0:13:5,28 --> 0:13:9,14
transaction IDs, because every
savepoint gets allocation of
248
0:13:9,14 --> 0:13:10,62
XID, real XID.
249
0:13:11,0 --> 0:13:15,8
So this is 1 of the dangers I identified,
that savepoints lead
250
0:13:15,8 --> 0:13:17,62
to higher consumption of XIDs.
251
0:13:18,68 --> 0:13:22,66
So yeah, and yeah, if it, of course,
like if we block a vacuum,
252
0:13:22,66 --> 0:13:26,82
it also, we need to be very accurate
with language here because
253
0:13:27,18 --> 0:13:30,52
I realized also when we say vacuum
is blocked, some people think
254
0:13:30,7 --> 0:13:32,28
it's blocked, it like fails.
255
0:13:32,78 --> 0:13:36,92
I even saw, so I have a new tool
to be released soon called PGBS
256
0:13:37,02 --> 0:13:41,5
detector, which is going to, and
we started using it internally
257
0:13:41,54 --> 0:13:46,48
to identify bullshit in our write-ups
and I saw RDS article from
258
0:13:46,48 --> 0:13:50,12
2019 and this was the only article
talking about hot_standby_feedback
259
0:13:50,12 --> 0:13:51,44
from RDS.
260
0:13:52,24 --> 0:13:53,08
Nothing else.
261
0:13:53,48 --> 0:13:56,92
So I tried to understand why they
keep default off and what's
262
0:13:56,92 --> 0:13:58,38
the reasoning behind it.
263
0:13:58,52 --> 0:14:4,14
So I saw that in that article,
this like very confusing statement,
264
0:14:4,28 --> 0:14:9,9
like vacuum is blocked and then
error like canceled statement,
265
0:14:10,2 --> 0:14:11,98
like it was vacuum statement canceled.
266
0:14:11,98 --> 0:14:17,38
No, when we say xmin horizon blockage
leads to affects vacuum
267
0:14:17,38 --> 0:14:22,48
negatively, It's not like vacuum
fails or cannot work at all.
268
0:14:22,48 --> 0:14:23,44
It works partially.
269
0:14:23,76 --> 0:14:29,24
It cleans up the dead tuples which
became dead before xmin horizon.
270
0:14:30,06 --> 0:14:34,24
And it just keeps and reports it
in the logs, tuples which it
271
0:14:34,24 --> 0:14:37,96
cannot clean as dead, already dead
but cannot be cleaned up yet.
272
0:14:37,96 --> 0:14:38,8
I don't remember.
273
0:14:38,8 --> 0:14:40,24
It cannot be removed, cannot be
deleted.
274
0:14:40,24 --> 0:14:41,34
I don't remember exactly.
275
0:14:42,04 --> 0:14:45,06
But this is what's useful to see
because you can see for each
276
0:14:45,06 --> 0:14:49,64
table, a vacuum visited, autovacuum
visited, you can see what
277
0:14:49,64 --> 0:14:51,74
is the damage for this.
278
0:14:52,08 --> 0:14:56,52
You can also run VACUUM VERBOSE
and VERBOSE will report such
279
0:14:56,52 --> 0:15:0,18
tuples which are dead, but cannot
be deleted because it's xmin horizon.
280
0:15:0,28 --> 0:15:2,72
It actually also reports xmin horizon.
281
0:15:3,34 --> 0:15:7,44
It just prints the transaction
ID and what's the age of it, which
282
0:15:7,44 --> 0:15:8,74
is very useful as well.
283
0:15:8,74 --> 0:15:11,4
So that's why autovacuum logging
should be enabled.
284
0:15:12,38 --> 0:15:16,22
And maybe not 0, which means all
occurrences of autovacuum,
285
0:15:16,32 --> 0:15:21,06
but maybe at least above a few
seconds, 10 seconds or 1 second,
286
0:15:21,34 --> 0:15:26,92
if you're concerned about some
very often executions, which are
287
0:15:26,92 --> 0:15:27,74
very fast.
288
0:15:27,8 --> 0:15:31,3
So anyway, autovacuum logs are
quite useful in this area.
289
0:15:31,88 --> 0:15:32,72
What's next?
290
0:15:33,26 --> 0:15:36,1
Michael: I think we've not really
even discussed what hot_stand
291
0:15:36,22 --> 0:15:37,58
by_feedback does.
292
0:15:37,68 --> 0:15:43,32
We've talked about the retention
of dead tuples while like something's
293
0:15:43,32 --> 0:15:48,26
happening, but hot_standby_feedback
comes in because it's something
294
0:15:48,26 --> 0:15:49,9
happening not on the primary, right?
295
0:15:49,9 --> 0:15:50,28
Like it's
296
0:15:50,28 --> 0:15:52,12
Nik: something happening on your
replica.
297
0:15:52,12 --> 0:15:53,54
Yeah, it's conflicts.
298
0:15:53,8 --> 0:15:57,54
So if you think how replication
works, physical replication,
299
0:15:57,56 --> 0:16:0,06
it replays WAL like it was recovery.
300
0:16:0,06 --> 0:16:3,48
It was built on top of recovery
from crashes, right?
301
0:16:3,82 --> 0:16:6,96
And the hot_standby_feedback is
just constantly replaying WAL
302
0:16:6,96 --> 0:16:8,3
like it was a recovery.
303
0:16:8,88 --> 0:16:13,34
That's why a function to check
is it primary or standby is called
304
0:16:13,34 --> 0:16:15,36
pg_is_in_recovery, which is confusing.
305
0:16:15,54 --> 0:16:16,04
Right?
306
0:16:16,68 --> 0:16:17,54
And it replays.
307
0:16:17,54 --> 0:16:18,16
WAL, good.
308
0:16:18,16 --> 0:16:22,58
But what if we have some query
ongoing, selecting from a huge
309
0:16:22,58 --> 0:16:24,06
table, for example, reporting,
right?
310
0:16:24,06 --> 0:16:24,96
It's pretty common.
311
0:16:24,96 --> 0:16:28,48
Let's offload heavy reporting queries
to read replicas.
312
0:16:29,24 --> 0:16:34,52
If it's running there and WAL has
instruction to clean up dead
313
0:16:34,66 --> 0:16:37,5
tuples which on the primary already
dead but we still reading
314
0:16:37,5 --> 0:16:38,0
them.
315
0:16:38,72 --> 0:16:42,76
This is 1 of conflicts and this
is snapshot conflict of replication
316
0:16:42,78 --> 0:16:47,54
conflicts with a query or query
or transaction conflicts with
317
0:16:47,54 --> 0:16:50,26
this WAL data right It cannot be
replayed.
318
0:16:50,94 --> 0:16:56,4
So depending on the type of physical
standby, 1 of 2 settings
319
0:16:56,68 --> 0:16:59,78
are used to decide what to do.
320
0:16:59,86 --> 0:17:1,72
So what to do is obvious.
321
0:17:2,08 --> 0:17:3,54
Replication is paused.
322
0:17:4,2 --> 0:17:5,26
It cannot be replayed.
323
0:17:7,42 --> 0:17:8,6
This is default option.
324
0:17:9,82 --> 0:17:13,04
So we wait until the end of this
transaction.
325
0:17:14,1 --> 0:17:15,78
Michael: And then we continue replaying.
326
0:17:17,22 --> 0:17:19,88
Nik: We wait until xmin horizon
shifts.
327
0:17:20,58 --> 0:17:22,54
If it shifts, we can replay it.
328
0:17:23,36 --> 0:17:26,86
So you can see it manually in pg_stat_
activity backend_xmin
329
0:17:27,26 --> 0:17:27,76
column.
330
0:17:28,14 --> 0:17:35,44
And also it's, yeah, So backend_
xmin is for each backend, you
331
0:17:35,44 --> 0:17:41,12
can see this local horizon for
each backend and minimum of them
332
0:17:41,12 --> 0:17:43,16
is our horizon for this node.
333
0:17:44,02 --> 0:17:46,74
It waits, it waits not forever.
334
0:17:47,62 --> 0:17:53,82
There is a setting I believe it's
called max_standby_streaming_
335
0:17:54,48 --> 0:17:54,98
delay.
336
0:17:55,86 --> 0:17:56,36
Yes,
337
0:17:56,74 --> 0:17:59,54
Michael: there's max_standby_streaming_
delay and max_standby_
338
0:17:59,54 --> 0:18:0,54
archive_delay.
339
0:18:0,72 --> 0:18:2,84
Nik: I will never remember all
GUCs.
340
0:18:2,84 --> 0:18:3,34
Yeah.
341
0:18:3,46 --> 0:18:6,76
So yeah, there are 2, 1 for streaming,
1 for WAL replay.
342
0:18:6,86 --> 0:18:10,74
Replicas which are consuming WALs
from archive, like S3 archive
343
0:18:10,74 --> 0:18:13,4
or something, according to restore_
command.
344
0:18:13,58 --> 0:18:17,44
And, but more common because they
have smaller lags is streaming
345
0:18:17,44 --> 0:18:22,36
replication, which connects to
the primary, unless we use cascade
346
0:18:22,36 --> 0:18:26,6
replication, and just streams the
WALs and applies them.
347
0:18:26,82 --> 0:18:29,98
And this is when we use max_standby_
streaming_delay.
348
0:18:30,86 --> 0:18:32,72
By default, I think it's 30 seconds.
349
0:18:33,54 --> 0:18:35,42
It waits a maximum of 30 seconds.
350
0:18:36,22 --> 0:18:42,04
After this, Postgres says replication
is more important and just
351
0:18:42,04 --> 0:18:43,72
cancels the query.
352
0:18:44,54 --> 0:18:47,76
And the 1st thing that happens,
people don't like queries being
353
0:18:47,76 --> 0:18:48,48
canceled, right?
354
0:18:48,48 --> 0:18:51,24
Or important reports.
355
0:18:51,6 --> 0:18:53,94
Michael: Especially sometimes long
ones, like if it's a long
356
0:18:53,94 --> 0:18:57,44
reporting query, you've already
waited 10 minutes for it.
357
0:18:57,44 --> 0:18:57,66
Nik: Right.
358
0:18:57,66 --> 0:19:1,26
And that's very visible because
reporting usually is close to
359
0:19:1,26 --> 0:19:3,74
decision makers, So they start
complaining very fast.
360
0:19:3,74 --> 0:19:5,64
Like, why report was not delivered?
361
0:19:5,64 --> 0:19:7,68
Okay, we have conflicts.
362
0:19:7,7 --> 0:19:8,2
Canceled.
363
0:19:8,46 --> 0:19:13,08
Okay, let's increase this to 3
minutes, 30 minutes, 3 hours a
364
0:19:13,08 --> 0:19:13,58
day.
365
0:19:14,44 --> 0:19:15,34
And it's okay.
366
0:19:15,9 --> 0:19:16,4
Yeah.
367
0:19:16,94 --> 0:19:18,96
At extreme, you can set minus 1.
368
0:19:19,62 --> 0:19:23,98
And so replication will wait until,
369
0:19:24,28 --> 0:19:24,78
Michael: yeah.
370
0:19:25,32 --> 0:19:26,04
So what happens now?
371
0:19:26,04 --> 0:19:28,38
And this whole time, I think it's
really important to stress,
372
0:19:28,38 --> 0:19:33,28
this whole time anything newer
than that conflict is not getting
373
0:19:33,28 --> 0:19:35,4
replayed on the replica.
374
0:19:35,46 --> 0:19:39,96
So no, any new inserts, any updates,
any deletes, nothing's coming
375
0:19:39,96 --> 0:19:40,46
through
376
0:19:40,96 --> 0:19:42,44
Nik: until that query finishes.
377
0:19:42,44 --> 0:19:43,055
Yeah, it's global.
378
0:19:43,055 --> 0:19:43,84
Horizon was global.
379
0:19:43,94 --> 0:19:46,42
Replication is just single process
actually.
380
0:19:46,72 --> 0:19:49,58
You can see a startup process in
top or ps.
381
0:19:50,02 --> 0:19:51,94
And this is what applies changes.
382
0:19:52,2 --> 0:19:55,76
By the way, changes might be received
already because the replication
383
0:19:55,76 --> 0:19:56,5
has stages.
384
0:19:56,68 --> 0:20:0,06
They can be flushed to local pg_wal
directory, but they just
385
0:20:0,06 --> 0:20:3,82
cannot be applied because somebody
needs very old, all the data,
386
0:20:4,0 --> 0:20:6,86
which we already want to clean
up.
387
0:20:7,8 --> 0:20:8,3
Right.
388
0:20:8,8 --> 0:20:11,92
So what happens next?
389
0:20:12,5 --> 0:20:13,54
Reports work.
390
0:20:13,62 --> 0:20:17,64
For example, we set 3 hours, All
reports last no more than 1
391
0:20:17,64 --> 0:20:18,12
hour.
392
0:20:18,12 --> 0:20:18,98
We are good.
393
0:20:19,74 --> 0:20:25,06
But then, usually what happens,
some users started to complain.
394
0:20:25,24 --> 0:20:26,1
Stale data.
395
0:20:27,74 --> 0:20:30,96
And if your load balancer is not
smart enough, If you have a
396
0:20:30,96 --> 0:20:35,44
smart load balancer, by the way,
you mentioned Postgres 19 has
397
0:20:35,44 --> 0:20:37,24
a wait for LSN feature, right?
398
0:20:37,24 --> 0:20:39,3
We probably should talk about it
separately.
399
0:20:39,44 --> 0:20:41,66
I have a lot to talk about this.
400
0:20:41,76 --> 0:20:45,42
But smart load balancing understands
that some replica is lagging
401
0:20:45,9 --> 0:20:47,28
and stops using it.
402
0:20:48,7 --> 0:20:52,44
Just because we don't want to deliver
stale data to our clients.
403
0:20:52,66 --> 0:20:57,1
But if you have pretty basic load
balancing and some either queries
404
0:20:57,34 --> 0:21:2,72
or transactions went to read replica,
It takes some time because
405
0:21:3,48 --> 0:21:7,08
usually who suffers is not decision
makers like management of
406
0:21:7,08 --> 0:21:9,72
a company wanting reports, but
some users.
407
0:21:9,88 --> 0:21:14,64
And they also usually not every
user is willing to waste energy
408
0:21:14,64 --> 0:21:16,16
to report problems, right?
409
0:21:16,16 --> 0:21:18,3
It takes some time usually, that's
a problem.
410
0:21:18,82 --> 0:21:21,66
Michael: It takes time and also
you have to rule out other issues
411
0:21:21,66 --> 0:21:22,2
1st, right?
412
0:21:22,2 --> 0:21:24,44
Like you have to make sure it's
not on your side.
413
0:21:24,44 --> 0:21:27,58
Like there's just because something
seems like it's not working
414
0:21:27,64 --> 0:21:30,36
doesn't you don't automatically
assume it's a service.
415
0:21:30,9 --> 0:21:33,7
Nik: Yeah And what I'm describing
is a very common situation,
416
0:21:34,24 --> 0:21:39,66
which I went through in 2006 or
7 and just thought it's solved.
417
0:21:39,8 --> 0:21:43,58
But due to defaults, especially
defaults on managed Postgres
418
0:21:43,58 --> 0:21:48,2
platforms, which keep hot_standby_feedback
off, This is what's happening.
419
0:21:48,48 --> 0:21:54,4
So people increase max_standby_
streaming_delay and then they
420
0:21:54,4 --> 0:21:58,22
just say, okay, we have replication
lags.
421
0:21:58,22 --> 0:22:1,34
And they start thinking why, because
it's not obvious why.
422
0:22:1,56 --> 0:22:6,26
It's not really not obvious, because
yeah, it's just lags and
423
0:22:6,26 --> 0:22:6,94
so on.
424
0:22:7,66 --> 0:22:12,52
Michael: But so should we switch,
should we move to then why
425
0:22:12,52 --> 0:22:16,08
we now have hot_standby_feedback
and what it does?
426
0:22:16,1 --> 0:22:20,2
Nik: So hot_standby_feedback reports
xmin horizon observed on
427
0:22:20,2 --> 0:22:21,38
replica to the primary.
428
0:22:21,82 --> 0:22:27,02
So the primary can involve it in
calculation of the final xmin horizon
429
0:22:27,44 --> 0:22:32,12
used by Vacuum, deciding what can
be cleaned safely, what still
430
0:22:32,12 --> 0:22:33,22
cannot be cleaned.
431
0:22:33,68 --> 0:22:40,58
So if you had 1 hour query on the
primary, which led to bloat
432
0:22:41,2 --> 0:22:45,02
sometimes, right, because xmin
horizon again, global, it
433
0:22:45,02 --> 0:22:45,74
was bloat.
434
0:22:45,86 --> 0:22:50,4
And then you offloaded to read
replica without hot_standby_feedback,
435
0:22:50,4 --> 0:22:54,3
you have lags up to 1 hour because
your queries are 1 hour.
436
0:22:54,52 --> 0:22:56,02
And it's terrible lag, right?
437
0:22:57,04 --> 0:23:2,8
Or when you switch to hot_standby_feedback
to on, you have the problem
438
0:23:2,8 --> 0:23:6,14
like the horizon being reported
and you have the similar situation
439
0:23:6,22 --> 0:23:8,8
as it was executed on the primary
itself.
440
0:23:9,34 --> 0:23:10,02
Matthew 14.
441
0:23:11,12 --> 0:23:14,82
Michael: And I think the critical
thing to mention is the chance
442
0:23:14,82 --> 0:23:18,56
of you then getting conflicts are
massively reduced because the
443
0:23:18,56 --> 0:23:23,8
primary won't have cleaned up those
dead tuples and therefore
444
0:23:24,18 --> 0:23:28,94
won't send those conflicts through
to the replica until it's
445
0:23:28,94 --> 0:23:30,32
finished what it was doing.
446
0:23:30,42 --> 0:23:33,92
So it avoids that case we were
just talking about in most cases.
447
0:23:34,02 --> 0:23:35,54
Nik: That's why lag doesn't happen.
448
0:23:35,54 --> 0:23:37,18
So conflict leads to lag.
449
0:23:37,54 --> 0:23:39,38
Conflict leads to 2 things.
450
0:23:39,38 --> 0:23:43,08
1st, lag of replication, and when
it's too much lag, then cancelling
451
0:23:43,22 --> 0:23:44,62
the source of the problem.
452
0:23:44,64 --> 0:23:45,14
Query.
453
0:23:45,72 --> 0:23:46,22
Right?
454
0:23:46,7 --> 0:23:49,32
So, don't block xmin horizon Progress.
455
0:23:49,66 --> 0:23:51,2
So, you are right.
456
0:23:51,22 --> 0:23:55,64
I like that you used reduce number
of conflicts because it doesn't.
457
0:23:55,64 --> 0:23:56,14
Yeah.
458
0:23:56,82 --> 0:24:0,02
For example, we talked about the
conflict when we need to clean
459
0:24:0,02 --> 0:24:4,46
up, but tuples still needed there,
Like this is a snapshot conflict,
460
0:24:4,46 --> 0:24:6,36
but there might be also a conflict.
461
0:24:6,38 --> 0:24:9,44
When we change schema, we need
ACCESS EXCLUSIVE lock.
462
0:24:9,52 --> 0:24:13,46
And the mistake is to keep this
ACCESS EXCLUSIVE lock for long.
463
0:24:15,06 --> 0:24:18,52
And on the primary, it leads to
many problems.
464
0:24:18,52 --> 0:24:21,02
We know like some selects even
will wait.
465
0:24:21,02 --> 0:24:23,34
It's ACCESS EXCLUSIVE lock, blocks
even selects.
466
0:24:23,68 --> 0:24:24,48
Schema change.
467
0:24:24,48 --> 0:24:28,52
So if you open transaction, edit
a column very briefly, and then
468
0:24:28,52 --> 0:24:30,26
start to read something from somewhere.
469
0:24:30,48 --> 0:24:32,68
You keep the lock until very end
of transactions.
470
0:24:32,68 --> 0:24:35,96
We discussed many times lock cannot
be released midway.
471
0:24:36,02 --> 0:24:37,54
It's released only at the end.
472
0:24:37,54 --> 0:24:42,38
If you do something else, this
lock is held and nobody can work
473
0:24:42,38 --> 0:24:43,3
with that table.
474
0:24:43,32 --> 0:24:46,74
But on the replica also, if you
keep this lock on the primary.
475
0:24:47,08 --> 0:24:52,44
WAL comes saying lock and again,
lag will happen.
476
0:24:52,44 --> 0:24:54,94
And hot_standby_feedback doesn't
solve this at all.
477
0:24:55,76 --> 0:24:56,66
This should be clear.
478
0:24:56,66 --> 0:24:58,48
So yeah, you're very good warning.
479
0:24:58,48 --> 0:24:59,06
I liked it.
480
0:24:59,06 --> 0:25:0,52
It's not solving fully.
481
0:25:0,8 --> 0:25:3,7
Even more, I learned it only recently.
482
0:25:4,64 --> 0:25:9,64
So if you usually use standard
replicas, these days I used streaming
483
0:25:9,64 --> 0:25:11,62
replication plus replication slots.
484
0:25:11,68 --> 0:25:15,36
Replication slots were created
later than streaming replication.
485
0:25:15,36 --> 0:25:16,9
It's like additional level.
486
0:25:17,36 --> 0:25:21,7
And you can use streaming replication
without slots.
487
0:25:21,7 --> 0:25:26,26
Usually people think replication
slots are protection from WALs
488
0:25:26,58 --> 0:25:28,16
being deleted from the primary.
489
0:25:28,44 --> 0:25:31,78
The replica lags too much, it cannot
converge anymore, right?
490
0:25:31,92 --> 0:25:35,38
It's solved usually if you have
good backups in S3, object storage,
491
0:25:35,38 --> 0:25:39,02
you can configure restore_command
on the replica and it will
492
0:25:39,02 --> 0:25:40,84
just take WALs from there, no
problem.
493
0:25:40,84 --> 0:25:42,64
So slots can be like mitigated.
494
0:25:42,9 --> 0:25:45,26
Also slots are convenient for observability.
495
0:25:45,72 --> 0:25:49,12
But what I also learned, imagine
we have streaming replication
496
0:25:49,12 --> 0:25:49,96
without slot.
497
0:25:50,28 --> 0:25:55,24
Slots have xmin horizon reporting
right in pg_replication_slots,
498
0:25:55,24 --> 0:25:57,52
you can see it, xmin, column.
499
0:25:57,98 --> 0:26:1,1
But if you don't have slot, streaming
replication, It works all
500
0:26:1,1 --> 0:26:1,6
good.
501
0:26:1,72 --> 0:26:5,1
But then some network issue, replica
disconnected.
502
0:26:5,46 --> 0:26:8,68
On the primary, for example, vacuum
deleted tuples, reconnection
503
0:26:8,82 --> 0:26:12,48
happened, vacuum already deleted
tuples and WAL is delivered,
504
0:26:12,54 --> 0:26:13,68
tuples are deleted.
505
0:26:14,16 --> 0:26:18,28
And despite hot_standby_feedback
being on, we have situation like
506
0:26:18,28 --> 0:26:19,26
it was off.
507
0:26:20,28 --> 0:26:23,1
This was an interesting finding
for me last week when I dived
508
0:26:23,1 --> 0:26:28,14
into this topic deeper, using our
new tool for experiments, reproduced
509
0:26:28,14 --> 0:26:30,56
a lot of failure scenarios, including
this 1.
510
0:26:30,72 --> 0:26:31,76
So it's interesting.
511
0:26:32,08 --> 0:26:35,28
It means that slots are useful
in many different cases also,
512
0:26:35,28 --> 0:26:36,84
like in sensors, right?
513
0:26:37,54 --> 0:26:42,34
So yeah, hot_standby_feedback
on drastically reduce the chance
514
0:26:42,34 --> 0:26:44,56
that you will have lag on standby.
515
0:26:45,06 --> 0:26:48,68
And this is what we want for the
replicas because we want them
516
0:26:48,68 --> 0:26:51,16
to be up to date as much as possible.
517
0:26:51,98 --> 0:26:53,9
Michael: But it also does 1 other
thing that I think is quite
518
0:26:53,9 --> 0:26:54,4
important.
519
0:26:54,94 --> 0:26:58,4
It should also drastically reduce
the number of cancellations
520
0:26:58,68 --> 0:27:1,12
you get if you have these long
running queries.
521
0:27:1,4 --> 0:27:4,58
Let's say longer than 30 seconds
or whatever you set that timing
522
0:27:4,64 --> 0:27:9,44
to, it should reduce those getting
cancelled because the conflicts
523
0:27:9,44 --> 0:27:10,32
aren't going through.
524
0:27:10,32 --> 0:27:11,76
Because you're not getting conflicts
in the
525
0:27:11,76 --> 0:27:12,7
Nik: 1st place, right?
526
0:27:13,5 --> 0:27:13,9742
Right.
527
0:27:13,9742 --> 0:27:18,74
Cancellation is a result of delay,
which is a result of conflict.
528
0:27:18,8 --> 0:27:23,7
So conflict, delay or replication
lag, and then cancellation
529
0:27:23,82 --> 0:27:25,26
when it's too high.
530
0:27:25,96 --> 0:27:27,6
Value of lag is too high.
531
0:27:28,34 --> 0:27:33,14
So that's why I think read replicas
should have hot_standby_feedback
532
0:27:33,14 --> 0:27:37,52
on and reasoning that it will be
worse because they will lead
533
0:27:37,52 --> 0:27:38,04
to bloat.
534
0:27:38,04 --> 0:27:40,6
This is just how Postgres works.
535
0:27:40,6 --> 0:27:44,24
It was usually people start with
all queries going to primary,
536
0:27:44,24 --> 0:27:46,16
then they offload queries to replica.
537
0:27:46,16 --> 0:27:49,28
It's just a mistake to expect that
those queries won't affect
538
0:27:49,28 --> 0:27:50,14
vacuum behavior.
539
0:27:51,14 --> 0:27:51,88
That's it.
540
0:27:51,9 --> 0:27:54,26
Of course, you can live with off.
541
0:27:55,16 --> 0:27:56,54
Actually, 2 points here.
542
0:27:56,54 --> 0:27:58,12
1st point is quite interesting.
543
0:27:59,06 --> 0:28:7,06
So we want to minimize damage of
long-running transactions which
544
0:28:7,06 --> 0:28:10,8
block xmin horizon progress, and
how can we do it?
545
0:28:10,8 --> 0:28:16,56
We can set transaction_timeout,
both on primary and all read
546
0:28:16,56 --> 0:28:17,06
replicas.
547
0:28:17,32 --> 0:28:20,96
For example, if we set it to 3
hours, that's it.
548
0:28:21,1 --> 0:28:22,7
No more 3 hours of damage.
549
0:28:22,74 --> 0:28:25,02
But as we discussed, damage is
relative.
550
0:28:25,14 --> 0:28:28,76
3 hours, we don't know how many
transactions happened, right?
551
0:28:29,2 --> 0:28:33,34
I think actually it would make
sense, it might make sense to
552
0:28:33,34 --> 0:28:35,2
have all timeouts.
553
0:28:35,46 --> 0:28:38,4
We have transaction_timeout, we
have statement_timeout, and
554
0:28:38,4 --> 0:28:39,9
idle_in_transaction_session_timeout.
555
0:28:40,64 --> 0:28:43,22
transaction_timeout limits whole
transaction, which is good.
556
0:28:43,26 --> 0:28:47,86
But I think it might make sense
either to have all these 3 measured
557
0:28:47,9 --> 0:28:52,32
in not in milliseconds or seconds,
but in a transaction count.
558
0:28:52,86 --> 0:28:56,5
Imagine transaction_timeout, but
count of transactions, right?
559
0:28:56,74 --> 0:29:1,72
Or adjust hot_standby_feedback so
it would be not like on and
560
0:29:1,72 --> 0:29:5,72
off, but some threshold after which
we cancel.
561
0:29:6,28 --> 0:29:9,14
So there is definitely room for
improvement here.
562
0:29:9,14 --> 0:29:13,92
And if you look, my AI told me
other database systems ship settings
563
0:29:14,22 --> 0:29:14,94
for replicas.
564
0:29:15,54 --> 0:29:18,96
Postgres ships 2 extremes, on and
off.
565
0:29:20,14 --> 0:29:23,86
But with transaction_timeout, at
least measured in seconds, right?
566
0:29:23,86 --> 0:29:27,62
It's indirect, but it's already
good enough in many cases, which
567
0:29:27,62 --> 0:29:29,42
was implemented with Postgres 17.
568
0:29:30,14 --> 0:29:32,22
So on old Postgres it's not available.
569
0:29:32,78 --> 0:29:33,9
But it's good enough.
570
0:29:33,9 --> 0:29:37,62
My recommendation, on and 3 hours
transaction_timeout.
571
0:29:39,0 --> 0:29:42,42
So nobody could leave transaction
open which blocks xmin horizon
572
0:29:42,5 --> 0:29:44,08
progress for a very long time.
573
0:29:44,16 --> 0:29:46,96
But you need to do it on the primary
as well, right?
574
0:29:46,96 --> 0:29:49,14
Because who knows what happens
there.
575
0:29:49,28 --> 0:29:50,88
It's not no difference here.
576
0:29:51,74 --> 0:29:57,22
Michael: I think the big difference
is people not offloading
577
0:29:57,36 --> 0:30:0,06
just like OLTP traffic to a replica.
578
0:30:0,06 --> 0:30:3,82
I think it's people think that
I've got a replica, I can send
579
0:30:3,82 --> 0:30:7,2
my reporting or analytics queries
there or I can give a data
580
0:30:7,2 --> 0:30:11,08
team access to that and it doesn't
matter, it protects the primary.
581
0:30:11,08 --> 0:30:16,32
I think the key learning from this
setting is That's not true.
582
0:30:17,54 --> 0:30:22,02
You have to be, it can still affect
the primary and therefore
583
0:30:22,02 --> 0:30:25,7
you have to factor that in on almost
like an architectural level.
584
0:30:25,76 --> 0:30:28,78
Should you even be running those
reporting queries there if it
585
0:30:28,78 --> 0:30:32,84
on an OLTP, like an important OLTP
system or should you not do
586
0:30:32,84 --> 0:30:33,34
that?
587
0:30:33,4 --> 0:30:35,7
I think it raises those important
questions.
588
0:30:36,42 --> 0:30:36,92
Nik: Yeah.
589
0:30:37,66 --> 0:30:41,68
For reporting replicas, maybe you
should keep it off, but the
590
0:30:41,68 --> 0:30:45,46
consequences are very often replication
lags.
591
0:30:45,86 --> 0:30:49,24
And I even say that it makes like
basically this replication
592
0:30:49,28 --> 0:30:54,92
replica, like single user, like
you take around very long query,
593
0:30:54,92 --> 0:30:57,8
all other users like suffer and
cannot use it anymore.
594
0:30:57,8 --> 0:30:59,64
Like they say, oh, it's very old
data.
595
0:30:59,64 --> 0:31:0,8
We need fresh data.
596
0:31:1,28 --> 0:31:6,26
So like you become very like expensive
user, but maybe it's fine
597
0:31:6,26 --> 0:31:10,64
in some cases, like maybe it's
better to do that and then catch
598
0:31:10,64 --> 0:31:11,14
up.
599
0:31:11,64 --> 0:31:13,3
I don't know, but it's quite expensive.
600
0:31:13,94 --> 0:31:17,78
It's quite expensive to have a
whole node for reports, which,
601
0:31:17,84 --> 0:31:19,84
and this node is lagging and catches
up.
602
0:31:19,84 --> 0:31:20,42
I don't know.
603
0:31:20,42 --> 0:31:23,68
Michael: Like it's, but some, some
people do like shipping it
604
0:31:23,68 --> 0:31:24,5
to like ClickHouse.
605
0:31:24,6 --> 0:31:26,46
We did whole episodes on this,
didn't we?
606
0:31:26,46 --> 0:31:28,76
And I think there's some interesting
alternatives.
607
0:31:29,34 --> 0:31:31,8
Nik: But this is not really a replica,
it's an analytical replica,
608
0:31:31,8 --> 0:31:31,96
right?
609
0:31:31,96 --> 0:31:36,94
Or if you have different storage,
there are some, not Postgres,
610
0:31:36,94 --> 0:31:40,02
but some alternative to Postgres,
which stores data differently
611
0:31:40,12 --> 0:31:42,14
and executes queries much faster.
612
0:31:42,66 --> 0:31:46,56
In this case, maybe hot_standby_feedback
on is still good because
613
0:31:46,56 --> 0:31:48,82
negative effect to vacuum will
be low, right?
614
0:31:49,24 --> 0:31:52,08
I can imagine some cases where
off is good.
615
0:31:52,08 --> 0:31:54,06
For example, if it's a delayed
replica.
616
0:31:55,2 --> 0:31:59,54
Some people keep 8 hours, 12 hours
delayed replica, which replays
617
0:31:59,54 --> 0:32:4,84
WAL with delay 10, 12 hours to
be able to very quickly restore
618
0:32:5,38 --> 0:32:7,86
point-in-time recovery, to perform
point-in-time recovery.
619
0:32:8,14 --> 0:32:11,78
I think this recipe is quite outdated
because now we have snapshots
620
0:32:11,82 --> 0:32:15,66
and if even with lazy load, it's
quite fast to provision multi-terabyte
621
0:32:15,88 --> 0:32:18,06
databases from cloud snapshots.
622
0:32:18,9 --> 0:32:22,36
But if you use like delayed replica,
of course you don't want
623
0:32:22,36 --> 0:32:23,16
hot_standby_feedback.
624
0:32:23,16 --> 0:32:25,68
I actually think it's not possible
to use it there because hot_stand
625
0:32:25,68 --> 0:32:28,3
by_feedback works only with streaming
replication, right?
626
0:32:28,925 --> 0:32:36,5
So with archive replicas which
work using restore_command, replaying
627
0:32:36,5 --> 0:32:38,5
WALs from archive or from some place.
628
0:32:39,24 --> 0:32:41,18
hot_standby_feedback cannot be applied
there.
629
0:32:41,76 --> 0:32:45,48
And you cannot see them in replication
slots, so these replicas
630
0:32:45,48 --> 0:32:46,02
are invisible.
631
0:32:46,02 --> 0:32:48,54
And this is how delayed replicas
should work.
632
0:32:48,62 --> 0:32:51,88
And in this case, you'll be dealing
with the same mechanics,
633
0:32:52,0 --> 0:32:59,35
but defined by max_standby, not
streaming, but what the other
634
0:32:59,35 --> 0:32:59,84
alternative?
635
0:32:59,84 --> 0:33:0,34
Shipping.
636
0:33:1,08 --> 0:33:5,46
Archive delay, max_standby_archive_
delay.
637
0:33:5,46 --> 0:33:5,96
Right.
638
0:33:6,46 --> 0:33:10,3
So I think this is, this covers
quite well this topic.
639
0:33:10,4 --> 0:33:14,88
I think Postgres has opportunity
to, to improve things drastically,
640
0:33:15,04 --> 0:33:16,68
like for better control.
641
0:33:17,38 --> 0:33:22,34
I wish like I had a capability
to define the damage, not in seconds,
642
0:33:22,34 --> 0:33:28,4
but precise, like maximum, like
100000 transaction IDs, yeah.
643
0:33:29,18 --> 0:33:34,28
And if it happens, if somebody
exceeds it, I want to cancel that
644
0:33:34,28 --> 0:33:34,78
1.
645
0:33:34,92 --> 0:33:38,66
And for reporting, the most standard
approach for large databases,
646
0:33:38,86 --> 0:33:43,1
I agree with you, it remains another
database system and you
647
0:33:43,1 --> 0:33:47,46
need to create some pipeline, like
a logical replication pipeline
648
0:33:47,56 --> 0:33:51,36
to ship data to ClickHouse or anything
else like Snowflake.
649
0:33:51,82 --> 0:33:52,32
Michael: Yeah.
650
0:33:52,42 --> 0:33:52,66
Yeah.
651
0:33:52,66 --> 0:33:53,86
We could monitor for that.
652
0:33:53,86 --> 0:33:55,22
Like you can do that.
653
0:33:55,32 --> 0:33:56,98
You can build that yourself, right?
654
0:33:56,98 --> 0:34:2,5
If you monitor for things holding
back the xmin horizon and you
655
0:34:2,5 --> 0:34:5,78
can check the age in like transaction
IDs.
656
0:34:5,86 --> 0:34:9,92
So that is that's already possible
today right but we just have
657
0:34:9,92 --> 0:34:12,74
to do it ourselves rather than
a configuration parameter.
658
0:34:14,24 --> 0:34:19,86
Nik: Yeah so yeah you are talking
about some automation which
659
0:34:19,86 --> 0:34:23,14
will be outside Postgres but it
will demonstrate all 5 reasons
660
0:34:23,14 --> 0:34:25,96
for xmin horizon being blocked,
identify them and then cancel
661
0:34:25,96 --> 0:34:26,46
them.
662
0:34:26,54 --> 0:34:27,14
Then 5.
663
0:34:27,74 --> 0:34:28,24
Michael: Yeah.
664
0:34:28,66 --> 0:34:29,16
Nik: Yeah.
665
0:34:29,2 --> 0:34:32,88
You like you're talking about self-driving
ideas, but it shouldn't
666
0:34:32,88 --> 0:34:34,12
be in Postgres in my opinion.
667
0:34:34,12 --> 0:34:35,78
This is, this is the wrong example.
668
0:34:36,02 --> 0:34:40,68
This logic is too basic, too fundamental
to be, it's possible
669
0:34:40,68 --> 0:34:41,78
to implement outside.
670
0:34:42,34 --> 0:34:42,84
Definitely.
671
0:34:42,9 --> 0:34:46,56
I remember actually many times
I implemented like something in
672
0:34:46,56 --> 0:34:50,02
pg_cron or regular cron, like
canceling long running transactions,
673
0:34:50,02 --> 0:34:51,68
which are like offensive.
674
0:34:52,72 --> 0:34:54,72
Looking at backend_xmin
and so on.
675
0:34:54,72 --> 0:34:56,02
Yeah, we did it actually.
676
0:34:56,14 --> 0:34:59,0
And this is like duct taping, right?
677
0:34:59,64 --> 0:35:0,46
Michael: Yeah, sure.
678
0:35:0,92 --> 0:35:5,58
Nik: This is not, but I'm talking
like, I just feel, I feel the
679
0:35:5,58 --> 0:35:10,02
need in Postgres itself could be
improved, maybe some listener
680
0:35:10,9 --> 0:35:12,28
should propose some patches.
681
0:35:12,88 --> 0:35:15,8
Michael: I think it would be tough
to, I think this is a really
682
0:35:15,8 --> 0:35:18,14
tough 1 because it's a genuine
trade off.
683
0:35:18,24 --> 0:35:22,72
I don't feel like there's a right
solution and it's going to
684
0:35:22,72 --> 0:35:27,84
depend on what you're using your
replica for and maybe there's
685
0:35:27,84 --> 0:35:32,9
like a case that 95% of people
want and like therefore we should
686
0:35:32,9 --> 0:35:33,98
just change it.
687
0:35:34,04 --> 0:35:37,8
But it feels to me like some people
do prefer the trade-off of
688
0:35:37,8 --> 0:35:40,38
having it on and some people prefer
the trade-off of having it
689
0:35:40,38 --> 0:35:40,88
off.
690
0:35:41,18 --> 0:35:45,36
And it's maybe tricky, gonna be
hard to find something everybody's
691
0:35:45,48 --> 0:35:46,56
actually happy with.
692
0:35:47,12 --> 0:35:51,04
Nik: Yeah, actually, I think for,
yeah, this is, yeah, I think
693
0:35:51,04 --> 0:35:54,18
to leave the choice on the user
who provisions read replica,
694
0:35:54,24 --> 0:35:55,52
it is also a good idea.
695
0:35:57,72 --> 0:36:1,16
By the way, I've read, I saw many
hackers proposed to switch
696
0:36:1,16 --> 0:36:3,3
default in Postgres itself to on.
697
0:36:3,64 --> 0:36:7,0
There was such opinion, very strong
1, multiple, very well-known
698
0:36:7,02 --> 0:36:7,86
hackers proposed it.
699
0:36:7,86 --> 0:36:11,32
But then it was a discussion, it
was opposition to it.
700
0:36:11,32 --> 0:36:15,06
And by the way, at that time,
transaction_timeout didn't exist.
701
0:36:15,06 --> 0:36:17,06
So maybe now it should be reconsidered.
702
0:36:17,44 --> 0:36:17,9
Yeah.
703
0:36:17,9 --> 0:36:19,18
It was before Postgres 17.
704
0:36:19,82 --> 0:36:24,78
And the reason I remember, which
like hits in my mind brightly,
705
0:36:25,08 --> 0:36:30,22
that it was named that problems
with lags and conflicts canceled,
706
0:36:30,66 --> 0:36:33,62
canceled like lags and canceled
queries on replica.
707
0:36:34,06 --> 0:36:34,56
Yes.
708
0:36:34,94 --> 0:36:36,24
Very obvious to you.
709
0:36:36,26 --> 0:36:39,1
And it's better to discover what's
happening, understand and
710
0:36:39,1 --> 0:36:43,32
then make decision based on your
situation and experience rather
711
0:36:43,32 --> 0:36:47,7
than you don't notice or bloat.
712
0:36:48,32 --> 0:36:48,84
Bloat on
713
0:36:48,84 --> 0:36:50,88
Michael: the primary building up
slowly.
714
0:36:51,04 --> 0:36:54,02
Nik: But at the same time, the
same problem exists on the primary
715
0:36:54,02 --> 0:36:54,52
itself.
716
0:36:54,64 --> 0:36:56,14
Absolutely the same, right?
717
0:36:57,18 --> 0:36:59,42
Michael: And I think you can make
the opposite argument.
718
0:37:0,92 --> 0:37:2,88
Bloat is quieter.
719
0:37:3,22 --> 0:37:5,6
The reason you don't notice bloat
is because maybe it's not as
720
0:37:5,6 --> 0:37:5,92
bad.
721
0:37:5,92 --> 0:37:7,86
Nik: Yeah, we should control bloat
anyway.
722
0:37:8,08 --> 0:37:10,1
We should control bloat anyway,
right?
723
0:37:10,2 --> 0:37:13,48
And on the primary it happens exactly
in the same mechanism,
724
0:37:13,52 --> 0:37:17,6
so why should we make different
approach, apply different approach
725
0:37:17,6 --> 0:37:18,66
to replica.
726
0:37:18,74 --> 0:37:20,9
And bloat control is a whole topic
anyway, right?
727
0:37:20,9 --> 0:37:23,72
If you want to improve that experience,
it should be done on
728
0:37:23,72 --> 0:37:24,7
the primary 1st.
729
0:37:25,42 --> 0:37:29,14
Michael: And if we care about that
from a Postgres tuning perspective,
730
0:37:29,2 --> 0:37:32,36
we should probably be changing
the defaults of the various auto
731
0:37:32,36 --> 0:37:33,62
vacuum settings 1st.
732
0:37:33,64 --> 0:37:37,04
Nik: You know what, I like how
I'm pulling you into discussion
733
0:37:37,12 --> 0:37:37,74
and hackers.
734
0:37:37,84 --> 0:37:40,46
You should think and write your
opinion next time.
735
0:37:41,6 --> 0:37:43,24
Michael: Yeah, I'll try and get
braver.
736
0:37:44,2 --> 0:37:46,66
Nik: Yeah, it's a rabbit hole,
I know.
737
0:37:48,28 --> 0:37:48,78
Yeah.
738
0:37:49,7 --> 0:37:51,74
Michael: Anyway, this was great
and helpful.
739
0:37:51,74 --> 0:37:56,5
And I think I agree with you that
it feels like the vast majority
740
0:37:56,82 --> 0:38:1,02
of OLTP replicas where you're just
offloading a huge number of
741
0:38:1,02 --> 0:38:4,7
really quick queries, hot_stand
by_feedback On makes so much more
742
0:38:4,7 --> 0:38:6,22
sense to me than Off.
743
0:38:6,62 --> 0:38:10,28
But I can't agree that like all
read replicas should have it
744
0:38:10,28 --> 0:38:10,46
off.
745
0:38:10,46 --> 0:38:14,06
Like I do think there are these
people using it, probably still
746
0:38:14,06 --> 0:38:18,16
it's a good architectural choice
as these analytics replicas
747
0:38:18,48 --> 0:38:21,0
and I think you're right that it
makes sense to keep those off
748
0:38:21,0 --> 0:38:23,86
and it's nice that you can do it
on a per replica basis like
749
0:38:23,86 --> 0:38:26,48
it doesn't have to be the same
for all of your replicas which
750
0:38:26,48 --> 0:38:27,14
is cool.
751
0:38:27,44 --> 0:38:27,94
Yeah.
752
0:38:28,7 --> 0:38:29,2
Good.
753
0:38:29,38 --> 0:38:29,88
Awesome.
754
0:38:29,96 --> 0:38:30,78
Anything else?
755
0:38:31,1 --> 0:38:31,6
Nik: No.
756
0:38:31,88 --> 0:38:35,94
Just don't let your replica to
lag too often.
757
0:38:36,22 --> 0:38:37,04
Michael: Nice 1, Nik.
758
0:38:37,04 --> 0:38:37,9
Thanks so much.
