PostgreSQL: The Swiss Army Knife of Modern Databases

Discover how PostgreSQL can replace your entire tech stack and why it's the go-to database for modern startups

PostgreSQL: The Swiss Army Knife of Modern Databases

When you think of PostgreSQL, you probably think "reliable database." But what if I told you that PostgreSQL can replace your message queue, cache layer, vector database, key-value store, and more? In this comprehensive guide, we'll explore how PostgreSQL has evolved into a versatile powerhouse that can significantly simplify your tech stack.

Why Consolidate Your Stack with PostgreSQL?

Before diving into the technical details, let's address the fundamental question: Why use PostgreSQL for more than just a database?

The answer lies in scale and complexity management. If you're operating at a smaller scale—say, fewer than 50,000 daily active users—you can move significantly faster by consolidating services into PostgreSQL rather than managing ten different systems like AWS SQS for queuing, Redis for caching, and separate vector databases for AI workloads.

The Trade-offs

Every architectural decision involves trade-offs. While PostgreSQL won't match the raw performance of specialized systems at massive scale, the operational simplicity it provides is invaluable for:

Startups building their MVP
Small to medium teams focusing on rapid iteration
Projects with moderate concurrent users (under 50K DAU)
Internal tools with predictable workloads

1. PostgreSQL as a Message Queue

One of PostgreSQL's most underappreciated features is its ability to function as a reliable message queue. This capability can eliminate the need for external queuing systems like AWS SQS or RabbitMQ in many scenarios.

The Core Concept

At its heart, a queue is just a table with rows representing jobs or messages. PostgreSQL provides native features that make this not just possible, but elegant and efficient.

-- Create a basic job queue 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,
    error_message TEXT,
    retry_count INTEGER DEFAULT 0
);
 
-- Add an index for efficient querying
CREATE INDEX idx_job_queue_status ON job_queue(status);
CREATE INDEX idx_job_queue_created ON job_queue(created_at);

The Magic: FOR UPDATE SKIP LOCKED

The critical feature that makes PostgreSQL queuing work reliably with multiple consumers is the FOR UPDATE SKIP LOCKED clause. This ensures that concurrent workers don't process the same job twice.

-- Worker query to fetch the next available job
BEGIN;
 
SELECT id, payload
FROM job_queue
WHERE status = 'pending'
ORDER BY created_at ASC
LIMIT 1
FOR UPDATE SKIP LOCKED;
 
-- Update the status to processing
UPDATE job_queue
SET status = 'processing',
    processed_at = NOW()
WHERE id = <selected_id>;
 
COMMIT;

How It Works

When multiple workers simultaneously query for pending jobs:

Each worker issues a SELECT ... FOR UPDATE SKIP LOCKED query
PostgreSQL locks the first available row for the first worker
Other workers automatically skip locked rows and grab the next available job
No race conditions, no duplicate processing—guaranteed by PostgreSQL's transaction system

Implementation Example

Here's a practical Node.js implementation:

const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
 
class PostgresQueue {
  async enqueue(jobType, payload) {
    const query = `
      INSERT INTO job_queue (job_type, payload, status)
      VALUES ($1, $2, 'pending')
      RETURNING id
    `;
    const result = await pool.query(query, [jobType, JSON.stringify(payload)]);
    return result.rows[0].id;
  }
 
  async dequeue() {
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      
      // Fetch and lock the next job
      const fetchQuery = `
        SELECT id, job_type, payload
        FROM job_queue
        WHERE status = 'pending'
        ORDER BY created_at ASC
        LIMIT 1
        FOR UPDATE SKIP LOCKED
      `;
      const result = await client.query(fetchQuery);
      
      if (result.rows.length === 0) {
        await client.query('COMMIT');
        return null;
      }
      
      const job = result.rows[0];
      
      // Mark as processing
      await client.query(
        `UPDATE job_queue SET status = 'processing', processed_at = NOW() WHERE id = $1`,
        [job.id]
      );
      
      await client.query('COMMIT');
      return job;
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }
 
  async markComplete(jobId) {
    await pool.query(
      `UPDATE job_queue SET status = 'completed' WHERE id = $1`,
      [jobId]
    );
  }
 
