PostgreSQL Advanced Patterns: JSONB Queries, Partial Indexes & Full-Text Search

PostgreSQL as Your Document Database

PostgreSQL's JSONB type stores JSON as a decomposed binary format that supports fast key-value lookups, nested queries, and full index coverage. Combined with GIN indexes, JSONB queries can be faster than equivalent MongoDB queries because PostgreSQL can plan the query in context of joins and other relational operations.

This article covers the advanced patterns that power Shopify's product metadata system, GitLab's CI configuration storage, and Stripe's event payload indexing.

1. JSONB Fundamentals — Operators and Functions

CREATE TABLE products (
    id          BIGSERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    price       NUMERIC(10,2) NOT NULL,
    metadata    JSONB NOT NULL DEFAULT '{}',
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO products (name, price, metadata) VALUES
('MacBook Pro M4', 1999.00, '{"brand":"Apple","specs":{"ram":16,"storage":512},"tags":["laptop","productivity"],"in_stock":true}'),
('Dell XPS 15',    1599.00, '{"brand":"Dell","specs":{"ram":32,"storage":1024},"tags":["laptop","workstation"],"in_stock":false}');

JSONB Operators

-- Arrow operators
SELECT metadata -> 'brand' FROM products;          -- Returns JSON: "Apple"
SELECT metadata ->> 'brand' FROM products;         -- Returns TEXT: Apple
SELECT metadata -> 'specs' ->> 'ram' FROM products; -- Nested: 16

-- Containment operators (use GIN index!)
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';
SELECT * FROM products WHERE metadata @> '{"tags": ["laptop"]}';
SELECT * FROM products WHERE metadata @> '{"specs": {"ram": 16}}';

-- Key existence
SELECT * FROM products WHERE metadata ? 'brand';
SELECT * FROM products WHERE metadata ?| ARRAY['brand', 'sku'];  -- ANY key
SELECT * FROM products WHERE metadata ?& ARRAY['brand', 'specs']; -- ALL keys

2. GIN Indexes for JSONB

-- Default GIN index — supports @>, ?, ?|, ?& operators
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- jsonb_path_ops — smaller, faster for @> containment only
CREATE INDEX idx_products_meta_path ON products USING GIN (metadata jsonb_path_ops);

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';
-- With GIN: Index Scan, actual time=0.02ms
-- Without:  Seq Scan, actual time=120ms on 1M rows

3. Partial Indexes — Index Only What You Query

A partial index only includes rows matching a WHERE clause, making it dramatically smaller and faster.

-- Full index wastes space on inactive records
CREATE INDEX idx_orders_status ON orders(status);

-- Partial index — only indexes rows you actually query
CREATE INDEX idx_orders_active ON orders(created_at DESC)
    WHERE status = 'ACTIVE' AND deadline > NOW();

-- Real-world size comparison (1M order table, 10% active):
-- Full index:    ~42 MB
-- Partial index: ~4.2 MB (10x smaller, faster cache utilisation)

Expression Indexes

-- Case-insensitive email search
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'user@gmail.com';  -- Uses index

-- Index on JSONB computed value
CREATE INDEX idx_products_ram ON products((CAST(metadata ->> 'specs' AS JSONB) ->> 'ram'));
SELECT * FROM products WHERE (metadata -> 'specs' ->> 'ram')::INT >= 16;

4. Full-Text Search with tsvector and tsquery

-- Generated tsvector column for full-text search
ALTER TABLE products ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        to_tsvector('english',
            coalesce(name, '') || ' ' ||
            coalesce(metadata ->> 'brand', '')
        )
    ) STORED;

CREATE INDEX idx_products_fts ON products USING GIN(search_vector);

-- Full-text search with ranking and highlighting
SELECT
    name,
    price,
    ts_rank(search_vector, query) AS rank,
    ts_headline('english', name, query, 'StartSel=<b>, StopSel=</b>') AS highlighted
FROM
    products,
    to_tsquery('english', 'MacBook & Pro') query
WHERE
    search_vector @@ query
ORDER BY rank DESC;

Weighted Search

-- Assign weights: A (title), B (description), C (tags)
UPDATE products SET search_vector =
    setweight(to_tsvector('english', name), 'A') ||
    setweight(to_tsvector('english', coalesce(metadata ->> 'description', '')), 'B') ||
    setweight(to_tsvector('english', coalesce(metadata ->> 'tags', '')::text), 'C');

5. Covering Indexes — Eliminate Heap Fetches

-- Regular index: still needs heap fetch for SELECT columns
CREATE INDEX idx_orders_user ON orders(user_id);

-- Covering index (INCLUDE): all needed columns in the index leaf
CREATE INDEX idx_orders_user_covering ON orders(user_id)
    INCLUDE (id, status, created_at);
-- Plan: Index Only Scan (no heap fetch!) — 3-10x faster for cached data

Rule of thumb: Add INCLUDE only for columns that appear in SELECT but not in WHERE/ORDER BY. Too many included columns negate the size benefits.