By Matty Stratton
May 2nd, 2026
6 min
This post is a practical companion to It's 2026, Just Use Postgres. That post makes the architectural case for consolidating on Postgres. This one shows you how.
Below are working SQL examples for each use case. Every extension listed here is available on Tiger Cloud with no additional setup. If you're self-hosting, each section links to the extension's repo.
What you'll be able to do after reading this: Set up Postgres extensions for full-text search, vector search, time-series, caching, message queues, document storage, geospatial queries, and scheduled jobs. Each section is self-contained, so you can skip to what you need.
Here's the full set. You probably don't need all of them. Pick the ones that match your workload.
CREATE EXTENSION pg_textsearch; -- BM25 full-text search
CREATE EXTENSION vector; -- Vector search (pgvector)
CREATE EXTENSION vectorscale; -- DiskANN index for vectors
CREATE EXTENSION ai; -- AI embeddings and RAG workflows
CREATE EXTENSION timescaledb; -- Time-series
CREATE EXTENSION pgmq; -- Message queues
CREATE EXTENSION pg_cron; -- Scheduled jobs
CREATE EXTENSION postgis; -- GeospatialExtension: pg_textsearch (true BM25 ranking)
What you're replacing: Elasticsearch (separate JVM cluster, complex mappings, sync pipelines), Solr, or Algolia ($1 per 1,000 searches).
What you get: The same BM25 algorithm that powers Elasticsearch, running natively in Postgres. No separate cluster. No sync jobs. No data drift.
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT,
content TEXT
);
-- Create a BM25 index
CREATE INDEX idx_articles_bm25 ON articles USING bm25(content)
WITH (text_config = 'english');
-- Search with BM25 scoring
SELECT title, -(content <@> 'database optimization') AS score
FROM articles
ORDER BY content <@> 'database optimization'
LIMIT 10;Deep dive: You Don't Need Elasticsearch: BM25 is Now in Postgres
Extensions: pgvector + pgvectorscale
What you're replacing: Pinecone ($70/month minimum, separate infrastructure, data sync), Qdrant, Milvus, or Weaviate.
What you get: pgvectorscale uses the DiskANN algorithm (from Microsoft Research). On a 50M vector benchmark, it achieved 28x lower p95 latency and 16x higher throughput than Pinecone at 99% recall.
CREATE EXTENSION vector;
CREATE EXTENSION vectorscale CASCADE;
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT,
embedding vector(1536)
);
-- High-performance DiskANN index
CREATE INDEX idx_docs_embedding ON documents USING diskann(embedding);
-- Find similar documents
SELECT content, embedding <=> '[0.1, 0.2, ...]'::vector AS distance
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;No more manual embedding pipelines. pgai regenerates embeddings automatically on every INSERT and UPDATE.
SELECT ai.create_vectorizer(
'documents'::regclass,
loading => ai.loading_column(column_name => 'content'),
embedding => ai.embedding_openai(
model => 'text-embedding-3-small',
dimensions => '1536'
)
);Every row stays in sync. No batch jobs. No drift.
This is where Postgres consolidation pays off immediately. Combining keyword search and semantic search in other stacks requires two API calls, result merging, failure handling, and double the latency. In Postgres, it's one query.
SELECT
title,
-(content <@> 'database optimization') AS bm25_score,
embedding <=> query_embedding AS vector_distance,
0.7 * (-(content <@> 'database optimization')) +
0.3 * (1 - (embedding <=> query_embedding)) AS hybrid_score
FROM articles
ORDER BY hybrid_score DESC
LIMIT 10;WITH bm25 AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY content <@> $1) AS rank
FROM documents LIMIT 20
),
vectors AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $2) AS rank
FROM documents LIMIT 20
)
SELECT d.*,
1.0 / (60 + COALESCE(b.rank, 1000)) +
1.0 / (60 + COALESCE(v.rank, 1000)) AS score
FROM documents d
LEFT JOIN bm25 b ON d.id = b.id
LEFT JOIN vectors v ON d.id = v.id
WHERE b.id IS NOT NULL OR v.id IS NOT NULL
ORDER BY score DESC LIMIT 10;One query. One transaction. One result set.
Extension: TimescaleDB (21K+ GitHub stars)
What you're replacing: InfluxDB (separate database, Flux or limited SQL), Prometheus (metrics only, not application data).
What you get: Automatic time-based partitioning, compression up to 95%, continuous aggregates for fast dashboards, and full SQL. Your time-series data lives alongside your relational data with JOINs and ACID guarantees.
CREATE EXTENSION timescaledb;
CREATE TABLE metrics (
time TIMESTAMPTZ NOT NULL,
device_id TEXT,
temperature DOUBLE PRECISION
);
-- Convert to a hypertable (automatic time partitioning)
SELECT create_hypertable('metrics', 'time');
-- Query with time buckets
SELECT time_bucket('1 hour', time) AS hour,
AVG(temperature)
FROM metrics
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour;TimescaleDB handles retention and compression policies so you don't have to build cron jobs for data management.
-- Automatically drop data older than 30 days
SELECT add_retention_policy('metrics', INTERVAL '30 days');
-- Compress data older than 7 days (up to 95% storage reduction)
ALTER TABLE metrics SET (timescaledb.compress);
SELECT add_compression_policy('metrics', INTERVAL '7 days');Case study: Plexigrid went from 4 databases to 1 and got 350x faster queries.
Feature: UNLOGGED tables + JSONB (built into Postgres, no extension needed)
What you're replacing: Redis for simple key-value caching scenarios.
What you get: In-memory-speed storage without WAL overhead. Good for session data, temporary lookups, and simple caches. No separate service to operate.
When to keep Redis: If you need pub/sub, sorted sets, Lua scripting, or complex data structures, Redis is still the better tool for those specific jobs.
-- UNLOGGED = no WAL overhead, faster writes
CREATE UNLOGGED TABLE cache (
key TEXT PRIMARY KEY,
value JSONB,
expires_at TIMESTAMPTZ
);
-- Set with expiration
INSERT INTO cache (key, value, expires_at)
VALUES ('user:123', '{"name": "Alice"}', NOW() + INTERVAL '1 hour')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;
-- Get
SELECT value FROM cache
WHERE key = 'user:123' AND expires_at > NOW();
-- Schedule cleanup with pg_cron
SELECT cron.schedule('cache_cleanup', '0 * * * *',
$$DELETE FROM cache WHERE expires_at < NOW()$$);Extension: pgmq
What you're replacing: Kafka or RabbitMQ for task queues and simple event processing.
What you get: A lightweight message queue inside Postgres. Send, receive with visibility timeouts, and delete after processing. Transactional with the rest of your data.
When to keep Kafka: If you need high-throughput event streaming across dozens of services, consumer groups, exactly-once semantics, or multi-datacenter replication, Kafka is purpose-built for that.
CREATE EXTENSION pgmq;
SELECT pgmq.create('my_queue');
-- Send a message
SELECT pgmq.send('my_queue', '{"event": "signup", "user_id": 123}');
-- Receive (with 30-second visibility timeout)
SELECT * FROM pgmq.read('my_queue', 30, 5);
-- Delete after processing
SELECT pgmq.delete('my_queue', msg_id);For simple job queues, Postgres has a built-in pattern using FOR UPDATE SKIP LOCKED:
CREATE TABLE jobs (
id SERIAL PRIMARY KEY,
payload JSONB,
status TEXT DEFAULT 'pending'
);
-- Worker claims a job atomically
UPDATE jobs SET status = 'processing'
WHERE id = (
SELECT id FROM jobs WHERE status = 'pending'
FOR UPDATE SKIP LOCKED LIMIT 1
) RETURNING *;Feature: Native JSONB (built into Postgres since 2014)
What you're replacing: MongoDB for document storage.
What you get: Schemaless document storage with GIN indexing, plus everything Postgres gives you: ACID transactions, relational JOINs, and SQL. No separate database for your "document-shaped" data.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
data JSONB
);
-- Insert a nested document
INSERT INTO users (data) VALUES ('{
"name": "Alice",
"profile": {"bio": "Developer", "links": ["github.com/alice"]}
}');
-- Query nested fields
SELECT data->>'name', data->'profile'->>'bio'
FROM users
WHERE data->'profile'->>'bio' LIKE '%Developer%';
-- Index specific JSON fields for fast lookups
CREATE INDEX idx_users_email ON users ((data->>'email'));Extension: PostGIS (the industry standard since 2001)
What you're replacing: Nothing, really. PostGIS is what most specialized GIS tools are built on. It powers OpenStreetMap and has been in production for 24 years.
CREATE EXTENSION postgis;
CREATE TABLE stores (
id SERIAL PRIMARY KEY,
name TEXT,
location GEOGRAPHY(POINT, 4326)
);
-- Find stores within 5km
SELECT name,
ST_Distance(location, ST_MakePoint(-122.4, 37.78)::geography) AS meters
FROM stores
WHERE ST_DWithin(location, ST_MakePoint(-122.4, 37.78)::geography, 5000);Extension: pg_cron
What you're replacing: External cron jobs, Kubernetes CronJobs, or Lambda scheduled triggers for database maintenance tasks.
What you get: Cron scheduling inside Postgres. Useful for cache cleanup, materialized view refreshes, data retention, and periodic aggregation.
CREATE EXTENSION pg_cron;
-- Run cache cleanup every hour
SELECT cron.schedule('cleanup', '0 * * * *',
$$DELETE FROM cache WHERE expires_at < NOW()$$);
-- Refresh a materialized view every night at 2 AM
SELECT cron.schedule('rollup', '0 2 * * *',
$$REFRESH MATERIALIZED VIEW CONCURRENTLY daily_stats$$);Extension: pg_trgm (built into Postgres)
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON products USING GIN (name gin_trgm_ops);
-- Finds "PostgreSQL" even when typed as "posgresql"
SELECT name FROM products
WHERE name % 'posgresql'
ORDER BY similarity(name, 'posgresql') DESC;If you want the architectural argument for why consolidating on Postgres matters (especially in the AI era), read It's 2026, Just Use Postgres.
All of these extensions come pre-configured on Tiger Cloud. Create a free database and start building.
Further reading:
// Related posts
Why Postgres wins in the AI era: 40 years of reliability, extensibility, and an open ecosystem. Agents need trusted foundations, not fragile architectures.
By Ajay Kulkarni
September 1st, 2026
Time-series cardinality benchmark: one new indexed column costs 29× more than 1M new rows. Solve with metadata tables and avoid the planner estimation cliff.
By Damaso Sanoja
August 28th, 2026
Your slowest query is rarely your most expensive. See how pg_stat_statements ranks queries by total cost and exposes planning time that mean latency hides.
By NanoHertz Communications
August 7th, 2026
Receive the latest technical articles and release notes in your inbox.