Nikolay: Hello, hello.

This is PostgresFM, episode number
98, right?

98.

Michael: Yes.

Nikolay: And this is Nikolay Samokhvalov,

founder of Postgres.AI.

And my co-host is Michael Christofides,
founder of pgMustard.

Hi, Michael.

Michael: Wow.

That's the first time you've attempted
my last name and you did

great.

Nikolay: Okay.

Thank you so much.

I practiced all these years.

So what's the topic today?

Michael: Yeah, So this was your
suggestion.

I think it's a great one.

We've been meaning to come back
to it for a while now.

It's full-text search.

Why did you want to talk about
it?

Nikolay: I think it was on our
list for a very long time, maybe

from the very beginning, because
it's a huge topic.

And when we built that list, I
think pgVector already existed,

but only a few people paid attention
to it, unlike today.

So since we didn't manage to discuss
full-text search before

this AI boom started last year.

I think we should maybe compare
it a little bit.

Since I used full-text search a
lot in the past, I still remember

many features it offers in Postgres.

I think it's interesting to not
only discuss, like usual discussion,

embedded full-text search in Postgres
versus Elastic.

But also it's interesting to discuss
embedded full-text search

of Postgres versus PgVector
extension and similarity

search, or maybe not versus, maybe
together.

And this is interesting.

But I think time is changing and
evolution is very interesting.

And today people probably pay less
attention to full-text search,

but they at least should know what
capabilities it can offer.

And let's maybe start from use
cases and discuss functions, features,

FTS, full-text search provides
in Postgres.

What do you think?

Or maybe we can talk about some
historical aspects.

I also can provide some overview
of what was happening in Postgres

and not only in Postgres.

What do you think?

Features, use cases, history, what's
better?

Michael: Actually, I don't know
the history that well.

That would be awesome to hear a
tiny bit about that.

And then I'd love to jump onto
use cases where you see it used

most commonly, that kind

Nikolay: of thing.

Yeah, actually we can combine.

I now see how we can do it.

So if, for example, we roll back
to first web era, Web 1.0, so

to speak, like 25 years ago, and
when Google was created.

Because Google is kind of, it's
a search engine, originally,

right?

And it solves the problem of finding
proper texts, most relevant

texts, right?

And at that time, before Google,
already other search engines

were created, obviously.

Full text search capabilities were
originally called T-Search

2.

Actually, I remember this contrib
module I was seeing at 2005,

maybe, when I first started working
with Postgres, created by

Oleg Bartunov, Teodor Sigaev, and
then Alexander Korotkov joined

them.

They created it because they were
building a search engine originally.

They helped building quite a big
search engine.

Then they incorporated many of
their works into Postgres, and

this became T-Search2 contrib module,
which later was merged

to the core and you don't need
to install extension, so it's

already in core, right?

But interesting thing that it was
before social networks and

so on, but If we look at how it's
built, it has a lot of things.

For example, you can search, you
can, for example, exclude some

words.

By default, it excludes some words.

It's called dictionary, which is
like in Postgres full text search

terminology, dictionary is a program.

It can process input somehow transforming
it.

So stop words, it's just exclusion
of various articles for example

and so on which we probably don't
have any meaning, so we can

just exclude them, that's it.

Then we have Snowball, which is
a stemmer, so it removes the

endings.

It's super fast and super simple.

So we can, for example, runs and
run, it's like kind of the same

word, so s should be removed, right?

And then more full-fledged thing,
we can ask to join, to use

some real dictionary, usually ISPELL,
which is also used for thesaurus,

like It can give a lot of things,
but it's usually slower, requires

some memory.

And all these things are configurable
in full-text search, and

they were configurable in TS Vector
2 originally, and it's great.

But if you think about the task,
the use case, what we want to

try to solve, we want to present
user some information which

is most relevant, right?

What is most relevant?

The user has some query.

I want a recipe with, I don't know,
with some eggplant, for example.

He puts eggplant.

If we have, for example, food recipe,
website, or system, knowledge

base.

And then we just stop words, normalizing
words, we can just filter,

right?

But filtering is just 1 thing.

We also need to somehow order,
maybe, right?

And present only the most relevant.

What is the most relevant?

Maybe these words are most frequently
used in these documents,

right?

Like repeated many times, for example.

And this is quite a basic idea.

If the word is used many times,
it's considered most relevant.

Now, confession, I never used this.

Why?

