1
0:0:0,099999994 --> 0:0:2,06
Nikolay: Hello, hello, this is Postgres FM.

2
0:0:2,28 --> 0:0:6,68
I'm Nik, PostgresAI, and as usual with me here is Michael,

3
0:0:6,8199997 --> 0:0:7,3199997
pgMustard.

4
0:0:7,7 --> 0:0:8,5
Hi, Michael.

5
0:0:9,4 --> 0:0:10,32
Michael: Hello, Nik.

6
0:0:10,32 --> 0:0:11,179999
How's it going?

7
0:0:11,92 --> 0:0:12,54
Nikolay: Very good.

8
0:0:12,54 --> 0:0:13,38
How are you?

9
0:0:13,94 --> 0:0:15,18
Michael: I am good, thank you.

10
0:0:15,18 --> 0:0:18,08
Nikolay: We haven't recorded last week because I was on trip

11
0:0:18,08 --> 0:0:23,54
in Oregon forest having some fun disconnected from internet mostly

12
0:0:24,66 --> 0:0:28,24
Yeah, so now I return you said that we should discuss sequences

13
0:0:28,259998 --> 0:0:29,119999
somehow, right?

14
0:0:30,36 --> 0:0:35,46
Michael: Yeah, so I was looking back through our listener suggestions.

15
0:0:35,68 --> 0:0:39,18
So we get, we've got a Google doc where we encourage people to

16
0:0:39,239998 --> 0:0:42,48
comment and add ideas for us to discuss topics.

17
0:0:43,52 --> 0:0:46,940002
And whenever I'm short of ideas, I love checking back through

18
0:0:46,940002 --> 0:0:47,2
that.

19
0:0:47,2 --> 0:0:51,0
And 1 of them from quite a long time ago actually caught my eye

20
0:0:51,04 --> 0:0:57,18
and it was the concept of gapless sequences and I guess this

21
0:0:57,18 --> 0:0:59,68
might this might be a couple of different things but I found

22
0:0:59,68 --> 0:1:2,56
it interesting both from like a theoretical point of view, but

23
0:1:2,56 --> 0:1:6,82
also in terms of practical solutions, as well as being 1 of those

24
0:1:6,82 --> 0:1:11,58
things that's kind of, so a sequence with gaps is 1 of those

25
0:1:11,58 --> 0:1:13,94
things that catches most engineers eye.

26
0:1:13,94 --> 0:1:19,24
Like if you start to run a production Postgres, you will see

27
0:1:19,54 --> 0:1:24,64
occasionally an incrementing ID and then a gap in it.

28
0:1:24,64 --> 0:1:25,92
And you think, what happened there?

29
0:1:25,92 --> 0:1:29,02
So it's 1 of those things I think most of us have come across

30
0:1:29,02 --> 0:1:31,72
at some point and been intrigued by.

31
0:1:31,92 --> 0:1:34,18
So yeah, there's a few interesting causes of that.

32
0:1:35,8 --> 0:1:38,92
Nikolay: Name sequence should mean it's sequential.

33
0:1:40,92 --> 0:1:41,96
Why the gap?

34
0:1:42,26 --> 0:1:42,94
It's unexpected.

35
0:1:44,1 --> 0:1:49,28
And by the way, this episode, is it number 163 or because I missed

36
0:1:49,28 --> 0:1:51,54
last week it will be number 164

37
0:1:53,24 --> 0:1:56,2
Michael: Do you know what, it would be quite funny to, should

38
0:1:56,2 --> 0:1:58,375
we increment the episode count?

39
0:1:58,375 --> 0:2:1,3
Nikolay: Yeah, what's the number because I was telling, yeah

40
0:2:1,78 --> 0:2:5,72
Michael: Either we should do yeah 164 missing 1 and then do 163

41
0:2:5,94 --> 0:2:9,94
next week as like a joke, because it's like coming in too late,

42
0:2:10,26 --> 0:2:12,98
or we just carry on increasing the number.

43
0:2:12,98 --> 0:2:16,16
Nikolay: This is another anomaly you can observe sometimes, because

44
0:2:16,16 --> 0:2:24,18
at commit time you can see like we have many users they can use

45
0:2:24,96 --> 0:2:28,7
next numbers from sequence all the time and Then at commit time

46
0:2:28,7 --> 0:2:31,42
the order is not, it can be different of course, right?

47
0:2:31,42 --> 0:2:34,2
Michael: Yeah, I forget the name for it, but whatever it whatever

48
0:2:34,2 --> 0:2:36,94
that phenomenon is but whatever is we should discuss that next

49
0:2:36,94 --> 0:2:38,1
week and have the...

50
0:2:38,1 --> 0:2:42,16
Nikolay: It's not serializable right so it's yeah it's not serialized

51
0:2:42,16 --> 0:2:46,72
so if you have 2 transactions and
1 for example you have support

52
0:2:47,02 --> 0:2:52,82
system, ticket tracking system,
and you generate ticket numbers,

53
0:2:53,36 --> 0:2:54,26
you think sequentially.

54
0:2:55,08 --> 0:2:58,94
1 user came, opened a ticket, but
haven't committed yet.

55
0:2:59,1 --> 0:3:3,24
Another user came, opened ticket,
and that ticket has bigger

56
0:3:3,24 --> 0:3:5,1
number, next 1, right?

57
0:3:5,1 --> 0:3:7,36
And then committed already, and
then you committed this, you

58
0:3:7,36 --> 0:3:11,74
see 1 ticket created before that
1 right but at the same time

59
0:3:11,74 --> 0:3:16,74
if you generate timestamp automatically
with like created at

60
0:3:16,74 --> 0:3:21,68
time created at Column with with
default clock timestamp or something

61
0:3:21,68 --> 0:3:25,68
and it was it INSERT happened at
the same time when a Sequence

62
0:3:25,68 --> 0:3:27,02
nextval call happened.

63
0:3:27,28 --> 0:3:32,1
In that case, created at values
will have the same order as Sequence

64
0:3:32,1 --> 0:3:33,88
values, like ID column values.

65
0:3:33,88 --> 0:3:34,18
Right.

66
0:3:34,18 --> 0:3:37,2
So there will, there will not be
a problem when you order by

67
0:3:37,2 --> 0:3:41,04
those tickets but normally can
be understood oh there is a ticket

68
0:3:41,04 --> 0:3:46,1
number like 10 and then number
9 visible layer because we don't

69
0:3:46,1 --> 0:3:50,82
see uncommitted writes right So
it should be committed first

70
0:3:50,82 --> 0:3:53,6
before it's visible to other transactions,
other sessions.

71
0:3:54,78 --> 0:3:55,08
Yeah.

72
0:3:55,08 --> 0:3:55,58
Yeah.

73
0:3:55,9 --> 0:3:58,98
But this is different anomaly,
like gapless gaps.

74
0:3:58,98 --> 0:4:3,08
It's this Anomaly is very well
known because for the sake of

75
0:4:3,08 --> 0:4:6,82
performance, Sequence, this mechanism
in Postgres exists for

76
0:4:6,82 --> 0:4:10,8
ages and it just gives you next
number all the time and of course

77
0:4:10,8 --> 0:4:14,58
if you for example decided to Rollback
your Transaction you

78
0:4:14,58 --> 0:4:15,54
lost that value.

79
0:4:16,1 --> 0:4:19,22
So this is number 1 thing.

80
0:4:19,82 --> 0:4:20,58
Michael: Yes, exactly.

81
0:4:20,66 --> 0:4:24,06
So it's to allow for concurrent
rights, isn't it?

82
0:4:24,06 --> 0:4:29,04
So if you've got, imagine like
within a microsecond, 2 of us

83
0:4:29,18 --> 0:4:35,32
trying to INSERT to the same Table,
if my if I am just before

84
0:4:35,32 --> 0:4:39,74
you and I get assigned the next
value in a Sequence and then

85
0:4:39,84 --> 0:4:45,74
my Transaction fails and is rolled
back you've already been assigned

86
0:4:45,74 --> 0:4:49,3
the next value after me so yeah
I think that's that's super interesting

87
0:4:49,3 --> 0:4:53,04
So I think that's probably the
most common, in fact possibly

88
0:4:53,04 --> 0:4:56,26
not, but that's the 1 I always
get see always see as the example

89
0:4:56,38 --> 0:4:57,66
given as to why.

90
0:4:58,1 --> 0:5:2,6
Nikolay: Yeah so for not to think
about going back to previous

91
0:5:2,6 --> 0:5:6,04
values like this is your value
and it's like fire and forget

