1
0:0:0,06 --> 0:0:2,06
Nikolay: Hello, hello, this is
PostgresFM.
2
0:0:2,12 --> 0:0:7,24
My name is Nik, PostgresAI, and
as usual with me, Michael, pgMustard.
3
0:0:7,5 --> 0:0:8,299999
Hi, Michael.
4
0:0:9,28 --> 0:0:10,12
Michael: Hi, Nik.
How are you doing?
5
0:0:11,24 --> 0:0:12,139999
Nikolay: So the topic...
6
0:0:12,7 --> 0:0:14,28
I'm doing great, how are
you?
7
0:0:14,599999 --> 0:0:15,86
Michael: I'm good, Thank you.
8
0:0:15,94 --> 0:0:16,86
What's the topic?
9
0:0:17,26 --> 0:0:18,119999
Nikolay: What's the topic?
10
0:0:18,119999 --> 0:0:21,66
The topic I chose, and everyone
already saw it on cover you created,
11
0:0:21,66 --> 0:0:22,06
right?
12
0:0:22,06 --> 0:0:29,86
So the topic is 5 or so, maybe
6 things you should check when
13
0:0:29,86 --> 0:0:32,98
you start new schema or you already
have some schema and your
14
0:0:32,98 --> 0:0:38,32
project is growing and you want
to be better prepared for bigger
15
0:0:38,32 --> 0:0:40,02
scale and performance wise.
16
0:0:40,6 --> 0:0:45,32
So what, especially if you use
AI to design and improve your
17
0:0:45,32 --> 0:0:48,48
schema and your project is growing.
18
0:0:48,76 --> 0:0:55,02
So what should you look at to check
your schema health?
19
0:0:55,38 --> 0:0:56,14
Michael: Makes sense.
20
0:0:56,32 --> 0:1:0,2
Just on the topic in general, I
do think there are some things
21
0:1:0,2 --> 0:1:3,58
that are better suited to asking
for help from AI and not?
22
0:1:3,68 --> 0:1:7,58
Do you think like schema design
is 1 of them or actually is it
23
0:1:7,58 --> 0:1:11,32
1 of those ones that maybe we should
still do it a little bit
24
0:1:11,32 --> 0:1:13,12
more ourselves at the moment?
25
0:1:13,66 --> 0:1:18,04
Nikolay: Well as you know I'm very
very very intensive user of
26
0:1:18,04 --> 0:1:18,54
AI.
27
0:1:18,86 --> 0:1:19,36
Yeah.
28
0:1:20,28 --> 0:1:23,4
Honestly, like I do everything
through AI right now.
29
0:1:24,44 --> 0:1:29,24
And I'm 100% schema design is a
topic where AI can help a lot.
30
0:1:29,24 --> 0:1:32,36
But it also matters which questions
you ask.
31
0:1:32,98 --> 0:1:37,06
If you just say, I want this and
don't pay attention, you will
32
0:1:37,06 --> 0:1:39,64
have low quality schema in many
cases still.
33
0:1:40,44 --> 0:1:44,34
That's why I said, let's have this
episode because it might be
34
0:1:44,34 --> 0:1:48,74
quite basic for many advanced users
of Postgres, But this checklist
35
0:1:48,84 --> 0:1:53,08
we are going to present today,
it can be good thing to keep in
36
0:1:53,08 --> 0:1:56,82
mind always when you design schema,
just to ask right questions
37
0:1:56,94 --> 0:1:57,66
and revisit.
38
0:1:58,26 --> 0:2:1,4
Also, I'm not sure how exactly
you use AI.
39
0:2:1,4 --> 0:2:6,16
I use it always like I work with
people, developers.
40
0:2:7,4 --> 0:2:12,58
When you just ask something and
think those engineers, humans
41
0:2:12,58 --> 0:2:16,46
or AI, will solve all your problems
and make all the decisions,
42
0:2:17,1 --> 0:2:20,28
you're offloading full responsibility
to their shoulders, so
43
0:2:20,28 --> 0:2:24,36
then you have big negative surprises
always, right?
44
0:2:24,6 --> 0:2:29,64
So it's better to keep requests
as detailed as possible when
45
0:2:29,64 --> 0:2:34,48
you work with AI, and also make
some iterations to review and
46
0:2:34,48 --> 0:2:34,98
improve.
47
0:2:35,54 --> 0:2:38,92
And when making iterations, like
first, like I want some schema,
48
0:2:39,02 --> 0:2:44,12
like, I don't know, like LinkedIn
social media schema or something,
49
0:2:44,12 --> 0:2:44,62
right?
50
0:2:45,04 --> 0:2:48,98
It will give you immediately something,
but then you want some
51
0:2:49,22 --> 0:2:50,52
details to be checked.
52
0:2:50,54 --> 0:2:53,76
And this is exactly when this checklist can be very helpful.
53
0:2:54,02 --> 0:2:58,82
So you can revisit and think and imagine when you will have gigabytes
54
0:2:58,86 --> 0:3:1,58
and terabytes of data, what will happen.
55
0:3:1,86 --> 0:3:7,64
So to avoid painful refactoring in the future and outages maybe
56
0:3:7,64 --> 0:3:12,16
or performance degradation, the price you can pay in the beginning
57
0:3:12,16 --> 0:3:14,98
is much lower than the price you pay later.
58
0:3:16,96 --> 0:3:21,86
So I never work with AI in 1 shot, I always make iterations to
59
0:3:21,86 --> 0:3:25,08
improve in any question and to revisit and sometimes combine
60
0:3:25,08 --> 0:3:26,18
multiple LLMs.
61
0:3:28,26 --> 0:3:31,36
This is exactly when you can use this list of questions.
62
0:3:31,36 --> 0:3:35,58
So first topic is the data type of primary key.
63
0:3:36,68 --> 0:3:39,56
Michael: Yeah, and I feel like the answer to this has changed,
64
0:3:39,64 --> 0:3:43,9
or my opinion on what the best default for this has changed,
65
0:3:43,9 --> 0:3:49,2
actually, in the last few years, just off the back of learning
66
0:3:49,2 --> 0:3:53,4
more about timestamp ordered UUIDs, right?
67
0:3:53,56 --> 0:3:57,88
This used to be an age-old debate, right, UUID versus integer
68
0:3:57,88 --> 0:3:59,34
or big integer really.
69
0:4:0,06 --> 0:4:3,84
And most of the kind of blog posts I would see are like trying
70
0:4:3,84 --> 0:4:6,3
to just encourage people to use BIGINT over int.
71
0:4:7,06 --> 0:4:12,48
But now that we have first-class support for time-ordered UUIDs,
72
0:4:13,48 --> 0:4:15,92
I don't see many reasons not to go with that.
73
0:4:15,92 --> 0:4:16,8
What do you think?
74
0:4:16,8 --> 0:4:20,78
Nikolay: Well, the size, 8, is double.
75
0:4:21,04 --> 0:4:21,54
Yeah.
76
0:4:21,58 --> 0:4:24,94
Yeah, your ID is 16 bytes, so it's quite a big price to pay.
77
0:4:25,2 --> 0:4:29,44
Each row, you need to have 8 more bytes.
78
0:4:30,14 --> 0:4:31,015
It's not that much, is it?
79
0:4:31,015 --> 0:4:32,02
In many cases, it's fine.
80
0:4:32,2 --> 0:4:34,34
Well, indexes will be also bigger.
81
0:4:34,82 --> 0:4:36,3
Indexes double, yeah.
82
0:4:37,12 --> 0:4:39,98
It might make sense if volumes are huge.
83
0:4:40,12 --> 0:4:44,44
But anyway, to keep things simple, a rule to remember, red flags,
84
0:4:45,06 --> 0:4:49,5
If you are prepared for really large volumes of data, it must
85
0:4:49,5 --> 0:4:55,16
not be int4, it should be int8.
86
0:4:55,44 --> 0:5:2,24
And in terms of UUID path, data type is always UUID for both
87
0:5:2,24 --> 0:5:4,18
version 4 and version 7.
88
0:5:4,6 --> 0:5:8,7
But you can check default how it's generated.
89
0:5:8,94 --> 0:5:13,78
And in modern Postgres, there is a function, I think in 18, only
90
0:5:13,78 --> 0:5:14,6
in the latest version.
91
0:5:14,6 --> 0:5:15,02
I think
92
0:5:15,02 --> 0:5:15,26
Michael: so.
93
0:5:15,26 --> 0:5:17,44
It didn't quite make 17, did it?
94
0:5:17,44 --> 0:5:21,76
Nikolay: So if you're on Postgres 18, so it's better to use UUIDv7
95
0:5:21,76 --> 0:5:25,86
but also remembering trade-offs, because there is
96
0:5:25,86 --> 0:5:29,52
timestamp there, and the values will be ordered, like with integer
97
0:5:30,06 --> 0:5:31,08
or big integer.
98
0:5:31,4 --> 0:5:33,18
So there is a nuance here.
99
0:5:33,58 --> 0:5:37,12
But performance-wise it's much better than UUIDv4.
100
0:5:37,9 --> 0:5:41,54
There are many articles about it and you just need to check,
101
0:5:41,54 --> 0:5:44,36
okay, are we going to go with integer?
102
0:5:45,04 --> 0:5:48,7
Then it's int8 or BIGINT, right?
103
0:5:48,96 --> 0:5:50,64
Are we going to go with UUID?
104
0:5:51,06 --> 0:5:56,2
Then default generation, default
should be version 7 for Postgres
105
0:5:56,2 --> 0:5:56,58
18.
106
0:5:56,58 --> 0:5:59,92
For older version, it's also not
an excuse to stick with version
107
0:5:59,92 --> 0:6:0,28
4.
108
0:6:0,28 --> 0:6:2,0
You can write your own function.
109
0:6:2,9 --> 0:6:5,42
And you ask AI to write your own
function.
110
0:6:6,04 --> 0:6:8,1
Michael: Yes, or generate them
outside of the database.
111
0:6:8,3 --> 0:6:12,62
Nikolay: I slightly prefer this
option less because if you have
112
0:6:12,62 --> 0:6:17,08
multiple application nodes, Who
knows what will happen with their
113
0:6:17,08 --> 0:6:17,58
clocks?
114
0:6:18,58 --> 0:6:25,4
And since UUIDv7 includes
timestamp inside it, order might
115
0:6:25,4 --> 0:6:29,9
be not, I don't know, like I prefer
to leave it on database shoulders
116
0:6:29,9 --> 0:6:32,8
to have like on the primary single
source of true.
117
0:6:32,8 --> 0:6:37,4
On the other side, UUID by design
is generated, it serves purpose
118
0:6:37,44 --> 0:6:40,46
to work on multiple, like on distributed
systems, right?
119
0:6:40,58 --> 0:6:42,18
So maybe it's not a big deal.
120
0:6:42,18 --> 0:6:43,82
Generate on client side.
121
0:6:44,38 --> 0:6:47,68
Michael: All I mean is if you're
on an old Postgres and it saves
122
0:6:47,68 --> 0:6:49,78
you creating your own function
or whatever.
123
0:6:50,28 --> 0:6:55,36
Nikolay: Postgres waited a couple
of years to bring UUIDv7 officially.
124
0:6:56,0 --> 0:6:59,1
Although again, you can create
even SQL function, even not PL/pgSQL
125
0:6:59,1 --> 0:7:0,8
function, simple SQL function.
126
0:7:0,8 --> 0:7:4,64
And if you ask your AI to search
for example, probably it will
127
0:7:4,64 --> 0:7:8,8
find my page how to do it, but
or other people's pages.
128
0:7:9,44 --> 0:7:13,68
But in other libraries from Google,
from Node.js library, everything,
129
0:7:14,24 --> 0:7:17,74
they have it for a couple of years
already, so for longer.
130
0:7:18,12 --> 0:7:20,74
They implemented before standard
was finalized.
131
0:7:21,62 --> 0:7:24,92
Michael: Yeah, so huge red flags
to look out for if it's using
132
0:7:24,92 --> 0:7:28,64
the integer data type, which is
the same as int4, or serial.
133
0:7:29,02 --> 0:7:31,92
I think AI sometimes uses, it will
define it as serial.
134
0:7:31,92 --> 0:7:35,46
So if you just see that word without
bigserial, you might hit
135
0:7:35,46 --> 0:7:38,48
that limit at 2000000000 or whatever
the, I can't remember the
136
0:7:38,48 --> 0:7:39,52
exact upper limit.
137
0:7:39,56 --> 0:7:44,08
Nikolay: Yeah, and avoid UUID version
4 if you want good performance
138
0:7:44,1 --> 0:7:45,4
on large volumes of data.
139
0:7:45,4 --> 0:7:46,56
Yeah, that's it actually.
140
0:7:46,56 --> 0:7:50,04
Let's keep it simple because we
could discuss more advanced situations.
141
0:7:50,74 --> 0:7:54,44
Michael: I think we should do a
whole episode someday on migrations,
142
0:7:54,72 --> 0:7:57,34
like for people that are getting
close to that 2 billion
143
0:7:57,34 --> 0:7:58,26
Nikolay: number, that
144
0:7:58,26 --> 0:7:59,44
Michael: would be a good topic.
145
0:7:59,44 --> 0:8:2,12
Nikolay: I can share, I had a lot
of experience with different
146
0:8:2,12 --> 0:8:2,62
approaches.
147
0:8:3,66 --> 0:8:4,28
Let's do that.
148
0:8:4,28 --> 0:8:4,78
Good.
149
0:8:5,66 --> 0:8:6,98
So how to define primary key?
150
0:8:6,98 --> 0:8:10,46
I can actually have an article
about it, how to...
151
0:8:11,28 --> 0:8:11,78
Cool.
152
0:8:12,1 --> 0:8:12,74
Michael: Let's do that.
153
0:8:12,74 --> 0:8:15,58
Nikolay: Covering multiple paths so we could talk about this.
154
0:8:16,38 --> 0:8:18,28
Number 2 is constraints.
155
0:8:18,62 --> 0:8:19,7
Topic is constraints.
156
0:8:21,38 --> 0:8:22,32
Michael: Other constraints.
157
0:8:22,54 --> 0:8:22,94
Nikolay: Yeah, yeah.
158
0:8:22,94 --> 0:8:23,6
Yeah, other.
159
0:8:23,94 --> 0:8:25,32
Beyond primary key.
160
0:8:25,58 --> 0:8:25,9
Yeah.
161
0:8:25,9 --> 0:8:31,68
So I would just ask, first thing, are we using constraints well?
162
0:8:31,78 --> 0:8:33,84
Like to myself or AI and so on.
163
0:8:33,84 --> 0:8:38,26
And because Postgres has 6 types of constraints and it's quite
164
0:8:38,26 --> 0:8:45,56
rich tooling, and it's much more convenient to have them earlier,
165
0:8:46,08 --> 0:8:48,48
not because adding them later is a big pain.
166
0:8:48,48 --> 0:8:54,44
Right now we have quite good support of two-step constraint definitions.
167
0:8:55,4 --> 0:9:0,48
I think in Postgres 19 it will be possible also if not null constraints,
168
0:9:0,48 --> 0:9:1,36
which is interesting.
169
0:9:1,36 --> 0:9:5,14
But anyway, check constraints, foreign keys, unique constraints,
170
0:9:5,14 --> 0:9:10,02
it's possible to create first in almost finished state and then
171
0:9:10,02 --> 0:9:13,38
finalize it to avoid the long-lasting locks.
172
0:9:13,38 --> 0:9:17,96
But this is not the reason why I prefer to think about it in
173
0:9:17,96 --> 0:9:18,82
the very beginning.
174
0:9:18,96 --> 0:9:21,8
The main reason is the quality of data.
175
0:9:21,98 --> 0:9:24,82
This is why constraints exist in the first place, right?
176
0:9:25,44 --> 0:9:29,62
So if you introduce constraints early in your project, data quality
177
0:9:29,62 --> 0:9:30,3
is good.
178
0:9:31,84 --> 0:9:32,52
Introducing them
179
0:9:32,52 --> 0:9:33,32
Michael: later probably will...
180
0:9:33,32 --> 0:9:36,42
Yeah, and database side as well.
181
0:9:36,42 --> 0:9:37,4
I mean, it just...
182
0:9:37,64 --> 0:9:38,6
Nikolay: Oh yeah, yeah.
183
0:9:38,94 --> 0:9:40,78
I don't trust constraints outside.
184
0:9:43,26 --> 0:9:45,54
With age, I became less radical.
185
0:9:45,66 --> 0:9:49,18
My points of view became less radical with it.
186
0:9:49,18 --> 0:9:53,54
So I just observe without fighting, like with backend engineers
187
0:9:53,94 --> 0:9:57,74
who try to implement constraints on application side.
188
0:9:57,74 --> 0:10:1,16
I just have a note, like they are not reliable because who knows
189
0:10:1,44 --> 0:10:5,08
which other layers of types of applications, especially these
190
0:10:5,08 --> 0:10:9,72
days with AI, it's so easy to add something else, implement it
191
0:10:9,72 --> 0:10:12,56
in a different framework or even a language, it's super easy
192
0:10:12,56 --> 0:10:13,24
right now.
193
0:10:13,38 --> 0:10:16,5
And you will need to reimplement those constraints, or you will
194
0:10:16,5 --> 0:10:19,46
just forget and miss them and data quality will suffer.
195
0:10:21,04 --> 0:10:25,64
Michael: So specifically, something I think I'd like to see more
196
0:10:25,64 --> 0:10:30,26
of or at least I see a lot of people not doing is columns that
197
0:10:30,26 --> 0:10:32,8
you're not expecting to put any null values in, or you don't
198
0:10:32,8 --> 0:10:36,22
want to have any null values in, actually explicitly making them
199
0:10:36,22 --> 0:10:37,0
not null.
200
0:10:37,06 --> 0:10:40,96
Like, if you don't specify, then columns are nullable.
201
0:10:41,76 --> 0:10:45,84
And I think most people, or at
least most AIs I see, are creating,
202
0:10:46,1 --> 0:10:49,58
like by default, they don't put
NOT NULL on every column.
203
0:10:49,84 --> 0:10:52,7
Nikolay: Yeah, there's an opinion
that NOT NULL should be default.
204
0:10:53,2 --> 0:10:56,2
Anyway, it's hard to have a default,
but inside your project
205
0:10:56,2 --> 0:11:0,1
you can say, okay, unless it's
explicitly specified, let's make
206
0:11:0,1 --> 0:11:1,28
columns NOT NULL.
207
0:11:1,62 --> 0:11:2,12
Exactly.
208
0:11:3,16 --> 0:11:6,82
And avoid troubles with NULL we
talked about in a special episode
209
0:11:6,82 --> 0:11:8,08
about NULLs, right?
210
0:11:8,26 --> 0:11:12,72
Michael: Yeah, well and I guess
in this specific case there is
211
0:11:12,72 --> 0:11:17,58
also, well with some constraints,
mostly we care about, we're
212
0:11:17,58 --> 0:11:20,52
doing it for quality reasons, but
there is, there is like a tiny
213
0:11:20,52 --> 0:11:23,56
amount of performance optimization
that comes out of them.
214
0:11:23,56 --> 0:11:28,48
I guess a bit more for unique constraints,
like if you, if the
215
0:11:28,48 --> 0:11:31,32
database knows for sure this is
going to be, this is unique,
216
0:11:31,32 --> 0:11:32,58
it can do some optimizations.
217
0:11:33,32 --> 0:11:36,02
So yeah, anyway, but yeah, good
point about, I like that about
218
0:11:36,02 --> 0:11:36,52
NOT NULL.
219
0:11:37,84 --> 0:11:39,82
Nikolay: Yeah, yeah, unique is
interesting stuff.
220
0:11:39,96 --> 0:11:44,08
And also, I don't think foreign
keys are underused in Postgres
221
0:11:44,12 --> 0:11:44,62
ecosystem.
222
0:11:45,3 --> 0:11:49,34
Days when everyone suggested that
foreign keys are super bad,
223
0:11:49,54 --> 0:11:52,8
for me it's 20 years ago, I remember
these topics, let's avoid
224
0:11:52,8 --> 0:11:53,76
foreign keys completely.
225
0:11:54,52 --> 0:11:57,5
Although we know that foreign keys
can bite badly, we had an
226
0:11:57,5 --> 0:12:4,14
episode with Metronome company
about issues basically with MultiXact
227
0:12:4,76 --> 0:12:8,56
related issues which can be caused
when you use foreign keys
228
0:12:8,56 --> 0:12:9,06
a lot.
229
0:12:9,06 --> 0:12:12,68
But anyway, I still think foreign
keys is a good thing to have,
230
0:12:12,8 --> 0:12:13,54
data quality.
231
0:12:13,86 --> 0:12:18,3
But I think what is usually underused
is check constraints.
232
0:12:18,84 --> 0:12:19,7
They are great.
233
0:12:20,38 --> 0:12:22,72
They could be used much more often.
234
0:12:23,1 --> 0:12:25,9
And for example, a classic example,
when I say, like, when I
235
0:12:25,9 --> 0:12:31,08
see in schema ENUM data type,
I think, okay, later, If we want
236
0:12:31,08 --> 0:12:36,36
to change this list of possible
values, how will our migration?
237
0:12:37,36 --> 0:12:38,94
Michael: Especially removing any.
238
0:12:39,74 --> 0:12:40,24
Yeah,
239
0:12:40,52 --> 0:12:43,76
Nikolay: so check constraints are
slightly more flexible.
240
0:12:46,58 --> 0:12:49,44
Well, Also removing is a topic,
right?
241
0:12:49,44 --> 0:12:52,18
But check constraint you can introduce
in 2 stages.
242
0:12:52,92 --> 0:12:57,04
Again, you can drop the old 1 and
you can first create a new
243
0:12:57,04 --> 0:13:0,6
1 in NOT VALID state and then validate
it in a separate transaction.
244
0:13:1,56 --> 0:13:7,12
But I just see it like quite useful,
quite convenient tool for
245
0:13:7,12 --> 0:13:11,04
me and you see it in schema right
away like this this column
246
0:13:11,04 --> 0:13:15,36
should be like for example in this
range or it should be this
247
0:13:15,36 --> 0:13:16,36
list or something.
248
0:13:17,02 --> 0:13:20,7
So I wish it was used more often.
249
0:13:21,48 --> 0:13:21,98
Michael: Yeah.
250
0:13:22,82 --> 0:13:24,3
What do you see in...
251
0:13:24,32 --> 0:13:28,9
Going back to foreign keys quickly, do you see a lot of the LLMs
252
0:13:29,32 --> 0:13:33,8
doing a lot of things on update cascade, on delete set NULL,
253
0:13:33,8 --> 0:13:34,76
things like that.
254
0:13:34,76 --> 0:13:38,68
Do you see any cascade stuff popping in there by default or not
255
0:13:38,68 --> 0:13:39,97
when it should be or when you think it
256
0:13:39,97 --> 0:13:40,08
Nikolay: should be?
257
0:13:40,08 --> 0:13:41,06
It's a good question.
258
0:13:41,18 --> 0:13:44,94
I actually don't remember having issues with this question.
259
0:13:45,12 --> 0:13:51,26
I know deferred constraints can might be an issue.
260
0:13:51,46 --> 0:13:55,12
I remember a case when pg_repack, it was a company called Miro,
261
0:13:55,12 --> 0:13:56,42
maybe you know it, right?
262
0:13:56,72 --> 0:13:59,7
They had issues with pg_repack and deferred constraints.
263
0:13:59,7 --> 0:14:3,42
There is an article, old article from them about this.
264
0:14:4,12 --> 0:14:8,72
But I don't remember particular issues here, and I think this
265
0:14:8,72 --> 0:14:12,48
leads us actually to the question for our particular projects,
266
0:14:12,8 --> 0:14:13,64
what's better?
267
0:14:14,68 --> 0:14:19,14
And If I had any doubts with AI, I would just make some experiments.
268
0:14:19,9 --> 0:14:24,96
Based on the product we are developing, I would say, okay, for
269
0:14:24,96 --> 0:14:30,58
example, in 1 year, how many of entities in which entity table
270
0:14:30,58 --> 0:14:31,4
we expect?
271
0:14:32,32 --> 0:14:37,46
All other tables, let's fill it with fake data, run vacuum Analyze
272
0:14:37,58 --> 0:14:41,76
to have good state, and then just explore plans with pgMustard,
273
0:14:41,98 --> 0:14:42,68
for example.
274
0:14:44,16 --> 0:14:47,96
Let's think what kind of workload we might have according to
275
0:14:47,96 --> 0:14:51,4
user stories in our specification of the product and so on.
276
0:14:51,58 --> 0:14:54,3
And then collect plans and see, right?
277
0:14:54,52 --> 0:14:59,44
And then include also modifying queries and collect those plans
278
0:14:59,44 --> 0:15:0,92
and think how it will work.
279
0:15:1,02 --> 0:15:5,74
It's so easy these days to make these experiments before we finalize
280
0:15:5,74 --> 0:15:9,38
all the decisions and then you will see it in action and it's
281
0:15:9,38 --> 0:15:9,88
great.
282
0:15:10,26 --> 0:15:15,22
So collect all the plans, find like weak spots where we have
283
0:15:15,38 --> 0:15:18,84
suboptimal plans and how we can deal with this.
284
0:15:18,84 --> 0:15:23,94
If, for example, we have entity and another, and 1 too many,
285
0:15:24,14 --> 0:15:26,14
and it's many, so many, right?
286
0:15:26,18 --> 0:15:30,14
And then we want Delete to be supported in UI, where people,
287
0:15:30,14 --> 0:15:33,58
as we know, expect 200 milliseconds, less than 1 second, let's
288
0:15:33,58 --> 0:15:34,26
say, right?
289
0:15:34,3 --> 0:15:38,48
And when this Delete of 1 row triggers propagating according
290
0:15:38,48 --> 0:15:43,62
to foreign key triggers, deletion of 1000000 rows, it cannot
291
0:15:43,62 --> 0:15:45,08
meet our requirements, right?
292
0:15:45,08 --> 0:15:49,64
Now we need to think about asynchronous deletion, maybe using
293
0:15:49,64 --> 0:15:52,66
some event queue system or something, right?
294
0:15:52,8 --> 0:15:53,3
Yeah.
295
0:15:53,6 --> 0:15:57,78
And this will pop up if you prepare a good experiment here, right?
296
0:15:57,78 --> 0:15:59,8
Without experiment, it's hard to say.
297
0:15:59,9 --> 0:16:1,3
You need to answer questions.
298
0:16:2,08 --> 0:16:5,64
How big are the tables and how this relationship between tables
299
0:16:5,64 --> 0:16:8,98
on foreign keys will be in the worst case, for example.
300
0:16:8,98 --> 0:16:13,52
Can user create like million of entities and then we need to
301
0:16:13,52 --> 0:16:14,96
delete this user, for example.
302
0:16:14,96 --> 0:16:18,16
This is a great question and with AI you can iterate so fast
303
0:16:18,16 --> 0:16:18,9
these days.
304
0:16:18,9 --> 0:16:19,62
So great.
305
0:16:20,28 --> 0:16:22,16
Collect plans and then think and decide.
306
0:16:23,5 --> 0:16:28,28
Okay, it's a good question, but I don't see it as a simple question.
307
0:16:28,78 --> 0:16:29,38
Michael: No, no, no.
308
0:16:29,38 --> 0:16:32,68
It was more, I just asked in case you'd seen there was a chance
309
0:16:32,68 --> 0:16:37,54
that LLMs were throwing it in on every foreign key and That was
310
0:16:37,54 --> 0:16:40,64
maybe a bad like bad deal Oh, but it sounds like they're not
311
0:16:40,64 --> 0:16:43,26
Nikolay: maybe a wrong person to ask about this because I don't
312
0:16:43,26 --> 0:16:46,24
just I design sometimes these days I have a few applications
313
0:16:46,36 --> 0:16:49,9
designed Yeah created from scratch with AI but I mostly deal
314
0:16:49,9 --> 0:16:53,26
with things which other people already created, and I just see
315
0:16:53,26 --> 0:16:54,9
how AI can help.
316
0:16:55,32 --> 0:17:0,12
So I'm not sure will this propagation of deletion, for example,
317
0:17:0,12 --> 0:17:6,5
will be supported in how exactly it will be implemented.
318
0:17:6,5 --> 0:17:10,02
But it's definitely worth thinking in the context of latencies
319
0:17:10,44 --> 0:17:13,44
and what should be offloaded and what tools we'll be using to
320
0:17:13,44 --> 0:17:15,78
offload in this, to background jobs, for example.
321
0:17:18,48 --> 0:17:24,72
It's just a thing that you need to, instead of thinking, looking
322
0:17:24,72 --> 0:17:30,08
at code, I would think looking at dynamic results, experiments.
323
0:17:31,64 --> 0:17:35,04
Michael: Yeah, and I'm thinking, like if we're talking specifically
324
0:17:35,4 --> 0:17:40,44
about schema design when you expect to hit scale, I might default
325
0:17:40,64 --> 0:17:47,56
to not using ON X CASCADE just by default because of those, because
326
0:17:47,56 --> 0:17:51,14
at scale it can become problematic so that would probably be,
327
0:17:51,34 --> 0:17:54,62
if you had to come up with a best practice for if you're designing
328
0:17:54,62 --> 0:17:56,58
at scale, maybe lean in that direction.
329
0:17:56,58 --> 0:18:0,22
Nikolay: That's interesting, that's interesting because This
330
0:18:0,22 --> 0:18:5,22
was my position for a long time, but it's not about AI building
331
0:18:5,22 --> 0:18:5,9
or something.
332
0:18:5,94 --> 0:18:6,92
This was my position.
333
0:18:7,04 --> 0:18:10,52
Let's, like, it's dangerous because who knows how many rows we
334
0:18:10,52 --> 0:18:11,46
will need to delete.
335
0:18:11,46 --> 0:18:13,98
Like, it's like it's unbounded delete, right?
336
0:18:14,06 --> 0:18:16,26
Unlimited delete, This is how it feels.
337
0:18:17,4 --> 0:18:18,8
Let's design without it.
338
0:18:18,8 --> 0:18:24,86
But then I see in my consulting practice, I see cases where really
339
0:18:24,86 --> 0:18:28,02
large projects have it and it's just not 1 project.
340
0:18:28,08 --> 0:18:31,96
Many projects I see and they keep it and somehow live with it.
341
0:18:31,96 --> 0:18:35,14
They at some point implement non-cascaded, like asynchronous
342
0:18:35,22 --> 0:18:39,22
deletion, but by default they still rely on cascaded deletes
343
0:18:39,22 --> 0:18:40,74
somehow and they are fine.
344
0:18:40,9 --> 0:18:41,68
This is interesting.
345
0:18:41,76 --> 0:18:44,28
So reality shows it's not that bad.
346
0:18:44,44 --> 0:18:48,34
But I agree with your way of thinking and this is my default
347
0:18:48,34 --> 0:18:49,66
way of thinking as well.
348
0:18:51,04 --> 0:18:55,6
Postgres can survive very terrible things these days much better
349
0:18:55,6 --> 0:18:57,1
than 20 years ago.
350
0:18:57,78 --> 0:18:58,78
Including the revolution
351
0:18:59,7 --> 0:19:0,06
of 100 years...
352
0:19:0,06 --> 0:19:0,48
Michael: The only
353
0:19:0,48 --> 0:19:4,44
time I see that being
a horrendous issue, even in small
354
0:19:4,44 --> 0:19:8,04
to medium-sized projects, is if
people haven't indexed the
355
0:19:8,48 --> 0:19:9,76
Nikolay: second part of foreign
key.
356
0:19:9,76 --> 0:19:11,3
Michael: Exactly, the referencing
column.
357
0:19:11,38 --> 0:19:12,88
Nikolay: There are 2 ends of foreign
key.
358
0:19:12,88 --> 0:19:18,42
1 is always indexed because it's
primary key, the other 1, it's
359
0:19:18,42 --> 0:19:20,04
your job to index it, right?
360
0:19:20,38 --> 0:19:21,48
To not forget.
361
0:19:22,06 --> 0:19:23,2
Yeah, yeah, yeah.
362
0:19:23,2 --> 0:19:25,52
We have now a checkup, old checkup,
actually.
363
0:19:25,52 --> 0:19:29,2
We have a special report just for
this case, yeah.
364
0:19:30,04 --> 0:19:35,06
Good, but it's all good questions,
but I feel we can spend a
365
0:19:35,06 --> 0:19:37,12
lot in the Constraints area.
366
0:19:37,12 --> 0:19:38,68
I think we had Constraints episodes.
367
0:19:38,68 --> 0:19:39,0
We did
368
0:19:39,0 --> 0:19:39,96
Michael: have an episode, yeah.
369
0:19:39,96 --> 0:19:41,74
Let's link that up and move on.
370
0:19:41,98 --> 0:19:42,86
Nikolay: Yeah, let's move on.
371
0:19:42,86 --> 0:19:47,18
Some entertaining topic, Column
Tetris, which is sometimes it
372
0:19:47,18 --> 0:19:51,3
might bring some quite interesting
and good savings.
373
0:19:52,12 --> 0:19:55,66
In many cases it won't, but I just
see people enjoy this topic,
374
0:19:55,94 --> 0:19:58,02
and I decided to include it.
375
0:19:58,02 --> 0:20:3,24
So the idea is, when you design
a new Schema, the price to pay
376
0:20:3,26 --> 0:20:5,16
to reorder Columns is very low.
377
0:20:5,16 --> 0:20:7,38
You can reorder it easily.
378
0:20:9,22 --> 0:20:13,48
The problem is that for Postgres,
physically, the Column order
379
0:20:13,48 --> 0:20:13,98
matters.
380
0:20:15,16 --> 0:20:19,92
Due to padding alignment, if int2, for example, is followed
381
0:20:19,92 --> 0:20:26,64
by int8, you will see a gap
of 6 bytes of zeros in every
382
0:20:26,64 --> 0:20:27,14
row.
383
0:20:27,34 --> 0:20:28,34
And this is bad.
384
0:20:28,58 --> 0:20:33,34
It's like a waste of storage and
also memory, which is not only
385
0:20:33,34 --> 0:20:36,54
disk, it will pollute with those
zeros, it will pollute memory.
386
0:20:36,58 --> 0:20:41,34
And sometimes you can be so unlucky,
I saw cases like 30% of
387
0:20:41,74 --> 0:20:43,48
each row is like zeros.
388
0:20:44,54 --> 0:20:48,96
It's, And Postgres doesn't have
automatic reordering of Columns.
389
0:20:48,96 --> 0:20:51,24
It could, actually, but it doesn't
have it.
390
0:20:51,9 --> 0:20:54,76
Michael: So the order you define
it in, let's say you do create
391
0:20:54,76 --> 0:20:58,78
table, the order you put those
Columns in, that's the order they
392
0:20:58,78 --> 0:20:59,76
end up on disk.
393
0:20:59,76 --> 0:21:3,14
And every new Column you add goes
at the end.
394
0:21:4,08 --> 0:21:5,28
That makes sense, right?
395
0:21:5,28 --> 0:21:8,68
But even if it could be slotted
in, let's say you add a new Boolean
396
0:21:8,68 --> 0:21:11,54
type, and it could, I don't actually
know if that would work,
397
0:21:11,54 --> 0:21:13,26
but it always goes on the end.
398
0:21:13,26 --> 0:21:14,044
Nikolay: And there's no way to
sum up.
399
0:21:14,044 --> 0:21:14,64
Boolean is 1 byte.
400
0:21:15,28 --> 0:21:17,64
Michael: Yeah, but I wonder if
that would work.
401
0:21:17,64 --> 0:21:19,58
Nikolay: In Boolean, 7 bits are
wasted already.
402
0:21:21,04 --> 0:21:22,96
If you zoom in like under my
403
0:21:22,96 --> 0:21:24,96
Michael: let's put brilliant brilliant
brilliant next to each
404
0:21:24,96 --> 0:21:25,66
other right
405
0:21:25,84 --> 0:21:28,18
Nikolay: no no no no no Boolean
406
0:21:28,18 --> 0:21:28,52
Michael: really
407
0:21:28,52 --> 0:21:29,74
Nikolay: is 1 bit.
408
0:21:30,16 --> 0:21:30,7
Michael: Oh bit.
409
0:21:30,7 --> 0:21:32,44
Yeah, good point good point, so
yeah,
410
0:21:32,64 --> 0:21:34,66
Nikolay: But it's stored as a 1
byte.
411
0:21:35,2 --> 0:21:37,36
7 bits already wasted.
412
0:21:37,8 --> 0:21:43,48
But if you have Boolean and then
timestamp, for example.
413
0:21:44,34 --> 0:21:48,58
For example, first column, as we
all agree, should be id, the
414
0:21:48,58 --> 0:21:49,56
primary key.
415
0:21:49,64 --> 0:21:52,56
I'm joking, it's not always, but
it's very popular.
416
0:21:52,9 --> 0:21:57,16
Id is the first column, int8, occupies the whole 8-byte
417
0:21:57,18 --> 0:21:57,68
word.
418
0:21:58,46 --> 0:22:0,38
And then we have Boolean and then
timestamp.
419
0:22:1,1 --> 0:22:5,98
Not only you wasted 7 bits inside
Boolean, it's already by design,
420
0:22:5,98 --> 0:22:9,12
like Postgres doesn't have less
than 1 byte.
421
0:22:10,02 --> 0:22:15,6
But then you waste 7 bytes, so
7 bits first and 7 bytes because
422
0:22:15,6 --> 0:22:17,06
of this alignment padding.
423
0:22:17,54 --> 0:22:23,0
And your Boolean, which is like,
by sense, it's only 1 bit, suddenly
424
0:22:23,0 --> 0:22:24,06
occupies 8 bytes.
425
0:22:24,06 --> 0:22:27,38
It's a huge waste of resources,
right?
426
0:22:27,72 --> 0:22:33,18
But the bigger picture here is
that people usually realize that
427
0:22:33,18 --> 0:22:37,48
waste is significant only when
it's a huge table, like 100 million
428
0:22:37,48 --> 0:22:38,2
rows, right?
429
0:22:38,44 --> 0:22:42,7
And then, oh, so many spaces wasted
because we didn't play this
430
0:22:42,7 --> 0:22:43,94
column Tetris initially.
431
0:22:45,06 --> 0:22:49,62
But this is like chicken or egg
problem, maybe inverted.
432
0:22:49,84 --> 0:22:54,02
Anyway, so you realize it later,
but you could do it earlier,
433
0:22:54,1 --> 0:22:56,96
but you didn't think about it because
when you have only 1,000
434
0:22:56,96 --> 0:22:59,32
rows, for example, it costs you
nothing.
435
0:23:0,18 --> 0:23:2,46
But again, with AI, it's so easy.
436
0:23:2,9 --> 0:23:5,3
You just say, let's play Column
Tetris in Postgres.
437
0:23:5,84 --> 0:23:7,04
AI should know it.
438
0:23:7,04 --> 0:23:9,88
I haven't tried, but I'm pretty
sure it should know it, because
439
0:23:9,88 --> 0:23:11,84
there are many articles about it.
440
0:23:12,12 --> 0:23:13,54
Michael: Oh, and we did an episode.
441
0:23:14,32 --> 0:23:17,08
Nikolay: Yeah, starting from Stack
Overflow, where this topic
442
0:23:17,08 --> 0:23:18,9
was very well described.
443
0:23:19,4 --> 0:23:22,16
So AI should know it, this topic.
444
0:23:22,66 --> 0:23:25,84
When you create a new table, you
think many rows will be stored,
445
0:23:25,84 --> 0:23:28,22
just let's apply column Tetris
to this.
446
0:23:28,48 --> 0:23:28,98
Boom.
447
0:23:29,38 --> 0:23:30,74
It will reorder columns.
448
0:23:32,2 --> 0:23:32,88
Never try it again.
449
0:23:32,88 --> 0:23:36,1
Michael: Yeah, what do you think,
like I feel like there's a...
450
0:23:37,58 --> 0:23:40,52
I don't know if I'd still want
to have, I'd still want to do
451
0:23:40,52 --> 0:23:46,32
it myself just because I feel like
there's also a natural order
452
0:23:46,32 --> 0:23:46,92
for columns.
453
0:23:46,92 --> 0:23:51,68
So I would want to put all the
8-byte ones together and 16-byte
454
0:23:51,76 --> 0:23:55,44
ones in to make sure they're all
with similar and then all of
455
0:23:55,44 --> 0:23:55,94
the...
456
0:23:56,54 --> 0:23:57,16
Yeah, exactly.
457
0:23:57,16 --> 0:23:58,04
Nikolay: Ah, similar.
458
0:23:59,06 --> 0:24:2,82
Michael: But equally, like, imagine
if it's like a users table
459
0:24:3,2 --> 0:24:8,72
and I could put the I could put
certain information about that
460
0:24:8,72 --> 0:24:12,08
user that makes sense to go together
together and then information
461
0:24:12,22 --> 0:24:15,92
about something like maybe like
there all the IDs that maybe
462
0:24:15,92 --> 0:24:19,24
the foreign keys like their team
ID or their organization, all
463
0:24:19,24 --> 0:24:22,58
those kind of like referencing
columns, put those together.
464
0:24:22,58 --> 0:24:25,78
So I know the order doesn't matter.
465
0:24:25,84 --> 0:24:28,86
I know you could just specify column
order, but you know when
466
0:24:28,86 --> 0:24:30,72
you do SELECT * or you just...
467
0:24:30,72 --> 0:24:34,34
Nikolay: This is what I was trying
to say, like, are you trying
468
0:24:34,34 --> 0:24:37,06
to tell me that you're using SELECT *?
469
0:24:38,04 --> 0:24:39,1
And here we go.
470
0:24:39,34 --> 0:24:41,28
So I'm joking, actually.
471
0:24:41,28 --> 0:24:43,16
I'm using SELECT * all the time.
472
0:24:44,34 --> 0:24:48,54
Also all the time I see articles
how bad it is to use SELECT *.
473
0:24:49,24 --> 0:24:49,74
Right?
474
0:24:50,14 --> 0:24:56,5
It's convenient, I know, yeah,
but also if you manually work,
475
0:24:56,98 --> 0:25:1,78
it's inconvenient to have order
and SELECT * doesn't work well.
476
0:25:1,84 --> 0:25:4,2
But applications shouldn't use
SELECT *.
477
0:25:4,2 --> 0:25:4,98
Michael: You're right.
478
0:25:5,08 --> 0:25:8,48
And I'm even thinking about examples
like the system views in
479
0:25:8,48 --> 0:25:8,86
Postgres.
480
0:25:8,86 --> 0:25:11,92
Like you do SELECT * FROM pg_stat_statements or something.
481
0:25:12,62 --> 0:25:14,12
But that's not even the table,
right?
482
0:25:14,12 --> 0:25:14,92
That's the view.
483
0:25:14,92 --> 0:25:16,4
And then they can define the order.
484
0:25:16,4 --> 0:25:18,3
So it doesn't actually matter.
485
0:25:19,12 --> 0:25:20,52
Nikolay: Yeah, it's an interesting
topic.
486
0:25:21,04 --> 0:25:24,66
I played Column Tetris a few times
heavily, and then it was really
487
0:25:24,66 --> 0:25:25,16
inconvenient.
488
0:25:25,68 --> 0:25:27,22
Oh, this is an ugly table.
489
0:25:27,6 --> 0:25:28,54
It looks ugly.
490
0:25:28,78 --> 0:25:30,4
Like, this order is ugly.
491
0:25:30,4 --> 0:25:33,34
But at the same time, it's your
problem that because you use
492
0:25:33,34 --> 0:25:36,1
SELECT *, which is considered
like not good practice.
493
0:25:36,28 --> 0:25:38,58
It's practice for exploring and
so on.
494
0:25:39,14 --> 0:25:42,8
Okay, explore with AI, ask to provide
better order or something.
495
0:25:42,8 --> 0:25:43,64
I don't know.
496
0:25:44,24 --> 0:25:47,84
But anyway, if you talk about,
if you focus on performance, again
497
0:25:47,84 --> 0:25:51,24
I saw cases where it was significant
waste.
498
0:25:51,42 --> 0:25:53,86
Michael: 1 of the first articles
I saw and it was by Braintree
499
0:25:53,86 --> 0:25:57,34
and I think they reported an average
of about 10% on some of
500
0:25:57,34 --> 0:25:58,44
their largest tables.
501
0:25:58,44 --> 0:25:59,0
Nikolay: It depends.
502
0:25:59,76 --> 0:26:2,48
Michael: Obviously it completely
depends but that was quite surprising
503
0:26:2,48 --> 0:26:2,68
to me.
504
0:26:2,68 --> 0:26:5,5
I know you mentioned an extreme
case where you saw 30%, but I
505
0:26:5,5 --> 0:26:9,96
was quite surprised that in an
actual production case, it was
506
0:26:9,96 --> 0:26:11,0
as high as 10%.
507
0:26:11,0 --> 0:26:13,68
And when you're talking about hundreds
of gigabytes or terabytes,
508
0:26:15,06 --> 0:26:16,36
That's actually a decent amount.
509
0:26:17,32 --> 0:26:18,3
Nikolay: Yeah, yeah, exactly.
510
0:26:18,52 --> 0:26:21,88
The problem also, of course, in
existing projects which were
511
0:26:21,88 --> 0:26:25,96
developed during many years, usually
it's not so simple because
512
0:26:25,96 --> 0:26:30,32
the schema is evolving during many
years and you cannot insert
513
0:26:30,32 --> 0:26:31,94
your new column in the middle.
514
0:26:32,04 --> 0:26:34,22
It's always, as you said, at the
end.
515
0:26:34,44 --> 0:26:38,9
So sometimes we do need to, for
example, to change the order
516
0:26:39,24 --> 0:26:42,08
using some redesign thing.
517
0:26:42,14 --> 0:26:48,78
By the way, have you heard that
Postgres 19 will have repack
518
0:26:48,84 --> 0:26:52,58
and also repack concurrently, which
is basically vacuum concurrently.
519
0:26:53,14 --> 0:26:55,4
We had an episode with author of
pg_squeeze.
520
0:26:56,2 --> 0:26:58,02
It's a vacuum full concurrently.
521
0:26:58,78 --> 0:26:59,76
Exactly, exactly.
522
0:26:59,76 --> 0:27:1,02
Not vacuum concurrently.
523
0:27:1,02 --> 0:27:4,78
But the decision was made to name
it Repack concurrently.
524
0:27:5,02 --> 0:27:7,78
Michael: I think that's sensible,
getting away from people thinking
525
0:27:7,78 --> 0:27:9,4
it's vacuum related, yeah.
526
0:27:9,86 --> 0:27:13,94
Nikolay: I haven't looked into
details, but naturally this is
527
0:27:13,94 --> 0:27:17,64
the point where you could reorder
columns, because it's based
528
0:27:17,64 --> 0:27:18,76
on logical replication.
529
0:27:19,74 --> 0:27:23,16
Michael: Yeah, I suspect if they
do that, it will be a different
530
0:27:23,16 --> 0:27:24,1
keyword again.
531
0:27:24,72 --> 0:27:25,46
Nikolay: Who knows?
532
0:27:25,6 --> 0:27:27,38
Again, I haven't looked into details.
533
0:27:27,44 --> 0:27:30,84
There are long discussions on pgsql-hackers
mailing list.
534
0:27:31,04 --> 0:27:34,54
It just feels naturally, because
it's based on logical replication.
535
0:27:34,54 --> 0:27:37,86
This is where you can reorder columns
and play Tetris for existing
536
0:27:37,86 --> 0:27:39,14
large tables.
537
0:27:39,62 --> 0:27:41,78
I hope it will be implemented at
some point.
538
0:27:41,78 --> 0:27:43,94
Maybe it's already partially implemented
or something.
539
0:27:43,94 --> 0:27:44,74
I don't know.
540
0:27:45,16 --> 0:27:49,54
You basically, before you start
filling the new table, you can
541
0:27:49,54 --> 0:27:51,52
reorder it, because it's logical,
right?
542
0:27:52,08 --> 0:27:53,22
It should be possible.
543
0:27:53,94 --> 0:27:56,4
Anyway, this is content, it's an
entertaining topic.
544
0:27:56,4 --> 0:28:0,68
Let's move on to a more serious
topic called indexes, index chaos.
545
0:28:1,34 --> 0:28:6,04
If you just use AI and if you don't
spend enough time for things
546
0:28:6,04 --> 0:28:11,3
as I said like experimenting with
real data benchmarks you might
547
0:28:11,38 --> 0:28:15,08
end up with under-indexed situation
or over-indexed situation
548
0:28:16,06 --> 0:28:18,98
And usually people are scared to
have under-indexed.
549
0:28:19,46 --> 0:28:22,16
In my practice, I see over-indexed
very often.
550
0:28:22,54 --> 0:28:23,74
Sometimes to extreme.
551
0:28:25,3 --> 0:28:28,3
Michael: I guess for new projects,
I would be surprised to see
552
0:28:28,3 --> 0:28:29,16
them over-indexed.
553
0:28:30,08 --> 0:28:30,76
But for- it's
554
0:28:30,76 --> 0:28:32,58
Nikolay: possible, it's possible
still.
555
0:28:32,78 --> 0:28:36,1
Michael: Is that like as a result
of AI or something else?
556
0:28:36,1 --> 0:28:39,62
Nikolay: Well, the problem here
is that, again, everything depends
557
0:28:39,62 --> 0:28:43,38
on your prompts, depends on angles.
558
0:28:43,68 --> 0:28:50,24
If you say we will need to order
by arbitrary column, right?
559
0:28:50,34 --> 0:28:53,92
Remember our episode with CTO Gadget,
Harry, right?
560
0:28:54,38 --> 0:28:56,42
So any column we can reorder.
561
0:28:57,44 --> 0:29:2,06
Naturally, AI might decide to put
index on every column, right?
562
0:29:2,86 --> 0:29:3,84
But is it really
563
0:29:3,84 --> 0:29:4,54
Michael: what you want?
564
0:29:4,54 --> 0:29:7,72
But that's what Gadget decided
to do as well, right?
565
0:29:7,8 --> 0:29:11,66
Nikolay: Right, but with understanding
consequences, right?
566
0:29:11,98 --> 0:29:17,68
But in reality, will it be used
If 1 year later you see that
567
0:29:17,68 --> 0:29:21,58
90% of those indexes are unused
and the gadget has special case
568
0:29:21,58 --> 0:29:25,84
They have many applications to
support if you design your own
569
0:29:25,84 --> 0:29:29,96
only 1 application This is where
I would just stop and think
570
0:29:29,96 --> 0:29:34,3
with AI again with some experiments
I would ask questions like
571
0:29:34,3 --> 0:29:36,46
this all depends on workload, right?
572
0:29:36,46 --> 0:29:39,66
Which queries we will have, right?
573
0:29:40,08 --> 0:29:44,34
And I would just think, which queries
we really need and which
574
0:29:44,34 --> 0:29:49,5
we won't need, and then choose
a proper minimal set of indexes.
575
0:29:51,6 --> 0:29:52,52
Michael: I like that.
576
0:29:52,64 --> 0:29:56,8
I think there's also like some
rules of thumb we can give people
577
0:29:56,8 --> 0:30:0,32
in terms of like how many is where
it starts to get dangerous
578
0:30:0,32 --> 0:30:1,2
for other reasons.
579
0:30:1,88 --> 0:30:6,72
But the kind of 1 place where it's
not even and it depends is
580
0:30:6,96 --> 0:30:11,4
overlapping and I see more and
more where people are adding indexes
581
0:30:11,58 --> 0:30:13,24
based on AI suggestions.
582
0:30:13,78 --> 0:30:16,72
They're adding indexes they've
effectively already got or they're
583
0:30:16,72 --> 0:30:20,8
adding ones that overlap with indexes
they've already got, and
584
0:30:20,8 --> 0:30:23,34
therefore only 1 of them is needed.
585
0:30:24,14 --> 0:30:26,52
And by the way, it won't always
show up in your stats, it won't
586
0:30:26,52 --> 0:30:29,8
always show up as an unused index,
because it is still using
587
0:30:29,8 --> 0:30:30,92
the smaller index.
588
0:30:31,56 --> 0:30:35,98
Nikolay: It will show up in PostgreSQL
stats because it's called
589
0:30:35,98 --> 0:30:39,52
in our case redundant index in
Postgres monitoring systems and
590
0:30:39,52 --> 0:30:40,02
CheckUp.
591
0:30:40,46 --> 0:30:42,42
I never heard the term overlapping.
592
0:30:43,38 --> 0:30:46,64
I see people talk about duplicate
indexes, which is like trivial
593
0:30:46,64 --> 0:30:48,9
case, exact same definition.
594
0:30:50,28 --> 0:30:52,2
But we call it redundant indexes.
595
0:30:52,2 --> 0:30:57,18
It's not an easy topic as 1 might
think, but we solved it in
596
0:30:57,18 --> 0:30:57,8
many parts.
597
0:30:57,8 --> 0:31:3,22
Over years, we have quite a good
detection approach, how to reliably
598
0:31:3,58 --> 0:31:5,14
detect redundant indexes.
599
0:31:5,86 --> 0:31:10,64
And as you say, this is true, in
many cases all of those indexes
600
0:31:10,64 --> 0:31:11,36
are used.
601
0:31:11,82 --> 0:31:16,56
And it's quite like, there is a
big fear, oh, I'm going to drop
602
0:31:16,56 --> 0:31:18,98
this, it's used, should I drop
it?
603
0:31:19,28 --> 0:31:23,82
Yeah, but the reports we have quite
reliable and of course I
604
0:31:23,82 --> 0:31:27,84
wish we had very native standard
algorithm to disable indexes
605
0:31:27,84 --> 0:31:32,32
for quite for some time like without
involving HypoPG and hypothetical
606
0:31:32,5 --> 0:31:35,46
indexes, which currently supports
it.
607
0:31:35,82 --> 0:31:38,14
Michael: Our last episode was on
what's missing in Postgres,
608
0:31:38,14 --> 0:31:41,5
and I think we got a nice comment,
I've forgotten who from, but
609
0:31:41,52 --> 0:31:45,26
saying 1 of their features would
be make this
610
0:31:45,26 --> 0:31:45,32
Nikolay: invisible.
611
0:31:45,32 --> 0:31:48,26
Temporally hide index from the
planner.
612
0:31:48,48 --> 0:31:52,54
There are extensions for it, HypoPG
supports it, but we want
613
0:31:52,54 --> 0:31:58,04
some mechanism beyond index setting,
and index is valid to false.
614
0:31:58,38 --> 0:31:59,14
We want this.
615
0:31:59,14 --> 0:32:0,66
Yeah, yeah, yeah, I agree.
616
0:32:0,96 --> 0:32:5,14
And this is a good thing to probably
work on for those who want
617
0:32:5,24 --> 0:32:9,8
to start hacking Postgres so it's
a good thing to officially implement
618
0:32:9,8 --> 0:32:10,3
something.
619
0:32:10,68 --> 0:32:15,94
Anyway, questions to ask is, they
all start here, should start
620
0:32:15,94 --> 0:32:17,36
from understanding workload.
621
0:32:17,86 --> 0:32:22,18
So we cannot think about indexes
without understanding workload.
622
0:32:22,28 --> 0:32:26,14
So first we need to generate some
fake data and then start thinking
623
0:32:26,38 --> 0:32:28,04
about usage patterns.
624
0:32:28,7 --> 0:32:30,92
And without AI It's really hard
work.
625
0:32:30,92 --> 0:32:32,06
It's a lot of time.
626
0:32:32,72 --> 0:32:34,8
With AI it becomes easier.
627
0:32:37,0 --> 0:32:38,7
If you have user stories defined
already, you understand what
628
0:32:38,7 --> 0:32:42,38
kind of patterns we will have,
and you can start imagining.
629
0:32:43,38 --> 0:32:47,78
It will not be 100% accurate, but
it will be good enough, much
630
0:32:47,78 --> 0:32:51,82
better than without it or with
manual work.
631
0:32:51,82 --> 0:32:56,4
So you can iterate here and see,
and then with queries in hand
632
0:32:56,84 --> 0:33:2,04
and some like lab already developed,
I see it should take 1 or
633
0:33:2,04 --> 0:33:7,08
2 hours for like medium-sized project
I mean in terms of complexity
634
0:33:7,42 --> 0:33:10,58
and then you can start iterating
collecting plans and think which
635
0:33:10,58 --> 0:33:12,32
indexes you can have playing
636
0:33:13,02 --> 0:33:15,58
Michael: But also this whilst this
might be the most important
637
0:33:15,58 --> 0:33:19,2
in terms of performance that we've
talked about so far it's also
638
0:33:19,2 --> 0:33:24,52
the the easiest to change later
right we can yeah yeah we can
639
0:33:24,52 --> 0:33:27,88
set up a certain set of indexes
and later decide we want a slightly
640
0:33:27,88 --> 0:33:30,92
different set of indexes and that's
not super painful in a lot
641
0:33:30,92 --> 0:33:34,54
in most cases especially if there's
no partitioning involved
642
0:33:35,28 --> 0:33:40,2
so that for me feels like yeah
it's important it's really important
643
0:33:40,58 --> 0:33:44,34
But if we don't get it right on
day 1, it's much, much easier
644
0:33:44,34 --> 0:33:47,82
to fix than the primary key being
the wrong data type or not
645
0:33:47,82 --> 0:33:50,34
having the right constraints in
place, that kind of thing.
646
0:33:50,72 --> 0:33:52,28
Or even the column order, yeah.
647
0:33:52,72 --> 0:33:56,82
Nikolay: So the point is you should
build like wind tunnel for
648
0:33:56,82 --> 0:33:58,04
your project, right?
649
0:33:58,86 --> 0:34:1,58
Like, you know, like, Test lab,
yeah.
650
0:34:1,86 --> 0:34:2,98
Database lab.
651
0:34:3,42 --> 0:34:7,42
And then you should put this workload like wind and see like
652
0:34:7,42 --> 0:34:11,94
how your profile of your database behaves under this wind.
653
0:34:12,28 --> 0:34:14,84
Michael: Yeah, a lot of people do that with production.
654
0:34:15,14 --> 0:34:17,7
They just do it with their slow queries.
655
0:34:18,94 --> 0:34:22,84
Nikolay: But with AI it's easier to have massive experiments
656
0:34:23,0 --> 0:34:25,06
before you finalize all the decisions.
657
0:34:25,38 --> 0:34:27,28
So my point is do it.
658
0:34:27,28 --> 0:34:30,72
And then questions to ask, are there any indexes we don't need
659
0:34:30,72 --> 0:34:33,9
in terms of they are redundant or unused or what indexes are
660
0:34:33,9 --> 0:34:36,72
missing because we have bad plans and this again like pgMustard
661
0:34:37,2 --> 0:34:40,62
will visualize it and so on and you have API already, right?
662
0:34:40,88 --> 0:34:42,94
Michael: Yeah Yeah, it's quite nice
663
0:34:43,14 --> 0:34:46,3
Nikolay: You can connect your AI to pgMustard for example and
664
0:34:46,3 --> 0:34:49,44
ask to use pgMustard to explain what's happening and find bad
665
0:34:49,44 --> 0:34:53,5
problems and missing indexes with this lab environment.
666
0:34:54,64 --> 0:34:55,12
Great?
667
0:34:55,12 --> 0:34:55,78
Michael: Yeah, OK.
668
0:34:57,1 --> 0:35:1,72
Going just like to the extreme, How many indexes on a table would
669
0:35:1,72 --> 0:35:5,7
you automatically just think, oh wow, that's like a lot?
670
0:35:6,22 --> 0:35:10,54
Maybe it's justified, but I'm already thinking that's too many.
671
0:35:10,92 --> 0:35:13,34
Nikolay: These rule of thumbs are quite weak in my opinion, but
672
0:35:13,34 --> 0:35:14,44
we have them anyway.
673
0:35:14,54 --> 0:35:17,44
Like for example, if the volume of indexes exceeds the volume
674
0:35:17,44 --> 0:35:20,88
of data, it's already some bell is ringing.
675
0:35:23,22 --> 0:35:27,72
Or before Postgres 18 with this nasty lightweight lock manager
676
0:35:27,72 --> 0:35:31,22
problem means I don't want more than 15 indexes per table.
677
0:35:31,96 --> 0:35:34,62
Michael: Because of the 16 relation limit.
678
0:35:34,94 --> 0:35:38,76
Nikolay: So simple, primary key lookup which quite likely will
679
0:35:38,76 --> 0:35:45,22
be very, will have quite high QPS will suffer, and because of
680
0:35:45,22 --> 0:35:45,72
locking.
681
0:35:46,26 --> 0:35:49,7
So there are a couple of kind of these rules, right, but they
682
0:35:49,7 --> 0:35:54,64
are not strict and again I say that I consider them weak, but
683
0:35:54,72 --> 0:35:55,22
maybe...
684
0:35:55,44 --> 0:35:57,78
Michael: But I think it's still helpful, right, if you've got
685
0:35:57,78 --> 0:36:2,56
a schema designed by AI and it's got 20 indexes on 1 table, maybe
686
0:36:2,56 --> 0:36:4,18
consider is that smart?
687
0:36:6,8 --> 0:36:11,3
Nikolay: But it happens, index data exceeding heap data it happens.
688
0:36:12,88 --> 0:36:15,6
Michael: Especially with some, especially like if you're doing
689
0:36:15,6 --> 0:36:16,34
gin indexes.
690
0:36:16,64 --> 0:36:17,34
Nikolay: Oh yes.
691
0:36:17,4 --> 0:36:19,54
Michael: If it's just B-trees, yeah.
692
0:36:20,38 --> 0:36:22,46
Nikolay: Yeah, okay, great.
693
0:36:22,54 --> 0:36:25,88
So the final topic, we couldn't decide on which 1 to choose.
694
0:36:25,88 --> 0:36:27,6
We had 2 ideas.
695
0:36:27,8 --> 0:36:30,7
1 is RLS and another is partitioning.
696
0:36:32,4 --> 0:36:33,6
Let's touch both maybe.
697
0:36:33,6 --> 0:36:34,4
Michael: Yeah I think so.
698
0:36:34,4 --> 0:36:39,3
So from an AI perspective, like when you're asking it for a new
699
0:36:39,3 --> 0:36:43,3
schema, do you see partitioning come up?
700
0:36:43,38 --> 0:36:44,4
Does it over partition?
701
0:36:44,4 --> 0:36:48,64
Nikolay: It won't come up Unless
you start saying I'm going to
702
0:36:48,64 --> 0:36:54,72
have a lot of data here and I want
good performance, I want billions
703
0:36:54,72 --> 0:36:58,04
of rows stored in this table, it
should be maintainable.
704
0:36:58,26 --> 0:37:1,3
It will naturally come to the idea
of having partitioning.
705
0:37:3,26 --> 0:37:7,32
Partitioning will be interesting
to join with UUID version 7 or
706
0:37:7,36 --> 0:37:8,9
8, it doesn't matter.
707
0:37:8,92 --> 0:37:13,12
It's possible, again, I have a
recipe for this in my set of how-tos.
708
0:37:14,06 --> 0:37:18,46
And it can be also the question,
like partitioning you cannot
709
0:37:18,46 --> 0:37:20,96
develop without understanding workload
again.
710
0:37:21,26 --> 0:37:24,62
It's similar to indexes, like it
will be something, but how it
711
0:37:24,62 --> 0:37:25,84
will behave, who knows?
712
0:37:25,84 --> 0:37:29,36
Maybe you will end up, your queries
will need to scan all partitions,
713
0:37:29,38 --> 0:37:31,36
which is terrible in most cases.
714
0:37:32,1 --> 0:37:34,24
And then plan behavior.
715
0:37:34,46 --> 0:37:37,7
Anyway, the question is to ask,
like you said requirements, I
716
0:37:37,7 --> 0:37:41,94
want a lot of rows and I want well
maintainability of this.
717
0:37:43,08 --> 0:37:43,82
Help me.
718
0:37:43,94 --> 0:37:46,2
Michael: Like deleting old data,
are you thinking?
719
0:37:46,72 --> 0:37:49,04
Nikolay: No, creating indexes,
for example, or reindexing.
720
0:37:49,12 --> 0:37:50,02
Michael: OK, yeah, sure.
721
0:37:50,02 --> 0:37:50,52
Vacuum.
722
0:37:50,9 --> 0:37:51,9
Nikolay: Vacuum, yeah, yeah.
723
0:37:51,9 --> 0:37:56,72
These are problems biting us much
more often and badly than just,
724
0:37:56,72 --> 0:37:59,76
I don't know, like, we talk about
this a lot, as well, right?
725
0:37:59,76 --> 0:38:0,7
Michael: I know, I know.
726
0:38:0,7 --> 0:38:1,2
Nikolay: Yeah.
727
0:38:2,38 --> 0:38:5,98
Direct performance benefits from
partitioning are good, but also
728
0:38:5,98 --> 0:38:13,32
the problem when creation of index
or full table vacuum, it will
729
0:38:13,32 --> 0:38:17,22
take hours or half a day, it's
already so painful.
730
0:38:17,24 --> 0:38:22,4
Especially if it's to prevent transaction
wraparound, or if it's,
731
0:38:22,7 --> 0:38:25,16
again, indexing means that
xmin horizon...
732
0:38:25,2 --> 0:38:27,42
Like anyway, let's not go too deep.
733
0:38:27,44 --> 0:38:31,12
The question to ask, will we survive
n number of rows because
734
0:38:31,12 --> 0:38:32,46
we expect all the data.
735
0:38:33,84 --> 0:38:34,52
I want-
736
0:38:34,54 --> 0:38:38,96
Michael: And just out of interest,
is it pretty much always doing
737
0:38:38,96 --> 0:38:41,7
range partitioning based on timestamp
type?
738
0:38:41,77 --> 0:38:42,52
Is that
739
0:38:42,52 --> 0:38:44,56
Nikolay: like- Again, I'm the wrong
person to ask.
740
0:38:44,56 --> 0:38:45,1
Michael: Okay, yeah, fair
741
0:38:45,1 --> 0:38:45,6
Nikolay: enough.
742
0:38:45,86 --> 0:38:49,74
In many cases, I just use to still
use TimescaleDB if it's time-based.
743
0:38:50,74 --> 0:38:51,54
Michael: Makes sense.
744
0:38:52,74 --> 0:38:55,16
Nikolay: And I'm happy because
there is compression there, it's
745
0:38:55,16 --> 0:38:55,66
great.
746
0:38:55,9 --> 0:39:0,08
But I also saw cases when it was
decided, not AI, but it was
747
0:39:0,08 --> 0:39:3,06
decided, like this partitioning
and full control over partitions,
748
0:39:3,42 --> 0:39:4,7
special table created.
749
0:39:4,9 --> 0:39:8,86
It was also working well and served
needs, but on surface, you
750
0:39:8,86 --> 0:39:13,02
should think about workload and
then push your AI or something
751
0:39:13,04 --> 0:39:17,06
to think about how it will behave
if you have that much data.
752
0:39:17,96 --> 0:39:19,86
Partitioning is inevitable there.
753
0:39:19,86 --> 0:39:21,14
And then experiments again.
754
0:39:21,28 --> 0:39:24,24
Even experiment with 1000000000
rows, it won't take a lot, just
755
0:39:24,24 --> 0:39:25,84
allocate machine and do it.
756
0:39:25,84 --> 0:39:28,78
It's like just so easy, right?
757
0:39:28,86 --> 0:39:33,04
Half an hour of wait to generate
it, or more like even if it's
758
0:39:33,04 --> 0:39:36,0
1 hour why I don't understand why
people don't do it all the
759
0:39:36,0 --> 0:39:40,58
time They come to they come to
us with questions, which I'm grateful.
760
0:39:40,58 --> 0:39:44,84
Thank you with money and so on
But it's so easy just to experiment
761
0:39:44,86 --> 0:39:46,2
more often these days
762
0:39:47,32 --> 0:39:50,0
Michael: Yeah, even when it even
leave it running overnight
763
0:39:51,0 --> 0:39:51,5
Nikolay: Yeah
764
0:39:53,98 --> 0:39:58,02
Michael: Yeah, I'm still I'm still
in the fence I love partitioning
765
0:39:58,04 --> 0:40:2,76
for the reasons you mentioned,
But I think so many projects won't
766
0:40:2,76 --> 0:40:6,96
ever need it that I understand
not doing it by default with a
767
0:40:6,96 --> 0:40:9,92
new project unless you know, unless
it's like a new feature for
768
0:40:9,92 --> 0:40:12,5
an existing system that you know
is going to get heavy amounts
769
0:40:12,5 --> 0:40:13,76
of data really quickly.
770
0:40:14,1 --> 0:40:17,22
Just there are so many trade-offs
like that it's still a little
771
0:40:17,22 --> 0:40:17,64
bit like...
772
0:40:17,64 --> 0:40:18,4
Nikolay: Which trade-offs?
773
0:40:20,18 --> 0:40:24,18
Michael: So for example indexes,
being able to create indexes
774
0:40:24,18 --> 0:40:26,9
concurrently, delete, drop indexes
concurrently.
775
0:40:27,98 --> 0:40:30,72
Nikolay: You can concurrently create
index on each partition
776
0:40:30,76 --> 0:40:34,04
and then yeah it exists on each
partition you can create this
777
0:40:34,04 --> 0:40:39,44
these things can be automated and
of course I like to be living
778
0:40:39,44 --> 0:40:42,94
much more better automated there
is a big potential here but
779
0:40:42,94 --> 0:40:47,78
also every Every Postgres version
gets a lot of improvements
780
0:40:47,86 --> 0:40:50,1
in this area, last maybe 10 years
already.
781
0:40:50,14 --> 0:40:53,94
So it's not like frozen.
782
0:40:54,66 --> 0:40:56,4
Yeah, but also make like,
783
0:40:57,34 --> 0:41:1,12
Michael: just being extra careful
that every high frequency query
784
0:41:1,12 --> 0:41:6,3
you have contains the partition
key and is getting pruned at
785
0:41:6,6 --> 0:41:9,5
planning time like there are a
few gotchas
786
0:41:9,64 --> 0:41:12,18
Nikolay: that hit you worse.
787
0:41:12,34 --> 0:41:12,5332
Michael: Yeah there are,
788
0:41:12,5332 --> 0:41:13,98
Nikolay: I have a series of articles
about this and I went quite
789
0:41:13,98 --> 0:41:15,92
deep there and it was crazy yeah.
790
0:41:16,42 --> 0:41:18,16
Michael: But that's my, they're
my hesitations.
791
0:41:19,2 --> 0:41:19,7
Nikolay: Creation
792
0:41:19,76 --> 0:41:20,72
Michael: of foreign keys.
793
0:41:22,42 --> 0:41:23,04
Nikolay: That's why I'm hesitant
for example working with timescale
794
0:41:23,04 --> 0:41:27,44
timescale DB something like often
we just Abandoned idea of having
795
0:41:27,44 --> 0:41:31,72
foreign key because yeah, it's
important If you do partitioning
796
0:41:31,72 --> 0:41:35,8
yourself, you will deal with, if
you have large tables, adding
797
0:41:35,8 --> 0:41:37,16
foreign key between them.
798
0:41:37,96 --> 0:41:41,06
If partitioning is involved, it's
an art, I would say.
799
0:41:41,14 --> 0:41:42,52
And GitLab mastered it.
800
0:41:42,52 --> 0:41:45,08
They have Migration Helpers RB.
801
0:41:45,48 --> 0:41:49,94
This is a great source of experience
of many people involved
802
0:41:49,94 --> 0:41:53,44
and I can't stop recommending how
great it is because it's open
803
0:41:53,44 --> 0:41:53,94
source.
804
0:41:54,38 --> 0:41:57,62
It's a great place to look at and
also they have great documentation
805
0:41:57,88 --> 0:41:58,58
about it.
806
0:41:58,58 --> 0:42:1,56
But anyway, it's a thing to decide.
807
0:42:1,56 --> 0:42:5,1
If you don't expect billions of
rows, don't do it, of course.
808
0:42:6,66 --> 0:42:11,26
At the same time, we have also
very sad cases when people come
809
0:42:11,26 --> 0:42:12,6
to us for consulting.
810
0:42:12,7 --> 0:42:15,6
We identify problems, we say partitioning
is needed.
811
0:42:16,22 --> 0:42:19,74
Sometimes we go and spend a lot
of effort because the project
812
0:42:19,74 --> 0:42:23,32
is huge or there are many databases
to take care of.
813
0:42:23,5 --> 0:42:26,5
And then finally it's implemented
great.
814
0:42:27,94 --> 0:42:31,26
There are many hidden dangers to
explore.
815
0:42:31,62 --> 0:42:36,04
Mid-journey, for example, they
came to us, helped, forgot about
816
0:42:36,04 --> 0:42:39,14
lightweight lock, well, not forgot,
we didn't know about that
817
0:42:39,14 --> 0:42:42,78
at that time, and they hit it,
lightweight LockManager problem
818
0:42:43,28 --> 0:42:47,72
was hit badly, and there are articles
about it.
819
0:42:48,1 --> 0:42:51,6
But then some projects just say,
okay, they evaluate how much
820
0:42:51,6 --> 0:42:54,78
effort it is to deal with partitioning
when tables are huge,
821
0:42:55,12 --> 0:42:58,06
and start talking, okay, maybe
we should migrate to my MongoDB.
822
0:42:59,72 --> 0:43:1,82
Michael: Yeah, Or even sharding,
right?
823
0:43:1,82 --> 0:43:6,82
Like instead, I think when we talked
to Notion, Arka, he mentioned
824
0:43:6,82 --> 0:43:10,44
that because they keep their shards
relatively small, they've
825
0:43:10,44 --> 0:43:12,68
opted to never partition at all.
826
0:43:12,74 --> 0:43:14,48
Nikolay: Yeah, the need for partitioning
diminishes.
827
0:43:15,04 --> 0:43:17,86
I agree with this, but not vanishes
completely.
828
0:43:18,96 --> 0:43:19,78
Michael: What, maybe?
829
0:43:20,34 --> 0:43:20,64
Nikolay: Not
830
0:43:20,64 --> 0:43:21,14
Michael: maybe.
831
0:43:21,28 --> 0:43:25,22
You mentioned, it depends how small
you keep your shards, right?
832
0:43:26,12 --> 0:43:27,68
Nikolay: Yeah, yeah.
833
0:43:28,08 --> 0:43:30,52
Michael: Like Sugu, for example,
Multigres, he talked about
834
0:43:30,52 --> 0:43:33,3
having lots of smaller databases
being much easier.
835
0:43:33,4 --> 0:43:34,42
Nikolay: Yeah, yeah, yeah, yeah.
836
0:43:34,64 --> 0:43:35,56
Maybe you're right.
837
0:43:36,34 --> 0:43:36,5
Michael: Okay.
838
0:43:36,5 --> 0:43:37,4
What about RLS?
839
0:43:37,54 --> 0:43:38,72
You mentioned you wanted to touch
on
840
0:43:38,72 --> 0:43:39,22
Nikolay: that.
841
0:43:39,38 --> 0:43:43,68
Yeah, finally, RLS, if you, for
example, I just noticed it's
842
0:43:43,68 --> 0:43:47,44
not like Supabase is very heavily
promoting RLS.
843
0:43:47,44 --> 0:43:51,02
They have a lot of articles including
how to avoid problems.
844
0:43:51,22 --> 0:43:56,6
But if you just use AI, I noticed
it often adds RLS.
845
0:43:57,18 --> 0:43:57,9
Michael: Oh, really?
846
0:43:58,26 --> 0:43:59,16
Nikolay: Yeah, I just...
847
0:43:59,16 --> 0:43:59,66
Interesting.
848
0:44:0,06 --> 0:44:0,92
This is how it works.
849
0:44:0,92 --> 0:44:4,86
I just noticed I had a couple of
cases where suddenly it brought
850
0:44:4,9 --> 0:44:6,9
like, we are going to use RLS.
851
0:44:6,9 --> 0:44:8,74
It's multi-tenancy here, let's
do it.
852
0:44:8,74 --> 0:44:12,58
Okay, let's do it, but let's also
pause and think about performance
853
0:44:13,38 --> 0:44:19,24
and avoid problems like current_setting
inside a loop and you
854
0:44:19,54 --> 0:44:23,2
SELECT count of million rows suddenly
becomes super slow.
855
0:44:23,42 --> 0:44:28,4
So the question to ask AI, again,
benchmark is ideal here.
856
0:44:30,06 --> 0:44:34,06
With RLS, without RLS, what is
the overhead of having RLS in
857
0:44:34,06 --> 0:44:34,92
our particular case?
858
0:44:34,92 --> 0:44:36,18
Let's benchmark it.
859
0:44:36,22 --> 0:44:39,72
And if you see it's not okay, then
let's optimize it, because
860
0:44:39,72 --> 0:44:41,82
there are tricks how to optimize
it.
861
0:44:43,66 --> 0:44:48,16
So if I notice an AI-generated
schema with RLS, I would ask question
862
0:44:48,16 --> 0:44:50,14
like, what will be performance
impact?
863
0:44:50,98 --> 0:44:53,74
Let's benchmark it, and if it's
bad, let's optimize.
864
0:44:53,8 --> 0:44:54,38
That's it.
865
0:44:54,38 --> 0:44:54,88
Yeah.
866
0:44:55,84 --> 0:44:56,34
Great.
867
0:44:57,22 --> 0:44:57,72
Great.
868
0:44:58,44 --> 0:44:59,32
That's it, actually.
869
0:44:59,38 --> 0:44:59,64
I think
870
0:44:59,64 --> 0:45:1,2
Michael: that's a useful checklist,
actually.
871
0:45:1,2 --> 0:45:5,72
Nikolay: 5 or 6 things to ask your
AI when you design schema.
872
0:45:7,66 --> 0:45:9,3
Michael: Yeah or your colleague
even.
873
0:45:10,24 --> 0:45:13,08
Nikolay: Yeah also or your own
schema which you have already
874
0:45:13,08 --> 0:45:14,06
10 years, why not.
875
0:45:14,06 --> 0:45:16,96
Michael: Yeah exactly or even a
new table if you're doing a new
876
0:45:16,96 --> 0:45:19,96
feature it still makes sense that
all of these make sense.
877
0:45:20,58 --> 0:45:21,24
Nikolay: I agree.
878
0:45:21,42 --> 0:45:23,5
Cool, thank you for listening.
879
0:45:24,16 --> 0:45:25,9
Yeah, see you soon.
0:0:0,06 --> 0:0:2,06
Nikolay: Hello, hello, this is
PostgresFM.
2
0:0:2,12 --> 0:0:7,24
My name is Nik, PostgresAI, and
as usual with me, Michael, pgMustard.
3
0:0:7,5 --> 0:0:8,299999
Hi, Michael.
4
0:0:9,28 --> 0:0:10,12
Michael: Hi, Nik.
How are you doing?
5
0:0:11,24 --> 0:0:12,139999
Nikolay: So the topic...
6
0:0:12,7 --> 0:0:14,28
I'm doing great, how are
you?
7
0:0:14,599999 --> 0:0:15,86
Michael: I'm good, Thank you.
8
0:0:15,94 --> 0:0:16,86
What's the topic?
9
0:0:17,26 --> 0:0:18,119999
Nikolay: What's the topic?
10
0:0:18,119999 --> 0:0:21,66
The topic I chose, and everyone
already saw it on cover you created,
11
0:0:21,66 --> 0:0:22,06
right?
12
0:0:22,06 --> 0:0:29,86
So the topic is 5 or so, maybe
6 things you should check when
13
0:0:29,86 --> 0:0:32,98
you start new schema or you already
have some schema and your
14
0:0:32,98 --> 0:0:38,32
project is growing and you want
to be better prepared for bigger
15
0:0:38,32 --> 0:0:40,02
scale and performance wise.
16
0:0:40,6 --> 0:0:45,32
So what, especially if you use
AI to design and improve your
17
0:0:45,32 --> 0:0:48,48
schema and your project is growing.
18
0:0:48,76 --> 0:0:55,02
So what should you look at to check
your schema health?
19
0:0:55,38 --> 0:0:56,14
Michael: Makes sense.
20
0:0:56,32 --> 0:1:0,2
Just on the topic in general, I
do think there are some things
21
0:1:0,2 --> 0:1:3,58
that are better suited to asking
for help from AI and not?
22
0:1:3,68 --> 0:1:7,58
Do you think like schema design
is 1 of them or actually is it
23
0:1:7,58 --> 0:1:11,32
1 of those ones that maybe we should
still do it a little bit
24
0:1:11,32 --> 0:1:13,12
more ourselves at the moment?
25
0:1:13,66 --> 0:1:18,04
Nikolay: Well as you know I'm very
very very intensive user of
26
0:1:18,04 --> 0:1:18,54
AI.
27
0:1:18,86 --> 0:1:19,36
Yeah.
28
0:1:20,28 --> 0:1:23,4
Honestly, like I do everything
through AI right now.
29
0:1:24,44 --> 0:1:29,24
And I'm 100% schema design is a
topic where AI can help a lot.
30
0:1:29,24 --> 0:1:32,36
But it also matters which questions
you ask.
31
0:1:32,98 --> 0:1:37,06
If you just say, I want this and
don't pay attention, you will
32
0:1:37,06 --> 0:1:39,64
have low quality schema in many
cases still.
33
0:1:40,44 --> 0:1:44,34
That's why I said, let's have this
episode because it might be
34
0:1:44,34 --> 0:1:48,74
quite basic for many advanced users
of Postgres, But this checklist
35
0:1:48,84 --> 0:1:53,08
we are going to present today,
it can be good thing to keep in
36
0:1:53,08 --> 0:1:56,82
mind always when you design schema,
just to ask right questions
37
0:1:56,94 --> 0:1:57,66
and revisit.
38
0:1:58,26 --> 0:2:1,4
Also, I'm not sure how exactly
you use AI.
39
0:2:1,4 --> 0:2:6,16
I use it always like I work with
people, developers.
40
0:2:7,4 --> 0:2:12,58
When you just ask something and
think those engineers, humans
41
0:2:12,58 --> 0:2:16,46
or AI, will solve all your problems
and make all the decisions,
42
0:2:17,1 --> 0:2:20,28
you're offloading full responsibility
to their shoulders, so
43
0:2:20,28 --> 0:2:24,36
then you have big negative surprises
always, right?
44
0:2:24,6 --> 0:2:29,64
So it's better to keep requests
as detailed as possible when
45
0:2:29,64 --> 0:2:34,48
you work with AI, and also make
some iterations to review and
46
0:2:34,48 --> 0:2:34,98
improve.
47
0:2:35,54 --> 0:2:38,92
And when making iterations, like
first, like I want some schema,
48
0:2:39,02 --> 0:2:44,12
like, I don't know, like LinkedIn
social media schema or something,
49
0:2:44,12 --> 0:2:44,62
right?
50
0:2:45,04 --> 0:2:48,98
It will give you immediately something,
but then you want some
51
0:2:49,22 --> 0:2:50,52
details to be checked.
52
0:2:50,54 --> 0:2:53,76
And this is exactly when this checklist can be very helpful.
53
0:2:54,02 --> 0:2:58,82
So you can revisit and think and imagine when you will have gigabytes
54
0:2:58,86 --> 0:3:1,58
and terabytes of data, what will happen.
55
0:3:1,86 --> 0:3:7,64
So to avoid painful refactoring in the future and outages maybe
56
0:3:7,64 --> 0:3:12,16
or performance degradation, the price you can pay in the beginning
57
0:3:12,16 --> 0:3:14,98
is much lower than the price you pay later.
58
0:3:16,96 --> 0:3:21,86
So I never work with AI in 1 shot, I always make iterations to
59
0:3:21,86 --> 0:3:25,08
improve in any question and to revisit and sometimes combine
60
0:3:25,08 --> 0:3:26,18
multiple LLMs.
61
0:3:28,26 --> 0:3:31,36
This is exactly when you can use this list of questions.
62
0:3:31,36 --> 0:3:35,58
So first topic is the data type of primary key.
63
0:3:36,68 --> 0:3:39,56
Michael: Yeah, and I feel like the answer to this has changed,
64
0:3:39,64 --> 0:3:43,9
or my opinion on what the best default for this has changed,
65
0:3:43,9 --> 0:3:49,2
actually, in the last few years, just off the back of learning
66
0:3:49,2 --> 0:3:53,4
more about timestamp ordered UUIDs, right?
67
0:3:53,56 --> 0:3:57,88
This used to be an age-old debate, right, UUID versus integer
68
0:3:57,88 --> 0:3:59,34
or big integer really.
69
0:4:0,06 --> 0:4:3,84
And most of the kind of blog posts I would see are like trying
70
0:4:3,84 --> 0:4:6,3
to just encourage people to use BIGINT over int.
71
0:4:7,06 --> 0:4:12,48
But now that we have first-class support for time-ordered UUIDs,
72
0:4:13,48 --> 0:4:15,92
I don't see many reasons not to go with that.
73
0:4:15,92 --> 0:4:16,8
What do you think?
74
0:4:16,8 --> 0:4:20,78
Nikolay: Well, the size, 8, is double.
75
0:4:21,04 --> 0:4:21,54
Yeah.
76
0:4:21,58 --> 0:4:24,94
Yeah, your ID is 16 bytes, so it's quite a big price to pay.
77
0:4:25,2 --> 0:4:29,44
Each row, you need to have 8 more bytes.
78
0:4:30,14 --> 0:4:31,015
It's not that much, is it?
79
0:4:31,015 --> 0:4:32,02
In many cases, it's fine.
80
0:4:32,2 --> 0:4:34,34
Well, indexes will be also bigger.
81
0:4:34,82 --> 0:4:36,3
Indexes double, yeah.
82
0:4:37,12 --> 0:4:39,98
It might make sense if volumes are huge.
83
0:4:40,12 --> 0:4:44,44
But anyway, to keep things simple, a rule to remember, red flags,
84
0:4:45,06 --> 0:4:49,5
If you are prepared for really large volumes of data, it must
85
0:4:49,5 --> 0:4:55,16
not be int4, it should be int8.
86
0:4:55,44 --> 0:5:2,24
And in terms of UUID path, data type is always UUID for both
87
0:5:2,24 --> 0:5:4,18
version 4 and version 7.
88
0:5:4,6 --> 0:5:8,7
But you can check default how it's generated.
89
0:5:8,94 --> 0:5:13,78
And in modern Postgres, there is a function, I think in 18, only
90
0:5:13,78 --> 0:5:14,6
in the latest version.
91
0:5:14,6 --> 0:5:15,02
I think
92
0:5:15,02 --> 0:5:15,26
Michael: so.
93
0:5:15,26 --> 0:5:17,44
It didn't quite make 17, did it?
94
0:5:17,44 --> 0:5:21,76
Nikolay: So if you're on Postgres 18, so it's better to use UUIDv7
95
0:5:21,76 --> 0:5:25,86
but also remembering trade-offs, because there is
96
0:5:25,86 --> 0:5:29,52
timestamp there, and the values will be ordered, like with integer
97
0:5:30,06 --> 0:5:31,08
or big integer.
98
0:5:31,4 --> 0:5:33,18
So there is a nuance here.
99
0:5:33,58 --> 0:5:37,12
But performance-wise it's much better than UUIDv4.
100
0:5:37,9 --> 0:5:41,54
There are many articles about it and you just need to check,
101
0:5:41,54 --> 0:5:44,36
okay, are we going to go with integer?
102
0:5:45,04 --> 0:5:48,7
Then it's int8 or BIGINT, right?
103
0:5:48,96 --> 0:5:50,64
Are we going to go with UUID?
104
0:5:51,06 --> 0:5:56,2
Then default generation, default
should be version 7 for Postgres
105
0:5:56,2 --> 0:5:56,58
18.
106
0:5:56,58 --> 0:5:59,92
For older version, it's also not
an excuse to stick with version
107
0:5:59,92 --> 0:6:0,28
4.
108
0:6:0,28 --> 0:6:2,0
You can write your own function.
109
0:6:2,9 --> 0:6:5,42
And you ask AI to write your own
function.
110
0:6:6,04 --> 0:6:8,1
Michael: Yes, or generate them
outside of the database.
111
0:6:8,3 --> 0:6:12,62
Nikolay: I slightly prefer this
option less because if you have
112
0:6:12,62 --> 0:6:17,08
multiple application nodes, Who
knows what will happen with their
113
0:6:17,08 --> 0:6:17,58
clocks?
114
0:6:18,58 --> 0:6:25,4
And since UUIDv7 includes
timestamp inside it, order might
115
0:6:25,4 --> 0:6:29,9
be not, I don't know, like I prefer
to leave it on database shoulders
116
0:6:29,9 --> 0:6:32,8
to have like on the primary single
source of true.
117
0:6:32,8 --> 0:6:37,4
On the other side, UUID by design
is generated, it serves purpose
118
0:6:37,44 --> 0:6:40,46
to work on multiple, like on distributed
systems, right?
119
0:6:40,58 --> 0:6:42,18
So maybe it's not a big deal.
120
0:6:42,18 --> 0:6:43,82
Generate on client side.
121
0:6:44,38 --> 0:6:47,68
Michael: All I mean is if you're
on an old Postgres and it saves
122
0:6:47,68 --> 0:6:49,78
you creating your own function
or whatever.
123
0:6:50,28 --> 0:6:55,36
Nikolay: Postgres waited a couple
of years to bring UUIDv7 officially.
124
0:6:56,0 --> 0:6:59,1
Although again, you can create
even SQL function, even not PL/pgSQL
125
0:6:59,1 --> 0:7:0,8
function, simple SQL function.
126
0:7:0,8 --> 0:7:4,64
And if you ask your AI to search
for example, probably it will
127
0:7:4,64 --> 0:7:8,8
find my page how to do it, but
or other people's pages.
128
0:7:9,44 --> 0:7:13,68
But in other libraries from Google,
from Node.js library, everything,
129
0:7:14,24 --> 0:7:17,74
they have it for a couple of years
already, so for longer.
130
0:7:18,12 --> 0:7:20,74
They implemented before standard
was finalized.
131
0:7:21,62 --> 0:7:24,92
Michael: Yeah, so huge red flags
to look out for if it's using
132
0:7:24,92 --> 0:7:28,64
the integer data type, which is
the same as int4, or serial.
133
0:7:29,02 --> 0:7:31,92
I think AI sometimes uses, it will
define it as serial.
134
0:7:31,92 --> 0:7:35,46
So if you just see that word without
bigserial, you might hit
135
0:7:35,46 --> 0:7:38,48
that limit at 2000000000 or whatever
the, I can't remember the
136
0:7:38,48 --> 0:7:39,52
exact upper limit.
137
0:7:39,56 --> 0:7:44,08
Nikolay: Yeah, and avoid UUID version
4 if you want good performance
138
0:7:44,1 --> 0:7:45,4
on large volumes of data.
139
0:7:45,4 --> 0:7:46,56
Yeah, that's it actually.
140
0:7:46,56 --> 0:7:50,04
Let's keep it simple because we
could discuss more advanced situations.
141
0:7:50,74 --> 0:7:54,44
Michael: I think we should do a
whole episode someday on migrations,
142
0:7:54,72 --> 0:7:57,34
like for people that are getting
close to that 2 billion
143
0:7:57,34 --> 0:7:58,26
Nikolay: number, that
144
0:7:58,26 --> 0:7:59,44
Michael: would be a good topic.
145
0:7:59,44 --> 0:8:2,12
Nikolay: I can share, I had a lot
of experience with different
146
0:8:2,12 --> 0:8:2,62
approaches.
147
0:8:3,66 --> 0:8:4,28
Let's do that.
148
0:8:4,28 --> 0:8:4,78
Good.
149
0:8:5,66 --> 0:8:6,98
So how to define primary key?
150
0:8:6,98 --> 0:8:10,46
I can actually have an article
about it, how to...
151
0:8:11,28 --> 0:8:11,78
Cool.
152
0:8:12,1 --> 0:8:12,74
Michael: Let's do that.
153
0:8:12,74 --> 0:8:15,58
Nikolay: Covering multiple paths so we could talk about this.
154
0:8:16,38 --> 0:8:18,28
Number 2 is constraints.
155
0:8:18,62 --> 0:8:19,7
Topic is constraints.
156
0:8:21,38 --> 0:8:22,32
Michael: Other constraints.
157
0:8:22,54 --> 0:8:22,94
Nikolay: Yeah, yeah.
158
0:8:22,94 --> 0:8:23,6
Yeah, other.
159
0:8:23,94 --> 0:8:25,32
Beyond primary key.
160
0:8:25,58 --> 0:8:25,9
Yeah.
161
0:8:25,9 --> 0:8:31,68
So I would just ask, first thing, are we using constraints well?
162
0:8:31,78 --> 0:8:33,84
Like to myself or AI and so on.
163
0:8:33,84 --> 0:8:38,26
And because Postgres has 6 types of constraints and it's quite
164
0:8:38,26 --> 0:8:45,56
rich tooling, and it's much more convenient to have them earlier,
165
0:8:46,08 --> 0:8:48,48
not because adding them later is a big pain.
166
0:8:48,48 --> 0:8:54,44
Right now we have quite good support of two-step constraint definitions.
167
0:8:55,4 --> 0:9:0,48
I think in Postgres 19 it will be possible also if not null constraints,
168
0:9:0,48 --> 0:9:1,36
which is interesting.
169
0:9:1,36 --> 0:9:5,14
But anyway, check constraints, foreign keys, unique constraints,
170
0:9:5,14 --> 0:9:10,02
it's possible to create first in almost finished state and then
171
0:9:10,02 --> 0:9:13,38
finalize it to avoid the long-lasting locks.
172
0:9:13,38 --> 0:9:17,96
But this is not the reason why I prefer to think about it in
173
0:9:17,96 --> 0:9:18,82
the very beginning.
174
0:9:18,96 --> 0:9:21,8
The main reason is the quality of data.
175
0:9:21,98 --> 0:9:24,82
This is why constraints exist in the first place, right?
176
0:9:25,44 --> 0:9:29,62
So if you introduce constraints early in your project, data quality
177
0:9:29,62 --> 0:9:30,3
is good.
178
0:9:31,84 --> 0:9:32,52
Introducing them
179
0:9:32,52 --> 0:9:33,32
Michael: later probably will...
180
0:9:33,32 --> 0:9:36,42
Yeah, and database side as well.
181
0:9:36,42 --> 0:9:37,4
I mean, it just...
182
0:9:37,64 --> 0:9:38,6
Nikolay: Oh yeah, yeah.
183
0:9:38,94 --> 0:9:40,78
I don't trust constraints outside.
184
0:9:43,26 --> 0:9:45,54
With age, I became less radical.
185
0:9:45,66 --> 0:9:49,18
My points of view became less radical with it.
186
0:9:49,18 --> 0:9:53,54
So I just observe without fighting, like with backend engineers
187
0:9:53,94 --> 0:9:57,74
who try to implement constraints on application side.
188
0:9:57,74 --> 0:10:1,16
I just have a note, like they are not reliable because who knows
189
0:10:1,44 --> 0:10:5,08
which other layers of types of applications, especially these
190
0:10:5,08 --> 0:10:9,72
days with AI, it's so easy to add something else, implement it
191
0:10:9,72 --> 0:10:12,56
in a different framework or even a language, it's super easy
192
0:10:12,56 --> 0:10:13,24
right now.
193
0:10:13,38 --> 0:10:16,5
And you will need to reimplement those constraints, or you will
194
0:10:16,5 --> 0:10:19,46
just forget and miss them and data quality will suffer.
195
0:10:21,04 --> 0:10:25,64
Michael: So specifically, something I think I'd like to see more
196
0:10:25,64 --> 0:10:30,26
of or at least I see a lot of people not doing is columns that
197
0:10:30,26 --> 0:10:32,8
you're not expecting to put any null values in, or you don't
198
0:10:32,8 --> 0:10:36,22
want to have any null values in, actually explicitly making them
199
0:10:36,22 --> 0:10:37,0
not null.
200
0:10:37,06 --> 0:10:40,96
Like, if you don't specify, then columns are nullable.
201
0:10:41,76 --> 0:10:45,84
And I think most people, or at
least most AIs I see, are creating,
202
0:10:46,1 --> 0:10:49,58
like by default, they don't put
NOT NULL on every column.
203
0:10:49,84 --> 0:10:52,7
Nikolay: Yeah, there's an opinion
that NOT NULL should be default.
204
0:10:53,2 --> 0:10:56,2
Anyway, it's hard to have a default,
but inside your project
205
0:10:56,2 --> 0:11:0,1
you can say, okay, unless it's
explicitly specified, let's make
206
0:11:0,1 --> 0:11:1,28
columns NOT NULL.
207
0:11:1,62 --> 0:11:2,12
Exactly.
208
0:11:3,16 --> 0:11:6,82
And avoid troubles with NULL we
talked about in a special episode
209
0:11:6,82 --> 0:11:8,08
about NULLs, right?
210
0:11:8,26 --> 0:11:12,72
Michael: Yeah, well and I guess
in this specific case there is
211
0:11:12,72 --> 0:11:17,58
also, well with some constraints,
mostly we care about, we're
212
0:11:17,58 --> 0:11:20,52
doing it for quality reasons, but
there is, there is like a tiny
213
0:11:20,52 --> 0:11:23,56
amount of performance optimization
that comes out of them.
214
0:11:23,56 --> 0:11:28,48
I guess a bit more for unique constraints,
like if you, if the
215
0:11:28,48 --> 0:11:31,32
database knows for sure this is
going to be, this is unique,
216
0:11:31,32 --> 0:11:32,58
it can do some optimizations.
217
0:11:33,32 --> 0:11:36,02
So yeah, anyway, but yeah, good
point about, I like that about
218
0:11:36,02 --> 0:11:36,52
NOT NULL.
219
0:11:37,84 --> 0:11:39,82
Nikolay: Yeah, yeah, unique is
interesting stuff.
220
0:11:39,96 --> 0:11:44,08
And also, I don't think foreign
keys are underused in Postgres
221
0:11:44,12 --> 0:11:44,62
ecosystem.
222
0:11:45,3 --> 0:11:49,34
Days when everyone suggested that
foreign keys are super bad,
223
0:11:49,54 --> 0:11:52,8
for me it's 20 years ago, I remember
these topics, let's avoid
224
0:11:52,8 --> 0:11:53,76
foreign keys completely.
225
0:11:54,52 --> 0:11:57,5
Although we know that foreign keys
can bite badly, we had an
226
0:11:57,5 --> 0:12:4,14
episode with Metronome company
about issues basically with MultiXact
227
0:12:4,76 --> 0:12:8,56
related issues which can be caused
when you use foreign keys
228
0:12:8,56 --> 0:12:9,06
a lot.
229
0:12:9,06 --> 0:12:12,68
But anyway, I still think foreign
keys is a good thing to have,
230
0:12:12,8 --> 0:12:13,54
data quality.
231
0:12:13,86 --> 0:12:18,3
But I think what is usually underused
is check constraints.
232
0:12:18,84 --> 0:12:19,7
They are great.
233
0:12:20,38 --> 0:12:22,72
They could be used much more often.
234
0:12:23,1 --> 0:12:25,9
And for example, a classic example,
when I say, like, when I
235
0:12:25,9 --> 0:12:31,08
see in schema ENUM data type,
I think, okay, later, If we want
236
0:12:31,08 --> 0:12:36,36
to change this list of possible
values, how will our migration?
237
0:12:37,36 --> 0:12:38,94
Michael: Especially removing any.
238
0:12:39,74 --> 0:12:40,24
Yeah,
239
0:12:40,52 --> 0:12:43,76
Nikolay: so check constraints are
slightly more flexible.
240
0:12:46,58 --> 0:12:49,44
Well, Also removing is a topic,
right?
241
0:12:49,44 --> 0:12:52,18
But check constraint you can introduce
in 2 stages.
242
0:12:52,92 --> 0:12:57,04
Again, you can drop the old 1 and
you can first create a new
243
0:12:57,04 --> 0:13:0,6
1 in NOT VALID state and then validate
it in a separate transaction.
244
0:13:1,56 --> 0:13:7,12
But I just see it like quite useful,
quite convenient tool for
245
0:13:7,12 --> 0:13:11,04
me and you see it in schema right
away like this this column
246
0:13:11,04 --> 0:13:15,36
should be like for example in this
range or it should be this
247
0:13:15,36 --> 0:13:16,36
list or something.
248
0:13:17,02 --> 0:13:20,7
So I wish it was used more often.
249
0:13:21,48 --> 0:13:21,98
Michael: Yeah.
250
0:13:22,82 --> 0:13:24,3
What do you see in...
251
0:13:24,32 --> 0:13:28,9
Going back to foreign keys quickly, do you see a lot of the LLMs
252
0:13:29,32 --> 0:13:33,8
doing a lot of things on update cascade, on delete set NULL,
253
0:13:33,8 --> 0:13:34,76
things like that.
254
0:13:34,76 --> 0:13:38,68
Do you see any cascade stuff popping in there by default or not
255
0:13:38,68 --> 0:13:39,97
when it should be or when you think it
256
0:13:39,97 --> 0:13:40,08
Nikolay: should be?
257
0:13:40,08 --> 0:13:41,06
It's a good question.
258
0:13:41,18 --> 0:13:44,94
I actually don't remember having issues with this question.
259
0:13:45,12 --> 0:13:51,26
I know deferred constraints can might be an issue.
260
0:13:51,46 --> 0:13:55,12
I remember a case when pg_repack, it was a company called Miro,
261
0:13:55,12 --> 0:13:56,42
maybe you know it, right?
262
0:13:56,72 --> 0:13:59,7
They had issues with pg_repack and deferred constraints.
263
0:13:59,7 --> 0:14:3,42
There is an article, old article from them about this.
264
0:14:4,12 --> 0:14:8,72
But I don't remember particular issues here, and I think this
265
0:14:8,72 --> 0:14:12,48
leads us actually to the question for our particular projects,
266
0:14:12,8 --> 0:14:13,64
what's better?
267
0:14:14,68 --> 0:14:19,14
And If I had any doubts with AI, I would just make some experiments.
268
0:14:19,9 --> 0:14:24,96
Based on the product we are developing, I would say, okay, for
269
0:14:24,96 --> 0:14:30,58
example, in 1 year, how many of entities in which entity table
270
0:14:30,58 --> 0:14:31,4
we expect?
271
0:14:32,32 --> 0:14:37,46
All other tables, let's fill it with fake data, run vacuum Analyze
272
0:14:37,58 --> 0:14:41,76
to have good state, and then just explore plans with pgMustard,
273
0:14:41,98 --> 0:14:42,68
for example.
274
0:14:44,16 --> 0:14:47,96
Let's think what kind of workload we might have according to
275
0:14:47,96 --> 0:14:51,4
user stories in our specification of the product and so on.
276
0:14:51,58 --> 0:14:54,3
And then collect plans and see, right?
277
0:14:54,52 --> 0:14:59,44
And then include also modifying queries and collect those plans
278
0:14:59,44 --> 0:15:0,92
and think how it will work.
279
0:15:1,02 --> 0:15:5,74
It's so easy these days to make these experiments before we finalize
280
0:15:5,74 --> 0:15:9,38
all the decisions and then you will see it in action and it's
281
0:15:9,38 --> 0:15:9,88
great.
282
0:15:10,26 --> 0:15:15,22
So collect all the plans, find like weak spots where we have
283
0:15:15,38 --> 0:15:18,84
suboptimal plans and how we can deal with this.
284
0:15:18,84 --> 0:15:23,94
If, for example, we have entity and another, and 1 too many,
285
0:15:24,14 --> 0:15:26,14
and it's many, so many, right?
286
0:15:26,18 --> 0:15:30,14
And then we want Delete to be supported in UI, where people,
287
0:15:30,14 --> 0:15:33,58
as we know, expect 200 milliseconds, less than 1 second, let's
288
0:15:33,58 --> 0:15:34,26
say, right?
289
0:15:34,3 --> 0:15:38,48
And when this Delete of 1 row triggers propagating according
290
0:15:38,48 --> 0:15:43,62
to foreign key triggers, deletion of 1000000 rows, it cannot
291
0:15:43,62 --> 0:15:45,08
meet our requirements, right?
292
0:15:45,08 --> 0:15:49,64
Now we need to think about asynchronous deletion, maybe using
293
0:15:49,64 --> 0:15:52,66
some event queue system or something, right?
294
0:15:52,8 --> 0:15:53,3
Yeah.
295
0:15:53,6 --> 0:15:57,78
And this will pop up if you prepare a good experiment here, right?
296
0:15:57,78 --> 0:15:59,8
Without experiment, it's hard to say.
297
0:15:59,9 --> 0:16:1,3
You need to answer questions.
298
0:16:2,08 --> 0:16:5,64
How big are the tables and how this relationship between tables
299
0:16:5,64 --> 0:16:8,98
on foreign keys will be in the worst case, for example.
300
0:16:8,98 --> 0:16:13,52
Can user create like million of entities and then we need to
301
0:16:13,52 --> 0:16:14,96
delete this user, for example.
302
0:16:14,96 --> 0:16:18,16
This is a great question and with AI you can iterate so fast
303
0:16:18,16 --> 0:16:18,9
these days.
304
0:16:18,9 --> 0:16:19,62
So great.
305
0:16:20,28 --> 0:16:22,16
Collect plans and then think and decide.
306
0:16:23,5 --> 0:16:28,28
Okay, it's a good question, but I don't see it as a simple question.
307
0:16:28,78 --> 0:16:29,38
Michael: No, no, no.
308
0:16:29,38 --> 0:16:32,68
It was more, I just asked in case you'd seen there was a chance
309
0:16:32,68 --> 0:16:37,54
that LLMs were throwing it in on every foreign key and That was
310
0:16:37,54 --> 0:16:40,64
maybe a bad like bad deal Oh, but it sounds like they're not
311
0:16:40,64 --> 0:16:43,26
Nikolay: maybe a wrong person to ask about this because I don't
312
0:16:43,26 --> 0:16:46,24
just I design sometimes these days I have a few applications
313
0:16:46,36 --> 0:16:49,9
designed Yeah created from scratch with AI but I mostly deal
314
0:16:49,9 --> 0:16:53,26
with things which other people already created, and I just see
315
0:16:53,26 --> 0:16:54,9
how AI can help.
316
0:16:55,32 --> 0:17:0,12
So I'm not sure will this propagation of deletion, for example,
317
0:17:0,12 --> 0:17:6,5
will be supported in how exactly it will be implemented.
318
0:17:6,5 --> 0:17:10,02
But it's definitely worth thinking in the context of latencies
319
0:17:10,44 --> 0:17:13,44
and what should be offloaded and what tools we'll be using to
320
0:17:13,44 --> 0:17:15,78
offload in this, to background jobs, for example.
321
0:17:18,48 --> 0:17:24,72
It's just a thing that you need to, instead of thinking, looking
322
0:17:24,72 --> 0:17:30,08
at code, I would think looking at dynamic results, experiments.
323
0:17:31,64 --> 0:17:35,04
Michael: Yeah, and I'm thinking, like if we're talking specifically
324
0:17:35,4 --> 0:17:40,44
about schema design when you expect to hit scale, I might default
325
0:17:40,64 --> 0:17:47,56
to not using ON X CASCADE just by default because of those, because
326
0:17:47,56 --> 0:17:51,14
at scale it can become problematic so that would probably be,
327
0:17:51,34 --> 0:17:54,62
if you had to come up with a best practice for if you're designing
328
0:17:54,62 --> 0:17:56,58
at scale, maybe lean in that direction.
329
0:17:56,58 --> 0:18:0,22
Nikolay: That's interesting, that's interesting because This
330
0:18:0,22 --> 0:18:5,22
was my position for a long time, but it's not about AI building
331
0:18:5,22 --> 0:18:5,9
or something.
332
0:18:5,94 --> 0:18:6,92
This was my position.
333
0:18:7,04 --> 0:18:10,52
Let's, like, it's dangerous because who knows how many rows we
334
0:18:10,52 --> 0:18:11,46
will need to delete.
335
0:18:11,46 --> 0:18:13,98
Like, it's like it's unbounded delete, right?
336
0:18:14,06 --> 0:18:16,26
Unlimited delete, This is how it feels.
337
0:18:17,4 --> 0:18:18,8
Let's design without it.
338
0:18:18,8 --> 0:18:24,86
But then I see in my consulting practice, I see cases where really
339
0:18:24,86 --> 0:18:28,02
large projects have it and it's just not 1 project.
340
0:18:28,08 --> 0:18:31,96
Many projects I see and they keep it and somehow live with it.
341
0:18:31,96 --> 0:18:35,14
They at some point implement non-cascaded, like asynchronous
342
0:18:35,22 --> 0:18:39,22
deletion, but by default they still rely on cascaded deletes
343
0:18:39,22 --> 0:18:40,74
somehow and they are fine.
344
0:18:40,9 --> 0:18:41,68
This is interesting.
345
0:18:41,76 --> 0:18:44,28
So reality shows it's not that bad.
346
0:18:44,44 --> 0:18:48,34
But I agree with your way of thinking and this is my default
347
0:18:48,34 --> 0:18:49,66
way of thinking as well.
348
0:18:51,04 --> 0:18:55,6
Postgres can survive very terrible things these days much better
349
0:18:55,6 --> 0:18:57,1
than 20 years ago.
350
0:18:57,78 --> 0:18:58,78
Including the revolution
351
0:18:59,7 --> 0:19:0,06
of 100 years...
352
0:19:0,06 --> 0:19:0,48
Michael: The only
353
0:19:0,48 --> 0:19:4,44
time I see that being
a horrendous issue, even in small
354
0:19:4,44 --> 0:19:8,04
to medium-sized projects, is if
people haven't indexed the
355
0:19:8,48 --> 0:19:9,76
Nikolay: second part of foreign
key.
356
0:19:9,76 --> 0:19:11,3
Michael: Exactly, the referencing
column.
357
0:19:11,38 --> 0:19:12,88
Nikolay: There are 2 ends of foreign
key.
358
0:19:12,88 --> 0:19:18,42
1 is always indexed because it's
primary key, the other 1, it's
359
0:19:18,42 --> 0:19:20,04
your job to index it, right?
360
0:19:20,38 --> 0:19:21,48
To not forget.
361
0:19:22,06 --> 0:19:23,2
Yeah, yeah, yeah.
362
0:19:23,2 --> 0:19:25,52
We have now a checkup, old checkup,
actually.
363
0:19:25,52 --> 0:19:29,2
We have a special report just for
this case, yeah.
364
0:19:30,04 --> 0:19:35,06
Good, but it's all good questions,
but I feel we can spend a
365
0:19:35,06 --> 0:19:37,12
lot in the Constraints area.
366
0:19:37,12 --> 0:19:38,68
I think we had Constraints episodes.
367
0:19:38,68 --> 0:19:39,0
We did
368
0:19:39,0 --> 0:19:39,96
Michael: have an episode, yeah.
369
0:19:39,96 --> 0:19:41,74
Let's link that up and move on.
370
0:19:41,98 --> 0:19:42,86
Nikolay: Yeah, let's move on.
371
0:19:42,86 --> 0:19:47,18
Some entertaining topic, Column
Tetris, which is sometimes it
372
0:19:47,18 --> 0:19:51,3
might bring some quite interesting
and good savings.
373
0:19:52,12 --> 0:19:55,66
In many cases it won't, but I just
see people enjoy this topic,
374
0:19:55,94 --> 0:19:58,02
and I decided to include it.
375
0:19:58,02 --> 0:20:3,24
So the idea is, when you design
a new Schema, the price to pay
376
0:20:3,26 --> 0:20:5,16
to reorder Columns is very low.
377
0:20:5,16 --> 0:20:7,38
You can reorder it easily.
378
0:20:9,22 --> 0:20:13,48
The problem is that for Postgres,
physically, the Column order
379
0:20:13,48 --> 0:20:13,98
matters.
380
0:20:15,16 --> 0:20:19,92
Due to padding alignment, if int2, for example, is followed
381
0:20:19,92 --> 0:20:26,64
by int8, you will see a gap
of 6 bytes of zeros in every
382
0:20:26,64 --> 0:20:27,14
row.
383
0:20:27,34 --> 0:20:28,34
And this is bad.
384
0:20:28,58 --> 0:20:33,34
It's like a waste of storage and
also memory, which is not only
385
0:20:33,34 --> 0:20:36,54
disk, it will pollute with those
zeros, it will pollute memory.
386
0:20:36,58 --> 0:20:41,34
And sometimes you can be so unlucky,
I saw cases like 30% of
387
0:20:41,74 --> 0:20:43,48
each row is like zeros.
388
0:20:44,54 --> 0:20:48,96
It's, And Postgres doesn't have
automatic reordering of Columns.
389
0:20:48,96 --> 0:20:51,24
It could, actually, but it doesn't
have it.
390
0:20:51,9 --> 0:20:54,76
Michael: So the order you define
it in, let's say you do create
391
0:20:54,76 --> 0:20:58,78
table, the order you put those
Columns in, that's the order they
392
0:20:58,78 --> 0:20:59,76
end up on disk.
393
0:20:59,76 --> 0:21:3,14
And every new Column you add goes
at the end.
394
0:21:4,08 --> 0:21:5,28
That makes sense, right?
395
0:21:5,28 --> 0:21:8,68
But even if it could be slotted
in, let's say you add a new Boolean
396
0:21:8,68 --> 0:21:11,54
type, and it could, I don't actually
know if that would work,
397
0:21:11,54 --> 0:21:13,26
but it always goes on the end.
398
0:21:13,26 --> 0:21:14,044
Nikolay: And there's no way to
sum up.
399
0:21:14,044 --> 0:21:14,64
Boolean is 1 byte.
400
0:21:15,28 --> 0:21:17,64
Michael: Yeah, but I wonder if
that would work.
401
0:21:17,64 --> 0:21:19,58
Nikolay: In Boolean, 7 bits are
wasted already.
402
0:21:21,04 --> 0:21:22,96
If you zoom in like under my
403
0:21:22,96 --> 0:21:24,96
Michael: let's put brilliant brilliant
brilliant next to each
404
0:21:24,96 --> 0:21:25,66
other right
405
0:21:25,84 --> 0:21:28,18
Nikolay: no no no no no Boolean
406
0:21:28,18 --> 0:21:28,52
Michael: really
407
0:21:28,52 --> 0:21:29,74
Nikolay: is 1 bit.
408
0:21:30,16 --> 0:21:30,7
Michael: Oh bit.
409
0:21:30,7 --> 0:21:32,44
Yeah, good point good point, so
yeah,
410
0:21:32,64 --> 0:21:34,66
Nikolay: But it's stored as a 1
byte.
411
0:21:35,2 --> 0:21:37,36
7 bits already wasted.
412
0:21:37,8 --> 0:21:43,48
But if you have Boolean and then
timestamp, for example.
413
0:21:44,34 --> 0:21:48,58
For example, first column, as we
all agree, should be id, the
414
0:21:48,58 --> 0:21:49,56
primary key.
415
0:21:49,64 --> 0:21:52,56
I'm joking, it's not always, but
it's very popular.
416
0:21:52,9 --> 0:21:57,16
Id is the first column, int8, occupies the whole 8-byte
417
0:21:57,18 --> 0:21:57,68
word.
418
0:21:58,46 --> 0:22:0,38
And then we have Boolean and then
timestamp.
419
0:22:1,1 --> 0:22:5,98
Not only you wasted 7 bits inside
Boolean, it's already by design,
420
0:22:5,98 --> 0:22:9,12
like Postgres doesn't have less
than 1 byte.
421
0:22:10,02 --> 0:22:15,6
But then you waste 7 bytes, so
7 bits first and 7 bytes because
422
0:22:15,6 --> 0:22:17,06
of this alignment padding.
423
0:22:17,54 --> 0:22:23,0
And your Boolean, which is like,
by sense, it's only 1 bit, suddenly
424
0:22:23,0 --> 0:22:24,06
occupies 8 bytes.
425
0:22:24,06 --> 0:22:27,38
It's a huge waste of resources,
right?
426
0:22:27,72 --> 0:22:33,18
But the bigger picture here is
that people usually realize that
427
0:22:33,18 --> 0:22:37,48
waste is significant only when
it's a huge table, like 100 million
428
0:22:37,48 --> 0:22:38,2
rows, right?
429
0:22:38,44 --> 0:22:42,7
And then, oh, so many spaces wasted
because we didn't play this
430
0:22:42,7 --> 0:22:43,94
column Tetris initially.
431
0:22:45,06 --> 0:22:49,62
But this is like chicken or egg
problem, maybe inverted.
432
0:22:49,84 --> 0:22:54,02
Anyway, so you realize it later,
but you could do it earlier,
433
0:22:54,1 --> 0:22:56,96
but you didn't think about it because
when you have only 1,000
434
0:22:56,96 --> 0:22:59,32
rows, for example, it costs you
nothing.
435
0:23:0,18 --> 0:23:2,46
But again, with AI, it's so easy.
436
0:23:2,9 --> 0:23:5,3
You just say, let's play Column
Tetris in Postgres.
437
0:23:5,84 --> 0:23:7,04
AI should know it.
438
0:23:7,04 --> 0:23:9,88
I haven't tried, but I'm pretty
sure it should know it, because
439
0:23:9,88 --> 0:23:11,84
there are many articles about it.
440
0:23:12,12 --> 0:23:13,54
Michael: Oh, and we did an episode.
441
0:23:14,32 --> 0:23:17,08
Nikolay: Yeah, starting from Stack
Overflow, where this topic
442
0:23:17,08 --> 0:23:18,9
was very well described.
443
0:23:19,4 --> 0:23:22,16
So AI should know it, this topic.
444
0:23:22,66 --> 0:23:25,84
When you create a new table, you
think many rows will be stored,
445
0:23:25,84 --> 0:23:28,22
just let's apply column Tetris
to this.
446
0:23:28,48 --> 0:23:28,98
Boom.
447
0:23:29,38 --> 0:23:30,74
It will reorder columns.
448
0:23:32,2 --> 0:23:32,88
Never try it again.
449
0:23:32,88 --> 0:23:36,1
Michael: Yeah, what do you think,
like I feel like there's a...
450
0:23:37,58 --> 0:23:40,52
I don't know if I'd still want
to have, I'd still want to do
451
0:23:40,52 --> 0:23:46,32
it myself just because I feel like
there's also a natural order
452
0:23:46,32 --> 0:23:46,92
for columns.
453
0:23:46,92 --> 0:23:51,68
So I would want to put all the
8-byte ones together and 16-byte
454
0:23:51,76 --> 0:23:55,44
ones in to make sure they're all
with similar and then all of
455
0:23:55,44 --> 0:23:55,94
the...
456
0:23:56,54 --> 0:23:57,16
Yeah, exactly.
457
0:23:57,16 --> 0:23:58,04
Nikolay: Ah, similar.
458
0:23:59,06 --> 0:24:2,82
Michael: But equally, like, imagine
if it's like a users table
459
0:24:3,2 --> 0:24:8,72
and I could put the I could put
certain information about that
460
0:24:8,72 --> 0:24:12,08
user that makes sense to go together
together and then information
461
0:24:12,22 --> 0:24:15,92
about something like maybe like
there all the IDs that maybe
462
0:24:15,92 --> 0:24:19,24
the foreign keys like their team
ID or their organization, all
463
0:24:19,24 --> 0:24:22,58
those kind of like referencing
columns, put those together.
464
0:24:22,58 --> 0:24:25,78
So I know the order doesn't matter.
465
0:24:25,84 --> 0:24:28,86
I know you could just specify column
order, but you know when
466
0:24:28,86 --> 0:24:30,72
you do SELECT * or you just...
467
0:24:30,72 --> 0:24:34,34
Nikolay: This is what I was trying
to say, like, are you trying
468
0:24:34,34 --> 0:24:37,06
to tell me that you're using SELECT *?
469
0:24:38,04 --> 0:24:39,1
And here we go.
470
0:24:39,34 --> 0:24:41,28
So I'm joking, actually.
471
0:24:41,28 --> 0:24:43,16
I'm using SELECT * all the time.
472
0:24:44,34 --> 0:24:48,54
Also all the time I see articles
how bad it is to use SELECT *.
473
0:24:49,24 --> 0:24:49,74
Right?
474
0:24:50,14 --> 0:24:56,5
It's convenient, I know, yeah,
but also if you manually work,
475
0:24:56,98 --> 0:25:1,78
it's inconvenient to have order
and SELECT * doesn't work well.
476
0:25:1,84 --> 0:25:4,2
But applications shouldn't use
SELECT *.
477
0:25:4,2 --> 0:25:4,98
Michael: You're right.
478
0:25:5,08 --> 0:25:8,48
And I'm even thinking about examples
like the system views in
479
0:25:8,48 --> 0:25:8,86
Postgres.
480
0:25:8,86 --> 0:25:11,92
Like you do SELECT * FROM pg_stat_statements or something.
481
0:25:12,62 --> 0:25:14,12
But that's not even the table,
right?
482
0:25:14,12 --> 0:25:14,92
That's the view.
483
0:25:14,92 --> 0:25:16,4
And then they can define the order.
484
0:25:16,4 --> 0:25:18,3
So it doesn't actually matter.
485
0:25:19,12 --> 0:25:20,52
Nikolay: Yeah, it's an interesting
topic.
486
0:25:21,04 --> 0:25:24,66
I played Column Tetris a few times
heavily, and then it was really
487
0:25:24,66 --> 0:25:25,16
inconvenient.
488
0:25:25,68 --> 0:25:27,22
Oh, this is an ugly table.
489
0:25:27,6 --> 0:25:28,54
It looks ugly.
490
0:25:28,78 --> 0:25:30,4
Like, this order is ugly.
491
0:25:30,4 --> 0:25:33,34
But at the same time, it's your
problem that because you use
492
0:25:33,34 --> 0:25:36,1
SELECT *, which is considered
like not good practice.
493
0:25:36,28 --> 0:25:38,58
It's practice for exploring and
so on.
494
0:25:39,14 --> 0:25:42,8
Okay, explore with AI, ask to provide
better order or something.
495
0:25:42,8 --> 0:25:43,64
I don't know.
496
0:25:44,24 --> 0:25:47,84
But anyway, if you talk about,
if you focus on performance, again
497
0:25:47,84 --> 0:25:51,24
I saw cases where it was significant
waste.
498
0:25:51,42 --> 0:25:53,86
Michael: 1 of the first articles
I saw and it was by Braintree
499
0:25:53,86 --> 0:25:57,34
and I think they reported an average
of about 10% on some of
500
0:25:57,34 --> 0:25:58,44
their largest tables.
501
0:25:58,44 --> 0:25:59,0
Nikolay: It depends.
502
0:25:59,76 --> 0:26:2,48
Michael: Obviously it completely
depends but that was quite surprising
503
0:26:2,48 --> 0:26:2,68
to me.
504
0:26:2,68 --> 0:26:5,5
I know you mentioned an extreme
case where you saw 30%, but I
505
0:26:5,5 --> 0:26:9,96
was quite surprised that in an
actual production case, it was
506
0:26:9,96 --> 0:26:11,0
as high as 10%.
507
0:26:11,0 --> 0:26:13,68
And when you're talking about hundreds
of gigabytes or terabytes,
508
0:26:15,06 --> 0:26:16,36
That's actually a decent amount.
509
0:26:17,32 --> 0:26:18,3
Nikolay: Yeah, yeah, exactly.
510
0:26:18,52 --> 0:26:21,88
The problem also, of course, in
existing projects which were
511
0:26:21,88 --> 0:26:25,96
developed during many years, usually
it's not so simple because
512
0:26:25,96 --> 0:26:30,32
the schema is evolving during many
years and you cannot insert
513
0:26:30,32 --> 0:26:31,94
your new column in the middle.
514
0:26:32,04 --> 0:26:34,22
It's always, as you said, at the
end.
515
0:26:34,44 --> 0:26:38,9
So sometimes we do need to, for
example, to change the order
516
0:26:39,24 --> 0:26:42,08
using some redesign thing.
517
0:26:42,14 --> 0:26:48,78
By the way, have you heard that
Postgres 19 will have repack
518
0:26:48,84 --> 0:26:52,58
and also repack concurrently, which
is basically vacuum concurrently.
519
0:26:53,14 --> 0:26:55,4
We had an episode with author of
pg_squeeze.
520
0:26:56,2 --> 0:26:58,02
It's a vacuum full concurrently.
521
0:26:58,78 --> 0:26:59,76
Exactly, exactly.
522
0:26:59,76 --> 0:27:1,02
Not vacuum concurrently.
523
0:27:1,02 --> 0:27:4,78
But the decision was made to name
it Repack concurrently.
524
0:27:5,02 --> 0:27:7,78
Michael: I think that's sensible,
getting away from people thinking
525
0:27:7,78 --> 0:27:9,4
it's vacuum related, yeah.
526
0:27:9,86 --> 0:27:13,94
Nikolay: I haven't looked into
details, but naturally this is
527
0:27:13,94 --> 0:27:17,64
the point where you could reorder
columns, because it's based
528
0:27:17,64 --> 0:27:18,76
on logical replication.
529
0:27:19,74 --> 0:27:23,16
Michael: Yeah, I suspect if they
do that, it will be a different
530
0:27:23,16 --> 0:27:24,1
keyword again.
531
0:27:24,72 --> 0:27:25,46
Nikolay: Who knows?
532
0:27:25,6 --> 0:27:27,38
Again, I haven't looked into details.
533
0:27:27,44 --> 0:27:30,84
There are long discussions on pgsql-hackers
mailing list.
534
0:27:31,04 --> 0:27:34,54
It just feels naturally, because
it's based on logical replication.
535
0:27:34,54 --> 0:27:37,86
This is where you can reorder columns
and play Tetris for existing
536
0:27:37,86 --> 0:27:39,14
large tables.
537
0:27:39,62 --> 0:27:41,78
I hope it will be implemented at
some point.
538
0:27:41,78 --> 0:27:43,94
Maybe it's already partially implemented
or something.
539
0:27:43,94 --> 0:27:44,74
I don't know.
540
0:27:45,16 --> 0:27:49,54
You basically, before you start
filling the new table, you can
541
0:27:49,54 --> 0:27:51,52
reorder it, because it's logical,
right?
542
0:27:52,08 --> 0:27:53,22
It should be possible.
543
0:27:53,94 --> 0:27:56,4
Anyway, this is content, it's an
entertaining topic.
544
0:27:56,4 --> 0:28:0,68
Let's move on to a more serious
topic called indexes, index chaos.
545
0:28:1,34 --> 0:28:6,04
If you just use AI and if you don't
spend enough time for things
546
0:28:6,04 --> 0:28:11,3
as I said like experimenting with
real data benchmarks you might
547
0:28:11,38 --> 0:28:15,08
end up with under-indexed situation
or over-indexed situation
548
0:28:16,06 --> 0:28:18,98
And usually people are scared to
have under-indexed.
549
0:28:19,46 --> 0:28:22,16
In my practice, I see over-indexed
very often.
550
0:28:22,54 --> 0:28:23,74
Sometimes to extreme.
551
0:28:25,3 --> 0:28:28,3
Michael: I guess for new projects,
I would be surprised to see
552
0:28:28,3 --> 0:28:29,16
them over-indexed.
553
0:28:30,08 --> 0:28:30,76
But for- it's
554
0:28:30,76 --> 0:28:32,58
Nikolay: possible, it's possible
still.
555
0:28:32,78 --> 0:28:36,1
Michael: Is that like as a result
of AI or something else?
556
0:28:36,1 --> 0:28:39,62
Nikolay: Well, the problem here
is that, again, everything depends
557
0:28:39,62 --> 0:28:43,38
on your prompts, depends on angles.
558
0:28:43,68 --> 0:28:50,24
If you say we will need to order
by arbitrary column, right?
559
0:28:50,34 --> 0:28:53,92
Remember our episode with CTO Gadget,
Harry, right?
560
0:28:54,38 --> 0:28:56,42
So any column we can reorder.
561
0:28:57,44 --> 0:29:2,06
Naturally, AI might decide to put
index on every column, right?
562
0:29:2,86 --> 0:29:3,84
But is it really
563
0:29:3,84 --> 0:29:4,54
Michael: what you want?
564
0:29:4,54 --> 0:29:7,72
But that's what Gadget decided
to do as well, right?
565
0:29:7,8 --> 0:29:11,66
Nikolay: Right, but with understanding
consequences, right?
566
0:29:11,98 --> 0:29:17,68
But in reality, will it be used
If 1 year later you see that
567
0:29:17,68 --> 0:29:21,58
90% of those indexes are unused
and the gadget has special case
568
0:29:21,58 --> 0:29:25,84
They have many applications to
support if you design your own
569
0:29:25,84 --> 0:29:29,96
only 1 application This is where
I would just stop and think
570
0:29:29,96 --> 0:29:34,3
with AI again with some experiments
I would ask questions like
571
0:29:34,3 --> 0:29:36,46
this all depends on workload, right?
572
0:29:36,46 --> 0:29:39,66
Which queries we will have, right?
573
0:29:40,08 --> 0:29:44,34
And I would just think, which queries
we really need and which
574
0:29:44,34 --> 0:29:49,5
we won't need, and then choose
a proper minimal set of indexes.
575
0:29:51,6 --> 0:29:52,52
Michael: I like that.
576
0:29:52,64 --> 0:29:56,8
I think there's also like some
rules of thumb we can give people
577
0:29:56,8 --> 0:30:0,32
in terms of like how many is where
it starts to get dangerous
578
0:30:0,32 --> 0:30:1,2
for other reasons.
579
0:30:1,88 --> 0:30:6,72
But the kind of 1 place where it's
not even and it depends is
580
0:30:6,96 --> 0:30:11,4
overlapping and I see more and
more where people are adding indexes
581
0:30:11,58 --> 0:30:13,24
based on AI suggestions.
582
0:30:13,78 --> 0:30:16,72
They're adding indexes they've
effectively already got or they're
583
0:30:16,72 --> 0:30:20,8
adding ones that overlap with indexes
they've already got, and
584
0:30:20,8 --> 0:30:23,34
therefore only 1 of them is needed.
585
0:30:24,14 --> 0:30:26,52
And by the way, it won't always
show up in your stats, it won't
586
0:30:26,52 --> 0:30:29,8
always show up as an unused index,
because it is still using
587
0:30:29,8 --> 0:30:30,92
the smaller index.
588
0:30:31,56 --> 0:30:35,98
Nikolay: It will show up in PostgreSQL
stats because it's called
589
0:30:35,98 --> 0:30:39,52
in our case redundant index in
Postgres monitoring systems and
590
0:30:39,52 --> 0:30:40,02
CheckUp.
591
0:30:40,46 --> 0:30:42,42
I never heard the term overlapping.
592
0:30:43,38 --> 0:30:46,64
I see people talk about duplicate
indexes, which is like trivial
593
0:30:46,64 --> 0:30:48,9
case, exact same definition.
594
0:30:50,28 --> 0:30:52,2
But we call it redundant indexes.
595
0:30:52,2 --> 0:30:57,18
It's not an easy topic as 1 might
think, but we solved it in
596
0:30:57,18 --> 0:30:57,8
many parts.
597
0:30:57,8 --> 0:31:3,22
Over years, we have quite a good
detection approach, how to reliably
598
0:31:3,58 --> 0:31:5,14
detect redundant indexes.
599
0:31:5,86 --> 0:31:10,64
And as you say, this is true, in
many cases all of those indexes
600
0:31:10,64 --> 0:31:11,36
are used.
601
0:31:11,82 --> 0:31:16,56
And it's quite like, there is a
big fear, oh, I'm going to drop
602
0:31:16,56 --> 0:31:18,98
this, it's used, should I drop
it?
603
0:31:19,28 --> 0:31:23,82
Yeah, but the reports we have quite
reliable and of course I
604
0:31:23,82 --> 0:31:27,84
wish we had very native standard
algorithm to disable indexes
605
0:31:27,84 --> 0:31:32,32
for quite for some time like without
involving HypoPG and hypothetical
606
0:31:32,5 --> 0:31:35,46
indexes, which currently supports
it.
607
0:31:35,82 --> 0:31:38,14
Michael: Our last episode was on
what's missing in Postgres,
608
0:31:38,14 --> 0:31:41,5
and I think we got a nice comment,
I've forgotten who from, but
609
0:31:41,52 --> 0:31:45,26
saying 1 of their features would
be make this
610
0:31:45,26 --> 0:31:45,32
Nikolay: invisible.
611
0:31:45,32 --> 0:31:48,26
Temporally hide index from the
planner.
612
0:31:48,48 --> 0:31:52,54
There are extensions for it, HypoPG
supports it, but we want
613
0:31:52,54 --> 0:31:58,04
some mechanism beyond index setting,
and index is valid to false.
614
0:31:58,38 --> 0:31:59,14
We want this.
615
0:31:59,14 --> 0:32:0,66
Yeah, yeah, yeah, I agree.
616
0:32:0,96 --> 0:32:5,14
And this is a good thing to probably
work on for those who want
617
0:32:5,24 --> 0:32:9,8
to start hacking Postgres so it's
a good thing to officially implement
618
0:32:9,8 --> 0:32:10,3
something.
619
0:32:10,68 --> 0:32:15,94
Anyway, questions to ask is, they
all start here, should start
620
0:32:15,94 --> 0:32:17,36
from understanding workload.
621
0:32:17,86 --> 0:32:22,18
So we cannot think about indexes
without understanding workload.
622
0:32:22,28 --> 0:32:26,14
So first we need to generate some
fake data and then start thinking
623
0:32:26,38 --> 0:32:28,04
about usage patterns.
624
0:32:28,7 --> 0:32:30,92
And without AI It's really hard
work.
625
0:32:30,92 --> 0:32:32,06
It's a lot of time.
626
0:32:32,72 --> 0:32:34,8
With AI it becomes easier.
627
0:32:37,0 --> 0:32:38,7
If you have user stories defined
already, you understand what
628
0:32:38,7 --> 0:32:42,38
kind of patterns we will have,
and you can start imagining.
629
0:32:43,38 --> 0:32:47,78
It will not be 100% accurate, but
it will be good enough, much
630
0:32:47,78 --> 0:32:51,82
better than without it or with
manual work.
631
0:32:51,82 --> 0:32:56,4
So you can iterate here and see,
and then with queries in hand
632
0:32:56,84 --> 0:33:2,04
and some like lab already developed,
I see it should take 1 or
633
0:33:2,04 --> 0:33:7,08
2 hours for like medium-sized project
I mean in terms of complexity
634
0:33:7,42 --> 0:33:10,58
and then you can start iterating
collecting plans and think which
635
0:33:10,58 --> 0:33:12,32
indexes you can have playing
636
0:33:13,02 --> 0:33:15,58
Michael: But also this whilst this
might be the most important
637
0:33:15,58 --> 0:33:19,2
in terms of performance that we've
talked about so far it's also
638
0:33:19,2 --> 0:33:24,52
the the easiest to change later
right we can yeah yeah we can
639
0:33:24,52 --> 0:33:27,88
set up a certain set of indexes
and later decide we want a slightly
640
0:33:27,88 --> 0:33:30,92
different set of indexes and that's
not super painful in a lot
641
0:33:30,92 --> 0:33:34,54
in most cases especially if there's
no partitioning involved
642
0:33:35,28 --> 0:33:40,2
so that for me feels like yeah
it's important it's really important
643
0:33:40,58 --> 0:33:44,34
But if we don't get it right on
day 1, it's much, much easier
644
0:33:44,34 --> 0:33:47,82
to fix than the primary key being
the wrong data type or not
645
0:33:47,82 --> 0:33:50,34
having the right constraints in
place, that kind of thing.
646
0:33:50,72 --> 0:33:52,28
Or even the column order, yeah.
647
0:33:52,72 --> 0:33:56,82
Nikolay: So the point is you should
build like wind tunnel for
648
0:33:56,82 --> 0:33:58,04
your project, right?
649
0:33:58,86 --> 0:34:1,58
Like, you know, like, Test lab,
yeah.
650
0:34:1,86 --> 0:34:2,98
Database lab.
651
0:34:3,42 --> 0:34:7,42
And then you should put this workload like wind and see like
652
0:34:7,42 --> 0:34:11,94
how your profile of your database behaves under this wind.
653
0:34:12,28 --> 0:34:14,84
Michael: Yeah, a lot of people do that with production.
654
0:34:15,14 --> 0:34:17,7
They just do it with their slow queries.
655
0:34:18,94 --> 0:34:22,84
Nikolay: But with AI it's easier to have massive experiments
656
0:34:23,0 --> 0:34:25,06
before you finalize all the decisions.
657
0:34:25,38 --> 0:34:27,28
So my point is do it.
658
0:34:27,28 --> 0:34:30,72
And then questions to ask, are there any indexes we don't need
659
0:34:30,72 --> 0:34:33,9
in terms of they are redundant or unused or what indexes are
660
0:34:33,9 --> 0:34:36,72
missing because we have bad plans and this again like pgMustard
661
0:34:37,2 --> 0:34:40,62
will visualize it and so on and you have API already, right?
662
0:34:40,88 --> 0:34:42,94
Michael: Yeah Yeah, it's quite nice
663
0:34:43,14 --> 0:34:46,3
Nikolay: You can connect your AI to pgMustard for example and
664
0:34:46,3 --> 0:34:49,44
ask to use pgMustard to explain what's happening and find bad
665
0:34:49,44 --> 0:34:53,5
problems and missing indexes with this lab environment.
666
0:34:54,64 --> 0:34:55,12
Great?
667
0:34:55,12 --> 0:34:55,78
Michael: Yeah, OK.
668
0:34:57,1 --> 0:35:1,72
Going just like to the extreme, How many indexes on a table would
669
0:35:1,72 --> 0:35:5,7
you automatically just think, oh wow, that's like a lot?
670
0:35:6,22 --> 0:35:10,54
Maybe it's justified, but I'm already thinking that's too many.
671
0:35:10,92 --> 0:35:13,34
Nikolay: These rule of thumbs are quite weak in my opinion, but
672
0:35:13,34 --> 0:35:14,44
we have them anyway.
673
0:35:14,54 --> 0:35:17,44
Like for example, if the volume of indexes exceeds the volume
674
0:35:17,44 --> 0:35:20,88
of data, it's already some bell is ringing.
675
0:35:23,22 --> 0:35:27,72
Or before Postgres 18 with this nasty lightweight lock manager
676
0:35:27,72 --> 0:35:31,22
problem means I don't want more than 15 indexes per table.
677
0:35:31,96 --> 0:35:34,62
Michael: Because of the 16 relation limit.
678
0:35:34,94 --> 0:35:38,76
Nikolay: So simple, primary key lookup which quite likely will
679
0:35:38,76 --> 0:35:45,22
be very, will have quite high QPS will suffer, and because of
680
0:35:45,22 --> 0:35:45,72
locking.
681
0:35:46,26 --> 0:35:49,7
So there are a couple of kind of these rules, right, but they
682
0:35:49,7 --> 0:35:54,64
are not strict and again I say that I consider them weak, but
683
0:35:54,72 --> 0:35:55,22
maybe...
684
0:35:55,44 --> 0:35:57,78
Michael: But I think it's still helpful, right, if you've got
685
0:35:57,78 --> 0:36:2,56
a schema designed by AI and it's got 20 indexes on 1 table, maybe
686
0:36:2,56 --> 0:36:4,18
consider is that smart?
687
0:36:6,8 --> 0:36:11,3
Nikolay: But it happens, index data exceeding heap data it happens.
688
0:36:12,88 --> 0:36:15,6
Michael: Especially with some, especially like if you're doing
689
0:36:15,6 --> 0:36:16,34
gin indexes.
690
0:36:16,64 --> 0:36:17,34
Nikolay: Oh yes.
691
0:36:17,4 --> 0:36:19,54
Michael: If it's just B-trees, yeah.
692
0:36:20,38 --> 0:36:22,46
Nikolay: Yeah, okay, great.
693
0:36:22,54 --> 0:36:25,88
So the final topic, we couldn't decide on which 1 to choose.
694
0:36:25,88 --> 0:36:27,6
We had 2 ideas.
695
0:36:27,8 --> 0:36:30,7
1 is RLS and another is partitioning.
696
0:36:32,4 --> 0:36:33,6
Let's touch both maybe.
697
0:36:33,6 --> 0:36:34,4
Michael: Yeah I think so.
698
0:36:34,4 --> 0:36:39,3
So from an AI perspective, like when you're asking it for a new
699
0:36:39,3 --> 0:36:43,3
schema, do you see partitioning come up?
700
0:36:43,38 --> 0:36:44,4
Does it over partition?
701
0:36:44,4 --> 0:36:48,64
Nikolay: It won't come up Unless
you start saying I'm going to
702
0:36:48,64 --> 0:36:54,72
have a lot of data here and I want
good performance, I want billions
703
0:36:54,72 --> 0:36:58,04
of rows stored in this table, it
should be maintainable.
704
0:36:58,26 --> 0:37:1,3
It will naturally come to the idea
of having partitioning.
705
0:37:3,26 --> 0:37:7,32
Partitioning will be interesting
to join with UUID version 7 or
706
0:37:7,36 --> 0:37:8,9
8, it doesn't matter.
707
0:37:8,92 --> 0:37:13,12
It's possible, again, I have a
recipe for this in my set of how-tos.
708
0:37:14,06 --> 0:37:18,46
And it can be also the question,
like partitioning you cannot
709
0:37:18,46 --> 0:37:20,96
develop without understanding workload
again.
710
0:37:21,26 --> 0:37:24,62
It's similar to indexes, like it
will be something, but how it
711
0:37:24,62 --> 0:37:25,84
will behave, who knows?
712
0:37:25,84 --> 0:37:29,36
Maybe you will end up, your queries
will need to scan all partitions,
713
0:37:29,38 --> 0:37:31,36
which is terrible in most cases.
714
0:37:32,1 --> 0:37:34,24
And then plan behavior.
715
0:37:34,46 --> 0:37:37,7
Anyway, the question is to ask,
like you said requirements, I
716
0:37:37,7 --> 0:37:41,94
want a lot of rows and I want well
maintainability of this.
717
0:37:43,08 --> 0:37:43,82
Help me.
718
0:37:43,94 --> 0:37:46,2
Michael: Like deleting old data,
are you thinking?
719
0:37:46,72 --> 0:37:49,04
Nikolay: No, creating indexes,
for example, or reindexing.
720
0:37:49,12 --> 0:37:50,02
Michael: OK, yeah, sure.
721
0:37:50,02 --> 0:37:50,52
Vacuum.
722
0:37:50,9 --> 0:37:51,9
Nikolay: Vacuum, yeah, yeah.
723
0:37:51,9 --> 0:37:56,72
These are problems biting us much
more often and badly than just,
724
0:37:56,72 --> 0:37:59,76
I don't know, like, we talk about
this a lot, as well, right?
725
0:37:59,76 --> 0:38:0,7
Michael: I know, I know.
726
0:38:0,7 --> 0:38:1,2
Nikolay: Yeah.
727
0:38:2,38 --> 0:38:5,98
Direct performance benefits from
partitioning are good, but also
728
0:38:5,98 --> 0:38:13,32
the problem when creation of index
or full table vacuum, it will
729
0:38:13,32 --> 0:38:17,22
take hours or half a day, it's
already so painful.
730
0:38:17,24 --> 0:38:22,4
Especially if it's to prevent transaction
wraparound, or if it's,
731
0:38:22,7 --> 0:38:25,16
again, indexing means that
xmin horizon...
732
0:38:25,2 --> 0:38:27,42
Like anyway, let's not go too deep.
733
0:38:27,44 --> 0:38:31,12
The question to ask, will we survive
n number of rows because
734
0:38:31,12 --> 0:38:32,46
we expect all the data.
735
0:38:33,84 --> 0:38:34,52
I want-
736
0:38:34,54 --> 0:38:38,96
Michael: And just out of interest,
is it pretty much always doing
737
0:38:38,96 --> 0:38:41,7
range partitioning based on timestamp
type?
738
0:38:41,77 --> 0:38:42,52
Is that
739
0:38:42,52 --> 0:38:44,56
Nikolay: like- Again, I'm the wrong
person to ask.
740
0:38:44,56 --> 0:38:45,1
Michael: Okay, yeah, fair
741
0:38:45,1 --> 0:38:45,6
Nikolay: enough.
742
0:38:45,86 --> 0:38:49,74
In many cases, I just use to still
use TimescaleDB if it's time-based.
743
0:38:50,74 --> 0:38:51,54
Michael: Makes sense.
744
0:38:52,74 --> 0:38:55,16
Nikolay: And I'm happy because
there is compression there, it's
745
0:38:55,16 --> 0:38:55,66
great.
746
0:38:55,9 --> 0:39:0,08
But I also saw cases when it was
decided, not AI, but it was
747
0:39:0,08 --> 0:39:3,06
decided, like this partitioning
and full control over partitions,
748
0:39:3,42 --> 0:39:4,7
special table created.
749
0:39:4,9 --> 0:39:8,86
It was also working well and served
needs, but on surface, you
750
0:39:8,86 --> 0:39:13,02
should think about workload and
then push your AI or something
751
0:39:13,04 --> 0:39:17,06
to think about how it will behave
if you have that much data.
752
0:39:17,96 --> 0:39:19,86
Partitioning is inevitable there.
753
0:39:19,86 --> 0:39:21,14
And then experiments again.
754
0:39:21,28 --> 0:39:24,24
Even experiment with 1000000000
rows, it won't take a lot, just
755
0:39:24,24 --> 0:39:25,84
allocate machine and do it.
756
0:39:25,84 --> 0:39:28,78
It's like just so easy, right?
757
0:39:28,86 --> 0:39:33,04
Half an hour of wait to generate
it, or more like even if it's
758
0:39:33,04 --> 0:39:36,0
1 hour why I don't understand why
people don't do it all the
759
0:39:36,0 --> 0:39:40,58
time They come to they come to
us with questions, which I'm grateful.
760
0:39:40,58 --> 0:39:44,84
Thank you with money and so on
But it's so easy just to experiment
761
0:39:44,86 --> 0:39:46,2
more often these days
762
0:39:47,32 --> 0:39:50,0
Michael: Yeah, even when it even
leave it running overnight
763
0:39:51,0 --> 0:39:51,5
Nikolay: Yeah
764
0:39:53,98 --> 0:39:58,02
Michael: Yeah, I'm still I'm still
in the fence I love partitioning
765
0:39:58,04 --> 0:40:2,76
for the reasons you mentioned,
But I think so many projects won't
766
0:40:2,76 --> 0:40:6,96
ever need it that I understand
not doing it by default with a
767
0:40:6,96 --> 0:40:9,92
new project unless you know, unless
it's like a new feature for
768
0:40:9,92 --> 0:40:12,5
an existing system that you know
is going to get heavy amounts
769
0:40:12,5 --> 0:40:13,76
of data really quickly.
770
0:40:14,1 --> 0:40:17,22
Just there are so many trade-offs
like that it's still a little
771
0:40:17,22 --> 0:40:17,64
bit like...
772
0:40:17,64 --> 0:40:18,4
Nikolay: Which trade-offs?
773
0:40:20,18 --> 0:40:24,18
Michael: So for example indexes,
being able to create indexes
774
0:40:24,18 --> 0:40:26,9
concurrently, delete, drop indexes
concurrently.
775
0:40:27,98 --> 0:40:30,72
Nikolay: You can concurrently create
index on each partition
776
0:40:30,76 --> 0:40:34,04
and then yeah it exists on each
partition you can create this
777
0:40:34,04 --> 0:40:39,44
these things can be automated and
of course I like to be living
778
0:40:39,44 --> 0:40:42,94
much more better automated there
is a big potential here but
779
0:40:42,94 --> 0:40:47,78
also every Every Postgres version
gets a lot of improvements
780
0:40:47,86 --> 0:40:50,1
in this area, last maybe 10 years
already.
781
0:40:50,14 --> 0:40:53,94
So it's not like frozen.
782
0:40:54,66 --> 0:40:56,4
Yeah, but also make like,
783
0:40:57,34 --> 0:41:1,12
Michael: just being extra careful
that every high frequency query
784
0:41:1,12 --> 0:41:6,3
you have contains the partition
key and is getting pruned at
785
0:41:6,6 --> 0:41:9,5
planning time like there are a
few gotchas
786
0:41:9,64 --> 0:41:12,18
Nikolay: that hit you worse.
787
0:41:12,34 --> 0:41:12,5332
Michael: Yeah there are,
788
0:41:12,5332 --> 0:41:13,98
Nikolay: I have a series of articles
about this and I went quite
789
0:41:13,98 --> 0:41:15,92
deep there and it was crazy yeah.
790
0:41:16,42 --> 0:41:18,16
Michael: But that's my, they're
my hesitations.
791
0:41:19,2 --> 0:41:19,7
Nikolay: Creation
792
0:41:19,76 --> 0:41:20,72
Michael: of foreign keys.
793
0:41:22,42 --> 0:41:23,04
Nikolay: That's why I'm hesitant
for example working with timescale
794
0:41:23,04 --> 0:41:27,44
timescale DB something like often
we just Abandoned idea of having
795
0:41:27,44 --> 0:41:31,72
foreign key because yeah, it's
important If you do partitioning
796
0:41:31,72 --> 0:41:35,8
yourself, you will deal with, if
you have large tables, adding
797
0:41:35,8 --> 0:41:37,16
foreign key between them.
798
0:41:37,96 --> 0:41:41,06
If partitioning is involved, it's
an art, I would say.
799
0:41:41,14 --> 0:41:42,52
And GitLab mastered it.
800
0:41:42,52 --> 0:41:45,08
They have Migration Helpers RB.
801
0:41:45,48 --> 0:41:49,94
This is a great source of experience
of many people involved
802
0:41:49,94 --> 0:41:53,44
and I can't stop recommending how
great it is because it's open
803
0:41:53,44 --> 0:41:53,94
source.
804
0:41:54,38 --> 0:41:57,62
It's a great place to look at and
also they have great documentation
805
0:41:57,88 --> 0:41:58,58
about it.
806
0:41:58,58 --> 0:42:1,56
But anyway, it's a thing to decide.
807
0:42:1,56 --> 0:42:5,1
If you don't expect billions of
rows, don't do it, of course.
808
0:42:6,66 --> 0:42:11,26
At the same time, we have also
very sad cases when people come
809
0:42:11,26 --> 0:42:12,6
to us for consulting.
810
0:42:12,7 --> 0:42:15,6
We identify problems, we say partitioning
is needed.
811
0:42:16,22 --> 0:42:19,74
Sometimes we go and spend a lot
of effort because the project
812
0:42:19,74 --> 0:42:23,32
is huge or there are many databases
to take care of.
813
0:42:23,5 --> 0:42:26,5
And then finally it's implemented
great.
814
0:42:27,94 --> 0:42:31,26
There are many hidden dangers to
explore.
815
0:42:31,62 --> 0:42:36,04
Mid-journey, for example, they
came to us, helped, forgot about
816
0:42:36,04 --> 0:42:39,14
lightweight lock, well, not forgot,
we didn't know about that
817
0:42:39,14 --> 0:42:42,78
at that time, and they hit it,
lightweight LockManager problem
818
0:42:43,28 --> 0:42:47,72
was hit badly, and there are articles
about it.
819
0:42:48,1 --> 0:42:51,6
But then some projects just say,
okay, they evaluate how much
820
0:42:51,6 --> 0:42:54,78
effort it is to deal with partitioning
when tables are huge,
821
0:42:55,12 --> 0:42:58,06
and start talking, okay, maybe
we should migrate to my MongoDB.
822
0:42:59,72 --> 0:43:1,82
Michael: Yeah, Or even sharding,
right?
823
0:43:1,82 --> 0:43:6,82
Like instead, I think when we talked
to Notion, Arka, he mentioned
824
0:43:6,82 --> 0:43:10,44
that because they keep their shards
relatively small, they've
825
0:43:10,44 --> 0:43:12,68
opted to never partition at all.
826
0:43:12,74 --> 0:43:14,48
Nikolay: Yeah, the need for partitioning
diminishes.
827
0:43:15,04 --> 0:43:17,86
I agree with this, but not vanishes
completely.
828
0:43:18,96 --> 0:43:19,78
Michael: What, maybe?
829
0:43:20,34 --> 0:43:20,64
Nikolay: Not
830
0:43:20,64 --> 0:43:21,14
Michael: maybe.
831
0:43:21,28 --> 0:43:25,22
You mentioned, it depends how small
you keep your shards, right?
832
0:43:26,12 --> 0:43:27,68
Nikolay: Yeah, yeah.
833
0:43:28,08 --> 0:43:30,52
Michael: Like Sugu, for example,
Multigres, he talked about
834
0:43:30,52 --> 0:43:33,3
having lots of smaller databases
being much easier.
835
0:43:33,4 --> 0:43:34,42
Nikolay: Yeah, yeah, yeah, yeah.
836
0:43:34,64 --> 0:43:35,56
Maybe you're right.
837
0:43:36,34 --> 0:43:36,5
Michael: Okay.
838
0:43:36,5 --> 0:43:37,4
What about RLS?
839
0:43:37,54 --> 0:43:38,72
You mentioned you wanted to touch
on
840
0:43:38,72 --> 0:43:39,22
Nikolay: that.
841
0:43:39,38 --> 0:43:43,68
Yeah, finally, RLS, if you, for
example, I just noticed it's
842
0:43:43,68 --> 0:43:47,44
not like Supabase is very heavily
promoting RLS.
843
0:43:47,44 --> 0:43:51,02
They have a lot of articles including
how to avoid problems.
844
0:43:51,22 --> 0:43:56,6
But if you just use AI, I noticed
it often adds RLS.
845
0:43:57,18 --> 0:43:57,9
Michael: Oh, really?
846
0:43:58,26 --> 0:43:59,16
Nikolay: Yeah, I just...
847
0:43:59,16 --> 0:43:59,66
Interesting.
848
0:44:0,06 --> 0:44:0,92
This is how it works.
849
0:44:0,92 --> 0:44:4,86
I just noticed I had a couple of
cases where suddenly it brought
850
0:44:4,9 --> 0:44:6,9
like, we are going to use RLS.
851
0:44:6,9 --> 0:44:8,74
It's multi-tenancy here, let's
do it.
852
0:44:8,74 --> 0:44:12,58
Okay, let's do it, but let's also
pause and think about performance
853
0:44:13,38 --> 0:44:19,24
and avoid problems like current_setting
inside a loop and you
854
0:44:19,54 --> 0:44:23,2
SELECT count of million rows suddenly
becomes super slow.
855
0:44:23,42 --> 0:44:28,4
So the question to ask AI, again,
benchmark is ideal here.
856
0:44:30,06 --> 0:44:34,06
With RLS, without RLS, what is
the overhead of having RLS in
857
0:44:34,06 --> 0:44:34,92
our particular case?
858
0:44:34,92 --> 0:44:36,18
Let's benchmark it.
859
0:44:36,22 --> 0:44:39,72
And if you see it's not okay, then
let's optimize it, because
860
0:44:39,72 --> 0:44:41,82
there are tricks how to optimize
it.
861
0:44:43,66 --> 0:44:48,16
So if I notice an AI-generated
schema with RLS, I would ask question
862
0:44:48,16 --> 0:44:50,14
like, what will be performance
impact?
863
0:44:50,98 --> 0:44:53,74
Let's benchmark it, and if it's
bad, let's optimize.
864
0:44:53,8 --> 0:44:54,38
That's it.
865
0:44:54,38 --> 0:44:54,88
Yeah.
866
0:44:55,84 --> 0:44:56,34
Great.
867
0:44:57,22 --> 0:44:57,72
Great.
868
0:44:58,44 --> 0:44:59,32
That's it, actually.
869
0:44:59,38 --> 0:44:59,64
I think
870
0:44:59,64 --> 0:45:1,2
Michael: that's a useful checklist,
actually.
871
0:45:1,2 --> 0:45:5,72
Nikolay: 5 or 6 things to ask your
AI when you design schema.
872
0:45:7,66 --> 0:45:9,3
Michael: Yeah or your colleague
even.
873
0:45:10,24 --> 0:45:13,08
Nikolay: Yeah also or your own
schema which you have already
874
0:45:13,08 --> 0:45:14,06
10 years, why not.
875
0:45:14,06 --> 0:45:16,96
Michael: Yeah exactly or even a
new table if you're doing a new
876
0:45:16,96 --> 0:45:19,96
feature it still makes sense that
all of these make sense.
877
0:45:20,58 --> 0:45:21,24
Nikolay: I agree.
878
0:45:21,42 --> 0:45:23,5
Cool, thank you for listening.
879
0:45:24,16 --> 0:45:25,9
Yeah, see you soon.
