JSON & full-text search
Store and query JSON/JSONB documents and run full-text search — native PostgreSQL features in Silos.
Two of Postgres's most useful capabilities — JSONB document storage and built-in full-text search — are part of the real engine Silos runs, so they work exactly as they do in upstream PostgreSQL.
JSON and JSONB
Store semi-structured data in a jsonb column and query into it with the standard
Postgres operators:
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload jsonb NOT NULL
);
INSERT INTO events (payload)
VALUES ('{"type": "signup", "user": {"id": 42, "plan": "pro"}}');
-- Extract a nested field
SELECT payload -> 'user' ->> 'plan' AS plan
FROM events
WHERE payload ->> 'type' = 'signup';
-- Containment query
SELECT * FROM events
WHERE payload @> '{"user": {"plan": "pro"}}';For fast containment and key lookups on large JSONB columns, add a GIN index:
CREATE INDEX idx_events_payload ON events USING gin (payload);Full-text search
Postgres full-text search is built in — tsvector, tsquery, ranking, and all:
CREATE TABLE articles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
body text NOT NULL
);
-- Match a query against the document
SELECT id, title
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
@@ websearch_to_tsquery('english', 'serverless postgres');Speed it up with a GIN index over a generated tsvector column:
ALTER TABLE articles
ADD COLUMN search tsvector
GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_search ON articles USING gin (search);Need vector similarity search or trigram fuzzy matching on top of this? Those come
from extensions — see Extensions for pgvector and pg_trgm.