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.