PostgreSQL

Introduction

If you've built a Django project beyond a weekend prototype, you've probably been told to "just use Postgres." Search that understands word stems. Flexible JSON columns. Rock-solid transactions. All of it comes from PostgreSQL, an open-source relational database that has been quietly powering serious applications for over 25 years.

The Problem PostgreSQL Solves

Every application needs somewhere to keep its data, and the choices tend to fall into a few uncomfortable camps:

• A file-based database like SQLite → great for prototypes, limited for concurrent writes and heavy workloads
• A NoSQL store → flexible, but you give up joins, constraints, and strong consistency
• Several specialized systems (one for search, one for JSON, one for queues) → powerful, but now you operate five things instead of one

This creates a different kind of disjointed experience. Data gets duplicated across systems, consistency guarantees disappear, and your deployment grows more complicated with every feature.

The PostgreSQL Approach

PostgreSQL takes the opposite stance. Instead of adding a new system for every new need, it gives you one reliable relational database that is extensible enough to cover most of them.

Core idea: Keep your data in well-structured tables with real constraints, and let the database do the heavy lifting: joins, transactions, indexing, searching, and even semi-structured data.

Simple Example

-- Create a table with real constraints
CREATE TABLE posts (
    id         SERIAL PRIMARY KEY,
    title      TEXT NOT NULL,
    summary    TEXT,
    published  BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Ask a question of it
SELECT title
FROM posts
WHERE published AND created_at > now() - interval '7 days'
ORDER BY created_at DESC;

The database enforces that every post has a title, stores timestamps with time zones correctly, and answers the query using whatever indexes you've defined. Your application never has to double-check any of it.

How This Blog Can Use PostgreSQL

A blog is a perfect small-scale showcase for what PostgreSQL does well. Here are three features that pay off immediately.

Full-Text Search

A basic icontains search matches raw substrings. PostgreSQL can do much better: it understands word stems, ignores filler words, and ranks results by relevance.

# views.py
from django.contrib.postgres.search import SearchQuery, SearchRank, SearchVector


def search(request):
    query = request.GET.get("q", "").strip()
    vector = SearchVector("title", weight="A") + SearchVector("summary", weight="B")
    search_query = SearchQuery(query)

    posts = (
        Post.published
        .annotate(rank=SearchRank(vector, search_query))
        .filter(rank__gt=0.0)
        .order_by("-rank")
    )
    return render(request, "blog/partials/post_cards.html", {"posts": posts})

What's happening:

• SearchVector combines the fields you want searchable, with title weighted above summary
• SearchQuery turns the user's input into a query PostgreSQL understands
• SearchRank scores each post so the best matches come first

Result: Searching for "running" also finds posts that mention "run" and "runs," all in a single query.

Flexible Data with JSONB

Sometimes a piece of data doesn't deserve its own table. PostgreSQL's JSONB type stores JSON in a binary format that you can index and query.

# models.py
class Post(models.Model):
    title = models.CharField(max_length=255)
    metadata = models.JSONField(default=dict, blank=True)


# Find posts where metadata says featured = true
Post.published.filter(metadata__featured=True)

You keep the safety of a relational schema for the core fields and the flexibility of a document store for the odd ones out.

Safe Writes with Transactions

Every change in PostgreSQL happens inside a transaction: either everything succeeds, or nothing does. Django makes this easy to use:

from django.db import transaction

with transaction.atomic():
    post = Post.objects.create(title="Hello", body_md="First post")
    post.tags.add(*selected_tags)
    # If anything above fails, the post is not saved at all.

Why This Matters

1. Correctness first — Constraints, foreign keys, and transactions mean bad data gets rejected at the door instead of discovered months later.

2. One system, many jobs — Full-text search, JSON documents, geospatial data (PostGIS), and even vector search (pgvector) live inside the same database. Fewer moving parts to deploy and monitor.

3. Scales further than you'd expect — Replication, partitioning, and a sophisticated query planner let a single well-tuned PostgreSQL server handle a surprising amount of traffic.

4. Open source and boring in the best way — No license fees, no vendor lock-in, a huge ecosystem, and a reputation for not losing your data.

Key PostgreSQL Features

PostgreSQL + Django

Django treats PostgreSQL as a first-class citizen. Setup is a few lines in your settings:

# settings.py
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "blog",
        "USER": "blog_user",
        "PASSWORD": os.environ["DB_PASSWORD"],
        "HOST": "localhost",
        "PORT": "5432",
    }
}

Add django.contrib.postgres to INSTALLED_APPS and you unlock the extra fields and lookups:

from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.search import SearchVectorField


class Post(models.Model):
    title = models.CharField(max_length=255)
    search_vector = SearchVectorField(null=True, editable=False)

    class Meta:
        indexes = [GinIndex(fields=["search_vector"])]

Flow:

1. A request arrives and the Django ORM builds a query
2. The psycopg driver sends it to PostgreSQL
3. The planner picks the cheapest route, using your indexes
4. Rows come back and Django turns them into model instances
5. Your template renders the result

Real-World Use Cases

Beyond a blog, PostgreSQL handles:

• Search — Product catalogs and documentation with ranked, stem-aware results
• Geospatial queries — "Find everything within 5 km" using the PostGIS extension
• Analytics — Window functions and aggregates for reports straight from your app database
• Job queues — SELECT ... FOR UPDATE SKIP LOCKED makes a simple, reliable queue
• AI features — Store and search embeddings with the pgvector extension
• Audit trails — Triggers and JSONB columns to record every change

Getting Started

1. Install PostgreSQL locally. Use your package manager, or run it in Docker:

   bash docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:17

2. Install the driver:

   bash pip install "psycopg[binary]"

3. Point Django at it and run python manage.py migrate.

4. Start small — Swap your search view to use SearchVector and see the difference.

5. Learn EXPLAIN ANALYZE — When a query feels slow, ask the database why instead of guessing.

The Philosophy

PostgreSQL isn't the flashiest tool in the stack, and that's the point. It's about recognizing that most applications need a dependable home for their data far more than they need a collection of specialized databases. PostgreSQL gives you correctness, flexibility, and room to grow without making you choose between them.

Start with one good database, learn it deeply, and add complexity only when you can prove you need it. The result is a system that's easier to reason about, easier to back up, and far more likely to still be running quietly years from now.

This post is part of a series on the tools behind this blog: Django, HTMX, Tailwind CSS, and now PostgreSQL. Posts are authored in the Django admin as Markdown and rendered to HTML on save.

Read more about the tech stack