Estimating work_mem

1
0:0:0,06 --> 0:0:1,98
Nik: Hello, hello, this is Postgres.FM

2
0:0:2,36 --> 0:0:7,06
It is Nik, as usual, PostgresAI,
and as usual, my co-host is

3
0:0:7,06 --> 0:0:8,24
Michael pgMustard.

6
0:0:8,24 --> 0:0:9,059999
Hi, Michael.

7
0:0:11,92 --> 0:0:16,32
And today we have a guest, which
I know for quite some time.

8
0:0:16,32 --> 0:0:21,0
I think never met in person, but
somehow our paths crossed many

9
0:0:21,0 --> 0:0:24,220001
times when we write materials or
read other people's materials,

10
0:0:24,220001 --> 0:0:26,34
obviously, and also on social media.

11
0:0:27,099998 --> 0:0:29,08
It's Shaun Thomas from pgEdge.

12
0:0:29,18 --> 0:0:29,92
Hello, Shaun.

13
0:0:29,92 --> 0:0:30,92
Thank you for coming.

14
0:0:31,08 --> 0:0:33,14
Shaun: Hi, Nik, Michael.

15
0:0:33,28 --> 0:0:34,18
What's going on?

16
0:0:35,02 --> 0:0:38,6
Nik: Yeah, and it took us some
time to choose the topic because

17
0:0:38,6 --> 0:0:41,559998
you obviously write about a lot
of various stuff.

18
0:0:42,52 --> 0:0:46,02
Shaun: Yeah, it's a very eclectic
grab bag, but I like to make

19
0:0:46,02 --> 0:0:48,78
sure that cover all the interesting
topics as they come up.

20
0:0:49,86 --> 0:0:53,8
Nik: And I was tempted to choose
an AI-related topic, obviously,

21
0:0:54,0 --> 0:0:57,26
because I know you also, since
the very beginning of GPT-4 and

22
0:0:57,26 --> 0:1:1,76
so on, I know you're like very
actively use it, right?

23
0:1:2,22 --> 0:1:3,94
Shaun: Yeah, a lot of that's your
fault, honestly.

24
0:1:3,94 --> 0:1:6,78
You pulled me in, I was like, well,
that's really a lot more behind

25
0:1:6,78 --> 0:1:8,18
the scenes than I would have realized.

26
0:1:9,32 --> 0:1:9,82
Cool.

27
0:1:9,92 --> 0:1:12,5
Nik: I'm glad that I influenced
a little bit.

28
0:1:12,5 --> 0:1:13,479996
That's great to hear.

29
0:1:13,479996 --> 0:1:14,18
Thank you.

30
0:1:14,28 --> 0:1:17,64
But we chose very boring on 1 side,
like a very technical topic,

31
0:1:17,64 --> 0:1:21,44
but I think what's important is
that this topic is very often

32
0:1:21,48 --> 0:1:26,54
misunderstood, especially if you
don't spend every day in Postgres

33
0:1:26,54 --> 0:1:28,86
internals a lot of hours, right?

34
0:1:28,86 --> 0:1:33,34
So it's easy to forget how to tune
memory properly, how to deal

35
0:1:33,34 --> 0:1:37,26
with work_mem, what to choose, and
pros and cons and trade-offs

36
0:1:37,28 --> 0:1:38,8
about various choices.

37
0:1:39,06 --> 0:1:41,12
So I think topic choice is great
here.

38
0:1:41,12 --> 0:1:43,94
Do you remember when 1st time you
needed to tune work_mem?

39
0:1:45,3 --> 0:1:46,78
It was long ago, right?

40
0:1:47,92 --> 0:1:48,76
Shaun: Yeah, that's...

41
0:1:49,14 --> 0:1:52,94
Some of these older settings, like,
there's something you fiddled

42
0:1:53,0 --> 0:1:57,72
with sometime 20 years ago, and
I'm like, oh yeah, I did have

43
0:1:57,72 --> 0:2:0,84
to fight with that a lot, but it
still comes up frequently, like

44
0:2:0,84 --> 0:2:5,74
in the mailing lists and in message
boards and Discord, Slack,

45
0:2:5,74 --> 0:2:9,1
wherever, there's always someone
that's wondering about how to

46
0:2:9,1 --> 0:2:9,78
set it.

47
0:2:9,84 --> 0:2:12,72
And I wouldn't say it's a black
art, but there's definitely some

48
0:2:12,72 --> 0:2:13,76
ambiguity there.

49
0:2:14,44 --> 0:2:17,82
Nik: I think It takes some significant
time to understand nuances

50
0:2:17,92 --> 0:2:18,78
around it.

51
0:2:19,2 --> 0:2:22,58
Because what people expect, obviously,
is that work_mem is some

52
0:2:22,58 --> 0:2:24,18
limit for the whole thing.

53
0:2:24,64 --> 0:2:26,14
It's like the number 1 problem.

54
0:2:26,82 --> 0:2:28,52
And yeah, it's obviously not so.

55
0:2:28,52 --> 0:2:32,76
So you published a blog post recently,
Let's Write Extension,

56
0:2:33,56 --> 0:2:36,2
that will help tune work_mem.

57
0:2:36,66 --> 0:2:40,82
I see it like 2 goals of this blog
post.

58
0:2:40,84 --> 0:2:42,32
1 is let's write extension.

59
0:2:43,18 --> 0:2:47,0
And honestly, my opinion is maybe
not popular, but I think we

60
0:2:47,0 --> 0:2:51,02
should write extensions less because
they are not supported by

61
0:2:51,02 --> 0:2:52,1
managed providers.

62
0:2:52,2 --> 0:2:53,4
Unless it's pg_tle.

63
0:2:54,16 --> 0:2:57,28
That's okay, because it will be
possible to run it anywhere.

64
0:2:57,34 --> 0:3:1,82
And also, like yesterday, someone
posted how many vulnerabilities

65
0:3:5,08 --> 0:3:7,06
various third-party extensions
bring.

66
0:3:7,8 --> 0:3:11,3
Then platforms like Neon, Supabase
and others are badly affected.

67
0:3:11,8 --> 0:3:14,76
So this is 1 more reason not to
write extension.

68
0:3:15,36 --> 0:3:18,28
So We can spend some time there,
I think, to talk about writing

69
0:3:18,28 --> 0:3:19,84
extensions, because it's still
useful.

70
0:3:19,92 --> 0:3:22,7
Of course, my opinion is very practical.

71
0:3:23,1 --> 0:3:26,12
Of course, we should write extensions,
but we should understand

72
0:3:26,12 --> 0:3:29,98
that probably it will be very limited
in terms of use.

73
0:3:30,4 --> 0:3:33,62
But obviously, the 2nd goal is
work_mem tuning.

74
0:3:34,22 --> 0:3:39,16
And I'm very curious, and my goal
for myself today is to see

75
0:3:39,72 --> 0:3:42,74
how our visions are aligned in
memory tuning.

76
0:3:44,02 --> 0:3:48,74
In the beginning, I checked some
old EnterpriseDB posts and

77
0:3:48,74 --> 0:3:50,58
I saw a formula, a very simple
formula.

78
0:3:50,58 --> 0:3:52,66
And I'm very curious what you think
about this.

79
0:3:52,78 --> 0:3:58,28
Let's take whole memory, divide
by 4, usually it's done with

80
0:3:58,28 --> 0:3:59,4
shared_buffers, right?

81
0:3:59,44 --> 0:4:1,5
And then divide by max_connections.

82
0:4:1,88 --> 0:4:3,36
And this is our work_mem.

83
0:4:3,48 --> 0:4:4,9
What do you think about this?

84
0:4:6,04 --> 0:4:9,48
Shaun: So I actually did a blog
post like that while I was at

85
0:4:9,48 --> 0:4:12,32
EDB, and I think my formula was
slightly different.

86
0:4:12,84 --> 0:4:18,42
I said something like divide by
max_connections and divide by

87
0:4:18,42 --> 0:4:18,92
5.

88
0:4:19,64 --> 0:4:23,44
Because the assumption there was
every query could have some

89
0:4:23,44 --> 0:4:28,04
amount of query nodes and since
each of those can create an instance

90
0:4:28,04 --> 0:4:33,28
of work_mem, Assuming 1 is probably
a bad idea, because only the

91
0:4:33,28 --> 0:4:35,94
most simple queries will only have
1 query node.

92
0:4:36,58 --> 0:4:39,56
Nik: Yeah, so 5 is quite, like
I would say, conservative.

93
0:4:40,52 --> 0:4:41,5
Shaun: Yeah, it's aggressive.

94
0:4:41,58 --> 0:4:43,84
It's aggressively conservative
because I know that's, for 1,

95
0:4:43,84 --> 0:4:45,92
that's how the Postgres community
tends to operate.

96
0:4:45,92 --> 0:4:48,84
They use what they call sensible
defaults.

97
0:4:49,7 --> 0:4:53,1
And it's an easy way to avoid out
of memorying yourself until

98
0:4:53,1 --> 0:4:55,18
you can have more time to tweak
it.

99
0:4:55,44 --> 0:4:56,26
Nik: That's interesting.

100
0:4:56,28 --> 0:4:59,72
Also, if we think about both of
these approaches, and you're

101
0:4:59,72 --> 0:5:2,8
obviously more advanced than that
simple 1.

102
0:5:3,34 --> 0:5:7,9
Like we understand that each query
can consume multiple times

103
0:5:7,9 --> 0:5:8,6
of work_mem.

104
0:5:9,28 --> 0:5:13,64
If we go with this formula to RDS,
you know what will happen,

105
0:5:13,64 --> 0:5:14,14
right?

106
0:5:14,72 --> 0:5:15,54
Shaun: No, I don't.

107
0:5:15,54 --> 0:5:16,5
What will RDS do?

108
0:5:16,5 --> 0:5:17,78
Nik: max_connections, 5, 000.

109
0:5:18,52 --> 0:5:19,7
Even for small clusters.

110
0:5:19,7 --> 0:5:20,28
Oh, geez.

111
0:5:21,04 --> 0:5:21,54
Yes.

112
0:5:21,9 --> 0:5:22,52
Shaun: All right.

113
0:5:22,96 --> 0:5:25,68
Nik: And they expect that everyone
will probably use RDS Proxy

114
0:5:25,68 --> 0:5:28,18
But we observe a lot of customers
who come to us.

115
0:5:28,18 --> 0:5:32,32
They don't know PgBouncer no,
this proxy only some poolers

116
0:5:32,32 --> 0:5:38,3
or on application side And those
guys who run application nodes,

117
0:5:38,3 --> 0:5:40,7
they put it to Kubernetes with
auto-scaling.

118
0:5:41,4 --> 0:5:45,61
So nobody knows which, like, probability
of reaching those 5,

119
0:5:45,61 --> 0:5:46,62
000 connections.

120
0:5:47,22 --> 0:5:50,0
Shaun: Yeah, we could have a whole
another conversation on pooling.

121
0:5:50,22 --> 0:5:52,64
My opinion there is like you should
never connect directly to

122
0:5:52,64 --> 0:5:53,14
Postgres.

123
0:5:53,42 --> 0:5:55,24
Nik: Right, yeah, poolers should
be inside.

124
0:5:55,24 --> 0:5:56,24
Yeah, I agree.

125
0:5:56,4 --> 0:6:0,64
But this is the reality and RDS
is the most popular 1 And people

126
0:6:0,64 --> 0:6:6,52
come with 16 vCPUs, 2,500 or 5,000
max_connections.

127
0:6:7,24 --> 0:6:7,72
Shaun: Yeah.

