GIN (Generalized Inverted Index)
An index type suitable for handling composite values and for searching within the composite values
GIN
(Generalized Inverted Index) is an index type which supports more complex data types than the usual key/value relationships supported by e.g. btree
indexes. Common applications for GIN
include indexing array values and full text search.
GIN
index support was introduced in PostgreSQL 8.2.
Change history
- PostgreSQL 13
- PostgreSQL 12
- PostgreSQL 11
- predicate locking added (commit 43d1ed60)
- PostgreSQL 10
- page locking during vacuum operations reduced (commit 218f5158)
- PostgreSQL 9.6
maintenance_work_mem
values of more than1GB
can be used forGIN
index builds (commit 30bb26b5)- pages deleted from a GIN index's pending list to the free space map immediately (commits e9568083 and dc943ad9)
- function
gin_clean_pending_list()
added (commit 7f46eaf0)
- PostgreSQL 9.5
gin_pending_list_limit
configuration parameter added (commit a1b395b6)- following functions for inspecting
GIN
indexes added topageinspect
(commit 3a82bc6f):gin_metapage_info()
gin_page_opaque_info()
gin_leafpage_items()
- PostgreSQL 9.4
- PostgreSQL 9.3
- function
pgstatginindex()
added topgstattuple
(commit 357cbaae)
- function
- PostgreSQL 9.1
- PostgreSQL 9.0
- use red-black binary trees for index creation (commit 5209c084)
- PostgreSQL 8.4
- support for partial-match searches added (commit e6dbcb72)
- index operator lossiness determination pushed down to
GIN
opclass functions (commit 9b5c8d45) - search tree depth limit for
GIN
bulk-insert operations added (commit e5efda44) - "fastupdate" support added (commit ff301d6e)
- multi-column support added (commit 27cb66fd)
- PostgreSQL 8.3
- PostgreSQL 8.2
- added (commit 8a3631f8)
References
- PostgreSQL documentation: GIN indexes
- PostgreSQL documentation: GiST and GIN Index Types
Useful links
- Understanding Postgres GIN Indexes: The Good and the Bad - December 2021 blog article by Lukas Fittl / pganalyze
- GIN - Just A Kind Of Index - explanation from Cybertec Schönig & Schönig GmbH
- Original developer documentation (outdated)