PostgreSQL as Queue, Cache, and Vector Database: 5 Ways to Simplify Your Stack

The PostgreSQL Paradox

Here's something that sounds like a contradiction: the most popular database in the world is also the most underutilized.

If you're building something new in 2024, you're probably using PostgreSQL. Stack Overflow's developer survey confirms it year after year. But here's what most developers miss: you're likely using only 10% of what PostgreSQL can actually do.

Most teams treat PostgreSQL as a dumb data warehouse. Rows go in. Rows come out. But beneath that familiar SQL interface lies a Swiss Army knife of infrastructure primitives: queues, caches, vector search engines, key-value stores, and document databases. All native. All battle-tested. All accessible without spinning up a single additional service.

This isn't about clever hacks. It's about architectural first principles. When you're small, complexity is your enemy. Every new service you add: Redis for caching, SQS for queuing, Pinecone for vectors: introduces network latency, operational overhead, and failure modes you haven't considered yet.

The question isn't "Can PostgreSQL do this?" The question is "Should it?" And the answer, until you hit serious scale, is often yes.

Let me show you why.

---

Why Consolidation Beats Distribution (At Small Scale)

Before diving into specific features, let's establish the physics of the situation.

When you add a new service to your stack, you don't just add code. You add:

• Network hops: Every request now travels across VPCs or containers

• Connection pools: Another set of file descriptors to manage

• Serialization overhead: Data transforms between formats

• Operational surface: Backups, monitoring, security patches

• Cognitive load: Your team must understand another system's failure modes

PostgreSQL, running on the same machine or container as your application, sidesteps most of this. A local Unix socket connection to PostgreSQL has microsecond-level latency. A round-trip to ElastiCache has millisecond-level latency. At small scale, that difference doesn't matter. The simplicity does.

The trade-off? You're using a generalist tool for specialist jobs. PostgreSQL's queue implementation won't beat RabbitMQ at millions of messages per second. Its vector search won't beat a dedicated GPU-accelerated database. But "won't beat" doesn't mean "won't work." It means "works well enough until you have product-market fit and revenue to optimize."

With that framework, let's explore five ways to make PostgreSQL your entire backend infrastructure.

---

1. PostgreSQL as a Message Queue: The FOR UPDATE SKIP LOCKED Pattern

The First Principle: Atomic Visibility

Message queues solve a specific coordination problem: multiple workers need to consume tasks without colliding. Worker A grabs task #1. Worker B must see that task #1 is taken and grab task #2 instead.

Traditional wisdom says you need Kafka, RabbitMQ, or SQS for this. But the underlying primitive is simpler: atomic conditional visibility. You need a way to say "Show me rows that aren't locked, and lock the ones I pick in the same operation."

PostgreSQL has this built in.

The Implementation

Create a simple table:

CREATE TABLE job_queue (
    id SERIAL PRIMARY KEY,
    payload JSONB NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW(),
    processed_at TIMESTAMP
);

CREATE INDEX idx_status_created ON job_queue(status, created_at) 
WHERE status = 'pending';

Now for the magic. When a worker wants a job, it runs:

BEGIN;

SELECT * FROM job_queue 
WHERE status = 'pending' 
ORDER BY created_at ASC 
LIMIT 1 
FOR UPDATE SKIP LOCKED;

-- If we got a row:
UPDATE job_queue 
SET status = 'processing' 
WHERE id = ?;

COMMIT;

Why This Works (The Database Internals)

FOR UPDATE SKIP LOCKED is PostgreSQL's implementation of predicate locking with queue semantics. Here's what happens under the hood:

1. Predicate lock acquisition: PostgreSQL places a lock on rows matching your WHERE clause

2. Conflict resolution: If another transaction already holds a lock on a row, SKIP LOCKED tells PostgreSQL to pretend that row doesn't exist for this query

3. Atomic visibility: The lock and the visibility check happen in the same MVCC snapshot, making race conditions impossible

This isn't a hack. It's a first-class concurrency primitive that PostgreSQL's query planner optimizes specifically for queue patterns. The WHERE status = 'pending' clause combined with the partial index ensures PostgreSQL uses an index scan rather than a sequential table scan, even with millions of rows.

The PGMQ Alternative

