Full text search

1
00:00:00,060 --> 00:00:00,660
Nikolay: Hello, hello.

2
00:00:00,660 --> 00:00:05,200
This is PostgresFM, episode number
98, right?

3
00:00:05,200 --> 00:00:05,700
98.

4
00:00:06,400 --> 00:00:06,900
Michael: Yes.

5
00:00:07,420 --> 00:00:09,180
Nikolay: And this is Nikolay Samokhvalov,

6
00:00:09,180 --> 00:00:11,680
founder of Postgres.AI.

7
00:00:11,920 --> 00:00:16,040
And my co-host is Michael Christofides,
founder of pgMustard.

8
00:00:16,880 --> 00:00:17,640
Hi, Michael.

9
00:00:18,240 --> 00:00:18,480
Michael: Wow.

10
00:00:18,480 --> 00:00:20,980
That's the first time you've attempted
my last name and you did

11
00:00:20,980 --> 00:00:21,480
great.

12
00:00:21,900 --> 00:00:22,400
Nikolay: Okay.

13
00:00:23,000 --> 00:00:24,000
Thank you so much.

14
00:00:24,060 --> 00:00:25,740
I practiced all these years.

15
00:00:27,260 --> 00:00:28,880
So what's the topic today?

16
00:00:29,820 --> 00:00:31,320
Michael: Yeah, So this was your
suggestion.

17
00:00:31,320 --> 00:00:32,360
I think it's a great one.

18
00:00:32,360 --> 00:00:34,840
We've been meaning to come back
to it for a while now.

19
00:00:34,840 --> 00:00:36,320
It's full-text search.

20
00:00:36,380 --> 00:00:38,260
Why did you want to talk about
it?

21
00:00:38,800 --> 00:00:42,540
Nikolay: I think it was on our
list for a very long time, maybe

22
00:00:42,620 --> 00:00:45,540
from the very beginning, because
it's a huge topic.

23
00:00:45,720 --> 00:00:50,460
And when we built that list, I
think pgVector already existed,

24
00:00:50,600 --> 00:00:54,580
but only a few people paid attention
to it, unlike today.

25
00:00:55,240 --> 00:01:01,280
So since we didn't manage to discuss
full-text search before

26
00:01:02,380 --> 00:01:04,960
this AI boom started last year.

27
00:01:05,220 --> 00:01:08,580
I think we should maybe compare
it a little bit.

28
00:01:09,640 --> 00:01:12,960
Since I used full-text search a
lot in the past, I still remember

29
00:01:12,960 --> 00:01:16,260
many features it offers in Postgres.

30
00:01:17,020 --> 00:01:20,920
I think it's interesting to not
only discuss, like usual discussion,

31
00:01:21,500 --> 00:01:24,180
embedded full-text search in Postgres
versus Elastic.

32
00:01:24,960 --> 00:01:28,860
But also it's interesting to discuss
embedded full-text search

33
00:01:29,380 --> 00:01:33,180
of Postgres versus PgVector
extension and similarity

34
00:01:33,340 --> 00:01:36,520
search, or maybe not versus, maybe
together.

35
00:01:37,960 --> 00:01:38,980
And this is interesting.

36
00:01:39,280 --> 00:01:44,720
But I think time is changing and
evolution is very interesting.

37
00:01:45,060 --> 00:01:49,240
And today people probably pay less
attention to full-text search,

38
00:01:49,300 --> 00:01:53,500
but they at least should know what
capabilities it can offer.

39
00:01:55,760 --> 00:02:02,300
And let's maybe start from use
cases and discuss functions, features,

40
00:02:03,160 --> 00:02:06,100
FTS, full-text search provides
in Postgres.

41
00:02:07,540 --> 00:02:08,440
What do you think?

42
00:02:08,940 --> 00:02:12,540
Or maybe we can talk about some
historical aspects.

43
00:02:12,720 --> 00:02:18,660
I also can provide some overview
of what was happening in Postgres

44
00:02:18,720 --> 00:02:20,040
and not only in Postgres.

45
00:02:20,800 --> 00:02:21,720
What do you think?

46
00:02:21,740 --> 00:02:24,940
Features, use cases, history, what's
better?

47
00:02:25,600 --> 00:02:27,340
Michael: Actually, I don't know
the history that well.

48
00:02:27,340 --> 00:02:29,380
That would be awesome to hear a
tiny bit about that.

49
00:02:29,380 --> 00:02:32,980
And then I'd love to jump onto
use cases where you see it used

50
00:02:32,980 --> 00:02:34,000
most commonly, that kind

51
00:02:34,000 --> 00:02:34,340
Nikolay: of thing.

52
00:02:34,340 --> 00:02:35,740
Yeah, actually we can combine.

53
00:02:35,740 --> 00:02:37,860
I now see how we can do it.

54
00:02:37,860 --> 00:02:47,180
So if, for example, we roll back
to first web era, Web 1.0, so

55
00:02:47,180 --> 00:02:51,360
to speak, like 25 years ago, and
when Google was created.

56
00:02:52,120 --> 00:02:55,460
Because Google is kind of, it's
a search engine, originally,

57
00:02:55,600 --> 00:02:56,100
right?

58
00:02:56,400 --> 00:03:00,060
And it solves the problem of finding
proper texts, most relevant

59
00:03:00,060 --> 00:03:00,980
texts, right?

60
00:03:01,860 --> 00:03:07,000
And at that time, before Google,
already other search engines

61
00:03:07,000 --> 00:03:09,520
were created, obviously.

62
00:03:09,660 --> 00:03:14,340
Full text search capabilities were
originally called T-Search

63
00:03:14,540 --> 00:03:15,040
2.

64
00:03:15,040 --> 00:03:19,220
Actually, I remember this contrib
module I was seeing at 2005,

65
00:03:19,500 --> 00:03:23,240
maybe, when I first started working
with Postgres, created by

66
00:03:23,240 --> 00:03:27,620
Oleg Bartunov, Teodor Sigaev, and
then Alexander Korotkov joined

67
00:03:27,620 --> 00:03:28,120
them.

68
00:03:28,380 --> 00:03:32,460
They created it because they were
building a search engine originally.

69
00:03:33,440 --> 00:03:36,360
They helped building quite a big
search engine.

70
00:03:37,480 --> 00:03:43,080
Then they incorporated many of
their works into Postgres, and

71
00:03:43,080 --> 00:03:47,740
this became T-Search2 contrib module,
which later was merged

72
00:03:48,240 --> 00:03:53,000
to the core and you don't need
to install extension, so it's

73
00:03:53,000 --> 00:03:54,300
already in core, right?

74
00:03:54,560 --> 00:03:58,440
But interesting thing that it was
before social networks and

75
00:03:58,440 --> 00:04:05,500
so on, but If we look at how it's
built, it has a lot of things.

76
00:04:05,500 --> 00:04:09,880
For example, you can search, you
can, for example, exclude some

77
00:04:09,880 --> 00:04:10,280
words.

78
00:04:10,280 --> 00:04:12,540
By default, it excludes some words.

79
00:04:12,540 --> 00:04:17,140
It's called dictionary, which is
like in Postgres full text search

80
00:04:17,140 --> 00:04:18,980
terminology, dictionary is a program.

81
00:04:19,620 --> 00:04:23,800
It can process input somehow transforming
it.

82
00:04:24,520 --> 00:04:30,200
So stop words, it's just exclusion
of various articles for example

83
00:04:30,200 --> 00:04:33,280
and so on which we probably don't
have any meaning, so we can

84
00:04:33,280 --> 00:04:34,860
just exclude them, that's it.

85
00:04:35,020 --> 00:04:40,760
Then we have Snowball, which is
a stemmer, so it removes the

86
00:04:40,760 --> 00:04:41,260
endings.

87
00:04:42,340 --> 00:04:44,440
It's super fast and super simple.

88
00:04:45,040 --> 00:04:48,280
So we can, for example, runs and
run, it's like kind of the same

89
00:04:48,280 --> 00:04:50,700
word, so s should be removed, right?

90
00:04:51,280 --> 00:04:56,760
And then more full-fledged thing,
we can ask to join, to use

91
00:04:56,760 --> 00:05:03,360
some real dictionary, usually ISPELL,
which is also used for thesaurus,

92
00:05:03,780 --> 00:05:08,300
like It can give a lot of things,
but it's usually slower, requires

93
00:05:08,300 --> 00:05:08,980
some memory.

94
00:05:09,840 --> 00:05:13,040
And all these things are configurable
in full-text search, and

95
00:05:13,040 --> 00:05:18,820
they were configurable in TS Vector
2 originally, and it's great.

96
00:05:19,540 --> 00:05:23,960
But if you think about the task,
the use case, what we want to

97
00:05:23,960 --> 00:05:29,800
try to solve, we want to present
user some information which

98
00:05:29,800 --> 00:05:31,660
is most relevant, right?

99
00:05:32,380 --> 00:05:33,480
What is most relevant?

100
00:05:33,480 --> 00:05:34,820
The user has some query.

101
00:05:34,820 --> 00:05:41,880
I want a recipe with, I don't know,
with some eggplant, for example.

102
00:05:41,880 --> 00:05:42,880
He puts eggplant.

103
00:05:42,880 --> 00:05:47,040
If we have, for example, food recipe,
website, or system, knowledge

104
00:05:47,040 --> 00:05:47,540
base.

105
00:05:48,640 --> 00:05:57,780
And then we just stop words, normalizing
words, we can just filter,

106
00:05:57,780 --> 00:05:58,280
right?

107
00:05:58,380 --> 00:06:00,040
But filtering is just 1 thing.

108
00:06:00,040 --> 00:06:02,840
We also need to somehow order,
maybe, right?

109
00:06:02,840 --> 00:06:05,300
And present only the most relevant.

110
00:06:05,660 --> 00:06:06,840
What is the most relevant?

111
00:06:07,300 --> 00:06:11,820
Maybe these words are most frequently
used in these documents,

112
00:06:11,960 --> 00:06:12,460
right?

113
00:06:12,740 --> 00:06:14,820
Like repeated many times, for example.

114
00:06:15,640 --> 00:06:17,620
And this is quite a basic idea.

115
00:06:18,580 --> 00:06:22,320
If the word is used many times,
it's considered most relevant.

116
00:06:23,680 --> 00:06:26,100
Now, confession, I never used this.

117
00:06:26,640 --> 00:06:27,040
Why?

118
00:06:27,040 --> 00:06:28,520
Because I'm from a different era.