128
0:6:7,72 --> 0:6:10,84
At that point you get to an area
where you have to say, sample

129
0:6:10,84 --> 0:6:13,18
your number of active connections
and then it's a little more

130
0:6:13,18 --> 0:6:15,04
involved, but it's roughly the
same idea.

131
0:6:15,28 --> 0:6:15,54
Nik: Yeah.

132
0:6:15,54 --> 0:6:16,9
So this is great.

133
0:6:17,38 --> 0:6:19,08
And this is like for many years.

134
0:6:19,08 --> 0:6:22,36
It was with number 1 like approach
We basically we tune based

135
0:6:22,36 --> 0:6:27,86
on observations feedback loop,
right but I really want something

136
0:6:27,86 --> 0:6:32,64
more like not looking into actual
thing, but predicting something,

137
0:6:32,64 --> 0:6:32,84
right?

138
0:6:32,84 --> 0:6:36,2
Because sometimes you launch new
service, or sometimes you expect

139
0:6:36,2 --> 0:6:41,76
some growth, and observing production,
it just doesn't feel super,

140
0:6:41,76 --> 0:6:43,42
it's very practical, of course.

141
0:6:43,66 --> 0:6:45,76
And it can work in either way as
well.

142
0:6:45,76 --> 0:6:51,2
For example, if we take your formula,
divide by 5, and even drop

143
0:6:51,2 --> 0:6:55,82
that 5, just no additional dividing
at all.

144
0:6:56,4 --> 0:7:0,04
And then we say, you know what,
we still need more memory.

145
0:7:0,24 --> 0:7:4,2
And we just observe there's a lot
of like page cache is huge

146
0:7:4,74 --> 0:7:7,74
Because work_mem is not allocated
immediately, right?

147
0:7:7,74 --> 0:7:12,04
It's allocated in chunks like it's
gradually So work_mem is

148
0:7:12,04 --> 0:7:16,26
limit for 1 operation inside query
But it doesn't like it doesn't

149
0:7:16,26 --> 0:7:20,02
say that within a query, each
operation will have work_mem, maybe

150
0:7:20,02 --> 0:7:20,52
less.

151
0:7:20,82 --> 0:7:26,26
If we observe from reality that
we actually use much less, we

152
0:7:26,26 --> 0:7:27,9
can over-commit here.

153
0:7:28,04 --> 0:7:29,06
This is what we do.

154
0:7:29,06 --> 0:7:34,46
Like we say, We go beyond theoretical
limits because we see that

155
0:7:34,46 --> 0:7:37,54
practically we don't have out-of-memory
risks.

156
0:7:38,24 --> 0:7:40,92
Theoretically we have them, but
practically no.

157
0:7:40,96 --> 0:7:44,76
And I know such clusters, huge
clusters, they are running, like,

158
0:7:44,76 --> 0:7:46,22
just ignoring this formula.

159
0:7:46,22 --> 0:7:49,52
And it's okay because majority
of queries they actually use tiny

160
0:7:49,54 --> 0:7:50,46
amount of work_mem.

161
0:7:50,46 --> 0:7:51,88
What do you think about this?

162
0:7:52,36 --> 0:7:57,74
Overall it's like a bag of very
different ideas and how to find

163
0:7:57,74 --> 0:8:0,26
better like path to some recipe?

164
0:8:0,76 --> 0:8:5,2
Shaun: The recipe I usually use
and the 1 I started with, honestly,

165
0:8:5,44 --> 0:8:8,38
is to use the database statistics.

166
0:8:9,06 --> 0:8:12,72
I think it's pg_stat_database, or
it could be in pg_database, where

167
0:8:12,72 --> 0:8:16,26
it tracks the number of temp files
and the number of temp file

168
0:8:16,26 --> 0:8:16,76
size.

169
0:8:17,32 --> 0:8:20,78
So what I do is I find out the
average size of the temp files

170
0:8:20,78 --> 0:8:25,6
Postgres is producing and then
I add that to whatever the work_mem

171
0:8:25,6 --> 0:8:26,42
setting is.

172
0:8:26,92 --> 0:8:30,06
So if it starts out at the default
4 megs and then I find out

173
0:8:30,06 --> 0:8:34,62
that Postgres is producing on average
8 megabyte temporary files.

174
0:8:34,96 --> 0:8:37,96
I'll add some padding onto that
and add that to the work_mem so

175
0:8:37,96 --> 0:8:40,46
I end up with 12 or 16 would be
my setting.

176
0:8:40,88 --> 0:8:45,26
And that reduces all your disk
spills by 90 plus percent and

177
0:8:45,26 --> 0:8:47,18
that usually addresses the problem.

178
0:8:47,66 --> 0:8:48,48
Nik: That's interesting.

179
0:8:48,48 --> 0:8:51,24
Let's have some small pause here.

180
0:8:51,46 --> 0:8:54,94
1st of all, obviously you take
shared blocks dirtied?

181
0:8:54,94 --> 0:8:58,62
Oh, not shared, temp blocks dirtied,
right?

182
0:9:0,52 --> 0:9:3,74
Shaun: Temp files and temp bytes,
like literally those 2 fields.

183
0:9:4,74 --> 0:9:6,56
Nik: Ah, okay, in pg_stat_database.

184
0:9:6,56 --> 0:9:8,36
I'm thinking about pg_stat_statements.

185
0:9:8,6 --> 0:9:12,26
This is where I struggle, because
Postgres has a huge gap.

186
0:9:12,26 --> 0:9:13,86
It doesn't have a number of calls.

187
0:9:14,24 --> 0:9:15,76
It has only a number of transactions.

188
0:9:16,64 --> 0:9:20,94
So you need to divide by calls
to have average temporary bytes

189
0:9:20,94 --> 0:9:22,42
written per call, right?

190
0:9:22,42 --> 0:9:24,62
But we don't have it in the
pg_stat_database.

191
0:9:24,9 --> 0:9:27,44
Shaun: Yeah, you just have a very
coarse heuristic that just

192
0:9:27,44 --> 0:9:31,36
says, I produced this many of temporary
files and these are how

193
0:9:31,36 --> 0:9:32,26
big they were.

194
0:9:32,5 --> 0:9:35,58
So you don't really have enough
fine-grained ability to see how

195
0:9:35,58 --> 0:9:38,22
big they were like for each individual
thing.

196
0:9:38,94 --> 0:9:42,72
Nik: Yeah, that's why I look at
pg_stat_statements because it has

197
0:9:42,72 --> 0:9:43,22
calls.

198
0:9:43,26 --> 0:9:46,62
Of course, it maybe tracks not
all queries, but by default only

199
0:9:46,62 --> 0:9:47,12
5, 000.

200
0:9:48,14 --> 0:9:50,04
pg_stat_statements.max is 5, 000.

201
0:9:50,58 --> 0:9:54,96
But we have both temporary blocks
written and calls.

202
0:9:54,96 --> 0:9:58,42
We can divide 1 by another and
get average per call.

203
0:9:58,42 --> 0:10:2,34
But then I literally thought about
this last week, even before

204
0:10:2,36 --> 0:10:6,12
your blog post, because we also,
we deal with temporary files

205
0:10:6,12 --> 0:10:9,52
generation in many customers, it's
normal, they don't tune it

206
0:10:9,52 --> 0:10:12,18
and then it becomes a problem.

207
0:10:12,26 --> 0:10:13,18
Why it's a problem?

208
0:10:13,18 --> 0:10:16,3
Because spilling to disk is slow,
right?

209
0:10:16,56 --> 0:10:17,78
So it affects performance.

210
0:10:18,24 --> 0:10:20,32
And I was thinking, OK, we have
average.

211
0:10:20,32 --> 0:10:22,96
We know, for example, 8 megabytes,
as you said, like per call,

212
0:10:22,96 --> 0:10:23,48
for example.

213
0:10:23,48 --> 0:10:25,58
And we have 8 megabytes work_mem.

214
0:10:25,86 --> 0:10:28,22
Thinking, OK, we will set up 16.

215
0:10:28,84 --> 0:10:32,23
But then I was thinking, What if
it's average, right?

216
0:10:32,23 --> 0:10:37,66
What if 90% of those queries are
tiny and the remaining 10, they

217
0:10:37,66 --> 0:10:38,98
need 100 megabytes?

218
0:10:39,64 --> 0:10:41,94
Shaun: Yeah, I usually run into
situations like that.

219
0:10:41,94 --> 0:10:44,0
You just kind of have to live with
the disk spilling.

220
0:10:44,54 --> 0:10:47,04
You obviously don't want to get
it past a certain amount where

221
0:10:47,04 --> 0:10:49,84
your queries are having to fight
for disk I/O.

222
0:10:49,84 --> 0:10:54,28
But at the same time, if the average
is too high, you can't make

223
0:10:54,28 --> 0:10:55,46
it infinitely high.

224
0:10:55,9 --> 0:10:56,26
Nik: Yeah.

225
0:10:56,26 --> 0:11:0,72
So it's a problem of averages and
lack of percentiles again here.

226
0:11:0,72 --> 0:11:1,22
Right.

227
0:11:2,06 --> 0:11:4,64
Michael: I've seen once you understand
your workload, I've seen

228
0:11:4,64 --> 0:11:7,44
some people tweak it on a per user.

229
0:11:7,44 --> 0:11:10,9
Instead of setting it globally,
keep the global low, but then

230
0:11:10,9 --> 0:11:14,28
set an application, like if there's
a reporting application that

231
0:11:14,28 --> 0:11:18,52
we know only ever fires off 1 or
2 queries at a time, set its

232
0:11:18,52 --> 0:11:19,6
work_mem higher.

233
0:11:19,6 --> 0:11:22,44
Have you seen things like that
or done that in the past, Shaun?

234
0:11:22,74 --> 0:11:25,9
Shaun: I haven't, but that would
definitely be another way out

235
0:11:25,9 --> 0:11:26,32
of it.

236
0:11:26,32 --> 0:11:30,4
The fact that you can set a user
level session GUCs is a way

237
0:11:30,4 --> 0:11:31,44
out of a lot of ways.

238
0:11:31,44 --> 0:11:34,94
In the past I've used that to turn
off nested looping for example.

239
0:11:35,22 --> 0:11:38,16
Back when the planner wasn't so
great and it still occasionally

240
0:11:38,16 --> 0:11:40,96
runs into stuff like this, you
get a query that just resists

241
0:11:41,1 --> 0:11:44,44
every attempt to get to run with
the same plan.

242
0:11:44,44 --> 0:11:47,9
So you just, You take out a hammer
and you knock out the parts

243
0:11:47,9 --> 0:11:50,18
you don't want to do and magically
it starts working.

244
0:11:50,38 --> 0:11:53,3
Now that's not so much a problem
with Postgres 19 coming with

245
0:11:53,3 --> 0:11:57,56
the new hint syntax So you can
tell it to prefer certain paths

246
0:11:57,56 --> 0:12:2,42
but before that you had a CTE or
some kind of wall of materialization

247
0:12:2,56 --> 0:12:5,62
you had to construct to make it
act a certain way.

248
0:12:6,22 --> 0:12:10,02
And 1 of those is, yeah, the coarse
knobs of adjusting the planner

249
0:12:10,02 --> 0:12:13,64
or working with work_mem or some
other thing.

250
0:12:14,34 --> 0:12:18,48
Nik: Yeah, and It's interesting
that work_mem is not, if you select

251
0:12:18,48 --> 0:12:23,5
star from pg_settings, work_mem is
not sitting in the Planner category.

252
0:12:23,68 --> 0:12:24,78
It's about memory.