92
0:5:6,04 --> 0:5:10,96
like this value is wasted and the
Sequence has shifted to new

93
0:5:10,96 --> 0:5:17,38
value although you can reset it
using there's nextval and there's

94
0:5:17,86 --> 0:5:21,48
setval there's currval and currval
requires a assignment first

95
0:5:21,48 --> 0:5:23,2
before you can use it and then
setval.

96
0:5:23,42 --> 0:5:28,68
So you can shift Sequence back
if you want, but it's global for

97
0:5:28,68 --> 0:5:29,18
everyone.

98
0:5:29,72 --> 0:5:33,16
And Also interesting, Sequence
is considered a Relation as well,

99
0:5:33,16 --> 0:5:33,66
right?

100
0:5:34,64 --> 0:5:36,76
Michael: Yeah, we discussed this
recently, didn't we?

101
0:5:36,76 --> 0:5:37,12
Yeah, yeah,

102
0:5:37,12 --> 0:5:41,92
Nikolay: in pg_class you see a
real kind equals, you said capital

103
0:5:41,92 --> 0:5:42,72
S, right?

104
0:5:42,72 --> 0:5:45,6
Michael: Capital S, and by the
way I was wrong, it's not the

105
0:5:45,6 --> 0:5:49,08
only 1 with a capital there is
1 other do you know the other

106
0:5:49,08 --> 0:5:49,4
1?

107
0:5:49,4 --> 0:5:49,9
Nikolay: No.

108
0:5:52,12 --> 0:5:53,86
Michael: Indexes on partitions.

109
0:5:54,16 --> 0:5:54,66
Okay.

110
0:5:55,4 --> 0:5:56,26
Capital I.

111
0:5:56,28 --> 0:5:56,78
Nikolay: Okay.

112
0:5:57,1 --> 0:6:0,1
Michael: So we've got 1 1 cause
already you've mentioned which

113
0:6:0,1 --> 0:6:2,64
is transactions rolling back I
want to go through a bunch of

114
0:6:2,64 --> 0:6:6,56
other causes, but before that should
we talk about, like why

115
0:6:6,56 --> 0:6:9,76
would you even want a gapless sequence?

116
0:6:9,76 --> 0:6:14,12
Like what, we've got sequences
and sequences with the odd gap

117
0:6:14,12 --> 0:6:16,7
in are fine for almost all use
cases.

118
0:6:16,84 --> 0:6:19,64
Should we talk a little bit about
why even bother?

119
0:6:19,64 --> 0:6:21,46
Like why even discuss this?

120
0:6:21,46 --> 0:6:22,54
Why is it a problem?

121
0:6:22,82 --> 0:6:26,02
Nikolay: Well, expectations, I
guess, right?

122
0:6:26,54 --> 0:6:27,78
You might have expectations.

123
0:6:28,38 --> 0:6:30,7
Michael: So I think I've only got
a couple here.

124
0:6:30,92 --> 0:6:34,54
I'm interested if other people
have seen others and but 1 I've

125
0:6:34,54 --> 0:6:40,82
got is user visible IDs that you
that you want to mean something.

126
0:6:41,2 --> 0:6:45,54
There was a really good blog post
on this topic by some folks

127
0:6:45,54 --> 0:6:49,62
at incident.io and it was actually
old friends of mine from GoCardless

128
0:6:49,76 --> 0:6:57,44
days and they wanted incident IDs
to increment by 1 for their

129
0:6:57,44 --> 0:7:1,2
customers so they could refer to
an incident ID And if they've

130
0:7:1,2 --> 0:7:5,28
had 3 that year, it's up to 3 and
then the fourth 1 gets assigned

131
0:7:5,38 --> 0:7:10,24
incident 4 and It's not ideal if
they want to if want them to

132
0:7:10,24 --> 0:7:14,84
mean something For them to miss
the odd 1 and much worse to miss

133
0:7:15,08 --> 0:7:16,96
like 10 or 20 in a row

134
0:7:17,06 --> 0:7:19,56
Nikolay: So they obviously have
many customers.

135
0:7:19,56 --> 0:7:21,46
So it's multi-tenant system, right?

136
0:7:21,46 --> 0:7:25,3
Yeah, And do they create a sequence
for each customer?

137
0:7:26,18 --> 0:7:28,22
Michael: Well they did initially.

138
0:7:28,62 --> 0:7:31,96
Nikolay: Okay yeah I'm asking like
because I saw this in other

139
0:7:31,96 --> 0:7:35,38
systems and I remember the approach
when we have a sequence just

140
0:7:35,38 --> 0:7:39,44
to support primary keys unless
we use beautiful UUID version 7.

141
0:7:39,44 --> 0:7:39,94
Yeah.

142
0:7:40,32 --> 0:7:45,56
Well, with some drawbacks, but
overall it's winning in my opinion

143
0:7:45,76 --> 0:7:46,58
these days.

144
0:7:47,02 --> 0:7:51,36
But for each customer, like the
namespace of each client ID or

145
0:7:51,36 --> 0:7:55,24
organization ID, doesn't matter,
project ID, we might want to

146
0:7:55,24 --> 0:7:56,66
have internal ID.

147
0:7:57,34 --> 0:8:1,6
Internal ID which is local, right?

148
0:8:1,72 --> 0:8:3,58
And then we shouldn't use sequences.

149
0:8:3,74 --> 0:8:8,26
It's like overuse of them because
if each customer has like thousands

150
0:8:8,26 --> 0:8:13,36
or millions of rows and like we
can handle it and collisions

151
0:8:14,34 --> 0:8:18,52
would happen only locally for this
organizational project or

152
0:8:18,52 --> 0:8:19,4
customer, right?

153
0:8:20,0 --> 0:8:20,88
Which is great.

154
0:8:21,46 --> 0:8:22,4
Yeah, right.

155
0:8:22,92 --> 0:8:23,86
So, so yeah.

156
0:8:24,52 --> 0:8:28,92
And for sequences, we just, the
only thing we care about is uniqueness,

157
0:8:29,64 --> 0:8:30,42
in my opinion.

158
0:8:30,56 --> 0:8:31,56
Michael: Yeah, yeah, you're right.

159
0:8:31,56 --> 0:8:34,74
Uniqueness is, but that's the job
of the primary key, right?

160
0:8:34,74 --> 0:8:37,36
It's also the fact they only go
up, I think.

161
0:8:37,36 --> 0:8:42,62
Nikolay: Yeah, yeah, well, unless
somebody rewind, right?

162
0:8:44,44 --> 0:8:44,94
Michael: Setval.

163
0:8:45,26 --> 0:8:46,3
Nikolay: Setval, exactly.

164
0:8:47,14 --> 0:8:52,32
So, and the capacity, like, just
forget about it because it's

165
0:8:52,58 --> 0:8:55,3
an int8 always for any sequence.

166
0:8:55,92 --> 0:8:59,1
I noticed some blog posts you share
with me, not this 1, different

167
0:8:59,1 --> 0:9:1,96
ones, that you used int4 primary
keys.

168
0:9:1,96 --> 0:9:5,82
I am very welcome this move because
these are our future clients.

169
0:9:8,3 --> 0:9:8,8
Yeah.

170
0:9:9,44 --> 0:9:10,56
So very good move.

171
0:9:10,56 --> 0:9:15,3
Everyone please use int4 primary
key and later if you're

172
0:9:15,3 --> 0:9:18,9
successful and have more money
you will pay us to fix that.

173
0:9:19,54 --> 0:9:20,04
Yeah.

174
0:9:20,28 --> 0:9:22,0
Michael: I like you flipping the
advice.

175
0:9:22,12 --> 0:9:23,94
So wait, but you said something
interesting then.

176
0:9:23,94 --> 0:9:26,38
So you said sequences are always
int8.

177
0:9:26,38 --> 0:9:29,96
So even if I have an int4
primary key, the sequence behind

178
0:9:29,96 --> 0:9:31,2
it is int8?

179
0:9:31,32 --> 0:9:35,12
Nikolay: Sequence is an independent
object which, well, independent

180
0:9:35,74 --> 0:9:38,44
relatively because there is dependency
which is also a weird

181
0:9:38,44 --> 0:9:41,82
thing, like owned by, right?

182
0:9:42,72 --> 0:9:46,64
It might be dependent, but it also
might belong to a column of

183
0:9:46,64 --> 0:9:47,28
a table.

184
0:9:48,34 --> 0:9:52,94
With alter table, alter sequence
owned by some column, right?

185
0:9:53,32 --> 0:9:57,34
But overall it's just a special
mechanism, int8 always,

