1
00:00:00,000 --> 00:00:03,467
Hello, and welcome to Postgres FM, episode number one

2
00:00:03,527 --> 00:00:04,337
Pilot episode.

3
00:00:05,144 --> 00:00:06,250
pilot episode yeah.

4
00:00:06,295 --> 00:00:09,715
I'm Michael I'm from pgMustard and I'm joined today by

5
00:00:10,394 --> 00:00:17,234
Nikolay, Samokhvalov, don't try my last name, unless
you have Russian or Ukrainian roots or Belarusian roots.

6
00:00:17,674 --> 00:00:20,784
So yes, I'm Nikolay from Postgres AI.

7
00:00:21,032 --> 00:00:22,879
Wonderful, looking forward to these.

8
00:00:22,898 --> 00:00:31,643
Let's discuss why we do it because I felt so long that we should
have some podcasts pure podcasts without any screencasting or slides

9
00:00:31,643 --> 00:00:35,513
and so on, because it's also a good format to have some discussion.

10
00:00:35,573 --> 00:00:43,123
And I feel like meetups died because of course this started
to die before COVID, but COVID finished them completely.

11
00:00:43,573 --> 00:00:53,368
So we, it's very hard to, to meet at some place and have some pizza,
pizza and beer, and I mean, people still do it, but only like 10 person's

12
00:00:53,398 --> 00:00:57,238
come so like, it's not, it's not working well, so we need more formats.

13
00:00:57,598 --> 00:01:03,148
I know many people hate on purely online
formats, but I actually actually love them.

14
00:01:03,508 --> 00:01:06,628
And I think we should experiment with more and more styles for formats.

15
00:01:06,628 --> 00:01:09,008
And the Postgres community needs some podcasts.

16
00:01:09,008 --> 00:01:15,338
That's why we discussed with Michael and we should start Postgres FM probably.

17
00:01:15,338 --> 00:01:15,608
Right.

18
00:01:16,418 --> 00:01:18,788
Maybe it will die also, but who knows?

19
00:01:18,788 --> 00:01:18,998
Right.

20
00:01:19,028 --> 00:01:20,378
But I, I, I hope not.

21
00:01:21,763 --> 00:01:22,843
Why, why do you do it?

22
00:01:22,913 --> 00:01:23,333
Yeah.

23
00:01:23,483 --> 00:01:25,583
Well, I, I love podcasts in general.

24
00:01:25,583 --> 00:01:26,693
I love them for learning.

25
00:01:26,723 --> 00:01:27,953
I find them really helpful.

26
00:01:27,953 --> 00:01:32,583
I can, I can listen to them at times where I
can't watch a video or I can't attend an event.

27
00:01:32,633 --> 00:01:37,903
I find them really helpful in terms of learning,
but also getting to know people in the community.

28
00:01:38,878 --> 00:01:44,218
See, different guests getting to hear interesting conversations
between people that I probably wouldn't be a part of generally.

29
00:01:44,578 --> 00:01:50,998
So I love them as kind of a, an insight into something that
you're not necessarily familiar with already, or a way of

30
00:01:50,998 --> 00:01:53,218
keeping up with people that you don't talk to all the time.

31
00:01:53,218 --> 00:01:55,048
Like that's quite a nice format as well.

32
00:01:55,057 --> 00:01:57,468
But yeah, basically thought it would be interesting.

33
00:01:57,468 --> 00:01:58,848
I thought I would like to listen to this.

34
00:01:58,848 --> 00:01:59,028
Right.

35
00:01:59,058 --> 00:02:08,567
I would like to listen to a couple of people talking about Postgres each
week or discussing something that is super interesting or controversial

36
00:02:08,567 --> 00:02:12,797
or not yet decided or a common issue that people see all the time.

37
00:02:12,797 --> 00:02:17,177
Something that I've just hit or might be able
to avoid hitting in future, that kind of thing.

38
00:02:17,501 --> 00:02:18,581
Right, I'm the same.

39
00:02:18,601 --> 00:02:21,221
I'm just trapped sometimes trying to fill gaps.

40
00:02:21,221 --> 00:02:27,731
So when I walk my dog or I'm on an airplane, just, I have some
time and I want to not just some entertainment, but something

41
00:02:27,761 --> 00:02:31,391
useful and podcasts is great for this kind of gaps to fill.

42
00:02:31,391 --> 00:02:31,601
Right.

43
00:02:31,601 --> 00:02:32,681
So, yeah.

44
00:02:32,711 --> 00:02:32,921
Okay.

45
00:02:32,921 --> 00:02:34,241
Let's, let's start from.

46
00:02:34,901 --> 00:02:44,561
Topics we have, I think we, like in general, we will try to talk more
about performance, but maybe not only right, but definitely about Postgres

47
00:02:44,724 --> 00:02:46,254
Yeah, I think always about Postgres.

48
00:02:46,284 --> 00:02:53,064
I think based on what we focus on, we probably will end up
bringing performance topics more than most would, but equally