253
0:12:25,08 --> 0:12:28,74
But it affects how Planner chooses
the plan.

254
0:12:29,06 --> 0:12:31,08
So it's also an interesting thing.

255
0:12:31,16 --> 0:12:32,64
It might change the plan.

256
0:12:33,54 --> 0:12:36,54
So yeah, and the good thing that
you can set either at session

257
0:12:36,54 --> 0:12:41,08
level or at user level as well,
alter role.

258
0:12:41,48 --> 0:12:45,64
So I don't think people use it
often, but it's a good thing for

259
0:12:45,66 --> 0:12:48,76
some heavy queries to move this
workload to a different user

260
0:12:48,76 --> 0:12:49,26
completely.

261
0:12:49,9 --> 0:12:54,3
But also this makes understanding
of the whole picture harder,

262
0:12:55,28 --> 0:12:55,78
slightly.

263
0:12:55,84 --> 0:12:59,04
Shaun: Yeah, but if you have a
mixed use case system, you have

264
0:12:59,04 --> 0:13:1,58
to choose your battles, right?

265
0:13:1,82 --> 0:13:1,98
Nik: So

266
0:13:1,98 --> 0:13:5,02
Shaun: I think Michael's point
is very salient because if you

267
0:13:5,02 --> 0:13:8,6
have an OLTP system that's taking,
that's 90% of doing what it's

268
0:13:8,6 --> 0:13:11,32
doing and every once in a while
a huge query sneaks in where

269
0:13:11,32 --> 0:13:14,92
you've got a batch job you need
to run like setting for the long

270
0:13:14,92 --> 0:13:19,46
batch job, the transaction length
to some larger amount.

271
0:13:20,28 --> 0:13:22,9
So there's lots of other different
settings you might do for

272
0:13:22,9 --> 0:13:25,68
that 1 particular thing that you
wouldn't want to change for

273
0:13:25,68 --> 0:13:26,54
the whole server.

274
0:13:26,98 --> 0:13:29,86
So usually in a case like that
I'd say make a reporting system

275
0:13:29,86 --> 0:13:33,84
so you can direct your OLTP stuff
there but that's a architectural

276
0:13:33,96 --> 0:13:35,42
thing for a later time.

277
0:13:36,34 --> 0:13:39,96
Michael: 1 thing that's changed
in recent years is the introduction

278
0:13:39,96 --> 0:13:43,58
of hash_mem_multiplier and the change
of the default of it from

279
0:13:43,58 --> 0:13:44,8
1 to 2.

280
0:13:45,04 --> 0:13:47,92
It was really great in your blog
post to see you taking that

281
0:13:47,92 --> 0:13:51,38
into account when estimating how
much a single query is going

282
0:13:51,38 --> 0:13:56,16
to take, but I haven't seen that
in many other blog posts even

283
0:13:56,16 --> 0:13:57,34
since it was changed.

284
0:13:57,66 --> 0:14:0,9
So do you think maybe some of the
formulas you might come across

285
0:14:1,4 --> 0:14:6,1
via search or even LLMs these days,
might be maybe not even conservative

286
0:14:6,16 --> 0:14:9,36
enough because there's this multiplier
now for quite a few operations.

287
0:14:9,96 --> 0:14:12,78
Shaun: Yeah, the problem with using
the hash_mem_multiplier

288
0:14:12,92 --> 0:14:17,36
as part of a generic formula is,
Unless you know how many hash

289
0:14:17,36 --> 0:14:19,96
memory operations are going to
occur within a query, you're just

290
0:14:19,96 --> 0:14:20,46
guessing.

291
0:14:21,04 --> 0:14:23,86
At least with the number of nodes,
you can say, I'll say there's

292
0:14:23,86 --> 0:14:26,88
like a 4 or 5 average or a 2 or
3 average or whatever it is for

293
0:14:26,88 --> 0:14:29,52
your platform, you can say, just
multiply it out.

294
0:14:29,54 --> 0:14:32,6
But with hash_mem_multiplier, it's just, it's
another factor that complicates

295
0:14:32,6 --> 0:14:34,6
the formula in a way that's not
really meaningful.

296
0:14:34,6 --> 0:14:36,36
Because we're already estimating,
right?

297
0:14:36,5 --> 0:14:38,86
We're already just picking a number
out of the sky about how

298
0:14:38,86 --> 0:14:40,84
many query nodes and how many things
that's going to be in your

299
0:14:40,84 --> 0:14:41,34
estimate.

300
0:14:41,52 --> 0:14:44,84
And even the extension that I wrote
is very coarse, it's just

301
0:14:45,06 --> 0:14:46,74
Not every hash node will be 2x.

302
0:14:46,76 --> 0:14:50,0
Not every, it doesn't handle
Appends, it doesn't walk the entire

303
0:14:50,0 --> 0:14:50,38
tree.

304
0:14:50,38 --> 0:14:54,16
Like it was a 1st approximation
to give a guess at a worst case

305
0:14:54,16 --> 0:14:54,66
scenario.

306
0:14:54,86 --> 0:14:57,38
Nik: Worst case scenario is a key
here, yeah, I agree.

307
0:14:57,56 --> 0:15:0,04
Michael: I understand that, but
I think the worst case scenario

308
0:15:0,04 --> 0:15:5,28
has just got worse since the, so
the multiplier of 2 means everybody's

309
0:15:5,6 --> 0:15:10,94
worst-case scenario has got worse
because those nodes could well

310
0:15:10,96 --> 0:15:12,58
could take more memory now.

311
0:15:12,88 --> 0:15:15,48
Shaun: To an extent but I believe
the reason they actually put

312
0:15:15,48 --> 0:15:18,26
that there was to constrain it,
constrain memory more.

313
0:15:18,56 --> 0:15:21,82
Previously, I could have this wrong,
but I remember a couple

314
0:15:21,82 --> 0:15:25,12
of threads in the mailing list
where someone was saying that

315
0:15:25,12 --> 0:15:28,14
hash memory was unbounded by work_mem
at all.

316
0:15:28,14 --> 0:15:31,4
So it was causing them to get OOMs
because 1 query would create

317
0:15:31,4 --> 0:15:33,9
an infinitely large hash and it
would just crash the system.

318
0:15:34,28 --> 0:15:39,4
So now they actually, hash mems,
honor a limit which they didn't

319
0:15:39,4 --> 0:15:39,9
before.

320
0:15:40,94 --> 0:15:42,78
So yes and no.

321
0:15:43,38 --> 0:15:46,4
Nik: Yeah, and your extension is
looking at the plan, right?

322
0:15:46,4 --> 0:15:51,42
So it knows exactly how many hashing
happened and other operations.

323
0:15:52,06 --> 0:15:53,08
That's a great idea.

324
0:15:53,42 --> 0:15:55,96
I think it's possible to apply
it at scale.

325
0:15:56,28 --> 0:15:59,66
For example, at least if, very
roughly, we talk about estimates.

326
0:15:59,86 --> 0:16:4,7
What if we take all queries from
pg_stat_statements, 5, 000

327
0:16:5,14 --> 0:16:8,5
by default, maximum, and just collect
generic plans.

328
0:16:8,94 --> 0:16:9,44
Yeah.

329
0:16:9,72 --> 0:16:10,64
That's it, right?

330
0:16:10,64 --> 0:16:13,82
So your extension, obviously, it
was like at a micro level, just

331
0:16:13,82 --> 0:16:15,18
a single query, right?

332
0:16:15,18 --> 0:16:20,32
And for those who haven't seen,
it's just producing some additional

333
0:16:20,6 --> 0:16:25,52
hints, log messages, hints what
peak usage for this query could

334
0:16:25,52 --> 0:16:26,02
be.

335
0:16:26,26 --> 0:16:30,46
And then you can understand, like
for session level, at least

336
0:16:30,46 --> 0:16:34,02
for this query, you can understand
how to avoid spilling to temporary

337
0:16:34,02 --> 0:16:35,26
files, to disk.

338
0:16:35,66 --> 0:16:38,9
But we can apply it to macro level,
right?

339
0:16:38,9 --> 0:16:40,86
With just collecting generic plans.

340
0:16:41,38 --> 0:16:44,76
Of course, it won't be super precise
because custom plans might

341
0:16:44,76 --> 0:16:49,44
have very different opinion how
many hashing or ordering should

342
0:16:49,44 --> 0:16:49,94
happen.

343
0:16:50,22 --> 0:16:52,98
But it can be good enough for estimate,
I think.

344
0:16:53,3 --> 0:16:54,02
Do you agree?

345
0:16:55,56 --> 0:16:56,06
Shaun: Yeah.

346
0:16:57,8 --> 0:17:1,44
So the way I wrote it was, the
extension was basically to exercise

347
0:17:1,62 --> 0:17:5,2
an example of, here's how you write
a function that a user can

348
0:17:5,2 --> 0:17:5,7
call.

349
0:17:5,82 --> 0:17:8,04
Here's how you can leverage GUCs.

350
0:17:8,2 --> 0:17:10,02
Here's how you can override hooks.

351
0:17:10,16 --> 0:17:13,74
But because of all that, yeah,
you can, you've got to use a little

352
0:17:13,74 --> 0:17:16,16
function you can call and it's
like a debugging process.

353
0:17:16,16 --> 0:17:18,46
I've got this huge query, I might
want to run in production.

354
0:17:18,76 --> 0:17:20,1
Will it blow things up?

355
0:17:20,28 --> 0:17:23,1
Oh, it could use up to a gigabyte
of memory.

356
0:17:23,1 --> 0:17:25,32
Whoops, let me go back and look
at that again.

357
0:17:25,32 --> 0:17:28,22
Or maybe I want to adjust my
work_mem to make it use less memory

358
0:17:28,22 --> 0:17:29,24
overall in general.

359
0:17:30,04 --> 0:17:32,72
But then yeah, you could in theory
point whatever's in pg_stat_statements

360
0:17:32,72 --> 0:17:36,04
at it and just send
them all through the function

361
0:17:36,04 --> 0:17:40,04
in the loop, or just as part of
a query call and get some kind

362
0:17:40,04 --> 0:17:41,74
of maximum estimate out of that.

363
0:17:42,04 --> 0:17:44,16
Nik: Yeah, that's I think quite
doable.

364
0:17:45,22 --> 0:17:48,02
And it could help to tune better,
actually, right?

365
0:17:48,54 --> 0:17:51,26
Yeah, that's good that you aligned
here as well.

366
0:17:51,26 --> 0:17:53,66
I have a very interesting specific
question.

367
0:17:54,44 --> 0:17:57,42
Maybe you or Michael knows.

368
0:17:58,32 --> 0:18:6,34
So Why does Postgres crash when
it reaches, like when there is

369
0:18:6,34 --> 0:18:7,32
not enough memory?

370
0:18:7,82 --> 0:18:13,12
Instead of crashing 1 backend somehow,
right?

371
0:18:13,26 --> 0:18:14,76
It crashes the whole cluster.

372
0:18:14,76 --> 0:18:18,18
Of course, if it's crashed the
backend abnormally, I understand

373
0:18:18,18 --> 0:18:19,3
like for protection.

374
0:18:21,56 --> 0:18:23,0
We need this to restart.

375
0:18:23,0 --> 0:18:23,6
I understand that.

376
0:18:23,6 --> 0:18:29,72
But I know RDS had some extension
or logic, proprietary 1, to

377
0:18:29,72 --> 0:18:34,54
just to give up only those backends
which cannot be, cannot like

378
0:18:34,54 --> 0:18:37,76
switch to memory, but not to lose
others.

