autovacuum

1
0:0:4,96 --> 0:0:7,8999996
Nikolay: Everyone who is running
Postgres has autovacuum, and

2
0:0:7,8999996 --> 0:0:12,0199995
most of people heard about autovacuum
and vacuuming and bloat

3
0:0:12,04 --> 0:0:13,48
and this garbage collection.

4
0:0:14,28 --> 0:0:22,08
But a lot of databases grow with
problems being unnoticed and

5
0:0:22,08 --> 0:0:25,08
they start tuning autovacuum
too late.

6
0:0:25,64 --> 0:0:30,04
So this topic today is brought
by you Michael, right?

7
0:0:30,06 --> 0:0:30,72
Hi Michael.

8
0:0:31,24 --> 0:0:31,98
Michael: Hi Nik.

9
0:0:32,84 --> 0:0:36,1
Nikolay: And we definitely had
this topic discussed in the past

10
0:0:36,1 --> 0:0:40,96
in terms of like vacuum and bloat,
but we never discussed the

11
0:0:40,96 --> 0:0:44,54
vacuum itself and how to tune it
properly and what are the best

12
0:0:44,54 --> 0:0:45,04
practices.

13
0:0:46,02 --> 0:0:48,059998
And yeah, I'm glad that you brought
it.

14
0:0:48,059998 --> 0:0:49,22
Let's talk about this.

15
0:0:49,81 --> 0:0:51,14
Michael: Sounds good.

16
0:0:51,14 --> 0:0:51,38
Yeah.

17
0:0:51,38 --> 0:0:54,14
I'm looking forward to hearing
your views on this kind of from

18
0:0:54,14 --> 0:0:57,62
a seeing lots and lots of different
clients perspective because

19
0:0:57,62 --> 0:1:1,82
I get the impression dealing with
people less frequently that

20
0:1:2,68 --> 0:1:6,66
Often it's left really late before
people start tuning or to

21
0:1:6,66 --> 0:1:10,46
vacuum and when I say late a startup
will grow to relatively

22
0:1:10,52 --> 0:1:15,72
significant size before touching
those parameters And I often

23
0:1:15,72 --> 0:1:20,64
think they could have avoided quite
a few headaches and got into

24
0:1:20,76 --> 0:1:26,06
issues slower, if that makes sense,
by just changing a few settings

25
0:1:26,28 --> 0:1:27,1
much earlier.

26
0:1:27,38 --> 0:1:31,2
So yeah, I was keen to get your
thoughts on If you see the same

27
0:1:31,2 --> 0:1:34,9
and also like, when would you advise
people like startup life's

28
0:1:34,9 --> 0:1:37,8
busy, when would you advise people
looking at this kind of thing?

29
0:1:38,04 --> 0:1:38,4
Nikolay: Yeah.

30
0:1:38,4 --> 0:1:43,74
First of all, every single client
which comes to us, we talk

31
0:1:43,74 --> 0:1:47,04
about autovacuum tuning on any
platform.

32
0:1:47,04 --> 0:1:47,7
Doesn't matter.

33
0:1:47,7 --> 0:1:49,4
Self-managed, any managed service.

34
0:1:49,4 --> 0:1:53,4
We always touch this topic and
always like in hundred percent

35
0:1:53,4 --> 0:1:56,26
of cases there is something that
should be done there.

36
0:1:56,78 --> 0:1:59,18
Which means your point is absolutely
right.

37
0:1:59,18 --> 0:2:3,16
It should be done earlier because
when people come to us for

38
0:2:3,16 --> 0:2:7,42
help it's already, they experience
already some problems, some

39
0:2:7,42 --> 0:2:9,7
troubles, right, and this is a
reactive approach.

40
0:2:10,38 --> 0:2:13,22
Michael: And just to clarify, when
you say 100 percent, do you

41
0:2:13,22 --> 0:2:17,84
mean some percentage have done
loads of tuning and still have

42
0:2:17,84 --> 0:2:20,38
issues and some percentage have
done nothing at all.

43
0:2:20,38 --> 0:2:22,26
What's the kind of distribution
like?

44
0:2:22,26 --> 0:2:23,4
Nikolay: It's hard to say.

45
0:2:23,4 --> 0:2:26,48
Actually I checked how many clusters
we observed lately.

46
0:2:26,48 --> 0:2:32,08
It was like 140 or something where
we used thorough like comprehensive

47
0:2:32,08 --> 0:2:35,68
health analysis and tuning and
recommendations and work closely

48
0:2:35,68 --> 0:2:36,48
and so on.

49
0:2:36,68 --> 0:2:43,48
So I'd say 0% have an ideal picture
in terms of how autovacuum

50
0:2:43,48 --> 0:2:44,12
is tuned.

51
0:2:45,04 --> 0:2:49,06
Most customers running on managed
services, especially RDS, have,

52
0:2:49,06 --> 0:2:51,72
let me say it, half-ass tuned
autovacuum.

53
0:2:53,5 --> 0:2:58,52
This is new term we can use here
because this is like, it amazes

54
0:2:58,52 --> 0:2:59,7
me why it's so.

55
0:2:59,7 --> 0:3:1,9
We can dive into details soon.

56
0:3:2,38 --> 0:3:8,36
And only few, like literally that's
a several, I saw they have

57
0:3:8,36 --> 0:3:12,94
very well tuned autovacuum and
we just shift focus to why it's

58
0:3:12,94 --> 0:3:16,4
still not enough and what we should
do with specific pieces of

59
0:3:16,4 --> 0:3:16,9
workload.

60
0:3:17,62 --> 0:3:18,96
Michael: Sure, makes sense.

61
0:3:19,2 --> 0:3:21,6
Nikolay: Local tuning already,
what to do about it.

62
0:3:21,6 --> 0:3:26,08
And in most cases there we also
involve, we focus on specific

63
0:3:26,16 --> 0:3:30,04
workload, but also we go, of course,
beyond just autovacuum

64
0:3:30,04 --> 0:3:34,08
tuning, because usually this is
like some pathological workload

65
0:3:34,12 --> 0:3:38,62
or some, there are some problems
which are not conflicting, how

66
0:3:38,62 --> 0:3:41,94
to say, like they are blocking
autovacuum work, right?

67
0:3:41,98 --> 0:3:42,48
Yes.

68
0:3:42,98 --> 0:3:48,22
Before we dive into details, maybe
let's just explain to wider

69
0:3:48,22 --> 0:3:53,22
audience what autovacuum is, because
I think many still misunderstand

70
0:3:53,3 --> 0:3:53,8
it.

71
0:3:53,94 --> 0:3:59,82
Recently I talked to, you know,
like our focus is fast growing

72
0:3:59,82 --> 0:4:3,38
startups and I talked to technical
founders quite often.

73
0:4:4,28 --> 0:4:6,42
These guys are super smart.

74
0:4:6,42 --> 0:4:9,1
Technically they understand, they
definitely understand throughput,

75
0:4:9,14 --> 0:4:10,76
latency, all the numbers and so
on.

76
0:4:10,76 --> 0:4:15,98
But I see they just didn't have
time to dive into autovacuum,

77
0:4:15,98 --> 0:4:16,64
what it is.

78
0:4:16,64 --> 0:4:20,82
They heard bloat issue, MVCC, Postgres
is very widely criticized,

79
0:4:21,18 --> 0:4:21,6
right?

80
0:4:21,6 --> 0:4:24,72
But what autovacuum is and what to
do about it, what they need

81
0:4:24,72 --> 0:4:28,98
to have, it's like, it's always
a good topic to discuss and dive

82
0:4:28,98 --> 0:4:29,48
into.

83
0:4:29,5 --> 0:4:33,42
So my simple explanation, autovacuum
is just garbage collection,

84
0:4:33,42 --> 0:4:34,3
first of all.

85
0:4:35,66 --> 0:4:40,32
It has a few more jobs, but the
main job, original job, is garbage

86
0:4:40,32 --> 0:4:40,82
collection.

87
0:4:41,18 --> 0:4:48,14
When you have updates, deletes,
or failed inserts, by the way,

88
0:4:48,14 --> 0:4:52,6
the third part is usually forgotten,
but failed inserts, all

89
0:4:52,6 --> 0:4:56,68
of them produce successful updates,
successful deletes, and failed

90
0:4:56,68 --> 0:4:58,34
inserts, rolled back inserts.

91
0:4:58,86 --> 0:5:5,44
They all produce dead row versions
called tuples, right?

92
0:5:6,22 --> 0:5:8,16
Michael: Probably also rolled back
updates.

93
0:5:10,08 --> 0:5:11,36
Nikolay: Oh yes, you're right.

94
0:5:11,48 --> 0:5:11,98
Yeah.

95
0:5:12,44 --> 0:5:19,08
Successful deletes, rolled back
inserts, or any updates.

96
0:5:19,62 --> 0:5:20,12
Yeah.

97
0:5:20,42 --> 0:5:24,86
Besides HOT updates, which are
nuances, right?

98
0:5:25,08 --> 0:5:25,68
Even HOT

99
0:5:25,68 --> 0:5:26,68
Michael: updates, yeah.

100
0:5:26,88 --> 0:5:29,44
Nikolay: If we want to be thorough,
like we are destroying my

101
0:5:29,44 --> 0:5:31,54
intention to provide simple explanation.

102
0:5:31,84 --> 0:5:33,5
Let's keep it short.

103
0:5:33,9 --> 0:5:36,36
So it's first of all garbage collections.

104
0:5:37,9 --> 0:5:41,94
So if, especially for guys who
run Postgres on machines with a

105
0:5:41,94 --> 0:5:48,74
lot of cores, like typical like
Intel machine 96 cores or 48

106
0:5:48,86 --> 0:5:51,72
or like AMD hundreds of cores,
right?

107
0:5:53,16 --> 0:5:55,9
Michael: They- We have very, we
have a very different definition

108
0:5:55,9 --> 0:5:57,54
of what typical is for-

109
0:5:57,7 --> 0:5:59,54
Nikolay: Well, typical big, large
database.

110
0:6:0,08 --> 0:6:0,9
Michael: Sure, sure.

111
0:6:1,06 --> 0:6:4,44
Nikolay: So it's, it amazes me,
like I, I saw, I told you, I

112
0:6:4,44 --> 0:6:9,48
saw Postgres clusters achieving
10, 20 terabytes with default

113
0:6:9,52 --> 0:6:10,58
autovacuum settings.

114
0:6:11,0 --> 0:6:14,32
Somehow surviving, already having
5 replicas.

115
0:6:14,76 --> 0:6:15,3
It's insane.