119
00:06:28,520 --> 00:06:33,840
I'm from web 2.0, where most relevant
meant something else because

120
00:06:33,840 --> 00:06:35,220
it was social networks.

121
00:06:35,920 --> 00:06:38,860
It was like also time matters a
lot.

122
00:06:38,860 --> 00:06:42,740
Like if it's fresh document, like
fresh blog post or something,

123
00:06:42,740 --> 00:06:43,920
it matters a lot.

124
00:06:44,540 --> 00:06:48,520
But Postgres full text search lacks
this, like it's hard to build

125
00:06:48,520 --> 00:06:49,020
together.

126
00:06:49,400 --> 00:06:52,840
There is a special index RAM, I mentioned
it many times already.

127
00:06:53,360 --> 00:06:57,220
Maybe I need to revisit it finally
and think, maybe it still

128
00:06:57,540 --> 00:07:00,880
can be used, because if you're
on self-managed Postgres, this

129
00:07:00,940 --> 00:07:03,460
type of index can be installed
as an additional extension.

130
00:07:04,280 --> 00:07:08,920
But the original full-text search can
order by only this tsRank,

131
00:07:09,000 --> 00:07:09,240
right?

132
00:07:09,240 --> 00:07:10,440
It's called function tsRank.

133
00:07:10,440 --> 00:07:14,040
And there is another function which
also considers positions

134
00:07:14,320 --> 00:07:15,900
and distance between words.

135
00:07:16,500 --> 00:07:18,280
I think like density or something.

136
00:07:18,280 --> 00:07:22,780
Like not density, but the closer
words are the better ranking

137
00:07:24,440 --> 00:07:25,500
score is, right?

138
00:07:25,680 --> 00:07:33,280
But for me, it was always nonsense
to use only these word positions

139
00:07:33,380 --> 00:07:34,200
and so on.

140
00:07:34,460 --> 00:07:37,860
I couldn't imagine how I can use
it because I always needed to

141
00:07:37,860 --> 00:07:39,500
take into account time.

142
00:07:39,540 --> 00:07:44,460
And also sometimes things like
likes, reactions, comments, if

143
00:07:44,460 --> 00:07:48,660
this post is very heavily discussed,
it's very important, right?

144
00:07:49,140 --> 00:07:53,800
But back to history, we know there
is BM25 and also there is

145
00:07:53,800 --> 00:07:54,840
like this TF-IDF.

146
00:07:55,840 --> 00:07:59,740
The idea, TF, it's term frequency,
inverted document frequency,

147
00:07:59,740 --> 00:08:00,240
right?

148
00:08:00,240 --> 00:08:05,100
If some document has this term
like word mentioned many times,

149
00:08:05,660 --> 00:08:07,460
it's good for this document, right?

150
00:08:07,540 --> 00:08:12,840
And if, in general, this term is
mentioned in our whole database,

151
00:08:13,820 --> 00:08:17,780
it's a whole dataset, it's mentioned
not very frequently, it's

152
00:08:17,780 --> 00:08:22,320
also good for our document, because
it means that it's exceptional

153
00:08:22,840 --> 00:08:24,640
in terms of frequency, right?

154
00:08:24,640 --> 00:08:26,760
So that's why it works, document
frequency.

155
00:08:26,980 --> 00:08:30,060
So it means that for such thing
we need to analyze the whole

156
00:08:30,060 --> 00:08:30,460
dataset.

157
00:08:30,460 --> 00:08:32,460
Postgres full text search doesn't
do it.

158
00:08:32,900 --> 00:08:36,260
It's quite hard to maintain such
type of thing, but I think if

159
00:08:36,260 --> 00:08:40,140
there are some attempts to have
it in Postgres, maybe some extensions

160
00:08:41,040 --> 00:08:41,540
exist.

161
00:08:42,340 --> 00:08:45,120
This is what Elastic uses, right?

162
00:08:45,300 --> 00:08:46,900
But there is another approach.

163
00:08:47,360 --> 00:08:51,960
Back 25 years ago, why did Google
win?

164
00:08:51,960 --> 00:08:53,420
Because it had PageRank.

165
00:08:54,060 --> 00:08:59,820
If we think deeply about this,
it's already from Web 2.0 idea.

166
00:08:59,960 --> 00:09:07,220
Because PageRank takes into account
social network of documents,

167
00:09:07,260 --> 00:09:08,080
I would say.

168
00:09:08,080 --> 00:09:11,020
It takes into account links, right?

169
00:09:11,240 --> 00:09:11,940
So interactions.

170
00:09:12,740 --> 00:09:13,920
We are not alone.

171
00:09:14,120 --> 00:09:15,740
And this idea is super powerful.

172
00:09:15,760 --> 00:09:19,200
I'm curious, are there any extensions
which implement this in

173
00:09:19,200 --> 00:09:21,300
Postgres and how it could be implemented?

174
00:09:21,300 --> 00:09:22,160
This is super interesting.

175
00:09:22,160 --> 00:09:24,640
But this is a big power.

176
00:09:25,680 --> 00:09:30,980
If we have links, we value this
document more.

177
00:09:31,320 --> 00:09:36,400
I think there are good algorithms
how to solve it with billions

178
00:09:36,400 --> 00:09:37,700
of documents of course.

179
00:09:37,900 --> 00:09:42,420
There are papers written, anyone
who studied search engine theory

180
00:09:42,980 --> 00:09:43,680
of course.

181
00:09:44,540 --> 00:09:49,140
There is a course from Stanford
mining massive data sets, very

182
00:09:49,140 --> 00:09:52,760
good material where I studied this
PageRank and other algorithms

183
00:09:53,560 --> 00:09:56,080
closer to machine learning on big
data sets.

184
00:09:56,200 --> 00:09:58,880
So idea is like links, right?

185
00:09:59,240 --> 00:10:03,420
You multiply by some matrices and
you have some good index and

186
00:10:03,420 --> 00:10:04,560
you can use it.

187
00:10:04,640 --> 00:10:06,860
Again, Postgres doesn't provide
this.

188
00:10:06,860 --> 00:10:09,360
Elastic doesn't provide PageRank,
I guess.

189
00:10:09,780 --> 00:10:15,460
But it's good because we start
taking into account not only word

190
00:10:15,460 --> 00:10:15,960
positions.

191
00:10:17,140 --> 00:10:20,720
Full-text search in Postgres, only
word position for single document

192
00:10:21,900 --> 00:10:22,840
and that's it.

193
00:10:23,360 --> 00:10:28,760
But since full-text search in Postgres
is inside relational database

194
00:10:28,780 --> 00:10:32,440
system, we can have other columns,
right?

195
00:10:32,440 --> 00:10:35,240
And we can have indexes on them.

196
00:10:35,740 --> 00:10:39,680
So for example, likes or something
like timestamp when document

197
00:10:39,680 --> 00:10:40,380
was created.

198
00:10:40,640 --> 00:10:44,240
We can construct SQL, which takes
into account all these things.

199
00:10:44,480 --> 00:10:45,780
It will be just slow, right?

200
00:10:45,780 --> 00:10:51,000
Maybe because the idea that the
fastest query is always like

201
00:10:51,040 --> 00:10:53,980
index scan or index only scan is
the best, right?

202
00:10:54,140 --> 00:10:58,080
But when we have full text search,
we have other columns and

203
00:10:58,080 --> 00:11:00,240
we have B-trees probably on them.

204
00:11:00,660 --> 00:11:03,040
Combination is a problem, right?

205
00:11:03,980 --> 00:11:05,600
This is the problem.

206
00:11:05,600 --> 00:11:11,000
But there are things how you can
solve it, but let's maybe discuss

207
00:11:11,000 --> 00:11:14,820
a little bit later, I mean, B-Tree GiST
and other extensions.

208
00:11:15,440 --> 00:11:20,200
But originally, the combination
of different factors in the ranking

209
00:11:20,860 --> 00:11:23,260
task, this is the problem, right?

210
00:11:23,760 --> 00:11:27,460
Later, Google and others, they
started taking into account social

211
00:11:27,720 --> 00:11:28,700
network activities.

212
00:11:29,440 --> 00:11:34,200
If some document is mentioned on
Twitter, for example, or Facebook.

213
00:11:34,960 --> 00:11:37,940
It happened like 15, 10 years ago.

214
00:11:38,500 --> 00:11:42,480
This document, everyone who dealt
with search engine optimization

215
00:11:42,620 --> 00:11:45,760
knows that if you have a lot of
likes in social networks, your

216
00:11:45,760 --> 00:11:48,060
document goes up in positions.

217
00:11:48,720 --> 00:11:52,700
But if we deal with our system,
we can take into account interactions

218
00:11:52,780 --> 00:11:56,260
like that because it's just some
data we store and that's it.

219
00:11:57,040 --> 00:12:01,680
Today it's not that bad usually
if you don't have tens of billions

220
00:12:01,680 --> 00:12:05,580
of records or just millions or
tens or hundreds of millions.

221
00:12:05,680 --> 00:12:08,040
It's not a huge volume of data.

222
00:12:08,080 --> 00:12:11,600
So you can rely on multiple indexes
and let Postgres decide what

223
00:12:11,600 --> 00:12:12,840
to choose based on statistics.

224
00:12:13,140 --> 00:12:16,120
This is very similar to what we
discussed, remember, with...

225
00:12:16,220 --> 00:12:16,720
Yeah?

226
00:12:16,880 --> 00:12:17,860
Yeah, very similar.

227
00:12:17,860 --> 00:12:22,480
Because it triggered my problems
with full-text search.

228
00:12:22,500 --> 00:12:26,300
I had trying to incorporate it
to social media projects.

229
00:12:26,840 --> 00:12:30,060
Because you have 2 indexes, which
to choose?

230
00:12:30,060 --> 00:12:31,680
Because you cannot choose them
together.

231
00:12:31,680 --> 00:12:36,180
Well, you can, you can have bitmap
in the scan, but it's already

232
00:12:36,180 --> 00:12:36,680
slower.

233
00:12:37,200 --> 00:12:41,120
But most likely Postgres will choose
just 1 index in 1, like

234
00:12:41,120 --> 00:12:45,200
for example, over time, or full
text search, but based on statistics.

235
00:12:45,540 --> 00:12:49,600
And then on the fly, it will apply
either sorting in memory or

236
00:12:49,600 --> 00:12:51,020
filtering in memory, right?

237
00:12:51,020 --> 00:12:51,980
Michael: Yeah, exactly.

