There is a genre of blog post going around that says you can replace your entire stack with Postgres. Kill Redis, kill Kafka, kill Elasticsearch, kill your vector database, all of it. Just use Postgres.
The genre is right about the feature list and wrong about the operational reality. Every claim in those posts is technically true. JSONB with GIN indexes really does give you MongoDB-ish flexibility. FOR UPDATE SKIP LOCKED really does build a queue. pg_trgm really does fuzzy search. pgvector really does ANN search. What those posts do not tell you is where each feature breaks, what it costs operationally, and when the specialized tool you were going to eliminate starts to look cheap again.
This post is the version I wish someone had written for me: a concrete adoption ladder with numbers, not a feature tour. Four tiers, from 'adopt without thinking' to 'do not even try'. Each item has a rough threshold that tells you when to stop.
Tier 1: Adopt Without Hesitation
Load-bearing parts of Postgres that a senior engineer should reach for on day one. Stable, cheap, no operational debt.
JSONB with GIN indexes
Structured-plus-flexible data lives here. A user profile with a predictable schema and a preferences blob? One table, two columns, one GIN index on the blob. You get join capability, ACID, and schema-on-read in the same place. The standard complaint is storage overhead (JSONB stores field names in every row), but that is a problem at hundreds of millions of documents, not at your pre-Series-B scale.
pg_trgm for fuzzy text matching
Trigram indexes solve the 'search bar that tolerates typos' problem in about five lines of SQL. This is the 90% case. If you are reaching for Elasticsearch to power a typeahead over 200k products, you are overengineering.
Materialized views with REFRESH CONCURRENTLY
If your dashboard query aggregates across a million rows and takes six seconds, the answer is not a Snowflake contract. It is a materialized view refreshed every five minutes. With a unique index you can refresh concurrently, which calculates in the background and swaps rows in without blocking readers.
pgvector under 5M vectors
Timescale's own 2024 benchmark (vendor-run, 50M embeddings at 99% recall) had pgvectorscale (a separate Tiger Data extension on top of pgvector, not vanilla pgvector) beating Pinecone's storage-optimized s1 pod index: 28ms p95 versus 784ms p95. Under 5M vectors with HNSW indexes, pgvector is not a compromise, it is the correct choice. This is especially true when you need hybrid filters ('semantic similarity but only documents owned by this tenant in the last week'). Hybrid filters are where specialized vector DBs still hurt.
BRIN indexes plus declarative partitioning for time-series
If your data is roughly monotonic (logs, events, metrics) and you partition by day or week, BRIN gives you an index one to three orders of magnitude smaller than a B-tree on the same data. Where you land in that range depends entirely on how closely the physical row order tracks the indexed column: near-perfect correlation puts you at the top of it, a shuffled table puts you at the bottom and makes BRIN the wrong index. Range scans stay fast. Disk stays cheap.
PostGIS for anything geospatial
PostGIS is not an alternative to commercial GIS systems, it is the standard those systems often lose to. If you are shipping anything that touches coordinates, start here and stop thinking about it.
Tier 2: Adopt With Operational Discipline From Day One
These work, but you have to pay attention. Ship them without the supporting discipline and you will learn the hard way.
Queues with FOR UPDATE SKIP LOCKED
The cleanest single SQL feature in Postgres. Turns a regular table into a concurrent work queue. The catch lives at a different layer: churn. A high-throughput queue means constant INSERT, UPDATE, DELETE on the same table, which means dead tuples, which means VACUUM pressure. If autovacuum cannot keep up, the query planner starts choosing bad plans, your 'instant' job grab turns into a 400ms seq scan, and your workers look broken.
Tune autovacuum per-table (dropping autovacuum_vacuum_scale_factor from the default 0.2 to 0.02 on queue tables is the industry standard, meaning vacuum triggers at 2% dead tuples instead of 20%), keep transactions short, monitor dead tuple ratio. This is not optional, it is part of adopting the pattern.
Full-text search with tsvector
Works beautifully under one million documents in a single language. Above that, you start to feel what Elasticsearch actually buys you: BM25 scoring that is tunable, real multilingual analyzers, faceted search, fuzzy matching beyond trigrams. The graduation threshold is roughly 1M to 5M records, or the first time a product manager asks for 'relevance like Algolia'.
PgBouncer in front of everything
Postgres is process-per-connection. Each connection is a 5MB OS process. Default max_connections is 100, and performance degrades above roughly 200 to 500. In production you will blow past that faster than you expect, especially with modern frameworks that open lazy connections everywhere. PgBouncer is not a 'later' tool, it is part of the base stack.
PgBouncer has three pooling modes: session (one server connection per client, safest but the pool fills fast), transaction (server connection released back to the pool when each transaction commits, the usual choice for most workloads), and statement (released after every statement, breaks transactions, rarely the right call). Use transaction pooling, pool size around 20 to 30 per app instance, monitor the wait queue. Be aware that some Postgres features stop working correctly in transaction pooling: LISTEN/NOTIFY, session-scoped advisory locks, SQL-level PREPARE. (Protocol-level prepared statements are supported since PgBouncer 1.21 and on by default since 1.24.) Know which features you lose before you adopt the mode.
Tier 3: Think Twice
Features that look great in blog posts and become load-bearing in ways you will regret.
LISTEN/NOTIFY for high-throughput pub/sub
Sounds free because it is built in. Here is the part the feature tour skips: NOTIFY takes a heavyweight exclusive lock just before commit and holds it until the transaction ends, so transactions that issue a NOTIFY commit one at a time. A plain UPDATE that notifies nobody never touches that lock. That sounds narrow until the notify sits on your hottest write path, and your write throughput is capped by a single serialized queue. Messages are not persisted, the payload limit is 8KB, and the whole thing does not work with transaction pooling. Use it for internal dashboards, admin panels, low-frequency invalidation signals. Do not use it as the nervous system of a high-traffic product.
Row-Level Security as your sole authorization layer
RLS is powerful, and for multi-tenant apps it is genuinely a good tool. The trap is performance: every query the planner sees has your RLS policy inlined into it, and if your policy touches a non-indexed column or a subquery, a simple SELECT can turn into something that reads like a 14-table join. Treat RLS as the backstop and keep application-level checks on the critical paths anyway. Adopt RLS with the same respect you would give a database trigger: useful, invisible, and capable of surprising you.
PostgREST or pg_graphql as your entire backend
Auto-generated APIs over your schema are a wonderful prototyping tool. They cover CRUD cleanly. The moment real business logic enters (multi-step workflows, external API calls, rules that live in code review), you are writing PL/pgSQL functions. PL/pgSQL has no modern stack traces, weak tooling, poor git discipline by default, and no language server worth the name. The pitch of 'eliminate the backend' becomes 'your entire business logic lives in random .sql files that nobody remembers writing'.
Tier 4: Do Not
Some workloads are not the job for a single Postgres instance, no matter how many extensions you load.
100M+ vectors
Above that range, dedicated vector DBs (Qdrant, Milvus, Weaviate) win meaningfully on recall at latency and on memory footprint. If your RAG system is over that threshold, the specialization is earned.
Millions of websocket events per second
Redis Streams, Kafka, and NATS exist for this. Postgres is not built for high-throughput fan-out at that rate, regardless of whether the backing store is memory (Redis) or a durable log (Kafka).
Pub/sub with thousands of concurrent listeners
LISTEN/NOTIFY caps out practically around 1000 listeners, and it degrades well before that under write load. Around 100 listeners is where it stays boring, which is the number to design to. The thousand is the wall, not the target.
Multi-region active-active writes
Postgres has one primary. Logical replication and active-active solutions exist, but they move the hard problems (conflict resolution, write amplification) into your application. If you genuinely need writes in three regions, the database is not the layer to keep simple.
The Mental Model
The point of the ladder is not 'Postgres is not enough'. The point is that Postgres is almost always enough, for longer than you think, if you adopt it with the operational discipline it expects. Most shops that adopt a Redis, a Kafka, an Elasticsearch, and a vector database in their first year are paying for complexity they have not yet earned.
The rule I use: start in Tier 1 without argument, promote to Tier 2 only when you have the ops maturity to back it, treat Tier 3 as a deliberate decision with a graduation plan, and recognize Tier 4 as 'different tool' rather than 'bigger Postgres'.
The unsexy answer to 'what should I use?' is usually 'Postgres, carefully'. The blog posts that say 'just use Postgres' are right about the direction and wrong about the ease.
References
- JSONB Data Type (PostgreSQL docs): https://www.postgresql.org/docs/current/datatype-json.html
- GIN Indexes (PostgreSQL docs): https://www.postgresql.org/docs/current/gin.html
- Full-Text Search with tsvector and tsquery (PostgreSQL docs): https://www.postgresql.org/docs/current/textsearch.html
- REFRESH MATERIALIZED VIEW CONCURRENTLY (PostgreSQL docs): https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html
- SELECT ... FOR UPDATE SKIP LOCKED (PostgreSQL docs): https://www.postgresql.org/docs/current/sql-select.html
- Table Partitioning (PostgreSQL docs): https://www.postgresql.org/docs/current/ddl-partitioning.html
- BRIN Indexes (PostgreSQL docs): https://www.postgresql.org/docs/current/brin.html
- Row Security Policies / RLS (PostgreSQL docs): https://www.postgresql.org/docs/current/ddl-rowsecurity.html
- LISTEN and NOTIFY (PostgreSQL docs): https://www.postgresql.org/docs/current/sql-notify.html
- NOTIFY queue and the commit-time serialization lock (PostgreSQL source, src/backend/commands/async.c): https://github.com/postgres/postgres/blob/master/src/backend/commands/async.c
- Routine Vacuuming and Autovacuum Tuning (PostgreSQL docs): https://www.postgresql.org/docs/current/routine-vacuuming.html
- pgvector official repository (HNSW since 0.5.0, August 2023): https://github.com/pgvector/pgvector
- pg_trgm extension (trigram matching): https://www.postgresql.org/docs/current/pgtrgm.html
- PostGIS official site: https://postgis.net/
- PostgREST official docs: https://postgrest.org/
- pg_graphql (Supabase): https://github.com/supabase/pg_graphql
- pgvector vs Pinecone benchmark (Timescale, now Tiger Data, June 2024): https://www.tigerdata.com/blog/pgvector-vs-pinecone
- Postgres Job Queues & Failure By MVCC (Brandur Leach): https://brandur.org/postgres-queues
- Postgres Full-Text Search vs Elasticsearch (ParadeDB): https://www.paradedb.com/blog/elasticsearch-vs-postgres
- PgBouncer: useful, important, and fraught with peril (JP Camara): https://jpcamara.com/2023/04/12/pgbouncer-is-useful.html