Because I'm from a different era.

I'm from web 2.0, where most relevant
meant something else because

it was social networks.

It was like also time matters a
lot.

Like if it's fresh document, like
fresh blog post or something,

it matters a lot.

But Postgres full text search lacks
this, like it's hard to build

together.

There is a special index RAM, I mentioned
it many times already.

Maybe I need to revisit it finally
and think, maybe it still

can be used, because if you're
on self-managed Postgres, this

type of index can be installed
as an additional extension.

But the original full-text search can
order by only this tsRank,

right?

It's called function tsRank.

And there is another function which
also considers positions

and distance between words.

I think like density or something.

Like not density, but the closer
words are the better ranking

score is, right?

But for me, it was always nonsense
to use only these word positions

and so on.

I couldn't imagine how I can use
it because I always needed to

take into account time.

And also sometimes things like
likes, reactions, comments, if

this post is very heavily discussed,
it's very important, right?

But back to history, we know there
is BM25 and also there is

like this TF-IDF.

The idea, TF, it's term frequency,
inverted document frequency,

right?

If some document has this term
like word mentioned many times,

it's good for this document, right?

And if, in general, this term is
mentioned in our whole database,

it's a whole dataset, it's mentioned
not very frequently, it's

also good for our document, because
it means that it's exceptional

in terms of frequency, right?

So that's why it works, document
frequency.

So it means that for such thing
we need to analyze the whole

dataset.

Postgres full text search doesn't
do it.

It's quite hard to maintain such
type of thing, but I think if

there are some attempts to have
it in Postgres, maybe some extensions

exist.

This is what Elastic uses, right?

But there is another approach.

Back 25 years ago, why did Google
win?

Because it had PageRank.

If we think deeply about this,
it's already from Web 2.0 idea.

Because PageRank takes into account
social network of documents,

I would say.

It takes into account links, right?

So interactions.

We are not alone.

And this idea is super powerful.

I'm curious, are there any extensions
which implement this in

Postgres and how it could be implemented?

This is super interesting.

But this is a big power.

If we have links, we value this
document more.

I think there are good algorithms
how to solve it with billions

of documents of course.

There are papers written, anyone
who studied search engine theory

of course.

There is a course from Stanford
mining massive data sets, very

good material where I studied this
PageRank and other algorithms

closer to machine learning on big
data sets.

So idea is like links, right?

You multiply by some matrices and
you have some good index and

you can use it.

Again, Postgres doesn't provide
this.

Elastic doesn't provide PageRank,
I guess.

But it's good because we start
taking into account not only word

positions.

Full-text search in Postgres, only
word position for single document

and that's it.

But since full-text search in Postgres
is inside relational database

system, we can have other columns,
right?

And we can have indexes on them.

So for example, likes or something
like timestamp when document

was created.

We can construct SQL, which takes
into account all these things.

It will be just slow, right?

Maybe because the idea that the
fastest query is always like

index scan or index only scan is
the best, right?

But when we have full text search,
we have other columns and

we have B-trees probably on them.

Combination is a problem, right?

This is the problem.

But there are things how you can
solve it, but let's maybe discuss

a little bit later, I mean, B-Tree GiST
and other extensions.

But originally, the combination
of different factors in the ranking

task, this is the problem, right?

Later, Google and others, they
started taking into account social

network activities.

If some document is mentioned on
Twitter, for example, or Facebook.

It happened like 15, 10 years ago.

This document, everyone who dealt
with search engine optimization

knows that if you have a lot of
likes in social networks, your

document goes up in positions.

But if we deal with our system,
we can take into account interactions

like that because it's just some
data we store and that's it.

Today it's not that bad usually
if you don't have tens of billions

of records or just millions or
tens or hundreds of millions.

It's not a huge volume of data.

So you can rely on multiple indexes
and let Postgres decide what

to choose based on statistics.

This is very similar to what we
discussed, remember, with...

Yeah?

Yeah, very similar.

Because it triggered my problems
with full-text search.

I had trying to incorporate it
to social media projects.

Because you have 2 indexes, which
to choose?

Because you cannot choose them
together.

Well, you can, you can have bitmap
in the scan, but it's already

slower.

But most likely Postgres will choose
just 1 index in 1, like

for example, over time, or full
text search, but based on statistics.

And then on the fly, it will apply
either sorting in memory or

filtering in memory, right?

Michael: Yeah, exactly.