186
0:9:58,1 --> 0:10:1,3
and just gives you a next number,
next number, That's it.

187
0:10:1,4 --> 0:10:1,9
Simple.

188
0:10:2,2 --> 0:10:2,7
Michael: Yeah.

189
0:10:3,42 --> 0:10:5,58
So yeah, by the way, I wasn't talking
about...

190
0:10:5,58 --> 0:10:8,64
So incident.io did use sequences
initially and it turned out to

191
0:10:8,64 --> 0:10:12,48
be a bad idea, but all I meant
was that that's a use case for

192
0:10:13,14 --> 0:10:16,32
not just monotonically increasing
IDs, but IDs that increase

193
0:10:16,32 --> 0:10:18,24
by exactly 1 each time.

194
0:10:18,62 --> 0:10:23,42
So that's 1 use case for like the
concept of gapless sequences.

195
0:10:24,14 --> 0:10:29,38
And another 1 came up in the blog
post by Sequin that I shared

196
0:10:29,38 --> 0:10:32,48
beforehand, and I'll link up in
the show notes again and that

197
0:10:32,48 --> 0:10:39,06
was the concept of Cursor-based
pagination so the the idea that

198
0:10:39,06 --> 0:10:42,74
you well I think it's I think it's
very similar to keyset pagination

199
0:10:42,8 --> 0:10:49,01
but based on an integer only so
the idea that it would I guess

200
0:10:49,01 --> 0:10:51,56
it's I guess for those it's most
important that it monotonically,

201
0:10:51,9 --> 0:10:57,14
that it only increases but also
that concept of the committing

202
0:10:57,22 --> 0:10:59,36
out of order becoming important.

203
0:10:59,36 --> 0:11:5,08
So if we read rows that are being
inserted right now there might

204
0:11:5,08 --> 0:11:10,46
be 1 that commits having started
later than a second 1 that sorry

205
0:11:10,46 --> 0:11:13,48
having started earlier than a second
1 that hasn't yet committed

206
0:11:13,68 --> 0:11:17,48
so we could see The example they
give is we could see IDs 1,

207
0:11:17,48 --> 0:11:22,18
2, and 4, and later 3 commits But
we only saw 1, 2, and 4 at

208
0:11:22,18 --> 0:11:26,78
the time of our read So if we were
paginating and got the first

209
0:11:26,78 --> 0:11:30,14
set and it went up to 4 And then
we only looked for ones above

210
0:11:30,14 --> 0:11:31,86
4, we've missed 3.

211
0:11:32,1 --> 0:11:37,36
So that's an interesting definition
of a sequence where you don't

212
0:11:37,36 --> 0:11:39,98
want there to be gaps maybe at
any point.

213
0:11:40,02 --> 0:11:42,24
Nikolay: You know what I'm looking
at the documentation right

214
0:11:42,24 --> 0:11:46,5
now and I think it would be great
if this thing called not sequence

215
0:11:46,5 --> 0:11:51,0
but something like generator, a
number generator or something.

216
0:11:51,18 --> 0:11:54,1
Because sequence it feels like
it should be sequential and gapless,

217
0:11:54,1 --> 0:11:55,78
like it's just some feeling you
know.

218
0:11:56,32 --> 0:12:0,42
This gives false expectations to
some people, not to everyone.

219
0:12:1,1 --> 0:12:4,78
Of course the condition says getSequence
define a new sequence

220
0:12:4,78 --> 0:12:5,28
generator.

221
0:12:5,8 --> 0:12:8,86
So generator is a better word of
this.

222
0:12:9,72 --> 0:12:14,72
And I think the condition could
be more explicit in terms of

223
0:12:14,72 --> 0:12:16,32
gaps to expect.

224
0:12:19,86 --> 0:12:25,24
So yeah, in my opinion, in my practice,
it happened not once

225
0:12:25,24 --> 0:12:28,7
when people expected them to be
gapless somehow, I don't know.

226
0:12:30,06 --> 0:12:32,34
A lot of new people are coming
to Postgres.

227
0:12:34,22 --> 0:12:36,1
Michael: All of us were new once,
right?

228
0:12:37,04 --> 0:12:38,74
I definitely experienced this.

229
0:12:38,94 --> 0:12:43,94
I think for us, moving on to a
second cause of this, I think

230
0:12:43,94 --> 0:12:47,98
the reason we were getting them
was using insert on conflict.

231
0:12:48,82 --> 0:12:55,24
So it was something around having
new users that had been added

232
0:12:55,24 --> 0:12:57,28
by somebody else in the team, for
example.

233
0:12:57,28 --> 0:13:1,18
So the user had already been created
behind the scenes because

234
0:13:1,18 --> 0:13:4,4
somebody invited them, and then
when they signed up, we were

235
0:13:4,4 --> 0:13:8,04
doing an insert on conflict update
or something like that and

236
0:13:8,04 --> 0:13:11,98
then so as part of that the next
file was called just in case

237
0:13:11,98 --> 0:13:15,92
we needed to insert a new row but
we ended up not needing to

238
0:13:15,92 --> 0:13:17,28
because it was an update instead.

239
0:13:17,28 --> 0:13:20,26
So I think you can get these again
through insert on conflicts.

240
0:13:20,86 --> 0:13:24,06
Nikolay: Yeah, and actually the
documentation mentions it.

241
0:13:24,96 --> 0:13:25,7
Oh, cool.

242
0:13:25,92 --> 0:13:30,14
It mentions it, like I think still
could be mentioned more explicitly

243
0:13:30,14 --> 0:13:31,94
maybe in the beginning and so on.

244
0:13:32,86 --> 0:13:39,78
And the thing is, someone might
consider sequences as not ACID,

245
0:13:39,86 --> 0:13:40,36
right?

246
0:13:40,52 --> 0:13:44,0
Because if rollback happens, they
don't rollback.

247
0:13:44,64 --> 0:13:46,42
For the sake of performance obviously.

248
0:13:46,92 --> 0:13:49,56
Michael: So it violates atomicity
does it?

249
0:13:53,56 --> 0:13:54,64
Nikolay: Yes or no?

250
0:13:54,84 --> 0:13:59,68
Yeah so if other things, other
writes are reverted, this change

251
0:13:59,72 --> 0:14:6,9
that we advanced sequence by 1, we shifted its position, it's

252
0:14:6,9 --> 0:14:7,9
not rolled back.

253
0:14:9,24 --> 0:14:15,02
So our operation is only partially
rewarded if we strictly look

254
0:14:15,02 --> 0:14:15,42
at it.

255
0:14:15,42 --> 0:14:20,68
For the sake of performance it's
pretty clear but yeah so like

256
0:14:20,68 --> 0:14:25,76
kind of not fully ACID and that's
okay it's just you need to

257
0:14:25,76 --> 0:14:29,6
understand it and that's it yeah
and the most natural but I can

258
0:14:29,6 --> 0:14:33,14
understand the feelings of people
who come to Postgres now and

259
0:14:34,0 --> 0:14:38,04
just from the meeting they expected
it but then boom.

260
0:14:38,3 --> 0:14:40,22
It's a simple thing to learn.

261
0:14:41,08 --> 0:14:43,44
Michael: Another case where naming
things is hard.

262
0:14:45,86 --> 0:14:50,14
Nikolay: So, yeah, for me it's
a generative number, huge capacity,

263
0:14:50,58 --> 0:14:56,44
8 bytes, and it gives me a tool
to guarantee some uniqueness

264
0:14:56,48 --> 0:14:57,72
when we generate numbers.

265
0:14:57,72 --> 0:14:58,4
That's it.

266
0:14:58,62 --> 0:14:59,36
Very performant.

267
0:15:0,42 --> 0:15:0,86
Very, very.

268
0:15:0,86 --> 0:15:4,54
I never think about performance
because rollback is not supported.

269
0:15:4,54 --> 0:15:4,94
That's it.

270
0:15:4,94 --> 0:15:11,2
Let's go and yeah, but let's talk
about again like If we really

271
0:15:11,2 --> 0:15:15,06
need it, I would think do we really
need it or we can be okay

272
0:15:15,06 --> 0:15:15,66
with it.

273
0:15:15,66 --> 0:15:20,7
If we really need it, I think we
should go with like specific

274
0:15:20,86 --> 0:15:25,52
allocation of numbers, maybe additional
ones, not primary keys,

275
0:15:25,52 --> 0:15:26,02
right?

276
0:15:28,18 --> 0:15:31,14
Michael: Yeah, well personally
I think this is a rare enough

277
0:15:31,5 --> 0:15:34,92
need that, it's not needed by every
project I don't