  async markFailed(jobId, errorMessage) {
    await pool.query(
      `UPDATE job_queue 
       SET status = 'failed', 
           error_message = $2,
           retry_count = retry_count + 1 
       WHERE id = $1`,
      [jobId, errorMessage]
    );
  }
}
 
// Worker implementation
async function worker() {
  const queue = new PostgresQueue();
  
  while (true) {
    const job = await queue.dequeue();
    
    if (!job) {
      await new Promise(resolve => setTimeout(resolve, 3000)); // Poll every 3 seconds
      continue;
    }
    
    try {
      // Process the job
      await processJob(job);
      await queue.markComplete(job.id);
    } catch (error) {
      await queue.markFailed(job.id, error.message);
    }
  }
}

Advanced: Using PGMQ Extension

For production use cases, consider the PGMQ extension, which builds on these PostgreSQL primitives to provide a lightweight message queue with additional features:

-- Install PGMQ extension
CREATE EXTENSION pgmq;
 
-- Create a queue
SELECT pgmq.create('my_queue');
 
-- Send a message
SELECT pgmq.send('my_queue', '{"task": "send_email", "to": "user@example.com"}');
 
-- Read a message (with visibility timeout)
SELECT * FROM pgmq.read('my_queue', 30, 1); -- 30 second timeout, 1 message

⚠️ Avoid LISTEN/NOTIFY for Queuing

PostgreSQL's LISTEN/NOTIFY feature might seem like a natural fit for queuing, but it has significant limitations:

Not persistent: Messages are lost if no listener is connected
Performance bottlenecks: Struggles with high message throughput
No delivery guarantees: Messages can be dropped under load

Stick with table-based queuing for reliable job processing.

2. UNLOGGED Tables: Built-in Caching and Rate Limiting

PostgreSQL's UNLOGGED tables are a hidden gem that can replace Redis for specific use cases like caching and rate limiting.

What Are UNLOGGED Tables?

An unlogged table skips the Write-Ahead Log (WAL), which is PostgreSQL's durability mechanism. This means:

✅ Significantly faster writes (no WAL overhead)
✅ Perfect for ephemeral data
❌ Data loss possible on crashes (acceptable for cache/rate limiting)
❌ Not replicated to standby servers

-- Create an unlogged cache table
CREATE UNLOGGED TABLE cache_store (
    cache_key VARCHAR(255) PRIMARY KEY,
    cache_value TEXT,
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);
 
-- Add index for expiration cleanup
CREATE INDEX idx_cache_expires ON cache_store(expires_at);

Cache Implementation

class PostgresCache {
  async get(key) {
    const result = await pool.query(
      `SELECT cache_value 
       FROM cache_store 
       WHERE cache_key = $1 
         AND (expires_at IS NULL OR expires_at > NOW())`,
      [key]
    );
    
    if (result.rows.length === 0) return null;
    return JSON.parse(result.rows[0].cache_value);
  }
 
  async set(key, value, ttlSeconds = 3600) {
    const expiresAt = ttlSeconds 
      ? new Date(Date.now() + ttlSeconds * 1000) 
      : null;
    
    await pool.query(
      `INSERT INTO cache_store (cache_key, cache_value, expires_at)
       VALUES ($1, $2, $3)
       ON CONFLICT (cache_key) 
       DO UPDATE SET cache_value = $2, expires_at = $3, created_at = NOW()`,
      [key, JSON.stringify(value), expiresAt]
    );
  }
 
  async delete(key) {
    await pool.query('DELETE FROM cache_store WHERE cache_key = $1', [key]);
  }
 
  // Cleanup expired entries (run periodically)
  async cleanup() {
    await pool.query('DELETE FROM cache_store WHERE expires_at < NOW()');
  }
}

Rate Limiting with UNLOGGED Tables

-- Create rate limiting table
CREATE UNLOGGED TABLE rate_limits (
    identifier VARCHAR(255) PRIMARY KEY,  -- user_id, ip_address, api_key
    request_count INTEGER DEFAULT 0,
    window_start TIMESTAMP DEFAULT NOW()
);
 
CREATE INDEX idx_rate_limit_window ON rate_limits(window_start);