49
00:02:53,064 --> 00:02:58,992
I think we're open to suggestions from other people, as
well as to what, what you'd be interested in hearing about.

50
00:02:58,992 --> 00:03:01,066
So yeah, we're very open on the topics front.

51
00:03:01,354 --> 00:03:06,878
My opinion about performance It was surprise to me,
but I think not everyone is interested in performance.

52
00:03:06,878 --> 00:03:10,568
Not every engineer who writes SQL is interested in performance.

53
00:03:10,568 --> 00:03:12,638
This is discovery I've made.

54
00:03:12,638 --> 00:03:20,028
Like it's like, it feels like the most interesting part,
like, scale, scalability, performance, these kinds of topic

55
00:03:20,518 --> 00:03:26,458
but I found many people, not just interested, they just need
some query to work and return proper results and that's it.

56
00:03:27,298 --> 00:03:36,802
So like, I wish everyone to be interested in performance,
but, anyway let's, let's start with the first topic.

57
00:03:36,852 --> 00:03:38,502
Well, I'm interested in that one, actually.

58
00:03:38,554 --> 00:03:42,364
Do you find there's a pattern as to who cares and who doesn't care so much?

59
00:03:42,394 --> 00:03:50,195
Or is it that they, they only care if it's below a certain
threshold or is it that they care about one thing, way more?

60
00:03:50,305 --> 00:03:59,455
I think, yeah, I think we're movement towards one of the topics we
wanted to discuss and the word, the word threshold is like as good here.

61
00:03:59,875 --> 00:04:09,086
So what I observed in organizations from like, small startups that
grow very fast, like several times all numbers increased several

62
00:04:09,086 --> 00:04:17,926
times per year, or very large organizations with thousands of
people and hundreds of engineers, I observe a quite good pattern.

63
00:04:17,986 --> 00:04:27,306
Like not good, like obvious pattern where, when a business dictates
a feature delivery, but to be very fast, like a lot of competitors

64
00:04:27,336 --> 00:04:31,896
around, so requirements are very strict, so we need to move faster.

65
00:04:31,926 --> 00:04:37,624
So developers mostly interested in having
features delivered like daily, for example.

66
00:04:37,924 --> 00:04:38,164
Right.

67
00:04:38,164 --> 00:04:42,094
So very, very fast move, very fast and under risk pressure.

68
00:04:42,124 --> 00:04:48,124
They don't have time to have the best performance ever.

69
00:04:48,154 --> 00:04:48,424
Right.

70
00:04:48,454 --> 00:04:50,635
They don't have this Like with just don't have time.

71
00:04:50,665 --> 00:04:54,475
So then they start to move like, okay, it works.

72
00:04:54,505 --> 00:04:54,955
Let's go.

73
00:04:55,405 --> 00:04:58,595
And, but the problem is who defines this threshold?

74
00:04:59,185 --> 00:05:01,705
Like where is the minimum and who checks it?

75
00:05:01,765 --> 00:05:02,855
How, how to check it.

76
00:05:02,855 --> 00:05:04,255
This it's usually a problem.

77
00:05:04,255 --> 00:05:11,927
So sometimes you observe very good performance when you develop the
feature, but when you deploy it, it's not good or you deploy it also good.

78
00:05:11,927 --> 00:05:21,692
But one year later, Normally detach this code, it left
unoptimized you have more data and in the query degrades

79
00:05:22,112 --> 00:05:25,262
.
So my thereshold, I have my threshold.

80
00:05:25,262 --> 00:05:27,002
I wrote an article about it.

81
00:05:27,452 --> 00:05:28,672
So it's.

82
00:05:29,582 --> 00:05:35,592
At every webpage should be faster than like 200, 300 milliseconds.

83
00:05:36,102 --> 00:05:38,112
One second as an absolute maximum.

84
00:05:39,222 --> 00:05:48,637
And since we consider page or API request, of course, and since
every page or API request may consist of multiple SQL queries.

85
00:05:49,297 --> 00:05:50,227
It may have zero.

86
00:05:50,337 --> 00:05:53,157
But sometimes we have dozens of queries.

87
00:05:53,157 --> 00:05:53,727
It's not good.

88
00:05:53,727 --> 00:05:55,687
Sometimes we have like a loop with queries.

89
00:05:55,707 --> 00:06:04,809
Of course it's not good, but in general it means like, like if we have the
requirement for a webpage to be not, not longer than one second, it needs to

90
00:06:04,809 --> 00:06:10,219
that general requirement for SQL to be not longer than dozens of milliseconds.

91
00:06:11,119 --> 00:06:16,509
And also my article also describes where this
number comes from, where does it come from?

92
00:06:16,869 --> 00:06:18,489
A human perception.

93
00:06:19,239 --> 00:06:25,209
It's like 200 milliseconds, a reaction of any human plus minus 15, maybe.

94
00:06:25,779 --> 00:06:34,919
So if you press some button, you expect the reaction
below 200 milliseconds better 100 milliseconds.