238
00:12:52,420 --> 00:12:54,360
That's where the inefficiency comes
in.

239
00:12:54,860 --> 00:12:55,620
Nikolay: Yeah, yeah.

240
00:12:55,840 --> 00:12:57,740
So this is like historical overview.

241
00:12:57,740 --> 00:13:03,540
We had like simple stop words, stemming,
dictionary.

242
00:13:03,540 --> 00:13:04,640
It's not simple, by the way.

243
00:13:04,640 --> 00:13:05,780
It's a lot of functionality.

244
00:13:06,660 --> 00:13:13,680
But ranking based on just our opinion
about how this document

245
00:13:13,700 --> 00:13:18,460
is relevant to your query, not
considering relations or other

246
00:13:18,460 --> 00:13:18,960
things.

247
00:13:19,460 --> 00:13:22,360
Second thing is, I think, page
rank and Postgres doesn't have

248
00:13:22,360 --> 00:13:22,860
it.

249
00:13:22,900 --> 00:13:26,740
And the third thing is taking into
account various factors like

250
00:13:26,740 --> 00:13:28,280
time and likes and so on.

251
00:13:28,280 --> 00:13:31,620
But also there is similarity search
now and we know search engines

252
00:13:31,840 --> 00:13:33,840
use it for long, right?

253
00:13:35,020 --> 00:13:35,520
So...

254
00:13:35,600 --> 00:13:37,600
Michael: Well, actually, I want
to go back to...

255
00:13:37,600 --> 00:13:40,580
I think your example is remarkably
useful.

256
00:13:40,640 --> 00:13:45,840
So the example you gave of searching
a site of recipes for eggplant,

257
00:13:45,840 --> 00:13:50,740
or let's add that maybe we're not
super sophisticated, we type

258
00:13:50,740 --> 00:13:51,420
an eggplant.

259
00:13:51,900 --> 00:13:56,020
So we can, for example, say that
an would count as a stop word.

260
00:13:56,630 --> 00:14:01,700
We don't want recipes that say
an a lot to rank highly.

261
00:14:01,920 --> 00:14:03,940
So that's getting rid of the stop
words.

262
00:14:04,380 --> 00:14:07,080
We might want, for example, to
do something slightly more.

263
00:14:07,080 --> 00:14:11,180
We might want to rank recipes that
list eggplant in the title.

264
00:14:11,460 --> 00:14:15,300
We might want those to score higher
than ones where it's listed

265
00:14:15,300 --> 00:14:17,020
in the document more times.

266
00:14:17,020 --> 00:14:20,100
So that's like an interesting additional
challenge.

267
00:14:20,280 --> 00:14:23,800
Secondly, it might be, let's say,
a user-generated recipe site,

268
00:14:23,800 --> 00:14:27,100
and we might want to factor in
how many likes a recipe has got

269
00:14:27,100 --> 00:14:29,460
or how recent it is or something
like that.

270
00:14:29,460 --> 00:14:32,480
So there's like all these, even
in the simplest example you can

271
00:14:32,480 --> 00:14:35,540
think of these days, it can get...

272
00:14:35,580 --> 00:14:37,000
Oh, I've got 1 more.

273
00:14:37,540 --> 00:14:40,860
You might also, if it's user-generated
content, you could have

274
00:14:40,860 --> 00:14:44,480
British people or like people using
aubergine instead of eggplant.

275
00:14:44,480 --> 00:14:46,920
And you also want those to rank
highly.

276
00:14:47,280 --> 00:14:48,220
So yes, exactly.

277
00:14:48,480 --> 00:14:51,760
Nikolay: So synonyms already, it's
already a bridge to discussing

278
00:14:52,540 --> 00:14:53,900
full text search versus similarity.

279
00:14:53,980 --> 00:14:56,540
Let's keep it as the last item,
right?

280
00:14:56,640 --> 00:14:59,600
But let's, great attempt to zoom.

281
00:14:59,600 --> 00:15:00,280
This is good.

282
00:15:00,280 --> 00:15:03,060
Let's do, or diving into deeper.

283
00:15:04,080 --> 00:15:08,980
So let's slightly mention how full-text
search works in Postgres.

284
00:15:09,440 --> 00:15:13,780
So after this pre-processing, we
just discussed like removal

285
00:15:13,780 --> 00:15:15,900
of stop words and you can control
it.

286
00:15:15,900 --> 00:15:19,140
Like good thing about full-text
search in Postgres, a lot of

287
00:15:19,140 --> 00:15:20,180
customization options.

288
00:15:20,180 --> 00:15:20,660
Like A lot.

289
00:15:20,660 --> 00:15:24,000
You, for example, can control stop
list and stop considering

290
00:15:25,020 --> 00:15:26,780
an as a stop word.

291
00:15:28,140 --> 00:15:30,100
Or you can, for example, remove
accents.

292
00:15:30,100 --> 00:15:32,880
There's a special extension on
accent, right?

293
00:15:33,580 --> 00:15:34,620
Additional pre-processing.

294
00:15:35,500 --> 00:15:38,040
Michael: So like in French, for
example, with lots of...

295
00:15:38,040 --> 00:15:41,040
Nikolay: Or there are support of
multiple languages also, it's

296
00:15:41,040 --> 00:15:41,880
also good.

297
00:15:42,180 --> 00:15:45,920
So you can start considering additional
stop words, for example,

298
00:15:45,920 --> 00:15:49,920
remove it if they mention too often,
like, almost all documents

299
00:15:49,920 --> 00:15:52,900
have them, let's remove it because
it doesn't make sense to use

300
00:15:52,900 --> 00:15:53,540
it, right?

301
00:15:53,680 --> 00:15:57,440
Then like stemming or dictionary
processing, I spell dictionary

302
00:15:57,440 --> 00:15:57,940
processing.

303
00:15:58,100 --> 00:16:02,470
So in the end we have a set of
normalized words, right?

304
00:16:02,880 --> 00:16:05,980
And then we build an array.

305
00:16:06,360 --> 00:16:10,540
It's called TS vector, again also
vector by the way.

306
00:16:11,040 --> 00:16:16,100
So it's a set of words with positions,
already normalized, in

307
00:16:16,100 --> 00:16:17,280
a normalized form.

308
00:16:17,660 --> 00:16:21,960
But basically it's just an array
of texts, right?

309
00:16:23,080 --> 00:16:26,260
Michael: Yeah, like words or lexemes
or whatever.

310
00:16:26,760 --> 00:16:27,260
Yeah.

311
00:16:27,380 --> 00:16:31,460
Nikolay: For example, I forgot
also to mention hyphened words.

312
00:16:32,440 --> 00:16:34,820
I think you also can control it.

313
00:16:34,940 --> 00:16:38,200
As I remember, it was many years
ago, I think you can choose

314
00:16:38,200 --> 00:16:42,040
either to consider them as a single
1 word or to split them to

315
00:16:42,040 --> 00:16:47,000
multiple words, like 2 words basically,
first and second, or

316
00:16:47,120 --> 00:16:47,620
together.

317
00:16:48,060 --> 00:16:53,600
Like You can put a pair of separate
words and whole word as hyphen,

318
00:16:53,600 --> 00:16:53,800
right?

319
00:16:53,800 --> 00:16:55,520
A total of 3 already, right?

320
00:16:55,680 --> 00:16:59,120
Michael: So that if people search
1 or the other, they still

321
00:16:59,320 --> 00:17:01,100
get that result back.

322
00:17:01,260 --> 00:17:01,760
Nikolay: Right.

323
00:17:02,220 --> 00:17:03,920
For example, shift left testing.

324
00:17:04,000 --> 00:17:09,220
Shift left, if it's a hyphen, you
can put both shift left and

325
00:17:09,220 --> 00:17:13,260
shift left as 3 separate entries
in your

326
00:17:13,260 --> 00:17:14,120
Michael: test vector.

327
00:17:14,380 --> 00:17:17,500
Nikolay: You can control how Postgres
will do it, building test

328
00:17:17,500 --> 00:17:18,000
vectors.

329
00:17:18,080 --> 00:17:21,580
Also, you can concatenate test
vectors so data can come from

330
00:17:21,580 --> 00:17:22,400
different sources.

331
00:17:22,940 --> 00:17:27,940
For example, we have title, we
have body, subject, or body, like

332
00:17:27,940 --> 00:17:32,140
for example if it's email, or if
it's blog posts, title and content,

333
00:17:32,620 --> 00:17:36,220
text, how you name it, it doesn't
matter.

334
00:17:36,220 --> 00:17:40,620
And you can concatenate 2 TS vectors,
or you can concatenate

335
00:17:40,680 --> 00:17:45,840
it before that with additional
space and then build TS vector.

336
00:17:46,500 --> 00:17:50,700
So TS vector is just an array of
texts, and you can explicitly

337
00:17:50,800 --> 00:17:52,740
store it, consuming space.

338
00:17:53,300 --> 00:17:57,380
Or you can rely on function, because
then we need to create index

339
00:17:57,380 --> 00:17:58,100
on it.

340
00:17:58,580 --> 00:18:00,040
Originally it was GIST.

341
00:18:00,860 --> 00:18:09,520
GIST, I think it was the work of
Heikki Linnakangas in Berkeley very

342
00:18:09,520 --> 00:18:12,180
long ago, in the early 90s maybe
or when.

343
00:18:12,800 --> 00:18:17,220
It was not finished, but the guys
who I mentioned, Bartunov and

344
00:18:17,220 --> 00:18:23,000
Sigaev, they just saw this directory
in PostgreSQL source code, as

345
00:18:23,000 --> 00:18:26,860
they say, and understood this is
exactly what they need.

346
00:18:27,340 --> 00:18:28,860
There's also an additional thing.

347
00:18:29,160 --> 00:18:32,940
GiST is a generalized search tree.

348
00:18:34,440 --> 00:18:38,200
It's similar to B-tree, but it's
abstract.

349
00:18:40,240 --> 00:18:43,580
You can use it for many data types
and data operators.

350
00:18:44,920 --> 00:18:49,040
For B-tree, it will be numbers
and operators less than or more

351
00:18:49,040 --> 00:18:50,580
than and equals.

352
00:18:51,020 --> 00:18:52,620
And it's just 1 axis, right?

353
00:18:52,740 --> 00:18:57,180
R3 it's 2 axes and then you can
have it for arrays as well.

354
00:18:57,340 --> 00:19:03,660
In this case we talk about operators
intersect, includes, contains,