379
0:18:38,46 --> 0:18:41,18
But then they somehow removed it,
deprecated.

380
0:18:41,82 --> 0:18:45,08
And I'm very curious what's happening
in this area and why Postgres

381
0:18:46,0 --> 0:18:49,6
cannot protect other, like others
already running give them a

382
0:18:49,6 --> 0:18:50,96
chance to complete, right?

383
0:18:51,5 --> 0:18:52,48
Do you know, no?

384
0:18:52,48 --> 0:18:54,56
Like, I'm very curious about this.

385
0:18:54,56 --> 0:18:55,34
I think

386
0:18:55,9 --> 0:18:56,98
Michael: it's a Linux thing.

387
0:18:56,98 --> 0:18:59,94
Correct me if I'm wrong, Shaun,
but I think it's the out-of-memory

388
0:19:0,06 --> 0:19:2,92
killer and the fact we've got the
postmaster.

389
0:19:4,28 --> 0:19:7,84
Shaun: So yeah, if you've got a
backend that's using a lot of

390
0:19:7,84 --> 0:19:12,72
RAM and Postgres gets killed arbitrarily
by the kernel, sure,

391
0:19:12,72 --> 0:19:15,92
that'll cause it to complain about
stuff.

392
0:19:16,38 --> 0:19:20,6
And I believe if there's no memory
available and you do a memory

393
0:19:20,6 --> 0:19:23,9
request, you'll just get back an
error and your backend won't

394
0:19:23,9 --> 0:19:24,24
die.

395
0:19:24,24 --> 0:19:27,4
I need to run a test to see actually
what happens if you run

396
0:19:27,4 --> 0:19:29,86
into a situation like that where
you've turned off over commit,

397
0:19:29,86 --> 0:19:32,52
for example, so you actually get
an out of memory error from

398
0:19:32,52 --> 0:19:35,18
your backend if it tries to allocate
more than is available.

399
0:19:35,9 --> 0:19:39,02
But yeah, I think that's, I think
what it is that the OOM killer

400
0:19:39,14 --> 0:19:43,3
terminates the backend, and then
since that backend can't clean

401
0:19:43,3 --> 0:19:47,56
up after itself, you could have
corrupted memory context because

402
0:19:47,56 --> 0:19:51,1
Postgres uses arenas with shared
memory context.

403
0:19:51,1 --> 0:19:54,92
And if you can't roll back your
arena back or clean out your

404
0:19:54,92 --> 0:19:58,5
context, the entire shared state
of the buffers could be corrupt

405
0:19:58,5 --> 0:20:0,68
in some way that you can't really
consider.

406
0:20:1,24 --> 0:20:4,44
So as a defensive measure, Postgres
shuts the entire thing down

407
0:20:4,44 --> 0:20:5,26
and then restarts.

408
0:20:6,28 --> 0:20:9,3
But aside from that I'm not entirely
sure.

409
0:20:9,32 --> 0:20:11,46
We need someone who's worked with
it.

410
0:20:11,6 --> 0:20:14,34
Andres or Masahiko Sawada, they would
know.

411
0:20:15,14 --> 0:20:16,24
Or maybe Álvaro.

412
0:20:17,28 --> 0:20:21,82
Nik: So yeah, I practically I would
prefer to have errors for

413
0:20:21,82 --> 0:20:25,58
specific sessions, but all others
are still working.

414
0:20:25,68 --> 0:20:29,78
And if we see that we're about
to be out of memory, that's practically

415
0:20:30,48 --> 0:20:31,3
more convenient.

416
0:20:32,32 --> 0:20:34,64
Similar case is with out-of-disk
space.

417
0:20:34,82 --> 0:20:36,68
It's not a good place to be.

418
0:20:36,9 --> 0:20:41,24
And I know some people place some
file filled by zeros and they

419
0:20:41,24 --> 0:20:43,98
just remove it if urgency happens,
right?

420
0:20:45,06 --> 0:20:47,78
Anyway, yeah, that's interesting
question as well.

421
0:20:48,34 --> 0:20:49,18
Good, okay.

422
0:20:49,64 --> 0:20:55,08
Speaking of writing extensions,
I have a feeling 20 years ago,

423
0:20:55,08 --> 0:20:58,02
Postgres is extensible, extensions
are great, and then managed

424
0:20:58,02 --> 0:21:2,98
services, managed providers, they
just somehow brought us to

425
0:21:2,98 --> 0:21:7,46
the point when extensions are against
extensibility, because

426
0:21:7,46 --> 0:21:9,52
they need to approve it.

427
0:21:9,52 --> 0:21:14,08
And it's like with all those scary
stories about vulnerabilities,

428
0:21:15,06 --> 0:21:17,06
their approval rates will slow
down.

429
0:21:18,26 --> 0:21:22,4
Shaun: Yeah, so the issue with
Postgres extensions is they're

430
0:21:22,5 --> 0:21:24,06
very much a double-edged sword.

431
0:21:24,52 --> 0:21:28,52
It adds this new functionality
that Postgres never had, and that's

432
0:21:28,52 --> 0:21:28,94
amazing.

433
0:21:28,94 --> 0:21:32,18
We wouldn't have pgvector, for
example, right now, or PostGIS

434
0:21:32,18 --> 0:21:35,26
or any of those other huge extensions
everyone relies on now,

435
0:21:35,38 --> 0:21:36,3
without it.

436
0:21:36,72 --> 0:21:41,4
But at the same time, the Postgres
extension mechanisms, and

437
0:21:41,4 --> 0:21:44,12
I now know this first-hand since
I've played with it with my

438
0:21:44,12 --> 0:21:46,88
tutorials, is awful.

439
0:21:47,68 --> 0:21:49,34
Like, for example, the hook system.

440
0:21:49,6 --> 0:21:53,3
There's no, like you normally see
register hook, some kind of

441
0:21:53,3 --> 0:21:56,28
function you'd call that would
put your hook on a stack and it

442
0:21:56,28 --> 0:21:59,64
would control that whatever the
hooks are called in, you wouldn't

443
0:21:59,64 --> 0:22:4,5
be able to accidentally not call
the next extension in the list.

444
0:22:4,54 --> 0:22:6,88
Right now, you actually have to
check and see if there's a next

445
0:22:6,88 --> 0:22:7,94
or previous hook.

446
0:22:8,08 --> 0:22:11,86
If there is, save it for later
and call it yourself in your extension.

447
0:22:12,18 --> 0:22:14,6
And if you don't do that, or if
someone decides they want to

448
0:22:14,6 --> 0:22:17,72
be a bad actor and don't do that
in their extension, the whole

449
0:22:17,72 --> 0:22:18,84
stack gets broken.

450
0:22:19,54 --> 0:22:21,76
The other issue is there's no sandbox.

451
0:22:22,36 --> 0:22:25,68
Even browsers, right, just some
user's browser they're using

452
0:22:25,68 --> 0:22:31,22
to browse the web has a sandbox
per thing, and Postgres has no

453
0:22:31,22 --> 0:22:32,94
even concept of that right now.

454
0:22:33,08 --> 0:22:36,48
So you end up with CVEs that get
escalated into the core because

455
0:22:37,28 --> 0:22:40,22
the Postgres extensions are literally
calling core structures.

456
0:22:40,52 --> 0:22:42,78
There's no API for any of this
stuff.

457
0:22:42,78 --> 0:22:45,06
It's just, oh, you know Postgres
function?

458
0:22:45,06 --> 0:22:46,86
Go ahead and include it and call
it internally.

459
0:22:46,92 --> 0:22:50,22
You have direct core access to
every part of Postgres all the

460
0:22:50,22 --> 0:22:50,72
time.

461
0:22:51,42 --> 0:22:53,4
Nik: This would be great to fix,
by the way.

462
0:22:53,4 --> 0:22:55,82
Do you know anyone looking in this
direction?

463
0:22:56,26 --> 0:23:0,36
Shaun: I don't, but I can't imagine
that nobody's thought about

464
0:23:0,36 --> 0:23:3,76
this or nobody's thought to, can
we add sandboxing or can we

465
0:23:3,76 --> 0:23:6,54
add a hook management mechanism
of some kind.

466
0:23:6,56 --> 0:23:9,82
But it's just low priority because
right now they, all the core

467
0:23:9,82 --> 0:23:13,74
developers work on core Postgres
and their interest in extensions

468
0:23:13,86 --> 0:23:15,72
is tangential at best, right?

469
0:23:15,72 --> 0:23:18,52
Because if they want something,
they just put it in the core,

470
0:23:18,52 --> 0:23:20,64
and if someone else wants something,
they can add it for their

471
0:23:20,64 --> 0:23:21,26
own convenience.

472
0:23:22,14 --> 0:23:26,32
Nik: And the core just experienced
the biggest, in terms of CVEs

473
0:23:26,32 --> 0:23:28,94
fixed, the biggest minor release,
28.

474
0:23:29,02 --> 0:23:32,8
Shaun: Oh, the 28 of the 28 CVEs
they fixed in 19, yeah.

475
0:23:32,8 --> 0:23:34,28
Nik: 18.6, yeah.

476
0:23:34,28 --> 0:23:35,32
Shaun: Yeah, right, 18.6.

477
0:23:35,46 --> 0:23:39,5
And then 18.5 never came out because
it got reverted.

478
0:23:39,88 --> 0:23:43,6
Nik: Which happened last time,
it happened in 2008, yeah.

479
0:23:43,66 --> 0:23:44,66
Shaun: It's been a while.

480
0:23:45,02 --> 0:23:46,36
Nik: Yeah, Yeah, obviously.

481
0:23:46,56 --> 0:23:51,46
But exactly this question triggers
the question about will the

482
0:23:51,46 --> 0:23:54,74
next minor release have even more
CVEs?

483
0:23:56,04 --> 0:23:58,82
And this triggers the next question,
what about extensions we

484
0:23:58,82 --> 0:24:0,18
have on managed providers?

485
0:24:0,86 --> 0:24:3,98
Or not managed providers, just
if we'll stay close to focus.

486
0:24:4,3 --> 0:24:9,02
Shaun: Not only will the next 1
have more, it's going to climb

487
0:24:9,02 --> 0:24:10,74
exponentially, in my opinion.

488
0:24:10,76 --> 0:24:15,22
Because, not just because of the
visibility, because obviously

489
0:24:15,94 --> 0:24:18,62
the 28 number sounds high and people
are like, we should probably

490
0:24:18,62 --> 0:24:21,26
take a look at this now and there's
gonna be more eyes on it,

491
0:24:21,3 --> 0:24:22,36
but because of AI.

492
0:24:23,0 --> 0:24:26,18
You said you didn't want, we weren't
gonna focus on that, but

493
0:24:26,38 --> 0:24:29,76
if we're being honest here, everyone
is pointing their AI agent

494
0:24:30,3 --> 0:24:32,46
at everything they possibly can.

495
0:24:32,98 --> 0:24:36,6
And now those can find bugs that
we never would have thought

496
0:24:36,6 --> 0:24:40,46
about because we just didn't have
time or priority or there wasn't

497
0:24:40,46 --> 0:24:41,82
a test case for it.

498
0:24:41,82 --> 0:24:46,28
And now there's infinite amount
of research area there.

499
0:24:46,44 --> 0:24:50,26
So not only are we going to see
that, we're going to see it accelerate.

500
0:24:52,06 --> 0:24:52,96
Michael: Is it infinite?

501
0:24:53,44 --> 0:24:55,42
I think it's finite, isn't it?

502
0:24:55,52 --> 0:24:57,34
Nik: There should be a plateau
at some point.

