1
0:0:0,099999994 --> 0:0:3,28
Michael: Hello and welcome to Postgres FM, a show about all things

2
0:0:3,28 --> 0:0:3,78
PostgreSQL.

3
0:0:3,9599998 --> 0:0:7,66
I am Michael, founder of pgMustard and I'm joined as usual by

4
0:0:7,66 --> 0:0:9,16
Nik, founder of PostgresAI.

5
0:0:9,16 --> 0:0:9,84
Hey Nik!

6
0:0:10,44 --> 0:0:11,82
Nikolay: Hi Michael, how are you?

7
0:0:12,28 --> 0:0:13,82
Michael: I am doing okay, thank you.

8
0:0:13,82 --> 0:0:14,639999
How are you?

9
0:0:15,719999 --> 0:0:19,74
Nikolay: Recovering from some bad flu but all good.

10
0:0:20,46 --> 0:0:21,92
Michael: Yeah, good to have you back.

11
0:0:22,36 --> 0:0:24,9
And yeah, so what are we talking about today?

12
0:0:26,04 --> 0:0:30,92
Nikolay: Let's talk about metadata and various meanings and including

13
0:0:30,92 --> 0:0:33,98
like comments, maybe more, right?

14
0:0:34,76 --> 0:0:38,86
Comments to database objects, comments inside queries, inside

15
0:0:38,86 --> 0:0:42,84
PL/pgSQL, stored procedures and functions, just broadly.

16
0:0:43,14 --> 0:0:48,22
What kind of metadata it makes sense to store and how?

17
0:0:49,12 --> 0:0:50,7
Pros and cons, I don't know.

18
0:0:50,82 --> 0:0:51,66
Michael: Yeah, I think so.

19
0:0:51,66 --> 0:0:55,58
And also some side effects of having it in there or use cases.

20
0:0:55,68 --> 0:0:58,22
I was actually quite surprised we hadn't talked about it already.

21
0:0:58,84 --> 0:1:3,82
I saw a recent blog post by Markus Winand on Modern SQL about,

22
0:1:3,82 --> 0:1:7,06
I think it was mostly actually about, it was called SQL comments,

23
0:1:7,06 --> 0:1:9,64
but it was mostly about query.

24
0:1:10,6 --> 0:1:11,5
I think so.

25
0:1:11,5 --> 0:1:15,04
Nikolay: It doesn't have a date and by default I assume all the

26
0:1:15,04 --> 0:1:20,84
posts are like very old somehow I think so, but yeah maybe it's

27
0:1:20,84 --> 0:1:21,64
new stuff.

28
0:1:21,76 --> 0:1:23,42
But it's very small post right?

29
0:1:23,72 --> 0:1:26,84
Michael: Yeah but small but also it's 1 of those on Modern SQL

30
0:1:26,84 --> 0:1:31,0
he has these like nice visualizations of which databases have

31
0:1:31,0 --> 0:1:36,22
supported, which syntax from which dates, and I always enjoy

32
0:1:36,22 --> 0:1:37,64
those when more

33
0:1:37,64 --> 0:1:42,18
Nikolay: when also in Postgres, but practically, I'm, I rarely

34
0:1:42,62 --> 0:1:44,6
leave the ecosystem of Postgres.

35
0:1:44,68 --> 0:1:48,16
So it's just okay, good to know Postgres is good here as well

36
0:1:48,16 --> 0:1:50,64
and here and here and that's it.

37
0:1:50,94 --> 0:1:54,32
Michael: Occasionally you come across them where Postgres doesn't

38
0:1:54,32 --> 0:1:57,36
support some syntax that another database does.

39
0:1:57,36 --> 0:2:1,3
It's not common but I have been caught out a few times thinking

40
0:2:1,3 --> 0:2:5,16
oh wow that's or not caught out but surprised thinking oh it's

41
0:2:5,16 --> 0:2:8,82
interesting there are somewhere Oracle or SQL Server have some

42
0:2:8,94 --> 0:2:12,5
new syntax that's even in the standard that we don't yet have.

43
0:2:12,5 --> 0:2:16,16
So it isn't often, and it is really nice to see the especially

44
0:2:16,16 --> 0:2:20,86
the dark green ticks it's like fully compliant tick not no no

45
0:2:20,86 --> 0:2:24,52
little subtext on the and how it deviates from the standard or

46
0:2:24,52 --> 0:2:28,86
anything else yeah but that was about query level comments so

47
0:2:28,86 --> 0:2:31,06
so single line and multi-line comments

48
0:2:31,26 --> 0:2:35,64
Nikolay: yeah in the main like SQL runtime like usually there

49
0:2:35,64 --> 0:2:38,74
is a standard minus comment style.

50
0:2:39,64 --> 0:2:43,26
And it's interesting to see that MySQL and MariaDB, they have

51
0:2:43,26 --> 0:2:47,02
issues there if you, there's a
requirement to have whitespace

52
0:2:47,08 --> 0:2:49,14
after 2 hyphens.

53
0:2:49,82 --> 0:2:50,32
Yeah.

54
0:2:50,54 --> 0:2:51,04
Okay.

55
0:2:51,6 --> 0:2:55,68
But yeah, Postgres supports that
because it's standard and also

56
0:2:55,68 --> 0:2:58,06
it supports C-style comments.

57
0:2:58,18 --> 0:2:58,26
Exactly.

58
0:2:58,26 --> 0:2:59,88
/* ... */

59
0:3:0,8 --> 0:3:1,3
Michael: Yeah.

60
0:3:1,74 --> 0:3:5,46
Which crucially can be, yeah, they're
good for multi-line comments,

61
0:3:5,46 --> 0:3:6,02
aren't they?

62
0:3:6,02 --> 0:3:10,84
So you can have the first slash
star, and then start writing.

63
0:3:10,84 --> 0:3:11,2
Yeah, and also,

64
0:3:11,2 --> 0:3:14,44
Nikolay: I try to use them all
the time because they are predictable.

65
0:3:14,64 --> 0:3:18,14
If you use SQL standard comments,
you might have issues when,

66
0:3:18,14 --> 0:3:23,56
for example, in some cases your
query, line endings are stripped

67
0:3:23,56 --> 0:3:24,52
from your query.

68
0:3:24,86 --> 0:3:29,98
In this case it's quite a messed
up situation because everything

69
0:3:29,98 --> 0:3:32,02
becomes a comment.

70
0:3:32,32 --> 0:3:34,08
Michael: Yeah that's a really good
point and I wasn't going to

71
0:3:34,08 --> 0:3:36,42
bring this up till later because
it felt like a really minor

72
0:3:36,42 --> 0:3:41,44
detail, but I was relatively surprised
to read that psql strips

73
0:3:41,6 --> 0:3:46,1
out the single line, the standard
comments before even sending

74
0:3:46,1 --> 0:3:48,88
them to the server, whereas it
doesn't for multi-line.

75
0:3:48,94 --> 0:3:50,74
So yeah, exactly client side.

76
0:3:50,74 --> 0:3:55,56
So when they're used, for example,
if you want that information

77
0:3:56,02 --> 0:4:0,2
server-side for some reason, it
needs to be 1 of the C style

78
0:4:0,28 --> 0:4:0,78
comments.

79
0:4:1,3 --> 0:4:1,62
Nikolay: Yeah.

80
0:4:1,62 --> 0:4:6,06
And we do have situations where
we appreciate comments, for example,

81
0:4:6,06 --> 0:4:8,08
coming to pg_stat_statements.

82
0:4:8,36 --> 0:4:11,02
Let's start with pg_stat_activity,
first of all.

83
0:4:11,2 --> 0:4:16,5
Comments for queries can be super
useful to pass some, I don't

84
0:4:16,5 --> 0:4:21,18
know, like trace ID, like origin,
even URL sometimes.

85
0:4:21,28 --> 0:4:24,72
Like you can indicate which part
of your application generated