278
0:15:34,92 --> 0:15:35,46
Nikolay: think right

279
0:15:35,46 --> 0:15:38,48
Michael: I've run plenty of projects
that have not needed this

280
0:15:38,48 --> 0:15:42,88
feature so I personally think there's
not a necessity to build

281
0:15:42,88 --> 0:15:47,14
it into Postgres core as a feature
as like you know a sequence

282
0:15:47,16 --> 0:15:50,46
type or something but I do think
it's interesting enough like

283
0:15:50,46 --> 0:15:54,02
it seems to come up from time to
time and I think there were

284
0:15:54,28 --> 0:15:58,74
neat enough solutions at least
at lower scales I'm sure there

285
0:15:58,74 --> 0:16:3,26
is a solution at high scale as
well but there are simple enough

286
0:16:3,26 --> 0:16:7,98
solutions at lower volumes that
I think there's no necessity

287
0:16:8,08 --> 0:16:12,9
I don't think for a pre-built solution
that everyone can use.

288
0:16:15,08 --> 0:16:16,36
Nikolay: High-performance solution?

289
0:16:18,82 --> 0:16:22,58
It's impossible because if there
is a transaction which wants

290
0:16:22,58 --> 0:16:26,84
to write number 10, for example,
but it hasn't committed yet

291
0:16:26,84 --> 0:16:31,08
and we want to write number next
number or also number 10, it

292
0:16:31,08 --> 0:16:33,58
depends on the status of that first
transaction.

293
0:16:33,58 --> 0:16:35,24
We need to wait for it, right?

294
0:16:36,0 --> 0:16:37,62
It creates natural bottleneck.

295
0:16:38,76 --> 0:16:39,24
Yeah.

296
0:16:39,24 --> 0:16:44,14
And we, we like, I cannot see how
it can be undone, right?

297
0:16:44,14 --> 0:16:45,22
Like can be done differently.

298
0:16:45,22 --> 0:16:46,92
We need to wait until that transaction.

299
0:16:46,92 --> 0:16:48,78
We need to serialize these rights.

300
0:16:49,4 --> 0:16:53,4
And again, for me, the only trick
in terms of performance here

301
0:16:53,4 --> 0:16:56,76
is to use the fact that if we have
a multi-tenant system, we

302
0:16:56,76 --> 0:17:0,76
can make these collisions very
local to each project or organization

303
0:17:0,86 --> 0:17:1,94
or tenant, right?

304
0:17:2,28 --> 0:17:6,16
So they compute only within this
organization and other organizations

305
0:17:6,42 --> 0:17:9,4
are not like, are separate in terms
of these collisions.

306
0:17:10,38 --> 0:17:13,18
Michael: And ultimately, then it's
about parallelizing writes,

307
0:17:13,18 --> 0:17:14,94
which I think is then sharding.

308
0:17:15,02 --> 0:17:15,3
Yeah.

309
0:17:15,3 --> 0:17:18,64
So if you've got the multi-tenant
system across multiple shards,

310
0:17:18,64 --> 0:17:22,12
you can then scale your write throughput
So yeah, it feels to

311
0:17:22,12 --> 0:17:26,36
me like another case of that probably
being the ultimate solution

312
0:17:27,52 --> 0:17:30,96
Nikolay: well, if you have sharding
and distributed systems,

313
0:17:31,62 --> 0:17:32,48
it's like

314
0:17:34,2 --> 0:17:35,74
Michael: Across shards, I don't
mean

315
0:17:35,74 --> 0:17:37,4
Nikolay: yeah locally locally

316
0:17:38,26 --> 0:17:38,76
Michael: Yeah, exactly.

317
0:17:38,76 --> 0:17:41,12
If you've got a tenant that's local
and you can...

318
0:17:41,12 --> 0:17:45,88
Nikolay: Because if you want pure
sequential gapless number generator

319
0:17:46,24 --> 0:17:49,84
for distributed systems, it's a
whole new problem to solve.

320
0:17:49,84 --> 0:17:53,16
You basically need to build service
for it and so on.

321
0:17:53,16 --> 0:17:54,88
But again, if you make...

322
0:17:55,64 --> 0:17:57,18
So you should think about it.

323
0:17:57,18 --> 0:18:2,16
Okay, we will have thousands of
writes of new rows inserted per

324
0:18:2,16 --> 0:18:3,48
second, for example, soon.

325
0:18:3,48 --> 0:18:4,44
What will happen?

326
0:18:5,9 --> 0:18:10,08
If the collision will happen only
within boundaries of 1 tenant

327
0:18:10,08 --> 0:18:12,32
or project organization, doesn't
matter.

328
0:18:12,84 --> 0:18:14,28
It's not that bad, right?

329
0:18:14,28 --> 0:18:19,36
They can afford inserting those
rows sequentially, 1 by 1, and

330
0:18:19,36 --> 0:18:23,62
maybe within 1 transaction or some
transactions will wait, but

331
0:18:23,62 --> 0:18:24,64
maybe just 1.

332
0:18:25,08 --> 0:18:27,8
So maybe this will affect our parallelization
logic.

333
0:18:27,8 --> 0:18:30,76
So saying, let's not deal with
multiple tenants and multiple

334
0:18:30,76 --> 0:18:32,14
backends and transactions.

335
0:18:32,32 --> 0:18:34,24
Let's do it in 1 transaction always.

336
0:18:34,74 --> 0:18:40,56
But if we like write thousands
of rows per second and they belong

337
0:18:40,56 --> 0:18:45,18
to different organizations, collisions
won't happen, right?

338
0:18:45,78 --> 0:18:47,88
Because they don't compete.

339
0:18:48,42 --> 0:18:52,44
So this dictates how we could build
this high performance, gapless

340
0:18:52,44 --> 0:18:53,38
sequence solution.

341
0:18:53,9 --> 0:18:57,44
We just should avoid collisions
between tenants for example.

342
0:18:57,44 --> 0:18:58,22
That's it.

343
0:18:58,26 --> 0:18:58,76
Michael: Yeah.

344
0:18:59,28 --> 0:19:1,16
But we've jumped straight to the
hardest part.

345
0:19:1,16 --> 0:19:3,68
Should we talk about a couple more
of the kind of times that

346
0:19:3,68 --> 0:19:4,06
you might...

347
0:19:4,06 --> 0:19:4,94
Nikolay: — Oh, surprises!

348
0:19:5,02 --> 0:19:10,46
Yeah, so rollback is 1 thing which
can waste your precious numbers.

349
0:19:13,02 --> 0:19:17,06
Another thing is, I learned about
it and I forgot, and relearned

350
0:19:17,08 --> 0:19:22,16
when you sent me these blog posts,
there is a hardcoded constant

351
0:19:22,2 --> 0:19:24,24
32 pre-allocate.

352
0:19:24,52 --> 0:19:26,92
Actually I think there is constant and I think there is some

353
0:19:26,92 --> 0:19:27,42
setting.

354
0:19:27,66 --> 0:19:30,04
Maybe I'm wrong but there should be some setting.

355
0:19:31,12 --> 0:19:36,1
Yeah, so which you can say I want to pre-allocate more.

356
0:19:36,54 --> 0:19:37,96
Michael: Oh, I didn't come across that.

357
0:19:37,96 --> 0:19:42,84
So we've got set log values, that's the hard-coded 1, right?

358
0:19:43,08 --> 0:19:44,48
Nikolay: Yeah, maybe I'm wrong actually.

359
0:19:45,06 --> 0:19:46,82
So there are pre-allocated values.

360
0:19:48,34 --> 0:19:49,92
And can we control it?

361
0:19:49,92 --> 0:19:51,68
No, we cannot control it, right?

362
0:19:51,74 --> 0:19:52,24
32.

363
0:19:53,1 --> 0:19:55,86
Ah, there is cache, right?

364
0:19:55,96 --> 0:19:56,88
What is cache?

365
0:19:56,98 --> 0:20:0,38
When you create a sequence, you can specify the cache parameter

366
0:20:0,38 --> 0:20:1,08
as well.

367
0:20:1,98 --> 0:20:3,4
Michael: Okay, so what does that control?

368
0:20:3,9 --> 0:20:7,28
Nikolay: Yeah, so this controls exactly like this.

369
0:20:7,28 --> 0:20:9,36
If you don't do it, it will be 32.

370
0:20:10,44 --> 0:20:11,14
Michael: Oh, okay.

371
0:20:11,54 --> 0:20:13,48
So it's defined on a per sequence.

372
0:20:13,52 --> 0:20:14,06
Nikolay: Per sequence.

