Postgres as More Than a Database

One Database, Five Fewer Services

Every additional service in your stack adds operational cost: monitoring, backups, failover, connection management, upgrades, and on-call expertise. A Redis here, an Elasticsearch there, a RabbitMQ over there, and suddenly your infrastructure diagram looks like a subway map.

PostgreSQL can replace many of these specialized services. Not all of them, and not in every scenario, but for a surprising number of use cases, the built-in feature or a well-maintained extension eliminates an entire category of infrastructure. The question is not "can Postgres do this?" but "is Postgres good enough at this for my workload?"

Job Queue with SKIP LOCKED

Instead of adding Redis, SQS, or a dedicated job queue system, Postgres can serve as a reliable, transactional job queue using FOR UPDATE SKIP LOCKED.

CREATE TABLE jobs (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    queue text NOT NULL DEFAULT 'default',
    payload jsonb NOT NULL,
    status text NOT NULL DEFAULT 'pending',
    attempts int NOT NULL DEFAULT 0,
    max_attempts int NOT NULL DEFAULT 3,
    scheduled_at timestamptz NOT NULL DEFAULT now(),
    locked_at timestamptz,
    completed_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_jobs_pending ON jobs (scheduled_at)
    WHERE status = 'pending';

Workers claim jobs atomically:

-- Worker claims the next available job
UPDATE jobs SET
    status = 'processing',
    locked_at = now(),
    attempts = attempts + 1
WHERE id = (
    SELECT id FROM jobs
    WHERE status = 'pending'
      AND scheduled_at <= now()
      AND attempts < max_attempts
    ORDER BY scheduled_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
RETURNING *;

SKIP LOCKED is the key. Multiple workers can run this query concurrently and each one gets a different job. No duplicates, no race conditions, no external coordination. The database handles it.

-- Mark job as completed
UPDATE jobs SET status = 'completed', completed_at = now() WHERE id = 42;

-- Mark job as failed (will be retried if under max_attempts)
UPDATE jobs SET status = 'pending', locked_at = NULL WHERE id = 42;

When to use a dedicated queue instead: When you need millions of messages per second, complex routing, fan-out to multiple consumers, or message replay. Kafka and RabbitMQ exist for these workloads.

Pub/Sub with LISTEN & NOTIFY

Postgres has built-in publish/subscribe messaging. No external message broker required.

-- Subscriber (in one connection)
LISTEN order_events;

-- Publisher (in another connection)
NOTIFY order_events, '{"order_id": 42, "status": "shipped"}';

The subscriber receives the notification in real time. This works across connections within the same Postgres cluster.

-- Trigger-based notification on table changes
CREATE OR REPLACE FUNCTION notify_order_change() RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('order_events', json_build_object(
        'operation', TG_OP,
        'order_id', NEW.id,
        'status', NEW.status
    )::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_notify
    AFTER INSERT OR UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION notify_order_change();

Application code listens for notifications:

-- In your application's database connection
LISTEN order_events;
-- Then poll with: SELECT * FROM pg_notification_queue();
-- Or use your driver's async notification support

Limitations: LISTEN/NOTIFY does not persist messages. If no one is listening, the notification is lost. There is no message queue, no replay, and no guaranteed delivery. It is fire-and-forget. For durable messaging, use a dedicated message broker or the SKIP LOCKED job queue pattern.

Caching with UNLOGGED Tables

UNLOGGED tables skip WAL writes, making them significantly faster for writes and reads. The trade-off: data is lost on crash or failover. This makes them perfect for caching.

-- Cache that survives normal restarts but not crashes
CREATE UNLOGGED TABLE cache (
    key text PRIMARY KEY,
    value jsonb NOT NULL,
    expires_at timestamptz NOT NULL
);

-- Upsert a cache entry
INSERT INTO cache (key, value, expires_at)
VALUES ('user:42:profile', '{"name": "Alice"}', now() + interval '1 hour')
ON CONFLICT (key) DO UPDATE SET
    value = EXCLUDED.value,
    expires_at = EXCLUDED.expires_at;

-- Read from cache
SELECT value FROM cache
WHERE key = 'user:42:profile' AND expires_at > now();

-- Expire old entries periodically
DELETE FROM cache WHERE expires_at < now();

UNLOGGED tables are 2-5x faster than regular tables for writes because they skip WAL. They are replicated to standbys as empty tables (the data exists only on the primary).

When to use Redis instead: When you need sub-millisecond reads, built-in TTL, pub/sub, sorted sets, or a cache that is shared across multiple application instances connecting to different database servers.

Full-Text Search

Postgres has built-in full-text search with stemming, ranking, and phrase matching. No Elasticsearch required for most use cases.

-- Add a search vector with automatic maintenance
ALTER TABLE articles ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body, '')), 'B')
    ) STORED;

CREATE INDEX idx_articles_fts ON articles USING gin(search_vector);

-- Search with ranking
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, websearch_to_tsquery('english', 'postgres performance tuning') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