95
00:06:36,070 --> 00:06:40,540
Yeah, I think I've heard, is it below a
hundred milliseconds it feels instantaneous.

96
00:06:40,592 --> 00:06:44,972
Anything above that we can perceive, we
perceive some delay, even if it's not instant.

97
00:06:45,002 --> 00:06:45,272
Yeah.

98
00:06:45,422 --> 00:06:48,512
So I know exactly where you're coming from on, that makes a lot of sense.

99
00:06:48,872 --> 00:06:56,934
So I guess the question is do you see a lot of companies that
generally have way worse than that performance and still don't care.

100
00:06:56,944 --> 00:06:57,634
Of course.

101
00:06:58,134 --> 00:07:00,142
Well, yes, yes, yes.

102
00:07:00,652 --> 00:07:01,012
A lot.

103
00:07:03,037 --> 00:07:07,675
If users don't complain, oftentimes we just don't care.

104
00:07:07,705 --> 00:07:14,065
Like, we have some functionality it's working, but we have
so many things to create, to compete with others, right.

105
00:07:14,095 --> 00:07:15,655
To, to expand, to grow.

106
00:07:16,345 --> 00:07:27,134
So, if users don't complain, sometimes you see some pagers
or API requests are above one second in duration and nobody.

107
00:07:28,099 --> 00:07:30,439
You can see it and from logs actually, you can check.

108
00:07:30,769 --> 00:07:35,039
If you have, for example, log_min_duration_statement
longer than one second, for example.

109
00:07:35,549 --> 00:07:38,424
And here we can discuss percentiles, right?

110
00:07:38,424 --> 00:07:45,614
So not only every query should be below one second or
below a hundred milliseconds, we say 99% should be below.

111
00:07:45,644 --> 00:07:45,914
Right?

112
00:07:45,964 --> 00:07:56,943
So any big projects should talk in these terms, but sometimes we have
a lot of slow queries and nobody cares until big feedback loop works.

113
00:07:56,973 --> 00:08:00,183
Big feedback loop is when a lot of users started complaining.

114
00:08:00,933 --> 00:08:02,313
And management understands.

115
00:08:02,313 --> 00:08:03,153
It's a big problem.

116
00:08:03,633 --> 00:08:04,983
Or churn right?

117
00:08:04,983 --> 00:08:10,343
Like  you hear some users complain, but you realize that
it's the ones that are churning the other one's complaining.

118
00:08:10,373 --> 00:08:11,183
They're the ones that are,

119
00:08:11,241 --> 00:08:11,781
well, yeah,

120
00:08:11,813 --> 00:08:17,523
know, it's real dollars that are being affected or, you know,
e-commerce maybe your conversion rate on the pages that are slower

121
00:08:17,523 --> 00:08:23,601
E-commerce knows how to measure every, every
second of downtime or at least minute of downtime.

122
00:08:23,601 --> 00:08:24,171
So yes.

123
00:08:25,101 --> 00:08:30,681
And degradation is tricky in terms of how to
measure it in dollars, but also maybe it's possible.

124
00:08:30,681 --> 00:08:31,401
I think it's possible.

125
00:08:32,511 --> 00:08:40,731
Again, if you check slow, slow query log, usually a lot of things are, I
mean, in the logical logic company or a large project, a lot of bad things.

126
00:08:41,361 --> 00:08:43,791
And if you have a luxury.

127
00:08:44,976 --> 00:08:51,216
To get a few weeks for optimization that is always
in large project to fill them with optimization.

128
00:08:51,486 --> 00:08:51,666
Right.

129
00:08:52,086 --> 00:08:57,363
But yeah, it's, it's a decision from management
let's optimize and be prepared for further growth.

130
00:08:57,815 --> 00:08:58,085
Yeah.

131
00:08:58,805 --> 00:09:03,875
So I've seen some exceptions that I guess, I think you'll
probably I've read your post and I think it's great.

132
00:09:03,965 --> 00:09:08,945
I think you've probably accounting for them cause they
probably fall a little bit into the analytical workloads,

133
00:09:08,975 --> 00:09:13,280
but some applications almost do analytics as OLTP.

134
00:09:13,410 --> 00:09:15,210
So they might be an analytics product.

135
00:09:15,270 --> 00:09:22,140
And if they let you set a few filters and it takes a few seconds to
load, sometimes that's acceptable from a user, but that seems like

136
00:09:22,140 --> 00:09:30,270
the, they seem like the exceptions where the user understands that
it's doing something complicated or data heavy behind the scenes.

137
00:09:30,480 --> 00:09:35,460
And if they, if they're waiting for that on a one-off
basis, and it's only a few seconds, they seem okay with it.

138
00:09:35,700 --> 00:09:38,310
But yeah, I think that's probably only at the exceptions.

139
00:09:38,774 --> 00:09:40,094
Some complex search.

140
00:09:40,364 --> 00:09:42,254
It may, it might, might be okay.