116
0:6:15,3 --> 0:6:18,48
Michael: This is what I meant in
terms of like, should people

117
0:6:18,66 --> 0:6:20,6
be doing it earlier or is it okay?

118
0:6:20,74 --> 0:6:21,35
There's a certain argument.

119
0:6:21,35 --> 0:6:21,54
Nikolay: It's not

120
0:6:21,54 --> 0:6:22,38
Michael: okay, no.

121
0:6:22,38 --> 0:6:26,7
Yeah, I think I agree, but as you
say, they've somehow got by

122
0:6:26,92 --> 0:6:28,54
even to that large extent.

123
0:6:28,74 --> 0:6:33,42
But I've also seen smaller systems
really struggling because

124
0:6:33,42 --> 0:6:34,84
autovacuum hasn't been tuned.

125
0:6:34,84 --> 0:6:38,12
So far fewer, like maybe not even
a terabyte, but struggling

126
0:6:38,12 --> 0:6:39,58
because of a certain workload.

127
0:6:40,24 --> 0:6:42,94
So yeah, it's not just size, right?

128
0:6:43,26 --> 0:6:47,12
Nikolay: It can be a table of 2
gigabytes in size, but it's mostly

129
0:6:47,12 --> 0:6:48,3
bloated, like 99.9%.

130
0:6:49,14 --> 0:6:52,32
I saw it many times, especially
with queue-like workloads.

131
0:6:52,38 --> 0:6:55,52
And then it can suffer, small database,
but it suffers from the

132
0:6:55,52 --> 0:6:58,7
fact that autovacuum is not tuned
and maybe something is blocking

133
0:6:58,7 --> 0:6:59,2
it.

134
0:6:59,34 --> 0:7:1,4
Garbage collection job is blocking
it.

135
0:7:1,4 --> 0:7:3,74
So we need to combine both things
here.

136
0:7:3,74 --> 0:7:6,86
But my point is, autovacuum is
just garbage collection.

137
0:7:7,48 --> 0:7:11,4
If you tune Postgres, or maybe
your provider tuned, for example,

138
0:7:11,4 --> 0:7:15,9
to have bigger shared_buffers,
bigger cache, right, and a work_mem

139
0:7:16,24 --> 0:7:19,24
for sort and join operations and
so on.

140
0:7:19,24 --> 0:7:22,36
If it's already tuned and max_connections
has increased, maybe

141
0:7:22,36 --> 0:7:25,62
a pooler is in front of it, like
you tuned it to handle workload.

142
0:7:26,72 --> 0:7:31,6
Why the heck garbage collection
remains untuned?

143
0:7:31,72 --> 0:7:34,54
It should be tuned together with
everything else, right?

144
0:7:34,54 --> 0:7:36,18
So it should be.

145
0:7:36,4 --> 0:7:39,2
But we see it's often lagging in
tuning.

146
0:7:40,24 --> 0:7:43,9
And it can be a local workload
on small database already suffering,

147
0:7:43,9 --> 0:7:48,4
or it can be a big cluster and
garbage collection mostly default.

148
0:7:48,76 --> 0:7:50,2
autovacuum is mostly default.

149
0:7:50,34 --> 0:7:53,76
But that being said, we should
mention that there are a few more

150
0:7:53,76 --> 0:7:55,3
jobs that the vacuum has.

151
0:7:55,72 --> 0:7:56,76
Maintenance statistics.

152
0:7:57,74 --> 0:7:58,14
Yeah?

153
0:7:58,14 --> 0:7:58,62
Do you want to

154
0:7:58,62 --> 0:8:0,02
Michael: stick to the vacuum ones
first?

155
0:8:0,02 --> 0:8:3,7
Because I think the name autovacuum
is quite a good name in

156
0:8:3,7 --> 0:8:9,38
that it's all of the jobs that
vacuum does but automated But

157
0:8:9,38 --> 0:8:13,7
with 1 addition, which is there
is also an auto-analyze

158
0:8:13,7 --> 0:8:15,72
feature Right, which is what you're
just about to talk about

159
0:8:15,72 --> 0:8:18,8
in terms of statistics, but I feel
like the other vacuum jobs

160
0:8:18,8 --> 0:8:22,5
are worth—do you think of it like
as garbage collection, statistics,

161
0:8:22,54 --> 0:8:23,95
and then the other vacuum jobs?

162
0:8:23,95 --> 0:8:25,64
Nikolay: — It's just easier to
memorize.

163
0:8:25,84 --> 0:8:29,36
Statistics also should be maintained
up to date.

164
0:8:30,02 --> 0:8:33,22
If it's lagging, it may affect
performance, might affect performance.

165
0:8:33,66 --> 0:8:36,44
But you're right, there are also
a couple of more jobs.

166
0:8:36,46 --> 0:8:37,62
Transaction ID wraparound.

167
0:8:38,22 --> 0:8:43,44
If you have a lot of transactions
per day, we know there are

168
0:8:43,44 --> 0:8:45,92
sometimes some like 1000000000
per day.

169
0:8:45,92 --> 0:8:46,64
It's a lot.

170
0:8:46,64 --> 0:8:50,14
In this case, churn is high and
int4 is not enough.

171
0:8:50,14 --> 0:8:51,94
Half of int4 is not enough.

172
0:8:52,36 --> 0:8:55,38
transaction ID wraparound prevention
should be very frequent

173
0:8:55,38 --> 0:8:58,22
in each tuple, in each row version.

174
0:8:58,98 --> 0:9:2,66
And also there is Another very
important job, especially for

175
0:9:2,66 --> 0:9:6,3
performance, it's maintaining visibility
maps.

176
0:9:7,12 --> 0:9:11,26
It's a bitmap with 2 bits for each
page.

177
0:9:11,28 --> 0:9:13,68
Page is 8 kibibytes, right?

178
0:9:13,68 --> 0:9:18,28
And 2 bits, is the whole page fully
visible, All visible?

179
0:9:18,5 --> 0:9:20,94
Means all tuples are visible to
all transactions.

180
0:9:21,18 --> 0:9:23,64
And second is all frozen.

181
0:9:24,34 --> 0:9:26,0
Yeah, just previous topic.

182
0:9:26,0 --> 0:9:28,54
Transaction ID wraparound prevention.

183
0:9:28,6 --> 0:9:32,54
So not to visit this page again
if we know that all the tuples

184
0:9:32,54 --> 0:9:35,58
are in the past, they're already
marked frozen, so they're definitely

185
0:9:35,58 --> 0:9:40,52
in the past, even if their xmin,
xmax look like they are in the

186
0:9:40,52 --> 0:9:41,02
future.

187
0:9:41,6 --> 0:9:42,46
This is freezing.

188
0:9:42,54 --> 0:9:43,4
It marks like...

189
0:9:44,16 --> 0:9:47,86
All frozen bit means like it's
a whole page in the past.

190
0:9:47,86 --> 0:9:49,78
The next vacuum will just skip
it.

191
0:9:49,92 --> 0:9:51,58
That's optimization as well, right?

192
0:9:51,58 --> 0:9:56,66
So for more frequent vacuuming,
not to do the same job once again,

193
0:9:57,26 --> 0:9:57,76
right?

194
0:9:58,14 --> 0:10:0,72
Michael: I want to come back to
that, but before we do, The all

195
0:10:0,72 --> 0:10:4,26
visible is really important for
index only scan performance,

196
0:10:4,34 --> 0:10:4,84
right?

197
0:10:5,46 --> 0:10:14,1
So if an index only scan can know
that every single tuple on

198
0:10:14,1 --> 0:10:19,18
that page is visible to all transactions
then it can serve the

199
0:10:20,02 --> 0:10:23,3
result it finds in the index because
it knows it can't have changed.

200
0:10:23,42 --> 0:10:27,72
So that's a really neat optimization
without having to do what

201
0:10:27,72 --> 0:10:28,94
it calls a heap fetch.

202
0:10:28,94 --> 0:10:31,56
So it's like fetching the data
from the table instead of from

203
0:10:31,56 --> 0:10:32,2
the index.

204
0:10:32,2 --> 0:10:34,86
So if you ever if you see an index
only scan that ends up with

205
0:10:34,86 --> 0:10:38,92
a lot of heap fetches that's because
Postgres couldn't...

206
0:10:39,38 --> 0:10:43,14
Yeah it might it might be that
information was in the index but

207
0:10:43,14 --> 0:10:46,8
because it Postgres didn't know
for sure that it hadn't changed

208
0:10:46,8 --> 0:10:48,66
it still went and got it from the
heap.

209
0:10:48,66 --> 0:10:51,04
So it might be the exact same information
that it already had

210
0:10:51,04 --> 0:10:53,6
from the index, but there's this
inefficiency if vacuum hasn't

211
0:10:53,6 --> 0:10:54,76
run recently enough.

212
0:10:54,76 --> 0:10:58,62
And it only takes, because it's
that bit 0 or 1, it only takes

213
0:10:58,62 --> 0:11:2,88
1 tuple on the page to have changed
for any of the tuples on

214
0:11:2,88 --> 0:11:6,36
that page to lose out on index
only scans.

215
0:11:6,58 --> 0:11:9,34
Nikolay: I think if information
is not in the index, you mean

216
0:11:9,34 --> 0:11:12,9
if we select columns which are
not part of index definition,

217
0:11:12,94 --> 0:11:15,62
it will be index scan immediately,
not index only scan.

218
0:11:15,62 --> 0:11:16,52
Michael: Yeah, of course.

219
0:11:16,52 --> 0:11:19,1
Nikolay: And index only scan already
means that information is

220
0:11:19,1 --> 0:11:19,6
present.

221
0:11:19,9 --> 0:11:25,54
Information means values, but what
is never present in indexes

222
0:11:26,32 --> 0:11:27,7
is visibility information.

223
0:11:27,7 --> 0:11:29,16
This xmin, xmax.

224
0:11:29,88 --> 0:11:35,32
So For that we need heap, but if
visibility map bit says for

225
0:11:35,32 --> 0:11:38,0
this page it's all visible, this
can be skipped.

226
0:11:38,24 --> 0:11:42,26
So if the fetch's number goes lower,
ideally it should be 0,

227
0:11:42,26 --> 0:11:45,26
then it's a perfect situation,
it's a true index-only scan.

228
0:11:45,3 --> 0:11:49,4
If it's high, it approaches the
performance of index scan, which

229
0:11:49,4 --> 0:11:51,26
is like always consulting heap.

230
0:11:52,28 --> 0:11:55,52
Yeah, naming should be better here,
but my position on naming,