Vector Search with pgvector

pgvector turns Postgres into a vector database for AI/ML workloads:

CREATE EXTENSION vector;

CREATE TABLE embeddings (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    content text NOT NULL,
    embedding vector(1536)
);

CREATE INDEX ON embeddings USING hnsw (embedding vector_cosine_ops);

-- Semantic search: find similar content
SELECT content, embedding <=> $1 AS distance
FROM embeddings
ORDER BY embedding <=> $1
LIMIT 5;

When to use a dedicated vector database: When you need billions of vectors, distributed search across a cluster, or specialized filtering that pgvector does not yet support efficiently.

JSON Document Store

JSONB turns Postgres into a document database with the added benefit of relational features:

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_type text NOT NULL,
    data jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- GIN index for fast JSON queries
CREATE INDEX idx_events_data ON events USING gin(data);

-- Query nested JSON
SELECT data->>'user_id' AS user_id, data->>'action' AS action
FROM events
WHERE data @> '{"source": "mobile"}' AND event_type = 'page_view';

-- JSON aggregation
SELECT data->>'page' AS page, count(*) AS views
FROM events
WHERE event_type = 'page_view'
  AND created_at > now() - interval '24 hours'
GROUP BY data->>'page'
ORDER BY views DESC;

You get document flexibility with relational guarantees: transactions, joins, constraints, and SQL queries. MongoDB trades those guarantees for a different query language and operational model.

Time Series with Partitioning

Native partitioning handles time-series data effectively:

CREATE TABLE metrics (
    time timestamptz NOT NULL,
    device_id int NOT NULL,
    value double precision NOT NULL
) PARTITION BY RANGE (time);

-- Monthly partitions
CREATE TABLE metrics_2026_01 PARTITION OF metrics
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE metrics_2026_02 PARTITION OF metrics
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE metrics_2026_03 PARTITION OF metrics
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- BRIN index for time-ordered data (tiny, fast)
CREATE INDEX ON metrics USING brin(time);

-- Time-bucket aggregation
SELECT date_trunc('hour', time) AS hour,
       avg(value) AS avg_value,
       max(value) AS max_value
FROM metrics
WHERE device_id = 7 AND time > now() - interval '7 days'
GROUP BY hour
ORDER BY hour;

-- Drop old data by detaching partitions (instant, no vacuum needed)
ALTER TABLE metrics DETACH PARTITION metrics_2026_01;
DROP TABLE metrics_2026_01;

For more advanced time-series features (continuous aggregates, compression, automated retention), add TimescaleDB.

Geospatial with PostGIS

PostGIS makes Postgres a full geospatial database:

CREATE EXTENSION postgis;

-- Find all restaurants within 1 km
SELECT name, ST_Distance(location::geography, ST_MakePoint(-73.98, 40.75)::geography) AS dist
FROM restaurants
WHERE ST_DWithin(location::geography, ST_MakePoint(-73.98, 40.75)::geography, 1000)
ORDER BY dist;

Deciding When to Use the Built-in Feature vs a Dedicated Tool

The general rule: start with Postgres. Add a dedicated tool only when you have measured evidence that Postgres cannot handle the workload. Most teams never reach that point.

Common Pitfalls

• Premature infrastructure complexity. Adding Redis "because we might need it" before testing whether Postgres handles the workload adds operational cost with no proven benefit.
• Ignoring LISTEN/NOTIFY limitations. Notifications are not persisted. If your subscriber disconnects, messages are lost. Do not use LISTEN/NOTIFY for critical workflows that need guaranteed delivery.
• Forgetting UNLOGGED table data loss behavior. After a crash, UNLOGGED tables are truncated. If you put important data in an UNLOGGED table, you will lose it.
• Not indexing JSONB columns. JSONB without a GIN index means sequential scans on every query. The flexibility of JSON does not excuse skipping indexes.
• Overloading a single Postgres instance. Postgres can replace five tools, but each additional workload consumes resources. Monitor CPU, memory, and I/O to ensure you are not overloading the server.
• Treating Postgres as a message broker. SKIP LOCKED is a job queue, not a message broker. It does not support topics, subscriptions, or consumer groups. Know the difference.

Key Takeaways

• Postgres can serve as a job queue (SKIP LOCKED), pub/sub system (LISTEN/NOTIFY), cache (UNLOGGED tables), search engine (FTS), vector database (pgvector), document store (JSONB), time-series database (partitioning), and geospatial database (PostGIS).
• Each built-in feature has clear limits. Know when the workload exceeds what Postgres handles well and add a dedicated tool at that point.
• The operational cost of each additional service (monitoring, backups, failover, expertise) is real. Consolidating on Postgres where possible reduces that cost.
• Start with Postgres for everything. Specialize only when you have measured evidence that a dedicated tool is necessary.
• The "just use Postgres" philosophy is not about avoiding better tools. It is about avoiding premature complexity.