class RateLimiter {
  async checkLimit(identifier, maxRequests, windowSeconds) {
    const client = await pool.connect();
    
    try {
      await client.query('BEGIN');
      
      const result = await client.query(
        `SELECT request_count, window_start 
         FROM rate_limits 
         WHERE identifier = $1
         FOR UPDATE`,
        [identifier]
      );
      
      const now = new Date();
      const windowStart = result.rows.length > 0 
        ? new Date(result.rows[0].window_start) 
        : now;
      const requestCount = result.rows.length > 0 
        ? result.rows[0].request_count 
        : 0;
      
      // Check if we need to reset the window
      const windowElapsed = (now - windowStart) / 1000;
      
      if (windowElapsed > windowSeconds) {
        // Reset window
        await client.query(
          `INSERT INTO rate_limits (identifier, request_count, window_start)
           VALUES ($1, 1, $2)
           ON CONFLICT (identifier)
           DO UPDATE SET request_count = 1, window_start = $2`,
          [identifier, now]
        );
        await client.query('COMMIT');
        return { allowed: true, remaining: maxRequests - 1 };
      }
      
      // Check limit
      if (requestCount >= maxRequests) {
        await client.query('COMMIT');
        const resetIn = windowSeconds - windowElapsed;
        return { allowed: false, resetIn };
      }
      
      // Increment counter
      await client.query(
        `UPDATE rate_limits 
         SET request_count = request_count + 1 
         WHERE identifier = $1`,
        [identifier]
      );
      
      await client.query('COMMIT');
      return { allowed: true, remaining: maxRequests - requestCount - 1 };
      
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }
}

Performance Considerations

While UNLOGGED tables won't match Redis's raw speed (expect 1-2ms additional latency), they offer significant operational benefits:

One less system to manage
No separate connection pooling
Unified security model
Built-in ACID transactions

For most small-to-medium applications, this trade-off is well worth it.

3. Vector Embeddings with pgvector

AI and machine learning applications often require vector similarity search. PostgreSQL's pgvector extension brings this capability directly into your database.

What is pgvector?

The pgvector extension adds vector data types and similarity search capabilities to PostgreSQL, enabling:

Semantic search based on meaning, not keywords
Recommendation systems using content similarity
AI-powered features without separate vector databases

-- Install pgvector extension
CREATE EXTENSION vector;
 
-- Create a table with vector embeddings
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    embedding vector(1536),  -- OpenAI embedding dimension
    created_at TIMESTAMP DEFAULT NOW()
);
 
-- Create an index for fast similarity search (HNSW is fastest)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

Understanding Vector Embeddings

Think of embeddings as coordinates in high-dimensional space where semantically similar concepts are located closer together:

Dimension 1 (Programming Difficulty) →
│
│     JavaScript ●
│                    
│                         ● Rust
│              ● Python
│
Dimension 2 (Execution Speed) ↓

While real embeddings use hundreds or thousands of dimensions, the principle remains the same: similarity is measured by distance.

Practical Implementation

const { OpenAI } = require('openai');
const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });
 
class VectorSearch {
  async addDocument(title, content) {
    // Generate embedding using OpenAI
    const response = await openai.embeddings.create({
      model: "text-embedding-3-small",
      input: content,
    });
    
    const embedding = response.data[0].embedding;
    
    // Store in PostgreSQL
    const result = await pool.query(
      `INSERT INTO documents (title, content, embedding)
       VALUES ($1, $2, $3)
       RETURNING id`,
      [title, content, `[${embedding.join(',')}]`]
    );
    
    return result.rows[0].id;
  }
 
  async semanticSearch(query, limit = 10) {
    // Generate embedding for the query
    const response = await openai.embeddings.create({
      model: "text-embedding-3-small",
      input: query,
    });
    
    const queryEmbedding = response.data[0].embedding;
    
    // Search for similar documents
    const result = await pool.query(
      `SELECT 
         id, 
         title, 
         content,
         1 - (embedding <=> $1) as similarity
       FROM documents
       ORDER BY embedding <=> $1
       LIMIT $2`,
      [`[${queryEmbedding.join(',')}]`, limit]
    );
    
    return result.rows;
  }
 
