JSONB, Full-Text Search, and Extensions

Use JSONB where appropriate, index flexible data, search text, and understand PostgreSQL extensions.

JSON and JSONB

PostgreSQL provides json and jsonb for JSON values. json stores the input text representation, while jsonb stores a decomposed binary representation that supports efficient operations and indexing. For most application workloads involving querying or updating JSON fields, jsonb is the usual choice.

JSONB is not a substitute for relational modeling in every case. Frequently queried, constrained, or joined attributes may belong in ordinary columns. Use JSONB for flexible or evolving attributes where its query model is useful, and consider generated columns or expression indexes for common access paths.

Query a JSONB document

CREATE TABLE events (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload JSONB NOT NULL
);

SELECT id, payload ->> 'type' AS event_type
FROM events
WHERE payload @> '{"type":"signup"}'::jsonb;

💡 ->> extracts a JSON field as text, while @> tests containment. A suitable GIN index may accelerate containment queries depending on the operator class and workload.

Key Takeaways

JSONB supports querying and indexing structured JSON values.
Use relational columns for stable, frequently queried domain attributes when appropriate.
Choose operators and indexes based on actual JSON query patterns.

Knowledge Check

Which type is commonly preferred for queryable JSON data in PostgreSQL?

Indexing JSONB

A GIN index can support many JSONB containment and key-existence operations. The default jsonb_ops operator class supports a broader set of operators, while jsonb_path_ops supports a narrower set with different storage and performance characteristics. The best choice depends on the operators used by the workload.

An index is only useful when the query expression and operator match its capabilities. For frequent scalar extraction, an expression index may be more appropriate than a broad GIN index. Test representative data and inspect query plans rather than assuming every JSON query will use an index.

GIN index for containment

CREATE INDEX idx_events_payload_gin
ON events USING GIN (payload);

SELECT id
FROM events
WHERE payload @> '{"type":"signup"}'::jsonb;

💡 The GIN index may help containment queries. Verify the plan with representative table size and statistics, and choose an operator class based on the operations required.

Key Takeaways

GIN is commonly used for JSONB containment and key operations.
Expression indexes can target frequently queried JSON fields.
Validate index benefits using EXPLAIN ANALYZE.

Knowledge Check

Which index type is commonly used for JSONB containment queries?

Full-Text Search and Extensions

PostgreSQL full-text search converts documents into searchable lexemes and supports language-aware matching and ranking. to_tsvector prepares document text, while plainto_tsquery or websearch_to_tsquery builds a query from user text. A GIN index on a text-search vector can speed up searches.

Extensions add capabilities to PostgreSQL. CREATE EXTENSION enables an installed extension within a database, subject to permissions and server availability. PostGIS is a popular geospatial extension, and pg_trgm supports trigram-based similarity and certain pattern searches. Extensions should be evaluated for compatibility, security, and managed-service support.

Full-text search query

SELECT id, title
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
      @@ websearch_to_tsquery('english', 'postgresql indexing');

💡 The text is converted to a searchable vector and compared with a query vector. For larger tables, store or index the search vector rather than recomputing it for every row.

Key Takeaways

Full-text search supports lexeme-based matching and ranking.
GIN indexes can accelerate text-search queries.
Extensions add specialized capabilities but must be supported by the server.

Knowledge Check

Which operator matches a tsvector against a tsquery?

JSON and JSONB

Indexing JSONB

Full-Text Search and Extensions

STAY UPDATED WITH BYTES!