86
0:4:24,72 --> 0:4:27,42
this query or participated in generation, right?

87
0:4:27,5 --> 0:4:30,06
There are some libraries for different
languages.

88
0:4:30,06 --> 0:4:32,42
I remember 1 for Ruby on Rails.

89
0:4:33,04 --> 0:4:35,34
It's called marginalia.

90
0:4:35,5 --> 0:4:36,3
I don't know.

91
0:4:36,38 --> 0:4:39,36
Michael: It would be margin, because
it's from the, from, I think

92
0:4:39,36 --> 0:4:42,8
the, yeah, from the margins, like
when you, in a book, if you

93
0:4:42,8 --> 0:4:44,38
write some little notes about it.

94
0:4:44,38 --> 0:4:44,82
Yeah.

95
0:4:44,82 --> 0:4:45,08
Nikolay: Yeah.

96
0:4:45,08 --> 0:4:46,56
So it's very useful.

97
0:4:46,7 --> 0:4:50,2
It can bring automatically generated
comments to your queries

98
0:4:50,32 --> 0:4:56,08
which are coming from Ruby on Rails
ORM and it's helpful to trace,

99
0:4:57,18 --> 0:5:1,68
to analyze and quickly find where
this query is coming from and

100
0:5:1,68 --> 0:5:6,26
also how like we obviously can
see them in pg_stat_activity.

101
0:5:7,08 --> 0:5:12,08
The downside obviously that it
increases the size of the query

102
0:5:12,1 --> 0:5:17,12
and by default pg_stat_activity the
query column it has only 1024

103
0:5:18,14 --> 0:5:18,64
characters.

104
0:5:19,32 --> 0:5:21,14
track_activity_query_size setting.

105
0:5:21,82 --> 0:5:24,18
We usually recommend to bump it
like 10x.

106
0:5:24,62 --> 0:5:25,08
Yeah.

107
0:5:25,08 --> 0:5:27,54
Because we have memory for it,
let's do it.

108
0:5:27,74 --> 0:5:32,06
Because queries tend to be bigger
and bigger over years, right?

109
0:5:32,12 --> 0:5:34,2
So 1000 is not enough.

110
0:5:34,9 --> 0:5:40,58
And of course, this comment is
put in front of the query and

111
0:5:40,58 --> 0:5:46,14
you might not be able to see, unfortunately,
how SQL is written.

112
0:5:46,16 --> 0:5:47,36
It starts with SELECT.

113
0:5:47,98 --> 0:5:51,7
And sometimes ORMs, they put a
lot of columns there.

114
0:5:52,4 --> 0:5:59,82
And with comment plus column list,
you might see that it's truncated

115
0:6:0,3 --> 0:6:3,1
and you don't see the FROM clause
at all.

116
0:6:3,58 --> 0:6:5,82
To see the FROM clause is super
essential.

117
0:6:6,06 --> 0:6:10,28
There are opinions that SQL, like
SELECT, is the wrong way around.

118
0:6:10,28 --> 0:6:13,7
Reorganized starting FROM, because
this is where things start

119
0:6:13,7 --> 0:6:14,52
to be executed.

120
0:6:14,76 --> 0:6:16,76
But It is what it is.

121
0:6:17,44 --> 0:6:22,9
And yeah, so it's if you have a
huge helpful comment, it might

122
0:6:22,9 --> 0:6:27,1
bite you here because you might
have your query trimmed faster

123
0:6:27,16 --> 0:6:28,52
and you don't see it.

124
0:6:28,98 --> 0:6:30,3
But it is what it is, right?

125
0:6:30,3 --> 0:6:34,4
So my recommendation is comments
are super helpful here, like

126
0:6:34,4 --> 0:6:37,6
from this library or you can write
your own or something, just

127
0:6:37,6 --> 0:6:40,64
to trace the origin of the query
and so on.

128
0:6:41,04 --> 0:6:45,24
And you just need to bump your
track_activity_query_size to have

129
0:6:45,24 --> 0:6:45,74
bigger.

130
0:6:45,84 --> 0:6:47,48
Unfortunately, it requires a restart.

131
0:6:47,48 --> 0:6:49,66
That's the downside of that change.

132
0:6:50,38 --> 0:6:52,96
Michael: I actually don't know
for sure, but I was looking at

133
0:6:52,96 --> 0:6:57,44
the marginalia documentation briefly
just before this and noticed

134
0:6:57,44 --> 0:6:59,48
that the comments were going at
the end of the query.

135
0:6:59,48 --> 0:7:1,7
So it might be that they've deliberately
done that.

136
0:7:1,8 --> 0:7:4,32
Nikolay: In this case you don't
see it in pg_stat_activity

137
0:7:4,32 --> 0:7:5,14
maybe, right?

138
0:7:5,74 --> 0:7:6,66
Michael: Yeah, good point.

139
0:7:6,96 --> 0:7:8,5
Maybe it's even worse, yeah.

140
0:7:9,06 --> 0:7:13,34
Nikolay: And what would be great
actually to, I don't know, this

141
0:7:13,34 --> 0:7:17,68
is strange, but I recently implemented
in our monitoring, I was

142
0:7:17,68 --> 0:7:22,5
hearing it's difficult, but then
I just took Claude Code and implemented

143
0:7:22,54 --> 0:7:23,9
it, and it was quite successful.

144
0:7:23,92 --> 0:7:27,94
So I implemented the approach I
saw in other systems.

145
0:7:28,68 --> 0:7:35,14
So We have a mode in our dashboards
in Grafana, we basically

146
0:7:35,14 --> 0:7:35,64
switch.

147
0:7:36,06 --> 0:7:39,86
You can see the whole query or
you can see the query with stripped

148
0:7:40,16 --> 0:7:41,6
less important parts.

149
0:7:41,68 --> 0:7:45,06
I consider the column list as less
important part and I just

150
0:7:45,06 --> 0:7:49,02
replace it with not dot dot dot
but a single symbol.

151
0:7:49,28 --> 0:7:51,4
There is a unique triple dot.

152
0:7:51,76 --> 0:7:54,92
And in this case you have more useful information and comments

153
0:7:54,92 --> 0:7:56,78
I think are also stripped in this case.

154
0:7:57,34 --> 0:7:57,84
Right?

155
0:7:57,88 --> 0:7:58,58
Makes sense.

156
0:7:58,62 --> 0:8:1,56
But yeah, depending on the situation, comments can be helpful

157
0:8:1,56 --> 0:8:6,6
for various observability activities to connect some dots.

158
0:8:6,9 --> 0:8:10,88
And it's interesting how comments in queries, for example, coming

159
0:8:10,88 --> 0:8:11,64
in front of...

160
0:8:11,64 --> 0:8:15,24
Oh, by the way, a big downside of having a comment in front of

161
0:8:15,24 --> 0:8:20,5
a query is also that in our old checkup we had analysis so-called

162
0:8:20,86 --> 0:8:21,36
mid-level.

163
0:8:22,62 --> 0:8:25,9
We had high level, it's just the whole workload, all metrics

164
0:8:25,9 --> 0:8:27,08
for the whole workload.

165
0:8:27,26 --> 0:8:30,1
According to like from pg_stat_statements, of course it's not the

166
0:8:30,1 --> 0:8:35,28
whole, usually it's only 5000 by default queries, normalized

167
0:8:35,34 --> 0:8:35,84
queries.

168
0:8:36,42 --> 0:8:39,84
But the lowest level it's individual normalized query, right?

169
0:8:40,08 --> 0:8:43,58
Mid-level is when we call it so-called first word analysis.

170
0:8:43,78 --> 0:8:46,1
So we just, which word is the first?

171
0:8:46,1 --> 0:8:46,8
Okay, SELECT.

172
0:8:46,8 --> 0:8:48,06
Or is it UPDATE?

173
0:8:48,12 --> 0:8:52,5
There is a trick with, because with can combine multiple things