503
0:24:58,14 --> 0:25:1,32
Michael: The number might be high,
and I have no idea if 28 is

504
0:25:1,32 --> 0:25:4,46
getting even close to the right
order of magnitude per release

505
0:25:4,54 --> 0:25:9,74
but it feels like it will accelerate
for a while and then hopefully

506
0:25:9,96 --> 0:25:10,82
slow down?

507
0:25:11,6 --> 0:25:14,82
Shaun: Yeah that's a valid point
but I think at least in the

508
0:25:14,82 --> 0:25:17,58
short term we are definitely going
to see an acceleration.

509
0:25:17,72 --> 0:25:21,18
28 is just a drop in the bucket
because I don't know about being

510
0:25:21,18 --> 0:25:25,32
honest is the right way to phrase
this, but Postgres being written

511
0:25:25,32 --> 0:25:30,38
in C has a lot of potential for
edge cases that we just haven't

512
0:25:30,38 --> 0:25:31,56
even looked at.

513
0:25:31,56 --> 0:25:35,4
You have fuzzers, you have memory
leak checkers, you've got pointer

514
0:25:35,74 --> 0:25:38,24
checks that are done right now
by various different mechanisms

515
0:25:38,24 --> 0:25:38,68
we have.

516
0:25:38,68 --> 0:25:39,82
We've got our build farms.

517
0:25:39,82 --> 0:25:43,7
There's lots of stuff to catch
this early, but you don't miss

518
0:25:43,7 --> 0:25:45,66
28 CVEs because you're dumb.

519
0:25:45,78 --> 0:25:50,14
It's just there are new attack
vectors that nobody thought of

520
0:25:50,28 --> 0:25:53,04
and extensions open that up even
more.

521
0:25:53,6 --> 0:25:57,04
So the popular extensions are going
to be also attack vectors

522
0:25:57,04 --> 0:26:0,72
and since extensions themselves
are a way to get into the Postgres

523
0:26:0,72 --> 0:26:3,58
core, if you exploit an extension,
you have access to core.

524
0:26:4,0 --> 0:26:8,32
So that even adds more vectors
that either they have to start

525
0:26:8,32 --> 0:26:12,24
to find a way to enforce a sandbox
or it's going to make that

526
0:26:12,24 --> 0:26:13,64
discussion a lot more germane.

527
0:26:14,38 --> 0:26:19,28
And I saw someone post on X, it
was a security researcher, and

528
0:26:19,28 --> 0:26:23,1
he's going to do a four-part or
a five-part series on how he

529
0:26:23,1 --> 0:26:26,14
broke into PostGIS, for example.

530
0:26:26,5 --> 0:26:29,72
And he escalated that all the way
to a root RCE where he could

531
0:26:29,72 --> 0:26:32,86
arbitrarily affect files on the
file system from PostGIS.

532
0:26:33,48 --> 0:26:35,24
And that's just part 1 of 5.

533
0:26:35,24 --> 0:26:37,84
And he's actually going to slowly
escalate the amount of attacks

534
0:26:37,84 --> 0:26:41,06
he does all the way to the point
of getting root on the container

535
0:26:41,18 --> 0:26:42,68
through Kubernetes.

536
0:26:43,38 --> 0:26:48,52
So it's just ridiculous the amount
of analysis that people have

537
0:26:48,52 --> 0:26:51,26
really not done to this point because
it just wasn't on their

538
0:26:51,26 --> 0:26:51,76
radar.

539
0:26:51,88 --> 0:26:55,12
But now it is and it has to be
because everyone and their dog

540
0:26:55,12 --> 0:26:58,94
has an AI that can be like oh I'll
just attack this 24 hours

541
0:26:58,94 --> 0:27:2,0
a day 7 days a week until I find
something.

542
0:27:2,86 --> 0:27:7,62
Nik: We should say thank you to
Anthropic and OpenAI for limiting

543
0:27:7,68 --> 0:27:8,18
capabilities.

544
0:27:9,24 --> 0:27:11,64
You cannot do it with their latest
models right now.

545
0:27:11,64 --> 0:27:14,18
They will like completely say I'm
not continuing.

546
0:27:14,76 --> 0:27:19,32
Shaun: Yeah, those models, but
anyone who has a modern Qwen or

547
0:27:19,7 --> 0:27:24,24
K3 or any of the local models that's
been put through a LoRA

548
0:27:24,24 --> 0:27:28,28
that is uncensored, and then if
you have access to, I don't know,

549
0:27:28,28 --> 0:27:31,56
$50, 000 worth of equipment, You
can direct it at anything.

550
0:27:31,96 --> 0:27:34,74
Nik: That's interesting, because
I saw Anthropic was mentioned

551
0:27:34,74 --> 0:27:38,16
a few times in the release notes
for those minor releases, but

552
0:27:38,16 --> 0:27:41,06
I don't remember any other AI systems
mentioned.

553
0:27:41,54 --> 0:27:43,68
Shaun: They're just the ones that
people are focusing on because

554
0:27:43,68 --> 0:27:47,06
they're the frontier models, but
anything that's been open sourced

555
0:27:47,44 --> 0:27:48,46
can be repurposed.

556
0:27:48,68 --> 0:27:49,44
Nik: Yeah, yeah,

557
0:27:49,44 --> 0:27:49,78
Shaun: yeah.

558
0:27:49,78 --> 0:27:53,98
We're getting a little bit off
the topic, but the reason extensions

559
0:27:54,24 --> 0:27:58,58
are at this point and Postgres,
and actually really any software

560
0:27:58,66 --> 0:28:2,76
now, is because of AI, for good
or ill.

561
0:28:4,3 --> 0:28:7,44
Nik: Yeah, maybe let's connect
topics so we could use AI to tune

562
0:28:7,44 --> 0:28:7,94
work_mem.

563
0:28:8,9 --> 0:28:12,86
Shaun: Yeah, I actually used it
to find all the bugs in my extension

564
0:28:12,86 --> 0:28:14,6
because my extension was a proof
of concept, right?

565
0:28:14,6 --> 0:28:16,02
It's just, here, you can do this.

566
0:28:16,02 --> 0:28:18,54
It's kind of fun, throwaway kind
of material.

567
0:28:19,06 --> 0:28:20,74
So it found all the issues in it.

568
0:28:20,74 --> 0:28:23,8
Oh, you're not looking at append,
you're not looking at whatever,

569
0:28:24,0 --> 0:28:27,04
you can use the built-in plan walker
and get all these extra

570
0:28:27,04 --> 0:28:27,84
things you missed.

571
0:28:27,84 --> 0:28:30,8
And oh, you don't realize that
if you run out with these inputs

572
0:28:30,8 --> 0:28:32,96
it causes your extension to crash,
which takes down the

573
0:28:32,96 --> 0:28:33,24
backend.

574
0:28:33,24 --> 0:28:36,84
And it found dozens of things I
could fix, were I so inclined.

575
0:28:37,48 --> 0:28:39,44
So anyone can do that to their
extension.

576
0:28:39,72 --> 0:28:41,02
And it's a huge help.

577
0:28:41,76 --> 0:28:42,7
Nik: And should do.

578
0:28:42,7 --> 0:28:43,45
Shaun: Should, yes, also.

579
0:28:43,45 --> 0:28:45,06
Nik: At this point, already, yeah.

580
0:28:45,06 --> 0:28:47,88
Shaun: Because the amount of eyes,
like, the thing is, even in

581
0:28:47,88 --> 0:28:51,34
our company, we don't have my pgEdge
Ansible project that I

582
0:28:51,34 --> 0:28:55,08
work on for doing distributions
of architectures, there's like

583
0:28:55,08 --> 0:28:57,6
maybe 2 other people in the company
that can help me do code

584
0:28:57,6 --> 0:28:58,58
review on that.

585
0:28:58,78 --> 0:29:2,26
But I always have Claude available
to look over things or

586
0:29:2,26 --> 0:29:5,28
CodeRabbit or whatever tool you
want to use and they'll catch

587
0:29:5,28 --> 0:29:6,66
stuff that I didn't consider.

588
0:29:7,9 --> 0:29:10,12
Michael: I had a question on things
you considered.

589
0:29:11,12 --> 0:29:14,62
I liked that it was simple, I liked
the formulas I could follow

590
0:29:14,62 --> 0:29:18,42
them, the examples I could follow
them, But I did wonder if you,

591
0:29:19,3 --> 0:29:23,1
like for example, sequential scans
were adding 1 times work_mem,

592
0:29:23,1 --> 0:29:24,96
like it was every node type it
seemed.

593
0:29:24,96 --> 0:29:28,62
Shaun: Yeah, like it was a very
naive, like I just, anything

594
0:29:28,62 --> 0:29:31,28
that was a node I counted it, I
just didn't even like check,

595
0:29:31,28 --> 0:29:33,76
The only thing that I gave an extra
bonus to was anything that

596
0:29:33,76 --> 0:29:34,76
had the word hash in it.

597
0:29:34,76 --> 0:29:38,68
Like literally, I just grepped for
the word hash for the node types

598
0:29:38,68 --> 0:29:40,46
and I just put them all on that
big list.

599
0:29:40,84 --> 0:29:43,58
But yeah, like that was the naive
approach.

600
0:29:43,68 --> 0:29:46,84
A more refined approach would be
to actually go through and figure

601
0:29:46,84 --> 0:29:48,46
out which nodes actually do what.

602
0:29:49,12 --> 0:29:52,42
Because the parent hash node is
not where the memory gets allocated,

603
0:29:52,42 --> 0:29:54,36
it's actually the child hash elements.

604
0:29:54,52 --> 0:29:57,28
So I was actually double counting
the hash nodes, ironically

605
0:29:57,62 --> 0:29:58,74
inflating the results.

606
0:29:59,44 --> 0:30:4,34
So Yeah, obviously a good opportunity
there would be to spend

607
0:30:4,34 --> 0:30:7,34
some time refining the algorithm,
but my worst case scenario

608
0:30:7,34 --> 0:30:9,52
was just like, let's just count
all the nodes and then multiply

609
0:30:9,52 --> 0:30:12,28
and then you get like a, here's
the maximum amount this thing

610
0:30:12,28 --> 0:30:13,08
could possibly take.

611
0:30:13,08 --> 0:30:15,06
It's off by a little bit, but it's
better than the estimates

612
0:30:15,06 --> 0:30:16,72
we've been relying on.

613
0:30:16,78 --> 0:30:20,64
And it was a semi-useful kind of
extension and it demonstrated

614
0:30:20,68 --> 0:30:23,62
the process of writing an extension,
except for the fact that

615
0:30:23,62 --> 0:30:25,22
I didn't create a memory context.

616
0:30:28,36 --> 0:30:31,98
Michael: 1 more question was Back
to what you said at the start

617
0:30:31,98 --> 0:30:36,72
around using max_connections as
a multiplier I wondered it wouldn't

618
0:30:36,72 --> 0:30:40,0
be the same formula, but I wondered
if instead it might be sensible

619
0:30:40,0 --> 0:30:44,86
to use some multiple of the number
of cores The reason I came

620
0:30:44,86 --> 0:30:51,1
to that was thinking parallelism
like a parallel plan could use

621
0:30:51,1 --> 0:30:53,6
multiple times the multiple work_mems.

622
0:30:54,1 --> 0:30:57,8
So like I've seen sorts as part
of a parallel plan use kind of

623
0:30:57,8 --> 0:31:2,9
4 or 5 times work_mem, But obviously
that's a much lower number

624
0:31:2,9 --> 0:31:5,2
in most cases, so it'd be a very
different formula.

625
0:31:5,2 --> 0:31:8,8
But I wondered if there was any
merit to that maybe in formulas.