141
00:09:42,254 --> 00:09:49,958
But usually the question is what will happen in a few years when we
have, when we will have a lot of data, much more data than today.

142
00:09:50,048 --> 00:09:54,188
If today we have 10 terabytes in a few years, we might have 50 terabytes.

143
00:09:54,188 --> 00:09:54,878
So what will happen?

144
00:09:55,418 --> 00:10:02,818
So if it takes three seconds today, it
may be already above 10 seconds in future.

145
00:10:03,328 --> 00:10:12,017
And of course, well, people usually are okay to wait a second or
two during different search from my opinion, but still not good.

146
00:10:12,647 --> 00:10:13,607
We should optimize.

147
00:10:14,763 --> 00:10:19,141
And the examples I've seen with, you mentioned
people scaling and having problems there.

148
00:10:19,411 --> 00:10:25,871
Sometimes these startups, they bring on a customer who has three
four times more data than all of their other customers combined.

149
00:10:25,871 --> 00:10:29,201
You know, when they're in a, an early phase, that's not that unusual.

150
00:10:29,621 --> 00:10:36,281
And if they haven't tested sometimes well performance
drops off a cliff or something goes badly wrong.

151
00:10:36,581 --> 00:10:37,931
We've seen that a few times.

152
00:10:37,961 --> 00:10:39,241
So it's yeah.

153
00:10:39,281 --> 00:10:48,851
Super interesting to think of like planning ahead, but most companies
don't seem to because, as you say that it, it has to be a focus on

154
00:10:49,451 --> 00:10:56,619
feature delivery and, you know, investors need updates and customers
want certain things delivered in certain timeframes, that kind of thing.

155
00:10:57,068 --> 00:10:57,828
Yeah, well

156
00:10:57,978 --> 00:10:58,659
So what can we do?

157
00:10:58,659 --> 00:11:00,069
What can we do to help people?

158
00:11:00,458 --> 00:11:06,864
Well, first, first thing to define some, some thresholds,
as you said, maybe it can be defined the form of life.

159
00:11:06,894 --> 00:11:12,144
If some salaries are involved and they have
some methodologies to control uptime and so on.

160
00:11:12,414 --> 00:11:15,594
So similar approach can be applied here.

161
00:11:15,624 --> 00:11:22,565
Like we can say we have good quality if, for example,
99% of all, queries are below a hundred milliseconds.

162
00:11:22,865 --> 00:11:28,175
We define it a form of SLO service level
objective, and we start monitoring it.

163
00:11:28,175 --> 00:11:30,095
We start having alerts.

164
00:11:30,155 --> 00:11:32,495
If it goes down, we.

165
00:11:33,515 --> 00:11:40,935
Also second thing we perform from time to time, like at least
once per quarter, we perform analysis of current situation.

166
00:11:40,995 --> 00:11:42,875
And also we try to predict future.

167
00:11:42,915 --> 00:11:44,595
So like some capacity planning.

168
00:11:45,195 --> 00:11:53,898
Are we okay with numbers growing with what we observe or we
predict our thresholds to be broken already in next quarter.

169
00:11:54,528 --> 00:11:58,578
So this is like usual approach to growing project.

170
00:11:58,683 --> 00:12:07,963
And then we should of course go down and perform query analysis and
optimization and analyzing whole workload, but it's a called different topic.

171
00:12:07,963 --> 00:12:10,153
Probably we should discuss it separately,

172
00:12:10,423 --> 00:12:16,913
Yeah, well, and the slow query log is a great way of
getting started right the log_min_duration_statement.

173
00:12:16,933 --> 00:12:21,133
If people don't have that turned on, that feels
really sensible for pretty much everybody.

174
00:12:21,190 --> 00:12:23,440
I've seen startups that don't do any monitoring yet.

175
00:12:23,470 --> 00:12:25,090
They don't do any performance monitoring.

176
00:12:25,090 --> 00:12:29,350
So the idea of even getting the 99th percentile might be a stretch.

177
00:12:29,380 --> 00:12:33,330
But if you, if you just start logging the worst queries.

178
00:12:33,788 --> 00:12:34,898
Yeah, I saw this.

179
00:12:35,048 --> 00:12:37,688
So, so what I described is for a larger organization.

180
00:12:37,688 --> 00:12:42,398
So like, like it's, it's already a sound
like some bureaucracy involved, right?

181
00:12:42,398 --> 00:12:50,228
So some processes, but it works in the larger organization that we
need to do it a little bit more complex process to be established.

182
00:12:50,498 --> 00:12:54,668
But then if it's a small startup, a few engineers only I saw this.

183
00:12:55,008 --> 00:13:02,873
CTO with couple of engineers, very small startup, by the way,
it was very successful and sold recently to very large company.

184
00:13:02,923 --> 00:13:12,533
But in the beginning, like three or four years ago, I saw
them a CTO having every query that went to the Postgres

185
00:13:12,533 --> 00:13:20,563
log because of log_min_duration_statement, 500 milliseconds
or so it was sent to Slack and immediate reaction.