355
00:19:04,200 --> 00:19:08,440
is contained by, and overlaps.

356
00:19:08,660 --> 00:19:10,820
Overlaps is at at, usually.

357
00:19:11,600 --> 00:19:15,260
You can actually redefine it and
use your own symbols.

358
00:19:15,720 --> 00:19:19,380
It's actually first you define
function, then operator, since

359
00:19:19,380 --> 00:19:20,580
Postgres is very extendable.

360
00:19:21,180 --> 00:19:26,920
But historically it's at at, or
less than at, or at greater

361
00:19:26,920 --> 00:19:27,420
than.

362
00:19:28,420 --> 00:19:33,600
So these operators, you can define
how to deal with them in terms

363
00:19:33,600 --> 00:19:35,820
of this B-tree-like index structure.

364
00:19:36,140 --> 00:19:40,880
So it's balanced with many, many
children on each node.

365
00:19:41,540 --> 00:19:44,120
And balancing is automated, everything
is automated.

366
00:19:44,760 --> 00:19:49,420
So when these folks found this Heikki Linnakangas's
work, it was not

367
00:19:49,420 --> 00:19:51,800
finished, and the WAL was not
supported.

368
00:19:52,200 --> 00:19:55,100
They worked on that supported WAL.

369
00:19:55,360 --> 00:20:00,140
Then additional thing, we won't
go deep because of lack of time

370
00:20:00,140 --> 00:20:03,080
and I also forgot everything, But
there's also a thing that's

371
00:20:03,080 --> 00:20:04,680
called signature tree.

372
00:20:04,960 --> 00:20:09,180
So to put our TS vectors to this
structure, there's additional

373
00:20:09,220 --> 00:20:11,460
concept of signature tree, you
need to build signature.

374
00:20:11,760 --> 00:20:12,840
It's also defined.

375
00:20:13,380 --> 00:20:18,360
So these signatures are placed
into the leaves of this tree.

376
00:20:19,020 --> 00:20:23,980
And then search is very bit tree-like,
but with additional exclusion,

377
00:20:24,320 --> 00:20:29,840
which makes today's GiST option
is not popular at all.

378
00:20:30,020 --> 00:20:35,460
Recheck is needed, because it's
not certain, this search.

379
00:20:36,740 --> 00:20:39,020
Michael: I've heard it's called
lossy index types.

380
00:20:39,320 --> 00:20:39,960
Nikolay: Right, right.

381
00:20:39,960 --> 00:20:44,200
So if you check, this is what you
do in pgMustard, right?

382
00:20:44,200 --> 00:20:44,920
Yeah, yeah.

383
00:20:44,920 --> 00:20:46,780
EXPLAIN, ANALYZE, BUFFERS, right?

384
00:20:46,920 --> 00:20:49,340
You check and see in the plan,
recheck happened.

385
00:20:49,640 --> 00:20:51,560
If GiST was used, recheck happened.

386
00:20:52,200 --> 00:20:54,780
So let me close some gap.

387
00:20:54,940 --> 00:21:00,040
Query also processed to build a
vector, right?

388
00:21:00,040 --> 00:21:01,320
But it's called TS query.

389
00:21:01,320 --> 00:21:04,340
It's called TS query, different
data type, but it's also similar.

390
00:21:04,340 --> 00:21:07,100
Also a bunch of words, also with
pre-processing.

391
00:21:07,380 --> 00:21:10,580
You can have different pre-processing,
you decide how to pre-process,

392
00:21:10,680 --> 00:21:11,180
right?

393
00:21:11,380 --> 00:21:15,400
But usually it's same as for TS
vector, for TS query, same pre-processing

394
00:21:15,440 --> 00:21:20,500
also remove stop words, remove
normalized words, and so on.

395
00:21:20,600 --> 00:21:26,380
So, and then our question is, let's
find everything which includes,

396
00:21:26,680 --> 00:21:29,820
like, we need to find documents
which include everything we asked

397
00:21:29,820 --> 00:21:31,600
for in our query, right?

398
00:21:32,340 --> 00:21:34,140
Michael: And rank them, but yeah.

399
00:21:34,300 --> 00:21:36,040
Nikolay: Yeah, and rank also additionally.

400
00:21:36,220 --> 00:21:42,100
So there is approach to put
it to trees, to such values, to

401
00:21:42,100 --> 00:21:42,600
leaves.

402
00:21:42,640 --> 00:21:48,480
And then it searches similar to
B-tree, but instead of greater

403
00:21:48,480 --> 00:21:55,160
than or less than, instead of that,
it checks if this vector

404
00:21:55,160 --> 00:21:56,620
is contained in that vector.

405
00:21:56,760 --> 00:22:00,040
I mean, this array is contained
in that array, and that's why

406
00:22:00,040 --> 00:22:02,660
you need to go left or right, something
like this.

407
00:22:02,800 --> 00:22:06,900
So it's called Russian doll tree
because it's like Russian doll,

408
00:22:06,900 --> 00:22:07,400
right?

409
00:22:08,000 --> 00:22:08,500
Somehow.

410
00:22:08,860 --> 00:22:09,360
RD3.

411
00:22:09,520 --> 00:22:14,280
So by the way, it's also in Berkeley
papers, this term RD3, somehow.

412
00:22:14,540 --> 00:22:19,020
So I think it was invented before
Bartunov and Kratkov.

413
00:22:19,020 --> 00:22:19,520
Probably.

414
00:22:19,540 --> 00:22:21,540
Maybe they influenced, I don't
know.

415
00:22:21,900 --> 00:22:27,320
But then, obviously it's slow in
terms of search for large volumes

416
00:22:27,320 --> 00:22:28,800
of data because of this recheck.

417
00:22:30,140 --> 00:22:33,780
This is not how Google and others
worked in terms of like...

418
00:22:33,940 --> 00:22:34,740
Not even Google.

419
00:22:34,740 --> 00:22:37,160
Google, we know, page rank is a
bigger thing.

420
00:22:37,360 --> 00:22:41,140
But eventually, Bartunov, Sigaev,
and then Kratkov, they created

421
00:22:41,520 --> 00:22:42,020
GIN.

422
00:22:42,340 --> 00:22:46,580
GIN is a generalized inverted tree
where we have a list of terms,

423
00:22:47,080 --> 00:22:50,780
our words, and for each list of...

424
00:22:51,960 --> 00:22:52,460
Michael: Documents.

425
00:22:52,900 --> 00:22:57,800
Nikolay: Right, all tuple IDs,
tuple CTIDs, I think, where this

426
00:22:57,800 --> 00:23:02,460
is stored, but not lists, there
are bit trees there, actually,

427
00:23:02,880 --> 00:23:04,060
for faster search.

428
00:23:04,640 --> 00:23:10,180
So it means that Gist is good only
at 1 thing, update speed.

429
00:23:10,240 --> 00:23:11,420
Michael: Gist fast is gin.

430
00:23:11,760 --> 00:23:12,540
Nikolay: Right, right.

431
00:23:12,560 --> 00:23:15,920
But gin, it was improved, there
is also fast update option.

432
00:23:16,080 --> 00:23:19,620
But anyway, default option for
us is gin, right?

433
00:23:21,180 --> 00:23:27,540
And comparing tsQuery and tsVector,
gin is good, search is fast,

434
00:23:28,340 --> 00:23:31,660
but order by tsRank, which is not
probably good.

435
00:23:31,960 --> 00:23:36,780
So back to the comparison, back
to these use cases you started

436
00:23:36,780 --> 00:23:37,280
mentioning.

437
00:23:37,440 --> 00:23:38,740
Let's think about them.

438
00:23:38,940 --> 00:23:44,380
First, you said, or at least second,
let's distinguish subject

439
00:23:44,380 --> 00:23:46,020
and body, right, for example.

440
00:23:47,100 --> 00:23:50,580
Michael: Yeah, or should we rank,
should we, should the, the

441
00:23:50,580 --> 00:23:53,400
presence of an ingredient in

442
00:23:54,520 --> 00:23:54,940
Nikolay: it or

443
00:23:54,940 --> 00:23:55,580
Michael: let's yeah.

444
00:23:55,580 --> 00:23:58,600
Words because we know that people
aren't going to be searching

445
00:23:58,680 --> 00:24:01,800
in our like search bar for random
things.

446
00:24:01,800 --> 00:24:04,540
Like maybe there's like a few different
things they might search

447
00:24:04,540 --> 00:24:04,840
by.

448
00:24:04,840 --> 00:24:06,360
The most common is probably ingredient.

449
00:24:06,840 --> 00:24:11,880
Should recipes that list that in
their title rank higher, or

450
00:24:11,880 --> 00:24:15,660
be weighted higher, even if they
only mention it like a few couple

451
00:24:15,660 --> 00:24:20,740
of times later on, I feel like
personally I'd be expecting those

452
00:24:20,740 --> 00:24:24,660
to rank higher than ones where
it was a small ingredient, or

453
00:24:24,660 --> 00:24:27,020
I only needed a small amount of
it maybe.

454
00:24:27,040 --> 00:24:29,440
Maybe the amount of the ingredient
matters a lot.

455
00:24:29,640 --> 00:24:30,600
Maybe that's easier.

456
00:24:31,380 --> 00:24:31,900
Nikolay: Right, right.

457
00:24:31,900 --> 00:24:39,200
So it's definitely a good thing
to want, right?

458
00:24:39,380 --> 00:24:43,180
If a word is entitled, it means
that maybe the whole article

459
00:24:43,180 --> 00:24:43,980
is about this, right?

460
00:24:43,980 --> 00:24:47,300
If eggplant is entitled, it pays
attention more to it.

461
00:24:47,440 --> 00:24:48,120
Makes sense.

462
00:24:48,120 --> 00:24:52,440
So for this, there are 2 bits which
you can use.

463
00:24:52,940 --> 00:24:55,940
2 bits means 4 options, right?

464
00:24:56,040 --> 00:24:57,880
So there is a function setWeight.

465
00:24:59,180 --> 00:25:02,800
And when you combine information
from multiple sources, from

466
00:25:02,800 --> 00:25:06,540
title column and content column,
for example, body column, you

467
00:25:06,540 --> 00:25:12,040
can say set weight A to first 1,
set weight B to second 1.

468
00:25:12,880 --> 00:25:19,180
And they are only ABCD, uppercase,
because again, 2 bytes only

469
00:25:19,180 --> 00:25:20,360
are used for this.

470
00:25:20,860 --> 00:25:25,580
And then later in your query, you
can also say, I pay attention