That's where the inefficiency comes
in.

Nikolay: Yeah, yeah.

So this is like historical overview.

We had like simple stop words, stemming,
dictionary.

It's not simple, by the way.

It's a lot of functionality.

But ranking based on just our opinion
about how this document

is relevant to your query, not
considering relations or other

things.

Second thing is, I think, page
rank and Postgres doesn't have

it.

And the third thing is taking into
account various factors like

time and likes and so on.

But also there is similarity search
now and we know search engines

use it for long, right?

So...

Michael: Well, actually, I want
to go back to...

I think your example is remarkably
useful.

So the example you gave of searching
a site of recipes for eggplant,

or let's add that maybe we're not
super sophisticated, we type

an eggplant.

So we can, for example, say that
an would count as a stop word.

We don't want recipes that say
an a lot to rank highly.

So that's getting rid of the stop
words.

We might want, for example, to
do something slightly more.

We might want to rank recipes that
list eggplant in the title.

We might want those to score higher
than ones where it's listed

in the document more times.

So that's like an interesting additional
challenge.

Secondly, it might be, let's say,
a user-generated recipe site,

and we might want to factor in
how many likes a recipe has got

or how recent it is or something
like that.

So there's like all these, even
in the simplest example you can

think of these days, it can get...

Oh, I've got 1 more.

You might also, if it's user-generated
content, you could have

British people or like people using
aubergine instead of eggplant.

And you also want those to rank
highly.

So yes, exactly.

Nikolay: So synonyms already, it's
already a bridge to discussing

full text search versus similarity.

Let's keep it as the last item,
right?

But let's, great attempt to zoom.

This is good.

Let's do, or diving into deeper.

So let's slightly mention how full-text
search works in Postgres.

So after this pre-processing, we
just discussed like removal

of stop words and you can control
it.

Like good thing about full-text
search in Postgres, a lot of

customization options.

Like A lot.

You, for example, can control stop
list and stop considering

an as a stop word.

Or you can, for example, remove
accents.

There's a special extension on
accent, right?

Additional pre-processing.

Michael: So like in French, for
example, with lots of...

Nikolay: Or there are support of
multiple languages also, it's

also good.

So you can start considering additional
stop words, for example,

remove it if they mention too often,
like, almost all documents

have them, let's remove it because
it doesn't make sense to use

it, right?

Then like stemming or dictionary
processing, I spell dictionary

processing.

So in the end we have a set of
normalized words, right?

And then we build an array.

It's called TS vector, again also
vector by the way.

So it's a set of words with positions,
already normalized, in

a normalized form.

But basically it's just an array
of texts, right?

Michael: Yeah, like words or lexemes
or whatever.

Yeah.

Nikolay: For example, I forgot
also to mention hyphened words.

I think you also can control it.

As I remember, it was many years
ago, I think you can choose

either to consider them as a single
1 word or to split them to

multiple words, like 2 words basically,
first and second, or

together.

Like You can put a pair of separate
words and whole word as hyphen,

right?

A total of 3 already, right?

Michael: So that if people search
1 or the other, they still

get that result back.

Nikolay: Right.

For example, shift left testing.

Shift left, if it's a hyphen, you
can put both shift left and

shift left as 3 separate entries
in your

Michael: test vector.

Nikolay: You can control how Postgres
will do it, building test

vectors.

Also, you can concatenate test
vectors so data can come from

different sources.

For example, we have title, we
have body, subject, or body, like

for example if it's email, or if
it's blog posts, title and content,

text, how you name it, it doesn't
matter.

And you can concatenate 2 TS vectors,
or you can concatenate

it before that with additional
space and then build TS vector.

So TS vector is just an array of
texts, and you can explicitly

store it, consuming space.

Or you can rely on function, because
then we need to create index

on it.

Originally it was GIST.

GIST, I think it was the work of
Heikki Linnakangas in Berkeley very

long ago, in the early 90s maybe
or when.

It was not finished, but the guys
who I mentioned, Bartunov and

Sigaev, they just saw this directory
in PostgreSQL source code, as

they say, and understood this is
exactly what they need.

There's also an additional thing.

GiST is a generalized search tree.

It's similar to B-tree, but it's
abstract.

You can use it for many data types
and data operators.

For B-tree, it will be numbers
and operators less than or more

than and equals.

And it's just 1 axis, right?

R3 it's 2 axes and then you can
have it for arrays as well.