174
0:8:52,5 --> 0:8:53,4
in 1 query.

175
0:8:53,54 --> 0:8:58,1
But usually it's quite helpful to understand how many of queries

176
0:8:58,1 --> 0:9:3,2
in terms of calls or overall timing, some metric, how many of

177
0:9:3,2 --> 0:9:6,8
them are SELECTs versus UPDATEs and DELETEs.

178
0:9:7,16 --> 0:9:12,34
You can get stats for writes from tuple statistics, but to analyze

179
0:9:12,38 --> 0:9:14,68
at query level, it's not like straightforward.

180
0:9:15,04 --> 0:9:16,28
So this is what we did.

181
0:9:16,28 --> 0:9:21,5
And then I remember having comments destroy this analysis.

182
0:9:21,7 --> 0:9:25,08
Of course, this is a method, like, it's easy to fix, right?

183
0:9:25,08 --> 0:9:26,5
You can just ignore comments.

184
0:9:27,62 --> 0:9:28,58
But this is interesting.

185
0:9:28,6 --> 0:9:32,14
So bringing some observability helpers, you might destroy some

186
0:9:32,14 --> 0:9:34,06
observability tools sometimes.

187
0:9:34,24 --> 0:9:34,74
Michael: Yeah.

188
0:9:35,46 --> 0:9:39,1
The other thing I wanted to make sure we mentioned in this area

189
0:9:39,18 --> 0:9:41,82
was how pg_stat_statements works

190
0:9:41,82 --> 0:9:42,32
Nikolay: because it

191
0:9:42,32 --> 0:9:43,48
Michael: does denormalize.

192
0:9:44,04 --> 0:9:48,26
So yeah, so comments, I think it's a quite good trade-off actually.

193
0:9:48,26 --> 0:9:51,8
I think I quite like the decision they've made so they denormalize

194
0:9:52,04 --> 0:9:54,02
So you if you have the same?

195
0:9:54,4 --> 0:9:57,16
Query, but with 2 different comments, they will count as the

196
0:9:57,16 --> 0:9:57,66
same query.

197
0:9:57,66 --> 0:10:0,48
You'll get the same query ID They'll be grouped together, But

198
0:10:0,48 --> 0:10:3,88
only the first 1 that gets stored under that query ID

199
0:10:4,86 --> 0:10:5,36
Nikolay: What?

200
0:10:6,1 --> 0:10:6,6
Unchanged

201
0:10:7,06 --> 0:10:10,24
Michael: it's so it's not unchanged
with the comment exactly

202
0:10:10,68 --> 0:10:13,52
Nikolay: For example, imagine we
have a simple select with blah

203
0:10:13,52 --> 0:10:17,04
blah blah where I don't know like
email equals some value or

204
0:10:17,04 --> 0:10:19,02
lower of email equals some value.

205
0:10:19,02 --> 0:10:21,6
And you decide to put this email
as a comment.

206
0:10:22,7 --> 0:10:26,78
This is how your PII leaks to pg_stat_statements,
but only the

207
0:10:26,78 --> 0:10:27,54
first occurrence.

208
0:10:27,54 --> 0:10:28,36
That's weird.

209
0:10:29,06 --> 0:10:29,56
So.

210
0:10:30,02 --> 0:10:30,2
Yeah.

211
0:10:30,2 --> 0:10:33,3
Michael: I don't know if I've seen
P, have you seen PII in comments?

212
0:10:33,34 --> 0:10:34,08
Nikolay: That's interesting.

213
0:10:34,08 --> 0:10:35,86
Imaginary situation, it's possible,
right?

214
0:10:35,86 --> 0:10:36,36
Michael: Sure.

215
0:10:36,74 --> 0:10:38,9
Nikolay: Yeah, it's definitely
possible.

216
0:10:38,9 --> 0:10:43,0
For example, we might, we can say,
okay, this is user with email,

217
0:10:43,32 --> 0:10:45,14
this is acting here.

218
0:10:45,6 --> 0:10:49,5
And we have, it's leaked to pg_stat_statements, everything else

219
0:10:49,5 --> 0:10:52,74
is stripped, so like it's normalized,
we don't see parameters,

220
0:10:52,76 --> 0:10:54,96
but comments we see only first
occurrence.

221
0:10:54,96 --> 0:10:55,94
This is weird.

222
0:10:56,48 --> 0:10:56,98
Michael: Yeah.

223
0:10:57,04 --> 0:11:0,04
It's weird but I quite like, imagine,
I was thinking what's the

224
0:11:0,04 --> 0:11:0,54
alternative?

225
0:11:0,66 --> 0:11:5,56
Either they don't show comments
at all, or they have to store

226
0:11:5,56 --> 0:11:7,58
loads of copies.

228
0:11:7,58 --> 0:11:10,02
Nikolay: Yeah, there are pros and
cons here.

229
0:11:11,32 --> 0:11:14,16
What I would like to see, it's
an unresolved problem still.

230
0:11:14,24 --> 0:11:20,64
I would like to have ability to
pass comments as maybe key value

231
0:11:21,2 --> 0:11:24,64
pairs, comma separated or white
space separated.

232
0:11:25,32 --> 0:11:28,7
For example, you say, okay, application
ID this or application

233
0:11:28,9 --> 0:11:32,5
component, component ID this, like
correlation ID, many things,

234
0:11:32,56 --> 0:11:33,06
URL.

235
0:11:34,74 --> 0:11:41,24
And then to be able to have aggregated
metrics based on those

236
0:11:41,68 --> 0:11:42,18
dimensions.

237
0:11:44,44 --> 0:11:48,62
So for example, I know my application
consists of various components,

238
0:11:48,68 --> 0:11:55,24
I pass this component ID to comments,
and then I want to see

239
0:11:55,84 --> 0:12:0,3
how many calls overall for this
query particularly, how many

240
0:12:0,3 --> 0:12:3,8
calls are coming from that component
ID versus different component

241
0:12:3,8 --> 0:12:4,3
ID.

242
0:12:4,82 --> 0:12:8,46
And this type of analysis would
be super powerful, basically

243
0:12:8,56 --> 0:12:11,26
custom dimensions for pg_stat_statements.

244
0:12:11,96 --> 0:12:17,22
I know it was discussed for pg_stat_kcache And the consensus was

245
0:12:17,22 --> 0:12:20,9
it should be in pg_stat_statements,
and I don't, it was many years

246
0:12:20,9 --> 0:12:23,6
ago and I don't know how it ended.

247
0:12:23,94 --> 0:12:27,76
But definitely there is like desire
to have some kind of analysis,

248
0:12:27,8 --> 0:12:31,86
but I imagine it can be quite expensive
if implemented not in

249
0:12:31,86 --> 0:12:32,7
a good way.

250
0:12:33,26 --> 0:12:35,86
But yeah, this is what people want.

251
0:12:35,86 --> 0:12:41,06
They come with questions like, okay, how to identify, we have 

252
0:12:41,06 --> 0:12:45,06
like many parts of, we have monolith, first of all, in terms 

253
0:12:45,06 --> 0:12:45,74
of code. 

254
0:12:46,3 --> 0:12:48,84
We have many teams working on different parts. 

255
0:12:49,74 --> 0:12:54,38
How to identify which part is most expensive in terms of CPU 

256
0:12:54,38 --> 0:12:55,42
usage for example. 

257
0:12:56,32 --> 0:12:59,08
Or time spent by Database to process this. 

258
0:12:59,5 --> 0:13:2,78
And here is where such kind of analysis would be super helpful. 

259
0:13:3,82 --> 0:13:7,04
Michael: Do you see people doing it, like you could for example 

260
0:13:7,04 --> 0:13:10,52
have them connect with different roles and that would then be 

261
0:13:10,52 --> 0:13:11,02
logged? 

262
0:13:11,28 --> 0:13:14,44
Nikolay: Yes, this is 1 way, this is indirect, like you could 

