Nikolay: Hello, hello, this is Postgres.FM.
As usual, I don't remember the episode number.
Michael: 137?
Nikolay: 137.
My name is Nikolay, Postgres.AI, and as usual, my co-host is Michael,
pgMustard.
Hi, Michael, how are you doing today?
Michael: Hello, Nikolay.
I am good, thank you.
How are you doing?
Nikolay: Fantastic, honestly.
Good.
A lot of things are happening, but all good.
I mean, like, things are changing and a lot of things are changing
in a constructive way, which I like.
So, yeah, let's discuss GIN.
It's one of things a lot of folks use and a lot of like it's one
of index types many people use still And I think it's not going
to disappear, despite the rise of vector-related types of indexes
and approximate k-nearest-neighbors indexes.
GIN is a very interesting type of index developed by the same
folks who brought WAL support to GiST.
GiST was the only thing for complex data types before GIN, but
then With the rise of JSON and JSONB, Gene became very popular,
finding a lot of applications.
Not only full-text search, as it was, I think, originally, it
was created for full-text search.
Michael: Yeah.
Yeah, I looked at the history.
I was shocked to see that it was added back in 8.2, so 2006.
Nearly 20 years ago.
Nikolay: Yeah, this is exactly when I started to be involved
with Postgres.
And 8.2 when I worked on XML as well and I thought how to provide
good indexing capabilities for XML But it was too big task for
me being like too young guy, but we discussed with People who
worked on GIN these ideas, of course, and of course I missed
those days and I feel Sad very sad about what's happening in
the world and how disconnected we became all of us Yeah
Michael: because these guys are all Russian folks, but also I
was looking at the domains and one of the TL there's a there's
a an article mentioned in the Postgres docs on GIN indexes
that references the initial release and mentions who's who sponsored
the work which I think was a German company but one of the domain
the domain it's hosted on is .su which I didn't even know I didn't
even know what that stood for it turns out it's USSR or Soviet
Union which makes sense.
Nikolay: Yeah I wanted to say that originally the primary purpose
was to support better performance for full-text search.
And originally, full-text search was powered by GiST.
And this version called RD3, Russian Doll Tree, where its B-tree-like
structure was created for basically arrays, for sets.
And supported operations, like instead of less than, greater
than, supported operations is contained, 1 array is contained
in another, or contains.
Right?
So, also overlaps.
And it just can work with this, it can support full text search,
but it's slow if you have large volumes of data and a lot of
words, different words, like vocabularies is huge.
Actually used vocabulary, right?
So GiN is inverted index.
And the name was inherited from just just as Generalized Search
Tree.
I like this so much.
Folks who are interested in databases and want to learn something
cool, read article by Hellerstein from Berkeley, Joseph Hellerstein,
and article about jumping from single dimension B-tree to two-dimensional
space R-tree and then generalizing this idea to GiST, generalized
search tree.
It's so cool.
And then additionally, RD-tree, it's 4 sets already.
And of course, you can have multiple dimensions, like many dimensions.
Not so many as for vectors, unfortunately, like not 2,000, but
for a few dimensions, GiST works well.
And Even there was a history for R-tree, the original implementation
of R-tree, native implementation.
GiST behaved much better, so it was replaced.
The original implementation of R-tree for multiple dimensions was
replaced by GiST, not for B-tree.
But there is extension, GiST B-tree, for various multi-column
indexes and so on, when you have columns of different nature.
And trees are great, but for full-text search, they are not so
great.
So they jumped from idea of working with trees to inverted index,
which is a more common idea for search engines.
And compared to tree-like structure, inverted index is very simple
explanation can be, okay, we have list of words, and for each
word we have list of references to places where this word was
noticed, right?
Like,
Michael: like, where is
Nikolay: links to tuples?
Yeah,
Michael: no, yeah.
Nikolay: Rovers.
So list of words, of course, normalized.
And then for each word list of places where it can be found.
It's very, very similar to index in a book, in the end of the
Michael: book.
Yeah.
I read this.
And for so many years, I've been using and reading about, you
know, indexes in the back of books or, you know, in recipe books
is like, there's a really famous example, really easy 1 to understand
how indexing speeds things up.
But it never occurred to me that they're not a B-tree structure
they are a list of words yeah exactly it's a it's an inverted
index so yeah and I think the thing that clicked for me as to
like why the word inverted is used was in a standard, in a non-inverted
index, we have an entry per row or row version and in an inverted
1 each row could be referenced multiple times assuming...
Nikolay: Exactly.
Michael: In a full text search case for example, each document
is likely to have lots of words in it.
So that's where the word inverted comes from.
Nikolay: Yeah, in tree-like structure, in tree, in B-tree, R-tree,
RD-tree, GiST in general, We perform descending.
We say we need to find this value, for example, and we jump from
1 node to another, down to the index, towards leaf nodes, leaf
nodes, right?
Leaves.
Performing some operations to understand where to go, which child
node to choose.
Not only direction, because it's, how to say, each internal node
have multiple, many children, unlike binary tree.
And my favorite example, favorite question, when hiring engineer,
not Database engineer.
For Database engineer, this question is mandatory.
But software engineer, Backend engineer, you can ask what is
B-tree and if you hear binary, this is game over.
And this, Unfortunately, I had situations when very, very good
software engineers, I had this feeling, do I want to ask that
question?
Because it's like kind of flipping the coin.
And then I hear binary, and okay, my internal principles say
this is a no-go.
So, yeah.
So, B-tree is not binary tree.
B-tree, unlike binary, where only 2 children is maximum.
We have many children for each internal node, maintaining some
rules.
Michael: In case anybody's wondering, balanced?
Nikolay: Well, there are several theories about it, as I remember,
but I don't remember theories.
But okay, yeah, balanced, but actually it's almost balanced,
meaning that, like, conceptually, the path from the root to each
leaf can be n or n plus 1.
This helps with balancing.
So almost balanced.
Okay, Again, when we work with tree, we have several operations
until we reach leaf.
And leaf has pointer to the tuple we are looking for.
Inverted index, okay, we find our word.
And then we find many places for this word, right?
And this also means there are several challenges here.
I remember originally, as I understand, maybe like my perception
might be wrong, but it was long ago.
How many?
20 years, right?
Almost.
I remember, GIN was lacking some internal B-tree indexes, it needs
B-tree indexes twice.
First, to find the word, because if the number of different words
is huge, we need additional mechanism to perform the search faster
and it can be B-tree, right?
And second, inside posting lists, we also have B-tree.
So inside GIN, there are 2 types of B-tree indexes, as I remember.
And I saw Oleg Bartunov's articles and book about internals.
And this book has, of course, a whole section about GIN indexes.
And I saw a representation, visualization for GIN was, I think
actually, the commutation also should have it, right?
It's 1 of the, maybe the first illustration which was added to
Postgres documentation was GIN, if I'm not mistaken.
My memory might trick me a little bit, but I remember I saw it
was 90 degrees rotated.
So instead of like in book, we have words, and then we see page
numbers to the right for each word.
It was rotated 90 degrees so everything looks down.
So it seems like we going down all the time like in case of GiST
or B-tree.
So starting from search of our word, then we using internal B-tree,
then we go in posting link, posting list, we also use B-tree
and also descending, right?
And I honestly feel confusion.
I like the book-like visualization Because it distinguishes from
tree structures.
And also we have 3 internal trees here, and it only adds confusion
in my opinion.
But, well, this is the choice.
I think the guys who I think created visualization and wrote
and Igor Rogov and others, they are great in terms of education.
And I like, I definitely admire them, their work in education.
So, but I personally like original representation.
We could write B-tree also rotated, highlighting that it's kind of
different beast in terms of indexes, right?
Michael: Yeah, good.
Nikolay: Yeah, this is, sorry, this was my introduction, like
just like history and so on like and some simple explanation
Yeah,
Michael: yeah, I really liked it and I really want to like diagrams
in the docs I think they a lot of effort goes into making diagrams
that are easy to understand unfortunately we don't have that
many in the Postgres documentation but I also find that I personally,
I don't learn best visually I don't think and I found the description
in the Postgres docs of how GIN works particularly useful.
It's really well written like most of the documentation But I
found that really helpful.
So I'll link to that in the show notes as well I think that's
an incredibly good description with a few examples of I mean,
obviously Fulltext search is the main use case, but it's not
the only
Nikolay: I think right now not a full text search full text search
was a regional main use case right now when use cases JSONB
Michael: yeah interesting well I don't honestly I don't see that
I'd say I Think I'd be interested in the results of your poll
I think you've put a poll on Twitter, but I I do I do think GIN
is probably the second most popular index type of city after
B-tree, but it's by somewhere in the region I was thinking order
of magnitude wise it's I say it's there's fewer than 1 in 10
maybe but probably more than 1 in a hundred so like somewhere
in between those So it's still not super common for me to see
them.
Nikolay: Yeah, almost half of folks voted.
They say 2 index types are used.
And I guess
Michael: it's
Nikolay: either b3 and GIN, or b3 and vector types like HNSW.
Interesting.
Who knows?
Michael: I do see the occasional BRIN and the occasional hash,
but often those are kind of people have left them lying around
after an experiment, after like trying them out, but not necessarily
getting that much usage.
Nikolay: Postgres should have telemetry enabled by default.
I'm joking, I'm joking.
To get usage stats.
Michael: Well, actually, I know this is a real tangent now, But
I do think that the usage stats can be misleading for people
as well, because the last person I came across that had a hash
index, they had a hash index on the same column they had a regular
B-tree index on.
And I said, you know, that's redundant, like, if you could, the
B-tree could...
Nikolay: You already pay the price for me.
Yeah, I'm b3.
So
Michael: Exactly, but the hash index was still getting used because
it's a bit smaller for that Like in so was still getting usage
in the time I'm
Nikolay: about hash indexes.
Remember?
Michael: Yeah but all I mean is the telemetry isn't enough to
know that you can drop that hash index because it's still getting
usage.
Nikolay: I'm joking about telemetry.
I cannot imagine Postgres would add some telemetry stuff enabled
by default.
It's like it's completely opposite to what it's usually done
in Postgres.
But I'm very curious of course.
I think big platforms might have some stats but who knows.
Anyway, It definitely looks like B-tree is number 1, but GIN may
be number 2 or number 3.
Michael: 0, I'm sorry, I understand what you mean by telemetry.
I thought you meant like for the users to see what they were
using.
Nikolay: No, no, imagine Postgres has telemetry sending to some
server or user shop.
Michael: The cloud providers could do it like exactly anonymized.
Nikolay: Yeah, if, if they can, because it's also a question
how, how much I can go to schema analysis, usually should be
possible.
But yeah, okay.
But it's better to focus on different tasks for cloud providers.
We can discuss this another time.
This is not number 1 priority, I'm pretty sure.
No.
So, okay.
Michael: How about you?
What do you see?
Like, how often do you see GIN being used in the wild?
Nikolay: Well, it's used for full-text search, but often it's
losing to Elastic in larger companies.
In smaller projects, it's great.
But in larger companies, we should discuss performance gains
and overhead next, right?
But yeah, what I see, it's great until it's not, in terms of
full-text search.
For JSONB, kind of similar, but there we can tune it, we can
use smaller size of index, just preserving all the information
about keys and values.
JSONB pattern ops, these things like additional, you can tweak
it, right?
Yeah, path ops, maybe.
Path ops, exactly.
Yeah, JSONB path ops.
So you can say I don't need everything to be indexed, all the
knowledge about structure, I just need keys and values and support
only part of search operations I have.
And for JSONB for sure I see it, but at the same time in many
cases JSON or JSONB values left unindexed because it's just
storage for some metadata, for example, and we don't need to
search in it.
Or we decide to search only using specific paths inside JSON
value.
And in this case, for both JSON and JSONB, and even for XML
data type, you can have B-tree because it becomes a scalar value
when you apply some.
You need to get the value from some path.
That's it.
Michael: It's just an expression index, yeah.
Nikolay: Yeah, exactly.
So I also see quite a lot trigram, pg_trgm being used sometimes
with some issues, but yeah, this I see as well, yeah.
Michael: Again, but again, normally related to full text search
right for I say trigram by the way and I thought maybe it's
trigram I don't
Nikolay: know I think JSONB is number 1 in my head it's so I
don't have I don't have analytics so it's some subjective perception
Michael: it makes sense right It makes sense if you think I might
want to do some search on this.
It's the perfect...
I mean, it came 10 years before JSONB, but it's the perfect
index type for it, if you don't know in advance what you're going
to want to be searching for.
Nikolay: There is also a full text search for JSONB, additionally,
a specific case.
I remember some support was added to perform full text search
inside JSON values, JSONB values, also powered by GIN, I guess.
Anyway, this index is great.
Well, for arrays as well, I use it many times for arrays.
If I know I need to store arrays, text arrays, number, like integer
arrays, doesn't matter.
I like how Postgres supports them for decades.
Arrays support in Postgres is great except 1 thing you know it
right?
Michael: Nodes?
Nikolay: No no no similar but no it's
Michael: I don't know it no
Nikolay: it's indexes it starts with 1 it's very confusing oh
you
Michael: confused me saying indexes I was thinking
Nikolay: no no yeah it's different yeah Yeah I mean to access
the first element, you know everywhere else Outside Postgres
at 0 right but in Postgres it's 1 and you need to switch switch
your mind all the time Not everywhere else.
But yeah and For a race, it's great.
For example, I remember my very first talk in the US in 2008
was about how cool Postgres is for Web 2.0.
And I talked about EAV structure, how it's performance-wise not
good for larger data volumes.
And when we talked, I talked about tagging, tags, right?
For social media, we need tags.
And putting tags into separate tables following EAV, entity attribute
value structure, is going to hit performance.
So instead, we can put it as arrays in the same table.
And then to search we can just use GiN, right?
And Russian Doll Tree will be effectively used, but supported
by GiN implicitly, right?
So in this case you can find quickly like give me rows where
where we have this tag right there will be problem although of
ordering Right.
This is the like Before we move on to discussing this problem,
I think this is the biggest problem with full-text search and
general GiN.
People suffer from it, but we will discuss it.
Before that, let's discuss performance gains and losses.
Gain is obvious.
Search speed is much better for inverted index than for tree-like
structure.
So for larger volumes of data, search is good, SELECT is good,
right?
If we, if like 1 comment here, Only if we talk about the use
Michael: of GiN only, right?
And like only searching by 1 dimension.
Nikolay: Yeah, well, 1 word or few words also, it has an internal
kind of additional internal query language, you can say, and
or phrase search, a lot of features there.
But also when we say this, search speed is better.
We need to think about edge cases as well.
For example, if we have a very, very popular word, which was
not excluded by putting it to stop list or vocabulary, stop list
dictionary.
If we didn't put it there, we're going to have poor selectivity
and high cardinality of results and performance.
I don't know.
The limit works, right?
But then there are edge cases where it's quite a popular word,
we use GiN.
And there is a big problem of the need to combine GiN based
search with something else, like for example, additional filter
on some scholar like timestamp or anything, or ordering.
Right.
And this, this is huge problem.
Like I want to find everything which includes these words or,
or these values doesn't matter, but I also Need to get 25 last
items from there And this is somehow
Michael: wait it like you might want to wait based on the the
more the more recent The more you want to wait it, but if it's
got, you know, the word many many times over I I get it I think
we're probably venturing a bit too far into full-text search
stuff rather than GIN stuff, but you're right.
Nikolay: It's about GIN,
it's not about full-text search.
For trigram, don't you want to have most popular or used or like
the latest?
Or for JSONB search, I want to find everything which includes
these things, but I want very fresh.
I want to order somehow.
And in most cases in modern world, like when it was created,
the idea was we are going to order by ranking.
It's all was full search, search, right?
Ranking like most relevant, but in social media, usually don't
want that.
We usually want the freshest information.
Fresh.
Yeah.
So creation time or ID, if it's numeric or your ID version 7
would work.
So we need to order by some scalar.
And this works not well with GIN.
Michael: I think you're right though.
I think maybe at scale it's always going to be a problem.
There is a feature that was added from the beginning with GIN,
at least for the stop word issue, and that's GIN fuzzy search
limit that you can set in, that it recommends in the thousands,
so that if your search would have returned more than that number
roughly it will limit them but it will limit them randomly so
that that plays into your which which 1 should be returned but
the idea behind that feature is if you're returning thousands
of options anyway how good is that search in the first place?
Like it's a...
So, yeah, you're right.
Nikolay: Also statistics-related work, like over time it was
improved, but I remember still having issues like with lossiness
of fore a planner, right?
Like we need to recheck.
I don't know actually details right now.
I'm using GIN blindly lately in terms of performance.
But for me, the key problem is inability for GIN to combine search
with additional Scalar-based filters or ordering.
Ordering is number 1 problem for me.
It led the same folks to create RUM indexes.
Michael: Yeah, which confused me because when they launched GIN
they said you should think of it as a genie not as an alcoholic
drink but then they come out with RUM and it's definitely about
alcoholic drinks.
So I don't believe them.
Yeah.
Nikolay: Ambush, right?
Yeah.
Well, and the RUM, unfortunately, in my case, didn't work well
because of like, it was huge and so on.
Maybe it was because I tried to put...
The idea of RUM is let's put columns into a structure, right?
Michael: Like timestamp.
So that you can do the thing you wanted.
Nikolay: Right, exactly.
And creation time, it's how many?
8 bytes or 16?
I keep forgetting.
It was huge.
I guess I could reduce it.
For example, putting 4 byte integers just to support ordering.
There are ideas how to improve, but still, this is an Extension,
it's not available on many platforms, unfortunately.
But there is another thing, there is a standard contrib module
Extension which is available everywhere called B-tree GIN.
At least this can help you to combine different filters.
For example, if you want to have filter to search in JSONB or
full text search or trigram index, you want to combine it with
additional filtering, for example, price, right?
Or age, depending on data, right?
This is possible, this is worth considering.
So to have multi-column index, and maybe for ordering it also
can be helpful, but if you don't do anything, what is usually
happening, the planner needs to decide.
Should it use, for example, a primary key to order, or create
and add timestamp to order by, and then apply filter on...
Instead of GIN, it will be just applying filter for whole data
it will find.
And it can be very...
It's like...
Alternative is to perform GIN filtering, find everything and
then sort in memory.
And it's hit and miss.
Sometimes we see that a planner thinks, a planner is very optimistic
thinking we will quickly find just following B-tree, by created
at, in reverse order, like very fresh item, dynamically check,
does it match our filter which is supposed to go through GIN?
Well, it's not matching, not matching, not matching.
And if it's a miss, it might skip many, many, many rows until
it finds our 25 target house.
And just following B-tree, not involving GIN at all.
And this is a terrible situation.
I saw many incidents because of that.
So, or vice versa, GIN, again, we use some popular word, it
finds millions of entries, then tries to sort them in memory
to find top 25.
Maybe it's like edge cases here may be hitting less in terms
of performance, but also not not not fun at all to have this
situation Right.
Yeah, and this is number 1 be
Michael: where the the fuzzy limit could actually maybe help
there, depending on how important it is to not have false negative,
like things that should show up not showing up, I guess, depending
on the use case.
If you just want to show something, let's say you're showing
a social media feed, trying to show engaging things to people,
it doesn't matter if there's a few really good ones that don't
show.
But then if you've got a different use case, then it matters
a lot.
Like if the full-text search, if you don't show the most relevant
thing, that's an issue.
Nikolay: Or for example, user just added something, goes to search,
expects this to be there, but it's not there, it's not good.
It's like, feeling of this is like it's a bug.
So it's in many cases, it's unacceptable, unfortunately.
And also there is a problem with pending list, right?
Pending list.
So
Michael: this is about inserts rather than selects.
Nikolay: Yeah, not posting lists.
Posting lists For each word, basically, we have references to
tuples.
It's posting list.
And then there is pending list, which was like the trick to,
we forgot to mention the main problem with GIN, actually.
The key problem is slow writes, right?
Because search is fast with all the things we discussed, but
writes are slower than writes to tree-like structures, to trees,
to B-tree, to GiST.
So even with rebalancing, I guess it's like slower.
And what was created?
Fast update option.
Michael: It's an unfair comparison, right?
It's almost by design that they have to be slower because documents
contain lots of words.
1 row being updated or added creates multiple writes to the index.
So it's not a fair fight, it's not a fair comparison.
Nikolay: I agree.
So to mitigate this fast update technique was created and there's
an option fast update when you create an index.
And I guess it's on by default, right?
Yeah.
So fast update means that changes will go to some temporary like
buffer, like kind of location inside the structure called...
Michael: Pending list.
Nikolay: Pending list, yes.
And then at some point when they reach by default 4 megabyte.
Michael: Yeah, there's a few things that can trigger it.
That's 1.
There's
Nikolay: like a
Michael: catch there.
What happens?
But also, Oh, it batch processes them, right?
Nikolay: Yeah, and it's happening, it can happen synchronously.
So some write, some Update or INSERT might suddenly be very slow.
So slow it might hit like your statement amount, which is like
30 seconds, for example, or 60 depending, right, or 10 seconds.
And this is like unpredictable performance for writes Yeah, or
vacuum can do it a synchronously.
Wow Yeah, vacuum.
I like but what I saw from practice vacuum is good and mitigate
this mitigate this and not allowing this job to be done in a
synchronous manner.
But it works only if our duration of vacuum is reasonable, which
points to the direction we do need Partitioning.
Because if we have a huge Table, vacuum is super slow.
It cannot catch up with our writes.
And pending list problem hits our writes.
And users notice it.
At some point, it might be so that we decide, OK, we need to
switch off FastUpdate for some Indexes, for large Tables, because
we want predictable, maybe not good, but predictable performance
of writes.
It depends.
There is a trade-off, and I see both cases are not ideal, you
know, like if you have really large volumes of data and workload,
heavy workload.
So Partitioning can help here and to keep FastUpdate on, in
my opinion.
Michael: Well, yeah, have you tried if you tried that I'm just
thinking actually then you've got to have the Partition key in
the Query and then suddenly you've got another dimension there
sounds tricky
Nikolay: and and so what we need different Index in this case
right
Michael: Well it just might not then use the GIN.
I've had trouble convincing the planner to use GIN indexes in
the past when like they've got another choice and it doesn't
I'm probably doing I'm maybe I'm doing something wrong but I
wanted to try and get a bitmap scan like anding the Index like
with a GIN Index as 1 bitmap Index scan and let's say a B-tree
Index is another.
Nikolay: Sounds like the case for B-tree GIN for me.
Michael: Well a multi-column GIN index with B-tree in it could
have helped but it wouldn't have been as like it wouldn't have
been structured quite as like optimally in my opinion so I was
hoping to get this and list really short and then a really like
quick scan and I didn't have luck with multi-column B-tree GIN
index being as like as performant as I thought we could get it
with a bitmap scan.
But the partitioning thing, I would love to be wrong, but the
partitioning thing sounds like you've now got a GIN index per
partition, right?
So you've got to get to the right partition so then it's like
again if it's time
Nikolay: partition should work partition pruning should be in
good shape of course but this is general rule for partitioning
Michael: yeah but so let's say you're partitioning by time, it
means we've got the time filter on, right?
Nikolay: Right.
Michael: Is that the case?
Like for full text search, for example, are we going to be filtering
by time?
Nikolay: Well, if we're ordering by time, as I, like For social
media, again, we need fresh information.
In most cases, this is the rule compared to previous, like pre-previous
era for Web 1.0.
Michael: Well, unless there isn't fresh information, then you
need to go back and back and back, right?
Nikolay: Maybe, yes.
Well, depends, depends.
So my question is how we limit how we order Like if we don't
limit don't order This is found fundamental question to this
query.
Why do we request unlimited?
Results Right.
This is this question.
I raised very often during consulting practice very often I see
no limit or no order by and this is the question in most cases
people say oh, actually, yeah, it makes sense to have pagination
here, for example.
Or pagination with GIN.
Yeah.
It's interesting.
Michael: It's difficult, right?
Nikolay: Yeah.
Well, again, if we order by ID or timestamp by scalar value,
it's like key set pagination works, and that's it.
And GIN is just additional filtering in this case.
Michael: And we're kind of back to where we started with your
original comment that it works until it doesn't.
So in scale, it's fine to not have these, it's fine to not have
the limit and the order by because your data set is small enough
that your query response times are pretty good.
Nikolay: But we designed for the future, not only for now, right?
Because, yeah, so it's always worth asking yourself how much
data you expect in the future, how much data you need to return
with this query to your application or frontend, I don't know.
Do we need everything?
I saw some case, interesting case when people decided, intentionally
decided to return everything and then do a lot of work on frontend.
I saw it many times actually, with guys who decided that our
frontend should be very thick, and a lot of logic there, and
it's better to cache everything.
Like, let's load and then work.
Well, in my opinion, it's a dead end.
Because if your project grows, it's a dead end.
You will experience poor performance.
And other case was guys who decided to perform analytics with
R, so they also needed to load everything and then build B-tree
and so on and kind of group by and so on was performed only in
memory using R, which for me looks like, why do you do this?
Do it inside your database.
Let me not forget, when pending list is growing to the limit,
it can exceed limits, right?
Or no?
Michael: Is it a soft limit or no?
I'm not sure.
Nikolay: This is a good question.
But anyway, maybe it was a bug when it exceeded.
I don't remember.
I remember some parts of my memory tell me that it happened in
the past.
I'm not sure it was a bug or not.
But obviously, also, select performance will be affected if pending
list is huge.
For example, imagine for specific filters, we are unlucky and
we need to process all like we we need to deal with it and selects
become slower so
Michael: yeah so it's for it's 4 megabytes right the
Nikolay: default I
Michael: suspect I suspect it's not too much of a penalty if
you leave it at the 4MB, but a tempting thing to do might be
to increase that so that you hopefully have autovacuum kick
in and do the work in the background instead of having a random
insert paying the penalty.
Nikolay: Right.
Michael: But then once you've increased the list, then each select
has to look through more pending entries, especially towards
the end, and then you're paying the price on select as well.
So yes, I don't think I haven't seen in fact, actually, we probably
should mention there's a really good write up about these kinds
of issues by Lukas Fittl of pganalyze who also
Nikolay: use
Michael: the GitLab work.
Nikolay: I think Lukas does a fantastic job analyzing various Postgres
cases.
I think that's why his company is called pganalyze, but this
is your joke, not mine.
Michael: You're stealing my jokes now.
Nikolay: Yes.
Well, yeah, good job with PG analysis.
And yeah, in GitLab's case, I was slightly involved only, and
I remember that case was a few years ago and the result was a decision
for specific indexes to switch off fast update because the table
was huge and so on, right
Michael: yeah anyway so switching off fast update that means
we're not using the pending list anymore.
And every insert and update pays the price at the time of insertion
to update the index.
And I think that's a really interesting trade-off to make each
insert a bit slower, but not have to have occasional very slow
ones.
I like that a lot in terms of performance, in terms of trade-off.
Nikolay: Anyway, kudos to GitLab for openness as usual, because
all the details are visible to the public, to the community, and it's
super helpful for, like, for general development of Postgres and
so on.
And kudos to Lukas for a very great analysis of this Postgres
case.
Good.
I think we touched a little bit of some deeper waters but not
super deep.
If someone wants to read internals Igor Rogov's articles and
books are really great in this area.
And yeah, I think that's it, right?
Or anything else?
Michael: Yeah, 1 last tiny tip that I think is worth, you mentioned
JSONBPathOps briefly.
I think for most other data types, the defaults are the only
operator class, but for JSONB, the default is actually JSONBOps,
not JSONBPathOps, and you can get a performance boost with JSONBPathOps
if you don't need set and op, if you're just using the usual JSON,
the usual contains operators.
Nikolay: Final words, GIN is a very important index type.
It's not going to disappear.
It's going to be used heavily.
It's 1 of the strengths of Postgres, like, rich set of index
types.
Yeah, for sure.
Good.
Okay, So thank you.
See you next time.
Michael: Thanks so much, Nikolay.
Catch you next week.
Bye.