231
0:11:55,52 --> 0:11:59,78
I'm always like, index scan could
be renamed to something which

232
0:11:59,78 --> 0:12:3,58
would be explicitly mentioning
that heap is always consulted.

233
0:12:4,46 --> 0:12:6,36
Anyway, this is a different topic.

234
0:12:6,7 --> 0:12:11,4
I think we covered quite well what
autovacuum does, and again,

235
0:12:11,4 --> 0:12:13,74
the main job is just garbage collection.

236
0:12:14,06 --> 0:12:19,92
Also, I wanted to mention, autovacuum
is improving a lot from

237
0:12:19,92 --> 0:12:20,94
release to release.

238
0:12:21,42 --> 0:12:23,4
And it's enabled by default, of
course.

239
0:12:24,64 --> 0:12:27,02
It should be disabled only by experts.

240
0:12:27,12 --> 0:12:32,58
For example, PgQ, Skype engineers
decided to disable autovacuum

241
0:12:32,68 --> 0:12:34,44
for queue partitions.

242
0:12:35,32 --> 0:12:38,8
And it's also disabled in PgQue tool,
which is built on top of

243
0:12:38,8 --> 0:12:39,3
PgQ.

244
0:12:39,52 --> 0:12:40,82
Name is awful, right?

245
0:12:43,14 --> 0:12:47,54
So anyway, there autovacuum is
disabled, but normally it should

246
0:12:47,54 --> 0:12:50,0
be never disabled, and it's enabled
by default.

247
0:12:50,28 --> 0:12:52,08
And it's done, drop, and so on.

248
0:12:52,58 --> 0:12:57,26
So it's just a garbage collection
plus additional features and

249
0:12:57,26 --> 0:13:1,92
it's improving from release to
release and Funny fact I recently

250
0:13:2,02 --> 0:13:6,58
purchased some Chinese modern auto
vacuum for home with washing

251
0:13:6,58 --> 0:13:6,98
feature.

252
0:13:6,98 --> 0:13:10,28
It's very similar, like advancement,
like I had a few years ago,

253
0:13:10,28 --> 0:13:13,44
I had a couple of them and they
were now there, like so many

254
0:13:13,44 --> 0:13:15,6
features, so it became much smarter.

255
0:13:15,86 --> 0:13:17,78
And they have multiple functions.

256
0:13:18,28 --> 0:13:20,46
So I enjoy how it works really.

257
0:13:20,92 --> 0:13:22,98
But it requires some tuning as
well.

258
0:13:23,6 --> 0:13:26,08
Michael: Yeah, I was gonna say,
Bruce brought this up in our

259
0:13:26,08 --> 0:13:27,16
episode with him, didn't he?

260
0:13:27,16 --> 0:13:30,82
He said people complain about vacuum
and bloat with Postgres

261
0:13:31,4 --> 0:13:34,12
and MVCC, but he's saying, which
version are you complaining

262
0:13:34,12 --> 0:13:34,44
about?

263
0:13:34,44 --> 0:13:36,68
Because it is continually getting
better.

264
0:13:36,82 --> 0:13:42,9
But I would say that some of the
defaults are still very conservative,

265
0:13:43,3 --> 0:13:47,12
or maybe make sense at smaller
table sizes.

266
0:13:47,46 --> 0:13:51,22
And they do have scale factors
built in, but they make less and

267
0:13:51,22 --> 0:13:54,52
less sense I think as tables get
bigger, some of them.

268
0:13:54,96 --> 0:13:59,34
Nikolay: So my position on defaults,
they are all BS defaults.

269
0:14:1,56 --> 0:14:8,5
They are like for, I call it Postgres,
kitchen kettle Postgres,

270
0:14:8,64 --> 0:14:13,2
which should have, I don't know,
like 1 gigabyte memory and some

271
0:14:13,2 --> 0:14:16,08
Raspberry Pi or something, a smart
pod or something.

272
0:14:16,08 --> 0:14:16,98
This is it.

273
0:14:17,22 --> 0:14:22,04
For them it's okay, but for any
even like small cluster with

274
0:14:22,04 --> 0:14:28,08
8 cores, 16 gig of memory, it's
not enough at all.

275
0:14:28,26 --> 0:14:32,02
And the settings can be split to
3 categories for simplicity.

276
0:14:32,86 --> 0:14:34,78
Number of workers, which requires
restart.

277
0:14:34,78 --> 0:14:37,8
The only thing which requires restarted
number is number of workers,

278
0:14:37,8 --> 0:14:39,18
which is 3 by default.

279
0:14:39,52 --> 0:14:41,96
If you have 8 cores, 3 maybe is
okay.

280
0:14:41,96 --> 0:14:42,68
It's okay.

281
0:14:42,88 --> 0:14:53,46
If you have 32, 64, 48, 96 cores,
3 is not enough, but we see

282
0:14:53,46 --> 0:14:54,44
it all the time.

283
0:14:54,6 --> 0:14:57,54
People come to us, 96 cores, 3
workers.

284
0:14:58,38 --> 0:14:59,44
This is 1 thing.

285
0:14:59,48 --> 0:15:0,92
Michael: You say it's not enough.

286
0:15:1,84 --> 0:15:4,9
I think you're right in the sense
it's not a sensible default.

287
0:15:4,9 --> 0:15:8,14
Like why not give it more in case
it needs it?

288
0:15:8,14 --> 0:15:8,64
Nikolay: Right.

289
0:15:9,14 --> 0:15:12,04
Michael: But as you said, sometimes
people come to you and they've

290
0:15:12,04 --> 0:15:15,92
only had 3 workers the whole time
and somehow it's not fallen

291
0:15:15,92 --> 0:15:16,42
over.

292
0:15:18,72 --> 0:15:21,48
Nikolay: It's grinding through
challenges.

293
0:15:21,53 --> 0:15:22,03
Yeah.

294
0:15:22,08 --> 0:15:26,82
Like super bloated tables, super
bloated indexes, and it's a

295
0:15:26,82 --> 0:15:30,68
lot of dirt to clean, which is
great for us.

296
0:15:30,68 --> 0:15:32,54
We have opportunity to help them.

297
0:15:32,64 --> 0:15:35,86
Instead of framing, oh, how bad
it is, I actually have it, and

298
0:15:35,86 --> 0:15:37,52
it's actually our product strategy.

299
0:15:38,16 --> 0:15:41,52
We frame it, oh, great, we can
help you really quickly, really

300
0:15:41,52 --> 0:15:42,02
quickly.

301
0:15:42,04 --> 0:15:43,94
Either way, we have low-hanging
fruit.

302
0:15:43,94 --> 0:15:44,68
Let's go.

303
0:15:45,06 --> 0:15:47,8
With our tooling and methodologies,
it's super simple.

304
0:15:47,8 --> 0:15:52,36
This low-hanging fruit, let's go,
and your database will fill

305
0:15:52,36 --> 0:15:54,12
and breathe quite soon.

306
0:15:54,28 --> 0:15:55,14
Much better.

307
0:15:55,24 --> 0:15:59,24
You can even consider downgrading
or postponing upgrade for a

308
0:15:59,24 --> 0:16:0,74
couple of years after this.

309
0:16:0,74 --> 0:16:1,24
Yeah.

310
0:16:1,38 --> 0:16:2,42
And this is great.

311
0:16:2,78 --> 0:16:3,94
Yeah, this is 1 thing.

312
0:16:3,94 --> 0:16:6,02
And this is super important thing,
we just discussed.

313
0:16:6,02 --> 0:16:10,92
This is how garbage collection
works and updates, deletes, and

314
0:16:10,92 --> 0:16:11,82
sometimes inserts.

315
0:16:11,82 --> 0:16:13,44
They do just part of the job.

316
0:16:14,28 --> 0:16:14,78
Right?

317
0:16:14,82 --> 0:16:20,98
And these poor autovacuum workers
need to do the rest of the

318
0:16:20,98 --> 0:16:21,48
job.

319
0:16:22,36 --> 0:16:23,6
Michael: The tidying up afterwards,

320
0:16:23,64 --> 0:16:24,14
Nikolay: yeah.

321
0:16:24,24 --> 0:16:28,16
You have 1,000 max_connections,
meaning you have up to 1,000

322
0:16:28,32 --> 0:16:34,22
back-end workers which produce
all those dead tuples.

323
0:16:34,94 --> 0:16:37,54
And only 3 that should catch up
with them.

324
0:16:37,54 --> 0:16:38,56
It's nonsense, right?

325
0:16:38,56 --> 0:16:40,1
It feels like it's nonsense.

326
0:16:40,28 --> 0:16:42,1
So it should be tuned.

327
0:16:42,1 --> 0:16:46,4
And simple rule, like we say, like
actually let's consider like

328
0:16:46,4 --> 0:16:51,94
25% of vCPU count, But we should
do it very properly, thinking

329
0:16:51,94 --> 0:16:54,22
about memory management and so
on.

330
0:16:54,78 --> 0:16:58,78
I know there is opposition to this
view, but this is my position.

331
0:16:58,78 --> 0:17:1,64
It's a simple case, just let's
give more workers, especially

332
0:17:1,64 --> 0:17:2,58
if you have partitioning.

333
0:17:3,68 --> 0:17:10,08
Because, fun fact, a worker processing
a table, if it's a huge

334
0:17:10,08 --> 0:17:13,38
table, it will be processing it
sequentially, your table and

335
0:17:13,38 --> 0:17:14,92
its indexes, and that's it.

336
0:17:14,92 --> 0:17:18,34
If it's a partitioned table, it
can be distributed.

337
0:17:18,4 --> 0:17:21,42
Each partition can be, you can
parallelize this, right?

338
0:17:21,6 --> 0:17:24,98
So it makes sense to have more
workers if you have more partitions.

339
0:17:25,6 --> 0:17:28,62
And it's great to have partitions
and more workers together.

340
0:17:28,62 --> 0:17:29,78
This is the best situation.

341
0:17:30,18 --> 0:17:31,74
And 2 more categories.

342
0:17:32,22 --> 0:17:34,66
1 defines frequency of revisiting.

343
0:17:35,82 --> 0:17:40,46
Frequency, how frequent workers
come to do stuff.

344
0:17:40,84 --> 0:17:45,04
And another defines how fast they
can throughput, basically,

345
0:17:45,04 --> 0:17:46,04
or throttling.

346
0:17:46,38 --> 0:17:47,42
I think

347
0:17:47,42 --> 0:17:49,66
Michael: throttling's the better
way of thinking about it, yeah.

348
0:17:49,66 --> 0:17:50,82
Nikolay: Throttling, yeah, limiting.

