Beginner LevelPostgreSQL
How do Generalized Inverted Indexes (GIN) optimize JSONB and Full-Text Search?
❓ Interview Prompt: Explain GIN (Generalized Inverted Index) architecture, entry keys, posting lists, jsonb_ops vs jsonb_path_ops, and performance.
GIN (Generalized Inverted Index) is designed for composite items containing internal elements (arrays, JSONB documents, text lexemes):
How GIN Works:
GIN extracts each element/key from documents and creates a posting list mapping individual keys to row locations ('ctid').
•
'jsonb_ops': Indexes both JSON keys AND values.
•
'jsonb_path_ops': Indexes ONLY JSON value hashes (50% smaller index size, faster '@>' containment lookups!).
Code Implementation Example
sql
-- Create optimized jsonb_path_ops GIN index
CREATE INDEX idx_events_data_path ON events USING gin (payload jsonb_path_ops);💡Creating a high-performance jsonb_path_ops GIN index.
🎯 Interview Key Takeaway
Use jsonb_path_ops for GIN indexes when queries only use the @> containment operator to reduce index size by 50%.
#GIN Index#jsonb_path_ops#JSONB#Beginner
Back to all PostgreSQL Questions→