PostgreSQL-Specific Features

After this lesson, you will be able to: Use Postgres-specific features: JSONB, arrays, full-text search, pg_trgm fuzzy search, EXPLAIN ANALYZE.

Postgres has features other RDBMS lack. If you're on Postgres, USE them, they're the reason you picked it.

Prerequisites:Database Design

JSONB — structured JSON in a column

Binary JSON. Indexable, queryable, fast.

sql

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  user_id INT,
  payload JSONB
);

INSERT INTO events (user_id, payload) VALUES
  (1, '{"type": "click", "target": "buy_button", "meta": {"region": "us"}}'),
  (2, '{"type": "view", "target": "home"}');

-- Query JSONB fields
SELECT * FROM events WHERE payload->>'type' = 'click';
SELECT * FROM events WHERE payload->'meta'->>'region' = 'us';
SELECT * FROM events WHERE payload @> '{"type": "click"}';

-- GIN index for fast JSONB queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);

Arrays

First-class array columns.

sql

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  tags TEXT[]
);

INSERT INTO products (name, tags) VALUES
  ('Mug',     ARRAY['kitchen', 'gift']),
  ('Book',    ARRAY['gift', 'education']);

-- Query arrays
SELECT * FROM products WHERE 'gift' = ANY(tags);
SELECT * FROM products WHERE tags @> ARRAY['gift'];
SELECT name, UNNEST(tags) AS tag FROM products;

Full-text search

Built-in tsvector + tsquery.

sql

-- Add a generated tsvector column for indexing
ALTER TABLE posts ADD COLUMN search_doc tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

CREATE INDEX idx_posts_search ON posts USING GIN(search_doc);

-- Search
SELECT id, title, ts_rank(search_doc, query) AS rank
FROM posts, to_tsquery('english', 'postgres & query') query
WHERE search_doc @@ query
ORDER BY rank DESC;

pg_trgm — fuzzy + LIKE acceleration

Trigram extension. Solves the 'slow LIKE' problem.

sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Index that makes LIKE '%text%' fast
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

-- Now this query is fast on big tables
SELECT * FROM users WHERE name ILIKE '%john%';

-- Similarity search (typo tolerance)
SELECT name, similarity(name, 'jonh') AS sim
FROM users
WHERE name % 'jonh'    -- threshold-based similarity
ORDER BY sim DESC;

EXPLAIN ANALYZE in depth

Your query-perf microscope.

sql

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.name
ORDER BY post_count DESC
LIMIT 10;

-- Read it bottom-up:
--   Look for 'Seq Scan' on big tables → consider adding index
--   Look for 'Sort' with large 'Disk' usage → increase work_mem
--   Look for 'Hash Join' vs 'Nested Loop', planner's choice based on stats
--   'cost' = planner estimate; 'actual time' = real time. Big gap = stale stats; run ANALYZE.

Common mistakes

Using JSON (not JSONB) in Postgres, JSON has no binary form, no GIN indexes. Storing JSON in TEXT column, loses query operators + validation. Full-text search without tsvector index, slow. Reading EXPLAIN top-down (read bottom-up, leaves are scanned first).

←Database Design and Normalization

Back to SQL

SQL from Applications→