263
0:13:14,44 --> 0:13:19,78
just downgrade this, like I just pictured very good, flexible 

264
0:13:20,38 --> 0:13:24,4
approach that you could do many dimensions, but you can downgrade 

265
0:13:24,52 --> 0:13:28,14
and let your different parts of applications speak using different 

266
0:13:28,14 --> 0:13:32,26
users and use the fact that pg_stat_statements has user ID. 

267
0:13:32,52 --> 0:13:36,62
The downside of this approach would be you need to think how 

268
0:13:36,62 --> 0:13:40,64
to manage pools in PgBouncer, for example, because different 

269
0:13:40,64 --> 0:13:46,5
users means you need to set different quotas, pool sizes, right? 

270
0:13:46,96 --> 0:13:50,4
This can be quite inflexible. 

271
0:13:50,82 --> 0:13:54,76
If you want to have a single quota for all users, how? 

272
0:13:55,24 --> 0:13:57,42
Maybe it's a question to PgBouncer. 

273
0:13:57,84 --> 0:14:0,36
Maybe it's actually possible, maybe no. 

274
0:14:0,52 --> 0:14:2,06
It's another question, right? 

275
0:14:2,16 --> 0:14:6,36
So once I like to, this is, I think, good practice to separate 

276
0:14:6,62 --> 0:14:11,04
your workload to different segments and each segment works under 

277
0:14:11,04 --> 0:14:12,24
different DB users. 

278
0:14:12,52 --> 0:14:16,46
But there is also management overhead for maintaining various 

279
0:14:16,56 --> 0:14:17,82
limits and so on. 

280
0:14:18,94 --> 0:14:19,44
Michael: Yeah. 

281
0:14:19,46 --> 0:14:19,64
It's 

282
0:14:19,64 --> 0:14:21,24
Nikolay: all about, I forgot how. 

283
0:14:21,34 --> 0:14:24,56
So it's an interesting, maybe some of our listeners has a clear 

284
0:14:24,56 --> 0:14:25,06
picture. 

285
0:14:25,68 --> 0:14:29,82
What best practice would be here and please leave a comment somewhere. 

286
0:14:30,8 --> 0:14:31,02
Michael: Yeah. 

287
0:14:31,02 --> 0:14:33,9
Or even just what people are doing, what you're doing in practice, 

288
0:14:33,9 --> 0:14:36,28
it'd be good to hear what solutions people have come up with. 

289
0:14:36,28 --> 0:14:39,16
Nikolay: What's possible right now I think is if you, for example, 

290
0:14:39,16 --> 0:14:44,72
have quite high track activity query size, like 10k for example, 

291
0:14:45,04 --> 0:14:48,78
I see people even go further, even more, like 30k. 

292
0:14:49,84 --> 0:14:55,02
You use comments from marginalia or something, and you have 

293
0:14:55,84 --> 0:14:59,86
already started to appreciate performance insights or wait event 

294
0:14:59,86 --> 0:15:0,22
analysis. 

295
0:15:0,22 --> 0:15:2,06
We talked about it a lot, right? 

296
0:15:2,38 --> 0:15:3,9
In this case, you can start... 

297
0:15:4,84 --> 0:15:8,16
You can recognize different wait events and how many active sessions 

298
0:15:8,3 --> 0:15:12,14
and segment them by wait event type and wait event. 

299
0:15:12,2 --> 0:15:16,04
In this case, you can bring this knowledge about dimensions to 

300
0:15:16,04 --> 0:15:21,5
this analysis and start saying, okay, we have usually like this

301
0:15:21,5 --> 0:15:27,68
amount of sessions spending on
I-O, and among them, like 90%

302
0:15:27,94 --> 0:15:31,66
is coming from that part of our
application according to comments.

303
0:15:32,1 --> 0:15:33,24
This is quite powerful.

304
0:15:33,34 --> 0:15:37,36
And this you don't need to do anything
except like just to you

305
0:15:37,36 --> 0:15:41,18
don't need to change pg_stat_statements
or how Postgres works.

306
0:15:41,26 --> 0:15:42,9
It's possible right now already.

307
0:15:43,86 --> 0:15:46,4
Michael: Yeah, as long as 2 parts
of your application aren't

308
0:15:46,4 --> 0:15:47,78
doing the same query.

309
0:15:48,08 --> 0:15:48,84
Yeah, that would be fun.

310
0:15:48,84 --> 0:15:51,58
Nikolay: They can do the same query,
but they put different comments.

311
0:15:51,58 --> 0:15:53,28
Michael: Yeah, but in pg_stat_statements.

312
0:15:53,64 --> 0:15:54,44
Not in pg_stat_statements.

313
0:15:54,44 --> 0:15:57,08
Nikolay: No, no, I'm talking about
wait event analysis.

314
0:15:57,26 --> 0:15:57,76
Michael: Sorry.

315
0:15:57,98 --> 0:16:1,32
Nikolay: And I'm talking actually
about, we call it lazy approach,

316
0:16:1,36 --> 0:16:3,64
like sampling of pg_stat_activity.

317
0:16:3,66 --> 0:16:3,76
Fine.

318
0:16:3,76 --> 0:16:7,58
It cannot be super frequent because
there is overhead.

319
0:16:7,7 --> 0:16:10,76
For example, every second or every
5 seconds you sample pg_stat_activity

320
0:16:10,76 --> 0:16:13,68
activity and you have a raw query
from there, including comments

321
0:16:13,68 --> 0:16:14,36
as is.

322
0:16:15,04 --> 0:16:19,64
If you start using pg_wait_sampling,
which is great in terms of

323
0:16:19,64 --> 0:16:23,3
sampling rate, 10 milliseconds,
every 10 milliseconds by default

324
0:16:23,3 --> 0:16:27,68
it samples, but you lose this,
you lose the raw query and comments,

325
0:16:27,84 --> 0:16:30,56
and you have the same problem as
pg_stat_statements.

326
0:16:31,22 --> 0:16:35,42
These dimensions become not available
at pg_wait_sampling level.

327
0:16:35,86 --> 0:16:39,46
So anyway, this is super interesting
observability topic, I think.

328
0:16:40,24 --> 0:16:44,62
And what do you think about comments
which, I don't know, which

329
0:16:44,62 --> 0:16:48,08
are put inside PL/pgSQL functions,
for example?

330
0:16:48,34 --> 0:16:48,96
Michael: What for?

331
0:16:48,96 --> 0:16:51,8
Is it to describe behavior like
almost like code comments?

332
0:16:52,3 --> 0:16:55,76
Nikolay: Yeah it can be explanation
of this what this function

333
0:16:55,76 --> 0:16:59,06
does and every piece like if you
for example look at Postgres

334
0:16:59,06 --> 0:17:1,04
code it's very well commented.

335
0:17:2,38 --> 0:17:7,76
Comments are very thorough right
they can be huge sometimes you

336
0:17:7,76 --> 0:17:13,72
open some .c file and
a huge comment in the beginning

337
0:17:13,94 --> 0:17:15,58
explaining what's happening here.

338
0:17:15,96 --> 0:17:16,12
Yeah.

339
0:17:16,12 --> 0:17:20,96
Is it a good idea to put this to
function bodies?

340
0:17:21,28 --> 0:17:22,32
Michael: What's the downside?

341
0:17:22,36 --> 0:17:25,58
I think I generally err on the
side of commenting things.

342
0:17:25,58 --> 0:17:26,68
I like comments.

343
0:17:27,38 --> 0:17:32,62
Although, you brought up AI already,
I do find some of the LLM

344
0:17:32,98 --> 0:17:37,28
commenting excessive at times,
or like, maybe not excessive in

345
0:17:37,28 --> 0:17:40,44
the sense that there's large comments,
it's more that there are

346
0:17:40,44 --> 0:17:42,66
just comments, there are too many
comments, just comments at

347
0:17:42,66 --> 0:17:43,58
too many stages.