373
0:20:14,06 --> 0:20:15,76
You can say I want 1000.

374
0:20:16,1 --> 0:20:16,6
Pre-allocate.

375
0:20:16,72 --> 0:20:18,14
Michael: What if we set it to 1?

376
0:20:18,82 --> 0:20:21,84
Nikolay: Well, only 1 will be pre-allocated, right?

377
0:20:24,72 --> 0:20:26,14
1 is minimum, actually.

378
0:20:27,38 --> 0:20:28,32
Michael: 1 is minimum.

379
0:20:28,62 --> 0:20:29,12
Nikolay: Yeah.

380
0:20:30,06 --> 0:20:32,96
Actually, it's also interesting, maybe I'm wrong because there

381
0:20:32,96 --> 0:20:33,72
is also...

382
0:20:34,12 --> 0:20:35,34
Yeah, so I'm confused.

383
0:20:36,1 --> 0:20:40,28
So the computation about this parameter says 1 is default, but

384
0:20:40,28 --> 0:20:43,52
we know there is also 32 hardcoded constant.

385
0:20:44,06 --> 0:20:47,66
In any way, I don't know this hardcoded constant can be associated

386
0:20:47,74 --> 0:20:49,74
with 32 gap.

387
0:20:50,38 --> 0:20:54,1
So when, for example, a failure happens or just you fail over,

388
0:20:54,1 --> 0:20:57,72
switch over to new primary, which should be like normal thing,

389
0:20:57,72 --> 0:20:58,22
right?

390
0:20:58,5 --> 0:21:1,32
You change something on your replica, switch over to it.

391
0:21:1,5 --> 0:21:5,82
This is when you can, you might have a gap which is described

392
0:21:5,82 --> 0:21:7,8
in 1 of those articles 32.

393
0:21:8,16 --> 0:21:13,28
So I'm not sure about this cache parameter, right?

394
0:21:13,48 --> 0:21:17,06
So maybe if you change it, it's only cache of pre-allocated values

395
0:21:17,06 --> 0:21:17,78
and that's it.

396
0:21:17,78 --> 0:21:23,36
Maybe like specifying it won't lead to bigger or smaller gaps.

397
0:21:23,52 --> 0:21:24,4
I'm not sure about that.

398
0:21:24,4 --> 0:21:27,42
So maybe there are 2 layers of implementation here.

399
0:21:27,8 --> 0:21:31,34
But based on articles, we know there are gaps of 32.

400
0:21:31,76 --> 0:21:35,04
And this is just common, right?

401
0:21:35,74 --> 0:21:39,28
And interestingly, this is connected to recent discussions we

402
0:21:39,28 --> 0:21:42,32
had with 1 of the big customers who have a lot of databases.

403
0:21:42,86 --> 0:21:44,84
And we discussed major upgrades.

404
0:21:45,36 --> 0:21:50,4
And we have 0 downtime, 0 data loss, reversible upgrades solution

405
0:21:50,42 --> 0:21:52,24
which multiple companies use.

406
0:21:53,08 --> 0:22:0,06
And part of it is like 1 of the most fragile parts is when we

407
0:22:0,06 --> 0:22:0,86
switch over.

408
0:22:1,32 --> 0:22:7,18
During switchover into logical replica, we do it with basically

409
0:22:7,2 --> 0:22:9,74
without downtime things to pause, resume and PgBouncer.

410
0:22:10,44 --> 0:22:12,84
Also Patroni supports it.

411
0:22:13,32 --> 0:22:15,52
So we pause and resume.

412
0:22:16,02 --> 0:22:21,62
And between pause and resume, where small latency spikes in transaction

413
0:22:21,62 --> 0:22:26,9
processing happens, we redirect PgBouncer to a new server.

414
0:22:27,54 --> 0:22:34,68
And that server by default has Sequence values corresponding

415
0:22:34,74 --> 0:22:38,16
to initialization, because the logical replication in Postgres

416
0:22:38,16 --> 0:22:40,38
doesn't support still, there is work in progress.

417
0:22:41,04 --> 0:22:42,26
I think it's close to.

418
0:22:42,26 --> 0:22:44,62
It doesn't replicate values of Sequences.

419
0:22:45,3 --> 0:22:48,64
So the question is how to deal with it.

420
0:22:48,64 --> 0:22:49,58
There are 2 options.

421
0:22:49,74 --> 0:22:53,8
First, you can synchronize Sequence values during this switcher,

422
0:22:53,8 --> 0:22:55,84
but it will increase this spike.

423
0:22:55,84 --> 0:22:58,66
We don't want it because we achieved a few seconds spike.

424
0:22:58,66 --> 0:22:59,36
That's it.

425
0:22:59,44 --> 0:23:1,5
It feels really pure 0 downtime.

426
0:23:2,42 --> 0:23:6,64
And if we start synchronizing Sequences, it will be incremented.

427
0:23:6,9 --> 0:23:10,82
Especially some customers had like 200,000 tables, it's insane.

428
0:23:11,16 --> 0:23:15,86
But okay, if it's only 1000 tables, I thought, well, I don't

429
0:23:15,86 --> 0:23:16,62
want it.

430
0:23:16,7 --> 0:23:21,16
Actually, 1 of the engineers on the customer side said, you know

431
0:23:21,16 --> 0:23:25,58
what, like, this set value is not too long.

432
0:23:25,64 --> 0:23:29,26
If we quickly read it, quickly adjust it, maybe, okay, another

433
0:23:29,26 --> 0:23:29,76
second.

434
0:23:30,28 --> 0:23:33,0
And testing shows, yeah, exactly, like, changing position of

435
0:23:33,0 --> 0:23:35,14
Sequences super fast, actually.

436
0:23:35,86 --> 0:23:39,42
Yes, if you have hundreds of thousands of tables and Sequences,

437
0:23:39,44 --> 0:23:40,88
it will be quite slow.

438
0:23:41,06 --> 0:23:45,8
But it's only a few, you can do it quite quick, also can paralyze

439
0:23:45,8 --> 0:23:49,66
it maybe, but it will make things more complicated.

440
0:23:50,38 --> 0:23:54,16
But another solution which I like much more, we just advance

441
0:23:54,16 --> 0:23:59,4
it beforehand, before switchover, with some significant gap,

442
0:24:0,1 --> 0:24:6,96
Like I say, check how many you spend during a day or 2.

443
0:24:7,5 --> 0:24:9,74
Millions, 10 millions, advance.

444
0:24:9,88 --> 0:24:12,54
We have enough capacity for our life.

445
0:24:12,8 --> 0:24:14,74
8 bytes, it's definitely enough.

446
0:24:15,6 --> 0:24:19,46
So, yeah, just bump it to like 10 millions.

447
0:24:19,76 --> 0:24:24,26
But then it works with, you know, your system, like 1000, 2000

448
0:24:24,36 --> 0:24:29,0
tables, just 1 system, and you know, these big gaps are fine.

449
0:24:29,38 --> 0:24:33,64
But when you think about very, very different projects, thousands

450
0:24:33,64 --> 0:24:38,18
of clusters, you think oh maybe some of them won't be happy with

451
0:24:38,2 --> 0:24:38,94
big gaps.

452
0:24:39,64 --> 0:24:40,3
You know?

453
0:24:41,0 --> 0:24:42,68
This is a hard problem to solve.

454
0:24:43,08 --> 0:24:45,2
Michael: And if you go back in
the other direction, let's say

455
0:24:45,2 --> 0:24:48,64
you want to be able to fail back
quickly, that's another gap.

456
0:24:48,64 --> 0:24:50,52
So each time you bounce back and
forth.

457
0:24:50,64 --> 0:24:51,14
Nikolay: Yeah.

458
0:24:51,16 --> 0:24:51,66
Yeah.

459
0:24:51,98 --> 0:24:52,48
Yeah.

460
0:24:52,58 --> 0:24:56,14
Since our processes, our process
is fully reversible.

461
0:24:56,14 --> 0:24:57,6
It's really blue green deployments.

462
0:24:58,58 --> 0:25:4,02
Every time you switch, You need
to jump and we recommend jumping

463
0:25:4,02 --> 0:25:7,68
big like we have big gaps and I
would say you should be fine

464
0:25:7,68 --> 0:25:9,66
with it But I can imagine

465
0:25:9,66 --> 0:25:10,96
Michael: why not smaller gaps.

466
0:25:10,96 --> 0:25:11,64
Why not?

467
0:25:12,18 --> 0:25:13,32
Like why not?

468
0:25:13,32 --> 0:25:15,32
Let's say it's a two-second pause.