In this case we talk about operators
intersect, includes, contains,

is contained by, and overlaps.

Overlaps is at at, usually.

You can actually redefine it and
use your own symbols.

It's actually first you define
function, then operator, since

Postgres is very extendable.

But historically it's at at, or
less than at, or at greater

than.

So these operators, you can define
how to deal with them in terms

of this B-tree-like index structure.

So it's balanced with many, many
children on each node.

And balancing is automated, everything
is automated.

So when these folks found this Heikki Linnakangas's
work, it was not

finished, and the WAL was not
supported.

They worked on that supported WAL.

Then additional thing, we won't
go deep because of lack of time

and I also forgot everything, But
there's also a thing that's

called signature tree.

So to put our TS vectors to this
structure, there's additional

concept of signature tree, you
need to build signature.

It's also defined.

So these signatures are placed
into the leaves of this tree.

And then search is very bit tree-like,
but with additional exclusion,

which makes today's GiST option
is not popular at all.

Recheck is needed, because it's
not certain, this search.

Michael: I've heard it's called
lossy index types.

Nikolay: Right, right.

So if you check, this is what you
do in pgMustard, right?

Yeah, yeah.

EXPLAIN, ANALYZE, BUFFERS, right?

You check and see in the plan,
recheck happened.

If GiST was used, recheck happened.

So let me close some gap.

Query also processed to build a
vector, right?

But it's called TS query.

It's called TS query, different
data type, but it's also similar.

Also a bunch of words, also with
pre-processing.

You can have different pre-processing,
you decide how to pre-process,

right?

But usually it's same as for TS
vector, for TS query, same pre-processing

also remove stop words, remove
normalized words, and so on.

So, and then our question is, let's
find everything which includes,

like, we need to find documents
which include everything we asked

for in our query, right?

Michael: And rank them, but yeah.

Nikolay: Yeah, and rank also additionally.

So there is approach to put
it to trees, to such values, to

leaves.

And then it searches similar to
B-tree, but instead of greater

than or less than, instead of that,
it checks if this vector

is contained in that vector.

I mean, this array is contained
in that array, and that's why

you need to go left or right, something
like this.

So it's called Russian doll tree
because it's like Russian doll,

right?

Somehow.

RD3.

So by the way, it's also in Berkeley
papers, this term RD3, somehow.

So I think it was invented before
Bartunov and Kratkov.

Probably.

Maybe they influenced, I don't
know.

But then, obviously it's slow in
terms of search for large volumes

of data because of this recheck.

This is not how Google and others
worked in terms of like...

Not even Google.

Google, we know, page rank is a
bigger thing.

But eventually, Bartunov, Sigaev,
and then Kratkov, they created

GIN.

GIN is a generalized inverted tree
where we have a list of terms,

our words, and for each list of...

Michael: Documents.

Nikolay: Right, all tuple IDs,
tuple CTIDs, I think, where this

is stored, but not lists, there
are bit trees there, actually,

for faster search.

So it means that Gist is good only
at 1 thing, update speed.

Michael: Gist fast is gin.

Nikolay: Right, right.

But gin, it was improved, there
is also fast update option.

But anyway, default option for
us is gin, right?

And comparing tsQuery and tsVector,
gin is good, search is fast,

but order by tsRank, which is not
probably good.

So back to the comparison, back
to these use cases you started

mentioning.

Let's think about them.

First, you said, or at least second,
let's distinguish subject

and body, right, for example.

Michael: Yeah, or should we rank,
should we, should the, the

presence of an ingredient in

Nikolay: it or

Michael: let's yeah.

Words because we know that people
aren't going to be searching

in our like search bar for random
things.

Like maybe there's like a few different
things they might search

by.

The most common is probably ingredient.

Should recipes that list that in
their title rank higher, or

be weighted higher, even if they
only mention it like a few couple

of times later on, I feel like
personally I'd be expecting those

to rank higher than ones where
it was a small ingredient, or

I only needed a small amount of
it maybe.

Maybe the amount of the ingredient
matters a lot.

Maybe that's easier.

Nikolay: Right, right.

So it's definitely a good thing
to want, right?

If a word is entitled, it means
that maybe the whole article

is about this, right?

If eggplant is entitled, it pays
attention more to it.

Makes sense.

So for this, there are 2 bits which
you can use.

2 bits means 4 options, right?

So there is a function setWeight.

And when you combine information
from multiple sources, from