471
00:25:25,580 --> 00:25:26,820
to this or to that.

472
00:25:26,980 --> 00:25:29,020
Actually, you can use it for filtering
as well.

473
00:25:29,020 --> 00:25:30,320
And this is what I did.

474
00:25:30,560 --> 00:25:32,460
But originally it was created for
ranking.

475
00:25:33,300 --> 00:25:36,900
I don't remember details, but setWeight
function you need to search

476
00:25:36,900 --> 00:25:40,600
in documentation, it should be
there and you will find out how

477
00:25:40,600 --> 00:25:41,040
to...

478
00:25:41,040 --> 00:25:42,540
It's a very strange concept.

479
00:25:44,480 --> 00:25:45,540
Why only 4?

480
00:25:45,780 --> 00:25:47,080
Because of 2 bits only.

481
00:25:47,080 --> 00:25:49,540
They had only 2 bits to spend for
it.

482
00:25:49,960 --> 00:25:51,700
Maybe there should be more.

483
00:25:51,740 --> 00:25:56,820
But I use it for sometimes like
we search only in subject, right?

484
00:25:56,820 --> 00:25:58,660
And I say, okay, only a category.

485
00:25:58,660 --> 00:26:00,040
It's called category maybe.

486
00:26:00,040 --> 00:26:02,260
It's embedded inside TS vector.

487
00:26:02,720 --> 00:26:05,140
So you can skip using it.

488
00:26:05,460 --> 00:26:07,900
It's there, but you can just ignore
it in search.

489
00:26:07,900 --> 00:26:11,340
But in different time, you can
say, I want to search only category

490
00:26:11,460 --> 00:26:13,360
A, it means only title search.

491
00:26:13,740 --> 00:26:16,740
You don't need to build 2 TS vectors
separately.

492
00:26:16,940 --> 00:26:18,420
However, you could, right?

493
00:26:19,140 --> 00:26:19,480
Michael: Got it.

494
00:26:19,480 --> 00:26:23,100
So like, if, for example, going
back to our example, I search

495
00:26:23,100 --> 00:26:27,260
what's clearly like an author name,
and we can maybe on the application

496
00:26:27,440 --> 00:26:31,360
side, we're doing a little like
a quick, Maybe we're doing something

497
00:26:31,360 --> 00:26:34,080
first to try and categorize what
people are searching for.

498
00:26:34,080 --> 00:26:37,320
If it's an author name, I could
then send that through to Postgres

499
00:26:37,540 --> 00:26:39,940
as like, let's only look in this
category.

500
00:26:40,380 --> 00:26:41,040
Nikolay: Right, right.

501
00:26:41,040 --> 00:26:44,160
But again, it originally was created
for ranking.

502
00:26:44,200 --> 00:26:45,660
I just didn't use it.

503
00:26:45,660 --> 00:26:48,940
So I cannot explain how the documentation
of course, explains

504
00:26:48,940 --> 00:26:49,440
how.

505
00:26:49,820 --> 00:26:50,320
Right.

506
00:26:50,820 --> 00:26:54,960
So good thing to mention also,
now we have generated always thing,

507
00:26:54,960 --> 00:26:55,460
right?

508
00:26:56,020 --> 00:26:56,980
Generated columns.

509
00:26:57,740 --> 00:27:04,520
They are stored, but Postgres maintains
them automatically.

510
00:27:04,780 --> 00:27:08,320
So, I think generation of TSVector
is probably a good thing to

511
00:27:08,320 --> 00:27:10,220
use together with that functionality.

512
00:27:11,160 --> 00:27:17,760
So you have author, title, body
of blog post, and then TSVector

513
00:27:17,860 --> 00:27:23,000
can be generated based on some
expression, which puts different

514
00:27:23,000 --> 00:27:29,020
categories to different sources
of data, again up to four, and generates

515
00:27:29,020 --> 00:27:32,140
and puts TSVector there, and then
you have an index on it, right?

516
00:27:32,360 --> 00:27:34,040
Or you can define a trigger.

517
00:27:34,740 --> 00:27:37,620
All my life I defined triggers
for this.

518
00:27:37,900 --> 00:27:40,680
And also there is another option
not to store it at all and just

519
00:27:40,680 --> 00:27:45,420
use index on expression, GIN, and
then your big expression, maybe

520
00:27:45,420 --> 00:27:48,500
with this set weight we just discussed.

521
00:27:49,080 --> 00:27:52,660
But in this case, it's good in
terms of storage.

522
00:27:53,100 --> 00:27:55,020
Less thing to keep in memory.

523
00:27:55,900 --> 00:27:58,760
But it might be bad in terms of...

524
00:28:00,060 --> 00:28:00,820
I don't know.

525
00:28:00,820 --> 00:28:06,360
Sometimes you need to deal with
TSVector directly.

526
00:28:07,840 --> 00:28:11,980
If you have only expressional index,
then you need to construct

527
00:28:11,980 --> 00:28:15,040
this expression once again to deal
with it.

528
00:28:16,460 --> 00:28:20,440
I don't like this approach somehow,
because it limits in terms

529
00:28:20,440 --> 00:28:23,140
of what you can do with such records.

530
00:28:23,940 --> 00:28:28,420
So, if you don't store it, you
need to build it on the fly to

531
00:28:28,420 --> 00:28:32,420
deal with it, to additionally somehow
analyze it or so.

532
00:28:32,780 --> 00:28:37,080
So I always prefer to store them,
although they can consume a

533
00:28:37,080 --> 00:28:39,280
lot of data and the TOAST and so
on.

534
00:28:39,280 --> 00:28:42,260
Sometimes I just put them to separate
table understanding that

535
00:28:42,260 --> 00:28:43,740
then I will need to join.

536
00:28:44,180 --> 00:28:48,240
For me it's easier to create a
trigger and use a regular column,

537
00:28:48,240 --> 00:28:48,740
right?

538
00:28:49,540 --> 00:28:53,800
But if you think to store it or
not to store it, it's good to

539
00:28:53,800 --> 00:28:56,340
think what you will do with it.

540
00:28:56,800 --> 00:29:03,180
And if you want to deal with TSVector
in different ways, then

541
00:29:03,180 --> 00:29:06,500
keeping it stored only in the index
itself.

542
00:29:06,500 --> 00:29:11,260
It's maybe not enough and maybe
it's good to put it to the table.

543
00:29:11,320 --> 00:29:15,840
But to put it to the same table
as a column and rely on TOASTing

544
00:29:15,840 --> 00:29:21,140
and so on, or maybe to allocate
a different table and like one-to-one

545
00:29:21,220 --> 00:29:21,720
relationship?

546
00:29:23,200 --> 00:29:24,220
It's a good question.

547
00:29:24,840 --> 00:29:26,420
Again, depending on workloads.

548
00:29:29,040 --> 00:29:31,160
So okay, This we covered.

549
00:29:31,340 --> 00:29:34,060
Can you remind me of other use
cases you mentioned?

550
00:29:34,940 --> 00:29:37,800
Michael: Well, I think it's all
I consider this kind of one use

551
00:29:37,800 --> 00:29:41,020
case, but I guess, I guess there's
like all the complexities

552
00:29:41,120 --> 00:29:43,220
that you may or may not want to
support.

553
00:29:43,480 --> 00:29:47,900
1 that I didn't mention, but probably
we care about is what if

554
00:29:47,900 --> 00:29:50,420
somebody spells eggplant with 1
g?

555
00:29:51,140 --> 00:29:52,120
Nikolay: Yeah, typos.

556
00:29:52,480 --> 00:29:52,980
Michael: Yes.

557
00:29:53,760 --> 00:29:57,880
Nikolay: For this, there is pg_trgm,
3 grams.

558
00:29:59,540 --> 00:30:02,420
Extension, which actually also
uses RD3.

559
00:30:02,840 --> 00:30:03,580
Gin, actually.

560
00:30:03,580 --> 00:30:04,400
Gin these days.

561
00:30:04,400 --> 00:30:05,540
RD3, forget about it.

562
00:30:05,540 --> 00:30:08,440
Forget about GiST, very rarely
used.

563
00:30:08,560 --> 00:30:09,820
So Gin, right?

564
00:30:10,020 --> 00:30:11,900
So we have some text, right?

565
00:30:12,040 --> 00:30:12,700
Or some...

566
00:30:12,700 --> 00:30:15,820
Basically, 3-grams should work
for words, right?

567
00:30:15,820 --> 00:30:19,460
We have word, and we suspect maybe
there is a typo there.

568
00:30:19,820 --> 00:30:24,940
So it depends 1 space on 1 side
and 2 spaces to different side

569
00:30:24,940 --> 00:30:29,920
and then just split for 3 words,
3 letters, 3 letters, 3 letters,

570
00:30:29,920 --> 00:30:30,420
right?

571
00:30:30,520 --> 00:30:32,380
And then we have array again, right?

572
00:30:33,580 --> 00:30:34,080
Vector.

573
00:30:34,840 --> 00:30:35,960
Vector, right.

574
00:30:36,060 --> 00:30:39,120
And then the question is which
is the most, the closest.

575
00:30:39,120 --> 00:30:41,420
Closest means like most overlapping.

576
00:30:42,440 --> 00:30:46,900
Most of members of array are the
same.

577
00:30:47,440 --> 00:30:49,380
Might be not all of them.

578
00:30:49,740 --> 00:30:57,320
And if it's just 1 letter typo,
it means 3 members of this vector

579
00:30:58,620 --> 00:30:59,940
will be different.

580
00:31:00,720 --> 00:31:01,560
Michael: Ah, nice.

581
00:31:01,560 --> 00:31:02,060
Yeah.

582
00:31:02,620 --> 00:31:08,040
Well, it wouldn't include EGG,
it wouldn't include GGP, and it...

583
00:31:08,560 --> 00:31:09,220
Is that...

584
00:31:09,220 --> 00:31:10,360
They're the only 2?

585
00:31:10,520 --> 00:31:13,840
In this case, I think, possibly,
just they're the only 2.

586
00:31:14,240 --> 00:31:16,920
Nikolay: So 3-grams, We're just
shifting, shifting, shifting

587
00:31:16,920 --> 00:31:17,620
also, right?

588
00:31:17,620 --> 00:31:19,340
We don't just split, we're shifting.

589
00:31:19,510 --> 00:31:24,940
So start position, first is,
for example, space, then 2 letters,

590
00:31:24,960 --> 00:31:27,720
then second, the first letter, second
letter, third letter, shift,