  async findSimilar(documentId, limit = 5) {
    const result = await pool.query(
      `SELECT 
         d2.id,
         d2.title,
         1 - (d2.embedding <=> d1.embedding) as similarity
       FROM documents d1
       CROSS JOIN documents d2
       WHERE d1.id = $1 AND d2.id != $1
       ORDER BY d2.embedding <=> d1.embedding
       LIMIT $2`,
      [documentId, limit]
    );
    
    return result.rows;
  }
}

Distance Operators

pgvector supports multiple distance metrics:

<=> (cosine distance): Best for normalized vectors (most common)
<-> (L2 distance): Euclidean distance
<#> (inner product): Dot product (for pre-normalized vectors)

When to Use pgvector vs Dedicated Vector DBs

Use pgvector when:

Building MVPs or internal tools
Fewer than 10,000 queries per second
Your vectors live alongside relational data
You want operational simplicity

Consider dedicated vector DBs when:

Scaling to millions of vectors
Requiring sub-10ms query latency at scale
Need advanced features like multi-tenancy or filtering

4. Key-Value Store: Simple and Effective

Sometimes you just need a simple key-value store for configuration, feature flags, or application state. PostgreSQL handles this beautifully without any extensions.

-- Create a simple KV store
CREATE TABLE kv_store (
    key VARCHAR(255) PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at TIMESTAMP DEFAULT NOW()
);
 
-- Create index for better read performance
CREATE INDEX idx_kv_updated ON kv_store(updated_at);
 
-- Add trigger to auto-update timestamp
CREATE OR REPLACE FUNCTION update_kv_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER kv_update_timestamp
    BEFORE UPDATE ON kv_store
    FOR EACH ROW
    EXECUTE FUNCTION update_kv_timestamp();

Implementation

class KeyValueStore {
  async get(key) {
    const result = await pool.query(
      'SELECT value FROM kv_store WHERE key = $1',
      [key]
    );
    
    if (result.rows.length === 0) return null;
    
    try {
      return JSON.parse(result.rows[0].value);
    } catch {
      return result.rows[0].value; // Return as string if not JSON
    }
  }
 
  async set(key, value) {
    const valueStr = typeof value === 'string' 
      ? value 
      : JSON.stringify(value);
    
    await pool.query(
      `INSERT INTO kv_store (key, value)
       VALUES ($1, $2)
       ON CONFLICT (key) 
       DO UPDATE SET value = $2`,
      [key, valueStr]
    );
  }
 
  async delete(key) {
    await pool.query('DELETE FROM kv_store WHERE key = $1', [key]);
  }
 
  async has(key) {
    const result = await pool.query(
      'SELECT 1 FROM kv_store WHERE key = $1',
      [key]
    );
    return result.rows.length > 0;
  }
 
  async keys(prefix = '') {
    const result = await pool.query(
      'SELECT key FROM kv_store WHERE key LIKE $1 ORDER BY key',
      [`${prefix}%`]
    );
    return result.rows.map(row => row.key);
  }
}
 
// Usage examples
const kv = new KeyValueStore();
 
// Store configuration
await kv.set('app.theme', 'dark');
await kv.set('app.max_upload_size', 10485760);
 
// Feature flags
await kv.set('features.new_dashboard', { enabled: true, rollout: 0.5 });
 
// Application state
await kv.set('last_sync', new Date().toISOString());
 
// Retrieve
const theme = await kv.get('app.theme');
const featureFlag = await kv.get('features.new_dashboard');

Advantages Over Redis for KV

Transactional consistency: Updates to multiple keys are atomic
No serialization complexity: Store any JSON structure
Built-in querying: Combine with SQL for complex operations
Unified backup: Part of your regular database backups

5. JSON Storage: Flexible Schema When You Need It

PostgreSQL's native JSON and JSONB support means you can store and query document-style data without a separate document database.

-- Create a table with JSONB column
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER UNIQUE NOT NULL,
    profile_data JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
 
-- Index for querying within JSON
CREATE INDEX idx_profile_data_gin ON user_profiles USING GIN (profile_data);
 
-- Index specific JSON paths for better performance
CREATE INDEX idx_profile_email ON user_profiles ((profile_data->>'email'));
CREATE INDEX idx_profile_country ON user_profiles ((profile_data->>'country'));

Querying JSON Data