title column and content column,
for example, body column, you

can say set weight A to first 1,
set weight B to second 1.

And they are only ABCD, uppercase,
because again, 2 bytes only

are used for this.

And then later in your query, you
can also say, I pay attention

to this or to that.

Actually, you can use it for filtering
as well.

And this is what I did.

But originally it was created for
ranking.

I don't remember details, but setWeight
function you need to search

in documentation, it should be
there and you will find out how

to...

It's a very strange concept.

Why only 4?

Because of 2 bits only.

They had only 2 bits to spend for
it.

Maybe there should be more.

But I use it for sometimes like
we search only in subject, right?

And I say, okay, only a category.

It's called category maybe.

It's embedded inside TS vector.

So you can skip using it.

It's there, but you can just ignore
it in search.

But in different time, you can
say, I want to search only category

A, it means only title search.

You don't need to build 2 TS vectors
separately.

However, you could, right?

Michael: Got it.

So like, if, for example, going
back to our example, I search

what's clearly like an author name,
and we can maybe on the application

side, we're doing a little like
a quick, Maybe we're doing something

first to try and categorize what
people are searching for.

If it's an author name, I could
then send that through to Postgres

as like, let's only look in this
category.

Nikolay: Right, right.

But again, it originally was created
for ranking.

I just didn't use it.

So I cannot explain how the documentation
of course, explains

how.

Right.

So good thing to mention also,
now we have generated always thing,

right?

Generated columns.

They are stored, but Postgres maintains
them automatically.

So, I think generation of TSVector
is probably a good thing to

use together with that functionality.

So you have author, title, body
of blog post, and then TSVector

can be generated based on some
expression, which puts different

categories to different sources
of data, again up to four, and generates

and puts TSVector there, and then
you have an index on it, right?

Or you can define a trigger.

All my life I defined triggers
for this.

And also there is another option
not to store it at all and just

use index on expression, GIN, and
then your big expression, maybe

with this set weight we just discussed.

But in this case, it's good in
terms of storage.

Less thing to keep in memory.

But it might be bad in terms of...

I don't know.

Sometimes you need to deal with
TSVector directly.

If you have only expressional index,
then you need to construct

this expression once again to deal
with it.

I don't like this approach somehow,
because it limits in terms

of what you can do with such records.

So, if you don't store it, you
need to build it on the fly to

deal with it, to additionally somehow
analyze it or so.

So I always prefer to store them,
although they can consume a

lot of data and the TOAST and so
on.

Sometimes I just put them to separate
table understanding that

then I will need to join.

For me it's easier to create a
trigger and use a regular column,

right?

But if you think to store it or
not to store it, it's good to

think what you will do with it.

And if you want to deal with TSVector
in different ways, then

keeping it stored only in the index
itself.

It's maybe not enough and maybe
it's good to put it to the table.

But to put it to the same table
as a column and rely on TOASTing

and so on, or maybe to allocate
a different table and like one-to-one

relationship?

It's a good question.

Again, depending on workloads.

So okay, This we covered.

Can you remind me of other use
cases you mentioned?

Michael: Well, I think it's all
I consider this kind of one use

case, but I guess, I guess there's
like all the complexities

that you may or may not want to
support.

1 that I didn't mention, but probably
we care about is what if

somebody spells eggplant with 1
g?

Nikolay: Yeah, typos.

Michael: Yes.

Nikolay: For this, there is pg_trgm,
3 grams.

Extension, which actually also
uses RD3.

Gin, actually.

Gin these days.

RD3, forget about it.

Forget about GiST, very rarely
used.

So Gin, right?

So we have some text, right?

Or some...

Basically, 3-grams should work
for words, right?

We have word, and we suspect maybe
there is a typo there.

So it depends 1 space on 1 side
and 2 spaces to different side

and then just split for 3 words,
3 letters, 3 letters, 3 letters,

right?

And then we have array again, right?

Vector.

Vector, right.

And then the question is which
is the most, the closest.

Closest means like most overlapping.

Most of members of array are the
same.

Might be not all of them.

And if it's just 1 letter typo,
it means 3 members of this vector

will be different.

Michael: Ah, nice.

Yeah.

Well, it wouldn't include EGG,
it wouldn't include GGP, and it...

Is that...

They're the only 2?

In this case, I think, possibly,
just they're the only 2.

Nikolay: So 3-grams, We're just
shifting, shifting, shifting

also, right?