349
0:17:51,22 --> 0:17:53,22
It's cost_limit and cost_delay.

350
0:17:53,32 --> 0:17:56,6
And how often they visit defines
by scale factors and threshold,

351
0:17:56,6 --> 0:17:59,84
but I'm okay to think about only
scale factors, actually.

352
0:18:0,52 --> 0:18:2,22
At first glance, it's enough.

353
0:18:2,86 --> 0:18:6,02
So we have 3 scale factors today,
right?

354
0:18:6,02 --> 0:18:10,22
Or maybe there is also in Postgres
18, there is

355
0:18:10,58 --> 0:18:12,22
autovacuum_vacuum_max_threshold.

356
0:18:12,88 --> 0:18:17,22
This is a new stuff in Postgres
18, which changes the picture.

357
0:18:17,82 --> 0:18:23,22
So usually we talked about only
scale factor and threshold.

358
0:18:23,22 --> 0:18:28,68
Scale factor by default, 3 scale
factors, but the basic 1 is

359
0:18:28,78 --> 0:18:31,5
autovacuum_vacuum_scale_factor,
right?

360
0:18:31,98 --> 0:18:34,34
And it is either 10 or 20 percent.

361
0:18:34,54 --> 0:18:37,08
So I think 20 percent, right?

362
0:18:37,36 --> 0:18:40,76
Michael: Yeah, so I suppose because
the threshold by default

363
0:18:40,76 --> 0:18:43,04
is 0.2, right, so 20 percent.

364
0:18:43,04 --> 0:18:45,2
Let's say you've got 1000000000
rows in the table.

365
0:18:45,2 --> 0:18:49,5
It doesn't wait to get to 200 million,
it starts at 100 million.

366
0:18:49,9 --> 0:18:53,04
Nikolay: Yeah, so we don't allow
more than 100 million, which

367
0:18:53,04 --> 0:18:54,04
is a huge number.

368
0:18:54,38 --> 0:18:55,68
Maybe huge, maybe not.

369
0:18:55,68 --> 0:18:57,98
100 million dead tuples in the
table.

370
0:18:58,08 --> 0:18:59,94
Michael: It's quite a lot though,
yeah.

371
0:19:0,2 --> 0:19:4,8
Nikolay: Yeah, so the basic is
the vacuum scale factor.

372
0:19:5,86 --> 0:19:7,06
This is the first thing.

373
0:19:7,06 --> 0:19:10,88
And it's 20% by default, which
means you need to accumulate 20%

374
0:19:11,12 --> 0:19:15,04
of dead tuples in the table before
it starts garbage collection.

375
0:19:15,32 --> 0:19:17,6
When I just say this, I had recent
experiences.

376
0:19:18,42 --> 0:19:21,76
When I said this to some startup
CTO who which is growing and

377
0:19:21,76 --> 0:19:26,5
so on he like, oh We don't have
garbage collection before 20%

378
0:19:26,64 --> 0:19:30,9
of dirt accumulated This is

379
0:19:32,68 --> 0:19:35,2
Michael: Of course when when things
are small, it's not a big

380
0:19:35,2 --> 0:19:35,6
deal.

381
0:19:35,6 --> 0:19:39,16
But when things get big, this is
just a huge number.

382
0:19:39,16 --> 0:19:40,36
And it's not just...

383
0:19:41,04 --> 0:19:41,7
Nikolay: Yeah, it's...

384
0:19:41,94 --> 0:19:46,96
Let's say you have like almost
terabyte size table in terms of

385
0:19:46,96 --> 0:19:52,1
size, billion rows unpartitioned,
which is common, I see this.

386
0:19:53,0 --> 0:19:58,66
20% means 200 million rows, dead
tuples, right?

387
0:19:58,66 --> 0:20:2,54
Which is by the way above that
threshold introduced in Postgres

388
0:20:2,56 --> 0:20:3,06
18.

389
0:20:4,4 --> 0:20:5,26
So, it's like, wow.

390
0:20:5,34 --> 0:20:7,74
Michael: But it's not just storage,
right?

391
0:20:7,74 --> 0:20:12,02
It's also polluting caches and
indexes.

392
0:20:12,34 --> 0:20:13,12
It's everything.

393
0:20:13,12 --> 0:20:14,6
Nikolay: Oh yes, oh yes.

394
0:20:14,6 --> 0:20:15,36
This is big point.

395
0:20:15,36 --> 0:20:18,82
And actually, let me add 1 important
notice here.

396
0:20:19,7 --> 0:20:23,94
Usually, it's not a big deal for
smaller tables.

397
0:20:24,52 --> 0:20:26,9
But in some cases, we see local...

398
0:20:27,98 --> 0:20:31,86
So if, for example, 1 row updated
many times, thousands of times.

399
0:20:32,36 --> 0:20:36,14
For 1 logical row, you have many
versions.

400
0:20:37,9 --> 0:20:44,74
And globally, like table is big,
but for this not lucky row,

401
0:20:45,3 --> 0:20:49,88
You have so many versions and they
can be, might be scattered

402
0:20:49,9 --> 0:20:51,0
among many pages.

403
0:20:51,94 --> 0:20:56,74
So, they are sitting there as data
tuples and for this specific

404
0:20:56,8 --> 0:20:59,18
query select where id equals something.

405
0:21:0,58 --> 0:21:4,16
This means that all of them need
to be fetched to be checked

406
0:21:4,16 --> 0:21:7,54
in terms of visibility for this
transaction and it degrades for

407
0:21:7,54 --> 0:21:10,08
a particular ID, like local degradation.

408
0:21:11,08 --> 0:21:15,88
Lots of queries are fine, but this
unlucky because it had a lot

409
0:21:15,88 --> 0:21:17,82
of updates or something, right?

410
0:21:18,08 --> 0:21:19,92
It became really degraded.

411
0:21:19,92 --> 0:21:21,82
And it's like, what's happening
here?

412
0:21:21,82 --> 0:21:25,92
This is where p99s or something
are good to have because average

413
0:21:25,92 --> 0:21:29,1
will hide it, average latency and
so on.

414
0:21:30,06 --> 0:21:34,4
And this means you should be vacuuming
more frequently, even

415
0:21:34,4 --> 0:21:35,46
on smaller tables.

416
0:21:36,28 --> 0:21:41,82
So this local degradation due to
dead tuple for particular logical

417
0:21:41,82 --> 0:21:44,62
rows, dead tuple count is high.

418
0:21:45,6 --> 0:21:47,78
So it's happening, and it's happening
silently.

419
0:21:47,9 --> 0:21:48,78
That's the problem.

420
0:21:48,92 --> 0:21:52,8
And tooling, which is like lacking
p99s and so on, is not helping

421
0:21:52,8 --> 0:21:53,72
us to understand.

422
0:21:53,8 --> 0:21:58,56
That's why still logging of slow
queries helps, and auto_explain

423
0:21:58,84 --> 0:22:5,28
helps to catch these like edge
cases and start analyzing why

424
0:22:5,28 --> 0:22:6,08
it's happening.

425
0:22:6,22 --> 0:22:9,94
The next day you select the same
row, maybe it's already vacuumed

426
0:22:9,96 --> 0:22:11,46
and degradation has gone.

427
0:22:12,98 --> 0:22:18,96
But in that moment, if you go back
in time and select it, you

428
0:22:18,96 --> 0:22:20,24
see so many buffers.

429
0:22:20,74 --> 0:22:23,68
This particular query, it's very
like it's primary key lookup,

430
0:22:23,68 --> 0:22:25,14
but too many buffers somehow.

431
0:22:25,14 --> 0:22:25,82
That's why.

432
0:22:26,14 --> 0:22:29,8
So it's important to feel it because
it's really like subtle

433
0:22:29,8 --> 0:22:30,14
problem.

434
0:22:30,14 --> 0:22:33,06
It's hard to catch it if you don't
know where to look at.

435
0:22:33,74 --> 0:22:34,24
Yeah.

436
0:22:34,54 --> 0:22:35,38
And you're right.

437
0:22:35,38 --> 0:22:36,36
So scale factors.

438
0:22:36,58 --> 0:22:39,66
And since Postgres 13, there is
also autovacuum_vacuum_insert_scale_factor,

439
0:22:39,66 --> 0:22:42,54
and there is also
scale factor for analyzing.

440
0:22:43,52 --> 0:22:44,9
For insert, it's really great.

441
0:22:44,9 --> 0:22:49,94
I remember Darafei, the guy who,
from Belarus, I think, he introduced

442
0:22:49,94 --> 0:22:50,14
it.

443
0:22:50,14 --> 0:22:52,12
It was so obvious, why didn't we
have it?

444
0:22:52,12 --> 0:22:55,84
We insert a lot with double table
in size, but vacuum doesn't

445
0:22:55,84 --> 0:22:56,34
come.

446
0:22:56,4 --> 0:23:0,04
And we need vacuum not to clean
up the tuples after successful

447
0:23:0,08 --> 0:23:3,66
insert, but to rebuild visibility
maps, first of all.

448
0:23:4,12 --> 0:23:8,46
And yeah, it is great because if
you have select count or something,

449
0:23:8,46 --> 0:23:11,76
you want index-only scan to be
with low heap fetches, as you

450
0:23:11,76 --> 0:23:12,26
mentioned.

451
0:23:12,44 --> 0:23:15,84
So anyway, there are these scale
factors, And it's enough at

452
0:23:15,84 --> 0:23:17,54
first glance to look only at them.

453
0:23:17,72 --> 0:23:21,64
Yes, you can go deeper and think
about thresholds and how additional

454
0:23:21,72 --> 0:23:24,78
tuning, but scale factors is already
enough.

455
0:23:25,34 --> 0:23:30,22
You want them to be really low
in OLTP workloads, just 1%.

456
0:23:31,32 --> 0:23:35,08
Sometimes 2%, sometimes 1%, I see
people choose, but 1% is is

457
0:23:35,08 --> 0:23:35,96
simple rule.

458
0:23:36,66 --> 0:23:40,58
Set it to 1 percent for all scale
factors and benefit.

459
0:23:40,92 --> 0:23:43,9
Michael: And to give people an
idea of how drastic a change that

460
0:23:43,9 --> 0:23:48,3
is, these scale factors are 20
percent for the vacuum threshold,

461
0:23:48,52 --> 0:23:52,04
10 percent for the analyze threshold,
and I think 20% also for

462
0:23:52,04 --> 0:23:53,48
the insert scale factor.

463
0:23:53,48 --> 0:23:53,88
Nikolay: Yes.

464
0:23:53,88 --> 0:23:54,34
So

