There’s a particular kind of developer madness that sets in after your third or fourth infrastructure meeting.
You’re staring at an architecture diagram that now includes Postgres, Redis, Elasticsearch, a GraphQL middleware server, a separate auth service, a vector database for your new AI feature, and something called “the cron worker” that nobody fully understands.
Every box on that diagram is a new bill, a new failure point, and a new thing to wake you up at 3 AM. And somewhere in the back of your head, a thought forms: do we actually need all of this?
What if I told you that Postgres, the same humble relational database you’ve been using since your first CRUD tutorial, can do almost all of it? Not theoretically. Not with janky hacks. Natively, with official extensions, right now.
This is the Postgres maximalist philosophy. And once you see it, you can’t unsee it.
🧠 First, Why Postgres Specifically?
Postgres is the rare piece of software that has been battle-tested for over 30 years and still manages to outpace newer tools. It’s open source, has a vibrant extension ecosystem, runs everywhere, and has a rock-solid reputation for correctness and reliability.
But here’s the thing, most developers don’t realise, Postgres was designed from day one to be extensible. Its extension system isn’t an afterthought, it’s a first-class citizen. That design choice is exactly why the Postgres community has been quietly building a replacement for your entire stack, one extension at a time.
Let me go through it, feature by feature.
1. 🗂️ Unstructured Data: The NoSQL Capabilities You Didn’t Know You Had
The problem: You need to store flexible, schema-less data, like user preferences, third-party API responses, or product metadata, where every item looks different. Normally, you’d reach for MongoDB.
The Postgres way: JSONB.
JSONB is Postgres's binary JSON column type. Unlike plain JSON, it stores data in a decomposed binary format that's indexable and blazing fast to query. You get the flexibility of a document store with the power of SQL.
-- Create a table with a JSONB column
CREATE TABLE events (
id SERIAL PRIMARY KEY,
payload JSONB
);
-- Insert raw JSON - no schema required
INSERT INTO events (payload)
VALUES ('{"user": "alice", "action": "click", "meta": {"button": "signup"}}');
-- Query deeply nested fields with the ->> operator
SELECT payload->>'user' AS username
FROM events
WHERE payload->>'action' = 'click'
AND payload->'meta'->>'button' = 'signup';What’s happening here: The ->> operator extracts a JSON field as text. The -> operator extracts it as a JSON object (useful for chaining into nested fields). You can index these fields using GIN indexes to make queries as fast as any dedicated document database.
For new developers: think of JSONB as a column that can hold an entire JSON object — like a drawer that doesn't care what shape the thing inside is.
2. ⏰ Scheduled Tasks: Free Cron Jobs Inside Your Database
The problem: You need to run a cleanup job every night at midnight, or aggregate hourly metrics. Normally, you’d spin up a separate cron worker, a Lambda function on a schedule, or something like Celery.
The Postgres way: pg_cron.
pg_cron is a lightweight background worker that lets you schedule SQL commands using standard cron syntax, all from inside your database.
-- Schedule a daily cleanup job at 3am UTC
SELECT cron.schedule(
'nightly-cleanup', -- job name
'0 3 * * *', -- cron expression: at 3:00 AM every day
$$DELETE FROM sessions WHERE created_at < NOW() - INTERVAL '30 days'$$
);What’s happening here: The cron expression 0 3 * * * means "at minute 0, hour 3, every day." The $$ dollar-quoting is just Postgres's way of wrapping a string that contains single quotes without escaping everything.
If you’re new to cron syntax, the five fields represent minute, hour, day-of-month, month, and day-of-week. * means "every."
This eliminates an entire category of infrastructure. No separate worker, no queue, no deploy. It lives right next to your data, where it belongs.
3. ⚡ In-Memory Cache: Replacing Redis Without Leaving Postgres
The problem: You need a fast, ephemeral cache for things like session tokens, rate-limiting counters, or API responses. Redis is the default answer, but it’s another service, another connection pool, and another monthly bill.
The Postgres way: Unlogged tables + shared buffers + pg_cron TTL cleanup.
This one is a three-step trick:
Step 1: Create an unlogged table:
-- Unlogged tables skip Write-Ahead Logging (WAL) for faster writes
CREATE UNLOGGED TABLE cache (
key TEXT PRIMARY KEY,
value TEXT,
expires_at TIMESTAMPTZ
);Step 2: Tune your Postgres config to keep it in RAM:
# postgresql.conf
shared_buffers = 2GB # Increase to keep hot tables in memoryStep 3: Auto-expire entries with pg_cron:
-- Run every minute to evict expired cache entries
SELECT cron.schedule(
'cache-eviction',
'* * * * *',
$$DELETE FROM cache WHERE expires_at < NOW()$$
);What’s happening here: UNLOGGED means Postgres skips writing changes to its crash-recovery log (WAL). This makes writes significantly faster, at the cost of data loss if the database crashes, which is perfectly fine for a cache. By bumping shared_buffersyou're telling Postgres to keep more data pages in RAM, so hot keys never touch disk.
Is this a perfect Redis replacement? No. Redis has pub/sub, atomic operations, and dedicated memory management. But for the vast majority of caching use cases, session storage, computed results, and rate limits, this works beautifully and removes a whole layer from your stack.
4. 🤖 AI and Vector Search: Your RAG Stack Lives Here Now
The problem: You’re building an AI feature, maybe a chatbot, a semantic search, or a document retrieval system. You’ve heard you need a “vector database” like Pinecone or Weaviate, which means more infrastructure, more APIs, more cost.
The Postgres way: pgvector + pgai.
A vector database stores numerical representations of data (called embeddings) and lets you find the closest matches mathematically. pgvector brings this capability natively into Postgres.
-- Enable the extension
CREATE EXTENSION vector;
-- Create a table that stores document embeddings
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT,
embedding VECTOR(1536) -- 1536 dimensions for OpenAI embeddings
);
-- Find the 5 most semantically similar documents to a given vector
SELECT content
FROM documents
ORDER BY embedding <-> '[0.1, 0.2, ...]' -- <-> is L2 (Euclidean) distance
LIMIT 5;pgai goes even further, it lets you call embedding models directly from SQL:
-- Vectorize a document without leaving SQL
SELECT ai.embed('text-embedding-3-small', 'What is Postgres?');What’s happening here: The <-> operator is the L2 distance operator provided by pgvector. Smaller distance = more similar. You can also use <#> for inner product or <=> for cosine distance, depending on how your model was trained.
For developers new to AI: an “embedding” is a list of numbers that represents the meaning of a piece of text. Things that mean similar things have embeddings that are mathematically close. Vector search lets you find “what’s most similar in meaning to this query.”
The killer feature here is co-location. Your business data and your AI retrieval layer are in the same database, the same transaction, the same backup. No API round-trips to a separate vector store.
5. 🔍 Full-Text Search: Goodbye, Elasticsearch
The problem: You need a search that handles typos, partial words, and relevance ranking. Elasticsearch can do it, but it’s operationally heavy and expensive.
The Postgres way: tsvector + GIN indexes + fuzzy matching.
-- Add a search vector column
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;
-- Populate it from title and body
UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || body);
-- Create a GIN index for fast lookups
CREATE INDEX articles_search_idx ON articles USING GIN(search_vector);
-- Search with relevance ranking
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgres & database') query
WHERE search_vector @@ query
ORDER BY rank DESC;What’s happening here:
to_tsvectorbreaks text into "lexemes", normalized word roots (so "running" becomes "run")GIN(Generalized Inverted Index) maps each lexeme to all the rows containing it — like a book index@@is the text search match operatorts_rankreturns a relevance score so you can sort results meaningfully
Want fuzzy search (typo tolerance)? Add the pg_trgm extension:
CREATE EXTENSION pg_trgm;
SELECT title FROM articles
WHERE title % 'postgress'; -- Finds 'postgres' even with typoFor most product search features, this is entirely sufficient, and it lives in the same place as your data.
6. ⚡ GraphQL API: Zero Middleware Required
The problem: Your frontend needs a GraphQL API. The standard approach is a Node.js (or Go) server sitting between your database and your client, translating GraphQL queries into SQL.
The Postgres way: pg_graphql.
-- Enable the extension
CREATE EXTENSION pg_graphql;
-- Your tables are automatically exposed as GraphQL types
-- You can write custom resolvers directly in SQL
CREATE FUNCTION graphql.resolve(
"operationName" TEXT DEFAULT NULL,
query TEXT DEFAULT NULL,
variables JSONB DEFAULT NULL,
extensions JSONB DEFAULT NULL
)
RETURNS JSONB ...Once installed, pg_graphql reflects your schema, tables, columns, and foreign keys, and exposes them as a GraphQL API automatically.
PostgREST (covered below) can serve this to clients with zero additional code. The middleware layer simply evaporates.
💡 Enjoying this article?
Every week day, I publish practical, production-ready deep dives covering Web development, System Design, Open source projects, Tech industry trends and AI Engineering and tools.
7. 🔄 Real-Time Sync: Live Data Without Writing WebSocket Code
The problem: You want the UI to update the moment data changes in the database, think live dashboards, collaborative editing, or live order tracking. Normally, this requires WebSocket servers, polling strategies, or managed services like Firebase Realtime Database.
The Postgres way: ElectricSQL.
ElectricSQL isn’t a Postgres extension, it’s a sync layer that sits directly between your Postgres database and your frontend. It uses Postgres logical replication (a built-in feature) to stream changes out and keep your client state synchronized automatically.
// In your React component
import { useShape } from '@electric-sql/react'
function OrderTracker() {
const { data } = useShape({
url: `http://localhost:3000/v1/shape/orders`
})
return <ul>{data.map(order => <li key={order.id}>{order.status}</li>)}</ul>
}Data changes in Postgres. ElectricSQL streams the diff. Your React component re-renders. No WebSocket servers to manage, no polling, no Firebase dependency.
8. 🔐 Authentication: Passwords and JWTs Without a Separate Auth Service
The problem: User auth usually means Auth0, Firebase Auth, Supabase Auth, or a custom Node.js service. Each adds a dependency and an integration surface.
The Postgres way: pgcrypto + pgjwt.
-- Step 1: Hash a password with bcrypt (pgcrypto)
INSERT INTO users (email, password_hash)
VALUES (
'[email protected]',
crypt('supersecret', gen_salt('bf')) -- bf = bcrypt
);
-- Step 2: Verify login credentials
SELECT id, email
FROM users
WHERE email = '[email protected]'
AND password_hash = crypt('supersecret', password_hash);
-- Step 3: Issue a JWT token
SELECT sign(
json_build_object(
'sub', user_id::text,
'role', 'authenticated',
'exp', extract(epoch FROM NOW() + INTERVAL '1 hour')
),
'your-secret-key' -- use an environment variable in production
) AS token
FROM users WHERE email = '[email protected]';What’s happening here:
gen_salt('bf')generates a bcrypt salt. Bcrypt is slow by design, that's a feature, not a bug. It makes brute-force attacks impractical.crypt(input, stored_hash)hashes the input using the same parameters as the stored hash, then compares. This is a constant-time comparison, preventing timing attacks.sign()frompgjwtproduces a cryptographically signed JWT that your frontend can send on every request.
For new developers: never store plain-text passwords. The hash is a one-way transformation, you can verify a password matches a hash, but you can’t reverse a hash back into the original password.
9. 🛡️ Row Level Security: Data Isolation Built Into the Database
The problem: In a multi-tenant app, every query needs to be filtered by the current user. If you do this in application code, one bug can expose another user’s data. It’s the kind of mistake that ends careers and companies.
The Postgres way: Row Level Security (RLS).
-- Enable RLS on the table
ALTER TABLE horses ENABLE ROW LEVEL SECURITY;
-- Create a policy: users can only see their own horses
CREATE POLICY user_isolation ON horses
USING (owner_id = current_user_id());
-- This helper function extracts the user ID from the JWT in the session
CREATE FUNCTION current_user_id() RETURNS UUID AS $$
SELECT (current_setting('request.jwt.claims', true)::jsonb->>'sub')::UUID
$$ LANGUAGE sql STABLE;Now, no matter what query runs against this table, whether from your app, from a reporting tool, or from a junior developer’s mistake, Postgres filters rows at the engine level. No code path bypasses it.
This is one of those features that, once you understand it, makes you feel slightly embarrassed about how you used to handle multi-tenancy.
10. 📊 Analytics & Time-Series: A DuckDB Engine Inside Postgres
The problem: Your app generates a lot of time-series data, metrics, logs, events, and IoT readings. Analytical queries over this data (aggregations, window functions, trend analysis) are notoriously slow on row-oriented databases.
The Postgres way: pg_mooncake.
pg_mooncake adds columnar storage tables to Postgres, powered by DuckDB, one of the fastest analytical query engines in existence.
CREATE EXTENSION pg_mooncake;
-- Create a columnstore table for analytics
CREATE TABLE metrics (
ts TIMESTAMPTZ,
sensor_id INT,
value FLOAT
) USING columnstore;
-- Analytical queries run at DuckDB speed
SELECT
date_trunc('hour', ts) AS hour,
AVG(value) AS avg_reading
FROM metrics
WHERE ts > NOW() - INTERVAL '7 days'
GROUP BY 1
ORDER BY 1;Columnar storage organizes data by column rather than by row, which is dramatically faster for queries that touch a few columns across millions of rows, exactly the pattern for analytics. The data can also be exported to cloud object storage (S3, GCS) and visualized with Grafana with minimal configuration.
11. 🌐 Instant REST API: PostgREST
The problem: Your database has the data. Your frontend needs an HTTP API. Normally, you write a server, define routes, handle auth, and deploy it.
The Postgres way: PostgREST.
PostgREST is a standalone web server (a single binary) that reads your Postgres schema and automatically generates a fully-featured REST API from it.
# Start PostgREST pointing at your database
postgrest postgrest.conf
# Querying is just HTTP
GET /horses?owner_id=eq.123&limit=10
Authorization: Bearer <jwt>
# It returns JSON automatically
[
{ "id": 1, "name": "Thunder", "breed": "Thoroughbred" },
...
]It supports filtering, pagination, sorting, bulk operations, and JWT-based auth that integrates directly with your RLS policies. It’s not a toy, PostgREST powers the backend of Supabase.
12. 🖥️ Serving the Frontend UI : Yes, Even the HTML
This is where the rabbit hole goes all the way down.
Some Postgres maximalists store their actual frontend, HTML, CSS, JavaScript, in the database itself, served through PostgREST. A few have even experimented with React Server Components running inside Postgres functions, keeping the render logic as physically close to the data as possible.
-- Store a UI component in the database
INSERT INTO ui_components (name, html) VALUES (
'dashboard-header',
'<header class="header"><h1>Dashboard</h1></header>'
);
-- PostgREST serves it via HTTP
-- GET /ui_components?name=eq.dashboard-headerIs this advisable for everyone? Probably not. But it’s a fascinating demonstration of how far the model extends, and how much of the traditional stack can, in principle, collapse into a single system.
🤔 Should You Actually Do This?
Here’s the honest answer: it depends.
For a solo project, a startup, or a team that wants to move fast without managing a dozen services, the Postgres maximalist approach is genuinely compelling. Fewer moving parts means fewer things to break, fewer things to monitor, and fewer things to pay for.
For a large-scale system with millions of users, extreme write throughput, or very specialized needs, you might genuinely need Redis’s pub/sub, or Elasticsearch’s fuzzy matching at scale, or a dedicated time-series database.
But here’s the question worth sitting with: are you reaching for those specialized tools because you actually need them, or because they’re the default answer?
Most applications never hit the limits where Postgres starts to struggle. And by the time they do, they’ll have the scale, the team, and the revenue to justify the added complexity.
Start simple. Start with Postgres.
🏁 The Postgres Maximalist Manifesto
If I had to distil this entire philosophy into one sentence, it would be this:
The best infrastructure is the infrastructure you’re not thinking about.
Every external service you eliminate is a bill you don’t pay, a failure mode you don’t debug, and cognitive overhead you don’t carry. Postgres is not a compromise, it’s a bet on a 30-year-old piece of software that still wins benchmarks, still gets better every year, and still surprises experienced engineers with what it can do.
Go build something. Use one database. See how far it takes you.
Thank You for Reading!
I hope you found it helpful and informative. If you have any questions or feedback, feel free to leave a comment below. Your support and engagement mean a lot to me.
If you enjoyed this article and would like to support my work,
and get the next one delivered straight to your inbox for free.