We don't just split, we're shifting.

So start position, first is,
for example, space, then 2 letters,

then second, the first letter, second
letter, third letter, shift,

shift, shift, and we construct
array out of it.

And then if, for example, you just
missed, forgot 1 letter, overlapping

will be huge.

Distance is very close.

Similarities, distance low, similarities
high.

If you, for example, mixed positions
of 2 letters, just swap

them.

Also huge.

This is an interesting idea behind
3-grams.

And again, we use either RD3, nobody
does it, or Gin.

GIN, we use GIN so we can find
arrays which are closest, overlapping

is higher.

That's how we find words which
actually present in our document

dataset.

And then they say, you probably
thought about this word.

1 thing, in the past I remember
we maintained the list of words

with usage counts probably, using
some statistics provided by

full text search.

I don't remember details, but you
can build a list of words with

stats, and then you can store it
in a table, and then use trigrams

on top of it.

You need triggers to maintain this,
or you need to refresh it

periodically.

For example, if you just imported
a lot of documents which use

new words, this list of words is
already outdated, you need to

rebuild it.

And then this Trigram approach
worked, you have input words,

you check the table and it works.

Now I think we don't need it, right?

It's automatically maintain this
list of words.

Or we just don't care and build
an index and then take whole

input.

Michael: That's how I've seen it
used, yeah.

Nikolay: So there are several ways
to use it, and maintaining

this additional table probably
still makes sense, right?

List of words.

Michael: Do you think it would
then speed up queries, or what

do you think the benefit is?

Speed?

Nikolay: You lose speed here probably,
right?

But accuracy is good.

Michael: Accuracy, yeah.

This is great, but I think the
conversation you mentioned earlier,

like the comparison of full-text
search versus semantic search

in the current climate, and something
I wanted to introduce was

whether the use case suits false
positives, or suits false negatives

better, like I think it's really
difficult to, to come up with

a solution that that does neither.

But often you see kind of slightly
biased towards like is it

better like Google for example
what PageRank was really good

at was making sure it didn't miss
a website that was clearly

very relevant to your search term
and so it was very very good

at avoiding false negatives but
you did often especially in the

earlier days get quite a lot of
false positives.

You'd get articles that didn't
match your intention, at least,

even if it did match the words.

But the recipe example, maybe we
don't care so much about like,

if you miss 1 great recipe, but
you get 17 perfectly matched

ones, that's still a good result.

Maybe you'd rather that kind of
weigh around on the trade off.

I don't know how you see that and
whether that's relevant.

Nikolay: I see this as many different
small use cases.

For example, if first of all, I
remember your different use cases.

This is what I mentioned as well.

Like we want to take into account
likes, comments, timestamp,

everything.

It gives us ability to build very
big query and then we think,

okay, but our full text search,
single GIN index is not enough,

right?

Sometimes we can use bit.ly gist,
for example, right?

Which allows us combining these
arrays of text and regular numbers

or timestamps in a single index.

And this is good.

We can have single index scan.

Also there is B-tree GiST.

Also interesting thing.

Also there is RAM which you need
to install as a separate extension,

but these 2, B-tree GiST and B-tree GIN,
they are included with regular

contrib modules, so with any Postgres
it's available.

But in some cases we want, like
I forgot to mention Postgres

also supports it.

I think functional web search to
test query or something like

this.

It supports some small language
of phrase search and inverted

search, you can exclude, user can
say, I want everything but

this word should not be there.

Just minus, right?

Or put in double quotes for exact
match.

This all great means that user
can control and in regular manner

Google also supports things like
that, right?

So it means that you want, for
example, to see exact phrase mentioning,

well you know it's there and you
just can do it.

But then similarity search, 2 big
use cases.

For example, you mentioned synonyms,
right?

Someone doesn't use the word eggplant,
there's another word meaning

the same thing.

Michael: Aubergine.

It's honestly, that's what we call
them.

Aubergine.

A-U-B-E-R.

I'm not going to try the

Nikolay: rest.

I haven't heard it.

Cool.

So if you want a synonym search,
full-text search supports it.

But you need to maintain it.

You need to maintain the dictionary
of synonyms.

And the normalization process will
automatically, all synonyms

will be defined in our test vector
and test query it will be

1 word we chose.

So we can maintain synonyms.

It's easy.

Not easy.

I mean, it's straightforward.

It's not easy because it requires
effort of maintaining synonyms,

