Using Just Postgres

One of the most beautiful open-source tools in the current world is Postgres. Battle-tested since 1986. It was never just a database. It's a full system engine. And for most full-stack systems, it can replace your entire data infrastructure. I love to use it, to learn more about Postgres, to go in-depth. In this article, I will walk you through how Postgres is a market giant. How can it replace your whole tech stack? What does it actually provide? We will try to cover most of the utilities it provides.

ACID Transactions

Every write in Postgres is atomic, consistent, isolated, and durable, by default. Think of it like this: you are processing a payment. The money leaves one account, an order gets created, and inventory gets updated - all at once. Either everything happens, or nothing does. No half-written records, no inconsistent state, no messy rollback logic hardcoded on the application side.

• Atomicity: either the whole transaction or not at all.
• Consistency: your data never ends up in a broken, invalid state - even if your server crashes mid-write.
• Isolation: two transactions happening at the same moment don't interfere with each other.
• Durability: once Postgres says a write is done, it's done. Data should be safe.

When something goes wrong, Postgres handles it cleanly. Your data stays sane, always.

Concurrency

Most databases start choking when too many people hit them at once. Reads and writes start stepping on each other. Locks pile up. Queues form. And what was a fast app at a hundred users becomes a sluggish mess at ten thousand. Postgres doesn't work that way. Postgres is built around a specific model. It's built around something called MVCC — Multi-Version Concurrency Control.

The idea is simple but powerful: instead of making everyone wait in line, Postgres gives each transaction its own consistent snapshot of the data to work with. Readers see a clean view of the world. Writers do their thing. Neither one knows the other exists. A thousand users are reading, a hundred writing, simultaneously. Nobody blocks. Nobody waits. The database just handles it.

Concurrency bugs are some of the hardest problems to debug in production. Deadlocks, race conditions, stale reads — these things show up at the worst possible moment, under real load, with real users. Postgres eliminates an entire class of these problems before you even have to think about them.

JSON Like a NoSQL Database

You don't have to choose between a structured relational database and a flexible document store. Postgres gives you both in one place. You can store rich, nested JSON data right alongside your regular tables — and still query it, index it, and filter it with full precision. The flexibility of MongoDB, without abandoning the reliability of a relational system.

MongoDB was never the only option for flexible schemas. It was just the loudest one. The flexibility of a document store. The reliability of the relational system. The consistency guarantees of ACID transactions - wrapping your JSON writes just like everything else.

Reddit employs a "ThingDB" model using PostgreSQL as a key-value store for most objects like links and comments, alongside traditional SQL for analytics.

By the way, NoSQL means Not Only SQL.

Indexing That Covers Everything

Your database is fast when your data is small. But as it grows — millions of rows, tens of millions, hundreds of millions — queries start slowing down. Pages start timing out. Indexing is how databases stay fast as your data grows. Postgres has one of the most sophisticated indexing systems of any database in existence.

It gives you a whole toolkit, each type of indexing purpose-built for a different kind of data and query pattern.

• B-Tree is the default — fast, balanced, and reliable for standard equality lookups, range queries, and sorting. It handles the vast majority of what most applications need.
• GIN (Generalised Inverted Index) is built for full-text search and JSON fields, where a single column holds multiple values that all need to be searchable independently.
• GiST handles geometric and spatial data — the index type PostGIS leans on when you're querying by distance or boundary.
• BRIN (Block Range Index) is built for massive, naturally ordered tables like time-series data, where it tracks min/max values per block instead of indexing every row — staying tiny while covering billions of records.
• And partial indexes let you index only the rows that actually matter — for example, only active users, or only unpaid orders — so your index stays lean and your queries stay targeted.

The right index doesn't just speed up a query. It can be the difference between a 30-millisecond response and a 30-second timeout. Postgres gives you the tools to make that choice deliberately, not by accident.

Geospatial - Built for Location

Install the PostGIS extension, and Postgres becomes a world-class geographic information system. You can store coordinates, calculate distances, find everything within a radius, and work with complex geographic boundaries — all inside your existing database.

Every food delivery app. Every ride-hailing platform. Every "find stores near me" feature. Every map that draws a boundary around a neighborhood — all of it runs on geospatial data. And most people assume you need a specialised database just to handle it. Postgres can do it. Your location data lives with your user data, your order data, and your inventory data. You can query across all of it in a single call. No syncing between systems. No API hop to a separate geo service. No extra latency. No extra cost.

Major companies utilising PostgreSQL's PostGIS extension for geospatial data include Uber, OpenStreetMap, Netflix, Redfin, Apple, and Google. It is used for routing, map rendering, satellite imagery analysis, and location-based analytics by firms like Descartes Labs and onXmaps

Replication and High Availability