348
0:17:43,62 --> 0:17:48,28
I do like the Postgres style, but
they tend to be Huge comment

349
0:17:48,28 --> 0:17:52,74
blocks describing a whole area
then loads of code not like comments

350
0:17:52,74 --> 0:17:56,0
at each line of the code Describing
what each line is doing so

351
0:17:56,0 --> 0:18:1,86
I like that style, but I tend to
find that people at least databases

352
0:18:1,88 --> 0:18:6,36
I've seen over the years aren't
commented generally as well as

353
0:18:6,82 --> 0:18:7,74
people's applications.

354
0:18:8,04 --> 0:18:12,0
I see application code commented
better on average than database

355
0:18:12,04 --> 0:18:12,4
code.

356
0:18:12,4 --> 0:18:15,94
Maybe I'm looking at the wrong
projects, but personally, before

357
0:18:15,94 --> 0:18:19,2
knowing the downsides, would think
it's a good idea.

358
0:18:19,2 --> 0:18:21,24
Are there downsides I don't know
about though?

359
0:18:21,98 --> 0:18:23,32
Nikolay: First of all, I agree
with you.

360
0:18:23,32 --> 0:18:26,96
If comment just explains what next
line does, it's like, instead

361
0:18:26,96 --> 0:18:28,94
of 4 ways, it's quite silly.

362
0:18:29,84 --> 0:18:31,9
Like, stupid, how to say it better.

363
0:18:32,54 --> 0:18:34,34
It's a low value comment, right?

364
0:18:34,66 --> 0:18:42,54
But if it explains some knowledge
and decision making, how it

365
0:18:42,54 --> 0:18:47,26
was made, some trade-offs which
were made, This is super valuable.

366
0:18:47,72 --> 0:18:51,82
And right now I think comments
makes even more sense because

367
0:18:51,82 --> 0:18:58,58
sometimes when we engineer something
and involve AI, we have

368
0:18:58,58 --> 0:19:4,52
some roadmap and some intention
and maybe first version is not

369
0:19:4,78 --> 0:19:6,42
final implementation of everything.

370
0:19:7,2 --> 0:19:10,58
So having to-do comment, right?

371
0:19:10,58 --> 0:19:12,32
To-do, fix me, right?

372
0:19:13,54 --> 0:19:15,1
This is a meme comment, right?

373
0:19:15,1 --> 0:19:17,42
But these days I think it's maybe...

374
0:19:17,64 --> 0:19:21,26
I just feel the shift here because
it makes sense to comment

375
0:19:21,82 --> 0:19:26,2
some future intentions more often
because next time we will revisit

376
0:19:26,2 --> 0:19:27,76
this with AI as well.

377
0:19:28,08 --> 0:19:31,9
We probably improve in the same
direction we wanted originally.

378
0:19:32,02 --> 0:19:38,86
So preserving context now using
comments makes a lot of sense.

379
0:19:39,34 --> 0:19:43,42
It's not always worth putting it
as a comment right inside function

380
0:19:43,42 --> 0:19:48,88
body, because we might end up having
huge, like the plan inside

381
0:19:48,9 --> 0:19:52,2
function body, and this doesn't
feel right, and it will consume

382
0:19:52,2 --> 0:19:53,8
a lot of bytes stored.

383
0:19:54,88 --> 0:19:59,34
So maybe some big comments should
go as a separate document adjacent

384
0:19:59,44 --> 0:20:2,86
to the place, like in the same
place where we store function

385
0:20:2,86 --> 0:20:3,9
in Git, for example.

386
0:20:3,9 --> 0:20:7,38
Maybe it's better documented separately,
right?

387
0:20:7,8 --> 0:20:11,38
But when you do something and you
say, okay, we do this, but

388
0:20:11,38 --> 0:20:16,2
we plan to extend it to this and
this, I like these to-do comments

389
0:20:16,2 --> 0:20:22,36
because they are in the same place
and next time AI or you reading

390
0:20:22,36 --> 0:20:26,06
this like you you understand okay
this is what we planned here

391
0:20:27,1 --> 0:20:32,86
and so to do fix me style makes
more sense now because well I

392
0:20:32,86 --> 0:20:36,92
don't know explain it and we plan
to fix it later, why not?

393
0:20:36,96 --> 0:20:38,5
Michael: But that was always true.

394
0:20:39,38 --> 0:20:43,88
If you work in a team, if you work
in a style that's iterative,

395
0:20:44,76 --> 0:20:48,74
like any kind of agile process,
any kind of extreme programming,

396
0:20:48,74 --> 0:20:52,68
that kind of let's do the minimum
version and then let's iterate,

397
0:20:53,0 --> 0:20:54,96
that's always been true hasn't
it?

398
0:20:54,96 --> 0:20:58,62
Nikolay: But it's always also been
true that a lot of dead code

399
0:20:58,62 --> 0:21:2,66
and a lot of such comments which
have very little chance to be

400
0:21:2,66 --> 0:21:3,72
really improved.

401
0:21:4,24 --> 0:21:9,32
So you say, to do, fix me, you
leave this comment, but you never

402
0:21:9,32 --> 0:21:10,74
return because of the capacity.

403
0:21:11,2 --> 0:21:14,64
Now it's much easier to return
and actually fix because we have

404
0:21:14,64 --> 0:21:15,14
AI.

405
0:21:16,84 --> 0:21:17,08
Michael: So

406
0:21:17,08 --> 0:21:18,62
Nikolay: capacity changed, right?

407
0:21:19,2 --> 0:21:23,24
And you think, okay, actually,
let's explain all the things like

408
0:21:23,24 --> 0:21:26,68
in the comment right here and we
will, we know that we will revisit

409
0:21:26,82 --> 0:21:30,82
it if this code survives and if
we don't drop it fully because

410
0:21:30,82 --> 0:21:35,42
of some different like understanding
of like product or something.

411
0:21:36,5 --> 0:21:37,76
We actually will improve.

412
0:21:37,8 --> 0:21:39,56
I start believing into this, right?

413
0:21:39,56 --> 0:21:45,66
Unlike pre-AI era when I knew nobody
will have capacity to work

414
0:21:45,66 --> 0:21:48,42
on this because everyone is busy,
too much everything and so

415
0:21:48,42 --> 0:21:48,92
on.

416
0:21:48,94 --> 0:21:50,24
And this is great actually.

417
0:21:51,02 --> 0:21:52,9
So comments are good.

418
0:21:53,42 --> 0:21:57,48
If you don't leave comment, some
weird decision made, code is

419
0:21:58,34 --> 0:22:1,94
hard to understand, like why it's
so, then we are in trouble.

420
0:22:2,66 --> 0:22:5,66
And inline comment is great because
the AI won't miss it.

421
0:22:5,66 --> 0:22:7,16
It's reading this part.

422
0:22:7,94 --> 0:22:9,36
Comment is here, all clear.

423
0:22:9,4 --> 0:22:12,26
But again, if it's some long document,
it's better to offload

424
0:22:12,26 --> 0:22:13,78
it to some different part.

425
0:22:14,06 --> 0:22:18,5
And We slowly move to the topic
we definitely wanted to discuss

426
0:22:18,52 --> 0:22:20,86
is database object level comments.

427
0:22:21,06 --> 0:22:25,74
Michael: Oh, before we do, can
I do 1 more for query level comments?

428
0:22:26,4 --> 0:22:26,9
Nikolay: Yeah.

429
0:22:27,44 --> 0:22:28,04
I just thought

430
0:22:28,04 --> 0:22:31,4
Michael: it was, I'd forgotten
about this until recently, that

431
0:22:31,4 --> 0:22:34,96
this is how pg_hint_plan puts hints
in.

432
0:22:35,02 --> 0:22:38,5
And I think it's true for other
databases too, different not

433
0:22:38,5 --> 0:22:42,94
just Postgres, how hints yeah it's
fascinating to me that's the