465
0:23:54,34 --> 0:23:58,74
Michael: dropping from 20 to 1
might feel like a huge change,

466
0:23:58,74 --> 0:24:2,44
it is quite a huge change, But
you're not repeating the same

467
0:24:2,44 --> 0:24:2,98
work, right?

468
0:24:2,98 --> 0:24:5,42
Like it's not, there's a tiny bit
of repetition.

469
0:24:6,2 --> 0:24:10,84
Yeah, it's, this is a slight difference
to analyze, isn't it,

470
0:24:10,84 --> 0:24:14,62
in that if you run vacuum twice
in quick succession on the same

471
0:24:14,62 --> 0:24:17,8
table, It's not doing all of the
same work again.

472
0:24:17,8 --> 0:24:19,12
It's done a lot of the cleanup.

473
0:24:19,12 --> 0:24:20,7
It's like tidying a room.

474
0:24:20,74 --> 0:24:21,4
Nikolay: Some work

475
0:24:21,54 --> 0:24:21,82
Michael: can

476
0:24:21,82 --> 0:24:22,82
Nikolay: be done again.

477
0:24:22,82 --> 0:24:25,92
For example, it's very nuanced
here.

478
0:24:25,92 --> 0:24:26,92
It's super interesting.

479
0:24:26,98 --> 0:24:29,8
Like you mentioned, it's not only
about data storage.

480
0:24:29,8 --> 0:24:32,56
It's polluting memory, WAL.

481
0:24:32,78 --> 0:24:34,84
WAL means replication, WAL backups.

482
0:24:35,5 --> 0:24:36,64
It's polluting everything.

483
0:24:36,76 --> 0:24:40,14
And memory is super, memory is
expensive these days, right?

484
0:24:42,04 --> 0:24:45,1
So we should optimize, not about
data storage, we should think

485
0:24:45,1 --> 0:24:49,46
about how many pages we need to
keep in this working set.

486
0:24:49,74 --> 0:24:55,58
But the ideal situation when you
raise the workers, make a vacuum

487
0:24:55,58 --> 0:24:59,62
to visit us frequently with lowering
scale factors, all of them

488
0:24:59,62 --> 0:25:2,5
to say 1% is very rough rule here.

489
0:25:2,94 --> 0:25:7,08
But also partition quite well,
because in this case some partitions,

490
0:25:7,2 --> 0:25:11,26
especially if it's historic data,
they will be processed with

491
0:25:11,26 --> 0:25:15,52
marked all visible, all frozen,
and very rarely those pages will

492
0:25:15,52 --> 0:25:16,2
be touched.

493
0:25:17,52 --> 0:25:21,24
So vacuuming will be like a breeze,
like skipping, right?

494
0:25:21,34 --> 0:25:27,0
Unlike if it's a huge, messy heap
with terabyte in size or so,

495
0:25:27,34 --> 0:25:30,6
and any page can be touched at
any given time.

496
0:25:31,1 --> 0:25:33,58
It means it will be need to process
again.

497
0:25:34,08 --> 0:25:38,24
Of course it means that if you
have infrequent vacuuming of a

498
0:25:38,24 --> 0:25:43,26
huge table, of course you're doing
work less, because maybe this

499
0:25:43,26 --> 0:25:46,28
page was visited multiple times
before it was vacuumed.

500
0:25:47,02 --> 0:25:51,6
And if you start vacuuming frequently
and between 2 processing

501
0:25:51,7 --> 0:25:56,82
these pages are revisited by some
rates, then it needs to be

502
0:25:56,82 --> 0:25:57,76
vacuumed again.

503
0:25:58,18 --> 0:26:2,96
So it's multiple processing of
the same page versus a single

504
0:26:2,96 --> 0:26:3,34
processing.

505
0:26:3,34 --> 0:26:7,12
So we cannot say it will be, but
it's highly optimized.

506
0:26:7,12 --> 0:26:8,34
Vacuum is highly optimized.

507
0:26:9,02 --> 0:26:12,9
But if you have a huge table, not
partition, maybe it will be

508
0:26:12,9 --> 0:26:17,16
like frequency, high frequency
vacuuming will cost you some extra,

509
0:26:17,16 --> 0:26:18,58
but it's worth it usually.

510
0:26:18,94 --> 0:26:21,38
So there are nuances here and trade-offs,
of course.

511
0:26:21,58 --> 0:26:27,42
But usually in OLTP, we very strongly
recommend to lower down

512
0:26:27,44 --> 0:26:30,26
scale factors so vacuuming happens
more frequently.

513
0:26:30,78 --> 0:26:35,2
And the fact is that if you're
on managed service, I see it's

514
0:26:35,2 --> 0:26:36,0
quite common.

515
0:26:36,66 --> 0:26:37,86
This is a big problem.

516
0:26:38,26 --> 0:26:39,9
RDS included, for example.

517
0:26:40,4 --> 0:26:43,26
They don't care about this somehow
and they keep defaults.

518
0:26:44,5 --> 0:26:48,58
I would like to hear their reasoning
why they don't proactively

519
0:26:48,8 --> 0:26:50,3
tune it for the customers.

520
0:26:51,5 --> 0:26:52,96
And because I

521
0:26:52,96 --> 0:26:54,92
Michael: mean, I mean, I mean,
I'm just makes, because they're

522
0:26:54,92 --> 0:26:55,92
a scale factor, right?

523
0:26:55,92 --> 0:26:57,72
They can make sense at any scale,
right?

524
0:26:57,72 --> 0:27:1,32
Like it, it's fine when you're
small, These are inexpensive things

525
0:27:1,32 --> 0:27:3,94
to run on small tables, and it
works when it's big.

526
0:27:4,12 --> 0:27:7,54
There's no reason not to tune it
from the start for all customers.

527
0:27:7,78 --> 0:27:11,38
You don't have to be careful about
which instance size.

528
0:27:11,38 --> 0:27:14,96
Nikolay: And I don't complain,
because as I said, I enjoy when

529
0:27:14,96 --> 0:27:16,08
I see the problem.

530
0:27:16,08 --> 0:27:20,28
And I'm excited to help and have
low-hanging fruit and see

531
0:27:20,28 --> 0:27:22,54
how health next day is much better.

532
0:27:22,72 --> 0:27:25,96
But of course we need also automated
reindexing, we need the

533
0:27:25,96 --> 0:27:28,38
pg_repack to be used and so on.

534
0:27:28,86 --> 0:27:32,7
By the way, we mentioned that Postgres
19 will have...

535
0:27:33,4 --> 0:27:33,54
Yeah.

536
0:27:33,54 --> 0:27:34,62
Yeah, it will have...

537
0:27:36,28 --> 0:27:37,8
Michael: REPACK and REPACK CONCURRENTLY.

538
0:27:38,68 --> 0:27:39,86
Nikolay: That's awesome.

539
0:27:39,96 --> 0:27:43,84
And we should somehow revisit this
topic, maybe inviting the

540
0:27:43,84 --> 0:27:45,6
guy who made it, right?

541
0:27:45,9 --> 0:27:47,01
Michael: Yeah, I think it was...

542
0:27:47,01 --> 0:27:47,38
Antonín, right?

543
0:27:47,38 --> 0:27:48,48
Yeah, Antonín was...

544
0:27:48,48 --> 0:27:51,92
We had him on, didn't we, to talk
about pg_squeeze, which inspired

545
0:27:52,04 --> 0:27:52,42
the work.

546
0:27:52,42 --> 0:27:56,88
Nikolay: Remember in the very end
of the episode, I learned about

547
0:27:56,88 --> 0:27:59,68
these plans to make VACUUM FULL
concurrently basically, which

548
0:27:59,68 --> 0:28:2,06
is like became REPACK CONCURRENTLY.

549
0:28:2,24 --> 0:28:5,8
I couldn't believe, I had like
doubts, is it like, but here we

550
0:28:5,8 --> 0:28:10,84
go, like this is happening a year
later, it's planned to be released,

551
0:28:10,84 --> 0:28:13,66
I hope it won't be reverted, it's
a super important feature to

552
0:28:13,66 --> 0:28:15,58
have, it's interesting how out
of the box.

553
0:28:15,58 --> 0:28:20,02
Michael: I have to admit when I,
yeah, I saw that REPACK, they

554
0:28:20,02 --> 0:28:24,28
introduced effectively a rename
of VACUUM FULL to REPACK and that

555
0:28:24,28 --> 0:28:26,98
was going to make 19 but it didn't
have a concurrently option

556
0:28:26,98 --> 0:28:29,6
and I was like, oh, that's such
a shame but it makes sense.

557
0:28:29,6 --> 0:28:30,76
Nikolay: Another year of waiting.

558
0:28:31,68 --> 0:28:34,28
Michael: And then about a week
later, REPACK CONCURRENTLY gets

559
0:28:34,28 --> 0:28:35,2
committed as well.

560
0:28:35,2 --> 0:28:36,22
So yeah, that was great.

561
0:28:36,22 --> 0:28:40,32
Nikolay: It's yeah, it was a mood
swing for me as well.

562
0:28:40,32 --> 0:28:41,38
It was like, oh no.

563
0:28:41,38 --> 0:28:43,14
Oh, yeah.

564
0:28:43,14 --> 0:28:45,42
So anyway, back to scale factors.

565
0:28:45,66 --> 0:28:51,54
This is probably 1 of the most
important to lower scale factors

566
0:28:51,54 --> 0:28:55,46
for OLTP workloads and benefit
from more frequent vacuuming

567
0:28:56,6 --> 0:28:58,24
and analyzing as well, right?

568
0:28:58,26 --> 0:29:0,72
Michael: Just a question, is there
any stage that people are

569
0:29:0,72 --> 0:29:2,54
at where you wouldn't recommend
this.

570
0:29:2,64 --> 0:29:5,38
I was thinking when should people
be thinking about these things

571
0:29:5,38 --> 0:29:8,6
but I think those 2 are such simple
or those 3 let's say are

572
0:29:8,6 --> 0:29:11,96
such simple settings to change
you could change them from the

573
0:29:11,96 --> 0:29:14,18
start and never think about it
again like

574
0:29:14,18 --> 0:29:14,8
Nikolay: globally I

575
0:29:14,8 --> 0:29:15,52
Michael: was thinking.

576
0:29:15,72 --> 0:29:20,34
Nikolay: This is complex and this
is complex thing in terms of

577
0:29:20,74 --> 0:29:24,98
we cannot talk about autovacuum
tuning completely without analyzing

578
0:29:25,38 --> 0:29:28,58
what's blocking the work.

579
0:29:28,78 --> 0:29:29,52
Michael: If anything.

