Replacing an entire modern stack with Postgres

My notes

Summary

Postgres can replace most of a modern stack (Redis, Kafka, Elasticsearch, Pinecone, MongoDB, Snowflake-style warehouses, separate API layers) via native features and extensions. The architecture wins are measurable: fewer moving parts, one source of truth, ACID guarantees across features that usually span multiple systems. The limit is extreme horizontal scale, until then, stacking on Postgres is usually the cheaper, saner choice.

Key Insight

• JSONB + GIN index replaces MongoDB: JSONB stores JSON as decomposed binary at insert time (no re-parsing on query). GIN (generalized inverted index) maps keys directly to row IDs, enabling instant queries on deeply nested fields AND joins with relational tables in one ACID transaction.
• FOR UPDATE SKIP LOCKED replaces RabbitMQ/Redis queues: the classic SQL queue deadlock problem vanishes, workers grab the next available row, skip locked ones, never wait. Handles thousands of jobs/sec on a regular table.
• tsvector + pg_trgm replaces Elasticsearch for app search: linguistic stemming (running to run), stop word removal, plus trigram-based fuzzy matching for typo tolerance. No secondary cluster, no sync layer.
• pgvector + HNSW replaces Pinecone: stores embeddings next to relational data. HNSW (hierarchical navigable small world) is a multi-layer graph index that does approximate nearest neighbour in milliseconds. Kills the “hybrid search problem”, combine vector similarity with strict WHERE filters in one query.
• PostGIS is the gold standard, not a substitute: GiST index uses bounding boxes to discard millions of points instantly before running expensive geometric math on survivors. Routinely outperforms standalone GIS.
• BRIN + declarative partitioning replaces time-series DBs: BRIN stores only min/max per physical block, not per row. For sequentially inserted logs this is dramatically smaller than B-trees and lets Postgres skip millions of disk pages per range query.
• Materialized views + REFRESH ... CONCURRENTLY replaces Snowflake for dashboards: query runs once, result persisted to disk, refresh in background without locking readers (requires unique index on the view).
• PostgREST / pg_graphql + Row-Level Security replaces custom API servers: auto-generates REST/GraphQL from schema. RLS enforces per-user access cryptographically at the DB layer, no controller boilerplate, no auth middleware bugs.
• Where it breaks: Postgres scales vertically beautifully, but horizontal sharding is painful. Millions of events/sec ingestion or sub-ms in-memory caching for millions of websockets still need specialized tools.
• Neon branching (sponsor-mentioned but architecturally interesting): separating compute from storage makes git-style DB branching cheap for staging/tests.