434
0:22:42,94 --> 0:22:46,22
method we've chosen it makes sense
right If we don't have hints

435
0:22:46,22 --> 0:22:49,24
at the database level, how else
could we get them in at the query

436
0:22:49,24 --> 0:22:52,84
level other than Putting them in
a structured format inside a

437
0:22:52,84 --> 0:22:57,5
comment and I didn't know I had
until reading the psql thing

438
0:22:57,5 --> 0:23:0,72
I didn't know for sure why it was
in a multi-line comment other

439
0:23:0,72 --> 0:23:3,68
than for readability But I found
it really interesting that it

440
0:23:3,68 --> 0:23:7,16
uses that syntax probably because
it gets stripped less often.

441
0:23:7,5 --> 0:23:12,16
So yeah, that seems to be another
like big use case for it for

442
0:23:12,16 --> 0:23:13,22
query level comments.

443
0:23:13,28 --> 0:23:15,46
Nikolay: PSQL doesn't strip C-like
comments?

444
0:23:16,56 --> 0:23:18,76
Michael: Doesn't strip the C style.

445
0:23:19,2 --> 0:23:19,98
Nikolay: That's interesting.

446
0:23:20,42 --> 0:23:23,8
Michael: Yeah, so you can use pg_hint_plan
with psql without issues.

447
0:23:23,8 --> 0:23:29,44
Nikolay: Yeah, so back to functions,
my approach is to have good

448
0:23:29,44 --> 0:23:34,34
comments, explaining intentions,
context, plans maybe, but if

449
0:23:34,34 --> 0:23:36,52
it's a huge document it should
be offloaded.

450
0:23:36,82 --> 0:23:41,26
But we also have for each function
we can create a separate metadata

451
0:23:41,32 --> 0:23:44,58
piece, we can say comment on function
name, comment on function

452
0:23:44,58 --> 0:23:45,84
and function name.

453
0:23:46,02 --> 0:23:51,68
Have you seen how many variations
comment on a statement has

454
0:23:51,68 --> 0:23:52,32
in Postgres?

455
0:23:53,44 --> 0:23:57,32
Michael: Yes, so I didn't realize,
and I think I might start

456
0:23:57,32 --> 0:24:1,4
using this more, but for, I didn't
realize you could add comments

457
0:24:1,4 --> 0:24:2,06
to indexes.

458
0:24:3,08 --> 0:24:3,58
Yeah.

459
0:24:3,82 --> 0:24:6,88
That's really cool in the sense
that sometimes you go to someone,

460
0:24:6,88 --> 0:24:10,34
like sometimes someone shows you
they've got these 16 indexes

461
0:24:10,44 --> 0:24:14,54
but they don't know like why certain
ones were added and wouldn't

462
0:24:14,54 --> 0:24:17,64
it be cool if you could just check
the comments on as to what

463
0:24:17,64 --> 0:24:18,3
you know.

464
0:24:18,56 --> 0:24:23,8
Nikolay: On constraints, on sequences,
isn't it like fascinating?

465
0:24:24,02 --> 0:24:26,98
Michael: I knew tables, I knew
columns, I knew like general objects

466
0:24:26,98 --> 0:24:30,34
you could put comments on them
but I didn't know there were so

467
0:24:30,34 --> 0:24:31,04
Nikolay: many options.

468
0:24:31,3 --> 0:24:32,68
That's already hacking.

469
0:24:32,68 --> 0:24:34,52
On access methods.

470
0:24:35,28 --> 0:24:36,84
Too much, too deep.

471
0:24:37,56 --> 0:24:39,24
Yeah so yeah, it's cool.

472
0:24:39,52 --> 0:24:40,74
44 lines there.

473
0:24:41,96 --> 0:24:47,72
And a funny thing, in 2005 or 6,
when we created first social

474
0:24:47,72 --> 0:24:50,82
network, it was Postgres plus PHP.

475
0:24:51,66 --> 0:24:57,1
And I, some time ago, not far ago,
not long ago, I stumbled upon

476
0:24:57,28 --> 0:25:2,96
an email, first review of my code
from someone with experience

477
0:25:3,0 --> 0:25:3,5
actually.

478
0:25:4,2 --> 0:25:7,76
And big criticism was like lack
of comments at database object

479
0:25:7,76 --> 0:25:8,26
level.

480
0:25:8,56 --> 0:25:10,78
Can you imagine 20 plus years ago?

481
0:25:11,04 --> 0:25:15,6
And I remember like actually how
I was like protective and defensive.

482
0:25:15,78 --> 0:25:16,52
Michael: Oh wow.

483
0:25:16,62 --> 0:25:17,12
Nikolay: Yeah.

484
0:25:17,12 --> 0:25:17,62
Yeah.

485
0:25:17,78 --> 0:25:19,14
But it's a good thing.

486
0:25:19,16 --> 0:25:24,2
And my point right now is it always
has been a good thing to

487
0:25:24,78 --> 0:25:31,78
have some approach in your project
and use comments because they

488
0:25:31,78 --> 0:25:37,72
can, sometimes they become like
not valuable, right?

489
0:25:37,72 --> 0:25:42,18
You can have a comment to a table
but also for each column yes

490
0:25:42,26 --> 0:25:46,56
and I remember I tried to enforce
this in multiple teams I had

491
0:25:46,56 --> 0:25:47,58
in different projects.

492
0:25:47,78 --> 0:25:49,12
I tried to enforce this rule.

493
0:25:49,12 --> 0:25:49,76
Let's do it.

494
0:25:49,76 --> 0:25:50,78
Let's do it.

495
0:25:51,28 --> 0:25:56,42
After that review, because I eventually
agreed it's a good thing

496
0:25:56,42 --> 0:25:57,04
to have.

497
0:25:57,04 --> 0:25:59,02
This is just a lot of metadata.

498
0:25:59,58 --> 0:26:4,84
But I remember also seeing, okay,
column ID, this is our ID.

499
0:26:4,86 --> 0:26:7,28
What comment can you put there
on this column?

500
0:26:8,04 --> 0:26:8,86
I don't know.

501
0:26:8,86 --> 0:26:11,56
Sometimes there is no like extra meaning, right?

502
0:26:11,98 --> 0:26:13,22
Super simple column.

503
0:26:13,94 --> 0:26:18,08
But right now I think it's valuable to think about, and this

504
0:26:18,08 --> 0:26:21,34
is engineering level, this is what humans should think, maybe

505
0:26:21,34 --> 0:26:27,04
brainstorming with AI, but what should we really document in

506
0:26:27,04 --> 0:26:28,22
database object comments?

507
0:26:28,94 --> 0:26:30,4
Right now it's so easy.

508
0:26:30,94 --> 0:26:35,88
When we do something, there is no more excuse not to write tests

509
0:26:36,04 --> 0:26:40,32
for CI, because this is what AI does quite well.

510
0:26:40,32 --> 0:26:41,82
You just need to control it.

511
0:26:42,18 --> 0:26:43,34
And not just coverage.

512
0:26:43,94 --> 0:26:48,96
You should go deeper and say, okay, coverage is like a super

513
0:26:48,96 --> 0:26:49,78
simple thing.

514
0:26:49,78 --> 0:26:52,96
We have 80 plus percent, but what does it really mean?

515
0:26:52,96 --> 0:26:57,18
We should cover edge cases, corner cases, really test things,

516
0:26:57,18 --> 0:26:57,68
right?

517
0:26:58,92 --> 0:26:59,94
And the same documentation.

518
0:27:0,58 --> 0:27:2,06
And comments is our documentation.

519
0:27:2,22 --> 0:27:3,88
This is a part of project documentation.

520
0:27:4,54 --> 0:27:7,06
Database table should have some comments, functions should have

521
0:27:7,06 --> 0:27:9,0
comments, columns as well.

522
0:27:9,02 --> 0:27:12,9
But of course, if it's like nothing to say about some simple