626
0:31:10,08 --> 0:31:15,06
Shaun: So, yeah, like, in that
case you would look at your max

627
0:31:15,06 --> 0:31:18,12
parallel_workers_per_gather option,
because that's the maximum

628
0:31:18,12 --> 0:31:21,22
number of cores that it would actually
leverage in a single query.

629
0:31:21,3 --> 0:31:23,26
And then multiply that by the number
of backends.

630
0:31:23,94 --> 0:31:25,54
But yeah, you could do that.

631
0:31:26,2 --> 0:31:26,68
Michael: Yeah.

632
0:31:26,68 --> 0:31:31,16
Just thinking if you've got 16
cores and you've got a bunch of

633
0:31:31,16 --> 0:31:34,34
queries trying to fire off lots
of parallel workers.

634
0:31:34,44 --> 0:31:37,12
The 1st few might get all of the
workers they want, but the next

635
0:31:37,12 --> 0:31:39,3
ones are only going to get a single
1.

636
0:31:39,52 --> 0:31:41,76
Like, it won't let you run more
than.

637
0:31:41,76 --> 0:31:43,76
Shaun: Yeah, yeah, that would act
as a cap.

638
0:31:44,1 --> 0:31:45,9
Michael: Have you ever done anything
like that, Nik?

639
0:31:46,62 --> 0:31:49,18
Nik: Yeah, I barely understand
what you're saying, Michael.

640
0:31:50,66 --> 0:31:56,26
Shaun: I think he was asking if
you had used the number of cores

641
0:31:56,26 --> 0:31:59,24
as part of your estimate for work_mem.

642
0:32:0,06 --> 0:32:3,16
And I kind of get where he's going
with it, because even if you

643
0:32:3,16 --> 0:32:7,78
set max_parallel_workers_per_gather
to limit the amount, eventually

644
0:32:7,78 --> 0:32:9,78
they'll run out of, you'll hit
your max_parallel_workers.

645
0:32:11,12 --> 0:32:13,06
Nik: Right, not vCPU count, max_parallel_workers.

646
0:32:13,38 --> 0:32:16,62
Postgres has no idea how many cores
or how much.

647
0:32:16,62 --> 0:32:19,82
Shaun: Right, but which is why
you set the max_parallel_workers

648
0:32:19,84 --> 0:32:22,62
and various other settings so you
wouldn't exceed it.

649
0:32:22,86 --> 0:32:23,26
Nik: Why?

650
0:32:23,26 --> 0:32:25,14
Like we can exceed it, we can exceed
it.

651
0:32:25,14 --> 0:32:26,92
Shaun: You can, you can, you can.

652
0:32:26,92 --> 0:32:28,3
I wouldn't recommend it.

653
0:32:29,34 --> 0:32:33,5
Nik: I agree with you But looking
at guys who come to us like

654
0:32:33,9 --> 0:32:39,72
16 or 32 cores and max_connections
5000, I already have a shift

655
0:32:39,72 --> 0:32:41,18
in my mind.

656
0:32:41,26 --> 0:32:42,66
I cannot convince them.

657
0:32:42,66 --> 0:32:47,06
We spent a lot of efforts saying
this max_connections is abnormal.

658
0:32:48,08 --> 0:32:51,8
Especially before Postgres 13,
14 when Andres Freund improved

659
0:32:52,36 --> 0:32:56,5
work with snapshots, which is very
related to memory consumption,

660
0:32:56,5 --> 0:32:57,0
right?

661
0:32:57,5 --> 0:33:1,36
Yeah, we tried like max_connections
should be like 3, 4 times

662
0:33:1,36 --> 0:33:3,3
more than vCPU count.

663
0:33:3,42 --> 0:33:5,52
Shaun: That's what everyone says,
but...

664
0:33:5,66 --> 0:33:6,6
Nik: No, not anymore.

665
0:33:6,6 --> 0:33:9,36
We don't say it anymore because
since Postgres 14, it's much

666
0:33:9,36 --> 0:33:9,86
better.

667
0:33:10,08 --> 0:33:13,0
And I think I had some tests showing,
we should revisit this

668
0:33:13,0 --> 0:33:13,5
by the way.

669
0:33:13,5 --> 0:33:18,62
I want to revisit with benchmarks
and see exactly how it degrades.

670
0:33:18,62 --> 0:33:20,42
But now it degrades less.

671
0:33:21,02 --> 0:33:22,54
Shaun: Yeah, it's not nearly as
bad.

672
0:33:22,54 --> 0:33:25,2
You still have to fight the kernel
process table, but it's not

673
0:33:25,2 --> 0:33:26,34
as bad as it was.

674
0:33:26,78 --> 0:33:28,48
Nik: Right, so this is just reality.

675
0:33:28,58 --> 0:33:32,72
These guys come to us with RDS
and telling them that you need

676
0:33:32,72 --> 0:33:39,04
to reduce max_connections drastically,
you need restart for it.

677
0:33:40,26 --> 0:33:44,28
And if they don't have proper database
side pooler, we cannot

678
0:33:44,38 --> 0:33:48,12
convince them to get rid of huge
amount of idle connections.

679
0:33:48,12 --> 0:33:53,2
They just need them to satisfy
application guys needs because

680
0:33:53,2 --> 0:33:57,24
those guys, as I said, they scale
their application nodes like

681
0:33:57,24 --> 0:34:0,36
this, especially e-commerce when
Black Friday happens, they just

682
0:34:0,36 --> 0:34:1,18
need to scale.

683
0:34:2,36 --> 0:34:6,2
Or some new system as well, like
social media, they need to be

684
0:34:6,2 --> 0:34:8,94
able to scale and they need those
idle connections.

685
0:34:9,78 --> 0:34:14,44
So this, back to work_mem here,
I don't know, it depends.

686
0:34:14,44 --> 0:34:19,62
Also, sometimes we need to reproduce
plans in an environment

687
0:34:19,74 --> 0:34:23,46
which is much weaker physically
than production, because we study

688
0:34:23,46 --> 0:34:24,98
behavior of Postgres.

689
0:34:25,44 --> 0:34:29,62
And we are okay for some contention
happening in terms of physical

690
0:34:29,62 --> 0:34:32,72
resources, But we want the planner
to behave exactly like in

691
0:34:32,72 --> 0:34:33,22
production.

692
0:34:33,66 --> 0:34:34,94
So there are some nuances.

693
0:34:36,26 --> 0:34:37,36
But I agree with you.

694
0:34:37,36 --> 0:34:40,6
Overall, I would like to see average
number of sessions below

695
0:34:40,6 --> 0:34:41,68
vCPU count.

696
0:34:41,68 --> 0:34:42,54
This is great.

697
0:34:43,26 --> 0:34:46,84
Shaun: Yeah, that's usually that's
what I still tell people.

698
0:34:46,84 --> 0:34:50,66
Not because necessarily they're
going to see a huge drop in performance

699
0:34:50,66 --> 0:34:53,72
because it's let if we let's face
it's going to be around

700
0:34:53,72 --> 0:34:56,42
the 20 or 30 percent mark at maximum
even if they're sending

701
0:34:56,42 --> 0:35:1,18
hundreds of thousands but it's
still I would say a best practice

702
0:35:1,18 --> 0:35:2,06
to do so

703
0:35:2,64 --> 0:35:5,94
Michael: yeah I'm going Maybe going
back to basics a little bit,

704
0:35:6,62 --> 0:35:11,68
when you're tuning work_mem, is
it always to do with latency?

705
0:35:11,98 --> 0:35:14,58
Just like speed of queries on average?

706
0:35:15,66 --> 0:35:19,02
Or are we sometimes trying to look
after the disks a little bit?

707
0:35:19,02 --> 0:35:21,06
Are we trying to increase headroom
there a little bit?

708
0:35:21,06 --> 0:35:24,22
Or is it mostly just user-facing
query times?

709
0:35:24,64 --> 0:35:26,58
Shaun: I guess it depends on what
hat you're wearing.

710
0:35:26,58 --> 0:35:29,8
As a DBA, you're just like, I don't
want my database to crash,

711
0:35:29,8 --> 0:35:32,32
or I don't want the hardware to
burst into flames because it's

712
0:35:32,32 --> 0:35:33,9
being misused in some way.

713
0:35:34,12 --> 0:35:36,9
From that perspective you're like,
okay, I'll set it to be just

714
0:35:36,9 --> 0:35:40,38
enough that I avoid lots of disk
spilling and then causing disk

715
0:35:40,38 --> 0:35:42,78
wear or really slow latency.

716
0:35:43,58 --> 0:35:45,48
But there's also a point of diminishing
returns, right?

717
0:35:45,48 --> 0:35:48,16
If you set it to some infinitely
high amount, you're not gaining

718
0:35:48,16 --> 0:35:49,28
anything out of it.

719
0:35:49,28 --> 0:35:52,06
All you're really doing is making
it so your maximum is higher

720
0:35:52,06 --> 0:35:55,34
for no reason, and you end up getting
more risk of an out-of-memory

721
0:35:55,38 --> 0:35:55,88
error.

722
0:35:56,12 --> 0:36:0,12
Really, it's just 1 of those things
like Nik had said, it's

723
0:36:0,38 --> 0:36:2,8
How do we set it properly without
going overboard?

724
0:36:2,8 --> 0:36:6,24
How do we go do it without going
too little?

725
0:36:7,06 --> 0:36:12,18
And part of that is taking what
you have and using heuristics

726
0:36:12,26 --> 0:36:16,72
to come up with some kind of reasonable
number, like using pg_stat_statements

727
0:36:16,72 --> 0:36:19,3
and sending it
through some kind of estimation

728
0:36:19,86 --> 0:36:20,36
process.

729
0:36:21,26 --> 0:36:24,16
Or I found out that apparently
if you send the query through

730
0:36:24,16 --> 0:36:28,86
the pre-execution step, it actually
calculates all the memory

731
0:36:28,86 --> 0:36:31,26
that it would allocate, but it
doesn't allocate it yet.

732
0:36:31,48 --> 0:36:34,28
So in theory I could walk the plan
nodes and actually get the

733
0:36:34,28 --> 0:36:37,88
estimates directly from the planner,
whereas my approach was

734
0:36:37,88 --> 0:36:41,98
very coarse and it just did it
based on the node types, you could

735
0:36:41,98 --> 0:36:45,28
get it directly from the plan output,
from the executor step

736
0:36:45,28 --> 0:36:46,4
itself, from the pre-executor.

737
0:36:46,62 --> 0:36:49,64
And you could actually pull those
bits of data and actually get

738
0:36:49,64 --> 0:36:54,62
an exact number of what the allocator
would have actually asked

739
0:36:54,62 --> 0:36:56,02
for from Postgres.

740
0:36:57,18 --> 0:37:0,4
So a better approach I would say,
at least as far as revising

741
0:37:0,4 --> 0:37:4,08
my extension, would be to say use
the built-in plan walker, do

742
0:37:4,08 --> 0:37:7,78
it after the pre-execution steps
so you have all the estimates

743
0:37:8,26 --> 0:37:11,68
of memory usage that it would have
done in the 1st place, and

744
0:37:11,68 --> 0:37:13,16
sum those totals instead.

745
0:37:13,78 --> 0:37:16,4
Then what you end up with is a
real estimate of what all the

746
0:37:16,4 --> 0:37:18,18
queries would have taken without
them executing.

747
0:37:19,02 --> 0:37:22,7
And then you can use that to design
your ideal work_mem based

748
0:37:22,7 --> 0:37:26,7
on your amount of average active
backends and whatnot.