469
0:25:15,64 --> 0:25:19,28
Nikolay: Yeah, if you know there
won't be spikes of writes right

470
0:25:19,28 --> 0:25:22,12
before you switchover, well, we
can do that.

471
0:25:22,12 --> 0:25:27,48
But it's like, it's just, there
are like risks increase of overlapping.

472
0:25:28,14 --> 0:25:34,0
If you did it wrong, after switchover
some inserts won't work

473
0:25:34,0 --> 0:25:36,14
because this sequence already used,
right?

474
0:25:36,14 --> 0:25:36,48
—

475
0:25:36,48 --> 0:25:38,26
Michael: With duplicate key, or
yeah.

476
0:25:38,26 --> 0:25:38,64
Nikolay: —

477
0:25:38,64 --> 0:25:39,2
Michael: Yeah, so...

478
0:25:39,2 --> 0:25:40,46
— What would the actual errors
be?

479
0:25:40,46 --> 0:25:41,6
Duplicate key violations?

480
0:25:41,8 --> 0:25:45,04
Nikolay: — Yeah, so your sequence...

481
0:25:45,56 --> 0:25:49,18
But yeah, it will heal itself,
right?

482
0:25:50,22 --> 0:25:54,26
Thanks to the nature of sequences
which waste numbers.

483
0:25:55,24 --> 0:25:58,64
Insert a unique key value, oh,
it works.

484
0:26:0,48 --> 0:26:1,26
It's funny.

485
0:26:1,84 --> 0:26:9,52
Yeah, anyway, I always preferred
to be on the safe side and do

486
0:26:9,52 --> 0:26:13,4
big jumps But when you think about
many many clusters and things

487
0:26:13,4 --> 0:26:17,38
of many people It's a different
kind of kind of problem to have

488
0:26:17,38 --> 0:26:20,64
and and so I'm just highlighting
the gaps are fine.

489
0:26:20,64 --> 0:26:22,04
But what about big gaps?

490
0:26:22,82 --> 0:26:26,18
Yeah, you know some Sometimes it
can look not good.

491
0:26:28,82 --> 0:26:34,24
In this case, We are still thinking
maybe we should just implement

492
0:26:34,34 --> 0:26:39,48
2 paths, you know, and by default
we do big jump, but if somebody

493
0:26:39,48 --> 0:26:44,1
is not okay with that, maybe they
would prefer a bigger spike

494
0:26:44,1 --> 0:26:48,46
or bigger like maintenance window,
like, okay, well, up to 30

495
0:26:48,46 --> 0:26:52,7
seconds or so yeah while we are
synchronizing those sequences

496
0:26:52,86 --> 0:26:56,68
and don't allow any gaps or will
for me naturally knowing how

497
0:26:56,68 --> 0:27:1,76
sequences work for years like gaps
should be normal right

498
0:27:3,46 --> 0:27:6,3
Michael: yeah it's so interesting
isn't it like the trade-offs

499
0:27:6,3 --> 0:27:7,86
that different people want to make.

500
0:27:7,96 --> 0:27:9,34
Nikolay: You know solution to this?

501
0:27:9,34 --> 0:27:9,84
Michael: Yeah.

502
0:27:10,2 --> 0:27:10,84
Pardon me?

503
0:27:10,84 --> 0:27:12,84
Nikolay: You know the good solution to this?

504
0:27:13,52 --> 0:27:16,7
Finally start supporting sequences in logical replication, that's

505
0:27:16,7 --> 0:27:16,88
it.

506
0:27:16,88 --> 0:27:17,68
Michael: Yeah, that would be...

507
0:27:17,68 --> 0:27:20,58
Well, yeah, and that might not be too far away, so yeah.

508
0:27:20,58 --> 0:27:20,9
I think

509
0:27:20,9 --> 0:27:22,1
Nikolay: so, I think so.

510
0:27:22,48 --> 0:27:26,72
This work in progress lasts quite some years.

511
0:27:27,28 --> 0:27:31,14
It's called logical replication of sequences or synchronization

512
0:27:31,32 --> 0:27:32,7
of sequences to subscriber.

513
0:27:34,02 --> 0:27:38,74
And it's already multiple iterations since 2014, I think.

514
0:27:39,14 --> 0:27:43,24
And it has chances to be in Postgres 19, but it requires reviews.

515
0:27:43,9 --> 0:27:48,3
It's a great, great point for you to take your code or Cursor

516
0:27:48,34 --> 0:27:52,64
and ask it to compile and test and so on and then think about

517
0:27:52,78 --> 0:27:54,36
edge cases, corner cases.

518
0:27:55,44 --> 0:27:58,78
If you don't know C, this is a great point to provide some review.

519
0:27:58,78 --> 0:28:2,08
You should be just an engineer, like writing some code, you will

520
0:28:2,08 --> 0:28:5,96
understand discussion, comments, it's not that difficult.

521
0:28:6,14 --> 0:28:10,44
So I encourage our listeners to participate in reviews, maybe

522
0:28:10,44 --> 0:28:14,84
with a AI, but there will be still value if you consider yourself

523
0:28:14,88 --> 0:28:15,58
an engineer.

524
0:28:16,24 --> 0:28:20,8
You will like figure it out over time which value you can bring.

525
0:28:20,8 --> 0:28:24,06
The biggest values in testing is to think about various edge

526
0:28:24,06 --> 0:28:27,9
cases and corner cases as a user, as Postgres user, right?

527
0:28:27,98 --> 0:28:31,32
And try to test them and AI will help you.

528
0:28:31,86 --> 0:28:32,36
Michael: Yeah.

529
0:28:32,52 --> 0:28:37,08
Well, I also think we do have several experienced Postgres, like

530
0:28:37,16 --> 0:28:45,54
C developers listening and I think it's always a bit of a challenge

531
0:28:45,72 --> 0:28:50,7
to know exactly which changes are going to be the most user beneficial

532
0:28:51,04 --> 0:28:55,68
because you don't always get a representative sample on the mailing

533
0:28:55,68 --> 0:28:56,18
lists.

534
0:28:56,32 --> 0:29:1,02
I think sometimes like a lot of the people asking questions are

535
0:29:1,02 --> 0:29:3,48
very like the beginning of their journey.

536
0:29:3,48 --> 0:29:5,86
They haven't yet worked out how to look at the source code to

537
0:29:5,86 --> 0:29:9,1
solve problems so you don't get some of the kind of slightly

538
0:29:9,1 --> 0:29:13,04
more advanced problems always reported because people can work

539
0:29:13,04 --> 0:29:15,32
around them and I think this is one of those ones that people have

540
0:29:15,32 --> 0:29:17,2
just been working around for many years.

541
0:29:17,2 --> 0:29:20,5
A lot of consultancies deal with this in different ways, but

542
0:29:20,5 --> 0:29:23,28
it is affecting every major version.

543
0:29:23,42 --> 0:29:27,38
There is friction on, so if any hackers, any experienced hackers

544
0:29:27,38 --> 0:29:30,92
are also wondering like, which changes could I review that would

545
0:29:30,92 --> 0:29:32,16
have the biggest user impact.

546
0:29:32,16 --> 0:29:32,98
This feels like one.

547
0:29:32,98 --> 0:29:36,04
Nikolay: This feels like so many wanted.

548
0:29:36,04 --> 0:29:39,3
Logical replication is used more and more like bluegreen deployments

549
0:29:39,36 --> 0:29:40,18
and so on.

550
0:29:40,44 --> 0:29:43,74
And it's like for me in the past, if I looked at this, let's

551
0:29:43,74 --> 0:29:46,46
include by the way, commitfest
entry so people could look at

552
0:29:46,46 --> 0:29:49,66
it and think if they can review
and help testing.

553
0:29:49,9 --> 0:29:54,22
So in the past, I would think,
okay, to test it, I need first

554
0:29:54,22 --> 0:29:57,38
of all, what I need, this is about
logical replication and behavior.

555
0:29:57,44 --> 0:30:0,72
I need logical replication, setting
up 2 clusters with logical

556
0:30:0,72 --> 0:30:1,22
replication.

557
0:30:1,3 --> 0:30:2,48
Oh, yeah, okay.

558
0:30:2,78 --> 0:30:5,08
I have better things to do actually,
right?

559
0:30:5,08 --> 0:30:8,32
Now you can just launch your cloud
code or Cursor and say I have

560
0:30:8,32 --> 0:30:11,54
Docker installed locally on my
laptop or something.

561
0:30:11,78 --> 0:30:16,68
Please launch 2 containers, different
versions maybe, create

