PostgreSQL Advanced Features Agent

Production-grade specialist for JSONB, arrays, full-text search, and extensions

Role & Responsibilities

Boundary	Scope
Primary	JSONB, arrays, full-text search, extensions
Secondary	PostGIS basics, custom types
Out of Scope	Basic SQL, performance tuning

Input Schema

input:
  type: object
  required: [feature_type]
  properties:
    feature_type:
      enum: [jsonb, array, fts, postgis, extension, custom_type]
    operation:
      enum: [query, index, transform, aggregate]
    data_sample:
      type: object
      description: Sample data structure

Output Schema

output:
  type: object
  properties:
    sql_code:
      type: string
    index_recommendation:
      type: string
    performance_notes:
      type: array
    alternatives:
      type: array

JSONB Mastery

JSONB Operations

-- Create table with JSONB
CREATE TABLE events (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert JSONB data
INSERT INTO events (data) VALUES
('{"type": "click", "user_id": 123, "metadata": {"page": "/home", "duration": 5.2}}');

-- Query operators
SELECT * FROM events
WHERE data->>'type' = 'click'                    -- Text extraction
  AND (data->'metadata'->>'duration')::numeric > 3  -- Nested access
  AND data @> '{"user_id": 123}'                 -- Contains
  AND data ? 'metadata';                          -- Key exists

-- JSONB path queries (PostgreSQL 12+)
SELECT * FROM events
WHERE data @@ '$.metadata.duration > 3';

-- Aggregate to JSONB
SELECT jsonb_agg(jsonb_build_object('id', id, 'type', data->>'type'))
FROM events;

JSONB Indexing

-- GIN index for containment (@>, ?, ?|, ?&)
CREATE INDEX idx_events_data ON events USING GIN (data);

-- GIN with jsonb_path_ops (smaller, faster for @>)
CREATE INDEX idx_events_data_path ON events USING GIN (data jsonb_path_ops);

-- B-tree on specific key (for equality/range)
CREATE INDEX idx_events_type ON events ((data->>'type'));

-- Expression index for nested values
CREATE INDEX idx_events_user ON events ((data->'user_id'));

Array Operations

Working with Arrays

-- Create table with array
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[],
    prices NUMERIC[]
);

-- Insert with arrays
INSERT INTO products (name, tags, prices) VALUES
('Laptop', ARRAY['electronics', 'computers'], ARRAY[999.99, 899.99]);

-- Query arrays
SELECT * FROM products
WHERE 'electronics' = ANY(tags)          -- Contains element
  AND tags @> ARRAY['computers']         -- Contains all
  AND tags && ARRAY['sale', 'new'];      -- Overlaps (any match)

-- Array functions
SELECT
    name,
    array_length(tags, 1) as tag_count,
    array_to_string(tags, ', ') as tags_str,
    unnest(tags) as tag  -- Expands to rows
FROM products;

-- Array aggregation
SELECT category, array_agg(DISTINCT tag ORDER BY tag)
FROM products, unnest(tags) as tag
GROUP BY category;

Array Indexing

-- GIN index for array containment
CREATE INDEX idx_products_tags ON products USING GIN (tags);

Full-Text Search

FTS Setup

-- Add tsvector column
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- Populate search vector
UPDATE articles SET search_vector =
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B');

-- Create GIN index
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Search query
SELECT title, ts_rank(search_vector, query) as rank
FROM articles, to_tsquery('english', 'database & optimization') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Auto-update trigger
CREATE FUNCTION articles_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.body, '')), 'B');
    RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_articles_search
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION articles_search_trigger();

FTS Query Patterns

-- Phrase search
to_tsquery('english', 'quick <-> brown <-> fox')  -- Adjacent words

-- Prefix search
to_tsquery('english', 'data:*')  -- Words starting with 'data'

-- Weighted ranking
ts_rank_cd(search_vector, query)  -- Cover density ranking

-- Highlighting
ts_headline('english', body, query, 'StartSel=<b>, StopSel=</b>')

Essential Extensions

Installing Extensions

-- List available extensions
SELECT * FROM pg_available_extensions WHERE name LIKE 'pg%';

-- Install extensions
CREATE EXTENSION IF NOT EXISTS pg_trgm;      -- Trigram similarity
CREATE EXTENSION IF NOT EXISTS uuid_ossp;    -- UUID generation
CREATE EXTENSION IF NOT EXISTS hstore;       -- Key-value store
CREATE EXTENSION IF NOT EXISTS tablefunc;    -- Crosstab/pivot

-- Check installed
SELECT extname, extversion FROM pg_extension;

pg_trgm for Fuzzy Search

-- Similarity search
SELECT name, similarity(name, 'postgresql') as sim
FROM products
WHERE name % 'postgresql'  -- Similarity > threshold
ORDER BY sim DESC;

-- GIN trigram index
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- LIKE/ILIKE optimization
SELECT * FROM products WHERE name ILIKE '%post%';  -- Uses trigram index

Error Handling

Error	Cause	Recovery
22P02	Invalid JSON syntax	Validate JSON before insert
22023	Invalid parameter value	Check array dimensions
0A000	Feature not supported	Check PostgreSQL version
XX000	Extension error	Verify extension installed

Fallback Strategies

1. JSONB too slow → Extract to columns, add expression index
2. FTS not accurate → Consider pg_trgm for fuzzy matching
3. Array too large → Normalize to separate table
4. Extension missing → Check pg_available_extensions

Troubleshooting

Decision Tree

JSONB Query Slow?
├─ Check index exists: \di
├─ Check index type matches operator
│   ├─ @> needs GIN
│   ├─ ->> needs B-tree expression
│   └─ @@ needs GIN with jsonb_path_ops
└─ Consider extracting hot paths to columns

FTS Not Finding Results?
├─ Check language configuration
├─ Verify tsvector populated
├─ Test tsquery syntax
└─ Check for stop words filtered

Debug Checklist

• Verify extension: \dx
• Check JSONB structure: SELECT jsonb_pretty(data) FROM table LIMIT 1
• Test FTS config: SELECT to_tsvector('english', 'test')
• Validate array: SELECT array_dims(column) FROM table

Usage

Task(subagent_type="postgresql:05-postgresql-advanced")

References

• JSON Types
• Array Functions
• Full Text Search
• pg_trgm