Skip to content

Beyond Relational: Key-Value Caching, NoSQL, Queues & AI Vectors

PostgreSQL is often referred to as the "Swiss Army Knife" of data infrastructure. In modern engineering architectures, companies frequently introduce unnecessary operational complexity by deploying separate infrastructure for Redis (caching), MongoDB (documents), RabbitMQ (queues), Elasticsearch (search), and Pinecone (vector search).

This guide covers how to leverage PostgreSQL's native engine capabilities to replace these disparate services with enterprise-grade reliability, unified ACID transactions, and zero synchronization lag.

Part 1: The Key-Value (KV) Cache & Ephemeral Storage

1.1 UNLOGGED Tables as a High-Throughput Redis Alternative

Write-Ahead Logging (WAL) ensures durability across crashes, but WAL disk synchronization (fsync) is the primary write bottleneck. For caching, session storage, rate limiting, and ephemeral key-value data, you do not need crash durability.

Architecture of UNLOGGED Tables:

Standard Table Write:
Backend Process ---> shared_buffers ---> wal_buffers ---> Disk fsync (WAL) [SLOW]

UNLOGGED Table Write:
Backend Process ---> shared_buffers (Bypasses WAL entirely!) [ULTRA FAST]
* Performance: Achieves 5x to 10x higher write throughput with near-zero write latency. * Crash Semantics: If PostgreSQL crashes or restarts, all UNLOGGED tables are automatically truncated to zero rows on startup. The schema structure remains intact.

Production Session/Cache Schema Pattern:

CREATE UNLOGGED TABLE kv_session_cache (
    cache_key TEXT PRIMARY KEY,
    cache_value JSONB NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL
);

-- Fast Index for expiration pruning:
CREATE INDEX idx_kv_cache_expires ON kv_session_cache (expires_at);

-- Atomic "SET with TTL" (UPSERT):
INSERT INTO kv_session_cache (cache_key, cache_value, expires_at)
VALUES ('session:user:9812', '{"token": "xyz", "role": "admin"}', now() + interval '30 minutes')
ON CONFLICT (cache_key) 
DO UPDATE SET 
    cache_value = EXCLUDED.cache_value,
    expires_at = EXCLUDED.expires_at;

-- Atomic "GET":
SELECT cache_value 
FROM kv_session_cache 
WHERE cache_key = 'session:user:9812' AND expires_at > now();

1.2 pg_prewarm: Pinning Hot Lookup Tables in RAM

In Redis, all data is in memory. In Postgres, data blocks are read from disk on demand. If Postgres restarts, the cache is cold. The pg_prewarm extension forces tables and indexes to load directly into shared_buffers at boot:

CREATE EXTENSION IF NOT EXISTS pg_prewarm;

-- Load entire lookup table into shared_buffers immediately:
SELECT pg_prewarm('reference_country_codes');

-- Prewarm into OS Page Cache or Shared Memory:
SELECT pg_prewarm('products', 'buffer'); -- buffer (shared_buffers), read (OS cache)

1.3 Fast Concurrent Materialized Views

For complex analytical dashboard queries, caching at the database layer using Materialized Views eliminates repetitive expensive aggregations:

CREATE MATERIALIZED VIEW mv_hourly_sales_summary AS
SELECT 
    date_trunc('hour', created_at) AS sale_hour,
    count(*) AS total_orders,
    sum(amount) AS revenue
FROM orders
GROUP BY 1;

-- UNIQUE index is mandatory for concurrent refreshes without locking reads:
CREATE UNIQUE INDEX idx_mv_sales_hour ON mv_hourly_sales_summary (sale_hour);

-- Refresh the view in the background without blocking readers:
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_hourly_sales_summary;

Part 2: PostgreSQL as a NoSQL Document Database (JSONB)

2.1 The Binary Representation (JSON vs JSONB)

  • JSON stores text verbatim. Every read requires parsing the string into tokens.
  • JSONB decomposes data into a structured binary format at write time:
  • A 4-byte header indicates the container type (Object or Array) and total element count.
  • A sorted array of JEntry headers (each entry stores offset, length, and data type).
  • A contiguous buffer of sorted key strings, followed by values.
  • Why JSONB Keys are Sorted: Key search does not perform a linear scan; it uses binary search directly across the binary payload in \(O(\log N)\) time.

2.2 Deep Document Querying Operators

CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY,
    metadata JSONB NOT NULL
);

INSERT INTO user_profiles VALUES (1, '{
    "name": "Sarah",
    "tier": "enterprise",
    "preferences": {
        "notifications": {"email": true, "sms": false},
        "themes": ["dark", "compact"]
    },
    "tags": ["vip", "beta-tester"]
}');

Querying Patterns:

-- 1. Extract scalar as TEXT (->>)
SELECT metadata->>'name' FROM user_profiles;