580
0:29:30,34 --> 0:29:33,28
Nikolay: Yes and that's why I'm
like we recently developed a

581
0:29:33,28 --> 0:29:37,2
dashboard for xmin horizon analysis
because we we probably need

582
0:29:37,2 --> 0:29:41,98
to revisit it once again in terms
of long transactions versus

583
0:29:42,42 --> 0:29:47,16
pure xmin horizon blocker analysis
and autovacuum throughput

584
0:29:47,16 --> 0:29:50,46
analysis and again like this I
cannot recommend enough Laurenz

585
0:29:50,46 --> 0:29:54,26
Albe recent blog post about how
he monitors autovacuum.

586
0:29:55,68 --> 0:29:58,94
Combining with what, how we monitor
autovacuum, I think it's

587
0:29:58,94 --> 0:30:3,82
much like we leveled up monitoring
of autovacuum blockers basically,

588
0:30:3,82 --> 0:30:4,04
right?

589
0:30:4,04 --> 0:30:8,42
So xmin horizon, there are 5 reasons
why it can be blocked.

590
0:30:9,52 --> 0:30:14,6
And if you keep those problems
unresolved for hours, or sometimes

591
0:30:14,6 --> 0:30:19,08
dozens of hours, tuning autovacuum
to run more frequently, especially

592
0:30:19,08 --> 0:30:23,86
if you reduce also autovacuum_naptime, how
long it sleeps between runs,

593
0:30:24,16 --> 0:30:25,18
and raised workers.

594
0:30:25,84 --> 0:30:28,62
And also, some problems might happen.

595
0:30:28,62 --> 0:30:32,06
For example, it might start consuming
too much resources just

596
0:30:32,06 --> 0:30:33,14
trying to do something.

597
0:30:33,14 --> 0:30:34,48
It cannot do something.

598
0:30:34,7 --> 0:30:38,22
And also, logging can be, you can
have observer effect.

599
0:30:38,8 --> 0:30:41,82
If there is a, like it tries to
do something, it cannot, but

600
0:30:41,82 --> 0:30:43,78
it reports and many workers report.

601
0:30:43,78 --> 0:30:47,92
And if you, at extreme, you have
log_autovacuum_min_duration

602
0:30:48,04 --> 0:30:51,3
set to 0, meaning all attempts
are logged.

603
0:30:51,42 --> 0:30:55,44
In this case, if you have 25 workers,
for example, and they cannot

604
0:30:55,44 --> 0:30:59,7
do, and they autovacuum_naptime 1 second,
it's a storm of logs.

605
0:30:59,72 --> 0:31:1,96
This is 1 problem I can definitely
imagine.

606
0:31:1,96 --> 0:31:3,06
Michael: You mean 1 millisecond?

607
0:31:3,84 --> 0:31:6,6
Nikolay: No, by default autovacuum_naptime
is 30 seconds.

608
0:31:7,2 --> 0:31:9,94
Michael: What am I thinking of
that's 2 milliseconds by default?

609
0:31:10,2 --> 0:31:11,48
Nikolay: Oh, actually I was wrong.

610
0:31:11,48 --> 0:31:14,28
I'm looking at it right now, it's
60, 1 minute.

611
0:31:15,48 --> 0:31:20,58
autovacuum_naptime is by default 1 minute,
which is quite long in my opinion,

612
0:31:20,9 --> 0:31:25,58
so we usually say let's choose
a few seconds, but I admit aggressive

613
0:31:25,68 --> 0:31:30,06
tuning without resolution of xmin horizon
blockers might lead

614
0:31:30,06 --> 0:31:33,66
to storm of logging and resource
utilization as well.

615
0:31:33,7 --> 0:31:37,08
So this is what I can think of
from top of my head.

616
0:31:37,54 --> 0:31:40,76
Michael: Yeah, so sorry, I was
thinking of cost, there's a cost

617
0:31:40,76 --> 0:31:41,26
delay.

618
0:31:41,28 --> 0:31:43,94
Nikolay: Right, this is the last
thing we haven't touched.

619
0:31:43,94 --> 0:31:47,16
So as I said, There are like throughput
in terms of number of

620
0:31:47,16 --> 0:31:50,68
workers, there is frequency, how
often vacuum and analyzing happens,

621
0:31:50,68 --> 0:31:54,22
and also the remaining piece, big
piece is throttling.

622
0:31:54,28 --> 0:31:56,26
You said we should say throttling,
right?

623
0:31:56,26 --> 0:31:57,42
So 2 parameters.

624
0:31:58,48 --> 0:32:0,88
And in Postgres, who is responsible
for naming?

625
0:32:1,64 --> 0:32:8,32
So there is an attempt to save
on some defaults or something.

626
0:32:8,32 --> 0:32:11,42
There is some parameters that have
relationships.

627
0:32:13,66 --> 0:32:14,98
Inheritance, right?

628
0:32:15,04 --> 0:32:17,74
So there are 2 parameters,
cost_delay and cost_limit.

629
0:32:18,2 --> 0:32:21,6
There are vacuum_cost_delay,
vacuum_cost_limit, this pair.

630
0:32:21,88 --> 0:32:25,14
And by default, I think cost_delay
is 0, means it's unthrottled.

631
0:32:25,24 --> 0:32:27,34
If you run vacuum manually, it's
unthrottled.

632
0:32:28,26 --> 0:32:32,6
It tries to go full speed, usually
bounded by I/O, disk.

633
0:32:33,06 --> 0:32:33,56
Right?

634
0:32:34,7 --> 0:32:37,66
And there is also autovacuum_vacuum_cost_delay,

635
0:32:37,66 --> 0:32:40,08
autovacuum_vacuum_cost_limit, 2 parameters.

636
0:32:40,08 --> 0:32:45,2
1 of which I think
autovacuum_vacuum_cost_limit is set to minus

637
0:32:45,2 --> 0:32:50,02
1, which means it inherits from
vacuum_cost_limit, which is set

638
0:32:50,02 --> 0:32:52,7
to 2 milliseconds, right?

639
0:32:52,7 --> 0:32:56,8
It was 200 milliseconds and then
it was, or 20 milliseconds in

640
0:32:56,8 --> 0:32:58,04
the past before Postgres.

641
0:32:59,18 --> 0:33:1,62
Michael: Yeah, the delay is, I've
just looked it up:

642
0:33:1,62 --> 0:33:3,46
autovacuum_vacuum_cost_delay is

643
0:33:3,46 --> 0:33:3,98
Nikolay: 2 milliseconds.

644
0:33:4,14 --> 0:33:6,04
Yeah, I confused everything, I
see.

645
0:33:6,26 --> 0:33:10,84
So autovacuum_vacuum_cost_delay
is 2 milliseconds and it was

646
0:33:10,92 --> 0:33:15,44
before Postgres 12 it was 30, so
it became 2 in Postgres 12 which

647
0:33:15,44 --> 0:33:16,12
is great.

648
0:33:16,62 --> 0:33:20,88
And autovacuum_vacuum_cost_limit
is minus 1 means that it inherits

649
0:33:20,92 --> 0:33:25,3
from vacuum_cost_limit Which is
by default 200

650
0:33:26,54 --> 0:33:30,3
Michael: Which is like an arbitrary
unit of like work right and

651
0:33:30,3 --> 0:33:33,22
there's some various things that
make up, yeah.

652
0:33:33,34 --> 0:33:37,94
Nikolay: Yeah, I your CPU cycles,
there is some math.

653
0:33:38,36 --> 0:33:42,8
What I don't like about acquisition
of EDB, of SecondQuadrant

654
0:33:42,8 --> 0:33:45,64
by EDB, they just killed the blog.

655
0:33:45,78 --> 0:33:47,06
Blog was brilliant.

656
0:33:47,22 --> 0:33:50,78
And they had a great blog post
about some math.

657
0:33:50,8 --> 0:33:54,9
If you think only about the I/O part,
all defaults before Postgres

658
0:33:54,9 --> 0:33:59,2
12 yielded to 8 MB/s of reads.

659
0:34:0,06 --> 0:34:4,78
So with 120 milliseconds, it was
roughly 8 mb of reads.

660
0:34:4,94 --> 0:34:10,32
So bumping 20 milliseconds to 2
milliseconds, I remember it was

661
0:34:10,32 --> 0:34:14,28
first considered to raise cost
limit, but then decided to reduce

662
0:34:14,38 --> 0:34:17,66
frequency of this
throttling, right?

663
0:34:17,66 --> 0:34:20,1
Because it makes things smoother,
right?

664
0:34:21,74 --> 0:34:25,18
So going down from 20 milliseconds
to 2 milliseconds and maintaining

665
0:34:25,38 --> 0:34:29,55
the cost_limit 200, inherited from
vacuum_cost_limit.

666
0:34:29,55 --> 0:34:32,96
I don't like this, just put straightforward
defaults.

667
0:34:32,96 --> 0:34:33,98
It doesn't hurt.

668
0:34:34,12 --> 0:34:39,52
Minus 1 makes life of humans much
harder, much harder, right?

669
0:34:39,52 --> 0:34:44,02
Like it's, okay, anyway, maybe
I should just propose it.

670
0:34:44,02 --> 0:34:44,92
Maybe it wasn't proposed.

671
0:34:44,92 --> 0:34:45,92
Maybe it was proposed.

672
0:34:46,42 --> 0:34:47,02
I don't know.

673
0:34:47,02 --> 0:34:50,74
It's My battle over dozens of,
over decades already.

674
0:34:50,74 --> 0:34:55,52
This is happening decades, this
suffering from memorizing what

675
0:34:55,52 --> 0:34:56,52
minus 1 means.

676
0:34:56,52 --> 0:34:58,22
We forgot actually memory management.

677
0:34:58,52 --> 0:34:59,48
Fourth piece, right?

678
0:34:59,54 --> 0:35:1,26
There is minus 1 there as well.

679
0:35:1,28 --> 0:35:5,2
autovacuum_work_mem is minus 1
by default, which means you need

680
0:35:5,2 --> 0:35:8,16
to go and consult and there is
big interesting trick happened

681
0:35:8,16 --> 0:35:10,22
in Postgres 17, separate story.

682
0:35:10,44 --> 0:35:10,84
So that's

683
0:35:10,84 --> 0:35:12,38
Michael: maintenance_work_mem,
right?

684
0:35:12,84 --> 0:35:14,28
Nikolay: Yeah, maintenance_work_mem.

685
0:35:14,34 --> 0:35:17,28
If we have time, we can talk about
that as well and dive into

686
0:35:17,28 --> 0:35:18,04
it a little bit.