-- Insert JSON data
INSERT INTO user_profiles (user_id, profile_data)
VALUES (1, '{
  "email": "john@example.com",
  "name": "John Doe",
  "preferences": {
    "theme": "dark",
    "notifications": true
  },
  "tags": ["developer", "premium"]
}');
 
-- Query by top-level field
SELECT * FROM user_profiles
WHERE profile_data->>'email' = 'john@example.com';
 
-- Query nested fields
SELECT * FROM user_profiles
WHERE profile_data->'preferences'->>'theme' = 'dark';
 
-- Query array containment
SELECT * FROM user_profiles
WHERE profile_data->'tags' ? 'premium';
 
-- Update specific JSON field
UPDATE user_profiles
SET profile_data = jsonb_set(
  profile_data,
  '{preferences, theme}',
  '"light"'
)
WHERE user_id = 1;

JavaScript Integration

class UserProfileService {
  async createProfile(userId, profileData) {
    const result = await pool.query(
      `INSERT INTO user_profiles (user_id, profile_data)
       VALUES ($1, $2)
       RETURNING id`,
      [userId, JSON.stringify(profileData)]
    );
    return result.rows[0].id;
  }
 
  async getProfile(userId) {
    const result = await pool.query(
      'SELECT profile_data FROM user_profiles WHERE user_id = $1',
      [userId]
    );
    return result.rows.length > 0 ? result.rows[0].profile_data : null;
  }
 
  async updatePreference(userId, key, value) {
    await pool.query(
      `UPDATE user_profiles
       SET profile_data = jsonb_set(
         profile_data,
         $2,
         to_jsonb($3::text)
       )
       WHERE user_id = $1`,
      [userId, `{preferences, ${key}}`, value]
    );
  }
 
  async addTag(userId, tag) {
    await pool.query(
      `UPDATE user_profiles
       SET profile_data = jsonb_set(
         profile_data,
         '{tags}',
         (profile_data->'tags') || to_jsonb($2::text),
         true
       )
       WHERE user_id = $1`,
      [userId, tag]
    );
  }
 
  async searchByCountry(country) {
    const result = await pool.query(
      `SELECT user_id, profile_data 
       FROM user_profiles
       WHERE profile_data->>'country' = $1`,
      [country]
    );
    return result.rows;
  }
}

JSONB vs JSON

Always use JSONB (binary JSON) instead of JSON:

✅ Faster to process (pre-parsed)
✅ Supports indexing (GIN indexes)
✅ Efficient operators (@>, ?, ?|, ?&)
✅ Automatic duplicate key elimination

The only reason to use JSON is if you need to preserve key order and whitespace (rare).

Performance Guidelines and Scaling Considerations

When PostgreSQL Excels

PostgreSQL as your primary (and only) data store works beautifully when:

Daily Active Users < 50,000: Most CRUD operations remain fast
Write-heavy operations < 10,000/min: WAL doesn't become a bottleneck
Database size < 500GB: Query planning and maintenance stay manageable
Team size < 20 engineers: Operational simplicity outweighs specialization

Optimization Strategies

-- 1. Proper indexing is critical
CREATE INDEX CONCURRENTLY idx_jobs_status_created 
ON job_queue(status, created_at) 
WHERE status = 'pending';
 
-- 2. Partition large tables
CREATE TABLE job_queue_2024_01 PARTITION OF job_queue
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
 
-- 3. Use materialized views for complex queries
CREATE MATERIALIZED VIEW daily_stats AS
SELECT 
  date_trunc('day', created_at) as day,
  status,
  count(*) as count
FROM job_queue
GROUP BY day, status;
 
-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_stats;
 
-- 4. Configure connection pooling
-- Use PgBouncer in transaction mode
-- max_connections = 100 in PostgreSQL
-- pool_size = 20 per application instance

Monitoring Essentials

-- Monitor table bloat
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
 
-- Check index usage
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public';
 
-- Monitor query performance
SELECT 
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  max_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

When to Graduate to Specialized Systems

As your application scales, you'll eventually need specialized systems. Here are the signs:

Message Queue → Dedicated Queue (SQS, RabbitMQ)

Migrate when:

Processing > 100,000 jobs/minute
Need advanced routing (pub/sub, topic exchanges)
Require multi-region distribution
Jobs have complex retry/DLQ requirements