If you want queue semantics without writing SQL, the pgmq extension wraps this pattern in a friendly API:

-- After: CREATE EXTENSION pgmq;
SELECT pgmq.send('my_queue', '{"user_id": 123, "action": "send_email"}');
SELECT * FROM pgmq.read('my_queue', 5, 30); -- 5 messages, 30s visibility timeout

PGMQ adds features like visibility timeouts (automatic retry if a worker dies), dead letter queues, and exactly-once semantics. But underneath, it's the same FOR UPDATE SKIP LOCKED mechanism.

What to Avoid: LISTEN/NOTIFY

PostgreSQL has a pub/sub system: LISTEN and NOTIFY. It seems perfect for real-time job distribution. Do not use it for queues.

LISTEN/NOTIFY operates outside PostgreSQL's transaction system. If your worker crashes after receiving a notification but before processing the job, that job is lost. There's no persistence, no retry, no dead letter queue. At even moderate load, connection overhead and notification buffering create bottlenecks that SKIP LOCKED avoids entirely.

Stick to polling with SKIP LOCKED. Modern PostgreSQL on SSDs can handle tens of thousands of these queries per second. Your startup doesn't need more.

---

2. Unlogged Tables: Volatile State Without WAL Overhead

The First Principle: Durability is Expensive

PostgreSQL's durability guarantee comes from the Write-Ahead Log (WAL). Every modification hits disk twice: once to the WAL (sequential, fast), once to the actual table pages (random, slower). This is the price of ACID.

But not all data deserves durability. Cache entries can be rebuilt. Rate limit counters can reset. Session state can evaporate. For this data, double-write durability is pure overhead.

The Solution: UNLOGGED

CREATE UNLOGGED TABLE cache_store (
    key TEXT PRIMARY KEY,
    value BYTEA,
    expires_at TIMESTAMP
);

CREATE INDEX idx_expires ON cache_store(expires_at) 
WHERE expires_at < NOW();

Unlogged tables bypass the WAL entirely. Writes go directly to heap pages. If PostgreSQL crashes, these tables truncate automatically. They're empty on restart.

Performance Characteristics

Benchmarks consistently show 3-5x faster writes for unlogged tables versus logged tables. For read-heavy cache workloads, performance is identical: both use the same buffer pool and indexing structures.

Use Cases

Caching: Store expensive query results, rendered templates, or API responses. Set expires_at and have a background job clean up:

DELETE FROM cache_store WHERE expires_at < NOW();

Rate Limiting: Track request counts per IP or user ID. The data is transient by definition: if you lose it, worst case you allow slightly more traffic than intended.

INSERT INTO rate_limits (key, count, window_start) 
VALUES ('ip:192.168.1.1', 1, NOW())
ON CONFLICT (key) DO UPDATE 
SET count = rate_limits.count + 1;

The Redis Comparison

Redis persists to disk (RDB snapshots, AOF logs) and offers complex data structures (sorted sets, hyperloglogs). Unlogged tables don't compete with that.

But if you're using Redis for simple string caching or basic counters, unlogged tables offer:

• Transactional consistency: Update cache and business data in the same ACID transaction

• No network latency: Local Unix socket vs. TCP round-trip

• Simpler operations: One backup strategy, one monitoring dashboard, one security model

At sub-50,000 DAU scale, the latency difference is in microseconds. The operational simplicity difference is massive.

---

3. pgvector: Semantic Search Without the Infrastructure

The First Principle: Embeddings are Just Points in Space

Modern AI applications rely on embeddings: numerical representations of text, images, or concepts where "semantic similarity" becomes "geometric distance." An embedding for "king" minus "man" plus "woman" lands near "queen." This isn't magic; it's linear algebra in high-dimensional space.

Vector databases like Pinecone or Weaviate specialize in fast nearest-neighbor search across millions of these high-dimensional points. But the core operation: "Find me the 10 closest vectors to this one" is something PostgreSQL can do natively with pgvector.

The Implementation

CREATE EXTENSION vector;

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536) -- OpenAI's text-embedding-3-small dimension
);

CREATE INDEX ON documents 
USING ivfflat (embedding vector_cosine_ops) 
WITH (lists = 100);

Query for similar documents:

SELECT id, content, 1 - (embedding <=> query_embedding) AS similarity
FROM documents
ORDER BY embedding <=> query_embedding
LIMIT 10;

The <=> operator computes cosine distance. The ivfflat index uses inverted file indexing: it partitions the vector space into clusters, searches the most promising clusters, and returns approximate nearest neighbors.

When This Works

pgvector shines in three scenarios:

1. Prototyping: You're building an AI feature and need semantic search without vendor lock-in

2. Moderate scale: Thousands to hundreds of thousands of vectors, not millions

3. Hybrid queries: You need to combine vector similarity with traditional filtering ("Find documents similar to this query, but only in the 'engineering' category")

The ivfflat index trades recall (finding truly closest neighbors) for speed. For most applications, 95% recall is indistinguishable from 100%. If you need exact search, pgvector supports that too: just omit the index and accept sequential scan speeds.

When to Upgrade

If you're doing >1000 vector queries per second, or have >10 million vectors, dedicated vector databases justify their cost. Their GPU acceleration and optimized memory layouts outperform PostgreSQL. But for internal tools, MVPs, or features with modest traffic, pgvector eliminates an entire service from your architecture.

---

4. Native Key-Value Storage: The Table You Already Have

The First Principle: Abstraction Over Implementation

Key-value stores optimize for specific access patterns: single-key lookups, high read concurrency, horizontal sharding. But at small scale, these optimizations don't matter. What matters is the abstraction: "Give me a string, I'll give you a value."

PostgreSQL handles this trivially:

CREATE TABLE kv_store (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_kv_expires ON kv_store(expires_at) 
WHERE expires_at IS NOT NULL;

Wrap it in your application's data layer:

def kv_get(key: str) -> Optional[str]:
    row = db.execute(
        "SELECT value FROM kv_store WHERE key = %s AND (expires_at > NOW() OR expires_at IS NULL)",
        (key,)
    ).fetchone()
    return row[0] if row else None

def kv_set(key: str, value: str, ttl_seconds: Optional[int] = None):
    expires = datetime.now() + timedelta(seconds=ttl_seconds) if ttl_seconds else None
    db.execute(
        """INSERT INTO kv_store (key, value, expires_at) 
           VALUES (%s, %s, %s)
           ON CONFLICT (key) DO UPDATE 
           SET value = EXCLUDED.value, expires_at = EXCLUDED.expires_at""",
        (key, value, expires)
    )

Why This Isn't Crazy

Redis persists to disk (RDB/AOF). It offers TTL expiration. It handles millions of keys. But for a few thousand keys with moderate access patterns, PostgreSQL's buffer cache keeps hot data in memory just as Redis does. The difference is:

• Query flexibility: Need to find all keys matching a pattern? SELECT key FROM kv_store WHERE key LIKE 'user:%'

• Atomicity: Update KV state and relational state in one transaction

• Durability options: Use regular tables for critical data, unlogged tables for ephemeral cache

The 50,000 DAU Rule of Thumb

Throughput scales with hardware, but here's a practical heuristic: if you have fewer than 50,000 daily active users, and your application isn't write-heavy (social feeds, real-time gaming), a single properly-tuned PostgreSQL instance can handle your entire workload: relational queries, caching, queuing, and search.

This isn't a hard limit. It's a sanity check. Beyond this, start measuring. If your p99 query latency climbs above 100ms, or your replication lag grows, it's time to specialize.

---

5. JSONB: When Schema Flexibility Meets SQL Power

The First Principle: Documents are Just Denormalized Relations

MongoDB popularized the document model: store related data together, avoid joins, embrace schema flexibility. But documents are just a specific case of the relational model: a table with one column containing structured data.

PostgreSQL's JSONB type provides this without sacrificing SQL's query power.

The Implementation

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(50),
    payload JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Index specific JSON paths for fast filtering
CREATE INDEX idx_payload_user ON events ((payload->>'user_id'));
CREATE INDEX idx_payload_tags ON events USING GIN ((payload->'tags'));

Insert flexible documents:

INSERT INTO events (event_type, payload) VALUES (
    'purchase',
    '{"user_id": "123", "amount": 99.99, "items": [{"id": 1, "name": "Widget"}], "tags": ["new_customer", "mobile"]}'
);

Query with SQL precision:

-- Find purchases over $50 by new customers
SELECT * FROM events 
WHERE event_type = 'purchase' 
  AND (payload->>'amount')::numeric > 50
  AND payload->'tags' ? 'new_customer';

The GIN Index Advantage

Generalized Inverted Indexes (GIN) make JSONB queries fast. They index every key and value within your JSON documents, allowing queries like "Find events where any tag is 'urgent'" to use an index rather than scanning every row.

When to Use JSONB vs. Normalized Tables

Use JSONB when:

• Schema evolves frequently (event logging, external API responses)

• Data is hierarchical and self-contained (a purchase with line items)

• You need PostgreSQL's reliability but MongoDB's flexibility

Use normalized tables when:

• You query across relationships frequently (users and their orders)

• Data integrity requires foreign keys

• You aggregate across large datasets (JSONB aggregation is slower than columnar aggregation)

The Migration Path

Many teams start with JSONB for velocity, then normalize as patterns emerge:

-- Extract frequently-queried fields to proper columns
ALTER TABLE events ADD COLUMN user_id INTEGER;
UPDATE events SET user_id = (payload->>'user_id')::int;
CREATE INDEX idx_user_id ON events(user_id);

-- Now you can join with your users table
SELECT e.*, u.email 
FROM events e 
JOIN users u ON e.user_id = u.id;

This hybrid approach: schema flexibility where you need it, structure where it matters: is something pure document databases struggle to provide.

---

The Honest Drawbacks: When to Stop Using PostgreSQL for Everything

I've argued for PostgreSQL as a universal tool. But universal tools have limits. Here are the failure modes:

1. Write Amplification

Every feature we've discussed adds write load. Unlogged tables help, but if you're doing thousands of writes per second across queues, caches, and KV stores, WAL volume becomes a bottleneck. Streaming replication lag increases. Backups grow slow.

The fix: When write volume exceeds your IOPS budget, split the workload. Move caching to Redis. Move queues to SQS.

2. Connection Limits

PostgreSQL creates a process per connection. Each process consumes memory. At a few hundred concurrent connections, context switching overhead degrades performance. PgBalle or PgPool help, but they're band-aids.

The fix: When you need thousands of concurrent clients, use connection pooling middleware or specialized services designed for massive concurrency.

3. Vector Search at Scale

pgvector's ivfflat index builds slowly and queries degrade as dimensionality grows. At millions of vectors, you'll wait seconds for index builds and tolerate lower recall than dedicated vector databases offer.

The fix: When vector search becomes core to your product, migrate to Pinecone, Weaviate, or a GPU-accelerated solution.

4. Operational Complexity of Extensions

Extensions like pgmq or pgvector require installation, updates, and compatibility management across PostgreSQL versions. They add operational surface area.

The fix: For teams without DBA expertise, managed PostgreSQL (AWS RDS, Google Cloud SQL) simplifies this, but verify your extensions are supported.

---

The Strategic Advantage: Optionality

Here's the meta-point: starting with PostgreSQL for everything isn't about avoiding growth. It's about preserving optionality.

When you use SQS from day one, you're optimizing for a scale you don't have, while locking yourself into AWS's pricing and APIs. When you use PostgreSQL as your queue, you can migrate to SQS later: your application logic treats it as a queue abstraction, not a specific technology.

Every specialized service you defer is a decision you can make with better information later. You'll know your actual query patterns, your actual scale, your actual latency requirements. You'll make better infrastructure choices because they'll be grounded in observed behavior, not predicted needs.

PostgreSQL's versatility lets you stay in this "informed procrastination" state longer. That's not technical debt. That's strategic patience.

---

Conclusion: The Database That Grows With You

PostgreSQL isn't just a database. It's a queue system, a cache, a search engine, a document store, and a key-value database. None of these implementations are best-in-class. All of them are good enough for most applications, most of the time.

The pattern isn't "Use PostgreSQL forever." It's "Use PostgreSQL until you know why you shouldn't." Until you have metrics showing Redis would be 10x faster, or SQS would reduce operational load, or a vector database would improve search quality.

Start simple. Stay simple. Let complexity earn its way into your stack.

Your future self, debugging a production incident at 3 AM, will thank you for having one less service to check.nnability and reference value that blog readers expect.