186
00:13:20,773 --> 00:13:25,693
So they try to achieve zero, zero events for this in the beginning.

187
00:13:25,693 --> 00:13:26,203
It's fine.

188
00:13:26,233 --> 00:13:33,033
But this process doesn't scale well, at some point you will
be overwhelmed of course, but in the beginning it's good,

189
00:13:34,103 --> 00:13:37,093
it's like you'll react to every occurrence of slow query.

190
00:13:38,053 --> 00:13:38,576
That's great.

191
00:13:38,588 --> 00:13:40,268
Anything else you wanted to cover on this one?

192
00:13:40,851 --> 00:13:42,411
Well, not at this point.

193
00:13:42,411 --> 00:13:44,851
I think that it's enough about slow queries.

194
00:13:45,251 --> 00:13:50,111
Like maybe we can talk about slow transactions because
it's quite different topic related, but different.

195
00:13:51,128 --> 00:13:51,818
Yeah, go for it.

196
00:13:52,406 --> 00:14:03,017
So we, we hear like, maybe like you discussed analytical
workloads, but it looks like we mostly discuss OLTP worloads for.

197
00:14:03,437 --> 00:14:06,456
It means like web and mobile apps.

198
00:14:07,146 --> 00:14:17,456
So if we talk about transactions, we should understand like that there is
a query, transaction and sessions three levels and to execute the query.

199
00:14:17,456 --> 00:14:22,556
You need to have a transaction, even if you don't define
it, it will be defined automatically a single query.

200
00:14:23,156 --> 00:14:25,166
Like it's like implicit transaction.

201
00:14:25,166 --> 00:14:32,429
Anyway I hate Ruby developers when they say I will
run this database migration without transaction.

202
00:14:32,549 --> 00:14:33,509
It's not possible.

203
00:14:33,779 --> 00:14:34,049
Right.

204
00:14:34,619 --> 00:14:37,549
And this disabled DDL transaction it's wrong.

205
00:14:38,739 --> 00:14:45,399
You cannot disable transactions in Postgres,
but, but still this like a weird term it's used.

206
00:14:45,759 --> 00:14:49,989
So you, you need to open transaction, but you
cannot open transaction if you don't open a session.

207
00:14:50,469 --> 00:14:50,619
Right.

208
00:14:50,619 --> 00:14:58,601
So like it's like one inside another and the problem with
transactions long transactions is that they there are two problems.

209
00:14:58,631 --> 00:15:09,011
First, if you acquired some exclusive lock and keep it, it's always
kept until the very end of transaction, either commit or rollback.

210
00:15:09,191 --> 00:15:12,641
So you need to stop otherwise you still keeping it.

211
00:15:12,677 --> 00:15:14,480
So at that means, so you can block out.

212
00:15:14,523 --> 00:15:16,275
And the second problem is auto vacuum.

213
00:15:16,545 --> 00:15:19,965
If you keep transaction open, even if it's read only transaction.

214
00:15:20,055 --> 00:15:26,655
And sometimes even if it's on a replica on a standby
server, if hostel the, that is on a, it means that

215
00:15:26,845 --> 00:15:29,985
autovacuum cannot delete freshly dead tuples right.

216
00:15:29,985 --> 00:15:36,385
So we, block some autovacuum work at least
partially, but the first problem is the most.

217
00:15:37,540 --> 00:15:44,260
Like it can be seen immediately if you keep long transaction
and you can, you can have a storm of locking issues.

218
00:15:44,710 --> 00:15:51,820
So this, this means like ideally transactions
should be also below one second, right?

219
00:15:51,870 --> 00:15:52,890
Oh, interesting.

220
00:15:53,009 --> 00:16:00,399
Right because otherwise, imagine you acquired the lock in the very
beginning of a transaction and someone also trying to acquire the lock.

221
00:16:00,419 --> 00:16:03,899
So you updated the row and someone else tries to all day to the same row.

222
00:16:04,379 --> 00:16:06,419
And this happens in the beginning of your transaction.

223
00:16:06,449 --> 00:16:12,633
If you keep it longer than one second, you may
block this session, for longer then one second.

224
00:16:12,633 --> 00:16:15,903
So our previous topic will be broken as well.

225
00:16:17,013 --> 00:16:17,283
Right.

226
00:16:17,283 --> 00:16:18,903
So this is a similar thing.

227
00:16:20,073 --> 00:16:29,373
And that's why I also say when you're split, worked into batches, try
to find some batch size that will allow you not to exceed one second.

228
00:16:29,433 --> 00:16:29,763
Roughly.

229
00:16:30,693 --> 00:16:33,423
Exactly because of this, right?

230
00:16:34,039 --> 00:16:34,489
Awesome.

231
00:16:34,609 --> 00:16:42,951
So what going back to the folks that don't have much of this setup
at all, and what would you recommend them logging or monitoring?

232
00:16:43,027 --> 00:16:47,239
Yeah, well every morning throwing should
have monitoring for a long transactions.