right?

But on the other hand, if we use
pgVector, it probably puts both

words in this highly dimensional
space very close, right?

Because meaning is the same.

Right, interesting.

So maybe semantic search provided
by pgVector is better here,

right?

Okay.

Another thing is, for example,
something is not working versus

something is working.

Not is a stop word, right?

What do we try to search?

Something is not working.

For example, my car is not working.

Maybe not working I should put
in double quotes, so not as definitely

there for exact phrase search.

Or maybe I just need to use pgVector
because it definitely will

distinguish semantically that not
working and working are very fine

in this highly dimensional space,
so similarity is not good,

right?

Distance is not good.

And if we are trying to find my
car is not working, we won't

find documents.

They will be put not high, right,
in terms of ranking by similarity.

But also PgVector...

AL

Michael: SUTTON, I think that depends,
though.

I think that's contextually important
and interesting because

let's say this was a car forum,
how many posts are you going

to find about cars that are working great?

Like realistically in the

Nikolay: data set?

Michael: Well, it's a bad

Nikolay: example. It was a bad
example, but in general, inverted,

since how full text search works
in Postgres, it will remove

not as a stop word.

And it's bad sometimes, right?

Because it's the opposite meaning,
right?

Michael: Yeah, makes sense.

I didn't realize that.

Nikolay: We might think about similar
examples where the removal

of the stop word basically leads to
very bad ranking or filtering,

also.

Michael: Yeah, like one that comes
up quite often is like film

titles or like band titles.

If you put the in front of it,
it could be a completely different

band.

Nikolay: Or some English is very,
has very such example, a lot

of such examples.

For example, go on, go on, go,
it's like goes, right?

On is just some...

So go on means continue, right?

Meaning is very different.

If you just look at words separately,
you don't get this meaning

at all.

And there are many such phrases,
right?

In many languages.

And semantic search will capture
this, the meaning, and put it

to vector, right?

And then we use approximate nearest
neighbor search, ANN search.

But it lacks many capabilities,
like for example, exact phrase

search or inverted word search,
or this categorization I mentioned.

What do you think?

It's interesting.

Should we use only semantic search
these days?

Michael: I don't think so, but
only because I'm thinking of some

quite boring use cases where, for
example, you're set, like,

I quite often think about software
as a service applications.

And let's say you might want, you
might be looking up a customer

and you might want to look up them
by email address or by name

or by like, there's a few different
things you might want to

look them up by.

And you might like want to provide
your users with a single search

bar to look those up.

Nikolay: Or you can have tags,
for example, if based on what

I just described, overall, like
these vectors or arrays of text,

text arrays, you can use GiN for
text search, putting all text

for document into single value,
single column, right?

All the text, denormalizing them.

I first did it, by the way, using
GiST in 2008.

It was the topic of my first talk
in the U.S.

I presented in 2008.

Can you imagine?

And I was, we were building this,
we put, it was social media,

and all tags were stored in single
column instead of EAV approach

when you have separately words
like tags, terms, can be phrases.

1 table, documents, another table
and between them like relationship

table, right?

And then you need to have 3 table
joins, like 2 joins all the

time.

In terms of storage and speed of
search, it was terrible, it

remains terrible, E-A-V, Entity
Attribute Value approach.

But here you can put all tags and
full text search can provide

you exact search by tags.

Versus if you put everything to
vector, probably how we would

do it?

We would do it, we probably would
just take author, title, body,

and then okay, we have tags.

And we append probably tags colon,
this comma, this comma, this,

right?

And rely on our vector search that
it will be used somehow, but

we lose control, right?

If it's full text search, or we
have multiple indexes, we can

reliably find proper documents,
which definitely contain some

text.

Michael: This might be my answer.

I think if I care a lot about reducing
false negatives, like

it would be really bad if you search
the exact name of a customer

in my application and we didn't
show it back to you.

If we couldn't find it.

So sometimes the problem with the
index types for vector search

is they can have false negatives.

Results that should be the correct
answer turn out to then not

be returned in the answer.

Nikolay: So and also we remember
that vector search since dimensionality

is super high, it's approximate
nearest neighbors.

Michael: So that's what I'm trying
to explain.

Nikolay: But full text search is
okay, nearest neighbors or something

like filtering is exact.

Yes.

Precise.

So let me rephrase you.

If we want to present user just
single input with a button, fine,

right?

Probably similarity search is enough,
right?

