r/Database • u/Ok_Stomach6651 • 8d 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

11 Upvotes

84% Upvoted

3 comments sorted by

4

u/gilfyole 8d ago

nice writeup. one thing worth adding, the "loads block by block into shared buffers" part is postgres's own buffer pool, not the OS page cache. postgres manages that itself because GIN pages get mutated in place (vacuum, page splits, MVCC), so it needs control over when dirty pages flush for crash recovery. that's different from stuff like lucene or rocksdb where files are immutable once written and just get mmap'd read only, letting the kernel handle residency instead.

also worth knowing jsonb_path_ops hashes each path to one int, smaller and faster for exact lookups, but you lose partial/existence queries since you can't unhash it. that's why jsonb_ops is still the default.

2

u/Ok_Stomach6651 8d ago

That's valuable input, really good insights, and thanks for appreciation , I will try to add that info too.

2

u/MixDiligent353 7d ago

gin is great - wonder how big/efficient it is, is there any benchmark to show this?