233
00:16:47,259 --> 00:16:49,931
And I see most of monitoring fails to do.

234
00:16:50,381 --> 00:16:52,601
I just fails like they don't have it.

235
00:16:52,661 --> 00:16:58,121
They don't report what, like we cannot, we open
monitoring and we can not answer the simple question.

236
00:16:58,331 --> 00:16:59,831
What is the longest transaction?

237
00:16:59,831 --> 00:17:01,241
What's the duration right now?

238
00:17:01,721 --> 00:17:02,651
Is it five minutes?

239
00:17:02,651 --> 00:17:03,461
Is it one hour?

240
00:17:04,481 --> 00:17:05,651
We are interested in this.

241
00:17:05,891 --> 00:17:10,331
We are even just, we don't discuss autovacuum and the xmin horizon here.

242
00:17:10,331 --> 00:17:11,351
Just this simple question.

243
00:17:12,461 --> 00:17:15,011
And I think this is first step.

244
00:17:15,041 --> 00:17:16,721
You should have it in any monitoring.

245
00:17:16,811 --> 00:17:19,031
If it is, if it's missing, you should add it.

246
00:17:19,601 --> 00:17:24,611
And then you should have alerts again, like soft or hard alerts.

247
00:17:24,611 --> 00:17:31,591
Like if some transaction is running a longer than 10 minutes
already not good like, like it's always this bad situation.

248
00:17:31,591 --> 00:17:34,501
Of course, sometimes you should exclude some transactions.

249
00:17:34,621 --> 00:17:37,345
For example, vacuum can run longer.

250
00:17:37,460 --> 00:17:45,112
But regular transactions should be under control in OLTP, we
cannot allow somebody just opened transaction and keep it forever.

251
00:17:46,732 --> 00:17:53,190
Yeah, so, I know you're focusing on the first part of this, but the
second part does feel worth mentioning in terms of avoiding transaction

252
00:17:53,190 --> 00:18:01,297
ID wraparound it, like, I know, I know some small companies shouldn't
get anywhere close to it at least for a long, long time, but we've

253
00:18:01,297 --> 00:18:04,837
seen big companies over the last few years, get tripped up by that.

254
00:18:04,911 --> 00:18:08,781
And it feels like maybe they might not have
been monitoring for this kind of thing.

255
00:18:08,811 --> 00:18:10,011
Even at these large company.

256
00:18:10,716 --> 00:18:11,716
With massive scale.

257
00:18:11,849 --> 00:18:12,959
Right, right, right.

258
00:18:12,989 --> 00:18:18,078
One of consequences of keeping transaction
very long is you block autovacuum as well.

259
00:18:18,078 --> 00:18:25,473
And that can not delete freshly dead tuples if you do it,
sometimes people open transaction and keep it for several days.

260
00:18:26,088 --> 00:18:34,041
If you're modifying queries or transactions are coming
at very high rate, it's it can be a problem as well.

261
00:18:34,041 --> 00:18:42,521
But I observe usually already every monitoring, has it like I'm less
concerned here because I had just see, for example, Datadog has.

262
00:18:43,341 --> 00:18:51,891
Others also implemented exactly because of this very painful
experience from Mailchimp and Sentry before like several years ago.

263
00:18:51,941 --> 00:18:54,281
It's a good that those guys, by the way, blogged about it.

264
00:18:54,381 --> 00:18:55,891
It's that's visibility.

265
00:18:56,411 --> 00:18:56,651
Yeah.

266
00:18:56,681 --> 00:18:58,391
Blogging is very, very important.

267
00:18:59,201 --> 00:19:06,661
So, I guess, even in my mind, I don't have very good model
for monitoring ideal model because we should distinguish

268
00:19:06,811 --> 00:19:10,471
various types of transactions modifying, which acquired lock.

269
00:19:11,041 --> 00:19:17,143
And also they have real transaction ID
or don't only do transactions on standby.

270
00:19:18,103 --> 00:19:19,783
And like different things, right?

271
00:19:19,783 --> 00:19:25,243
So, so maybe we should have couple of
monitorings aimed, to different purposes.

272
00:19:25,243 --> 00:19:35,413
One is for locking issues and other is for autovacuum issues . By the way,
we can discuss different things, not transaction duration, but xmin horizon.

273
00:19:35,833 --> 00:19:37,753
And only then we care where.

274
00:19:39,328 --> 00:19:43,558
From our transaction on our primary or
from replication slot or somewhere else.

275
00:19:43,648 --> 00:19:49,853
So, so maybe we should have different things,
two monitoring charts, metrics, right?

276
00:19:51,159 --> 00:19:59,559
The other thing that interplays with this that I think I've heard you
speaking about before are timeouts, so to protect against some of these

277
00:20:00,938 --> 00:20:01,168
Yeah.

278
00:20:01,168 --> 00:20:04,638
I recently had one company, quite a really big startup.

279
00:20:05,268 --> 00:20:08,858
And they experienced a lot of issues because of lack of timeouts involved.