562
0:30:16,68 --> 0:30:18,82
logical replication and let's start
testing.

563
0:30:18,82 --> 0:30:20,96
And then like not containers.

564
0:30:21,94 --> 0:30:25,96
If containers work, now you can
say, okay, now I want some of

565
0:30:25,96 --> 0:30:30,52
them are built locally from source
code and then same thing.

566
0:30:30,6 --> 0:30:32,56
And you don't need to install logical
repli...

567
0:30:32,56 --> 0:30:35,14
To set up logical replication yourself,
that's it.

568
0:30:35,74 --> 0:30:40,76
So, yeah, so this like roadblocks
can be eliminated by AI and

569
0:30:40,76 --> 0:30:45,46
then you focus only on use cases
where this thing can be broken

570
0:30:45,46 --> 0:30:47,46
and this is where you can start
contributing.

571
0:30:47,64 --> 0:30:50,34
You just need to be a good Postgres
user, that's it.

572
0:30:52,28 --> 0:30:53,04
Michael: Yeah, nice.

573
0:30:53,04 --> 0:30:53,54
Nikolay: Good.

574
0:30:54,96 --> 0:30:57,84
Just to be able to distinguish
logical replica from physical

575
0:30:58,04 --> 0:31:2,9
replica manually, that's the only
thing you need to know to start.

576
0:31:2,9 --> 0:31:3,56
Yeah, good.

577
0:31:3,56 --> 0:31:4,06
Okay.

578
0:31:4,6 --> 0:31:10,94
So, are there any other cases where
we can experience gaps?

579
0:31:12,04 --> 0:31:15,16
Michael: Well, I actually thought,
I only wanted to talk about

580
0:31:15,16 --> 0:31:16,4
2 more things for sure.

581
0:31:16,4 --> 0:31:18,3
1 is why 32?

582
0:31:18,34 --> 0:31:19,74
Why do we pre-allocate these?

583
0:31:19,74 --> 0:31:20,78
I think that's interesting.

584
0:31:21,28 --> 0:31:25,08
And 2, what can you actually do
about, like if you, I thought

585
0:31:25,08 --> 0:31:28,44
the incident, like especially at
lower volumes, there's like

586
0:31:28,44 --> 0:31:29,44
some neat solutions.

587
0:31:29,82 --> 0:31:32,22
That were the only last 2 things
I had on my list.

588
0:31:32,72 --> 0:31:35,5
Nikolay: Well, for performance
we pre-log it, right?

589
0:31:35,5 --> 0:31:39,48
Because technically it's a page,
it's like also a relation which

590
0:31:39,48 --> 0:31:41,4
stores value and so on, right?

591
0:31:42,04 --> 0:31:45,14
Michael: Well, I got the impression
from a comment in the source

592
0:31:45,14 --> 0:31:46,32
code that it was...

593
0:31:46,64 --> 0:31:47,9
So let me read it exactly.

594
0:31:47,9 --> 0:31:50,94
We don't want to log each fetching
of value from a sequence so

595
0:31:50,94 --> 0:31:52,86
we pre-log a few fetches in advance.

596
0:31:53,3 --> 0:31:56,92
In the event of a crash we can
lose, in brackets skip over, as

597
0:31:56,92 --> 0:31:58,24
many values as we pre-logged.

598
0:31:58,26 --> 0:32:1,14
So I got the impression it was
to avoid spamming the WAL.

599
0:32:1,2 --> 0:32:4,18
Nikolay: Yeah, it's optimization
technique, that's it.

600
0:32:4,54 --> 0:32:7,12
Michael: So I could imagine a case
where you'd want to pay that

601
0:32:7,12 --> 0:32:10,64
trade off the other way around,
like, and it's good to know,

602
0:32:10,64 --> 0:32:13,58
as you mentioned, that you can
reduce it on a per-sequence basis.

603
0:32:13,58 --> 0:32:14,48
Nikolay: I think it's different.

604
0:32:14,48 --> 0:32:19,92
I think what you can reduce is
cache, but it's not the thing that

605
0:32:19,92 --> 0:32:20,92
goes to WAL.

606
0:32:21,04 --> 0:32:23,16
I'm not 100% sure here.

607
0:32:24,72 --> 0:32:27,14
I just think you still lose 32.

608
0:32:27,44 --> 0:32:30,06
But because these are 2 different
things.

609
0:32:30,06 --> 0:32:34,2
1 is a hard-coded constant value,
another is dynamic control

610
0:32:34,2 --> 0:32:34,9
by user.

611
0:32:36,46 --> 0:32:37,86
But maybe I'm wrong again here.

612
0:32:37,86 --> 0:32:41,68
It's a good question to check,
but it's nuance.

613
0:32:41,88 --> 0:32:46,52
For me, a sequence is always a
gap, having gaps, that's it.

614
0:32:47,24 --> 0:32:48,1
Michael: And it's okay.

615
0:32:48,28 --> 0:32:54,34
So okay, the last thing was solutions.

616
0:32:55,6 --> 0:32:59,76
And I thought the instant 1 was
really neat but also quite oh

617
0:32:59,76 --> 0:33:3,76
it's very very simple I like simple
solutions that work for now

618
0:33:3,76 --> 0:33:5,66
and we can solve later problems
later.

619
0:33:5,9 --> 0:33:11,5
And it was just to do a subquery
and read the max value currently

620
0:33:11,84 --> 0:33:15,46
and increment it by 1, so not using
sequences of course.

621
0:33:16,0 --> 0:33:17,94
Nikolay: Yeah, no sequences, it's
just reading.

622
0:33:17,96 --> 0:33:21,88
It reminds us of the episode we
had with Haki Benita, right?

623
0:33:23,42 --> 0:33:24,3
And the problems.

624
0:33:24,52 --> 0:33:25,86
Yes, yeah.

625
0:33:26,14 --> 0:33:28,38
Get or Create or something like
this, right?

626
0:33:28,78 --> 0:33:29,14
Yes.

627
0:33:29,14 --> 0:33:32,34
So we need to basically, we need
to deal with other, like we

628
0:33:32,34 --> 0:33:37,58
need to read the maximum value
and get plus 1, but maybe others

629
0:33:37,58 --> 0:33:39,06
do the same thing in parallel.

630
0:33:39,06 --> 0:33:45,44
And how to deal with performance
in this multi-concurrent environment.

631
0:33:45,44 --> 0:33:52,44
Again, the clue for me is to narrow
down the scope of collisions.

632
0:33:53,42 --> 0:33:54,18
That's it.

633
0:33:54,48 --> 0:33:56,74
So contention would be local to...

634
0:33:57,18 --> 0:33:59,16
Michael: So there's multiple options,
right?

635
0:33:59,16 --> 0:34:2,72
You could just implement, I say
just, as if it's simple.

636
0:34:2,94 --> 0:34:3,9
Retries are 1 option.

637
0:34:3,9 --> 0:34:9,0
If you expect collisions to be
super super uncommon retries would

638
0:34:9,0 --> 0:34:15,04
be a solution but I think there's
well the Sequin blog post actually

639
0:34:15,04 --> 0:34:19,48
goes into a bit of depth into how
you could scale this if you

640
0:34:19,48 --> 0:34:22,32
are doing tons, like a lot per
second.

641
0:34:22,8 --> 0:34:24,58
So that's an interesting solution.

642
0:34:24,96 --> 0:34:28,32
There's way too much code to go
into now, but I'll link that

643
0:34:28,32 --> 0:34:29,44
up in the show notes.

644
0:34:30,4 --> 0:34:33,82
But yeah, I did think there's like
a range of solutions like

645
0:34:33,82 --> 0:34:38,36
from we have a multi-tenancy system,
like incident for example.

646
0:34:38,36 --> 0:34:41,26
You're not going to be creating,
hopefully most organizations

647
0:34:41,32 --> 0:34:45,9
are not going to be creating thousands
of incidents per, never

648
0:34:45,9 --> 0:34:47,44
mind second, per day.

649
0:34:47,58 --> 0:34:52,5
So the chance of collisions or
like issues there are so low that

650
0:34:52,78 --> 0:34:58,32
it's almost a non-issue whereas
a different use case I actually

651
0:34:58,32 --> 0:35:2,36
I can't think of a use case for
like needing a gapless sequence

652
0:35:2,36 --> 0:35:6,36
that can insert thousands or like
thousands per second.

653
0:35:7,06 --> 0:35:11,82
So I just don't see that being
a, well, I'd love to hear from

654
0:35:11,82 --> 0:35:14,82
people that have seen that or have
had to deal with that and