749
0:37:27,72 --> 0:37:29,44
But it's just 1 of those things
with Postgres.

750
0:37:29,44 --> 0:37:34,2
You have to always go back and
retroactively examine how your

751
0:37:34,2 --> 0:37:35,18
system's been operating.

752
0:37:35,66 --> 0:37:37,36
And a lot of that is observation.

753
0:37:37,36 --> 0:37:38,38
Do you have a dashboard?

754
0:37:38,44 --> 0:37:41,24
Do you have observability and visibility
across your entire cluster

755
0:37:41,24 --> 0:37:43,66
to see what, how it's actually
operating?

756
0:37:43,98 --> 0:37:46,04
And that should be how you drive
your systems.

757
0:37:46,04 --> 0:37:49,34
If you see that you're always running
out of memory at some point,

758
0:37:49,34 --> 0:37:51,58
look at your memory settings, look
at shared_buffers, look at

759
0:37:51,58 --> 0:37:54,34
work_mem, look at anything that
could possibly allocate stuff

760
0:37:54,34 --> 0:37:56,82
and then maybe reduce it a little
bit if it's running out of

761
0:37:56,82 --> 0:37:58,52
memory or increase it if it's not.

762
0:37:58,94 --> 0:38:2,38
It's a delicate balancing act and
unfortunately there's no 1

763
0:38:2,38 --> 0:38:5,82
size fits all way to addressing
everything, which is why there's

764
0:38:5,82 --> 0:38:11,98
so many guides, there's blogs and
tutorials and videos and everything

765
0:38:11,98 --> 0:38:15,48
galore of how to do it, and no
1 really can agree on 1 final

766
0:38:15,48 --> 0:38:15,98
answer.

767
0:38:16,1 --> 0:38:19,14
Nik: There is no official runbook
how-to documentation.

768
0:38:19,4 --> 0:38:22,54
This is sad actually, but I can
imagine how hard it would be

769
0:38:22,54 --> 0:38:26,38
to achieve consensus on the concrete
protocol.

770
0:38:27,26 --> 0:38:30,84
Actually, we somehow avoided the
topic of swap as well.

771
0:38:31,02 --> 0:38:32,64
We could enable swap, right?

772
0:38:32,64 --> 0:38:35,68
This is like instead of temporary
file for each query, let's

773
0:38:35,68 --> 0:38:37,58
enable on that far end.

774
0:38:37,58 --> 0:38:40,46
If we achieve that, let's swap
there, 1 thing.

775
0:38:40,64 --> 0:38:43,66
And another thing, like I just
resonate very, like a lot with

776
0:38:43,66 --> 0:38:49,12
your words about, for example,
maintenance_work_mem, which usually

777
0:38:49,12 --> 0:38:52,94
is inherited by autovacuum_work_mem,
being minus 1, right?

778
0:38:53,1 --> 0:38:57,08
And then we tell everyone we should
have more workers for autovacuum

779
0:38:57,24 --> 0:38:57,7
workers.

780
0:38:57,7 --> 0:39:3,56
And then Postgres 17 silently,
like unexpectedly, lifts unspoken

781
0:39:3,7 --> 0:39:4,84
limit 1 gigabyte.

782
0:39:5,24 --> 0:39:9,34
It was not like obvious that we
actually were limited, but guys

783
0:39:9,34 --> 0:39:13,44
already raised maintenance_work_mem
to say 8 gigabytes and

784
0:39:13,44 --> 0:39:15,42
raise number of workers to say
25.

785
0:39:15,42 --> 0:39:18,9
And now we have interesting memory
allocation for autovacuum,

786
0:39:18,9 --> 0:39:20,04
which we didn't want.

787
0:39:20,5 --> 0:39:24,44
So now we say raise the number
of autovacuum workers, but also

788
0:39:24,62 --> 0:39:28,1
limit autovacuum_work_mem by 1
gigabyte, because when you will

789
0:39:28,1 --> 0:39:31,72
upgrade to 17, so Layers of logic,
yeah.

790
0:39:31,72 --> 0:39:34,08
Shaun: I totally forgot about maintenance_work_mem
and autovacuum_work_mem

791
0:39:34,08 --> 0:39:37,12
because those, you don't
really think about those because

792
0:39:37,12 --> 0:39:39,62
the 1 that really bites everyone
is work_mem because they set

793
0:39:39,62 --> 0:39:41,88
it to some value and then it explodes
on them.

794
0:39:41,94 --> 0:39:44,52
But yeah, the other 2 definitely
are a factor.

795
0:39:45,06 --> 0:39:50,68
Nik: Swap, like swap, like I always
try to avoid swap on Postgres

796
0:39:50,68 --> 0:39:52,78
machines, I remember, but it's
very old.

797
0:39:52,78 --> 0:39:57,88
I haven't revisited this topic
because I remember dealing with

798
0:39:58,48 --> 0:40:1,94
database, Postgres database, which
experiences heavy swap.

799
0:40:2,22 --> 0:40:4,02
Since then, I always avoided it.

800
0:40:4,02 --> 0:40:6,94
But then I remember Bruce Momjian
said, we should just, small

801
0:40:6,94 --> 0:40:7,9
swap is good.

802
0:40:8,0 --> 0:40:9,78
I said, no, it is completely avoided.

803
0:40:9,78 --> 0:40:12,98
I would better see out of memory
and fix my memory settings and

804
0:40:12,98 --> 0:40:13,58
so on.

805
0:40:13,58 --> 0:40:18,78
What's your opinion about having
swap enabled on the machine

806
0:40:18,78 --> 0:40:19,44
with Postgres?

807
0:40:20,34 --> 0:40:24,52
Shaun: The problem I usually see
with swap is you can't really

808
0:40:24,52 --> 0:40:27,04
account for what the kernel or
the memory pressure systems will

809
0:40:27,04 --> 0:40:27,54
do.

810
0:40:28,08 --> 0:40:32,02
And I've had problems in the past
with previous kernels doing

811
0:40:32,02 --> 0:40:33,14
things that they shouldn't.

812
0:40:33,58 --> 0:40:36,24
So I try to get as much control
as I can.

813
0:40:36,34 --> 0:40:39,16
So in that case, I usually set
swappiness to 1, because if you

814
0:40:39,16 --> 0:40:42,54
set it too low or to 0, the memory
pressure systems go wonky.

815
0:40:43,46 --> 0:40:47,7
And then I set to some low amount,
like 2 gigs, 4 gigs, some

816
0:40:47,72 --> 0:40:51,02
token amount just so the kernel
has area to work with.

817
0:40:51,02 --> 0:40:53,98
Then I set overcommit_memory to
2 so you can't overcommit.

818
0:40:54,78 --> 0:40:59,18
And then I set overcommit_kbytes
to the exact amount of physical

819
0:40:59,18 --> 0:41:0,82
memory that there is on the system.

820
0:41:1,24 --> 0:41:5,8
So it will not use swap because
it basically can't, because it's

821
0:41:5,8 --> 0:41:7,32
been subtracted from the total.

822
0:41:8,3 --> 0:41:11,72
Any allocation has to be physically
backed by actual RAM, at

823
0:41:11,72 --> 0:41:14,06
least as far as the database is
concerned, so you don't end up

824
0:41:14,06 --> 0:41:18,48
with overcommit and the OOM killer
won't kick in because there's

825
0:41:18,48 --> 0:41:21,16
not anything using too much RAM,
because it can't.

826
0:41:21,18 --> 0:41:23,26
If you make a request, you simply
get denied.

827
0:41:23,32 --> 0:41:25,56
And then your backend will
go, oh, I can't allocate memory,

828
0:41:25,56 --> 0:41:27,08
so I won't run this query for you.

829
0:41:27,08 --> 0:41:29,68
And I'd rather have a failed query
and have someone have to go

830
0:41:29,68 --> 0:41:32,9
back and look at their query or
revise it or whatever, then take

831
0:41:32,9 --> 0:41:36,14
the system down because OOM killer
decided that there's a rogue

832
0:41:36,14 --> 0:41:36,64
process.

833
0:41:37,06 --> 0:41:41,82
So basically, it always comes down
to, as a DBA, getting as much

834
0:41:41,82 --> 0:41:44,58
control as you can over the system
and enforcing it stringently,

835
0:41:45,06 --> 0:41:48,76
which is a little harder to do
in the Kubernetes context because

836
0:41:49,14 --> 0:41:51,38
those limits aren't enforced the
same way.

837
0:41:52,28 --> 0:41:54,9
Nik: Right, and there is 1 more
point.

838
0:41:55,24 --> 0:41:58,88
We somehow also avoided an important
topic.

839
0:41:59,44 --> 0:42:3,42
We could plan everything very well,
shared_buffers,

840
0:42:3,42 --> 0:42:8,0
maintenance_work_mem for index creation,
autovacuum workers, then our backends

841
0:42:8,06 --> 0:42:11,54
with work_mem, But somehow we forget
about page cache.

842
0:42:11,54 --> 0:42:14,08
And in many systems, page cache
is super important.

843
0:42:15,1 --> 0:42:19,46
Sometimes, like if it's, for example,
if we had a situation when

844
0:42:19,46 --> 0:42:23,04
it was exceeding shared_buffers,
for example, a simple example,

845
0:42:23,3 --> 0:42:27,54
we perform minor upgrade, we restart
server, and we don't think

846
0:42:27,54 --> 0:42:30,44
about pre-warming because actually
there is page cache sitting

847
0:42:30,44 --> 0:42:33,92
there, which helps us to have better
performance sooner.

848
0:42:34,78 --> 0:42:37,2
We recently had an internal discussion
about that.

849
0:42:37,2 --> 0:42:42,54
Do we need to have a pre-warming,
pg_prewarm, automated or not

850
0:42:42,54 --> 0:42:43,04
automated?

851
0:42:43,28 --> 0:42:45,76
And the question is, if there is
huge page cache, probably we

852
0:42:45,76 --> 0:42:46,94
don't need to bother.

853
0:42:47,3 --> 0:42:53,28
But if we start tuning work_mem,
page cache will become thin and

854
0:42:53,3 --> 0:42:54,18
very narrow, right?

855
0:42:54,18 --> 0:42:56,14
And this can be a problem as well.

856
0:42:56,72 --> 0:42:58,08
So it's very tricky, right?

857
0:42:58,08 --> 0:43:0,88
To think about macro level and
everything.

858
0:43:2,34 --> 0:43:5,2
Shaun: That definitely directs
how you will want to choose your

859
0:43:5,2 --> 0:43:8,86
hardware because 1 of my talks
at Postgres Open, I think it was

860
0:43:8,86 --> 0:43:14,52
2012, was about our woes with trying
to get pre-warming working.

861
0:43:15,04 --> 0:43:17,44
We would have a Postgres crash
because we were using EDB at the

862
0:43:17,44 --> 0:43:20,84
time and there were, we were using
a couple of extensions that

863
0:43:20,84 --> 0:43:24,28
from EDB that were, let's just
say beta quality, but they were

864
0:43:24,28 --> 0:43:25,42
doing something we needed.

865
0:43:25,68 --> 0:43:27,94
So occasionally we'd get a crash,
fine, whatever.

866
0:43:28,26 --> 0:43:31,56
So the database goes down, suddenly
our shared_buffers have been

867
0:43:31,56 --> 0:43:35,76
invalidated, but we were also finding
that the page cache was

868
0:43:35,76 --> 0:43:39,68
not sufficient because a lot of
those backends, at least at the

869
0:43:39,68 --> 0:43:43,98
time, were Postgres backends and
they had their minimum memory