Postgres ships with streaming replication out of the box. Set up a primary database and one or more replicas — your reads scale horizontally, and if your primary goes down, a replica takes over. This is how you build systems that stay up when things go wrong. The tooling around this is mature. It's been running in production at some of the largest companies in the world for years. It's well understood, well documented, and battle-tested in ways that newer databases simply aren't yet.

OpenAI operate a single primary Azure Postgres instance and nearly 50 read replicas across multiple geographic regions to increase availability and reduce latency.

Handling Massive Scale with Partitioning

When your tables grow to hundreds of millions of rows, most databases start to struggle. Postgres handles it with table partitioning — the idea is elegant. Instead of one massive table holding every row you've ever written, Postgres quietly splits it into smaller, focused chunks behind the scenes.

You can partition by date — all orders from January in one chunk, February in another. By region. By user ID range. By any dimension that makes sense for your data. When a query comes in, Postgres knows exactly which chunk to look in. It doesn't scan the whole table. It goes straight to the relevant partition and ignores everything else.

A query that used to crawl through a hundred million rows now only touches the fraction it actually needs. Your application doesn't need to change at all. Postgres handles the routing automatically. Queries stay fast. You don't need to re-architect everything just because your data grew. To go in-depth into the database partitioning study, Chapter 6 of Designing Data Intensive Applications by Martine Kleppmann.

Caching Without Redis

Postgres can serve as a fast cache for ephemeral data — session stores, temporary computation results, and rate limit counters. The way it works is simple. Postgres normally writes everything to a log before committing — that's how it guarantees durability. This special mode skips that step entirely. No log. No durability guarantee. Just raw, fast writes and reads. The tradeoff is intentional — if the server crashes, that data is gone. But for cache data, that's perfectly fine. You were going to expire it anyway.

Is it a full Redis replacement for every situation? No. If you need pub/sub at a massive scale, or microsecond response times for millions of concurrent connections, Redis still earns its place. But for the majority of applications using Redis for basic caching and session storage? Postgres can handle it. Quietly. Without the extra infrastructure.

Netflix uses Aurora PostgreSQL (which features highly optimised caching and storage layers) to consolidate its relational workloads and achieve up to 75% improved performance.

Message Queue and Job Queue Built In

Every serious application eventually needs two things. A way to do work in the background, like sending emails, processing payments, resizing images, etc, and a way to work on a schedule, like daily backups, nightly analytics, subscription renewals, or weekly summary emails.

The default assumption is that you need dedicated tools for it — RabbitMQ, Kafka, Celery, Sidekiq, BullMQ. Another service to deploy. Another system to monitor. Another thing that can go wrong at the worst possible moment. Postgres handles both. Natively. It has a built-in notification system that lets your application listen for database events and respond in real time. Extensions like pgmq add proper message queues with delivery guarantees, retries, and dead-letter handling.

Extensions like pg_cron let you schedule recurring database jobs without any external cron infrastructure. Cloud providers like AWS RDS and Google Cloud SQL use this extension to let users schedule recurring SQL tasks directly in the database.

For a lot of teams, this eliminates the need for Celery, Sidekiq, RabbitMQ, or a separate job scheduler entirely. One system. Many responsibilities. All of it is working together. Supabase recently launched Supabase Queues, a managed message queue built directly on the pgmq extension for durable background task processing.

Full-Text Search(FTS) Without Elasticsearch

Postgres has a full-text search engine built right in. And it's more capable than most developers ever realise. It understands language. When someone searches for "running," it knows to also look for "run" and "runs" — because it stems words down to their root form automatically. It understands relevance. Results aren't just returned randomly — they're ranked, so the most meaningful match rises to the top.