523
0:27:12,9 --> 0:27:15,52
column, okay, it can be skipped.

524
0:27:15,66 --> 0:27:19,54
But there should be some rule, and AI should be helping to maintain

525
0:27:20,32 --> 0:27:26,18
good comments so later when you try to add more features or do

526
0:27:26,18 --> 0:27:31,02
refactoring, there is a great context and also when you work

527
0:27:31,02 --> 0:27:35,74
with database, all like all those MCP Servers, APIs, like if

528
0:27:35,74 --> 0:27:39,14
you work with database and it can describe itself, it's great

529
0:27:39,14 --> 0:27:40,3
things to have, right?

530
0:27:41,04 --> 0:27:44,48
Instead of guessing the meaning of column just on column name,

531
0:27:44,48 --> 0:27:45,78
you have a comment.

532
0:27:45,88 --> 0:27:46,62
That's great.

533
0:27:46,78 --> 0:27:50,86
So now I think there's no excuse to avoid this powerful tool.

534
0:27:51,9 --> 0:27:52,12
Yeah.

535
0:27:52,12 --> 0:27:53,22
And have everything documented.

536
0:27:54,56 --> 0:27:56,64
Michael: I think you raised some interesting things.

537
0:27:56,64 --> 0:28:0,62
I do think that being strict about it on ID columns you make

538
0:28:0,62 --> 0:28:2,4
a perfect point, there's no point.

539
0:28:2,86 --> 0:28:8,04
But the place I've seen this super useful is reporting queries

540
0:28:8,3 --> 0:28:14,18
so I think sometimes it can be quite complex to make sure you

541
0:28:14,38 --> 0:28:18,38
are like summing the right columns or the column means what you

542
0:28:18,38 --> 0:28:19,08
think it means.

543
0:28:19,08 --> 0:28:22,44
Does this number, like what, this revenue number, what does it

544
0:28:22,44 --> 0:28:23,86
include, what doesn't it include?

545
0:28:23,94 --> 0:28:27,98
And that's like super relevant when you're trying to report stuff.

546
0:28:28,54 --> 0:28:32,22
And maybe sometimes like just the data person knows that, but

547
0:28:32,22 --> 0:28:36,26
if they can put it in comments on the schema then their future

548
0:28:36,26 --> 0:28:39,88
if once they hire a team those people can know it and nowadays

549
0:28:40,56 --> 0:28:44,76
even LLM is writing reporting queries they've got a better chance

550
0:28:44,76 --> 0:28:48,58
of getting that right rather than pure mistake.

551
0:28:48,76 --> 0:28:49,58
Exactly, exactly.

552
0:28:49,9 --> 0:28:55,08
Nikolay: And the ideal schema, 2 columns, ID, column ID, and

553
0:28:55,08 --> 0:28:56,5
it's of type UUID.

554
0:28:57,1 --> 0:29:2,18
And we should have a comment, never put UUID version 4 here,

555
0:29:2,36 --> 0:29:4,04
always UUID version 7.

556
0:29:4,44 --> 0:29:6,8
And second column, data, JSONB.

557
0:29:8,74 --> 0:29:10,28
It's a joke, just in case.

558
0:29:10,32 --> 0:29:11,14
Michael: Yeah, yeah.

559
0:29:11,32 --> 0:29:12,26
Sounds like Mongo.

560
0:29:13,26 --> 0:29:15,54
Nikolay: And then the comment which you extend all the time,

561
0:29:15,54 --> 0:29:19,7
extending schema of that JSONB, explaining what's inside.

562
0:29:21,5 --> 0:29:25,08
Anyway, 1 of the interesting cases we discussed recently, it

563
0:29:25,08 --> 0:29:28,66
was some project which was originally monolith but they split

564
0:29:28,66 --> 0:29:30,56
the database into several pieces.

565
0:29:31,06 --> 0:29:35,46
So when you do this, you need to abandon some foreign keys because

566
0:29:35,46 --> 0:29:38,68
you cannot have foreign keys between 2 clusters, between 2 primaries,

567
0:29:38,72 --> 0:29:39,22
right?

568
0:29:40,08 --> 0:29:43,68
And I remember we discussed maybe we should maintain some fake

569
0:29:43,94 --> 0:29:46,96
foreign keys, like imaginary foreign keys and define them in

570
0:29:46,96 --> 0:29:47,58
the comments.

571
0:29:49,44 --> 0:29:51,28
It was just an idea, right?

572
0:29:51,28 --> 0:29:55,32
Because who will be enforcing the rule that nobody will ever

573
0:29:55,32 --> 0:29:56,24
write those comments?

574
0:29:56,24 --> 0:29:57,8
I don't know, but it's possible.

575
0:29:57,8 --> 0:30:1,56
So you have a column in 1 cluster saying that it should, values

576
0:30:1,56 --> 0:30:5,42
here should match values of that column in that cluster.

577
0:30:5,9 --> 0:30:9,52
And periodically application or some additional tooling checks

578
0:30:9,52 --> 0:30:10,02
this.

579
0:30:11,42 --> 0:30:15,84
Anyway, tests and comments are super cheap to write these days.

580
0:30:16,06 --> 0:30:20,66
There should be some like rule to enforce in every project to

581
0:30:20,66 --> 0:30:21,64
make them rich.

582
0:30:22,58 --> 0:30:25,24
Michael: Yeah, not only are they cheaper though, I think there's,

583
0:30:25,24 --> 0:30:26,28
they've always been valuable.

584
0:30:26,28 --> 0:30:30,58
I'm a big fan of tests, like I was, I love making changes to

585
0:30:30,58 --> 0:30:33,48
things and knowing that we haven't introduced any regressions

586
0:30:33,52 --> 0:30:36,66
like all the previous bugs that now have tests that mean that

587
0:30:36,66 --> 0:30:39,52
we can't reintroduce them or reintroduce something similar.

588
0:30:39,76 --> 0:30:43,36
So I'm a big fan of tests, I'm a big fan of comments but I think

589
0:30:43,36 --> 0:30:47,12
their value might even be going up in this new world Like I think

590
0:30:47,12 --> 0:30:50,38
it's not just that they just as valuable and cheaper to add,

591
0:30:50,38 --> 0:30:52,28
but I think they might be even more valuable.

592
0:30:52,28 --> 0:30:57,62
Like I would be super scared letting an AI make changes to an

593
0:30:57,62 --> 0:31:0,28
application that doesn't have good test coverage these days.

594
0:31:0,28 --> 0:31:4,2
Like just the value of tests for me is going up even higher because

595
0:31:4,2 --> 0:31:7,64
I have less trust that people have properly reviewed things So

596
0:31:7,64 --> 0:31:10,46
it's did you see where I'm coming from that actually these things

597
0:31:10,46 --> 0:31:12,6
might be even more like comments quality.

598
0:31:13,48 --> 0:31:18,94
I think so and Yeah checking obviously getting it to add these

599
0:31:18,94 --> 0:31:22,78
things is 1 thing, but then checking that they, there is a reasonable

600
0:31:22,78 --> 0:31:27,34
comment that it is documenting what you think that column is

601
0:31:27,34 --> 0:31:28,1
or does.

602
0:31:28,52 --> 0:31:30,04
Yeah, I can see that.

603
0:31:30,06 --> 0:31:32,36
The 1 thing I was going to ask
you though is what do you think

604
0:31:32,36 --> 0:31:33,5
about index comments?

605
0:31:33,5 --> 0:31:34,28
Do you use them?

606
0:31:34,28 --> 0:31:34,78
Honestly,

607
0:31:35,46 --> 0:31:37,58
Nikolay: I don't remember I used
them ever.

608
0:31:37,84 --> 0:31:44,54
But it makes sense to document
why we created this index, right?

609
0:31:44,54 --> 0:31:45,3
Michael: I think so.

610
0:31:45,3 --> 0:31:50,14
I even think maybe because we re-index,
right, sometimes for

