Nikolay and Michael discuss GIN indexes in Postgres — what they are, what they're used for, and some limitations to be aware of.
Here are some links to things they mentioned:
Here are some links to things they mentioned:
- GIN Indexes https://www.postgresql.org/docs/current/gin.html
- Generalized Search Trees for Database Systems (Hellerstein, Naughton, Pfeffer) https://dsf.berkeley.edu/papers/vldb95-gist.pdf
- RUM extension https://pgxn.org/dist/rum/1.1.0/
- Understanding Postgres GIN Indexes: The Good and the Bad (Lukas Fittl) https://pganalyze.com/blog/gin-index
~~~
What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~
Postgres FM is produced by:
- Michael Christofides, founder of pgMustard
- Nikolay Samokhvalov, founder of Postgres.ai
With credit to:
- Jessie Draws for the elephant artwork
Creators & Guests
What is Postgres FM?
A weekly podcast about all things PostgreSQL
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.
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.
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.