591
00:31:27,720 --> 00:31:30,760
shift, shift, and we construct
array out of it.

592
00:31:30,920 --> 00:31:39,180
And then if, for example, you just
missed, forgot 1 letter, overlapping

593
00:31:39,240 --> 00:31:40,120
will be huge.

594
00:31:40,200 --> 00:31:41,520
Distance is very close.

595
00:31:41,580 --> 00:31:44,780
Similarities, distance low, similarities
high.

596
00:31:45,100 --> 00:31:49,440
If you, for example, mixed positions
of 2 letters, just swap

597
00:31:49,440 --> 00:31:49,940
them.

598
00:31:50,740 --> 00:31:51,660
Also huge.

599
00:31:52,760 --> 00:31:56,020
This is an interesting idea behind
3-grams.

600
00:31:56,460 --> 00:32:00,920
And again, we use either RD3, nobody
does it, or Gin.

601
00:32:01,240 --> 00:32:07,620
GIN, we use GIN so we can find
arrays which are closest, overlapping

602
00:32:07,740 --> 00:32:08,440
is higher.

603
00:32:08,720 --> 00:32:12,900
That's how we find words which
actually present in our document

604
00:32:13,260 --> 00:32:13,760
dataset.

605
00:32:15,140 --> 00:32:17,860
And then they say, you probably
thought about this word.

606
00:32:18,400 --> 00:32:23,420
1 thing, in the past I remember
we maintained the list of words

607
00:32:24,760 --> 00:32:28,480
with usage counts probably, using
some statistics provided by

608
00:32:28,520 --> 00:32:29,560
full text search.

609
00:32:30,040 --> 00:32:33,680
I don't remember details, but you
can build a list of words with

610
00:32:33,680 --> 00:32:38,660
stats, and then you can store it
in a table, and then use trigrams

611
00:32:38,760 --> 00:32:39,840
on top of it.

612
00:32:40,580 --> 00:32:43,980
You need triggers to maintain this,
or you need to refresh it

613
00:32:43,980 --> 00:32:44,480
periodically.

614
00:32:45,060 --> 00:32:48,200
For example, if you just imported
a lot of documents which use

615
00:32:48,200 --> 00:32:51,980
new words, this list of words is
already outdated, you need to

616
00:32:51,980 --> 00:32:52,800
rebuild it.

617
00:32:53,080 --> 00:32:58,980
And then this Trigram approach
worked, you have input words,

618
00:32:59,180 --> 00:33:01,160
you check the table and it works.

619
00:33:01,240 --> 00:33:03,240
Now I think we don't need it, right?

620
00:33:03,240 --> 00:33:05,820
It's automatically maintain this
list of words.

621
00:33:06,900 --> 00:33:11,540
Or we just don't care and build
an index and then take whole

622
00:33:11,540 --> 00:33:12,040
input.

623
00:33:12,740 --> 00:33:14,760
Michael: That's how I've seen it
used, yeah.

624
00:33:15,180 --> 00:33:18,720
Nikolay: So there are several ways
to use it, and maintaining

625
00:33:18,780 --> 00:33:22,320
this additional table probably
still makes sense, right?

626
00:33:23,300 --> 00:33:24,240
List of words.

627
00:33:25,760 --> 00:33:27,980
Michael: Do you think it would
then speed up queries, or what

628
00:33:27,980 --> 00:33:29,440
do you think the benefit is?

629
00:33:29,540 --> 00:33:30,040
Speed?

630
00:33:30,180 --> 00:33:32,120
Nikolay: You lose speed here probably,
right?

631
00:33:32,420 --> 00:33:34,140
But accuracy is good.

632
00:33:34,340 --> 00:33:35,440
Michael: Accuracy, yeah.

633
00:33:37,060 --> 00:33:41,120
This is great, but I think the
conversation you mentioned earlier,

634
00:33:41,120 --> 00:33:44,600
like the comparison of full-text
search versus semantic search

635
00:33:44,600 --> 00:33:49,640
in the current climate, and something
I wanted to introduce was

636
00:33:50,680 --> 00:33:55,020
whether the use case suits false
positives, or suits false negatives

637
00:33:55,020 --> 00:33:58,660
better, like I think it's really
difficult to, to come up with

638
00:33:58,660 --> 00:34:00,720
a solution that that does neither.

639
00:34:00,720 --> 00:34:05,140
But often you see kind of slightly
biased towards like is it

640
00:34:05,140 --> 00:34:09,640
better like Google for example
what PageRank was really good

641
00:34:09,640 --> 00:34:12,540
at was making sure it didn't miss
a website that was clearly

642
00:34:12,540 --> 00:34:16,860
very relevant to your search term
and so it was very very good

643
00:34:16,860 --> 00:34:21,080
at avoiding false negatives but
you did often especially in the

644
00:34:21,080 --> 00:34:23,040
earlier days get quite a lot of
false positives.

645
00:34:23,100 --> 00:34:27,100
You'd get articles that didn't
match your intention, at least,

646
00:34:27,100 --> 00:34:28,740
even if it did match the words.

647
00:34:29,020 --> 00:34:32,640
But the recipe example, maybe we
don't care so much about like,

648
00:34:32,640 --> 00:34:37,040
if you miss 1 great recipe, but
you get 17 perfectly matched

649
00:34:37,040 --> 00:34:38,680
ones, that's still a good result.

650
00:34:38,680 --> 00:34:41,760
Maybe you'd rather that kind of
weigh around on the trade off.

651
00:34:41,760 --> 00:34:44,000
I don't know how you see that and
whether that's relevant.

652
00:34:44,060 --> 00:34:48,140
Nikolay: I see this as many different
small use cases.

653
00:34:49,460 --> 00:34:55,100
For example, if first of all, I
remember your different use cases.

654
00:34:55,120 --> 00:34:56,480
This is what I mentioned as well.

655
00:34:56,480 --> 00:35:01,740
Like we want to take into account
likes, comments, timestamp,

656
00:35:02,040 --> 00:35:02,540
everything.

657
00:35:03,160 --> 00:35:07,200
It gives us ability to build very
big query and then we think,

658
00:35:07,200 --> 00:35:11,380
okay, but our full text search,
single GIN index is not enough,

659
00:35:11,380 --> 00:35:11,880
right?

660
00:35:12,260 --> 00:35:16,740
Sometimes we can use bit.ly gist,
for example, right?

661
00:35:16,980 --> 00:35:22,320
Which allows us combining these
arrays of text and regular numbers

662
00:35:22,440 --> 00:35:24,800
or timestamps in a single index.

663
00:35:25,440 --> 00:35:26,420
And this is good.

664
00:35:27,380 --> 00:35:29,060
We can have single index scan.

665
00:35:29,060 --> 00:35:30,700
Also there is B-tree GiST.

666
00:35:31,740 --> 00:35:32,920
Also interesting thing.

667
00:35:33,520 --> 00:35:36,660
Also there is RAM which you need
to install as a separate extension,

668
00:35:36,660 --> 00:35:41,680
but these 2, B-tree GiST and B-tree GIN,
they are included with regular

669
00:35:42,660 --> 00:35:45,260
contrib modules, so with any Postgres
it's available.

670
00:35:46,100 --> 00:35:50,280
But in some cases we want, like
I forgot to mention Postgres

671
00:35:50,280 --> 00:35:51,420
also supports it.

672
00:35:52,360 --> 00:35:56,640
I think functional web search to
test query or something like

673
00:35:56,640 --> 00:35:57,140
this.

674
00:35:57,280 --> 00:36:03,080
It supports some small language
of phrase search and inverted

675
00:36:03,120 --> 00:36:06,440
search, you can exclude, user can
say, I want everything but

676
00:36:06,440 --> 00:36:07,940
this word should not be there.

677
00:36:08,180 --> 00:36:09,360
Just minus, right?

678
00:36:09,520 --> 00:36:12,880
Or put in double quotes for exact
match.

679
00:36:14,380 --> 00:36:19,060
This all great means that user
can control and in regular manner

680
00:36:19,080 --> 00:36:21,840
Google also supports things like
that, right?

681
00:36:22,160 --> 00:36:28,760
So it means that you want, for
example, to see exact phrase mentioning,

682
00:36:28,780 --> 00:36:32,420
well you know it's there and you
just can do it.

683
00:36:32,780 --> 00:36:37,420
But then similarity search, 2 big
use cases.

684
00:36:37,420 --> 00:36:39,520
For example, you mentioned synonyms,
right?

685
00:36:40,080 --> 00:36:44,760
Someone doesn't use the word eggplant,
there's another word meaning

686
00:36:44,760 --> 00:36:45,640
the same thing.

687
00:36:46,180 --> 00:36:46,680
Michael: Aubergine.

688
00:36:49,760 --> 00:36:51,740
It's honestly, that's what we call
them.

689
00:36:51,740 --> 00:36:52,240
Aubergine.

690
00:36:52,740 --> 00:36:53,240
A-U-B-E-R.

691
00:36:54,000 --> 00:36:55,000
I'm not going to try the

692
00:36:55,000 --> 00:36:55,260
Nikolay: rest.

693
00:36:55,260 --> 00:36:56,540
I haven't heard it.

694
00:36:56,660 --> 00:36:57,160
Cool.

695
00:36:57,540 --> 00:37:02,900
So if you want a synonym search,
full-text search supports it.

696
00:37:02,980 --> 00:37:04,300
But you need to maintain it.

697
00:37:04,300 --> 00:37:06,840
You need to maintain the dictionary
of synonyms.

698
00:37:07,780 --> 00:37:12,260
And the normalization process will
automatically, all synonyms

699
00:37:12,340 --> 00:37:16,820
will be defined in our test vector
and test query it will be

700
00:37:17,040 --> 00:37:18,080
1 word we chose.

701
00:37:18,080 --> 00:37:19,740
So we can maintain synonyms.

702
00:37:19,920 --> 00:37:20,680
It's easy.

703
00:37:20,680 --> 00:37:21,220
Not easy.

704
00:37:21,220 --> 00:37:22,760
I mean, it's straightforward.

705
00:37:23,000 --> 00:37:26,160
It's not easy because it requires
effort of maintaining synonyms,

706
00:37:26,160 --> 00:37:26,660
right?

707
00:37:27,660 --> 00:37:35,500
But on the other hand, if we use
pgVector, it probably puts both

708
00:37:35,500 --> 00:37:39,800
words in this highly dimensional
space very close, right?

709
00:37:40,640 --> 00:37:42,180
Because meaning is the same.

