Postgres is powerful enough to replace most specialized tools: full-text search (Elasticsearch), time-series storage (InfluxDB), pub/sub (Redis), and queues (RabbitMQ). Before adding infrastructure complexity, check if Postgres can do it.
Full-Text Search
Built-in full-text search handles most use cases without Elasticsearch:
-- Add a tsvector column for efficient search
ALTER TABLE articles ADD COLUMN search_vector tsvector;
-- Populate it
UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || body);
-- Create a GIN index for fast lookups
CREATE INDEX articles_search_idx ON articles USING GIN(search_vector);
-- Auto-update on insert/update
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);
-- Search query
SELECT id, title,
ts_rank(search_vector, query) AS rank
FROM articles,
plainto_tsquery('english', 'nextjs performance') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;
Queues with SKIP LOCKED
Postgres can function as a reliable job queue:
-- Queue table
CREATE TABLE jobs (
id BIGSERIAL PRIMARY KEY,
type TEXT NOT NULL,
payload JSONB NOT NULL,
status TEXT DEFAULT 'pending',
attempts INT DEFAULT 0,
scheduled_at TIMESTAMPTZ DEFAULT NOW(),
locked_at TIMESTAMPTZ,
locked_by TEXT
);
-- Claim the next job atomically
UPDATE jobs
SET status = 'processing',
locked_at = NOW(),
locked_by = $1 -- worker id
WHERE id = (
SELECT id FROM jobs
WHERE status = 'pending'
AND scheduled_at <= NOW()
ORDER BY scheduled_at
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING *;
SKIP LOCKED prevents multiple workers from claiming the same job.
JSONB for Flexible Data
-- Store arbitrary metadata without schema changes
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
type TEXT NOT NULL,
user_id UUID NOT NULL,
properties JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Index a specific JSONB field
CREATE INDEX events_plan_idx ON events ((properties->>'plan'));
-- Query inside JSONB
SELECT * FROM events
WHERE properties->>'plan' = 'pro'
AND properties->'metadata'->>'source' = 'stripe';
-- Aggregate JSONB values
SELECT properties->>'plan' AS plan,
COUNT(*) AS count
FROM events
WHERE type = 'subscription.created'
GROUP BY properties->>'plan';
Time-Series with TimescaleDB
For true time-series workloads, TimescaleDB extends Postgres:
-- Create a hypertable (automatically partitioned by time)
SELECT create_hypertable('metrics', 'time');
-- Continuous aggregation for dashboards
CREATE MATERIALIZED VIEW hourly_metrics
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
metric_name,
AVG(value) AS avg_value,
MAX(value) AS max_value
FROM metrics
GROUP BY bucket, metric_name;
Window Functions for Analytics
-- Month-over-month revenue change
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(amount) AS revenue,
LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', created_at)) AS prev_revenue,
ROUND(
(SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', created_at)))
/ LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', created_at)) * 100, 2
) AS growth_pct
FROM orders
GROUP BY month
ORDER BY month;
-- Running total
SELECT
created_at,
amount,
SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM payments;
Row-Level Security
Enforce multi-tenancy at the database level:
-- Enable RLS on a table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
-- Policy: users can only see their org's projects
CREATE POLICY org_isolation ON projects
USING (organization_id = current_setting('app.current_org_id')::uuid);
-- Set the org context before queries
SET app.current_org_id = 'org_123';
SELECT * FROM projects; -- only returns org_123's projects
The AI SaaS Starter at whoffagents.com uses Prisma over Postgres with migrations pre-configured. The schema includes JSONB event logging and RLS-ready organization isolation. $99 one-time.
Top comments (0)