687
0:35:18,04 --> 0:35:22,7
So, minus 1 for
autovacuum_vacuum_cost_limit inherited 200,

688
0:35:23,2 --> 0:35:27,04
this like arbitrary cost, not arbitrary
actually, but some cost

689
0:35:27,04 --> 0:35:28,28
like 2 milliseconds.

690
0:35:28,78 --> 0:35:31,8
Since Postgres 12, meaning in all
current versions, it means by

691
0:35:31,8 --> 0:35:37,8
default that can just take reads
of 80 MB/s,

692
0:35:38,44 --> 0:35:41,92
roughly, which is not enough in
modern systems.

693
0:35:42,26 --> 0:35:46,88
Ah, and it's shared among all workers,
unless you start tuning

694
0:35:46,88 --> 0:35:50,68
a table level, which makes things
much more complex because then

695
0:35:50,68 --> 0:35:53,9
quotas with throttling becomes
local for this table, for this

696
0:35:53,9 --> 0:35:56,54
worker which is processing specific
table.

697
0:35:56,76 --> 0:35:58,26
I prefer not to go there.

698
0:35:58,26 --> 0:36:1,66
We can have a separate episode
for table level analysis.

699
0:36:2,84 --> 0:36:4,36
Tuning, table level tuning.

700
0:36:4,36 --> 0:36:9,62
So big picture is like by default
it has only 80 MB/s

701
0:36:9,62 --> 0:36:15,06
of reads and if you
have powerful, even EBS volume

702
0:36:15,06 --> 0:36:21,34
which I don't know it's a nitro,
old nitro architecture so it

703
0:36:21,34 --> 0:36:25,08
definitely NVMe, like it has
good throughput.

704
0:36:25,96 --> 0:36:27,9
80 is tiny among all workers.

705
0:36:27,9 --> 0:36:30,26
Again, this is garbage collection
for everything.

706
0:36:30,76 --> 0:36:34,68
So it should have several hundred
megabytes per second at least.

707
0:36:34,78 --> 0:36:38,66
But the good thing is, for example,
RDS fixes this, especially

708
0:36:38,72 --> 0:36:42,24
on Aurora we see enormous quota.

709
0:36:42,78 --> 0:36:46,36
Throttling there is quite good
in terms of like it has a lot

710
0:36:46,36 --> 0:36:47,14
of power.

711
0:36:47,72 --> 0:36:53,1
But in many other cases, we see
it needs attention and we need

712
0:36:53,1 --> 0:36:59,22
to give more like, how to say,
work capacity because otherwise

713
0:36:59,22 --> 0:37:1,7
it's too slow And that's not good.

714
0:37:4,3 --> 0:37:6,4
Michael: It never catches up, if
that makes sense.

715
0:37:8,16 --> 0:37:9,36
Nikolay: It's a kind of situation.

716
0:37:9,52 --> 0:37:13,18
You see all the workers you allocated,
they're always busy.

717
0:37:13,18 --> 0:37:16,2
This is number 1, how you can understand
that something is not

718
0:37:16,2 --> 0:37:16,7
okay.

719
0:37:17,02 --> 0:37:20,14
And second, we have a special report,
we have a query, I have

720
0:37:20,14 --> 0:37:23,1
a blog post about that, and we
also have it in our monitoring,

721
0:37:23,72 --> 0:37:27,98
like a queue or line of tables
which sit there and should be

722
0:37:27,98 --> 0:37:30,4
already processed but not processed
yet.

723
0:37:30,4 --> 0:37:34,98
And this, like, basically a wait
line for tables.

724
0:37:35,28 --> 0:37:38,88
And I think Laurenz also has in
his blog post a slightly different

725
0:37:38,88 --> 0:37:39,38
analysis.

726
0:37:40,08 --> 0:37:41,88
But the idea is the same.

727
0:37:41,88 --> 0:37:43,5
How bad is it, the picture?

728
0:37:44,22 --> 0:37:46,24
It should be done already, but
not yet done.

729
0:37:46,24 --> 0:37:49,34
This is how you also feel this
situation happening.

730
0:37:50,98 --> 0:37:54,94
Michael: From the other side of
things, I saw a good post recently

731
0:37:54,94 --> 0:38:1,74
by Jeremy Schneider about the risk
of completely removing throttling.

732
0:38:2,08 --> 0:38:5,72
So he wrote a good post recently
about an issue they encountered

733
0:38:5,92 --> 0:38:6,16
once.

734
0:38:6,16 --> 0:38:7,7
I think they reduced the...

735
0:38:9,24 --> 0:38:10,34
Nikolay: I haven't seen it.

736
0:38:11,76 --> 0:38:13,48
Sometimes people put to Cron.

737
0:38:13,78 --> 0:38:16,58
We haven't touched this specific
cases related to MultiXact

738
0:38:16,92 --> 0:38:20,58
member situation when people have
this approaching and they need

739
0:38:20,58 --> 0:38:21,84
to vacuum separately.

740
0:38:22,34 --> 0:38:25,68
Switching of indexes because index
vacuuming because for that

741
0:38:25,68 --> 0:38:29,34
problem you don't need, and this
is actually another job to clean

742
0:38:29,34 --> 0:38:30,54
up those things.

743
0:38:30,92 --> 0:38:34,2
You don't need indexes to be involved
and this slows down the

744
0:38:34,2 --> 0:38:35,88
whole thing if you have many indexes.

745
0:38:35,98 --> 0:38:41,68
So anyway, I wonder, I need to
look at that, but, and when you

746
0:38:41,68 --> 0:38:45,66
do manual vacuuming it's unthrottled,
as I said, because

747
0:38:45,66 --> 0:38:46,98
vacuum_cost_delay is 0.

748
0:38:47,12 --> 0:38:48,48
So it's not checking at all.

749
0:38:48,48 --> 0:38:49,22
Let's go.

750
0:38:49,74 --> 0:38:52,48
Michael: But I think that's, isn't
that because every now and

751
0:38:52,48 --> 0:38:56,26
again, you want to run a manual
vacuum as like an emergency procedure?

752
0:38:56,42 --> 0:38:56,92
Like,

753
0:38:57,16 --> 0:38:58,24
Nikolay: yeah, it makes sense.

754
0:38:58,24 --> 0:38:58,86
I agree.

755
0:38:58,94 --> 0:39:3,58
But if it's throttled and it's
unthrottled, Actually, this is

756
0:39:3,58 --> 0:39:4,64
super interesting for me.

757
0:39:4,64 --> 0:39:7,74
In the past, I had problems with
unthrottled vacuum, but it was

758
0:39:7,74 --> 0:39:13,66
when we had some enterprise disks
and self-hosted Postgres and

759
0:39:13,66 --> 0:39:16,76
our capacity was like 400 megabytes
per second.

760
0:39:17,02 --> 0:39:22,42
I also am very curious right now
in Postgres 18 with AIO and

761
0:39:22,42 --> 0:39:26,12
so on, like how a single worker
might behave.

762
0:39:26,12 --> 0:39:29,54
Remember I told you I was super
impressed to see the pg_basebackup,

763
0:39:29,54 --> 0:39:33,04
which is single threaded
and I expected 100 megabytes

764
0:39:33,04 --> 0:39:34,78
of reads from it.

765
0:39:34,94 --> 0:39:37,96
Suddenly it amazed me, approaching
like gigabyte per second of

766
0:39:37,96 --> 0:39:39,78
reads in Postgres 18.

767
0:39:40,44 --> 0:39:44,68
So maybe a vacuum also, I'm not
sure, like this I haven't visited.

768
0:39:44,68 --> 0:39:45,36
So- Yeah,

769
0:39:45,36 --> 0:39:48,54
Michael: I think it was me that
suggested it might be AIO related.

770
0:39:49,22 --> 0:39:51,88
Nikolay: Yeah, but let me correct
myself.

771
0:39:51,88 --> 0:39:54,36
So there is fourth piece memory,
super important.

772
0:39:54,8 --> 0:39:58,0
And if we don't think about it,
it might bite badly, especially

773
0:39:58,08 --> 0:39:59,16
if we raise workers.

774
0:39:59,16 --> 0:40:2,4
So all our clients, we say, okay,
we raised workers, but we're

775
0:40:2,4 --> 0:40:7,76
jumping from say Postgres 15 to
17, you must limit memory for

776
0:40:7,76 --> 0:40:11,76
autovacuum in advance because
it's by default minus 1 means

777
0:40:11,76 --> 0:40:14,8
it takes the value from maintenance_work_mem.

778
0:40:14,8 --> 0:40:19,24
And maintenance_work_mem people
usually raise quite quickly thinking

779
0:40:19,24 --> 0:40:21,94
they will have much faster index
creation.

780
0:40:21,94 --> 0:40:23,94
So they put 2 gigabytes or so.

781
0:40:24,06 --> 0:40:28,94
Now I think like 25 workers, 2
gigabytes is already a lot, right?

782
0:40:28,98 --> 0:40:32,48
And the problem is like before
Postgres 17, you might not notice

783
0:40:32,48 --> 0:40:37,36
it because autovacuum workers
suddenly limit themselves with

784
0:40:37,36 --> 0:40:41,68
1 gigabyte and then suddenly jump
to no obvious limit anymore.

785
0:40:41,82 --> 0:40:44,96
No 1 gigabyte limit anymore for
autovacuum workers.

786
0:40:44,96 --> 0:40:49,04
And If you have 4 gigabytes, I
saw 8 gigabytes, like these things,

787
0:40:49,04 --> 0:40:49,54
right?

788
0:40:49,54 --> 0:40:52,16
And many workers, it's huge memory
consumption.

789
0:40:52,92 --> 0:40:56,12
Michael: Or risk, like not necessarily,
but there's the risk

790
0:40:56,12 --> 0:40:57,8
that they could take up that much,
right?

791
0:40:57,8 --> 0:40:59,54
It's not pre-allocated or anything.

792
0:40:59,54 --> 0:41:1,66
It's just that's the budget they
have.

793
0:41:1,66 --> 0:41:5,64
Nikolay: It can influence and it
can increase risks of out of

794
0:41:5,64 --> 0:41:6,14
memory.

795
0:41:6,4 --> 0:41:6,5
—

796
0:41:6,5 --> 0:41:7,36
Michael: Yeah, yeah, yeah.

797
0:41:7,36 --> 0:41:8,04
— OOM killer.

798
0:41:8,68 --> 0:41:8,8
Nikolay: —

799
0:41:8,8 --> 0:41:12,6
Michael: So, but then you can set,
yeah, great.

