Databases & System Design•Part 2: Relational Databases (RDBMS / PostgreSQL / MySQL) (6 - 25)
PostgreSQL JSONB and GIN Indexes:
In Simple Words:
JSONB stores JSON in a decomposed binary format rather than plain text. A GIN (Generalized Inverted Index) indexes internal keys and values, allowing fast $O(1)$ search queries inside JSON payloads.
sql
CREATE TABLE events (id SERIAL PRIMARY KEY, payload JSONB);
CREATE INDEX idx_events_payload ON events USING gin (payload);
-- Fast GIN index lookup using the containment operator (@>):
SELECT * FROM events WHERE payload @> '{"status": "completed", "priority": "high"}';