611
0:31:50,14 --> 0:31:54,1
maintenance, I was even thinking
like when we added it, who added

612
0:31:54,1 --> 0:31:56,5
it, what for, there might be some
interesting metadata

613
0:31:56,52 --> 0:31:56,64
Nikolay: in there.

614
0:31:56,64 --> 0:31:57,54
That's interesting.

615
0:31:57,92 --> 0:31:58,76
That's interesting.

616
0:31:58,78 --> 0:32:4,24
And not only index, I think, yeah,
I remember cases when I thought,

617
0:32:4,24 --> 0:32:8,94
oh damn, I wish we could establish
proper, like we could figure

618
0:32:8,94 --> 0:32:11,88
out when something was really created
in Postgres.

619
0:32:12,34 --> 0:32:13,64
Some table or index.

620
0:32:14,2 --> 0:32:14,7
Michael: Function.

621
0:32:15,48 --> 0:32:18,22
Nikolay: Yeah, if you, for example,
establish a rule that when

622
0:32:18,22 --> 0:32:21,72
you create something or recreate,
rebuild index, you document

623
0:32:21,74 --> 0:32:23,1
it in a comment, why not?

624
0:32:23,1 --> 0:32:24,64
It's an interesting idea actually.

625
0:32:24,86 --> 0:32:30,4
Probably I should borrow it for
our pg_index_pilot project, which

626
0:32:30,4 --> 0:32:31,6
re-indexes automatically.

627
0:32:32,98 --> 0:32:38,44
Michael: Yeah, so for a lot of
people using ORMs, they'll have

628
0:32:38,44 --> 0:32:39,68
the source control of this, right?

629
0:32:39,68 --> 0:32:42,9
Like they can look up when was
this first created and hopefully

630
0:32:42,9 --> 0:32:44,1
that comes with a commit...

631
0:32:44,14 --> 0:32:45,66
Nikolay: Or looking at logs.

632
0:32:46,16 --> 0:32:48,8
Yeah Like not easy usually.

633
0:32:49,78 --> 0:32:50,66
Michael: Looking at logs.

634
0:32:50,66 --> 0:32:52,62
Nikolay: Logs, if you document
DDL.

635
0:32:53,94 --> 0:32:56,46
Yeah, but how long do people store
those for?

636
0:32:56,64 --> 0:32:59,94
If it's a serious project, usually
we have something like Elastic

637
0:33:0,04 --> 0:33:2,26
and store it for quite long, not
forever.

638
0:33:2,4 --> 0:33:3,98
I agree, it's a lot.

639
0:33:4,0 --> 0:33:4,5
Yeah.

640
0:33:4,94 --> 0:33:7,54
Michael: Indexes could easily have
been created years ago and

641
0:33:7,54 --> 0:33:8,26
people wouldn't like it.

642
0:33:8,26 --> 0:33:10,84
Nikolay: Yeah, and I remember,
well, actually this is also interesting.

643
0:33:10,84 --> 0:33:13,84
I remember in pg_index_pilot,
we of course have a couple of

644
0:33:13,84 --> 0:33:17,66
tables where we store such metadata
and all the history of rebuilding

645
0:33:17,68 --> 0:33:18,44
and so on.

646
0:33:18,44 --> 0:33:18,94
Nice.

647
0:33:19,3 --> 0:33:21,36
Yeah, and this is interesting.

648
0:33:21,6 --> 0:33:25,66
This is like to think pros and
cons of storing some metadata

649
0:33:25,76 --> 0:33:30,1
in the comment versus you have
specific table and store it there.

650
0:33:31,12 --> 0:33:35,14
Pros and cons are not obvious to
me, because of course, comment

651
0:33:35,14 --> 0:33:36,36
is closer, right?

652
0:33:36,46 --> 0:33:38,1
It's easier than to consume.

653
0:33:38,94 --> 0:33:41,4
But you don't have history, for
example.

654
0:33:42,6699 --> 0:33:47,46
Only additional table, you need
to maintain it and so on.

655
0:33:47,6 --> 0:33:51,24
Pros of storing some comments separately,
also permissions.

656
0:33:51,42 --> 0:33:54,68
Sometimes you want to store some
data which you don't want regular

657
0:33:54,68 --> 0:33:56,14
users to observe, for example.

658
0:33:56,14 --> 0:34:0,6
It's very specific nuance for like
your goals, right?

659
0:34:1,46 --> 0:34:2,62
But yeah.

660
0:34:4,14 --> 0:34:7,2
Last thing we wanted to mention
is this blog post from Andrei

661
0:34:7,2 --> 0:34:7,7
Lepikhov.

662
0:34:7,96 --> 0:34:12,38
An interesting idea to use security
labels as metadata storage.

663
0:34:13,58 --> 0:34:17,06
It's quite elegant, I think, and
we discussed before.

664
0:34:17,16 --> 0:34:20,54
So the idea is that we need some
metadata storage, but instead

665
0:34:20,54 --> 0:34:24,52
of creating a table and write it
there, in that case, it was,

666
0:34:24,52 --> 0:34:28,94
I think, it was pgEdge, so it was
related to probably a multi-master

667
0:34:29,16 --> 0:34:32,7
solution and logical replication,
bidirectional logical replication,

668
0:34:32,72 --> 0:34:33,22
something.

669
0:34:33,9 --> 0:34:38,8
So the idea was, let's use these
security labels coming from

670
0:34:38,8 --> 0:34:43,82
integration with SE Linux security
stuff and benefit from the

671
0:34:43,82 --> 0:34:48,84
fact that You can put anything
there and for different users,

672
0:34:49,74 --> 0:34:53,44
unlike for example comment, which
is single comment for database

673
0:34:53,46 --> 0:34:58,7
object, there you can have multiple
metadata pieces belonging

674
0:34:58,78 --> 0:35:2,42
to specifically like to which user,
so it's one-to-many relationship,

675
0:35:2,52 --> 0:35:3,46
so it's interesting.

676
0:35:4,3 --> 0:35:7,68
And putting there some custom data,
why not actually?

677
0:35:9,48 --> 0:35:11,96
Michael: Yeah, so it's called security
labels, but I guess you

678
0:35:11,96 --> 0:35:14,16
could just think of them as labels?

679
0:35:15,12 --> 0:35:17,52
Nikolay: Yeah, So it's interesting.

680
0:35:17,58 --> 0:35:21,18
I never thought about this and
maybe there are different use

681
0:35:21,18 --> 0:35:23,74
cases where you can benefit from
this.

682
0:35:24,1 --> 0:35:27,7
If you need specific comments for
specific users for this particular

683
0:35:27,7 --> 0:35:28,64
database object.

684
0:35:28,7 --> 0:35:29,98
Michael: Yeah, many options.

685
0:35:30,94 --> 0:35:35,28
Nikolay: Yeah, anyway, comments
should be used more in the AI

686
0:35:35,28 --> 0:35:35,78
era.

687
0:35:36,14 --> 0:35:39,16
Like table level, index level,
I like it a lot.

688
0:35:39,18 --> 0:35:41,26
Never use but I'm going to think.

689
0:35:42,04 --> 0:35:45,12
Michael: Yeah, and even if you're
somewhere that isn't using

690
0:35:45,46 --> 0:35:49,12
AI stuff all the time, I don't
know how many of them there are

691
0:35:49,12 --> 0:35:49,86
these days.

692
0:35:50,34 --> 0:35:54,06
But just I think this is useful
anyway even for teams that are

693
0:35:54,06 --> 0:35:58,3
collaborating like this comments
are good for communication generally.

694
0:35:59,38 --> 0:35:59,88
Good.

695
0:36:1,3 --> 0:36:2,96
Alright, nice one Nikolay.

696
0:36:2,96 --> 0:36:5,36
Thanks so much for this and catch
you next time.

697
0:36:5,46 --> 0:36:7,2
Nikolay: Have a great week, bye
bye.