800
0:41:12,66 --> 0:41:15,78
But then you can set the autovacuum_work_mem.

801
0:41:16,08 --> 0:41:17,74
I've forgotten the exact name of
it.

802
0:41:17,74 --> 0:41:20,58
Nikolay: — Yeah, I found like half
a gigabyte, some math should

803
0:41:20,58 --> 0:41:24,86
be done there, and we usually like,
we have it, like some formulas.

804
0:41:25,26 --> 0:41:30,06
So usually half a gigabyte, even
maybe lower, and it will be

805
0:41:30,06 --> 0:41:31,42
okay, right?

806
0:41:31,46 --> 0:41:34,54
It's interesting topic, but yeah,
so usually for maintenance

807
0:41:34,54 --> 0:41:39,32
work, again, I wish like this minus
1 is killing me, like decayed

808
0:41:39,32 --> 0:41:39,72
already.

809
0:41:39,72 --> 0:41:45,14
Like why we don't put explicit
value right away?

810
0:41:45,42 --> 0:41:49,24
Why this like I need to go in some
maze and remember.

811
0:41:49,94 --> 0:41:55,68
It took me many years to settle
in terms of these, like, relationship

812
0:41:55,8 --> 0:41:58,16
between vacuum and autovacuum
settings.

813
0:41:58,98 --> 0:42:2,64
And for non-expert who is not working
with Postgres settings

814
0:42:2,64 --> 0:42:5,28
every day, it's impossible to memorize.

815
0:42:5,74 --> 0:42:6,24
Right?

816
0:42:6,46 --> 0:42:7,12
It's super tricky.

817
0:42:7,12 --> 0:42:9,66
Michael: So you would have hard-coded
values for each even if

818
0:42:9,66 --> 0:42:10,92
they start with the same value?

819
0:42:10,92 --> 0:42:11,78
Nikolay: Doesn't hurt.

820
0:42:12,04 --> 0:42:15,02
Yeah, so if we have maintenance_work_mem by default, I don't know,

821
0:42:15,02 --> 0:42:18,7
what is it, like 128 megabytes
or so, like maybe more.

822
0:42:18,94 --> 0:42:19,84
Just copy paste.

823
0:42:19,84 --> 0:42:23,18
This is not the place where we
should save on copy pasting.

824
0:42:23,6 --> 0:42:26,42
We shouldn't save like few bytes.

825
0:42:26,98 --> 0:42:28,82
Minus 1 is 2 bytes, right?

826
0:42:29,06 --> 0:42:30,92
128, it's okay, a few bytes more.

827
0:42:30,92 --> 0:42:34,4
This is not the place where we
should save it, right?

828
0:42:34,4 --> 0:42:40,2
This is this complexity, extra
complexity, which is like, okay,

829
0:42:40,2 --> 0:42:43,48
I need to redirect this frustration
to proper place.

830
0:42:44,06 --> 0:42:47,32
Michael: Yeah, by the way it's
64 megabytes default

831
0:42:47,32 --> 0:42:47,78
maintenance_work_mem.

832
0:42:47,78 --> 0:42:48,5498
I didn't realize it was

833
0:42:48,5498 --> 0:42:48,68
Nikolay: that low.

834
0:42:48,68 --> 0:42:50,24
Postgres, yeah, Postgres.

835
0:42:50,28 --> 0:42:50,78
Yeah.

836
0:42:51,38 --> 0:42:52,58
Michael: All right, awesome.

837
0:42:52,66 --> 0:42:55,94
And actually, the last thing I
wanted to say, so as a little

838
0:42:56,32 --> 0:43:2,44
summary, if people have less than
8 vCPUs, maybe leave, you can

839
0:43:2,44 --> 0:43:6,02
probably leave the workers in place,
but still change the thresholds

840
0:43:6,02 --> 0:43:8,94
and it's just those 3 thresholds
will get you like 90% of the

841
0:43:8,94 --> 0:43:9,82
way there probably.

842
0:43:10,64 --> 0:43:11,82
I think most of...

843
0:43:13,28 --> 0:43:16,4
Reduce, you said down to 1% I think
that makes a lot of sense.

844
0:43:16,4 --> 0:43:19,94
I've generally advised people to
like 5 or 2% as like a starting

845
0:43:19,94 --> 0:43:22,12
point but 1% makes a lot of sense.

846
0:43:22,12 --> 0:43:25,24
It's a bit more, but I think it

847
0:43:25,24 --> 0:43:28,06
Nikolay: won't save you from large
tables It won't save you from

848
0:43:28,06 --> 0:43:33,62
this problem of local degradation
for particular rows It's just

849
0:43:33,62 --> 0:43:37,3
like maybe you need to think about
it additionally, but overall

850
0:43:37,3 --> 0:43:40,44
it's like good, very rough tuning
for

851
0:43:40,44 --> 0:43:40,52
Michael: all the teams.

852
0:43:40,52 --> 0:43:41,26
Yeah, absolutely.

853
0:43:41,28 --> 0:43:44,02
I think that'll get a lot of people
a lot further.

854
0:43:44,28 --> 0:43:46,32
Most providers let you set that
globally.

855
0:43:46,32 --> 0:43:49,84
I did come across 1 recently that
only lets you change these

856
0:43:49,84 --> 0:43:54,52
things at a table level, at which
point my advice would be focus

857
0:43:54,52 --> 0:43:57,18
on your important tables and just
change a few of those.

858
0:43:57,18 --> 0:43:58,82
But that was only 1 provider.

859
0:43:59,64 --> 0:44:0,62
Nikolay: Or just migrate.

860
0:44:1,22 --> 0:44:2,54
Michael: Or migrate, yeah.

861
0:44:3,82 --> 0:44:5,92
That small task of migrating database.

862
0:44:6,56 --> 0:44:8,6
But yeah, so that sounds wise.

863
0:44:8,6 --> 0:44:13,98
And then, yeah, memory tuning,
make sure it's not using

864
0:44:13,98 --> 0:44:15,62
maintenance_work_mem if you've upped that.

865
0:44:15,62 --> 0:44:16,02
Smart advice.

866
0:44:16,02 --> 0:44:18,34
Nikolay: Yeah, there are other
parameters, but it's like already

867
0:44:18,34 --> 0:44:19,52
deeper things.

868
0:44:20,42 --> 0:44:21,8
Michael: Yeah, sounds good.

869
0:44:22,8 --> 0:44:28,34
Nikolay: My simple rough tuning
list, raise number of workers.

870
0:44:29,06 --> 0:44:33,22
This requires restart, unfortunately,
then make visits more frequent,

871
0:44:33,26 --> 0:44:38,04
decreasing scale factors to 1%,
for example, roughly, and give

872
0:44:38,04 --> 0:44:42,56
more power, unless it's already
done by provider, raising, for

873
0:44:42,56 --> 0:44:44,12
example, raising cost_limit.

874
0:44:44,14 --> 0:44:46,2
Oh yeah, cost_limit, right?

875
0:44:46,4 --> 0:44:49,74
And default settings, as I said,
Postgres default settings, because

876
0:44:49,74 --> 0:44:51,14
not providers default settings.

877
0:44:51,14 --> 0:44:55,26
So it's roughly 80 MB/s for disk reads.

878
0:44:55,68 --> 0:44:58,94
Think how much you want, like maybe
just raise it, maybe sometimes

879
0:44:58,94 --> 0:45:2,14
4 or 5 times, depending on disk
capacity.

880
0:45:2,66 --> 0:45:4,46
It's very important to understand
that.

881
0:45:5,46 --> 0:45:9,96
And finally, autovacuum_work_mem,
set it explicitly, minus 1 sucks in all

882
0:45:9,96 --> 0:45:10,46
senses.

883
0:45:10,64 --> 0:45:12,6
It's just very hard to deal with.

884
0:45:13,66 --> 0:45:14,02
I like

885
0:45:14,02 --> 0:45:14,34
Michael: that.

886
0:45:14,34 --> 0:45:17,2
I like setting it explicitly, even
if you plan to keep it the

887
0:45:17,2 --> 0:45:18,6
same as your maintenance_work_mem
now.

888
0:45:18,6 --> 0:45:22,32
Nikolay: Yes, like freeze it before
upgrading to 17 for example.

889
0:45:22,44 --> 0:45:25,22
Or before touching number of autovacuum
workers.

890
0:45:26,2 --> 0:45:30,06
Just make it explicit so to simplify
math analysis including

891
0:45:30,06 --> 0:45:34,34
with AI so it won't jump additional
like mind hop.

892
0:45:34,9 --> 0:45:37,74
Michael: And just avoiding that
future change where somebody

893
0:45:37,74 --> 0:45:40,2
wants to increase maintenance_work_mem
and doesn't think

894
0:45:40,2 --> 0:45:40,32
Nikolay: at

895
0:45:40,32 --> 0:45:42,88
Michael: that moment about
autovacuuming.

896
0:45:43,18 --> 0:45:44,94
Nikolay: Because these are 2 separate
things.

897
0:45:45,06 --> 0:45:47,22
They operational, they are very
separate.

898
0:45:47,54 --> 0:45:50,42
Indexing and vacuuming, autovacuuming.

899
0:45:50,74 --> 0:45:51,98
Yeah, and that's it.

900
0:45:51,98 --> 0:45:54,56
These 4 areas for rough tuning,
it's enough.

901
0:45:55,64 --> 0:46:0,4
It should become bloat situation
much better, but there are also

902
0:46:0,4 --> 0:46:0,9
blockers.

903
0:46:0,92 --> 0:46:5,28
If You can tune a lot, tune everything
perfectly, but if you

904
0:46:5,28 --> 0:46:10,94
have some xmin horizon blocker,
and we have episode about xmin horizon,

905
0:46:11,74 --> 0:46:13,02
then it won't help.

906
0:46:13,48 --> 0:46:15,04
Bloat will accumulate anyway.

907
0:46:15,56 --> 0:46:16,06
Michael: Yeah.

908
0:46:17,38 --> 0:46:17,88
Great.

909
0:46:18,18 --> 0:46:18,74
All right.

910
0:46:18,74 --> 0:46:19,24
Nikolay: Good.

911
0:46:19,4 --> 0:46:19,9
Yeah.

912
0:46:20,66 --> 0:46:21,34
Michael: Nice one, Nik.

913
0:46:21,34 --> 0:46:22,32
Thanks so much.

914
0:46:22,76 --> 0:46:24,06
Nikolay: Yeah, I hope it was helpful.

915
0:46:24,06 --> 0:46:25,12
See you next time.

916
0:46:25,3 --> 0:46:26,04
Michael: See you next time.