Modern web development is a mess.
We have incredible tools that solve every imaginable problem, yet somehow, it's never been easier to build a billion-dollar failed app idea.
By the time you deploy, you're locked into paying 20 different YC-backed startups just to keep things running.
You need an in-memory cache database to handle your five users, a cron service, authentication, performance monitoring, analytics, serverless functions—the list never ends.
You could throw all of these SaaS subscriptions into the bin and rely on nothing but PostgreSQL? Yes, the same relational database that’s been around for decades can actually handle 90% of your web development needs.
In this post, we’ll explore 11 unconventional, borderline insane ways to use PostgreSQL to build a full-stack application—without all the fancy shovels the modern web throws at you.
Why PostgreSQL?
Why PostgreSQL and not MySQL or SQLite? Out of the box, PostgreSQL provides advanced data types like JSONB, arrays, key-value stores, and even geometric types for defining shapes.
More importantly, it's extensible—you can create custom data types and leverage an ecosystem of powerful extensions.
Before we dive in, a quick disclaimer: just because you can do something in PostgreSQL doesn’t mean you should.
Before you cancel all your SaaS subscriptions, use some common sense and pick the right tool for the job.
1. NoSQL in SQL: JSONB to the Rescue
One of the biggest selling points of NoSQL databases is their ability to handle unstructured data. "SQL can’t do that! Every table needs a strict schema!"
Well, actually, PostgreSQL can do that with JSONB.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
data JSONB
);
INSERT INTO users (data) VALUES ('{"name": "Alice", "age": 25}');
SELECT * FROM users WHERE data->>'name' = 'Alice';
Boom. NoSQL-like flexibility, but with all the ACID guarantees of a relational database.
2. PostgreSQL as a Cron Job Manager
Need a job scheduler? Instead of paying for a third-party cron service, just install the pg_cron extension.
SELECT cron.schedule('daily_cleanup', '0 3 * * *', $$DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days'$$);
Now your database can automatically run scheduled tasks, no extra servers or services needed.
3. Redis? Who Needs It? Use PostgreSQL as a Cache
Instead of spinning up Redis, use an unlogged table in PostgreSQL for fast, ephemeral storage.
Unlogged tables provide high-speed operations because they don’t write to PostgreSQL’s Write-Ahead Log (WAL). This means data isn’t persisted to disk but is kept in memory for lightning-fast access. However, if the server crashes, this data is lost since it's never written to the WAL.
CREATE UNLOGGED TABLE cache (
key TEXT PRIMARY KEY,
value TEXT,
expires_at TIMESTAMP
);
For a cache, persistence isn't a priority, and disabling WAL provides better performance.
To further optimize, update the PostgreSQL configuration to store this table in a shared buffer, keeping the data readily available in RAM.
INSERT INTO cache (key, value, expires_at)
VALUES ('session_123', 'user_data', NOW() + INTERVAL '1 hour');
SELECT value
FROM cache
WHERE key = 'session_123' AND expires_at > NOW();
To prevent bloat, enable autovacuum, and for automated cache expiration, use pg_cron to delete expired entries:
SELECT cron.schedule(
'purge_expired_cache',
'0 * * * *',
$$ DELETE FROM cache WHERE expires_at <= NOW(); $$
);
This setup keeps PostgreSQL functioning as a high-speed cache, eliminating the need for an external service like Redis.
4. PostgreSQL as a Vector Database for AI
Think you need Pinecone or Weaviate for AI-powered search? Nah.
Just install pgvector and store embeddings directly in PostgreSQL:
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT NOT NULL,
embedding vector(768)
);
INSERT INTO documents (content, embedding)
VALUES (
'Hi Dev.to!',
'[0.01, 0.23, -0.47, ... ]'
);
SELECT id, content
FROM documents
ORDER BY embedding <-> '[0.02, 0.20, -0.50, ...]';
pgai: A suite of tools to develop RAG, semantic search, and other AI applications more easily with PostgreSQL
Now you can perform nearest-neighbor searches for similarity-based recommendations using nothing but SQL.
5. Full-Text Search Without Elasticsearch
Why pay for Algolia or Elasticsearch when PostgreSQL has built-in full-text search? With just a few SQL commands, you can implement a powerful, scalable search engine—without extra dependencies or costs.
Start by adding a TSVECTOR column to store searchable text:
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT,
content TEXT,
search_vector TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED
);
Next, create a GIN index to enable lightning-fast lookups:
CREATE INDEX search_vector_idx ON articles USING GIN(search_vector);
Now, let’s insert some sample data and run a search query:
INSERT INTO articles (title, content) VALUES
('Dev.to Rocks!', 'Find everything there'),
('Kafka', 'Why Kafka? A Developer-Friendly Guide to Event-Driven Architecture'),
('Database Design', 'Speed Up DB Queries Like a Pro');
SELECT title,
ts_rank(search_vector, query) AS rank
FROM articles,
to_tsquery('english', 'kafka') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;
Boom! Just like that, you have full-text search running directly inside PostgreSQL—no extra services required.
6. GraphQL Without a Server: pg_graphql
Want a GraphQL API without the hassle of setting up Apollo Server or Hasura? With the pg_graphql extension, PostgreSQL itself becomes your GraphQL backend—no extra servers, no complex configurations, just pure SQL.
Setting it up is as simple as running:
CREATE EXTENSION pg_graphql;
Define your tables as usual:
CREATE TABLE book (
id INT PRIMARY KEY,
title TEXT
);
Insert some data:
INSERT INTO book (id, title) VALUES (1, 'book 1');
And now, run GraphQL queries directly inside PostgreSQL:
SELECT graphql.resolve($$
query {
bookCollection {
edges {
node {
id
}
}
}
}
$$);
You now have a fully functional GraphQL API—powered entirely by PostgreSQL.
No middleware, no extra services, just a clean, efficient way to expose your database as a GraphQL endpoint.
7. Real-Time Sync Without Firebase
Skip the complexity of Firebase and Supabase—sync your PostgreSQL data in real-time with ElectricSQL.
ElectricSQL acts as a sync layer, keeping your frontend instantly updated without the hassle of managing WebSockets, polling, or backend workarounds.
Enjoy real-time reactivity with a seamless developer experience.
Many ideas have been taken from this video, enjoy watching too.
PostgreSQL is the Only Tool You Need xD
At this point, we’ve built a full-stack application using nothing but PostgreSQL.
We’ve replaced Redis, Firebase, cron jobs, GraphQL servers—all without leaving our database.
Of course, this doesn’t mean PostgreSQL should replace everything in your stack.
But next time you reach for a fancy new SaaS tool, ask yourself: Do I really need this, or can PostgreSQL do it for free?
And with that, congratulations—you’re now officially a PostgreSQL purist. Welcome to the resistance.
I’ve been working on a super-convenient tool called LiveAPI.
LiveAPI helps you get all your backend APIs documented in a few minutes
With LiveAPI, you can quickly generate interactive API documentation that allows users to execute APIs directly from the browser.
If you’re tired of manually creating docs for your APIs, this tool might just make your life easier.
Top comments (0)