Or if we want to have some of this
language, like exclude this

word or exact phrase search in
double quotes, or we have advanced

search form, like choose the offer,
date range or something.

Similarity search is not enough
and probably we will need not

only full text search but also
faceted search with filtering,

additional things.

Some things we can put to full text
search index, as I described,

but not all of them.

And it's interesting that now we
have similarity search, now

we have full text search, typo
correction basically, but it requires

effort, as I said.

Other indexes, B-tree, doesn't go away
at all, right?

And then we can probably build
some system which can be very

powerful in terms of what you can
do with it.

And if we consider a particular
example, for example, I know

you used PostgreSQL.org and Google
search, right?

2 search engines, right?

And PostgreSQL.org search is remarkably
weak.

Is

Michael: it true?

Yeah, it's not great, is it?

Nikolay: It seems it doesn't use
full text search, or maybe it

uses it in some strange form, because
I don't see all power full

text search provides.

Maybe we could look into details
because I guess source code

should be available.

But in general it can be improved
and things like just using

PostgreSQL's own full-text search
will improve it and we could

define more things, right?

But if pgVector would be installed,
it would be even better,

right?

Because we could combine semantic
search and full-text search

phrase.

I think, for me, full-text search
is good in terms of these capabilities

like this language, like phrase
search and negative exclusion,

and also categories.

Yes.

So as someone who built a lot of
systems using full text search

and recently built 1 system using
pgVector and semantics similarity.

I think it's probably a proper
time to start adding full text

search capabilities also, and it
will be tricky and interesting

how to properly combine them.

I saw simple examples, trivial
examples, like how to combine

them.

Let's find 50 documents using semantic
search, 50 documents using

full-text search, that's it.

Or let's find 1,000 using full
text search, then re-rank them

using similarity and leave only
100.

And then define some additional
ranking and leave only 10.

Quite weak examples because I have
no idea how pagination will

work, right?

And also, what about speed?

So for me, it remains an open question
how to look back at old

functionality provided by full-text
search and bring them to

similarity search.

So it's a super interesting topic.

Michael: It depends on the use
case, right?

So I'm thinking about your bot
for Postgres AI, you don't need

to paginate, right?

Like if you're returning the results
in a chat-based interface.

Nikolay: Yeah, there, yes.

Michael: So it's an interesting,
like we maybe don't, depending

on the use case, maybe don't always
have the same constraints

we're used to having, like, via
web search type interfaces.

Nikolay: Well, I think yes and
no.

We don't need it now, but I already
see building the bot, I follow

my own behavior using Google.

When using Google.

Michael: And there

Nikolay: I definitely sometimes
go to page number 2, page number

3.

Michael: But not page 20.

Like pagination maybe is not that
big a deal if you're looking

at the first 3 pages.

Nikolay: But speed will suffer.

But anyway, I think I should combine,
start combining with full

text search because sometimes I
want an exact search, I know the exact

phrase or function name.

Sometimes I want to exclude something.

I mean, both already want to exclude
something.

It happens.

And also I think about two-phase
search when we search for more

money.

Maybe indeed pagination is not
needed.

I think about the idea to search
a lot of items, like a thousand,

2000 items, but then analyze snippets
like in Google.

Using, again, automated analysis
using LLM and leave only a few.

For them, we need to expand because
I always open every relevantly

looking snippet.

I just click it and open a new
tab and consume the whole document.

But we cannot consume whole documents
for 10 or 20 items.

It's interesting there.

But full text search, I think,
is about to return.

Because again, sometimes I want
to search by title reliably.

I know it was mentioned in the
title or something.

Or authors.

Also author.

Yeah.

Michael: Makes sense.

Exciting times.

Nikolay: Yeah, maybe it was slightly
messed in terms of content

structure this time.

But I still have questions and
I don't like examples like just

50 semantics, 50 full-text search.

Michael: Do you want to hear from
people?

Should people let us know how they're
currently?

Nikolay: Yeah, maybe there are
better ideas already.

I think it's a very evolving area,
right?

And maybe there will be more systems
where capabilities will

be combined, because we know in,
for example, in Google, it combines

a lot, including similarity and
like full text things.

Michael: Yeah, so if you're doing
this in Postgres already combining

the 2 techniques, let us know.

Nikolay: Yeah, how exactly, what
are the details?

It's super interesting.

Good.

Michael: Nice one.

Cheers, Nikolay.