280
00:20:08,888 --> 00:20:11,658
And once again, we saw the problem that Postgres.

281
00:20:11,703 --> 00:20:13,045
It doesn't have very important handle.

282
00:20:13,045 --> 00:20:14,785
Actually somebody should develop it.

283
00:20:15,205 --> 00:20:18,604
Maybe I should develop it but I'm not a hacker anymore.

284
00:20:18,610 --> 00:20:23,169
First, what do we have, if we talk about like
session transaction, and query statement.

285
00:20:23,199 --> 00:20:26,739
So three levels, we can limit statements using statement timeout

286
00:20:26,792 --> 00:20:28,196
but we cannot limit transaction.

287
00:20:28,766 --> 00:20:30,956
There is no such way in Postgres at all.

288
00:20:32,166 --> 00:20:33,256
That's it.

289
00:20:33,446 --> 00:20:36,922
Can we can, we do session timeouts.

290
00:20:36,952 --> 00:20:37,222
That's

291
00:20:37,301 --> 00:20:42,601
I think it's, it's usually, if it comes
through pgbouncer, we can limit idle sessions.

292
00:20:42,601 --> 00:20:44,921
So we can say drop connection.

293
00:20:44,921 --> 00:20:49,536
If we like, I don't remember the concrete
names out of top of my head, but right.

294
00:20:49,536 --> 00:20:53,526
But it's, I think this is possible, but session is less a problem.

295
00:20:53,586 --> 00:20:55,086
This is not a big problem.

296
00:20:55,596 --> 00:21:00,576
I would, I would prefer to have a way to
limit transaction duration, but there is no

297
00:21:00,737 --> 00:21:04,337
But if we, if we kill the session that kills the transaction.

298
00:21:04,337 --> 00:21:04,547
Right.

299
00:21:04,577 --> 00:21:06,977
But I guess if it's only, only have it's idle.

300
00:21:07,067 --> 00:21:07,367
Yeah.

301
00:21:07,457 --> 00:21:08,147
Interesting.

302
00:21:09,006 --> 00:21:18,326
But like if transactions are small, session for example can be
very long and then you establish connection yesterday and continue

303
00:21:18,326 --> 00:21:21,866
working politely like very small, transaction, very brief.

304
00:21:22,706 --> 00:21:23,836
Why should we kill you?

305
00:21:24,076 --> 00:21:28,246
We can limit statement and we can limit breaks
between statements and side transaction.

306
00:21:28,246 --> 00:21:31,606
It's called idle_in_transaction_session_timeout, a very long name.

307
00:21:32,686 --> 00:21:41,506
So we can limit statement, limit breaks pauses between statements
and everyone should do it actually, I think any OLTP should start

308
00:21:41,506 --> 00:21:49,996
with, like, we had the discussion in Twitter sometime ago and I'm
big fan of global default, very limiting for all OLTP project to

309
00:21:50,206 --> 00:22:00,805
those who need to extend they can do it in session or for user,
but in OLTP, I prefer, like to see statement timeout, 30 seconds

310
00:22:00,835 --> 00:22:05,143
idle_in_transaction_session_timeout also like 30 seconds, sometimes even 15.

311
00:22:05,159 --> 00:22:14,949
Imagine that transaction, which consists of like a chain of very small,
very brief statements with small pauses between you don't break any timeout

312
00:22:14,949 --> 00:22:19,629
settings in this case and your transaction can last hours and I saw it.

313
00:22:19,762 --> 00:22:20,032
Yeah,

314
00:22:20,406 --> 00:22:20,886
it's bad.

315
00:22:20,916 --> 00:22:21,336
It's bad.

316
00:22:21,772 --> 00:22:22,072
point.

317
00:22:22,342 --> 00:22:22,552
Yeah.

318
00:22:22,592 --> 00:22:25,022
And it seems actually potentially very tricky.

319
00:22:25,242 --> 00:22:30,219
When do you see people using multi query transactions?

320
00:22:30,288 --> 00:22:33,553
So like, let's say the, the rails app that we were talking about

321
00:22:34,180 --> 00:22:41,143
Well, with long transaction we have two risks again, like locking
issues, so you can block others and it can be very painful.

322
00:22:41,623 --> 00:22:50,695
And autovacuum . So what I saw people don't understand this simple idea
that locks are released in the very end and they split work into batches,

323
00:22:50,695 --> 00:22:54,955
but these batches are inside one transaction block and it's, it's awful.

324
00:22:55,345 --> 00:22:57,115
Like we perform small updates.

325
00:22:57,205 --> 00:22:58,025
Everything is fine.

326
00:22:58,975 --> 00:23:00,865
But transactional lasts one hour.

327
00:23:01,435 --> 00:23:07,745
And those updates that we are in the very beginning
locks that's a shame that those updates are still held.

328
00:23:08,525 --> 00:23:16,245
So you have a lot of blocked concurrent commits and
they have those like victim queries they can, can