655
0:35:14,82 --> 0:35:15,7
what they did.

656
0:35:15,9 --> 0:35:17,0
Nikolay: Thousands per second?

657
0:35:17,64 --> 0:35:18,14
Michael: Yeah.

658
0:35:18,82 --> 0:35:21,24
For an a gapless sequence like.

659
0:35:21,28 --> 0:35:21,78
Yeah.

660
0:35:21,9 --> 0:35:24,12
Where it's important not to have
gaps.

661
0:35:24,24 --> 0:35:25,74
Nikolay: Yeah, yeah, yeah, yeah.

662
0:35:25,76 --> 0:35:30,82
Yeah, because if you have a lot
of inserts, you have big numbers.

663
0:35:32,78 --> 0:35:37,84
So the idea is a desire to have
gapless matters when we have

664
0:35:37,84 --> 0:35:38,8
only small numbers.

665
0:35:39,52 --> 0:35:41,54
Michael: I think it's more important,
right?

666
0:35:41,78 --> 0:35:42,66
Nikolay: Yeah, maybe.

667
0:35:42,66 --> 0:35:44,16
Also, the little 32

668
0:35:44,28 --> 0:35:45,36
Michael: would disappear quickly.

669
0:35:45,36 --> 0:35:47,96
Imagine a gap of 32, it would disappear
quite quickly.

670
0:35:47,96 --> 0:35:48,84
If you're, if you're,

671
0:35:48,84 --> 0:35:53,82
Nikolay: if it's a big number,
stop, stop paying attention to,

672
0:35:53,94 --> 0:35:55,22
yeah, maybe, maybe.

673
0:35:55,4 --> 0:35:58,74
Michael: And also, I don't think
computers care about gaps.

674
0:35:58,74 --> 0:36:0,42
I think it's humans that care.

675
0:36:0,66 --> 0:36:1,16
Yeah.

676
0:36:1,84 --> 0:36:3,14
Personally, I don't know.

677
0:36:3,14 --> 0:36:3,44
Nikolay: Yeah.

678
0:36:3,44 --> 0:36:8,28
Well, with sequences, I remember
it was 2005-06 when we wanted

679
0:36:8,62 --> 0:36:14,04
to hide actual numbers of users
and things created in our social

680
0:36:14,04 --> 0:36:14,54
network.

681
0:36:15,1 --> 0:36:23,32
So we used 2 prime numbers and
set default next val from sequence

682
0:36:23,6 --> 0:36:28,68
multiplied by 1 big number and
then a module of the different

683
0:36:28,68 --> 0:36:29,18
number.

684
0:36:29,54 --> 0:36:34,32
So it was like fake random, you
know, to hide it.

685
0:36:34,54 --> 0:36:39,52
I figure like you can still, if
you create some of things yourself,

686
0:36:39,62 --> 0:36:42,6
you see numbers, you can quickly
understand the logic and like

687
0:36:42,6 --> 0:36:46,44
it's still, you can hack it and
understand the actual growth

688
0:36:46,44 --> 0:36:46,94
rates.

689
0:36:46,96 --> 0:36:49,58
But it's hard to understand absolute
value for this.

690
0:36:50,58 --> 0:36:55,16
You don't know how many things,
compared to like people who don't

691
0:36:55,16 --> 0:36:59,38
care, they're just 1 global sequence
for all users and okay,

692
0:36:59,38 --> 0:37:1,62
number of posts, this like 1 million
something.

693
0:37:1,62 --> 0:37:3,74
Okay, this platform has 1 million
posts.

694
0:37:4,22 --> 0:37:6,84
It gives some signals to your competitors
right

695
0:37:7,66 --> 0:37:11,02
Michael: so Have you, I learned
today what that generally is

696
0:37:11,02 --> 0:37:14,34
called it's called the German tank
problem have you heard this?

697
0:37:14,62 --> 0:37:19,64
No It's like the maybe not the
first but like the first famous

698
0:37:19,64 --> 0:37:24,66
case of this was, I think, in World
War II, the Allies were using

699
0:37:24,72 --> 0:37:28,68
the numbers, like an incrementing
number found on German tanks

700
0:37:28,68 --> 0:37:32,18
to find out how many they were
going through, like how many,

701
0:37:32,26 --> 0:37:34,14
what their production capacity
was.

702
0:37:34,44 --> 0:37:37,4
It was a useful thing in the war.

703
0:37:37,7 --> 0:37:40,22
So yeah, this is older than computers.

704
0:37:44,76 --> 0:37:51,1
Nikolay: Yeah, it reminds me how
the guys who from my former

705
0:37:51,1 --> 0:37:55,9
country went to your country to
poison some guy and their passports

706
0:37:55,94 --> 0:37:56,64
were sequential.

707
0:37:57,5 --> 0:37:59,08
That's how they were tracked.

708
0:37:59,12 --> 0:37:59,62
Yes.

709
0:38:0,74 --> 0:38:1,9
So stupid, right?

710
0:38:2,06 --> 0:38:3,96
I mean, sometimes gaps are good.

711
0:38:4,54 --> 0:38:6,6
If you want to hide some things.

712
0:38:8,68 --> 0:38:11,56
So if you build some system maybe
you want gaps actually.

713
0:38:12,26 --> 0:38:14,74
Michael: Yeah, that's the next
episode, another different episode.

714
0:38:15,06 --> 0:38:16,22
Nikolay: How to build gaps.

715
0:38:16,56 --> 0:38:18,36
Michael: Gapful sequences, yeah.

716
0:38:18,48 --> 0:38:23,52
Nikolay: Some random gaps so everyone
doesn't understand how

717
0:38:23,52 --> 0:38:24,02
many.

718
0:38:24,72 --> 0:38:27,02
Michael: Yeah, just UUIDV4, right?

719
0:38:28,2 --> 0:38:29,16
Nikolay: Random jumps.

720
0:38:30,06 --> 0:38:31,12
Yeah, so that's it.

721
0:38:31,12 --> 0:38:33,7
I also wanted to mention sequences
have a...

722
0:38:34,28 --> 0:38:38,6
Like, a sequence has a few more
parameters you can specify,

723
0:38:38,72 --> 0:38:44,12
like, min value, max value, and
you can say it should be in loop.

724
0:38:44,24 --> 0:38:46,22
I don't know why, I never used
it.

725
0:38:46,74 --> 0:38:48,14
Cycle, It's called cycle.

726
0:38:48,64 --> 0:38:51,86
So you can specify from 1 to thousand
and cycle.

727
0:38:52,58 --> 0:38:55,84
Michael: So you don't, for example,
need to, it doesn't need

728
0:38:55,84 --> 0:38:57,22
to be on a primary key.

729
0:38:57,4 --> 0:38:59,2
So it couldn't be on a primary
key.

730
0:38:59,2 --> 0:38:59,84
That 1.

731
0:38:59,88 --> 0:39:0,38
Nikolay: Yeah.

732
0:39:0,48 --> 0:39:5,02
I would use like just percent operator
model, just divide by

733
0:39:5,02 --> 0:39:6,76
something and have the same effect.

734
0:39:7,58 --> 0:39:8,04
But...

735
0:39:8,04 --> 0:39:10,58
Michael: Yeah, I guess it's similar
to transaction IDs.

736
0:39:10,58 --> 0:39:12,94
If you think about how transaction
IDs look.

737
0:39:13,18 --> 0:39:14,2
Nikolay: Wrap around, yeah.

738
0:39:14,38 --> 0:39:16,8
If you want to wrap around, go
for it.

739
0:39:16,8 --> 0:39:20,78
Yeah, I'm very curious use cases
for this never never used it

740
0:39:22,82 --> 0:39:28,08
yeah but increment also you can
specify jump and like only odd

741
0:39:28,08 --> 0:39:29,54
numbers for example right

742
0:39:31,3 --> 0:39:32,98
Michael: yeah or any positive might
be more

743
0:39:32,98 --> 0:39:33,34
common.

744
0:39:33,34 --> 0:39:35,1
Nikolay: We want to increment by random.

745
0:39:35,22 --> 0:39:38,5
This will be our random gaps to
fool everyone.

746
0:39:38,94 --> 0:39:39,44
Yeah.

747
0:39:39,52 --> 0:39:40,66
Okay, good.

748
0:39:40,96 --> 0:39:42,22
Enough about sequences.

749
0:39:42,88 --> 0:39:44,1
Thank you for the topic.

750
0:39:44,4924 --> 0:39:44,9924
Michael: Likewise.

751
0:39:46,12 --> 0:39:48,1
Good to see you and catch you soon.