It handles fuzzy matching, so a typo doesn't return zero results and leave your user staring at a blank page. You can search across multiple columns at once — title, body, tags, description — all weighted differently based on what matters most. AI and data-driven companies use PostgreSQL's Full-Text Search to combine traditional keyword matching(it's fast) with modern AI capabilities. This is done to create Hybrid Search Systems, where a user's query is filtered to exact words first(FTS) and then ranked by "semantics" using vector search.

You can index your search data so queries stay fast as your content grows to millions of records. Is it Elasticsearch? No. For truly complex search — advanced faceting, massive scale, machine learning ranking — Elasticsearch still has its place. But for the search feature in the vast majority of applications? Postgres handles it cleanly, without the operational overhead, without the syncing headaches, and without the extra infrastructure cost.

Analytics without a Separate Warehouse

Postgres handles analytical queries with elegance. Rolling averages, running totals, cohort analysis, time-based aggregations — these are things Postgres does well without you needing to pipe your data into a separate warehouse. For heavier workloads, the Citus extension transforms Postgres into a distributed analytical engine. But for a surprising number of products, vanilla Postgres already handles the analytics layer just fine.

Time-Series Data with TimescaleDB

Time-series data is completely different. It's a continuous stream of moments. A temperature sensor firing every second. A server reporting its CPU usage every ten seconds. A stock price ticking with every trade. A user's activity is being logged with every click. The data never stops arriving, every single record is tied to a specific point in time, and the history is just as important as the present.

This kind of data breaks the assumptions most databases are built around. Rows arrive in enormous, relentless bursts. Queries almost always filter by time range first. Old data needs to be compressed or expire automatically. Aggregations need to roll up across time windows efficiently. And the volume — millions of data points per day, billions per month — makes naive storage approaches collapse under their own weight.

TimescaleDB is a Postgres extension built specifically for this world.

It takes your regular Postgres table and quietly transforms it underneath into something purpose-built for time-series workloads. Data gets automatically partitioned into time-based chunks — recent data stays hot and fast, older data gets compressed automatically to a fraction of its original size. Rollups get precomputed in the background, so your dashboard queries return in milliseconds instead of grinding through raw data. Retention policies expire old data on a schedule so your storage doesn't grow forever.

Vector Search for AI — pgvector

This one matters a lot, especially right now. If you've built anything with AI in the last couple of years — a chatbot, a document assistant, a semantic search feature, a recommendation engine — you've dealt with embeddings. And if you've dealt with embeddings at scale, someone probably told you that you need a vector database. Pinecone. Weaviate. Qdrant. Chroma, etc. A whole new category of infrastructure, purpose-built for AI storage.

Let's back up for a second. When you send text through an AI model, it gets converted into a vector — a long list of numbers that captures the meaning of that text mathematically. Two sentences that mean the same thing will have vectors that are close to each other, even if the words are completely different. Semantic search, RAG, etc., follow this philosophy.

pgvector brings AI-native vector storage and similarity search directly into Postgres. Store your embeddings — from OpenAI, Cohere, or any other model — right next to your relational data. Then search by semantic similarity, filtered by any other condition you care about. The thing that makes this special: you can combine vector search with regular data filters in a single query. Find the most semantically similar documents for this specific user in this specific category created after this date. That kind of combined query is natural in Postgres. In dedicated vector databases, it's awkward. This is how modern AI apps should be built — and Postgres makes it straightforward.

Security at the Data Layer, Row-Level Security

Most apps handle authorisation in application code. Postgres lets you push security all the way down to the database itself. Row-Level Security means you define the rules once, directly in the database — "this user can only see their own rows, full stop" — and every single query from every single layer of your application automatically respects those rules. No exceptions. No forgotten middleware. No buggy endpoint that accidentally exposes too much. Even if your application code has a flaw and tries to fetch data, it shouldn't; the database simply won't return it.

Security isn't a layer you add on top. It's baked into the foundation. This is exactly how Supabase powers multi-tenant applications at scale — and it's robust, auditable, and surprisingly elegant.

Event Streaming with Logical Replication

Every insert, update, and delete in Postgres generates a structured event. Logical replication exposes that event stream to the outside world. This is the Change Data Capture pattern — and it's how you build event-driven architectures where your database becomes the authoritative source of truth, broadcasting every change to every downstream consumer in real time. No separate event store. No dual-write complexity. The database is the stream.

And There's Still More

Even after all of that, we haven't covered foreign data wrappers (query external systems like S3 or MySQL directly from Postgres), materialized views (precomputed query results that refresh automatically), triggers and stored procedures (business logic that lives at the database layer), or the hundreds of extensions in the ecosystem covering cryptography, graph queries, audit logging, and beyond. The extension ecosystem is one of Postgres's secret weapons. When you need something that it doesn't do natively, someone has almost certainly already built it.

Should You Replace Everything With Postgres?

Probably not everything. Redis at true hyperscale, Kafka for massive event throughput, dedicated search engines for extremely complex ranking — these tools earn their complexity when you genuinely hit the ceiling. But most teams add infrastructure before they need it. They pay the operational cost upfront and defer the benefit indefinitely. They're running five systems when one would have been fine. The smarter default: start with Postgres. Add other tools only when Postgres genuinely can't do the job. Nine times out of ten, it can.

The Bigger Idea

Stop thinking of Postgres as a relational database you use alongside a zoo of other tools. Think of it as a programmable data platform that happens to speak SQL. It stores your data, keeps it consistent, enforces your security model, queues your jobs, searches your content, streams your events, crunches your analytics, and understands your AI embeddings. It's been around since 1986. It's boring in the best possible way. It will still be here long after the current generation of "modern databases" has pivoted twice and been acqui-hired. Pick boring technology for your foundation. Save your complexity budget for the problems that actually make your product interesting. Postgres is enough. Build on it.