870
0:43:43,98 --> 0:43:47,28
allocation and The page caches
were too small because of all

871
0:43:47,28 --> 0:43:47,78
that.

872
0:43:48,12 --> 0:43:53,1
You end up with Postgres RAM, backend
allocations, and then a

873
0:43:53,1 --> 0:43:54,72
small functional page cache.

874
0:43:54,72 --> 0:43:58,92
So what ended up happening is,
after the crash, Postgres would

875
0:43:58,92 --> 0:44:2,7
take an hour to warm up based on
user queries.

876
0:44:2,9 --> 0:44:7,16
So that whole time your latency
jumps by like 20 times because

877
0:44:7,16 --> 0:44:9,5
you're running off of an old RAID
array or something.

878
0:44:10,52 --> 0:44:14,46
So our fix was to get a Fusion-io
drive, which would be equivalent

879
0:44:14,46 --> 0:44:20,2
to a 100, 000 IOPS device from EBS
or something, like an io2,

880
0:44:21,04 --> 0:44:24,96
or just a really high-end NVMe
M.2 stick or something.

881
0:44:24,96 --> 0:44:26,26
Nik: Local NVMe, yeah.

882
0:44:26,4 --> 0:44:27,14
It's great.

883
0:44:27,28 --> 0:44:30,24
Shaun: It was really the only way
out at the time because you'd

884
0:44:30,24 --> 0:44:36,28
see the spike of activity and the
I/O usage from iostat, right?

885
0:44:36,28 --> 0:44:40,38
It would shoot up to 100% usage
for, and it would just stay there

886
0:44:40,38 --> 0:44:41,26
for an hour.

887
0:44:41,78 --> 0:44:45,36
As soon as we upgraded to the newer
device, it would spike once

888
0:44:45,36 --> 0:44:47,96
in the beginning, as soon as the
crash was over, and then it

889
0:44:47,96 --> 0:44:49,26
would just hover around 20%.

890
0:44:49,9 --> 0:44:52,7
But that 20% was on a 100, 000 IOPS
device.

891
0:44:53,16 --> 0:44:56,66
So if it were anything less than
that, it would be a lot worse.

892
0:44:57,54 --> 0:45:2,72
So even page cache can't really
save you in certain circumstances.

893
0:45:2,8 --> 0:45:4,5
It really depends on your workload.

894
0:45:5,8 --> 0:45:9,06
And with Postgres needing 0.25
of your RAM, well, not needing,

895
0:45:9,14 --> 0:45:13,1
we recommend using 0.25 of your
RAM up to a certain limit.

896
0:45:13,78 --> 0:45:16,24
If your page cache is too big,
then you're essentially double

897
0:45:16,24 --> 0:45:16,74
buffering.

898
0:45:17,21 --> 0:45:21,58
Nik: Yeah, and you have much higher
likelihood that temporary

899
0:45:21,58 --> 0:45:23,42
files spilling to disk will happen,
right?

900
0:45:23,42 --> 0:45:25,78
So this is the whole point to avoid.

901
0:45:26,38 --> 0:45:29,34
This, the case you just described
exactly like what I'm saying

902
0:45:29,34 --> 0:45:32,32
about, and Having faster disks
definitely helps.

903
0:45:32,86 --> 0:45:36,42
These days we can have millions
of IOPS with local NVMEs, right?

904
0:45:36,94 --> 0:45:44,22
But I also think if we tune work_mem,
so we make page cache quite

905
0:45:44,22 --> 0:45:48,52
thin, and we need to think about
how fast or slow disks are.

906
0:45:48,52 --> 0:45:52,32
And if we know they are slow, then
maybe we should consider automatic

907
0:45:52,6 --> 0:45:53,1
pre-warming.

908
0:45:53,44 --> 0:45:57,392
Because pg_prewarm right now supports
automated pre-warming, so

909
0:45:57,392 --> 0:46:2,68
it can capture maybe this is the
exact moment, slow disks and

910
0:46:2,68 --> 0:46:4,9
small page cache and which should
start.

911
0:46:4,96 --> 0:46:8,62
This actually adds to the whole
picture of tuning work_mem, right?

912
0:46:8,94 --> 0:46:12,1
Shaun: Yeah, and back when this
happened, pg_prewarm wasn't really

913
0:46:12,1 --> 0:46:12,76
a thing.

914
0:46:13,32 --> 0:46:16,58
So I cheated by just like using
dd.

915
0:46:17,22 --> 0:46:20,98
So I queried the catalog, figured
out which backend files went

916
0:46:20,98 --> 0:46:23,76
with the most used tables, and
I would just, before I started

917
0:46:23,76 --> 0:46:26,78
the server, I would dd them all
into memory and then I would

918
0:46:26,78 --> 0:46:27,48
start Postgres.

919
0:46:27,52 --> 0:46:31,26
And then that solved 80% of the
problem, but that wasn't sustainable

920
0:46:31,26 --> 0:46:33,3
long term, so that's why we bought
the storage.

921
0:46:33,34 --> 0:46:34,92
But there's ways you can get around
it.

922
0:46:34,92 --> 0:46:36,24
So definitely pg_prewarm.

923
0:46:36,88 --> 0:46:39,52
It's an extension people don't
really think about, because it's

924
0:46:39,52 --> 0:46:41,98
not really a problem so much anymore,
because everyone's got

925
0:46:41,98 --> 0:46:43,78
infinite IOPS, it's roughly.

926
0:46:44,24 --> 0:46:48,96
But if you don't, and you don't
want to pay io2 fees or load

927
0:46:48,96 --> 0:46:52,32
up your system with expensive storage,
then pre-warming is still

928
0:46:52,32 --> 0:46:53,0
an option.

929
0:46:53,2 --> 0:46:56,6934
Nik: I agree with you and like
bigger databases with local NVMes,

930
0:46:56,6934 --> 0:47:1,32
it's like we know a few companies
who bet on it heavily, right?

931
0:47:1,52 --> 0:47:6,3
But also there are many more smaller
clusters, usually single

932
0:47:6,3 --> 0:47:13,98
node clusters, which are needed
to support some AI building,

933
0:47:14,16 --> 0:47:15,64
AI builders products.

934
0:47:15,78 --> 0:47:19,02
They just experiment a lot and
they don't need serious database,

935
0:47:19,02 --> 0:47:20,78
but they still need some database.

936
0:47:21,04 --> 0:47:24,9
And in that case, tuning, like
what we just discussed could be

937
0:47:24,9 --> 0:47:30,06
used for them because tuning work_mem,
so queries are good enough

938
0:47:30,06 --> 0:47:34,6
in terms of performance, but also
you know that restart will

939
0:47:34,6 --> 0:47:39,38
help you survive not being super
slow for 0.5 an hour.

940
0:47:39,72 --> 0:47:43,64
So I think there are interesting
cases and this case with smaller

941
0:47:44,02 --> 0:47:49,32
databases because of AI, I think
they will grow a lot.

942
0:47:50,64 --> 0:47:54,66
Shaun: Well, I mean, that's actually
a good point, because especially

943
0:47:54,66 --> 0:47:57,84
if you have database branching,
like I know that you have in

944
0:47:57,84 --> 0:48:1,5
your product, you've got the ability
to fork off, like dev instances

945
0:48:1,5 --> 0:48:3,66
of databases, and those are entirely
cold.

946
0:48:3,9 --> 0:48:6,34
Without proper backing on them,
they're going to be slow for

947
0:48:6,34 --> 0:48:7,78
a while, at least after start.

948
0:48:8,16 --> 0:48:8,36
In the

949
0:48:8,36 --> 0:48:12,22
Nik: case of DBLab, it's ZFS, and
ZFS has ARC.

950
0:48:12,26 --> 0:48:17,22
We usually allocate 0.5 of memory
to it, so Those blocks are

951
0:48:17,22 --> 0:48:20,02
warmed up already usually, so that
helps a lot.

952
0:48:20,02 --> 0:48:21,34
We have a different concern.

953
0:48:21,68 --> 0:48:25,24
Developers ask us, can you implement
cold cache?

954
0:48:25,24 --> 0:48:29,7
Because we study EXPLAIN plans,
we want cold cache to see how

955
0:48:29,7 --> 0:48:30,66
the worst case.

956
0:48:30,92 --> 0:48:35,1
And this is tricky in this architecture
because it's a multi-tenant

957
0:48:35,32 --> 0:48:35,82
thing.

958
0:48:36,38 --> 0:48:40,94
A lot of Postgres exploration happening
on the same VM and we

959
0:48:40,94 --> 0:48:42,1
need that cache actually.

960
0:48:43,52 --> 0:48:45,32
Shaun: Yeah, how would you even
do that?

961
0:48:45,32 --> 0:48:48,18
You'd have to move it over to another
instance, and that's cold.

962
0:48:48,2 --> 0:48:51,96
Nik: Yeah, you cannot do it without
losing the common cache.

963
0:48:51,96 --> 0:48:55,36
But common cache helps others,
because we have different use

964
0:48:55,36 --> 0:48:55,68
cases.

965
0:48:55,68 --> 0:48:59,38
It's not always exploration of
EXPLAIN plans, but also sometimes

966
0:48:59,38 --> 0:49:0,98
just preview environments for testing.

967
0:49:1,64 --> 0:49:3,96
And those guys want better performance.

968
0:49:4,82 --> 0:49:6,6
So it's a complex topic.

969
0:49:6,9 --> 0:49:11,04
But we learned very early, actually,
that we should match work_mem

970
0:49:11,04 --> 0:49:14,54
to production because it affects
the planner behavior, which

971
0:49:14,54 --> 0:49:15,18
I mentioned.

972
0:49:16,22 --> 0:49:18,46
This is important for any lab environment.

973
0:49:19,12 --> 0:49:20,42
Okay, I'm out of questions.

974
0:49:20,42 --> 0:49:24,14
We touched a lot, like we went
quite broadly, touched a lot of

975
0:49:24,14 --> 0:49:25,06
additional questions.

976
0:49:25,08 --> 0:49:25,76
Thank you so much.

977
0:49:25,76 --> 0:49:27,68
It was a very interesting discussion.

978
0:49:29,16 --> 0:49:31,76
I especially like you confirmed
a lot of things I have in my

979
0:49:31,76 --> 0:49:33,98
head, so it's great to hear confirmation.

980
0:49:34,66 --> 0:49:37,2
But also learned a lot of new stuff,
thank you so much.

981
0:49:37,2 --> 0:49:41,12
I will follow your new blog posts,
don't stop writing, it's very

982
0:49:41,12 --> 0:49:42,02
interesting always.

983
0:49:42,26 --> 0:49:43,2
Shaun: Yeah, I don't plan to.

984
0:49:43,2 --> 0:49:45,9
And I always like to remind people
that if you've ever heard

985
0:49:45,9 --> 0:49:49,4
of Perl as the pathologically eclectic
rubbish lister, that's

986
0:49:49,4 --> 0:49:50,86
basically how my brain works.

987
0:49:51,82 --> 0:49:53,8
Nik: Okay, okay.

988
0:49:54,48 --> 0:49:55,58
Great, yeah.

989
0:49:56,68 --> 0:49:57,84
Michael: Well, really nice to meet
you.

990
0:49:57,84 --> 0:49:59,08
Thanks for joining us.

991
0:49:59,16 --> 0:49:59,68
Nik: Thank you.

992
0:49:59,68 --> 0:50:0,16
Shaun: You too.

993
0:50:0,16 --> 0:50:1,18
Hope you have a good day.

994
0:50:1,32 --> 0:50:2,36
Nik: You have a great week.