710
00:37:43,840 --> 00:37:44,700
Right, interesting.

711
00:37:45,040 --> 00:37:49,540
So maybe semantic search provided
by pgVector is better here,

712
00:37:49,540 --> 00:37:50,040
right?

713
00:37:51,160 --> 00:37:51,660
Okay.

714
00:37:51,960 --> 00:37:56,420
Another thing is, for example,
something is not working versus

715
00:37:56,420 --> 00:37:58,100
something is working.

716
00:37:58,620 --> 00:38:00,740
Not is a stop word, right?

717
00:38:02,420 --> 00:38:03,660
What do we try to search?

718
00:38:03,740 --> 00:38:04,740
Something is not working.

719
00:38:04,740 --> 00:38:06,360
For example, my car is not working.

720
00:38:06,780 --> 00:38:11,180
Maybe not working I should put
in double quotes, so not as definitely

721
00:38:11,260 --> 00:38:13,640
there for exact phrase search.

722
00:38:13,940 --> 00:38:18,120
Or maybe I just need to use pgVector
because it definitely will

723
00:38:18,120 --> 00:38:22,480
distinguish semantically that not
working and working are very fine

724
00:38:22,480 --> 00:38:26,600
in this highly dimensional space,
so similarity is not good,

725
00:38:26,600 --> 00:38:26,880
right?

726
00:38:26,880 --> 00:38:28,540
Distance is not good.

727
00:38:28,980 --> 00:38:34,820
And if we are trying to find my
car is not working, we won't

728
00:38:34,820 --> 00:38:36,000
find documents.

729
00:38:37,120 --> 00:38:42,380
They will be put not high, right,
in terms of ranking by similarity.

730
00:38:43,680 --> 00:38:44,740
But also PgVector...

731
00:38:44,980 --> 00:38:45,480
AL

732
00:38:45,720 --> 00:38:47,220
Michael: SUTTON, I think that depends,
though.

733
00:38:47,220 --> 00:38:50,360
I think that's contextually important
and interesting because

734
00:38:50,820 --> 00:38:55,440
let's say this was a car forum,
how many posts are you going

735
00:38:55,440 --> 00:38:57,360
to find about cars that are working great?

736
00:38:57,360 --> 00:38:59,180
Like realistically in the

737
00:38:59,180 --> 00:38:59,627
Nikolay: data set?

738
00:38:59,627 --> 00:39:00,507
Michael: Well, it's a bad

739
00:39:00,507 --> 00:39:05,040
Nikolay: example. It was a bad
example, but in general, inverted,

740
00:39:06,280 --> 00:39:11,080
since how full text search works
in Postgres, it will remove

741
00:39:11,520 --> 00:39:13,040
not as a stop word.

742
00:39:13,700 --> 00:39:15,080
And it's bad sometimes, right?

743
00:39:15,080 --> 00:39:17,300
Because it's the opposite meaning,
right?

744
00:39:18,500 --> 00:39:19,340
Michael: Yeah, makes sense.

745
00:39:19,340 --> 00:39:20,560
I didn't realize that.

746
00:39:20,640 --> 00:39:23,440
Nikolay: We might think about similar
examples where the removal

747
00:39:23,440 --> 00:39:31,260
of the stop word basically leads to
very bad ranking or filtering,

748
00:39:31,320 --> 00:39:31,820
also.

749
00:39:32,360 --> 00:39:35,860
Michael: Yeah, like one that comes
up quite often is like film

750
00:39:35,860 --> 00:39:38,800
titles or like band titles.

751
00:39:38,800 --> 00:39:41,780
If you put the in front of it,
it could be a completely different

752
00:39:41,780 --> 00:39:42,280
band.

753
00:39:42,860 --> 00:39:50,980
Nikolay: Or some English is very,
has very such example, a lot

754
00:39:50,980 --> 00:39:51,820
of such examples.

755
00:39:51,820 --> 00:39:55,220
For example, go on, go on, go,
it's like goes, right?

756
00:39:55,240 --> 00:39:56,540
On is just some...

757
00:39:56,780 --> 00:39:58,820
So go on means continue, right?

758
00:39:59,600 --> 00:40:00,980
Meaning is very different.

759
00:40:01,040 --> 00:40:05,440
If you just look at words separately,
you don't get this meaning

760
00:40:05,440 --> 00:40:05,740
at all.

761
00:40:05,740 --> 00:40:08,680
And there are many such phrases,
right?

762
00:40:10,440 --> 00:40:11,440
In many languages.

763
00:40:12,340 --> 00:40:17,360
And semantic search will capture
this, the meaning, and put it

764
00:40:17,360 --> 00:40:18,160
to vector, right?

765
00:40:18,160 --> 00:40:23,100
And then we use approximate nearest
neighbor search, ANN search.

766
00:40:24,440 --> 00:40:28,440
But it lacks many capabilities,
like for example, exact phrase

767
00:40:28,440 --> 00:40:34,440
search or inverted word search,
or this categorization I mentioned.

768
00:40:35,280 --> 00:40:36,140
What do you think?

769
00:40:36,140 --> 00:40:36,920
It's interesting.

770
00:40:37,020 --> 00:40:39,740
Should we use only semantic search
these days?

771
00:40:42,040 --> 00:40:44,760
Michael: I don't think so, but
only because I'm thinking of some

772
00:40:44,760 --> 00:40:49,040
quite boring use cases where, for
example, you're set, like,

773
00:40:49,240 --> 00:40:52,160
I quite often think about software
as a service applications.

774
00:40:52,900 --> 00:40:56,420
And let's say you might want, you
might be looking up a customer

775
00:40:56,580 --> 00:41:00,180
and you might want to look up them
by email address or by name

776
00:41:00,180 --> 00:41:03,000
or by like, there's a few different
things you might want to

777
00:41:03,000 --> 00:41:04,060
look them up by.

778
00:41:04,400 --> 00:41:07,820
And you might like want to provide
your users with a single search

779
00:41:07,820 --> 00:41:09,700
bar to look those up.

780
00:41:10,240 --> 00:41:14,060
Nikolay: Or you can have tags,
for example, if based on what

781
00:41:14,060 --> 00:41:18,300
I just described, overall, like
these vectors or arrays of text,

782
00:41:18,380 --> 00:41:25,520
text arrays, you can use GiN for
text search, putting all text

783
00:41:25,520 --> 00:41:28,400
for document into single value,
single column, right?

784
00:41:28,500 --> 00:41:31,080
All the text, denormalizing them.

785
00:41:31,080 --> 00:41:34,700
I first did it, by the way, using
GiST in 2008.

786
00:41:34,940 --> 00:41:38,300
It was the topic of my first talk
in the U.S.

787
00:41:38,600 --> 00:41:40,580
I presented in 2008.

788
00:41:40,740 --> 00:41:41,540
Can you imagine?

789
00:41:42,180 --> 00:41:47,380
And I was, we were building this,
we put, it was social media,

790
00:41:47,780 --> 00:41:53,560
and all tags were stored in single
column instead of EAV approach

791
00:41:53,560 --> 00:41:59,200
when you have separately words
like tags, terms, can be phrases.

792
00:42:00,200 --> 00:42:05,780
1 table, documents, another table
and between them like relationship

793
00:42:06,020 --> 00:42:06,880
table, right?

794
00:42:08,300 --> 00:42:13,140
And then you need to have 3 table
joins, like 2 joins all the

795
00:42:13,140 --> 00:42:13,640
time.

796
00:42:14,160 --> 00:42:17,800
In terms of storage and speed of
search, it was terrible, it

797
00:42:17,800 --> 00:42:21,100
remains terrible, E-A-V, Entity
Attribute Value approach.

798
00:42:21,600 --> 00:42:26,040
But here you can put all tags and
full text search can provide

799
00:42:26,040 --> 00:42:29,480
you exact search by tags.

800
00:42:30,600 --> 00:42:35,248
Versus if you put everything to
vector, probably how we would

801
00:42:35,248 --> 00:42:35,344
do it?

802
00:42:35,344 --> 00:42:41,200
We would do it, we probably would
just take author, title, body,

803
00:42:41,200 --> 00:42:42,780
and then okay, we have tags.

804
00:42:42,980 --> 00:42:47,800
And we append probably tags colon,
this comma, this comma, this,

805
00:42:47,800 --> 00:42:48,300
right?

806
00:42:48,420 --> 00:42:53,940
And rely on our vector search that
it will be used somehow, but

807
00:42:54,240 --> 00:42:56,100
we lose control, right?

808
00:42:56,120 --> 00:43:00,200
If it's full text search, or we
have multiple indexes, we can

809
00:43:00,200 --> 00:43:03,640
reliably find proper documents,
which definitely contain some

810
00:43:03,640 --> 00:43:04,140
text.

811
00:43:04,940 --> 00:43:06,440
Michael: This might be my answer.

812
00:43:06,500 --> 00:43:11,680
I think if I care a lot about reducing
false negatives, like

813
00:43:11,680 --> 00:43:15,600
it would be really bad if you search
the exact name of a customer

814
00:43:15,780 --> 00:43:20,080
in my application and we didn't
show it back to you.

815
00:43:20,080 --> 00:43:21,340
If we couldn't find it.

816
00:43:21,340 --> 00:43:26,140
So sometimes the problem with the
index types for vector search

817
00:43:26,440 --> 00:43:29,080
is they can have false negatives.

818
00:43:29,440 --> 00:43:33,240
Results that should be the correct
answer turn out to then not

819
00:43:33,240 --> 00:43:34,620
be returned in the answer.

820
00:43:34,960 --> 00:43:40,660
Nikolay: So and also we remember
that vector search since dimensionality

821
00:43:42,100 --> 00:43:44,960
is super high, it's approximate
nearest neighbors.

822
00:43:45,040 --> 00:43:46,560
Michael: So that's what I'm trying
to explain.

823
00:43:46,560 --> 00:43:51,140
Nikolay: But full text search is
okay, nearest neighbors or something

824
00:43:51,140 --> 00:43:52,680
like filtering is exact.

825
00:43:53,520 --> 00:43:54,020
Yes.

826
00:43:54,400 --> 00:43:54,900
Precise.

827
00:43:55,320 --> 00:43:57,280
So let me rephrase you.

828
00:43:57,440 --> 00:44:01,840
If we want to present user just
single input with a button, fine,

829
00:44:01,840 --> 00:44:02,340
right?

830
00:44:02,460 --> 00:44:05,720
Probably similarity search is enough,
right?