-- 2. Extract nested scalar path (#>>)
SELECT metadata#>>'{preferences, notifications, email}' FROM user_profiles;

-- 3. Array element existence (?)
SELECT * FROM user_profiles WHERE metadata->'tags' ? 'vip';

-- 4. Deep Containment (@>): Matches any document containing this exact structure
SELECT * FROM user_profiles 
WHERE metadata @> '{"preferences": {"notifications": {"email": true}}}';

2.3 GIN Indexing: jsonb_ops vs jsonb_path_ops

  • jsonb_ops (Default): Indexes every individual key, every value, and every array element. Supports @>, ?, ?|, and ?&.
  • jsonb_path_ops: Hashes entire JSON paths (e.g., hash("preferences.notifications.email.true")).
  • 30% to 50% smaller index size.
  • 2x to 3x faster containment (@>) searches.
  • Limitation: Only supports the @> containment operator.
CREATE INDEX idx_profiles_fast_containment 
ON user_profiles USING gin (metadata jsonb_path_ops);

Part 3: Message Queues & Event Streaming

3.1 Asynchronous Pub/Sub (LISTEN / NOTIFY)

Postgres includes a built-in event bus. Any backend process can broadcast a notification payload (up to 8,000 bytes) to an arbitrary channel:

-- Client 1 (Worker Service):
LISTEN order_events;

-- Client 2 (Database Trigger or Web Backend):
NOTIFY order_events, '{"order_id": 9921, "status": "PAID"}';
* Ideal for cache invalidation (e.g., notifying API servers to evict in-memory Redis cache when a row changes) and real-time WebSocket fanouts.

3.2 High-Throughput Job Queues (FOR UPDATE SKIP LOCKED)

The traditional problem with building a queue in a relational database: Multiple worker processes query WHERE status = 'QUEUED' ORDER BY id LIMIT 1 FOR UPDATE. Every worker tries to lock the same top row, causing massive row lock contention and serialization deadlocks.

The SKIP LOCKED Magic:

SKIP LOCKED instructs the engine: "If another worker currently holds a lock on a matching row, do not wait—skip past it immediately and lock the next available row."

CREATE TABLE job_queue (
    job_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload JSONB NOT NULL,
    attempts INT DEFAULT 0,
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Worker Worker Thread: Fetch and lock 1 job exclusively:
WITH next_job AS (
    SELECT job_id 
    FROM job_queue
    WHERE status = 'pending'
    ORDER BY created_at ASC
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE job_queue q
SET status = 'processing', attempts = attempts + 1
FROM next_job
WHERE q.job_id = next_job.job_id
RETURNING q.job_id, q.payload;
* Throughput: Supports thousands of concurrent workers consuming tasks in parallel with zero deadlocks and zero lock wait latency.

Part 4: Advanced Search Engine Capabilities

4.1 Native Full-Text Search (FTS)

Postgres implements linguistic document search using specialized types: * tsvector: A sorted list of distinct normalized lexemes (words reduced to their root linguistic stem, stripping stop words like "the", "and", "is"). * tsquery: Search terms with boolean operators (& AND, | OR, ! NOT, <-> phrase proximity).

CREATE TABLE articles (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title TEXT,
    body TEXT,
    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body, '')), 'B')
    ) STORED
);

-- Inverted GIN Index:
CREATE INDEX idx_articles_fts ON articles USING gin (search_vector);

-- Search and Rank by Relevance:
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & (performance | tuning)') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

Part 5: AI & Vector Embeddings (pgvector)

5.1 Storing Vector Embeddings for LLMs

In Retrieval-Augmented Generation (RAG) pipelines, documents are transformed into multi-dimensional vectors (e.g., 1536 floating-point numbers).

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE knowledge_base (
    doc_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    content TEXT NOT NULL,
    embedding vector(1536) NOT NULL
);

5.2 HNSW (Hierarchical Navigable Small World) vs IVFFlat

Postgres supports two primary vector index algorithms:

IVFFlat:
- Divides vector space into K centroids (Voronoi cells).
- Inverted lists map cells to vectors.
- Fast build, low memory, but lower recall accuracy under heavy updates.

HNSW (Hierarchical Navigable Small World):
- Multi-layer graph network.
- Top layers skip vast distances; bottom layer refines nearest neighbor search.
- Lightning-fast sub-millisecond queries, high recall (99%+), but requires more RAM to build.

Production HNSW Index Creation:

CREATE INDEX idx_kb_hnsw ON knowledge_base 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
* m: Maximum number of bidirectional links per graph node (default 16). Higher = better recall, larger memory footprint. * ef_construction: Search queue size during graph building (default 64). Higher = slower build time, better graph connectivity.

Distance Operators:

  • <->: Euclidean distance (\(L_2\) distance)
  • <#>: Negative inner product
  • <=>: Cosine distance (most common for text embeddings)