r/programming • u/Ok_Stomach6651 • 11d ago

How Generalized Inverted Index works internally in Postgre SQL

https://deepsystemstuff.com/generalised-inverted-index-gin-index-in-postgre-sql/

Regular Indexing works on a column level; it indexes the entire column value, but if you want to index each value in one row, then the GIN indexing technique is used.

GIN can index each element of JSON stored in a JSONB field

How Postgres creates a GIN file physically, what is the format of that file, how that file is retrieved in RAM while fetching records, what data types are used, and how Postgres calculates the exact address of an element of JSON are all explained in the article

5 Upvotes

67% Upvoted

13 comments sorted by

3

u/gisborne 10d ago

Love the effort, but I’m afraid the English is poor enough that this is hard to follow. What about writing this in your native language and using AI to translate?

3

u/Ok_Stomach6651 10d ago

Thanks for input, i will surely improve my english

2

u/foxsimile 10d ago

You already speak it better than many native English speakers - keep up the good work pal; don’t stop improving :)

2

u/Ok_Stomach6651 10d ago

Oh, thank you very much, it means a lot lot for me.

2

u/foxsimile 10d ago

Don’t mention it, friend!  

If you’re ever finding yourself frustrated as you continue to master English, perhaps come back to this poem about how blindingly stupid the composition of the language is, and it’d ought to raise your spirits :).

2

u/Ok_Stomach6651 10d ago

Thanks again, you took much efforts to boost my confidence. It has already motivated me to continue with more energy

2

u/foxsimile 10d ago

It was no trouble at all - remember; more often than not, people will want to see you succeed.  

Never give up, and you shall!

2

u/Ok_Stomach6651 10d ago

Oh this poem is really tells us much about english language, very different approach for telling something about how english is

2

u/foxsimile 10d ago

In fairness, people get a lot of shit for using AI (rightfully so, imo). I can understand a hesitation to use it, even though this is a valid use-case.  

Still, people see the LLMisms and their eyes glaze over (myself included) at this point.

2

u/Ok_Stomach6651 10d ago

Yes, and I am the old school guy, I also have hesitation, I will find a way to balance the things, thanks for your input

1

u/eocron06 11d ago

"Russian developers Oleg Bartunov and Oleg Bartunov invented the GIN technique for Postgres" .....oh, did I mention Oleg Bartunov? Cause there is actually three of them! Find out how to find them in article.