Cache → Redis/Memcached

Migrate when:

Cache hit ratio is critical for performance
Need < 1ms response times consistently
Require advanced data structures (sorted sets, hyperloglogs)
Cache invalidation becomes complex

Vector Search → Pinecone/Weaviate

Migrate when:

Storing > 10 million vectors
Need < 10ms p99 latency on similarity search
Require advanced features (hybrid search, multi-tenancy)
Vector operations dominate your workload

Real-World Architecture Example

Here's how a modern SaaS application might use PostgreSQL for multiple purposes:

// Unified PostgreSQL architecture
class ApplicationBackend {
  constructor() {
    this.pool = new Pool({ connectionString: process.env.DATABASE_URL });
    this.queue = new PostgresQueue(this.pool);
    this.cache = new PostgresCache(this.pool);
    this.rateLimiter = new RateLimiter(this.pool);
    this.vectorSearch = new VectorSearch(this.pool);
    this.kv = new KeyValueStore(this.pool);
  }
 
  async handleUserRequest(userId, action, data) {
    // 1. Check rate limit
    const rateCheck = await this.rateLimiter.checkLimit(
      `user:${userId}`,
      100,  // 100 requests
      3600  // per hour
    );
    
    if (!rateCheck.allowed) {
      throw new Error('Rate limit exceeded');
    }
 
    // 2. Check cache
    const cacheKey = `user:${userId}:${action}`;
    const cached = await this.cache.get(cacheKey);
    if (cached) return cached;
 
    // 3. Process request (could involve relational queries, JSON queries, etc.)
    const result = await this.processAction(userId, action, data);
 
    // 4. Update cache
    await this.cache.set(cacheKey, result, 300); // 5 minute TTL
 
    // 5. Queue follow-up job if needed
    if (action === 'create_document') {
      await this.queue.enqueue('generate_embeddings', {
        documentId: result.id,
        userId
      });
    }
 
    return result;
  }
 
  async processAction(userId, action, data) {
    // Mix of relational and JSON queries
    if (action === 'update_profile') {
      return await this.pool.query(
        `UPDATE user_profiles
         SET profile_data = profile_data || $1::jsonb
         WHERE user_id = $2
         RETURNING *`,
        [JSON.stringify(data), userId]
      );
    }
    
    if (action === 'search_documents') {
      return await this.vectorSearch.semanticSearch(data.query);
    }
 
    // ... other actions
  }
 
  async runWorker() {
    // Background worker processing queued jobs
    while (true) {
      const job = await this.queue.dequeue();
      
      if (!job) {
        await new Promise(resolve => setTimeout(resolve, 3000));
        continue;
      }
 
      try {
        if (job.job_type === 'generate_embeddings') {
          await this.generateEmbeddings(job.payload);
        }
        await this.queue.markComplete(job.id);
      } catch (error) {
        await this.queue.markFailed(job.id, error.message);
      }
    }
  }
}

Conclusion: The Power of Simplicity

PostgreSQL's versatility doesn't just come from its features—it comes from the operational simplicity of managing a single, robust system instead of a constellation of specialized services.

Key Takeaways

Start simple: Use PostgreSQL for queuing, caching, and KV storage when starting out
Scale thoughtfully: Don't prematurely optimize with specialized systems
Know the limits: Understand when to graduate to dedicated services
Leverage extensions: pgvector, PGMQ, and others extend PostgreSQL's capabilities
Monitor and tune: Good indexing and configuration are critical

The 50K Rule of Thumb

If you have fewer than 50,000 daily active users and moderate write volumes, PostgreSQL can probably handle your entire backend stack. This isn't just about what's technically possible—it's about moving faster by managing fewer systems.

Getting Started

Ready to consolidate your stack? Start here:

Audit your current systems—what's Redis doing that a cache table couldn't?
Identify your queue usage—could FOR UPDATE SKIP LOCKED replace SQS?
Review your vector database—is pgvector sufficient for your scale?
Calculate your actual workload—are you optimizing for problems you don't have?

PostgreSQL isn't just a database. It's a complete data platform hiding in plain sight.

Additional Resources

Have you used PostgreSQL in creative ways? Share your experience in the comments below!