1
00:00:00,060 --> 00:00:02,600
Michael: Hello and welcome to PostgresFM, a weekly show about
2
00:00:02,600 --> 00:00:03,540
all things PostgreSQL.
3
00:00:03,740 --> 00:00:05,580
I am Michael, founder of pgMustard.
4
00:00:05,680 --> 00:00:07,640
This is Nikolay, founder of Postgres.AI.
5
00:00:07,640 --> 00:00:08,460
Hello Nikolay.
6
00:00:08,940 --> 00:00:10,300
Nikolay: Hi Michael, how are you?
7
00:00:10,860 --> 00:00:12,280
Michael: I'm good, how are you?
8
00:00:12,560 --> 00:00:13,280
Nikolay: Very good.
9
00:00:13,580 --> 00:00:15,660
Let's talk about some crazy stuff.
10
00:00:16,160 --> 00:00:20,040
Michael: I chose the topic this week and it was a listener suggestion,
11
00:00:20,280 --> 00:00:24,020
it's Postgres gotchas and we've had a short discussion before
12
00:00:24,020 --> 00:00:26,700
this call about what we should even count as gotchas.
13
00:00:27,400 --> 00:00:30,620
So how do you define it, what do you think of when you hear Postgres
14
00:00:30,620 --> 00:00:31,120
gotcha?
15
00:00:31,580 --> 00:00:35,160
Nikolay: Well if you for example run, I don't know, like SELECT
16
00:00:35,360 --> 00:00:39,980
5/2, what will you get?
17
00:00:41,580 --> 00:00:43,220
Michael: Is this like data type casting?
18
00:00:43,740 --> 00:00:47,000
Nikolay: Right right well it's not Postgres related I think it's
19
00:00:47,040 --> 00:00:51,140
SQL related but still what do you expect to get?
20
00:00:51,180 --> 00:00:54,560
Normal person would expect 2.5, right?
21
00:00:55,260 --> 00:00:58,300
But you will get, and I just double checked because I always...
22
00:00:59,220 --> 00:01:02,260
I'm always like thinking, am I right?
23
00:01:02,580 --> 00:01:05,580
So you're getting 2, so 5.
24
00:01:05,940 --> 00:01:08,740
Michael: 0 wow, I actually thought it was going to be 3.
25
00:01:13,700 --> 00:01:15,700
Nikolay: We probably have 2 things to discuss here.
26
00:01:15,700 --> 00:01:19,560
First is of course integer and integer, right?
27
00:01:19,560 --> 00:01:23,000
Results should be integer, so there's no decimal, there's no
28
00:01:23,000 --> 00:01:24,820
point here, there's no dot.
29
00:01:25,280 --> 00:01:27,680
And, but 2 or 3?
30
00:01:29,340 --> 00:01:31,600
Rounding or flooring, right?
31
00:01:31,640 --> 00:01:35,280
Michael: Yeah, because like rounding conventions at least where
32
00:01:35,280 --> 00:01:40,600
I grew up we generally round up from 0.5 but not clearly not
33
00:01:40,600 --> 00:01:45,560
here anyway I like this almost this definition which is something
34
00:01:45,860 --> 00:01:50,820
where you may expect 1 outcome but a different outcome happens.
35
00:01:50,860 --> 00:01:51,360
That's
36
00:01:51,560 --> 00:01:54,100
Nikolay: unexpected behavior basically in other words.
37
00:01:54,860 --> 00:01:57,900
Michael: Yeah, especially unexpected if you haven't read the
38
00:01:57,900 --> 00:01:58,400
documentation.
39
00:02:00,640 --> 00:02:04,740
Nikolay: Sometimes documentation also wrong or misleading or
40
00:02:05,020 --> 00:02:07,360
sometimes it's just confusing.
41
00:02:08,540 --> 00:02:09,220
Michael: Yeah fair enough.
42
00:02:09,220 --> 00:02:12,160
I just wanted an excuse to list a few things that are made very
43
00:02:12,160 --> 00:02:16,020
clear in the documentation, but I know people get confused by
44
00:02:16,020 --> 00:02:17,940
it because often they don't read that.
45
00:02:18,820 --> 00:02:22,280
Nikolay: You know, I'm curious, I actually found a good example,
46
00:02:22,280 --> 00:02:22,540
right?
47
00:02:22,540 --> 00:02:25,680
It's like probably a gotcha SQL, not Postgres, because
48
00:02:25,680 --> 00:02:30,040
I think it's maybe defined by standard, but, and this is super
49
00:02:30,040 --> 00:02:34,940
basic, but I'm wondering why indeed it's 2 not 3 because if you
50
00:02:34,940 --> 00:02:39,440
for example I what I did I double check of course I checked floor
51
00:02:39,480 --> 00:02:44,060
and round functions and of course
as expected floor I converted
52
00:02:44,060 --> 00:02:47,780
5 to numeric or just just to
53
00:02:47,780 --> 00:02:49,320
Michael: last with the colon colon
54
00:02:49,360 --> 00:02:52,360
Nikolay: you're right right so
the result is also numeric and
55
00:02:52,360 --> 00:02:59,480
then we have of course 2.500 like
so but then I checked floor
56
00:02:59,480 --> 00:03:03,800
gives me 2 round gives me 3 is
expected because 2.5 means like
57
00:03:03,800 --> 00:03:08,800
we need if you're rounding according
to their names for these
58
00:03:08,800 --> 00:03:09,860
rules, I don't remember, right?
59
00:03:09,860 --> 00:03:14,700
But usually 5 you go up, 4 you
go down, right?
60
00:03:15,540 --> 00:03:18,660
And it gives 3, Round gives 3.
61
00:03:18,660 --> 00:03:26,100
But I was thinking, okay, why 5
slash 2 integer and integer gives
62
00:03:26,100 --> 00:03:27,320
2 and not 3?
63
00:03:27,400 --> 00:03:29,200
Maybe because it uses 4.
64
00:03:30,180 --> 00:03:34,120
We could check the source code,
of course, but the next thing
65
00:03:34,120 --> 00:03:36,300
I checked is the final thing I
checked.
66
00:03:36,740 --> 00:03:40,160
5 converted to numeric slash 2
gives you 2.5.
67
00:03:40,260 --> 00:03:42,040
And then you convert back to int,
right?
68
00:03:42,040 --> 00:03:43,860
Again, double colon int.
69
00:03:44,380 --> 00:03:45,600
It gives 3.
70
00:03:46,300 --> 00:03:46,800
Amazing.
71
00:03:50,440 --> 00:03:55,940
So how come we have 2 if we don't
use type conversion, explicit
72
00:03:55,960 --> 00:03:57,320
type conversion at all?
73
00:03:58,080 --> 00:03:58,760
Any ideas?
74
00:03:59,760 --> 00:04:01,580
It was good gotcha to find.
75
00:04:01,960 --> 00:04:04,200
I always knew that it's 2 because
I demonstrated.
76
00:04:04,200 --> 00:04:06,920
I just didn't think like why.
77
00:04:07,500 --> 00:04:08,600
Now I think why.
78
00:04:08,940 --> 00:04:09,940
I have questions.
79
00:04:10,240 --> 00:04:13,780
Maybe there is some good explanation,
simple, but I can see easily
80
00:04:13,780 --> 00:04:15,040
that it can be gotcha.
81
00:04:15,480 --> 00:04:17,180
It can be considered gotcha.
82
00:04:17,860 --> 00:04:20,420
And let's agree on 1 more thing.
83
00:04:20,740 --> 00:04:22,400
We don't talk about nulls today.
84
00:04:23,920 --> 00:04:26,520
Michael: I actually did have them
on my list, but not to go into
85
00:04:26,520 --> 00:04:27,020
detail.
86
00:04:27,260 --> 00:04:30,580
Nikolay: We had a whole episode,
and this whole episode is telling
87
00:04:30,580 --> 00:04:36,000
like, nulls are surrounded by gotchas
both like standard defined
88
00:04:36,600 --> 00:04:41,040
standard produce and like global
SQL gotchas and local Postgres
89
00:04:41,040 --> 00:04:45,060
related gotchas as well for example
when you go to a race right
90
00:04:45,060 --> 00:04:49,460
Let's just exclude it because it's
a whole massive area.
91
00:04:49,900 --> 00:04:51,260
Michael: I think that's very sensible.
92
00:04:51,600 --> 00:04:53,800
And if you haven't listened to
that episode, I'll link it up
93
00:04:53,800 --> 00:04:54,740
in the show notes.
94
00:04:55,240 --> 00:04:58,940
Nikolay: Consider it to be fully
included here.
95
00:04:59,140 --> 00:04:59,640
Yeah.
96
00:05:00,080 --> 00:05:00,580
Right.
97
00:05:00,920 --> 00:05:03,680
Okay, let's maybe switch to your
ideas.
98
00:05:03,680 --> 00:05:06,400
I know you have a list, pull something
from it.
99
00:05:06,460 --> 00:05:09,940
Michael: Yeah, I've got a few that
I wanted to make sure we mentioned.
100
00:05:10,080 --> 00:05:14,180
I'm gonna start with some ones
I consider more beginner level
101
00:05:14,540 --> 00:05:17,580
gotchas and then maybe we can work
our way up a little bit.
102
00:05:17,620 --> 00:05:23,880
The first 1 I had is 1 I see all
the time in kind of forums every
103
00:05:23,880 --> 00:05:28,260
now and again on Reddit, quite
a few times in like the Postgres
104
00:05:28,260 --> 00:05:30,520
Slack, a few up on a few mailing
lists.
105
00:05:31,220 --> 00:05:35,760
People getting confused or annoyed
when they name objects in
106
00:05:35,760 --> 00:05:42,380
Postgres and they use mixed case,
so maybe camel case or some
107
00:05:42,380 --> 00:05:47,220
way of naming a table like for
example like a two-worded name
108
00:05:47,380 --> 00:05:50,640
and using capital letters for the
2 names and no space in between
109
00:05:50,640 --> 00:05:53,680
them that kind of thing and then
realizing the table is not called
110
00:05:53,680 --> 00:05:58,040
that behind the scenes and it's
all lowercase and if they've
111
00:05:58,120 --> 00:06:02,820
used an ORM or something to name
these at the ORM often puts
112
00:06:03,420 --> 00:06:07,500
quotes around those case names
so then they become cased in the
113
00:06:07,500 --> 00:06:08,000
database.
114
00:06:08,420 --> 00:06:13,300
So I think this whole area is like
a 1 big gotcha for beginners
115
00:06:14,440 --> 00:06:18,060
and the people who set up the schema
in the first place, if they
116
00:06:18,340 --> 00:06:22,340
do something like this, if they
don't just use lowercase object
117
00:06:22,340 --> 00:06:26,140
names, can really make life a bit
painful for everybody that
118
00:06:26,140 --> 00:06:29,680
comes after them having to double
quote everything.
119
00:06:30,180 --> 00:06:33,840
Nikolay: Yeah, especially Java
people like it.
120
00:06:34,300 --> 00:06:35,040
Michael: Oh, really?
121
00:06:35,140 --> 00:06:37,180
Nikolay: Yeah, I noticed.
122
00:06:37,940 --> 00:06:43,180
Usually, if we have a new customer
and we start discussing their
123
00:06:43,180 --> 00:06:47,780
database, we see some camel-stack-cased
table names.
124
00:06:47,780 --> 00:06:52,440
And I think, I'm already thinking,
are you Java guys?
125
00:06:52,440 --> 00:06:53,540
Yeah, there are.
126
00:06:53,940 --> 00:06:56,260
Usually, not always, but usually
so.
127
00:06:56,440 --> 00:07:00,520
And my previous startup was using
it camel-style.
128
00:07:03,420 --> 00:07:06,240
And we just decided to keep it
as is because it was inherited
129
00:07:06,260 --> 00:07:06,960
from some...
130
00:07:07,200 --> 00:07:09,260
I think it was in MySQL originally.
131
00:07:09,900 --> 00:07:14,020
Like, we started very lean, so
we borrowed some schema from some
132
00:07:14,020 --> 00:07:15,080
open source project.
133
00:07:15,480 --> 00:07:16,580
And it had it.
134
00:07:16,640 --> 00:07:20,680
And I just, knowing this very well,
I still decided to keep it.
135
00:07:20,680 --> 00:07:22,960
But the problem with double quotes
is not like...
136
00:07:22,960 --> 00:07:24,640
It's not a big deal, but when...
137
00:07:24,720 --> 00:07:27,760
You know what comes with quotes
usually, right?
138
00:07:28,580 --> 00:07:29,080
Backslashes.
139
00:07:30,480 --> 00:07:31,180
Michael: Oh, having...
140
00:07:31,180 --> 00:07:32,800
Yeah, having to escape them.
141
00:07:32,900 --> 00:07:34,920
Nikolay: Yeah, and you cannot escape
from this.
142
00:07:34,920 --> 00:07:37,400
You need to use it to escape.
143
00:07:37,660 --> 00:07:37,940
Right?
144
00:07:37,940 --> 00:07:39,980
I mean, yeah.
145
00:07:40,080 --> 00:07:44,780
And backslashes, if you have automation
in many languages, like
146
00:07:44,780 --> 00:07:46,100
sometimes it's nightmare.
147
00:07:46,960 --> 00:07:50,580
Right now we have an ongoing problem
with backslashes and escaping
148
00:07:51,040 --> 00:07:54,360
of quotes and double quotes in
our AI system.
149
00:07:54,440 --> 00:07:57,900
It's a combination of Python and
Bash actually.
150
00:07:58,520 --> 00:08:00,520
And yeah, it drives us crazy sometimes.
151
00:08:00,700 --> 00:08:03,220
We solved some problems, but not
all of them still.
152
00:08:03,580 --> 00:08:07,660
And when Yi needs to talk about
your table names and you give
153
00:08:07,660 --> 00:08:11,420
the schema with double quotes,
and then you want to experiment
154
00:08:11,480 --> 00:08:16,620
in DBLab to find some plans or
something, it sometimes is broken
155
00:08:16,620 --> 00:08:21,160
just because of problems with backslashes
and passing these pieces
156
00:08:21,220 --> 00:08:22,700
between different components.
157
00:08:24,060 --> 00:08:27,040
I would prefer keeping everything
lowercase, that's it.
158
00:08:27,040 --> 00:08:27,680
In post-process.
159
00:08:27,800 --> 00:08:28,300
Right?
160
00:08:29,440 --> 00:08:30,600
Snake style, right?
161
00:08:31,100 --> 00:08:31,600
Michael: Yeah.
162
00:08:31,620 --> 00:08:32,220
Nikolay: And that's it.
163
00:08:32,220 --> 00:08:33,640
And just forget about it.
164
00:08:35,220 --> 00:08:36,000
Michael: Yeah, plays.
165
00:08:36,140 --> 00:08:37,040
Pay for plays.
166
00:08:37,500 --> 00:08:40,440
Nikolay: I wanted to mention the
list of gotchas I saw actually
167
00:08:40,440 --> 00:08:44,960
this is like as we discussed right
before this recording I didn't
168
00:08:44,960 --> 00:08:49,780
know this the word gotcha until
2006 or 2007 and this is exactly
169
00:08:49,780 --> 00:08:52,860
when this list was published.
170
00:08:53,220 --> 00:08:56,500
And I remember we had 2 lists,
1 for Postgres and 1 for MySQL.
171
00:08:57,180 --> 00:09:00,840
For MySQL, it's outside of scope,
right?
172
00:09:01,100 --> 00:09:03,640
Again, even more than nulls.
173
00:09:03,820 --> 00:09:08,080
But this list, I'm looking at it
right now and I'm very first
174
00:09:08,080 --> 00:09:15,940
of all it's survived how many years
like it's insane 18 17 years.
175
00:09:16,860 --> 00:09:17,300
Wow.
176
00:09:17,300 --> 00:09:21,600
And I'm looking at this, it's quite
short list at SQLinfo.de,
177
00:09:23,100 --> 00:09:23,600
right?
178
00:09:24,480 --> 00:09:27,040
And almost everything is crossed
out.
179
00:09:27,980 --> 00:09:28,480
Good.
180
00:09:29,240 --> 00:09:31,640
Michael: Yeah, this I've never
seen this page before.
181
00:09:31,640 --> 00:09:32,740
This is really cool.
182
00:09:32,980 --> 00:09:36,980
And yeah, maybe more than half
of them have been cost
183
00:09:36,980 --> 00:09:37,480
down.
184
00:09:40,080 --> 00:09:41,760
Nikolay: Yeah, account start is very slow.
185
00:09:42,660 --> 00:09:44,120
Let's just laugh at it.
186
00:09:45,060 --> 00:09:47,140
Michael: Oh, and we had an episode
on that, right?
187
00:09:47,540 --> 00:09:48,840
Nikolay: I think we should.
188
00:09:48,840 --> 00:09:51,580
Unicode means UTF-8 left as well.
189
00:09:51,580 --> 00:09:52,400
Well, yeah.
190
00:09:53,740 --> 00:09:56,840
Yeah, constraint checking is interesting,
but maybe let's not
191
00:09:56,840 --> 00:09:57,620
dive into it.
192
00:09:57,620 --> 00:10:00,720
And finally, I wanted to highlight
what else is left.
193
00:10:00,720 --> 00:10:01,660
Only 4 items.
194
00:10:01,880 --> 00:10:02,880
5, Okay, 5.
195
00:10:02,880 --> 00:10:06,960
But 1 of them is upgrades, upgrades,
related stuff.
196
00:10:07,200 --> 00:10:12,100
And I was discussing on Twitter,
on X, I was discussing this
197
00:10:12,100 --> 00:10:13,940
topic over the last few days.
198
00:10:14,180 --> 00:10:20,260
I just checked documentation of
3 major managed Postgres providers.
199
00:10:20,840 --> 00:10:24,300
So RDS, Cloud SQL, and Azure Postgres.
200
00:10:25,240 --> 00:10:30,460
I saw that all of them say, okay,
upgrade is done, major upgrade.
201
00:10:30,600 --> 00:10:33,900
Now it's time, don't forget, now it's time to run ANALYZE.
202
00:10:33,900 --> 00:10:38,300
And they say ANALYZE like they give you a snippet, ANALYZE semicolon,
203
00:10:38,400 --> 00:10:41,220
which means it's single-threaded as well.
204
00:10:41,520 --> 00:10:43,860
First of all, they don't include it.
205
00:10:43,860 --> 00:10:46,800
None of them include it into automation.
206
00:10:47,620 --> 00:10:50,460
But we don't talk about managed Postgres providers today, we
207
00:10:50,460 --> 00:10:51,320
talk about Postgres.
208
00:10:52,500 --> 00:10:58,180
My question is why pg_upgrade does not include...
209
00:10:58,180 --> 00:11:01,220
It prints only some text in there, but who reads the text?
210
00:11:01,440 --> 00:11:05,140
Many people will say, for those who don't read, it's on their
211
00:11:05,140 --> 00:11:06,520
shoulders, it's their problem.
212
00:11:06,820 --> 00:11:08,460
But I cannot agree with it.
213
00:11:09,600 --> 00:11:14,340
Michael: I could even make a case for the gotcha here is that
214
00:11:14,340 --> 00:11:17,460
statistics are not preserved on major upgrade.
215
00:11:17,800 --> 00:11:23,860
I think if you asked somebody that didn't know whether they would
216
00:11:23,860 --> 00:11:27,900
expect the statistics to be wiped or to be preserved, somebody
217
00:11:27,900 --> 00:11:31,080
maybe doesn't understand how difficult it might be or like the
218
00:11:31,080 --> 00:11:34,640
catalog, like the reasons why they're not preserved, you'd be
219
00:11:34,640 --> 00:11:38,600
forgiven to think like in a in the most advanced open source
220
00:11:38,600 --> 00:11:42,600
database that that might be that might happen even though it's
221
00:11:42,600 --> 00:11:45,110
complicated and I think there has been some work.
222
00:11:45,110 --> 00:11:45,290
It should
223
00:11:45,290 --> 00:11:47,660
Nikolay: happen and Everyone agrees on it, just not solved.
224
00:11:48,280 --> 00:11:50,520
Michael: Yeah, so I think that's the main gotcha.
225
00:11:50,540 --> 00:11:54,280
And then, obviously, the solution at the moment is we need to
226
00:11:54,280 --> 00:11:55,060
want to analyze.
227
00:11:55,520 --> 00:11:56,980
Nikolay: Yeah, let's unwrap a little bit.
228
00:11:56,980 --> 00:11:59,860
So we talk about statistics, which is required for a planner
229
00:11:59,860 --> 00:12:01,720
to make right decisions.
230
00:12:02,300 --> 00:12:05,640
If the database is not trivial, like containing like 100 rows
231
00:12:05,640 --> 00:12:06,960
in each table, that's it.
232
00:12:06,960 --> 00:12:08,860
If it's not trivial, we need statistics.
233
00:12:09,520 --> 00:12:11,440
Without statistics, database is slow.
234
00:12:12,700 --> 00:12:17,680
And what happens, I think in a zillion cases, it happens.
235
00:12:17,920 --> 00:12:21,420
Like It's hard to estimate, but when I posted this tweet, I received
236
00:12:21,420 --> 00:12:29,120
several replies immediately that, oh, we got beaten by this badly.
237
00:12:31,400 --> 00:12:35,740
Some person wrote, we learned this hard way, obviously.
238
00:12:36,260 --> 00:12:38,460
And this is like what happens all the time.
239
00:12:38,460 --> 00:12:45,800
And recently, during summer, we also like failed to help a customer
240
00:12:45,800 --> 00:12:46,160
with it.
241
00:12:46,160 --> 00:12:48,580
Like we discussed major upgrades, but we...
242
00:12:48,680 --> 00:12:52,040
From now on, I'm pointing out, like, don't forget, don't forget,
243
00:12:52,040 --> 00:12:52,780
don't forget.
244
00:12:53,100 --> 00:12:55,960
But we discussed upgrades, we discussed some, like, complicated
245
00:12:56,040 --> 00:13:00,520
stuff, and then they went to upgrade, and next Monday they say,
246
00:13:00,520 --> 00:13:02,160
oh, today they are in Europe.
247
00:13:02,160 --> 00:13:06,220
So in the morning, it was already by the evening in Europe.
248
00:13:06,220 --> 00:13:10,380
And they say, we had nasty database incidents today because guess
249
00:13:10,380 --> 00:13:10,760
what?
250
00:13:10,760 --> 00:13:13,120
We forgot to run ANALYZE.
251
00:13:14,240 --> 00:13:18,480
And this is huge gotcha not only
by managed services, they could
252
00:13:18,480 --> 00:13:21,520
do it but I don't care too much
about them.
253
00:13:21,660 --> 00:13:23,040
This is a gotcha of pg_upgrade.
254
00:13:23,240 --> 00:13:26,140
I think it's very well understood,
there is work in progress
255
00:13:26,200 --> 00:13:31,980
for export and import of statistics,
like quite a long thread
256
00:13:31,980 --> 00:13:34,500
in hackers already and commitfest
entry.
257
00:13:34,900 --> 00:13:38,340
It's still not done, but there
is hope.
258
00:13:39,960 --> 00:13:41,400
There is 1 more thing here.
259
00:13:41,400 --> 00:13:44,940
So I think pg_upgrade definitely
should have it.
260
00:13:45,060 --> 00:13:48,220
And also pg_restore, but let's return
to it in a few minutes.
261
00:13:50,280 --> 00:13:54,780
So when someone says you need to
recalculate statistics yourself
262
00:13:55,240 --> 00:13:57,140
after upgrade, pg_upgrade.
263
00:13:58,920 --> 00:14:01,820
I definitely would expect pg_upgrade
to do this for sure.
264
00:14:02,020 --> 00:14:04,220
But okay, it just prints do it.
265
00:14:04,340 --> 00:14:07,420
pg_upgrade prints vacuumdb, I think,
no?
266
00:14:07,420 --> 00:14:10,280
Or in stages, or like vacuumdb
in stages.
267
00:14:10,280 --> 00:14:14,060
It has this vacuumdb analyze only
in stages.
268
00:14:14,060 --> 00:14:14,640
2 options.
269
00:14:14,640 --> 00:14:16,940
Analyze only means no vacuum and
just Analyze.
270
00:14:17,320 --> 00:14:23,420
In stages means first it gets only
1 bucket for each table, for
271
00:14:23,420 --> 00:14:25,320
each column actually, right?
272
00:14:25,520 --> 00:14:29,480
And then some midway until your...
273
00:14:29,480 --> 00:14:34,600
And then default_statistics_target
or individual column based
274
00:14:34,600 --> 00:14:36,180
setting for each column.
275
00:14:36,340 --> 00:14:39,780
default_statistics_target by default
is 100, 100 buckets.
276
00:14:40,560 --> 00:14:46,580
This is what defines how much Postgres
planner will know about
277
00:14:46,680 --> 00:14:48,780
distribution in each column, right?
278
00:14:48,780 --> 00:14:52,040
And it can be adjusted globally
or for individual columns.
279
00:14:52,840 --> 00:14:53,260
But...
280
00:14:53,260 --> 00:14:56,100
Michael: Almost like a sample size,
like the sample size increases
281
00:14:56,260 --> 00:14:57,320
of what it samples.
282
00:14:57,320 --> 00:14:57,560
Nikolay: Yeah.
283
00:14:57,560 --> 00:15:01,200
And I quickly realized this is
not what you want to do in OLTP,
284
00:15:01,220 --> 00:15:06,560
because in OLTP, you either include
Analyze inside maintenance
285
00:15:06,580 --> 00:15:06,980
window.
286
00:15:06,980 --> 00:15:11,180
In this case, there is no sense
to run it 3 times, or you just...
287
00:15:12,880 --> 00:15:14,940
Or that's it, there is only option
actually.
288
00:15:15,180 --> 00:15:19,440
Like, I don't trust in opening
gates with weak statistics.
289
00:15:19,680 --> 00:15:22,220
It will be like unpredictable again
incident.
290
00:15:23,080 --> 00:15:23,580
Michael: Yeah.
291
00:15:24,140 --> 00:15:24,820
Yeah, risky.
292
00:15:24,860 --> 00:15:28,080
But this feels like a topic that
might be worth a deeper dive.
293
00:15:28,440 --> 00:15:28,940
Nikolay: Right.
294
00:15:29,280 --> 00:15:29,640
Right.
295
00:15:29,640 --> 00:15:30,040
Right.
296
00:15:30,040 --> 00:15:34,300
I just wanted to point out that
when people say Analyze, it's
297
00:15:34,300 --> 00:15:36,640
just 1 threaded, right?
298
00:15:37,120 --> 00:15:41,420
And you expect Postgres has a lot
of parallelism implemented.
299
00:15:41,940 --> 00:15:44,440
But when you run Analyze, it's
always single threaded.
300
00:15:44,680 --> 00:15:46,800
This is unexpected as well, right?
301
00:15:47,100 --> 00:15:47,540
Michael: Good 1.
302
00:15:47,540 --> 00:15:48,520
Yeah, I like that.
303
00:15:48,520 --> 00:15:51,640
Nikolay: It's kind of gotcha inside
gotcha we have matryoshka
304
00:15:51,700 --> 00:15:54,620
style gotcha here right Russian
doll style
305
00:15:54,620 --> 00:15:57,620
Michael: what do you do you recommend
doing like just kicking
306
00:15:57,620 --> 00:16:00,540
off a bunch of queries analyzed
tape like per table
307
00:16:00,540 --> 00:16:04,380
Nikolay: I recommend partition
I recommend vacuumdb
308
00:16:04,800 --> 00:16:05,780
Michael: nice okay
309
00:16:05,920 --> 00:16:08,680
Nikolay: And speed up and go full
speed inside maintenance window.
310
00:16:08,680 --> 00:16:09,940
Again, it's a different story.
311
00:16:10,200 --> 00:16:15,780
But this vacuumdb, it has
--jobs
312
00:16:15,780 --> 00:16:17,120
or -j.
313
00:16:17,440 --> 00:16:18,300
You can define.
314
00:16:18,300 --> 00:16:22,240
And usually, we take as many courses
we have on the server and
315
00:16:22,240 --> 00:16:24,780
go full speed inside maintenance
window to achieve.
316
00:16:25,120 --> 00:16:28,200
Analyze should be quite fast if
you don't do vacuum.
317
00:16:29,160 --> 00:16:33,540
There is also gotcha a little bit
in vacuumdb options because
318
00:16:33,600 --> 00:16:37,320
vacuumdb's, --analyze
will also do vacuum.
319
00:16:37,580 --> 00:16:38,200
So there's
320
00:16:38,200 --> 00:16:39,860
Michael: also analyze only or
321
00:16:39,860 --> 00:16:41,140
Nikolay: there is analyze only.
322
00:16:41,240 --> 00:16:43,120
Yeah, but it's easy to overlook.
323
00:16:43,340 --> 00:16:45,800
There's 1 more gotcha here inside
all this.
324
00:16:46,300 --> 00:16:51,560
When people go with manual single-threaded
analyze or maybe multi-threaded
325
00:16:51,820 --> 00:16:54,120
but just explicit analyze.
326
00:16:55,080 --> 00:16:59,960
I've like quickly, it was interesting,
I was thinking someone
327
00:16:59,960 --> 00:17:01,560
said analyze can be super expensive.
328
00:17:01,560 --> 00:17:05,140
I agree, but even if it's like,
if the default_statistics_target
329
00:17:05,140 --> 00:17:06,780
is 1000, it's not that expensive.
330
00:17:06,780 --> 00:17:11,020
But back to this, can we throttle
it?
331
00:17:12,520 --> 00:17:16,100
For example, you want it to go
very slow, because autovacuum
332
00:17:16,100 --> 00:17:19,660
has autovacuum, doesn't have actually
333
00:17:19,660 --> 00:17:20,940
Michael: a vacuum_cost_delay also?
334
00:17:21,180 --> 00:17:24,060
Nikolay: Yes, there is
vacuum_cost_delay and vacuum_cost_limit.
335
00:17:25,360 --> 00:17:28,440
By default, it's off because I
think the cost limit is 0, meaning
336
00:17:28,440 --> 00:17:30,040
that it's not checked at all.
337
00:17:30,400 --> 00:17:33,720
Not cost limit, cost delay is 0,
like no check.
338
00:17:34,120 --> 00:17:38,820
But autovacuum has like mirrored
pair of these settings.
339
00:17:39,560 --> 00:17:40,460
It's throttled.
340
00:17:41,060 --> 00:17:43,520
Long story short, autovacuum is
throttled usually.
341
00:17:44,340 --> 00:17:47,440
And we actually usually fight with
our customers a little bit
342
00:17:47,440 --> 00:17:51,000
to increase this, make it even
more aggressive.
343
00:17:52,800 --> 00:17:56,040
And managed providers already did
part of that job, which is
344
00:17:56,040 --> 00:17:56,540
good.
345
00:17:56,880 --> 00:18:00,840
But if we run it manually, it's
not throttled.
346
00:18:00,840 --> 00:18:02,140
It will go full speed.
347
00:18:02,200 --> 00:18:06,960
I doubt you will need to throttle
it, but some people want, okay,
348
00:18:06,960 --> 00:18:08,040
let's throttle it.
349
00:18:08,100 --> 00:18:09,340
Question, how?
350
00:18:09,620 --> 00:18:14,440
We have a vacuum limit, vacuum_cost_limit, vacuum_cost_delay.
351
00:18:15,040 --> 00:18:19,060
We can set vacuum_cost_delay from default 0 to some point.
352
00:18:19,600 --> 00:18:23,320
But is it about Analyze, or it's only about vacuum?
353
00:18:23,320 --> 00:18:25,680
Based on just the naming, it's not about Analyze.
354
00:18:25,680 --> 00:18:27,040
It's about vacuum only.
355
00:18:28,140 --> 00:18:28,640
Michael: Right?
356
00:18:28,680 --> 00:18:29,900
Yeah, that would be my guess.
357
00:18:29,900 --> 00:18:33,780
But by the line of your questioning, I'm guessing it does affect
358
00:18:33,780 --> 00:18:34,620
your analysis.
359
00:18:34,820 --> 00:18:35,220
Nikolay: Right.
360
00:18:35,220 --> 00:18:39,620
I go to documentation and I see vacuum_cost_limit, vacuum_cost_delay,
361
00:18:39,620 --> 00:18:40,680
I think.
362
00:18:40,680 --> 00:18:41,740
And I see description.
363
00:18:41,980 --> 00:18:44,560
They talk only about vacuum, they don't talk about analysis.
364
00:18:45,040 --> 00:18:47,740
So I make conclusion, it's only about vacuum.
365
00:18:48,400 --> 00:18:53,560
But some part of my very long-term memory tells me, is it really
366
00:18:53,560 --> 00:18:54,060
so?
367
00:18:55,840 --> 00:18:59,540
And then Sadek Dusty, who helped me a lot with, and still helping
368
00:18:59,540 --> 00:19:03,240
sometimes when I write at how-to's and reviews How-To's, I'm
369
00:19:03,240 --> 00:19:06,060
very thankful for that.
370
00:19:06,880 --> 00:19:08,660
I have already 94 how-tos.
371
00:19:09,720 --> 00:19:11,660
So I wrote how-to, how-to Analyze.
372
00:19:11,930 --> 00:19:14,980
And right now I need to add this.
373
00:19:16,220 --> 00:19:19,140
vacuum_cost_limit, vacuum_cost_delay, they affect Analyze.
374
00:19:19,440 --> 00:19:23,680
If you scroll up to the beginning of section, you will see that
375
00:19:23,680 --> 00:19:26,340
it talks about both vacuum and Analyze.
376
00:19:26,800 --> 00:19:30,680
And we also, using AI, I checked source code and quickly found
377
00:19:30,680 --> 00:19:35,200
that there is a function called vacuumExec, which works with
378
00:19:35,200 --> 00:19:36,540
both vacuum and Analyze.
379
00:19:36,820 --> 00:19:40,040
And this confusion comes from source code, propagates to the
380
00:19:40,040 --> 00:19:42,480
documentation, and then to our heads, right?
381
00:19:42,560 --> 00:19:43,940
This is gotcha as well.
382
00:19:43,940 --> 00:19:47,460
So you can use vacuum_cost_limit, vacuum_cost_delay, to throttle
383
00:19:47,600 --> 00:19:51,300
Analyze, although I usually don't do it because I think we need
384
00:19:51,300 --> 00:19:52,320
to go full speed.
385
00:19:53,400 --> 00:19:55,900
Michael: And in the 1 example we're talking about here, we want
386
00:19:55,900 --> 00:19:57,280
to go faster, not slower.
387
00:19:57,660 --> 00:19:58,880
Nikolay: Right, right.
388
00:19:59,340 --> 00:20:00,560
But it's interesting, right?
389
00:20:00,660 --> 00:20:02,080
So there is some small confusion.
390
00:20:02,080 --> 00:20:04,060
I think there are many such confusions.
391
00:20:04,820 --> 00:20:06,540
But they are slowly fixed.
392
00:20:06,680 --> 00:20:08,400
They are slowly being fixed.
393
00:20:08,400 --> 00:20:10,860
Michael: Great example of 1 where the documentation
394
00:20:10,920 --> 00:20:13,760
wasn't helpful in avoiding that 1.
395
00:20:13,940 --> 00:20:14,780
So yeah, nice.
396
00:20:15,120 --> 00:20:17,960
I've got a few more beginner-friendly ones that maybe we could
397
00:20:17,960 --> 00:20:18,740
rattle through.
398
00:20:18,740 --> 00:20:23,560
I'm not even sure you'd consider them all gotchas, but 1 relates
399
00:20:27,620 --> 00:20:28,280
of places.
400
00:20:29,040 --> 00:20:30,900
I think that not only catches...
401
00:20:30,920 --> 00:20:32,660
Well, you don't think it's a gotcha?
402
00:20:33,120 --> 00:20:36,220
Nikolay: I think we should exclude
this on the same, like, we're
403
00:20:36,220 --> 00:20:41,540
using the same approach as now
because it's a huge bag of, yeah.
404
00:20:41,920 --> 00:20:42,320
Michael: Cool.
405
00:20:42,320 --> 00:20:45,080
And then another 1 that I see catch
out, well, a couple that
406
00:20:45,080 --> 00:20:48,280
I see catch out beginners, again,
maybe this is another huge
407
00:20:48,280 --> 00:20:53,400
topic, but access exclusive locks
on like just basically DDL
408
00:20:54,300 --> 00:20:58,080
blocking selects, like blocking
every, blocking inserts and like
409
00:20:58,080 --> 00:21:02,620
you know all sorts of people getting,
I think people don't realize
410
00:21:02,620 --> 00:21:05,680
because if they're working on small
databases at first, they
411
00:21:05,680 --> 00:21:09,860
start to grow in confidence of
how to do things.
412
00:21:10,080 --> 00:21:14,620
And then it only catches them out
once the startup's got really
413
00:21:14,620 --> 00:21:17,260
successful and the table's much
bigger, that kind of thing.
414
00:21:17,680 --> 00:21:23,080
Nikolay: Yeah, I think there is
some memes with Gauss distribution,
415
00:21:23,080 --> 00:21:24,500
or how it's called, right?
416
00:21:24,680 --> 00:21:25,660
Gaussian distribution.
417
00:21:25,760 --> 00:21:29,840
And first, I think schema changes
are painful.
418
00:21:31,300 --> 00:21:34,660
In the, like, schema changes are
super painful.
419
00:21:34,660 --> 00:21:36,920
They require expertise, like, beginner,
right?
420
00:21:37,040 --> 00:21:41,460
Then in the middle, you can imagine,
oh, Postgres has transactional
421
00:21:41,580 --> 00:21:44,720
DDL, everything is fine, we just
need to wrap everything into
422
00:21:44,720 --> 00:21:48,400
1 transaction and, and rely on
Postgres great capabilities of
423
00:21:48,400 --> 00:21:49,040
transactional DDL.
424
00:21:49,040 --> 00:21:50,860
Michael: Yeah, or just concurrently
and think
425
00:21:50,860 --> 00:21:53,400
Nikolay: yeah Yeah, MVCC works.
426
00:21:53,400 --> 00:21:56,680
Well, it doesn't block sell Excel
X are always working you can
427
00:21:56,680 --> 00:22:01,580
you can have a lot of Like exciting
stuff in the middle and then
428
00:22:01,580 --> 00:22:05,360
on the on the right you say post
this doesn't look was this has
429
00:22:05,360 --> 00:22:08,520
come Transactional detail, but
you cannot use it
430
00:22:09,040 --> 00:22:11,280
Michael: Well the in on the right
has to be the same as the left,
431
00:22:11,280 --> 00:22:11,720
doesn't it?
432
00:22:11,720 --> 00:22:13,040
Schema changes are painful.
433
00:22:13,380 --> 00:22:17,320
Nikolay: Yes, they are painful,
transactional DDL cannot be used
434
00:22:17,320 --> 00:22:24,380
in many, many cases, and you can
shoot your feet very well many
435
00:22:24,380 --> 00:22:24,880
times.
436
00:22:29,600 --> 00:22:31,120
So this is it, I think.
437
00:22:31,120 --> 00:22:32,940
And a lot of gotchas there.
438
00:22:33,740 --> 00:22:36,180
Michael: Another 1 that's not necessarily,
I'm not sure if this
439
00:22:36,180 --> 00:22:38,400
counts as a gotcha, I'd be interested
in your opinion but it
440
00:22:38,400 --> 00:22:41,780
does have seen it catch people
out and I guess it's a little
441
00:22:41,780 --> 00:22:45,340
bit of a foot gun and that's you
know you see the memes about
442
00:22:45,340 --> 00:22:48,620
this as well actually DELETE or
UPDATE without a WHERE clause
443
00:22:48,620 --> 00:22:52,000
and then just suddenly seeing you
know instead of 5 updated.
444
00:22:55,080 --> 00:22:57,720
But if you think like it's not
Postgres, why do you say it's
445
00:22:57,720 --> 00:22:59,120
not Postgres out of interest?
446
00:22:59,340 --> 00:23:02,360
Nikolay: Because the SQL standard
does it like defines this and
447
00:23:02,360 --> 00:23:06,540
any relational database which follows
SQL standard to some extent
448
00:23:07,120 --> 00:23:07,940
has it.
449
00:23:08,760 --> 00:23:09,260
Michael: Yeah.
450
00:23:10,140 --> 00:23:17,360
But I've seen clients catch these,
But the ones that ship, well,
451
00:23:17,360 --> 00:23:18,980
the only, the psql doesn't.
452
00:23:19,120 --> 00:23:22,480
So I don't think it's unreasonable
that Postgres does it this
453
00:23:22,480 --> 00:23:24,980
way, but I've definitely seen it
catch people out.
454
00:23:25,440 --> 00:23:26,100
Nikolay: I agree.
455
00:23:26,580 --> 00:23:27,880
Blame SQL standard.
456
00:23:28,140 --> 00:23:30,780
And there is extension to forbid
it.
457
00:23:30,780 --> 00:23:35,080
And also it was maybe 1 of the
very first hacking sessions with
458
00:23:35,080 --> 00:23:35,500
Andrei.
459
00:23:35,500 --> 00:23:43,000
We implemented some setting, GUC
setting in Postgres, which would
460
00:23:43,000 --> 00:23:48,820
allow administrators to, I don't
think error, warning, like completely
461
00:23:48,820 --> 00:23:50,380
forbid or just warn, I don't remember.
462
00:23:50,380 --> 00:23:52,220
I think error should be here.
463
00:23:52,540 --> 00:23:55,680
But it was not accepted, of course,
because I think it was discussed
464
00:23:55,680 --> 00:23:56,420
many times.
465
00:23:57,280 --> 00:23:58,480
I mean, it's possible.
466
00:23:58,940 --> 00:24:02,960
And right now, as I see, I think
PostgREST does it, right?
467
00:24:03,340 --> 00:24:03,840
PostgREST...
468
00:24:04,460 --> 00:24:05,440
Michael: Oh, does it?
469
00:24:05,640 --> 00:24:08,800
Nikolay: So usually right now it's
sold, if there is some middleware,
470
00:24:08,940 --> 00:24:09,840
it's sold there.
471
00:24:10,320 --> 00:24:15,220
So if you develop API using PostgREST,
for example, I think, Maybe
472
00:24:15,220 --> 00:24:17,220
I'm mistaken, but I think there
is a way.
473
00:24:17,320 --> 00:24:19,900
If there is no way, it's a good
idea to implement, actually.
474
00:24:19,900 --> 00:24:20,980
Michael: Yeah, it would be, yeah.
475
00:24:20,980 --> 00:24:26,820
Nikolay: Yeah, so if in URL of
your HTTP request of RESTful API,
476
00:24:27,080 --> 00:24:31,720
there is a command to run delete
without any filters, I think
477
00:24:31,720 --> 00:24:36,860
it will not do it it will not go
to progress with it so but inside
478
00:24:36,860 --> 00:24:42,380
database I don't I don't think
it's going to change right and
479
00:24:42,380 --> 00:24:44,080
we tried actually so
480
00:24:44,760 --> 00:24:48,060
Michael: yes with the well and
we tried what do you mean each
481
00:24:48,060 --> 00:24:49,120
right again
482
00:24:49,140 --> 00:24:51,900
Nikolay: we had a session yeah
we had a patch and they submitted
483
00:24:51,900 --> 00:24:56,240
it, but it was a small discussion
and chances are close to 0
484
00:24:56,240 --> 00:24:58,860
that it will be successful.
485
00:24:59,840 --> 00:25:02,060
Michael: Patch in psql or somewhere
else?
486
00:25:02,220 --> 00:25:03,340
Nikolay: No, not in psql.
487
00:25:03,580 --> 00:25:08,340
We wanted to have a Postgres
setting, GUC setting, to just
488
00:25:08,340 --> 00:25:12,840
allow administrators to forbid
unspecified, I don't remember
489
00:25:12,840 --> 00:25:16,500
how we called it, maybe unspecified
delete and update, when you
490
00:25:16,500 --> 00:25:19,020
don't have the WHERE clause at
all.
491
00:25:19,800 --> 00:25:21,120
Just like,
492
00:25:21,220 --> 00:25:21,720
Michael: cool,
493
00:25:22,060 --> 00:25:22,820
Nikolay: don't do it.
494
00:25:22,820 --> 00:25:26,260
There's a truncate for if you want
to hit right an update of
495
00:25:26,260 --> 00:25:30,900
whole table I don't know yeah but
again like right now it's in
496
00:25:30,900 --> 00:25:33,700
middleware usually sold in middle
where You can have triggers
497
00:25:33,700 --> 00:25:35,900
or something to protect yourself
if you want.
498
00:25:36,300 --> 00:25:38,960
Or this extension if you're on
self-managed Postgres.
499
00:25:38,960 --> 00:25:40,540
There is some extension for it.
500
00:25:40,760 --> 00:25:44,840
I wanted to mention 1 thing, step
back to DDL.
501
00:25:45,420 --> 00:25:51,000
My favorite example is, do you
think just adding a Column, you
502
00:25:51,000 --> 00:25:55,240
cannot put your traffic down or
your Database down?
503
00:25:55,240 --> 00:25:57,580
You can, if it cannot acquire Lock.
504
00:25:57,800 --> 00:26:01,660
It starts blocking all Queries
to this Table, even SELECTs.
505
00:26:02,580 --> 00:26:05,780
Like, I cannot acquire Lock, everyone
else can wait.
506
00:26:05,900 --> 00:26:11,540
Even SELECTs who come with access
Share Lock, don't touch this.
507
00:26:12,040 --> 00:26:12,760
That's it.
508
00:26:13,320 --> 00:26:14,340
And that's not good.
509
00:26:14,680 --> 00:26:17,660
And this is super unexpected and
a super big surprise.
510
00:26:18,720 --> 00:26:22,000
Michael: Yeah, especially because
you could even test that.
511
00:26:22,580 --> 00:26:27,620
You could even make a fork of production
or clone it run that
512
00:26:27,620 --> 00:26:31,520
on its own in isolation where there
is no Lock and it runs super
513
00:26:31,520 --> 00:26:35,540
fast so you think this is safe
but even though it was super fast
514
00:26:35,540 --> 00:26:39,720
it does require that heavy Lock
and that blocks everything else
515
00:26:39,720 --> 00:26:41,780
definitely a gotcha that's a great
1
516
00:26:41,800 --> 00:26:45,480
Nikolay: yeah yeah and there are
many others in the area of DDL
517
00:26:45,480 --> 00:26:51,600
but this I find like affecting
every single project which had
518
00:26:51,660 --> 00:26:55,900
like at least some growth like
I don't like to 1000 TPS and it's
519
00:26:55,900 --> 00:26:58,820
already noticeable it's not noticeable
in tiny projects
520
00:27:00,140 --> 00:27:02,900
Michael: yeah yeah which I think
is part is kind of part of the
521
00:27:02,900 --> 00:27:06,100
problem right or like at least
makes it worse because anybody
522
00:27:06,100 --> 00:27:10,920
that gains experience on smaller
projects as they grow Get some
523
00:27:10,920 --> 00:27:13,940
confidence that these things work
or they're fine and then it
524
00:27:13,940 --> 00:27:15,740
change changes at some point
525
00:27:16,320 --> 00:27:16,820
Nikolay: exactly
526
00:27:18,060 --> 00:27:20,140
Michael: so you've got a great
article in this actually I think
527
00:27:20,140 --> 00:27:24,900
that I can share about DDL it's
all about DDL changes I think
528
00:27:25,160 --> 00:27:28,860
Nikolay: yeah it's yeah yeah it's
this this 1 is definitely included
529
00:27:28,860 --> 00:27:33,180
I think this 1 like has a different
blog post about like I have
530
00:27:33,180 --> 00:27:37,980
few about Details okay what else
531
00:27:38,760 --> 00:27:41,720
Michael: well in that in that area
of making big changes partly
532
00:27:41,720 --> 00:27:45,200
by mistake you know that the DELETE
or UPDATE without WHERE Do
533
00:27:45,200 --> 00:27:48,660
you think people would expect to
be able to undo something?
534
00:27:48,940 --> 00:27:54,440
I'm not thinking just from people
with experience of relational
535
00:27:54,440 --> 00:27:58,520
databases, but the fact that if
you do something like that by
536
00:27:58,520 --> 00:28:02,080
mistake, that you have to go to
your backups to get that data
537
00:28:02,080 --> 00:28:02,580
back.
538
00:28:03,080 --> 00:28:06,260
If I was in my early 20s or just
out, you know, if I was in my
539
00:28:06,260 --> 00:28:10,120
teens, all other software I use
has the ability to undo something
540
00:28:10,120 --> 00:28:11,420
if I do it by mistake.
541
00:28:11,940 --> 00:28:16,220
So it does feel slightly unexpected,
potentially if I've grown
542
00:28:16,220 --> 00:28:19,640
up in that world, that I can make
1 mistake and not be able to
543
00:28:19,640 --> 00:28:19,960
undo it.
544
00:28:19,960 --> 00:28:21,220
Nikolay: And what Database you
can?
545
00:28:21,220 --> 00:28:22,440
I mean, of course, there are like...
546
00:28:22,440 --> 00:28:23,440
Michael: Yeah, I don't know.
547
00:28:23,440 --> 00:28:24,660
Nikolay: Time travel or...
548
00:28:25,840 --> 00:28:27,500
If branching is supported, you
can...
549
00:28:27,500 --> 00:28:32,140
But it's still like not super simple, right?
550
00:28:32,520 --> 00:28:33,720
Well, yeah, I don't know.
551
00:28:33,720 --> 00:28:36,800
I mean, there are several projects which try to solve this problem
552
00:28:36,820 --> 00:28:41,760
and I mean the data specific databases supporting branching for
553
00:28:41,760 --> 00:28:47,620
example or even other I think Have you heard about this new company
554
00:28:47,660 --> 00:28:49,520
where Michael Stonebraker is participating?
555
00:28:50,920 --> 00:28:51,060
Michael: I've
556
00:28:51,060 --> 00:28:53,000
heard of it, I don't know much about it.
557
00:28:53,000 --> 00:28:53,500
Nikolay: DBOps?
558
00:28:53,840 --> 00:28:54,840
Or how is it called?
559
00:28:54,840 --> 00:28:55,700
I don't remember.
560
00:28:55,920 --> 00:28:57,100
Not DBOps, DBOS.
561
00:28:58,700 --> 00:28:59,200
Michael: Yes.
562
00:29:00,180 --> 00:29:03,160
Nikolay: So I wonder if this feature will be inside it.
563
00:29:03,860 --> 00:29:04,700
I don't know.
564
00:29:04,740 --> 00:29:06,260
For me, it's not expected.
565
00:29:06,380 --> 00:29:12,100
I cannot name this gotcha because it's a hard problem, and we
566
00:29:13,140 --> 00:29:18,820
didn't get used to it, to having it, because it's rare.
567
00:29:19,710 --> 00:29:28,100
But if we go to young people's minds, there are many gotchas
568
00:29:28,100 --> 00:29:28,780
for them, right?
569
00:29:28,780 --> 00:29:30,700
Oh, this is working not as I expected.
570
00:29:32,080 --> 00:29:36,360
So naming is hard just because it should avoid gotchas.
571
00:29:36,380 --> 00:29:40,480
If you chose some name, you should think about other people and
572
00:29:40,480 --> 00:29:42,180
think to be very predictable.
573
00:29:42,980 --> 00:29:44,840
This is the art of naming.
574
00:29:45,440 --> 00:29:49,200
And we just discussed this vacuum_cost_limit affecting analyze.
575
00:29:50,140 --> 00:29:56,280
So yeah, and in young minds, gotchas, because gotchas world is
576
00:29:56,280 --> 00:30:00,740
much bigger because they don't understand the logic in many places,
577
00:30:00,740 --> 00:30:05,740
how it's like, like common practice in many places, right?
578
00:30:05,740 --> 00:30:08,540
And that's why many more unexpected situations.
579
00:30:09,640 --> 00:30:10,460
Michael: Yeah, fair.
580
00:30:11,000 --> 00:30:14,540
Nikolay: Yeah, by the way, if you run pg_dump on a very large
581
00:30:14,880 --> 00:30:21,400
database, like 1 table after another, And then we deal with snapshot,
582
00:30:21,400 --> 00:30:21,900
right?
583
00:30:22,580 --> 00:30:26,700
With snapshot, it's a repeatable read and snapshot.
584
00:30:26,820 --> 00:30:32,000
And even if you use multiple workers for pg_dump, they are synchronized
585
00:30:32,360 --> 00:30:33,780
reading from the same snapshot.
586
00:30:34,200 --> 00:30:38,100
But what if some table is dropped?
587
00:30:38,360 --> 00:30:40,340
Will it be present in this table?
588
00:30:41,040 --> 00:30:42,180
I'm not sure, actually.
589
00:30:42,880 --> 00:30:44,340
I just think there is a God check
590
00:30:44,340 --> 00:30:44,840
Michael: here.
591
00:30:45,660 --> 00:30:51,300
Nikolay: If during dump there are some schema changes, can we
592
00:30:51,300 --> 00:30:54,240
have some inconsistency in backups?
593
00:30:55,260 --> 00:30:56,580
Not in backups, in dumps.
594
00:30:56,580 --> 00:31:01,160
By the way, this confusion was resolved recently, right?
595
00:31:01,220 --> 00:31:06,040
We didn't mention it in the previous discussions of confusions,
596
00:31:06,060 --> 00:31:15,520
but the pg_dump page is not saying it's for dumps anymore.
597
00:31:15,940 --> 00:31:17,380
So dumps are not backups.
598
00:31:19,660 --> 00:31:20,880
Michael: That's coming.
599
00:31:21,960 --> 00:31:22,960
Nikolay: Yeah, in 2018.
600
00:31:23,100 --> 00:31:23,940
Okay, okay, okay.
601
00:31:23,940 --> 00:31:25,300
We will discuss this additionally.
602
00:31:25,640 --> 00:31:29,940
But this is a big confusion when
the computation says pg_dump
603
00:31:30,060 --> 00:31:31,080
is a backup tool.
604
00:31:31,080 --> 00:31:33,220
It's still so, even in 16.
605
00:31:33,760 --> 00:31:38,660
And now we have all rights to say
it's like all people who said
606
00:31:38,800 --> 00:31:42,500
pg_dump is not a backup tool, we
are right, because finally in
607
00:31:42,500 --> 00:31:44,040
18 it's already changed.
608
00:31:44,040 --> 00:31:47,340
I think it won't be reverted, I
hope so, this patch.
609
00:31:49,540 --> 00:31:54,800
But back to our topic, pg_dump can
be expected to be a tool for
610
00:31:54,800 --> 00:31:56,460
backups just because of documentation.
611
00:31:56,720 --> 00:32:01,320
Then you have quite a painful process
of realizing it's very
612
00:32:01,320 --> 00:32:02,320
bad for backups.
613
00:32:02,900 --> 00:32:06,600
So it's a gotcha, which is already
fixed in the future in Postgres
614
00:32:07,000 --> 00:32:07,500
18.
615
00:32:09,860 --> 00:32:18,900
And if we, in this area, pg_restore
doesn't run analyze, which
616
00:32:19,160 --> 00:32:24,620
hurts every second attempt to restore
a big database, even with
617
00:32:24,620 --> 00:32:26,620
experienced people like I am.
618
00:32:26,880 --> 00:32:29,920
Because, again, it's like with
pg_upgrade, we discussed it.
619
00:32:29,920 --> 00:32:33,400
pg_restore, You restore a huge
dump, you start working with
620
00:32:33,400 --> 00:32:38,940
it, but it's slow, and in plans
you see where you expect a simple
621
00:32:38,940 --> 00:32:42,520
index scan, you see bitmap scans,
bitmap index, bitmap heap scan,
622
00:32:42,520 --> 00:32:44,720
because there is no vacuum, there
is no analysis.
623
00:32:45,060 --> 00:32:50,580
It also should run on a vacuum
to collect visibility maps.
624
00:32:51,340 --> 00:32:51,840
Or
625
00:32:53,300 --> 00:32:56,980
Michael: it might not be unreasonable
to think that maybe backups
626
00:32:56,980 --> 00:32:59,980
would include the statistics and
then restore would restore the
627
00:32:59,980 --> 00:33:00,480
statistics.
628
00:33:01,400 --> 00:33:02,920
Nikolay: Physical backups, I agree.
629
00:33:03,280 --> 00:33:04,860
Logical backups, I disagree.
630
00:33:06,680 --> 00:33:10,260
You don't include indexes, which
is derivative from data itself.
631
00:33:10,260 --> 00:33:12,840
Statistics is also derivative of
data.
632
00:33:13,660 --> 00:33:16,120
So you include description, like
create index.
633
00:33:16,320 --> 00:33:20,520
There should be, maybe should be
some word like analyze in there.
634
00:33:22,060 --> 00:33:25,620
I wanted to say create statistics,
but it was analyzed already.
635
00:33:26,180 --> 00:33:27,100
There's such word.
636
00:33:27,500 --> 00:33:28,200
Why restore?
637
00:33:28,200 --> 00:33:31,420
And restore could run it.
638
00:33:31,420 --> 00:33:37,280
If we use pg_restore -j
8, for example, 8 parallel drops.
639
00:33:37,920 --> 00:33:43,160
In this case, why are we not recalculating
statistics?
640
00:33:44,100 --> 00:33:48,720
Right now, everyone who deals with
some automation, for example,
641
00:33:48,720 --> 00:33:52,200
for DBLab Engine, we need it when
provisioning is logical.
642
00:33:52,200 --> 00:33:55,440
So it's dump restore and we need
to move as fast as possible
643
00:33:55,440 --> 00:34:00,180
multiple jobs, multiple workers
for dump, multiple workers for
644
00:34:00,180 --> 00:34:00,680
restore.
645
00:34:01,100 --> 00:34:05,920
And then of course, vacuumdb,
in this case, --analyze
646
00:34:05,920 --> 00:34:10,840
to have vacuum as well and also
-j, but People forget about
647
00:34:10,840 --> 00:34:14,340
this and forget forgetting means
like you expect it will be fine,
648
00:34:14,340 --> 00:34:20,220
but if you don't run analyze yourself
ideally in multiple threads
649
00:34:20,220 --> 00:34:23,400
and multiple workers using multiple
workers in this case performance
650
00:34:23,400 --> 00:34:28,200
will be very bad and people start
thinking oh Postgres is slow
651
00:34:30,040 --> 00:34:33,540
have you heard about this Like
I restore from dump and it's slow.
652
00:34:35,140 --> 00:34:39,400
Michael: I have to admit I haven't
heard that complaint, no.
653
00:34:40,860 --> 00:34:46,240
Nikolay: Okay, I'm hearing it and
I'm doing it myself almost
654
00:34:46,240 --> 00:34:47,060
20 years.
655
00:34:47,088 --> 00:34:49,172
I know about this.
656
00:34:49,200 --> 00:34:51,500
Michael: No, I'm not saying it
doesn't exist, I just haven't,
657
00:34:51,500 --> 00:34:54,880
you know, maybe it's just not the
category of problem maybe what
658
00:34:54,880 --> 00:34:58,940
happens when people experience
that is they don't noticed online
659
00:34:59,380 --> 00:35:02,860
or I didn't know it's or they don't
post they just go try something
660
00:35:02,860 --> 00:35:03,360
else
661
00:35:03,520 --> 00:35:07,120
Nikolay: yeah it's it's not a big
deal if you restore, and it's
662
00:35:07,120 --> 00:35:10,760
working, and then you want to deal
with it 1 day later.
663
00:35:10,760 --> 00:35:14,120
Because probably, likely, autovacuum
will do it.
664
00:35:14,600 --> 00:35:18,620
But If you expect to run fast queries
immediately after restoration
665
00:35:18,920 --> 00:35:24,000
from a dump, I'm talking about
something which is 100% very well
666
00:35:24,000 --> 00:35:27,940
known to everyone, who is at least
a little bit experienced.
667
00:35:28,700 --> 00:35:32,000
But it doesn't make it non-gotcha,
it's still gotcha.
668
00:35:34,360 --> 00:35:39,440
That pg_restore doesn't calculate
statistics and doesn't run Vacuum.
669
00:35:42,040 --> 00:35:44,620
Michael: I mean it's quite remarkable,
I know we could go on
670
00:35:44,620 --> 00:35:47,960
with this list for quite a while
and there's like varying levels
671
00:35:47,960 --> 00:35:53,000
of gotchas but I like that I like
the list you sent me how how
672
00:35:53,000 --> 00:35:57,840
short and brief it is and I I really
think for many other databases
673
00:35:58,320 --> 00:36:02,760
it would be a much much longer
list I listened to well you're
674
00:36:02,760 --> 00:36:06,260
listening to your experience of
you know a couple of days of
675
00:36:06,260 --> 00:36:10,840
trying to use MySQL at the beginning,
and recently I was listening
676
00:36:10,840 --> 00:36:14,820
to an interview with Tom Lane,
and he mentioned, like, checking
677
00:36:14,820 --> 00:36:19,540
out MySQL at the beginning and
not liking a few things about
678
00:36:19,540 --> 00:36:19,940
it.
679
00:36:19,940 --> 00:36:22,100
And that feels like a really common
experience.
680
00:36:22,540 --> 00:36:25,920
And having worked with commercial
databases like SQL Server and
681
00:36:25,920 --> 00:36:32,080
Oracle, that often move much faster
in terms of adding features
682
00:36:32,080 --> 00:36:33,940
that enterprise clients really
want.
683
00:36:34,120 --> 00:36:39,240
There's just so many more gotchas
that result from processes
684
00:36:39,360 --> 00:36:39,800
like that.
685
00:36:39,800 --> 00:36:41,120
So I imagine I could be wrong.
686
00:36:41,120 --> 00:36:44,620
Nikolay: I think Postgres has lots
of gotchas, lots of them.
687
00:36:45,300 --> 00:36:47,580
Michael: But compared to other
relational databases?
688
00:36:48,340 --> 00:36:51,360
Nikolay: Yeah, it's a complex system,
all complex systems have
689
00:36:51,360 --> 00:36:52,280
a lot of gotchas.
690
00:36:52,600 --> 00:36:57,920
Some of them are just very niche,
very narrow and deep and so
691
00:36:57,920 --> 00:36:58,420
on.
692
00:36:58,940 --> 00:37:01,760
Some of them are like nobody knows
about them.
693
00:37:01,760 --> 00:37:04,900
For example, SELECT INTO.
694
00:37:06,820 --> 00:37:09,740
This is what you can write inside
PL/pgSQL, right?
695
00:37:10,080 --> 00:37:15,140
So you write a FUNCTION or TRIGGER,
and you write SELECT INTO
696
00:37:15,300 --> 00:37:17,560
some variable, blah, blah, blah.
697
00:37:17,560 --> 00:37:20,860
And then you think, OK, this SELECT
is like maybe it's complex
698
00:37:20,860 --> 00:37:21,760
SELECT, actually.
699
00:37:22,380 --> 00:37:26,040
Subselect or, I don't know, joins
and so on.
700
00:37:26,040 --> 00:37:29,360
But in the end, you have a scalar
or something, and you select
701
00:37:29,600 --> 00:37:31,680
into a variable inside PL/pgSQL.
702
00:37:31,800 --> 00:37:34,240
And then you think, I want to understand
the plan, or I want
703
00:37:34,240 --> 00:37:38,940
to just run it and try to troubleshoot,
and you put it to psql,
704
00:37:39,840 --> 00:37:40,840
what will happen?
705
00:37:40,840 --> 00:37:45,860
If you put SELECT INTO blah blah
blah, and then some regular
706
00:37:45,920 --> 00:37:49,380
select from where order by you
know
707
00:37:49,380 --> 00:37:52,620
Michael: just an error no what
happens
708
00:37:52,900 --> 00:37:54,240
Nikolay: it will create a table
709
00:37:56,120 --> 00:37:57,120
Michael: oh okay yeah
710
00:37:57,120 --> 00:38:01,360
Nikolay: go select into is 1 of
your lights it's great table
711
00:38:02,420 --> 00:38:06,140
This I think just should be removed,
like completely removed,
712
00:38:06,380 --> 00:38:06,880
deprecated.
713
00:38:07,900 --> 00:38:10,300
Select into creating a table, it's
a DDL.
714
00:38:15,140 --> 00:38:17,580
This is definitely a gotcha for
everyone.
715
00:38:18,840 --> 00:38:21,540
Michael: Yes, imagine all those
parsers that just...
716
00:38:21,820 --> 00:38:25,240
I know this is flawed for multiple
reasons, but look at any query
717
00:38:25,240 --> 00:38:28,140
that starts with SELECT, and like,
oh, it's a read query.
718
00:38:28,200 --> 00:38:32,780
Nikolay: Well, yeah, usually we
say SELECT for update is acquiring
719
00:38:32,860 --> 00:38:35,280
a heavy lock, blocking all writes.
720
00:38:36,220 --> 00:38:42,300
Yes, blocking all writes on these
rows, particular rows, not
721
00:38:42,380 --> 00:38:43,280
the whole table.
722
00:38:43,620 --> 00:38:46,780
But Select can create a table.
723
00:38:47,180 --> 00:38:49,700
And we have create table as select
already.
724
00:38:49,840 --> 00:38:53,180
And this is what all people use,
I think, who need it.
725
00:38:53,600 --> 00:38:59,440
So select into probably just some
very old stuff that could be
726
00:38:59,440 --> 00:39:01,080
potentially removed, I think.
727
00:39:01,400 --> 00:39:03,280
Michael: If it's not in the SQL
standard
728
00:39:05,200 --> 00:39:09,560
Nikolay: oh it's a good point if
it is no chances right
729
00:39:09,780 --> 00:39:13,700
Michael: yeah well probably not
no cool anything else you want
730
00:39:13,700 --> 00:39:14,840
to make sure we covered
731
00:39:15,420 --> 00:39:18,480
Nikolay: maybe it's enough I think
we covered less than 1% of
732
00:39:18,480 --> 00:39:19,620
what we could.
733
00:39:19,700 --> 00:39:20,740
This is my feeling.
734
00:39:20,740 --> 00:39:21,640
What do you think?
735
00:39:22,240 --> 00:39:25,560
Michael: Well, it depends what
you count as gotchas and how niche
736
00:39:25,560 --> 00:39:26,640
you're willing to go.
737
00:39:26,640 --> 00:39:29,640
It turns out the more you know,
the more gotchas you know about,
738
00:39:29,640 --> 00:39:30,280
I guess.
739
00:39:31,220 --> 00:39:34,100
Nikolay: So I could not resist
and I checked the documentation.
740
00:39:34,640 --> 00:39:37,940
SQL standard uses SELECT INTO to
represent selecting values into
741
00:39:37,940 --> 00:39:43,380
a scalar variable of a host program
rather than creating a new
742
00:39:43,380 --> 00:39:43,700
table.
743
00:39:43,700 --> 00:39:47,220
It's definitely Postgresism and
I think it should be removed.
744
00:39:47,720 --> 00:39:49,340
Michael: Okay, cool, good 1.
745
00:39:50,500 --> 00:39:53,220
Nikolay: Good, okay, good chat,
maybe some kind of entertaining
746
00:39:53,240 --> 00:39:56,100
more than useful, but I enjoyed
it.
747
00:39:56,100 --> 00:39:56,820
I enjoyed it.
748
00:39:56,820 --> 00:39:57,540
Michael: Yeah, absolutely.
749
00:39:58,320 --> 00:40:02,660
And feel free to send us any more
that you've got or that you
750
00:40:02,660 --> 00:40:05,900
Nikolay: Maybe we should think
about specific areas like we did
751
00:40:05,900 --> 00:40:11,740
for for now and maybe we should
explore some areas from this
752
00:40:11,740 --> 00:40:13,320
perspective what can be unexpected
753
00:40:14,340 --> 00:40:16,680
Michael: Yeah, for sure. Thanks
so much.
754
00:40:16,680 --> 00:40:17,220
Nikolay.
755
00:40:17,220 --> 00:40:18,180
Catch you next week.
756
00:40:18,180 --> 00:40:18,240
Nikolay: Thank you.
757
00:40:18,240 --> 00:40:18,560
You too.
00:00:00,060 --> 00:00:02,600
Michael: Hello and welcome to PostgresFM, a weekly show about
2
00:00:02,600 --> 00:00:03,540
all things PostgreSQL.
3
00:00:03,740 --> 00:00:05,580
I am Michael, founder of pgMustard.
4
00:00:05,680 --> 00:00:07,640
This is Nikolay, founder of Postgres.AI.
5
00:00:07,640 --> 00:00:08,460
Hello Nikolay.
6
00:00:08,940 --> 00:00:10,300
Nikolay: Hi Michael, how are you?
7
00:00:10,860 --> 00:00:12,280
Michael: I'm good, how are you?
8
00:00:12,560 --> 00:00:13,280
Nikolay: Very good.
9
00:00:13,580 --> 00:00:15,660
Let's talk about some crazy stuff.
10
00:00:16,160 --> 00:00:20,040
Michael: I chose the topic this week and it was a listener suggestion,
11
00:00:20,280 --> 00:00:24,020
it's Postgres gotchas and we've had a short discussion before
12
00:00:24,020 --> 00:00:26,700
this call about what we should even count as gotchas.
13
00:00:27,400 --> 00:00:30,620
So how do you define it, what do you think of when you hear Postgres
14
00:00:30,620 --> 00:00:31,120
gotcha?
15
00:00:31,580 --> 00:00:35,160
Nikolay: Well if you for example run, I don't know, like SELECT
16
00:00:35,360 --> 00:00:39,980
5/2, what will you get?
17
00:00:41,580 --> 00:00:43,220
Michael: Is this like data type casting?
18
00:00:43,740 --> 00:00:47,000
Nikolay: Right right well it's not Postgres related I think it's
19
00:00:47,040 --> 00:00:51,140
SQL related but still what do you expect to get?
20
00:00:51,180 --> 00:00:54,560
Normal person would expect 2.5, right?
21
00:00:55,260 --> 00:00:58,300
But you will get, and I just double checked because I always...
22
00:00:59,220 --> 00:01:02,260
I'm always like thinking, am I right?
23
00:01:02,580 --> 00:01:05,580
So you're getting 2, so 5.
24
00:01:05,940 --> 00:01:08,740
Michael: 0 wow, I actually thought it was going to be 3.
25
00:01:13,700 --> 00:01:15,700
Nikolay: We probably have 2 things to discuss here.
26
00:01:15,700 --> 00:01:19,560
First is of course integer and integer, right?
27
00:01:19,560 --> 00:01:23,000
Results should be integer, so there's no decimal, there's no
28
00:01:23,000 --> 00:01:24,820
point here, there's no dot.
29
00:01:25,280 --> 00:01:27,680
And, but 2 or 3?
30
00:01:29,340 --> 00:01:31,600
Rounding or flooring, right?
31
00:01:31,640 --> 00:01:35,280
Michael: Yeah, because like rounding conventions at least where
32
00:01:35,280 --> 00:01:40,600
I grew up we generally round up from 0.5 but not clearly not
33
00:01:40,600 --> 00:01:45,560
here anyway I like this almost this definition which is something
34
00:01:45,860 --> 00:01:50,820
where you may expect 1 outcome but a different outcome happens.
35
00:01:50,860 --> 00:01:51,360
That's
36
00:01:51,560 --> 00:01:54,100
Nikolay: unexpected behavior basically in other words.
37
00:01:54,860 --> 00:01:57,900
Michael: Yeah, especially unexpected if you haven't read the
38
00:01:57,900 --> 00:01:58,400
documentation.
39
00:02:00,640 --> 00:02:04,740
Nikolay: Sometimes documentation also wrong or misleading or
40
00:02:05,020 --> 00:02:07,360
sometimes it's just confusing.
41
00:02:08,540 --> 00:02:09,220
Michael: Yeah fair enough.
42
00:02:09,220 --> 00:02:12,160
I just wanted an excuse to list a few things that are made very
43
00:02:12,160 --> 00:02:16,020
clear in the documentation, but I know people get confused by
44
00:02:16,020 --> 00:02:17,940
it because often they don't read that.
45
00:02:18,820 --> 00:02:22,280
Nikolay: You know, I'm curious, I actually found a good example,
46
00:02:22,280 --> 00:02:22,540
right?
47
00:02:22,540 --> 00:02:25,680
It's like probably a gotcha SQL, not Postgres, because
48
00:02:25,680 --> 00:02:30,040
I think it's maybe defined by standard, but, and this is super
49
00:02:30,040 --> 00:02:34,940
basic, but I'm wondering why indeed it's 2 not 3 because if you
50
00:02:34,940 --> 00:02:39,440
for example I what I did I double check of course I checked floor
51
00:02:39,480 --> 00:02:44,060
and round functions and of course
as expected floor I converted
52
00:02:44,060 --> 00:02:47,780
5 to numeric or just just to
53
00:02:47,780 --> 00:02:49,320
Michael: last with the colon colon
54
00:02:49,360 --> 00:02:52,360
Nikolay: you're right right so
the result is also numeric and
55
00:02:52,360 --> 00:02:59,480
then we have of course 2.500 like
so but then I checked floor
56
00:02:59,480 --> 00:03:03,800
gives me 2 round gives me 3 is
expected because 2.5 means like
57
00:03:03,800 --> 00:03:08,800
we need if you're rounding according
to their names for these
58
00:03:08,800 --> 00:03:09,860
rules, I don't remember, right?
59
00:03:09,860 --> 00:03:14,700
But usually 5 you go up, 4 you
go down, right?
60
00:03:15,540 --> 00:03:18,660
And it gives 3, Round gives 3.
61
00:03:18,660 --> 00:03:26,100
But I was thinking, okay, why 5
slash 2 integer and integer gives
62
00:03:26,100 --> 00:03:27,320
2 and not 3?
63
00:03:27,400 --> 00:03:29,200
Maybe because it uses 4.
64
00:03:30,180 --> 00:03:34,120
We could check the source code,
of course, but the next thing
65
00:03:34,120 --> 00:03:36,300
I checked is the final thing I
checked.
66
00:03:36,740 --> 00:03:40,160
5 converted to numeric slash 2
gives you 2.5.
67
00:03:40,260 --> 00:03:42,040
And then you convert back to int,
right?
68
00:03:42,040 --> 00:03:43,860
Again, double colon int.
69
00:03:44,380 --> 00:03:45,600
It gives 3.
70
00:03:46,300 --> 00:03:46,800
Amazing.
71
00:03:50,440 --> 00:03:55,940
So how come we have 2 if we don't
use type conversion, explicit
72
00:03:55,960 --> 00:03:57,320
type conversion at all?
73
00:03:58,080 --> 00:03:58,760
Any ideas?
74
00:03:59,760 --> 00:04:01,580
It was good gotcha to find.
75
00:04:01,960 --> 00:04:04,200
I always knew that it's 2 because
I demonstrated.
76
00:04:04,200 --> 00:04:06,920
I just didn't think like why.
77
00:04:07,500 --> 00:04:08,600
Now I think why.
78
00:04:08,940 --> 00:04:09,940
I have questions.
79
00:04:10,240 --> 00:04:13,780
Maybe there is some good explanation,
simple, but I can see easily
80
00:04:13,780 --> 00:04:15,040
that it can be gotcha.
81
00:04:15,480 --> 00:04:17,180
It can be considered gotcha.
82
00:04:17,860 --> 00:04:20,420
And let's agree on 1 more thing.
83
00:04:20,740 --> 00:04:22,400
We don't talk about nulls today.
84
00:04:23,920 --> 00:04:26,520
Michael: I actually did have them
on my list, but not to go into
85
00:04:26,520 --> 00:04:27,020
detail.
86
00:04:27,260 --> 00:04:30,580
Nikolay: We had a whole episode,
and this whole episode is telling
87
00:04:30,580 --> 00:04:36,000
like, nulls are surrounded by gotchas
both like standard defined
88
00:04:36,600 --> 00:04:41,040
standard produce and like global
SQL gotchas and local Postgres
89
00:04:41,040 --> 00:04:45,060
related gotchas as well for example
when you go to a race right
90
00:04:45,060 --> 00:04:49,460
Let's just exclude it because it's
a whole massive area.
91
00:04:49,900 --> 00:04:51,260
Michael: I think that's very sensible.
92
00:04:51,600 --> 00:04:53,800
And if you haven't listened to
that episode, I'll link it up
93
00:04:53,800 --> 00:04:54,740
in the show notes.
94
00:04:55,240 --> 00:04:58,940
Nikolay: Consider it to be fully
included here.
95
00:04:59,140 --> 00:04:59,640
Yeah.
96
00:05:00,080 --> 00:05:00,580
Right.
97
00:05:00,920 --> 00:05:03,680
Okay, let's maybe switch to your
ideas.
98
00:05:03,680 --> 00:05:06,400
I know you have a list, pull something
from it.
99
00:05:06,460 --> 00:05:09,940
Michael: Yeah, I've got a few that
I wanted to make sure we mentioned.
100
00:05:10,080 --> 00:05:14,180
I'm gonna start with some ones
I consider more beginner level
101
00:05:14,540 --> 00:05:17,580
gotchas and then maybe we can work
our way up a little bit.
102
00:05:17,620 --> 00:05:23,880
The first 1 I had is 1 I see all
the time in kind of forums every
103
00:05:23,880 --> 00:05:28,260
now and again on Reddit, quite
a few times in like the Postgres
104
00:05:28,260 --> 00:05:30,520
Slack, a few up on a few mailing
lists.
105
00:05:31,220 --> 00:05:35,760
People getting confused or annoyed
when they name objects in
106
00:05:35,760 --> 00:05:42,380
Postgres and they use mixed case,
so maybe camel case or some
107
00:05:42,380 --> 00:05:47,220
way of naming a table like for
example like a two-worded name
108
00:05:47,380 --> 00:05:50,640
and using capital letters for the
2 names and no space in between
109
00:05:50,640 --> 00:05:53,680
them that kind of thing and then
realizing the table is not called
110
00:05:53,680 --> 00:05:58,040
that behind the scenes and it's
all lowercase and if they've
111
00:05:58,120 --> 00:06:02,820
used an ORM or something to name
these at the ORM often puts
112
00:06:03,420 --> 00:06:07,500
quotes around those case names
so then they become cased in the
113
00:06:07,500 --> 00:06:08,000
database.
114
00:06:08,420 --> 00:06:13,300
So I think this whole area is like
a 1 big gotcha for beginners
115
00:06:14,440 --> 00:06:18,060
and the people who set up the schema
in the first place, if they
116
00:06:18,340 --> 00:06:22,340
do something like this, if they
don't just use lowercase object
117
00:06:22,340 --> 00:06:26,140
names, can really make life a bit
painful for everybody that
118
00:06:26,140 --> 00:06:29,680
comes after them having to double
quote everything.
119
00:06:30,180 --> 00:06:33,840
Nikolay: Yeah, especially Java
people like it.
120
00:06:34,300 --> 00:06:35,040
Michael: Oh, really?
121
00:06:35,140 --> 00:06:37,180
Nikolay: Yeah, I noticed.
122
00:06:37,940 --> 00:06:43,180
Usually, if we have a new customer
and we start discussing their
123
00:06:43,180 --> 00:06:47,780
database, we see some camel-stack-cased
table names.
124
00:06:47,780 --> 00:06:52,440
And I think, I'm already thinking,
are you Java guys?
125
00:06:52,440 --> 00:06:53,540
Yeah, there are.
126
00:06:53,940 --> 00:06:56,260
Usually, not always, but usually
so.
127
00:06:56,440 --> 00:07:00,520
And my previous startup was using
it camel-style.
128
00:07:03,420 --> 00:07:06,240
And we just decided to keep it
as is because it was inherited
129
00:07:06,260 --> 00:07:06,960
from some...
130
00:07:07,200 --> 00:07:09,260
I think it was in MySQL originally.
131
00:07:09,900 --> 00:07:14,020
Like, we started very lean, so
we borrowed some schema from some
132
00:07:14,020 --> 00:07:15,080
open source project.
133
00:07:15,480 --> 00:07:16,580
And it had it.
134
00:07:16,640 --> 00:07:20,680
And I just, knowing this very well,
I still decided to keep it.
135
00:07:20,680 --> 00:07:22,960
But the problem with double quotes
is not like...
136
00:07:22,960 --> 00:07:24,640
It's not a big deal, but when...
137
00:07:24,720 --> 00:07:27,760
You know what comes with quotes
usually, right?
138
00:07:28,580 --> 00:07:29,080
Backslashes.
139
00:07:30,480 --> 00:07:31,180
Michael: Oh, having...
140
00:07:31,180 --> 00:07:32,800
Yeah, having to escape them.
141
00:07:32,900 --> 00:07:34,920
Nikolay: Yeah, and you cannot escape
from this.
142
00:07:34,920 --> 00:07:37,400
You need to use it to escape.
143
00:07:37,660 --> 00:07:37,940
Right?
144
00:07:37,940 --> 00:07:39,980
I mean, yeah.
145
00:07:40,080 --> 00:07:44,780
And backslashes, if you have automation
in many languages, like
146
00:07:44,780 --> 00:07:46,100
sometimes it's nightmare.
147
00:07:46,960 --> 00:07:50,580
Right now we have an ongoing problem
with backslashes and escaping
148
00:07:51,040 --> 00:07:54,360
of quotes and double quotes in
our AI system.
149
00:07:54,440 --> 00:07:57,900
It's a combination of Python and
Bash actually.
150
00:07:58,520 --> 00:08:00,520
And yeah, it drives us crazy sometimes.
151
00:08:00,700 --> 00:08:03,220
We solved some problems, but not
all of them still.
152
00:08:03,580 --> 00:08:07,660
And when Yi needs to talk about
your table names and you give
153
00:08:07,660 --> 00:08:11,420
the schema with double quotes,
and then you want to experiment
154
00:08:11,480 --> 00:08:16,620
in DBLab to find some plans or
something, it sometimes is broken
155
00:08:16,620 --> 00:08:21,160
just because of problems with backslashes
and passing these pieces
156
00:08:21,220 --> 00:08:22,700
between different components.
157
00:08:24,060 --> 00:08:27,040
I would prefer keeping everything
lowercase, that's it.
158
00:08:27,040 --> 00:08:27,680
In post-process.
159
00:08:27,800 --> 00:08:28,300
Right?
160
00:08:29,440 --> 00:08:30,600
Snake style, right?
161
00:08:31,100 --> 00:08:31,600
Michael: Yeah.
162
00:08:31,620 --> 00:08:32,220
Nikolay: And that's it.
163
00:08:32,220 --> 00:08:33,640
And just forget about it.
164
00:08:35,220 --> 00:08:36,000
Michael: Yeah, plays.
165
00:08:36,140 --> 00:08:37,040
Pay for plays.
166
00:08:37,500 --> 00:08:40,440
Nikolay: I wanted to mention the
list of gotchas I saw actually
167
00:08:40,440 --> 00:08:44,960
this is like as we discussed right
before this recording I didn't
168
00:08:44,960 --> 00:08:49,780
know this the word gotcha until
2006 or 2007 and this is exactly
169
00:08:49,780 --> 00:08:52,860
when this list was published.
170
00:08:53,220 --> 00:08:56,500
And I remember we had 2 lists,
1 for Postgres and 1 for MySQL.
171
00:08:57,180 --> 00:09:00,840
For MySQL, it's outside of scope,
right?
172
00:09:01,100 --> 00:09:03,640
Again, even more than nulls.
173
00:09:03,820 --> 00:09:08,080
But this list, I'm looking at it
right now and I'm very first
174
00:09:08,080 --> 00:09:15,940
of all it's survived how many years
like it's insane 18 17 years.
175
00:09:16,860 --> 00:09:17,300
Wow.
176
00:09:17,300 --> 00:09:21,600
And I'm looking at this, it's quite
short list at SQLinfo.de,
177
00:09:23,100 --> 00:09:23,600
right?
178
00:09:24,480 --> 00:09:27,040
And almost everything is crossed
out.
179
00:09:27,980 --> 00:09:28,480
Good.
180
00:09:29,240 --> 00:09:31,640
Michael: Yeah, this I've never
seen this page before.
181
00:09:31,640 --> 00:09:32,740
This is really cool.
182
00:09:32,980 --> 00:09:36,980
And yeah, maybe more than half
of them have been cost
183
00:09:36,980 --> 00:09:37,480
down.
184
00:09:40,080 --> 00:09:41,760
Nikolay: Yeah, account start is very slow.
185
00:09:42,660 --> 00:09:44,120
Let's just laugh at it.
186
00:09:45,060 --> 00:09:47,140
Michael: Oh, and we had an episode
on that, right?
187
00:09:47,540 --> 00:09:48,840
Nikolay: I think we should.
188
00:09:48,840 --> 00:09:51,580
Unicode means UTF-8 left as well.
189
00:09:51,580 --> 00:09:52,400
Well, yeah.
190
00:09:53,740 --> 00:09:56,840
Yeah, constraint checking is interesting,
but maybe let's not
191
00:09:56,840 --> 00:09:57,620
dive into it.
192
00:09:57,620 --> 00:10:00,720
And finally, I wanted to highlight
what else is left.
193
00:10:00,720 --> 00:10:01,660
Only 4 items.
194
00:10:01,880 --> 00:10:02,880
5, Okay, 5.
195
00:10:02,880 --> 00:10:06,960
But 1 of them is upgrades, upgrades,
related stuff.
196
00:10:07,200 --> 00:10:12,100
And I was discussing on Twitter,
on X, I was discussing this
197
00:10:12,100 --> 00:10:13,940
topic over the last few days.
198
00:10:14,180 --> 00:10:20,260
I just checked documentation of
3 major managed Postgres providers.
199
00:10:20,840 --> 00:10:24,300
So RDS, Cloud SQL, and Azure Postgres.
200
00:10:25,240 --> 00:10:30,460
I saw that all of them say, okay,
upgrade is done, major upgrade.
201
00:10:30,600 --> 00:10:33,900
Now it's time, don't forget, now it's time to run ANALYZE.
202
00:10:33,900 --> 00:10:38,300
And they say ANALYZE like they give you a snippet, ANALYZE semicolon,
203
00:10:38,400 --> 00:10:41,220
which means it's single-threaded as well.
204
00:10:41,520 --> 00:10:43,860
First of all, they don't include it.
205
00:10:43,860 --> 00:10:46,800
None of them include it into automation.
206
00:10:47,620 --> 00:10:50,460
But we don't talk about managed Postgres providers today, we
207
00:10:50,460 --> 00:10:51,320
talk about Postgres.
208
00:10:52,500 --> 00:10:58,180
My question is why pg_upgrade does not include...
209
00:10:58,180 --> 00:11:01,220
It prints only some text in there, but who reads the text?
210
00:11:01,440 --> 00:11:05,140
Many people will say, for those who don't read, it's on their
211
00:11:05,140 --> 00:11:06,520
shoulders, it's their problem.
212
00:11:06,820 --> 00:11:08,460
But I cannot agree with it.
213
00:11:09,600 --> 00:11:14,340
Michael: I could even make a case for the gotcha here is that
214
00:11:14,340 --> 00:11:17,460
statistics are not preserved on major upgrade.
215
00:11:17,800 --> 00:11:23,860
I think if you asked somebody that didn't know whether they would
216
00:11:23,860 --> 00:11:27,900
expect the statistics to be wiped or to be preserved, somebody
217
00:11:27,900 --> 00:11:31,080
maybe doesn't understand how difficult it might be or like the
218
00:11:31,080 --> 00:11:34,640
catalog, like the reasons why they're not preserved, you'd be
219
00:11:34,640 --> 00:11:38,600
forgiven to think like in a in the most advanced open source
220
00:11:38,600 --> 00:11:42,600
database that that might be that might happen even though it's
221
00:11:42,600 --> 00:11:45,110
complicated and I think there has been some work.
222
00:11:45,110 --> 00:11:45,290
It should
223
00:11:45,290 --> 00:11:47,660
Nikolay: happen and Everyone agrees on it, just not solved.
224
00:11:48,280 --> 00:11:50,520
Michael: Yeah, so I think that's the main gotcha.
225
00:11:50,540 --> 00:11:54,280
And then, obviously, the solution at the moment is we need to
226
00:11:54,280 --> 00:11:55,060
want to analyze.
227
00:11:55,520 --> 00:11:56,980
Nikolay: Yeah, let's unwrap a little bit.
228
00:11:56,980 --> 00:11:59,860
So we talk about statistics, which is required for a planner
229
00:11:59,860 --> 00:12:01,720
to make right decisions.
230
00:12:02,300 --> 00:12:05,640
If the database is not trivial, like containing like 100 rows
231
00:12:05,640 --> 00:12:06,960
in each table, that's it.
232
00:12:06,960 --> 00:12:08,860
If it's not trivial, we need statistics.
233
00:12:09,520 --> 00:12:11,440
Without statistics, database is slow.
234
00:12:12,700 --> 00:12:17,680
And what happens, I think in a zillion cases, it happens.
235
00:12:17,920 --> 00:12:21,420
Like It's hard to estimate, but when I posted this tweet, I received
236
00:12:21,420 --> 00:12:29,120
several replies immediately that, oh, we got beaten by this badly.
237
00:12:31,400 --> 00:12:35,740
Some person wrote, we learned this hard way, obviously.
238
00:12:36,260 --> 00:12:38,460
And this is like what happens all the time.
239
00:12:38,460 --> 00:12:45,800
And recently, during summer, we also like failed to help a customer
240
00:12:45,800 --> 00:12:46,160
with it.
241
00:12:46,160 --> 00:12:48,580
Like we discussed major upgrades, but we...
242
00:12:48,680 --> 00:12:52,040
From now on, I'm pointing out, like, don't forget, don't forget,
243
00:12:52,040 --> 00:12:52,780
don't forget.
244
00:12:53,100 --> 00:12:55,960
But we discussed upgrades, we discussed some, like, complicated
245
00:12:56,040 --> 00:13:00,520
stuff, and then they went to upgrade, and next Monday they say,
246
00:13:00,520 --> 00:13:02,160
oh, today they are in Europe.
247
00:13:02,160 --> 00:13:06,220
So in the morning, it was already by the evening in Europe.
248
00:13:06,220 --> 00:13:10,380
And they say, we had nasty database incidents today because guess
249
00:13:10,380 --> 00:13:10,760
what?
250
00:13:10,760 --> 00:13:13,120
We forgot to run ANALYZE.
251
00:13:14,240 --> 00:13:18,480
And this is huge gotcha not only
by managed services, they could
252
00:13:18,480 --> 00:13:21,520
do it but I don't care too much
about them.
253
00:13:21,660 --> 00:13:23,040
This is a gotcha of pg_upgrade.
254
00:13:23,240 --> 00:13:26,140
I think it's very well understood,
there is work in progress
255
00:13:26,200 --> 00:13:31,980
for export and import of statistics,
like quite a long thread
256
00:13:31,980 --> 00:13:34,500
in hackers already and commitfest
entry.
257
00:13:34,900 --> 00:13:38,340
It's still not done, but there
is hope.
258
00:13:39,960 --> 00:13:41,400
There is 1 more thing here.
259
00:13:41,400 --> 00:13:44,940
So I think pg_upgrade definitely
should have it.
260
00:13:45,060 --> 00:13:48,220
And also pg_restore, but let's return
to it in a few minutes.
261
00:13:50,280 --> 00:13:54,780
So when someone says you need to
recalculate statistics yourself
262
00:13:55,240 --> 00:13:57,140
after upgrade, pg_upgrade.
263
00:13:58,920 --> 00:14:01,820
I definitely would expect pg_upgrade
to do this for sure.
264
00:14:02,020 --> 00:14:04,220
But okay, it just prints do it.
265
00:14:04,340 --> 00:14:07,420
pg_upgrade prints vacuumdb, I think,
no?
266
00:14:07,420 --> 00:14:10,280
Or in stages, or like vacuumdb
in stages.
267
00:14:10,280 --> 00:14:14,060
It has this vacuumdb analyze only
in stages.
268
00:14:14,060 --> 00:14:14,640
2 options.
269
00:14:14,640 --> 00:14:16,940
Analyze only means no vacuum and
just Analyze.
270
00:14:17,320 --> 00:14:23,420
In stages means first it gets only
1 bucket for each table, for
271
00:14:23,420 --> 00:14:25,320
each column actually, right?
272
00:14:25,520 --> 00:14:29,480
And then some midway until your...
273
00:14:29,480 --> 00:14:34,600
And then default_statistics_target
or individual column based
274
00:14:34,600 --> 00:14:36,180
setting for each column.
275
00:14:36,340 --> 00:14:39,780
default_statistics_target by default
is 100, 100 buckets.
276
00:14:40,560 --> 00:14:46,580
This is what defines how much Postgres
planner will know about
277
00:14:46,680 --> 00:14:48,780
distribution in each column, right?
278
00:14:48,780 --> 00:14:52,040
And it can be adjusted globally
or for individual columns.
279
00:14:52,840 --> 00:14:53,260
But...
280
00:14:53,260 --> 00:14:56,100
Michael: Almost like a sample size,
like the sample size increases
281
00:14:56,260 --> 00:14:57,320
of what it samples.
282
00:14:57,320 --> 00:14:57,560
Nikolay: Yeah.
283
00:14:57,560 --> 00:15:01,200
And I quickly realized this is
not what you want to do in OLTP,
284
00:15:01,220 --> 00:15:06,560
because in OLTP, you either include
Analyze inside maintenance
285
00:15:06,580 --> 00:15:06,980
window.
286
00:15:06,980 --> 00:15:11,180
In this case, there is no sense
to run it 3 times, or you just...
287
00:15:12,880 --> 00:15:14,940
Or that's it, there is only option
actually.
288
00:15:15,180 --> 00:15:19,440
Like, I don't trust in opening
gates with weak statistics.
289
00:15:19,680 --> 00:15:22,220
It will be like unpredictable again
incident.
290
00:15:23,080 --> 00:15:23,580
Michael: Yeah.
291
00:15:24,140 --> 00:15:24,820
Yeah, risky.
292
00:15:24,860 --> 00:15:28,080
But this feels like a topic that
might be worth a deeper dive.
293
00:15:28,440 --> 00:15:28,940
Nikolay: Right.
294
00:15:29,280 --> 00:15:29,640
Right.
295
00:15:29,640 --> 00:15:30,040
Right.
296
00:15:30,040 --> 00:15:34,300
I just wanted to point out that
when people say Analyze, it's
297
00:15:34,300 --> 00:15:36,640
just 1 threaded, right?
298
00:15:37,120 --> 00:15:41,420
And you expect Postgres has a lot
of parallelism implemented.
299
00:15:41,940 --> 00:15:44,440
But when you run Analyze, it's
always single threaded.
300
00:15:44,680 --> 00:15:46,800
This is unexpected as well, right?
301
00:15:47,100 --> 00:15:47,540
Michael: Good 1.
302
00:15:47,540 --> 00:15:48,520
Yeah, I like that.
303
00:15:48,520 --> 00:15:51,640
Nikolay: It's kind of gotcha inside
gotcha we have matryoshka
304
00:15:51,700 --> 00:15:54,620
style gotcha here right Russian
doll style
305
00:15:54,620 --> 00:15:57,620
Michael: what do you do you recommend
doing like just kicking
306
00:15:57,620 --> 00:16:00,540
off a bunch of queries analyzed
tape like per table
307
00:16:00,540 --> 00:16:04,380
Nikolay: I recommend partition
I recommend vacuumdb
308
00:16:04,800 --> 00:16:05,780
Michael: nice okay
309
00:16:05,920 --> 00:16:08,680
Nikolay: And speed up and go full
speed inside maintenance window.
310
00:16:08,680 --> 00:16:09,940
Again, it's a different story.
311
00:16:10,200 --> 00:16:15,780
But this vacuumdb, it has
--jobs
312
00:16:15,780 --> 00:16:17,120
or -j.
313
00:16:17,440 --> 00:16:18,300
You can define.
314
00:16:18,300 --> 00:16:22,240
And usually, we take as many courses
we have on the server and
315
00:16:22,240 --> 00:16:24,780
go full speed inside maintenance
window to achieve.
316
00:16:25,120 --> 00:16:28,200
Analyze should be quite fast if
you don't do vacuum.
317
00:16:29,160 --> 00:16:33,540
There is also gotcha a little bit
in vacuumdb options because
318
00:16:33,600 --> 00:16:37,320
vacuumdb's, --analyze
will also do vacuum.
319
00:16:37,580 --> 00:16:38,200
So there's
320
00:16:38,200 --> 00:16:39,860
Michael: also analyze only or
321
00:16:39,860 --> 00:16:41,140
Nikolay: there is analyze only.
322
00:16:41,240 --> 00:16:43,120
Yeah, but it's easy to overlook.
323
00:16:43,340 --> 00:16:45,800
There's 1 more gotcha here inside
all this.
324
00:16:46,300 --> 00:16:51,560
When people go with manual single-threaded
analyze or maybe multi-threaded
325
00:16:51,820 --> 00:16:54,120
but just explicit analyze.
326
00:16:55,080 --> 00:16:59,960
I've like quickly, it was interesting,
I was thinking someone
327
00:16:59,960 --> 00:17:01,560
said analyze can be super expensive.
328
00:17:01,560 --> 00:17:05,140
I agree, but even if it's like,
if the default_statistics_target
329
00:17:05,140 --> 00:17:06,780
is 1000, it's not that expensive.
330
00:17:06,780 --> 00:17:11,020
But back to this, can we throttle
it?
331
00:17:12,520 --> 00:17:16,100
For example, you want it to go
very slow, because autovacuum
332
00:17:16,100 --> 00:17:19,660
has autovacuum, doesn't have actually
333
00:17:19,660 --> 00:17:20,940
Michael: a vacuum_cost_delay also?
334
00:17:21,180 --> 00:17:24,060
Nikolay: Yes, there is
vacuum_cost_delay and vacuum_cost_limit.
335
00:17:25,360 --> 00:17:28,440
By default, it's off because I
think the cost limit is 0, meaning
336
00:17:28,440 --> 00:17:30,040
that it's not checked at all.
337
00:17:30,400 --> 00:17:33,720
Not cost limit, cost delay is 0,
like no check.
338
00:17:34,120 --> 00:17:38,820
But autovacuum has like mirrored
pair of these settings.
339
00:17:39,560 --> 00:17:40,460
It's throttled.
340
00:17:41,060 --> 00:17:43,520
Long story short, autovacuum is
throttled usually.
341
00:17:44,340 --> 00:17:47,440
And we actually usually fight with
our customers a little bit
342
00:17:47,440 --> 00:17:51,000
to increase this, make it even
more aggressive.
343
00:17:52,800 --> 00:17:56,040
And managed providers already did
part of that job, which is
344
00:17:56,040 --> 00:17:56,540
good.
345
00:17:56,880 --> 00:18:00,840
But if we run it manually, it's
not throttled.
346
00:18:00,840 --> 00:18:02,140
It will go full speed.
347
00:18:02,200 --> 00:18:06,960
I doubt you will need to throttle
it, but some people want, okay,
348
00:18:06,960 --> 00:18:08,040
let's throttle it.
349
00:18:08,100 --> 00:18:09,340
Question, how?
350
00:18:09,620 --> 00:18:14,440
We have a vacuum limit, vacuum_cost_limit, vacuum_cost_delay.
351
00:18:15,040 --> 00:18:19,060
We can set vacuum_cost_delay from default 0 to some point.
352
00:18:19,600 --> 00:18:23,320
But is it about Analyze, or it's only about vacuum?
353
00:18:23,320 --> 00:18:25,680
Based on just the naming, it's not about Analyze.
354
00:18:25,680 --> 00:18:27,040
It's about vacuum only.
355
00:18:28,140 --> 00:18:28,640
Michael: Right?
356
00:18:28,680 --> 00:18:29,900
Yeah, that would be my guess.
357
00:18:29,900 --> 00:18:33,780
But by the line of your questioning, I'm guessing it does affect
358
00:18:33,780 --> 00:18:34,620
your analysis.
359
00:18:34,820 --> 00:18:35,220
Nikolay: Right.
360
00:18:35,220 --> 00:18:39,620
I go to documentation and I see vacuum_cost_limit, vacuum_cost_delay,
361
00:18:39,620 --> 00:18:40,680
I think.
362
00:18:40,680 --> 00:18:41,740
And I see description.
363
00:18:41,980 --> 00:18:44,560
They talk only about vacuum, they don't talk about analysis.
364
00:18:45,040 --> 00:18:47,740
So I make conclusion, it's only about vacuum.
365
00:18:48,400 --> 00:18:53,560
But some part of my very long-term memory tells me, is it really
366
00:18:53,560 --> 00:18:54,060
so?
367
00:18:55,840 --> 00:18:59,540
And then Sadek Dusty, who helped me a lot with, and still helping
368
00:18:59,540 --> 00:19:03,240
sometimes when I write at how-to's and reviews How-To's, I'm
369
00:19:03,240 --> 00:19:06,060
very thankful for that.
370
00:19:06,880 --> 00:19:08,660
I have already 94 how-tos.
371
00:19:09,720 --> 00:19:11,660
So I wrote how-to, how-to Analyze.
372
00:19:11,930 --> 00:19:14,980
And right now I need to add this.
373
00:19:16,220 --> 00:19:19,140
vacuum_cost_limit, vacuum_cost_delay, they affect Analyze.
374
00:19:19,440 --> 00:19:23,680
If you scroll up to the beginning of section, you will see that
375
00:19:23,680 --> 00:19:26,340
it talks about both vacuum and Analyze.
376
00:19:26,800 --> 00:19:30,680
And we also, using AI, I checked source code and quickly found
377
00:19:30,680 --> 00:19:35,200
that there is a function called vacuumExec, which works with
378
00:19:35,200 --> 00:19:36,540
both vacuum and Analyze.
379
00:19:36,820 --> 00:19:40,040
And this confusion comes from source code, propagates to the
380
00:19:40,040 --> 00:19:42,480
documentation, and then to our heads, right?
381
00:19:42,560 --> 00:19:43,940
This is gotcha as well.
382
00:19:43,940 --> 00:19:47,460
So you can use vacuum_cost_limit, vacuum_cost_delay, to throttle
383
00:19:47,600 --> 00:19:51,300
Analyze, although I usually don't do it because I think we need
384
00:19:51,300 --> 00:19:52,320
to go full speed.
385
00:19:53,400 --> 00:19:55,900
Michael: And in the 1 example we're talking about here, we want
386
00:19:55,900 --> 00:19:57,280
to go faster, not slower.
387
00:19:57,660 --> 00:19:58,880
Nikolay: Right, right.
388
00:19:59,340 --> 00:20:00,560
But it's interesting, right?
389
00:20:00,660 --> 00:20:02,080
So there is some small confusion.
390
00:20:02,080 --> 00:20:04,060
I think there are many such confusions.
391
00:20:04,820 --> 00:20:06,540
But they are slowly fixed.
392
00:20:06,680 --> 00:20:08,400
They are slowly being fixed.
393
00:20:08,400 --> 00:20:10,860
Michael: Great example of 1 where the documentation
394
00:20:10,920 --> 00:20:13,760
wasn't helpful in avoiding that 1.
395
00:20:13,940 --> 00:20:14,780
So yeah, nice.
396
00:20:15,120 --> 00:20:17,960
I've got a few more beginner-friendly ones that maybe we could
397
00:20:17,960 --> 00:20:18,740
rattle through.
398
00:20:18,740 --> 00:20:23,560
I'm not even sure you'd consider them all gotchas, but 1 relates
399
00:20:27,620 --> 00:20:28,280
of places.
400
00:20:29,040 --> 00:20:30,900
I think that not only catches...
401
00:20:30,920 --> 00:20:32,660
Well, you don't think it's a gotcha?
402
00:20:33,120 --> 00:20:36,220
Nikolay: I think we should exclude
this on the same, like, we're
403
00:20:36,220 --> 00:20:41,540
using the same approach as now
because it's a huge bag of, yeah.
404
00:20:41,920 --> 00:20:42,320
Michael: Cool.
405
00:20:42,320 --> 00:20:45,080
And then another 1 that I see catch
out, well, a couple that
406
00:20:45,080 --> 00:20:48,280
I see catch out beginners, again,
maybe this is another huge
407
00:20:48,280 --> 00:20:53,400
topic, but access exclusive locks
on like just basically DDL
408
00:20:54,300 --> 00:20:58,080
blocking selects, like blocking
every, blocking inserts and like
409
00:20:58,080 --> 00:21:02,620
you know all sorts of people getting,
I think people don't realize
410
00:21:02,620 --> 00:21:05,680
because if they're working on small
databases at first, they
411
00:21:05,680 --> 00:21:09,860
start to grow in confidence of
how to do things.
412
00:21:10,080 --> 00:21:14,620
And then it only catches them out
once the startup's got really
413
00:21:14,620 --> 00:21:17,260
successful and the table's much
bigger, that kind of thing.
414
00:21:17,680 --> 00:21:23,080
Nikolay: Yeah, I think there is
some memes with Gauss distribution,
415
00:21:23,080 --> 00:21:24,500
or how it's called, right?
416
00:21:24,680 --> 00:21:25,660
Gaussian distribution.
417
00:21:25,760 --> 00:21:29,840
And first, I think schema changes
are painful.
418
00:21:31,300 --> 00:21:34,660
In the, like, schema changes are
super painful.
419
00:21:34,660 --> 00:21:36,920
They require expertise, like, beginner,
right?
420
00:21:37,040 --> 00:21:41,460
Then in the middle, you can imagine,
oh, Postgres has transactional
421
00:21:41,580 --> 00:21:44,720
DDL, everything is fine, we just
need to wrap everything into
422
00:21:44,720 --> 00:21:48,400
1 transaction and, and rely on
Postgres great capabilities of
423
00:21:48,400 --> 00:21:49,040
transactional DDL.
424
00:21:49,040 --> 00:21:50,860
Michael: Yeah, or just concurrently
and think
425
00:21:50,860 --> 00:21:53,400
Nikolay: yeah Yeah, MVCC works.
426
00:21:53,400 --> 00:21:56,680
Well, it doesn't block sell Excel
X are always working you can
427
00:21:56,680 --> 00:22:01,580
you can have a lot of Like exciting
stuff in the middle and then
428
00:22:01,580 --> 00:22:05,360
on the on the right you say post
this doesn't look was this has
429
00:22:05,360 --> 00:22:08,520
come Transactional detail, but
you cannot use it
430
00:22:09,040 --> 00:22:11,280
Michael: Well the in on the right
has to be the same as the left,
431
00:22:11,280 --> 00:22:11,720
doesn't it?
432
00:22:11,720 --> 00:22:13,040
Schema changes are painful.
433
00:22:13,380 --> 00:22:17,320
Nikolay: Yes, they are painful,
transactional DDL cannot be used
434
00:22:17,320 --> 00:22:24,380
in many, many cases, and you can
shoot your feet very well many
435
00:22:24,380 --> 00:22:24,880
times.
436
00:22:29,600 --> 00:22:31,120
So this is it, I think.
437
00:22:31,120 --> 00:22:32,940
And a lot of gotchas there.
438
00:22:33,740 --> 00:22:36,180
Michael: Another 1 that's not necessarily,
I'm not sure if this
439
00:22:36,180 --> 00:22:38,400
counts as a gotcha, I'd be interested
in your opinion but it
440
00:22:38,400 --> 00:22:41,780
does have seen it catch people
out and I guess it's a little
441
00:22:41,780 --> 00:22:45,340
bit of a foot gun and that's you
know you see the memes about
442
00:22:45,340 --> 00:22:48,620
this as well actually DELETE or
UPDATE without a WHERE clause
443
00:22:48,620 --> 00:22:52,000
and then just suddenly seeing you
know instead of 5 updated.
444
00:22:55,080 --> 00:22:57,720
But if you think like it's not
Postgres, why do you say it's
445
00:22:57,720 --> 00:22:59,120
not Postgres out of interest?
446
00:22:59,340 --> 00:23:02,360
Nikolay: Because the SQL standard
does it like defines this and
447
00:23:02,360 --> 00:23:06,540
any relational database which follows
SQL standard to some extent
448
00:23:07,120 --> 00:23:07,940
has it.
449
00:23:08,760 --> 00:23:09,260
Michael: Yeah.
450
00:23:10,140 --> 00:23:17,360
But I've seen clients catch these,
But the ones that ship, well,
451
00:23:17,360 --> 00:23:18,980
the only, the psql doesn't.
452
00:23:19,120 --> 00:23:22,480
So I don't think it's unreasonable
that Postgres does it this
453
00:23:22,480 --> 00:23:24,980
way, but I've definitely seen it
catch people out.
454
00:23:25,440 --> 00:23:26,100
Nikolay: I agree.
455
00:23:26,580 --> 00:23:27,880
Blame SQL standard.
456
00:23:28,140 --> 00:23:30,780
And there is extension to forbid
it.
457
00:23:30,780 --> 00:23:35,080
And also it was maybe 1 of the
very first hacking sessions with
458
00:23:35,080 --> 00:23:35,500
Andrei.
459
00:23:35,500 --> 00:23:43,000
We implemented some setting, GUC
setting in Postgres, which would
460
00:23:43,000 --> 00:23:48,820
allow administrators to, I don't
think error, warning, like completely
461
00:23:48,820 --> 00:23:50,380
forbid or just warn, I don't remember.
462
00:23:50,380 --> 00:23:52,220
I think error should be here.
463
00:23:52,540 --> 00:23:55,680
But it was not accepted, of course,
because I think it was discussed
464
00:23:55,680 --> 00:23:56,420
many times.
465
00:23:57,280 --> 00:23:58,480
I mean, it's possible.
466
00:23:58,940 --> 00:24:02,960
And right now, as I see, I think
PostgREST does it, right?
467
00:24:03,340 --> 00:24:03,840
PostgREST...
468
00:24:04,460 --> 00:24:05,440
Michael: Oh, does it?
469
00:24:05,640 --> 00:24:08,800
Nikolay: So usually right now it's
sold, if there is some middleware,
470
00:24:08,940 --> 00:24:09,840
it's sold there.
471
00:24:10,320 --> 00:24:15,220
So if you develop API using PostgREST,
for example, I think, Maybe
472
00:24:15,220 --> 00:24:17,220
I'm mistaken, but I think there
is a way.
473
00:24:17,320 --> 00:24:19,900
If there is no way, it's a good
idea to implement, actually.
474
00:24:19,900 --> 00:24:20,980
Michael: Yeah, it would be, yeah.
475
00:24:20,980 --> 00:24:26,820
Nikolay: Yeah, so if in URL of
your HTTP request of RESTful API,
476
00:24:27,080 --> 00:24:31,720
there is a command to run delete
without any filters, I think
477
00:24:31,720 --> 00:24:36,860
it will not do it it will not go
to progress with it so but inside
478
00:24:36,860 --> 00:24:42,380
database I don't I don't think
it's going to change right and
479
00:24:42,380 --> 00:24:44,080
we tried actually so
480
00:24:44,760 --> 00:24:48,060
Michael: yes with the well and
we tried what do you mean each
481
00:24:48,060 --> 00:24:49,120
right again
482
00:24:49,140 --> 00:24:51,900
Nikolay: we had a session yeah
we had a patch and they submitted
483
00:24:51,900 --> 00:24:56,240
it, but it was a small discussion
and chances are close to 0
484
00:24:56,240 --> 00:24:58,860
that it will be successful.
485
00:24:59,840 --> 00:25:02,060
Michael: Patch in psql or somewhere
else?
486
00:25:02,220 --> 00:25:03,340
Nikolay: No, not in psql.
487
00:25:03,580 --> 00:25:08,340
We wanted to have a Postgres
setting, GUC setting, to just
488
00:25:08,340 --> 00:25:12,840
allow administrators to forbid
unspecified, I don't remember
489
00:25:12,840 --> 00:25:16,500
how we called it, maybe unspecified
delete and update, when you
490
00:25:16,500 --> 00:25:19,020
don't have the WHERE clause at
all.
491
00:25:19,800 --> 00:25:21,120
Just like,
492
00:25:21,220 --> 00:25:21,720
Michael: cool,
493
00:25:22,060 --> 00:25:22,820
Nikolay: don't do it.
494
00:25:22,820 --> 00:25:26,260
There's a truncate for if you want
to hit right an update of
495
00:25:26,260 --> 00:25:30,900
whole table I don't know yeah but
again like right now it's in
496
00:25:30,900 --> 00:25:33,700
middleware usually sold in middle
where You can have triggers
497
00:25:33,700 --> 00:25:35,900
or something to protect yourself
if you want.
498
00:25:36,300 --> 00:25:38,960
Or this extension if you're on
self-managed Postgres.
499
00:25:38,960 --> 00:25:40,540
There is some extension for it.
500
00:25:40,760 --> 00:25:44,840
I wanted to mention 1 thing, step
back to DDL.
501
00:25:45,420 --> 00:25:51,000
My favorite example is, do you
think just adding a Column, you
502
00:25:51,000 --> 00:25:55,240
cannot put your traffic down or
your Database down?
503
00:25:55,240 --> 00:25:57,580
You can, if it cannot acquire Lock.
504
00:25:57,800 --> 00:26:01,660
It starts blocking all Queries
to this Table, even SELECTs.
505
00:26:02,580 --> 00:26:05,780
Like, I cannot acquire Lock, everyone
else can wait.
506
00:26:05,900 --> 00:26:11,540
Even SELECTs who come with access
Share Lock, don't touch this.
507
00:26:12,040 --> 00:26:12,760
That's it.
508
00:26:13,320 --> 00:26:14,340
And that's not good.
509
00:26:14,680 --> 00:26:17,660
And this is super unexpected and
a super big surprise.
510
00:26:18,720 --> 00:26:22,000
Michael: Yeah, especially because
you could even test that.
511
00:26:22,580 --> 00:26:27,620
You could even make a fork of production
or clone it run that
512
00:26:27,620 --> 00:26:31,520
on its own in isolation where there
is no Lock and it runs super
513
00:26:31,520 --> 00:26:35,540
fast so you think this is safe
but even though it was super fast
514
00:26:35,540 --> 00:26:39,720
it does require that heavy Lock
and that blocks everything else
515
00:26:39,720 --> 00:26:41,780
definitely a gotcha that's a great
1
516
00:26:41,800 --> 00:26:45,480
Nikolay: yeah yeah and there are
many others in the area of DDL
517
00:26:45,480 --> 00:26:51,600
but this I find like affecting
every single project which had
518
00:26:51,660 --> 00:26:55,900
like at least some growth like
I don't like to 1000 TPS and it's
519
00:26:55,900 --> 00:26:58,820
already noticeable it's not noticeable
in tiny projects
520
00:27:00,140 --> 00:27:02,900
Michael: yeah yeah which I think
is part is kind of part of the
521
00:27:02,900 --> 00:27:06,100
problem right or like at least
makes it worse because anybody
522
00:27:06,100 --> 00:27:10,920
that gains experience on smaller
projects as they grow Get some
523
00:27:10,920 --> 00:27:13,940
confidence that these things work
or they're fine and then it
524
00:27:13,940 --> 00:27:15,740
change changes at some point
525
00:27:16,320 --> 00:27:16,820
Nikolay: exactly
526
00:27:18,060 --> 00:27:20,140
Michael: so you've got a great
article in this actually I think
527
00:27:20,140 --> 00:27:24,900
that I can share about DDL it's
all about DDL changes I think
528
00:27:25,160 --> 00:27:28,860
Nikolay: yeah it's yeah yeah it's
this this 1 is definitely included
529
00:27:28,860 --> 00:27:33,180
I think this 1 like has a different
blog post about like I have
530
00:27:33,180 --> 00:27:37,980
few about Details okay what else
531
00:27:38,760 --> 00:27:41,720
Michael: well in that in that area
of making big changes partly
532
00:27:41,720 --> 00:27:45,200
by mistake you know that the DELETE
or UPDATE without WHERE Do
533
00:27:45,200 --> 00:27:48,660
you think people would expect to
be able to undo something?
534
00:27:48,940 --> 00:27:54,440
I'm not thinking just from people
with experience of relational
535
00:27:54,440 --> 00:27:58,520
databases, but the fact that if
you do something like that by
536
00:27:58,520 --> 00:28:02,080
mistake, that you have to go to
your backups to get that data
537
00:28:02,080 --> 00:28:02,580
back.
538
00:28:03,080 --> 00:28:06,260
If I was in my early 20s or just
out, you know, if I was in my
539
00:28:06,260 --> 00:28:10,120
teens, all other software I use
has the ability to undo something
540
00:28:10,120 --> 00:28:11,420
if I do it by mistake.
541
00:28:11,940 --> 00:28:16,220
So it does feel slightly unexpected,
potentially if I've grown
542
00:28:16,220 --> 00:28:19,640
up in that world, that I can make
1 mistake and not be able to
543
00:28:19,640 --> 00:28:19,960
undo it.
544
00:28:19,960 --> 00:28:21,220
Nikolay: And what Database you
can?
545
00:28:21,220 --> 00:28:22,440
I mean, of course, there are like...
546
00:28:22,440 --> 00:28:23,440
Michael: Yeah, I don't know.
547
00:28:23,440 --> 00:28:24,660
Nikolay: Time travel or...
548
00:28:25,840 --> 00:28:27,500
If branching is supported, you
can...
549
00:28:27,500 --> 00:28:32,140
But it's still like not super simple, right?
550
00:28:32,520 --> 00:28:33,720
Well, yeah, I don't know.
551
00:28:33,720 --> 00:28:36,800
I mean, there are several projects which try to solve this problem
552
00:28:36,820 --> 00:28:41,760
and I mean the data specific databases supporting branching for
553
00:28:41,760 --> 00:28:47,620
example or even other I think Have you heard about this new company
554
00:28:47,660 --> 00:28:49,520
where Michael Stonebraker is participating?
555
00:28:50,920 --> 00:28:51,060
Michael: I've
556
00:28:51,060 --> 00:28:53,000
heard of it, I don't know much about it.
557
00:28:53,000 --> 00:28:53,500
Nikolay: DBOps?
558
00:28:53,840 --> 00:28:54,840
Or how is it called?
559
00:28:54,840 --> 00:28:55,700
I don't remember.
560
00:28:55,920 --> 00:28:57,100
Not DBOps, DBOS.
561
00:28:58,700 --> 00:28:59,200
Michael: Yes.
562
00:29:00,180 --> 00:29:03,160
Nikolay: So I wonder if this feature will be inside it.
563
00:29:03,860 --> 00:29:04,700
I don't know.
564
00:29:04,740 --> 00:29:06,260
For me, it's not expected.
565
00:29:06,380 --> 00:29:12,100
I cannot name this gotcha because it's a hard problem, and we
566
00:29:13,140 --> 00:29:18,820
didn't get used to it, to having it, because it's rare.
567
00:29:19,710 --> 00:29:28,100
But if we go to young people's minds, there are many gotchas
568
00:29:28,100 --> 00:29:28,780
for them, right?
569
00:29:28,780 --> 00:29:30,700
Oh, this is working not as I expected.
570
00:29:32,080 --> 00:29:36,360
So naming is hard just because it should avoid gotchas.
571
00:29:36,380 --> 00:29:40,480
If you chose some name, you should think about other people and
572
00:29:40,480 --> 00:29:42,180
think to be very predictable.
573
00:29:42,980 --> 00:29:44,840
This is the art of naming.
574
00:29:45,440 --> 00:29:49,200
And we just discussed this vacuum_cost_limit affecting analyze.
575
00:29:50,140 --> 00:29:56,280
So yeah, and in young minds, gotchas, because gotchas world is
576
00:29:56,280 --> 00:30:00,740
much bigger because they don't understand the logic in many places,
577
00:30:00,740 --> 00:30:05,740
how it's like, like common practice in many places, right?
578
00:30:05,740 --> 00:30:08,540
And that's why many more unexpected situations.
579
00:30:09,640 --> 00:30:10,460
Michael: Yeah, fair.
580
00:30:11,000 --> 00:30:14,540
Nikolay: Yeah, by the way, if you run pg_dump on a very large
581
00:30:14,880 --> 00:30:21,400
database, like 1 table after another, And then we deal with snapshot,
582
00:30:21,400 --> 00:30:21,900
right?
583
00:30:22,580 --> 00:30:26,700
With snapshot, it's a repeatable read and snapshot.
584
00:30:26,820 --> 00:30:32,000
And even if you use multiple workers for pg_dump, they are synchronized
585
00:30:32,360 --> 00:30:33,780
reading from the same snapshot.
586
00:30:34,200 --> 00:30:38,100
But what if some table is dropped?
587
00:30:38,360 --> 00:30:40,340
Will it be present in this table?
588
00:30:41,040 --> 00:30:42,180
I'm not sure, actually.
589
00:30:42,880 --> 00:30:44,340
I just think there is a God check
590
00:30:44,340 --> 00:30:44,840
Michael: here.
591
00:30:45,660 --> 00:30:51,300
Nikolay: If during dump there are some schema changes, can we
592
00:30:51,300 --> 00:30:54,240
have some inconsistency in backups?
593
00:30:55,260 --> 00:30:56,580
Not in backups, in dumps.
594
00:30:56,580 --> 00:31:01,160
By the way, this confusion was resolved recently, right?
595
00:31:01,220 --> 00:31:06,040
We didn't mention it in the previous discussions of confusions,
596
00:31:06,060 --> 00:31:15,520
but the pg_dump page is not saying it's for dumps anymore.
597
00:31:15,940 --> 00:31:17,380
So dumps are not backups.
598
00:31:19,660 --> 00:31:20,880
Michael: That's coming.
599
00:31:21,960 --> 00:31:22,960
Nikolay: Yeah, in 2018.
600
00:31:23,100 --> 00:31:23,940
Okay, okay, okay.
601
00:31:23,940 --> 00:31:25,300
We will discuss this additionally.
602
00:31:25,640 --> 00:31:29,940
But this is a big confusion when
the computation says pg_dump
603
00:31:30,060 --> 00:31:31,080
is a backup tool.
604
00:31:31,080 --> 00:31:33,220
It's still so, even in 16.
605
00:31:33,760 --> 00:31:38,660
And now we have all rights to say
it's like all people who said
606
00:31:38,800 --> 00:31:42,500
pg_dump is not a backup tool, we
are right, because finally in
607
00:31:42,500 --> 00:31:44,040
18 it's already changed.
608
00:31:44,040 --> 00:31:47,340
I think it won't be reverted, I
hope so, this patch.
609
00:31:49,540 --> 00:31:54,800
But back to our topic, pg_dump can
be expected to be a tool for
610
00:31:54,800 --> 00:31:56,460
backups just because of documentation.
611
00:31:56,720 --> 00:32:01,320
Then you have quite a painful process
of realizing it's very
612
00:32:01,320 --> 00:32:02,320
bad for backups.
613
00:32:02,900 --> 00:32:06,600
So it's a gotcha, which is already
fixed in the future in Postgres
614
00:32:07,000 --> 00:32:07,500
18.
615
00:32:09,860 --> 00:32:18,900
And if we, in this area, pg_restore
doesn't run analyze, which
616
00:32:19,160 --> 00:32:24,620
hurts every second attempt to restore
a big database, even with
617
00:32:24,620 --> 00:32:26,620
experienced people like I am.
618
00:32:26,880 --> 00:32:29,920
Because, again, it's like with
pg_upgrade, we discussed it.
619
00:32:29,920 --> 00:32:33,400
pg_restore, You restore a huge
dump, you start working with
620
00:32:33,400 --> 00:32:38,940
it, but it's slow, and in plans
you see where you expect a simple
621
00:32:38,940 --> 00:32:42,520
index scan, you see bitmap scans,
bitmap index, bitmap heap scan,
622
00:32:42,520 --> 00:32:44,720
because there is no vacuum, there
is no analysis.
623
00:32:45,060 --> 00:32:50,580
It also should run on a vacuum
to collect visibility maps.
624
00:32:51,340 --> 00:32:51,840
Or
625
00:32:53,300 --> 00:32:56,980
Michael: it might not be unreasonable
to think that maybe backups
626
00:32:56,980 --> 00:32:59,980
would include the statistics and
then restore would restore the
627
00:32:59,980 --> 00:33:00,480
statistics.
628
00:33:01,400 --> 00:33:02,920
Nikolay: Physical backups, I agree.
629
00:33:03,280 --> 00:33:04,860
Logical backups, I disagree.
630
00:33:06,680 --> 00:33:10,260
You don't include indexes, which
is derivative from data itself.
631
00:33:10,260 --> 00:33:12,840
Statistics is also derivative of
data.
632
00:33:13,660 --> 00:33:16,120
So you include description, like
create index.
633
00:33:16,320 --> 00:33:20,520
There should be, maybe should be
some word like analyze in there.
634
00:33:22,060 --> 00:33:25,620
I wanted to say create statistics,
but it was analyzed already.
635
00:33:26,180 --> 00:33:27,100
There's such word.
636
00:33:27,500 --> 00:33:28,200
Why restore?
637
00:33:28,200 --> 00:33:31,420
And restore could run it.
638
00:33:31,420 --> 00:33:37,280
If we use pg_restore -j
8, for example, 8 parallel drops.
639
00:33:37,920 --> 00:33:43,160
In this case, why are we not recalculating
statistics?
640
00:33:44,100 --> 00:33:48,720
Right now, everyone who deals with
some automation, for example,
641
00:33:48,720 --> 00:33:52,200
for DBLab Engine, we need it when
provisioning is logical.
642
00:33:52,200 --> 00:33:55,440
So it's dump restore and we need
to move as fast as possible
643
00:33:55,440 --> 00:34:00,180
multiple jobs, multiple workers
for dump, multiple workers for
644
00:34:00,180 --> 00:34:00,680
restore.
645
00:34:01,100 --> 00:34:05,920
And then of course, vacuumdb,
in this case, --analyze
646
00:34:05,920 --> 00:34:10,840
to have vacuum as well and also
-j, but People forget about
647
00:34:10,840 --> 00:34:14,340
this and forget forgetting means
like you expect it will be fine,
648
00:34:14,340 --> 00:34:20,220
but if you don't run analyze yourself
ideally in multiple threads
649
00:34:20,220 --> 00:34:23,400
and multiple workers using multiple
workers in this case performance
650
00:34:23,400 --> 00:34:28,200
will be very bad and people start
thinking oh Postgres is slow
651
00:34:30,040 --> 00:34:33,540
have you heard about this Like
I restore from dump and it's slow.
652
00:34:35,140 --> 00:34:39,400
Michael: I have to admit I haven't
heard that complaint, no.
653
00:34:40,860 --> 00:34:46,240
Nikolay: Okay, I'm hearing it and
I'm doing it myself almost
654
00:34:46,240 --> 00:34:47,060
20 years.
655
00:34:47,088 --> 00:34:49,172
I know about this.
656
00:34:49,200 --> 00:34:51,500
Michael: No, I'm not saying it
doesn't exist, I just haven't,
657
00:34:51,500 --> 00:34:54,880
you know, maybe it's just not the
category of problem maybe what
658
00:34:54,880 --> 00:34:58,940
happens when people experience
that is they don't noticed online
659
00:34:59,380 --> 00:35:02,860
or I didn't know it's or they don't
post they just go try something
660
00:35:02,860 --> 00:35:03,360
else
661
00:35:03,520 --> 00:35:07,120
Nikolay: yeah it's it's not a big
deal if you restore, and it's
662
00:35:07,120 --> 00:35:10,760
working, and then you want to deal
with it 1 day later.
663
00:35:10,760 --> 00:35:14,120
Because probably, likely, autovacuum
will do it.
664
00:35:14,600 --> 00:35:18,620
But If you expect to run fast queries
immediately after restoration
665
00:35:18,920 --> 00:35:24,000
from a dump, I'm talking about
something which is 100% very well
666
00:35:24,000 --> 00:35:27,940
known to everyone, who is at least
a little bit experienced.
667
00:35:28,700 --> 00:35:32,000
But it doesn't make it non-gotcha,
it's still gotcha.
668
00:35:34,360 --> 00:35:39,440
That pg_restore doesn't calculate
statistics and doesn't run Vacuum.
669
00:35:42,040 --> 00:35:44,620
Michael: I mean it's quite remarkable,
I know we could go on
670
00:35:44,620 --> 00:35:47,960
with this list for quite a while
and there's like varying levels
671
00:35:47,960 --> 00:35:53,000
of gotchas but I like that I like
the list you sent me how how
672
00:35:53,000 --> 00:35:57,840
short and brief it is and I I really
think for many other databases
673
00:35:58,320 --> 00:36:02,760
it would be a much much longer
list I listened to well you're
674
00:36:02,760 --> 00:36:06,260
listening to your experience of
you know a couple of days of
675
00:36:06,260 --> 00:36:10,840
trying to use MySQL at the beginning,
and recently I was listening
676
00:36:10,840 --> 00:36:14,820
to an interview with Tom Lane,
and he mentioned, like, checking
677
00:36:14,820 --> 00:36:19,540
out MySQL at the beginning and
not liking a few things about
678
00:36:19,540 --> 00:36:19,940
it.
679
00:36:19,940 --> 00:36:22,100
And that feels like a really common
experience.
680
00:36:22,540 --> 00:36:25,920
And having worked with commercial
databases like SQL Server and
681
00:36:25,920 --> 00:36:32,080
Oracle, that often move much faster
in terms of adding features
682
00:36:32,080 --> 00:36:33,940
that enterprise clients really
want.
683
00:36:34,120 --> 00:36:39,240
There's just so many more gotchas
that result from processes
684
00:36:39,360 --> 00:36:39,800
like that.
685
00:36:39,800 --> 00:36:41,120
So I imagine I could be wrong.
686
00:36:41,120 --> 00:36:44,620
Nikolay: I think Postgres has lots
of gotchas, lots of them.
687
00:36:45,300 --> 00:36:47,580
Michael: But compared to other
relational databases?
688
00:36:48,340 --> 00:36:51,360
Nikolay: Yeah, it's a complex system,
all complex systems have
689
00:36:51,360 --> 00:36:52,280
a lot of gotchas.
690
00:36:52,600 --> 00:36:57,920
Some of them are just very niche,
very narrow and deep and so
691
00:36:57,920 --> 00:36:58,420
on.
692
00:36:58,940 --> 00:37:01,760
Some of them are like nobody knows
about them.
693
00:37:01,760 --> 00:37:04,900
For example, SELECT INTO.
694
00:37:06,820 --> 00:37:09,740
This is what you can write inside
PL/pgSQL, right?
695
00:37:10,080 --> 00:37:15,140
So you write a FUNCTION or TRIGGER,
and you write SELECT INTO
696
00:37:15,300 --> 00:37:17,560
some variable, blah, blah, blah.
697
00:37:17,560 --> 00:37:20,860
And then you think, OK, this SELECT
is like maybe it's complex
698
00:37:20,860 --> 00:37:21,760
SELECT, actually.
699
00:37:22,380 --> 00:37:26,040
Subselect or, I don't know, joins
and so on.
700
00:37:26,040 --> 00:37:29,360
But in the end, you have a scalar
or something, and you select
701
00:37:29,600 --> 00:37:31,680
into a variable inside PL/pgSQL.
702
00:37:31,800 --> 00:37:34,240
And then you think, I want to understand
the plan, or I want
703
00:37:34,240 --> 00:37:38,940
to just run it and try to troubleshoot,
and you put it to psql,
704
00:37:39,840 --> 00:37:40,840
what will happen?
705
00:37:40,840 --> 00:37:45,860
If you put SELECT INTO blah blah
blah, and then some regular
706
00:37:45,920 --> 00:37:49,380
select from where order by you
know
707
00:37:49,380 --> 00:37:52,620
Michael: just an error no what
happens
708
00:37:52,900 --> 00:37:54,240
Nikolay: it will create a table
709
00:37:56,120 --> 00:37:57,120
Michael: oh okay yeah
710
00:37:57,120 --> 00:38:01,360
Nikolay: go select into is 1 of
your lights it's great table
711
00:38:02,420 --> 00:38:06,140
This I think just should be removed,
like completely removed,
712
00:38:06,380 --> 00:38:06,880
deprecated.
713
00:38:07,900 --> 00:38:10,300
Select into creating a table, it's
a DDL.
714
00:38:15,140 --> 00:38:17,580
This is definitely a gotcha for
everyone.
715
00:38:18,840 --> 00:38:21,540
Michael: Yes, imagine all those
parsers that just...
716
00:38:21,820 --> 00:38:25,240
I know this is flawed for multiple
reasons, but look at any query
717
00:38:25,240 --> 00:38:28,140
that starts with SELECT, and like,
oh, it's a read query.
718
00:38:28,200 --> 00:38:32,780
Nikolay: Well, yeah, usually we
say SELECT for update is acquiring
719
00:38:32,860 --> 00:38:35,280
a heavy lock, blocking all writes.
720
00:38:36,220 --> 00:38:42,300
Yes, blocking all writes on these
rows, particular rows, not
721
00:38:42,380 --> 00:38:43,280
the whole table.
722
00:38:43,620 --> 00:38:46,780
But Select can create a table.
723
00:38:47,180 --> 00:38:49,700
And we have create table as select
already.
724
00:38:49,840 --> 00:38:53,180
And this is what all people use,
I think, who need it.
725
00:38:53,600 --> 00:38:59,440
So select into probably just some
very old stuff that could be
726
00:38:59,440 --> 00:39:01,080
potentially removed, I think.
727
00:39:01,400 --> 00:39:03,280
Michael: If it's not in the SQL
standard
728
00:39:05,200 --> 00:39:09,560
Nikolay: oh it's a good point if
it is no chances right
729
00:39:09,780 --> 00:39:13,700
Michael: yeah well probably not
no cool anything else you want
730
00:39:13,700 --> 00:39:14,840
to make sure we covered
731
00:39:15,420 --> 00:39:18,480
Nikolay: maybe it's enough I think
we covered less than 1% of
732
00:39:18,480 --> 00:39:19,620
what we could.
733
00:39:19,700 --> 00:39:20,740
This is my feeling.
734
00:39:20,740 --> 00:39:21,640
What do you think?
735
00:39:22,240 --> 00:39:25,560
Michael: Well, it depends what
you count as gotchas and how niche
736
00:39:25,560 --> 00:39:26,640
you're willing to go.
737
00:39:26,640 --> 00:39:29,640
It turns out the more you know,
the more gotchas you know about,
738
00:39:29,640 --> 00:39:30,280
I guess.
739
00:39:31,220 --> 00:39:34,100
Nikolay: So I could not resist
and I checked the documentation.
740
00:39:34,640 --> 00:39:37,940
SQL standard uses SELECT INTO to
represent selecting values into
741
00:39:37,940 --> 00:39:43,380
a scalar variable of a host program
rather than creating a new
742
00:39:43,380 --> 00:39:43,700
table.
743
00:39:43,700 --> 00:39:47,220
It's definitely Postgresism and
I think it should be removed.
744
00:39:47,720 --> 00:39:49,340
Michael: Okay, cool, good 1.
745
00:39:50,500 --> 00:39:53,220
Nikolay: Good, okay, good chat,
maybe some kind of entertaining
746
00:39:53,240 --> 00:39:56,100
more than useful, but I enjoyed
it.
747
00:39:56,100 --> 00:39:56,820
I enjoyed it.
748
00:39:56,820 --> 00:39:57,540
Michael: Yeah, absolutely.
749
00:39:58,320 --> 00:40:02,660
And feel free to send us any more
that you've got or that you
750
00:40:02,660 --> 00:40:05,900
Nikolay: Maybe we should think
about specific areas like we did
751
00:40:05,900 --> 00:40:11,740
for for now and maybe we should
explore some areas from this
752
00:40:11,740 --> 00:40:13,320
perspective what can be unexpected
753
00:40:14,340 --> 00:40:16,680
Michael: Yeah, for sure. Thanks
so much.
754
00:40:16,680 --> 00:40:17,220
Nikolay.
755
00:40:17,220 --> 00:40:18,180
Catch you next week.
756
00:40:18,180 --> 00:40:18,240
Nikolay: Thank you.
757
00:40:18,240 --> 00:40:18,560
You too.