329
00:23:16,245 --> 00:23:19,365
be, of course can reach statement timeout and fail.

330
00:23:19,365 --> 00:23:22,995
So we see degradation in best case a statement.

331
00:23:22,995 --> 00:23:28,408
Timeout will save us to have a, like a
chain reaction, but it's still not good.

332
00:23:29,758 --> 00:23:30,448
This is why.

333
00:23:31,198 --> 00:23:31,558
Yeah.

334
00:23:32,038 --> 00:23:39,388
That's why I think by default in OLTP transaction duration should
be limited, and I saw people implementing these on application side.

335
00:23:40,748 --> 00:23:49,783
Which is of course like a weak limitation, because you might have multiple
application called parts if sometimes in different, different languages.

336
00:23:49,813 --> 00:23:54,193
So, and still somebody can connect to using some tool and so on.

337
00:23:54,193 --> 00:23:58,093
And so like, I would rather see it on
the Postgres side, but doesn't exist yet.

338
00:23:59,299 --> 00:23:59,629
Yeah.

339
00:23:59,809 --> 00:24:01,699
And just to go back to something you said earlier.

340
00:24:01,779 --> 00:24:08,547
So if you set quite an aggressive one, let's say even if it's
30 seconds as a timeout if we're doing a big data migration,

341
00:24:08,547 --> 00:24:15,346
or if we, if we need to do a big schema change or add an
added large index, we might need to set that the okay, cool.

342
00:24:15,396 --> 00:24:17,526
I'm guessing if we create an index concurrently

343
00:24:17,759 --> 00:24:20,189
you should do statement amounts set to zero.

344
00:24:20,279 --> 00:24:20,549
Right?

345
00:24:20,851 --> 00:24:21,121
Yup.

346
00:24:21,609 --> 00:24:21,899
Right.

347
00:24:22,289 --> 00:24:24,919
But it's, there's a, another topic there.

348
00:24:24,919 --> 00:24:32,509
We probably should discuss it next time about lock timeout setting
. Let's keep it outside of today's discussion  so yeah, exactly.

349
00:24:32,509 --> 00:24:33,319
This is a good point.

350
00:24:33,320 --> 00:24:33,860
Wonderful.

351
00:24:34,160 --> 00:24:35,600
I think we've done alright there.

352
00:24:35,750 --> 00:24:42,600
What I like about it actually like sometimes people
say we don't want to have timeout because it's painful.

353
00:24:42,600 --> 00:24:43,830
You reach it, you fail.

354
00:24:44,070 --> 00:24:48,360
Well, if you have 15 seconds and then you fail, you see it immediately.

355
00:24:48,930 --> 00:24:57,705
Then you said, like, if you have 30 minutes, for example,
and you fail after 30 minutes, damage already there.

356
00:24:57,705 --> 00:25:02,025
And you also like feedback loop is huge.

357
00:25:02,385 --> 00:25:06,165
This is painful, but small statement timeouts are not that painful.

358
00:25:06,375 --> 00:25:08,755
People see them, people adjust settings and go, that's

359
00:25:08,755 --> 00:25:09,375
That's a really good point.

360
00:25:09,375 --> 00:25:11,525
I'd not considered that, that the smaller it is the less painful it is.

361
00:25:11,555 --> 00:25:17,595
Of course the user doesn't see what they're expecting to see, and  there's
a problem, but in the grand scheme of things, it's a much smaller

362
00:25:17,595 --> 00:25:21,135
problem than you would have had if it had been a minutes or, or longer.

363
00:25:21,165 --> 00:25:21,465
Yeah.

364
00:25:21,525 --> 00:25:22,005
Great point.

365
00:25:22,275 --> 00:25:22,485
Right.

366
00:25:22,485 --> 00:25:28,795
Small, timeouts are good in terms of user experience,
because it's like fail fast and, and adjust that's it.

367
00:25:29,645 --> 00:25:29,905
Right.

368
00:25:30,427 --> 00:25:30,817
Awesome.

369
00:25:31,207 --> 00:25:33,808
Is there anything else you wanted to cover on that?

370
00:25:33,858 --> 00:25:38,328
maybe that's it for, for first step pilot episode, right?

371
00:25:39,288 --> 00:25:39,468
Yeah.

372
00:25:39,498 --> 00:25:40,098
Let's see.

373
00:25:40,165 --> 00:25:44,485
Let's see what people will tell us in Twitter where, where we can.

374
00:25:45,010 --> 00:25:46,641
Get the feedback.

375
00:25:46,672 --> 00:25:53,854
I'll put some links in our, in the show notes so you can find us yeah,
please let us know what you think, what you want to be discussed.

376
00:25:53,974 --> 00:25:55,774
Any questions you have be really welcome.

377
00:25:57,674 --> 00:25:59,394
Thank you, Ni see you next week.

378
00:25:59,528 --> 00:25:59,618
Good.

379
00:25:59,850 --> 00:26:00,390
See you.

380
00:26:00,510 --> 00:26:00,810
Bye bye.