831
00:44:06,000 --> 00:44:11,420
Or if we want to have some of this
language, like exclude this

832
00:44:11,420 --> 00:44:16,620
word or exact phrase search in
double quotes, or we have advanced

833
00:44:18,180 --> 00:44:22,080
search form, like choose the offer,
date range or something.

834
00:44:22,840 --> 00:44:25,740
Similarity search is not enough
and probably we will need not

835
00:44:25,740 --> 00:44:30,560
only full text search but also
faceted search with filtering,

836
00:44:30,600 --> 00:44:31,720
additional things.

837
00:44:32,080 --> 00:44:36,860
Some things we can put to full text
search index, as I described,

838
00:44:36,860 --> 00:44:38,280
but not all of them.

839
00:44:38,680 --> 00:44:41,680
And it's interesting that now we
have similarity search, now

840
00:44:41,680 --> 00:44:47,020
we have full text search, typo
correction basically, but it requires

841
00:44:47,020 --> 00:44:48,160
effort, as I said.

842
00:44:48,760 --> 00:44:51,800
Other indexes, B-tree, doesn't go away
at all, right?

843
00:44:52,200 --> 00:44:56,580
And then we can probably build
some system which can be very

844
00:44:56,580 --> 00:44:59,640
powerful in terms of what you can
do with it.

845
00:45:00,060 --> 00:45:04,440
And if we consider a particular
example, for example, I know

846
00:45:04,440 --> 00:45:08,580
you used PostgreSQL.org and Google
search, right?

847
00:45:09,020 --> 00:45:10,360
2 search engines, right?

848
00:45:12,360 --> 00:45:18,920
And PostgreSQL.org search is remarkably
weak.

849
00:45:19,540 --> 00:45:19,820
Is

850
00:45:19,820 --> 00:45:20,380
Michael: it true?

851
00:45:20,380 --> 00:45:22,100
Yeah, it's not great, is it?

852
00:45:22,500 --> 00:45:26,180
Nikolay: It seems it doesn't use
full text search, or maybe it

853
00:45:26,180 --> 00:45:31,580
uses it in some strange form, because
I don't see all power full

854
00:45:31,580 --> 00:45:32,620
text search provides.

855
00:45:33,280 --> 00:45:37,580
Maybe we could look into details
because I guess source code

856
00:45:37,580 --> 00:45:38,400
should be available.

857
00:45:38,900 --> 00:45:44,180
But in general it can be improved
and things like just using

858
00:45:44,180 --> 00:45:47,840
PostgreSQL's own full-text search
will improve it and we could

859
00:45:47,840 --> 00:45:50,460
define more things, right?

860
00:45:50,660 --> 00:45:54,980
But if pgVector would be installed,
it would be even better,

861
00:45:55,640 --> 00:45:55,960
right?

862
00:45:55,960 --> 00:46:00,140
Because we could combine semantic
search and full-text search

863
00:46:00,140 --> 00:46:00,480
phrase.

864
00:46:00,480 --> 00:46:05,580
I think, for me, full-text search
is good in terms of these capabilities

865
00:46:05,860 --> 00:46:09,980
like this language, like phrase
search and negative exclusion,

866
00:46:11,120 --> 00:46:12,180
and also categories.

867
00:46:13,280 --> 00:46:13,780
Yes.

868
00:46:14,280 --> 00:46:17,220
So as someone who built a lot of
systems using full text search

869
00:46:17,220 --> 00:46:23,860
and recently built 1 system using
pgVector and semantics similarity.

870
00:46:25,680 --> 00:46:30,760
I think it's probably a proper
time to start adding full text

871
00:46:30,760 --> 00:46:33,900
search capabilities also, and it
will be tricky and interesting

872
00:46:33,900 --> 00:46:36,180
how to properly combine them.

873
00:46:36,180 --> 00:46:39,440
I saw simple examples, trivial
examples, like how to combine

874
00:46:39,440 --> 00:46:39,900
them.

875
00:46:39,900 --> 00:46:45,200
Let's find 50 documents using semantic
search, 50 documents using

876
00:46:45,200 --> 00:46:47,220
full-text search, that's it.

877
00:46:47,640 --> 00:46:52,640
Or let's find 1,000 using full
text search, then re-rank them

878
00:46:52,640 --> 00:46:55,220
using similarity and leave only
100.

879
00:46:55,400 --> 00:47:00,140
And then define some additional
ranking and leave only 10.

880
00:47:00,480 --> 00:47:04,060
Quite weak examples because I have
no idea how pagination will

881
00:47:04,060 --> 00:47:04,540
work, right?

882
00:47:04,540 --> 00:47:06,140
And also, what about speed?

883
00:47:06,680 --> 00:47:14,040
So for me, it remains an open question
how to look back at old

884
00:47:14,120 --> 00:47:17,120
functionality provided by full-text
search and bring them to

885
00:47:17,120 --> 00:47:18,160
similarity search.

886
00:47:18,220 --> 00:47:20,100
So it's a super interesting topic.

887
00:47:20,580 --> 00:47:22,320
Michael: It depends on the use
case, right?

888
00:47:22,860 --> 00:47:27,280
So I'm thinking about your bot
for Postgres AI, you don't need

889
00:47:27,280 --> 00:47:28,180
to paginate, right?

890
00:47:28,180 --> 00:47:32,980
Like if you're returning the results
in a chat-based interface.

891
00:47:32,980 --> 00:47:33,800
Nikolay: Yeah, there, yes.

892
00:47:34,460 --> 00:47:37,200
Michael: So it's an interesting,
like we maybe don't, depending

893
00:47:37,200 --> 00:47:39,620
on the use case, maybe don't always
have the same constraints

894
00:47:39,620 --> 00:47:42,460
we're used to having, like, via
web search type interfaces.

895
00:47:43,200 --> 00:47:46,400
Nikolay: Well, I think yes and
no.

896
00:47:46,400 --> 00:47:52,040
We don't need it now, but I already
see building the bot, I follow

897
00:47:52,300 --> 00:47:54,020
my own behavior using Google.

898
00:47:54,400 --> 00:47:55,460
When using Google.

899
00:47:55,920 --> 00:47:56,320
Michael: And there

900
00:47:56,320 --> 00:47:59,440
Nikolay: I definitely sometimes
go to page number 2, page number

901
00:47:59,440 --> 00:47:59,940
3.

902
00:48:00,740 --> 00:48:02,020
Michael: But not page 20.

903
00:48:02,020 --> 00:48:05,860
Like pagination maybe is not that
big a deal if you're looking

904
00:48:05,860 --> 00:48:07,240
at the first 3 pages.

905
00:48:08,800 --> 00:48:09,920
Nikolay: But speed will suffer.

906
00:48:09,920 --> 00:48:14,240
But anyway, I think I should combine,
start combining with full

907
00:48:14,240 --> 00:48:18,280
text search because sometimes I
want an exact search, I know the exact

908
00:48:18,280 --> 00:48:19,940
phrase or function name.

909
00:48:20,660 --> 00:48:22,260
Sometimes I want to exclude something.

910
00:48:22,480 --> 00:48:25,180
I mean, both already want to exclude
something.

911
00:48:26,260 --> 00:48:26,980
It happens.

912
00:48:26,980 --> 00:48:32,760
And also I think about two-phase
search when we search for more

913
00:48:32,760 --> 00:48:33,260
money.

914
00:48:33,280 --> 00:48:35,780
Maybe indeed pagination is not
needed.

915
00:48:36,060 --> 00:48:40,060
I think about the idea to search
a lot of items, like a thousand,

916
00:48:40,580 --> 00:48:44,080
2000 items, but then analyze snippets
like in Google.

917
00:48:45,060 --> 00:48:49,620
Using, again, automated analysis
using LLM and leave only a few.

918
00:48:49,960 --> 00:48:54,840
For them, we need to expand because
I always open every relevantly

919
00:48:55,040 --> 00:48:56,760
looking snippet.

920
00:48:56,760 --> 00:49:00,220
I just click it and open a new
tab and consume the whole document.

921
00:49:01,280 --> 00:49:06,320
But we cannot consume whole documents
for 10 or 20 items.

922
00:49:06,540 --> 00:49:07,820
It's interesting there.

923
00:49:07,820 --> 00:49:10,280
But full text search, I think,
is about to return.

924
00:49:11,020 --> 00:49:14,780
Because again, sometimes I want
to search by title reliably.

925
00:49:15,040 --> 00:49:17,680
I know it was mentioned in the
title or something.

926
00:49:17,680 --> 00:49:18,420
Or authors.

927
00:49:18,900 --> 00:49:19,780
Also author.

928
00:49:20,420 --> 00:49:20,920
Yeah.

929
00:49:22,740 --> 00:49:23,500
Michael: Makes sense.

930
00:49:23,940 --> 00:49:24,860
Exciting times.

931
00:49:25,240 --> 00:49:29,320
Nikolay: Yeah, maybe it was slightly
messed in terms of content

932
00:49:30,060 --> 00:49:31,260
structure this time.

933
00:49:32,040 --> 00:49:36,020
But I still have questions and
I don't like examples like just

934
00:49:36,020 --> 00:49:38,040
50 semantics, 50 full-text search.

935
00:49:38,560 --> 00:49:40,140
Michael: Do you want to hear from
people?

936
00:49:40,160 --> 00:49:42,180
Should people let us know how they're
currently?

937
00:49:42,360 --> 00:49:44,680
Nikolay: Yeah, maybe there are
better ideas already.

938
00:49:45,140 --> 00:49:49,620
I think it's a very evolving area,
right?

939
00:49:49,820 --> 00:49:54,840
And maybe there will be more systems
where capabilities will

940
00:49:54,840 --> 00:49:58,980
be combined, because we know in,
for example, in Google, it combines

941
00:49:58,980 --> 00:50:04,020
a lot, including similarity and
like full text things.

942
00:50:05,380 --> 00:50:08,200
Michael: Yeah, so if you're doing
this in Postgres already combining

943
00:50:08,200 --> 00:50:10,580
the 2 techniques, let us know.

944
00:50:11,040 --> 00:50:13,520
Nikolay: Yeah, how exactly, what
are the details?

945
00:50:13,520 --> 00:50:14,660
It's super interesting.

946
00:50:15,480 --> 00:50:15,980
Good.

947
00:50:16,860 --> 00:50:17,540
Michael: Nice one.

948
00:50:17,540 --> 00:50:18,360
Cheers, Nikolay.