PostgreSQL for Django Guide

PostgreSQL offers features beyond standard SQL. Django provides first-class PostgreSQL support for JSON, arrays, and full-text search. At ZIRA Software, PostgreSQL-specific features power complex healthcare applications.

Why PostgreSQL for Django?

PostgreSQL advantages:

• Native JSON support
• Full-text search without ElasticSearch
• Array and range fields
• Advanced indexing (GiST, GIN)
• Materialized views
• Window functions
• LISTEN/NOTIFY for real-time

Configuration

Install PostgreSQL adapter:

pip install psycopg2-binary

Configure Django:

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydatabase',
        'USER': 'myuser',
        'PASSWORD': 'mypassword',
        'HOST': 'localhost',
        'PORT': '5432',
        'OPTIONS': {
            'options': '-c search_path=public',
        },
    }
}

JSON Fields

Store and query JSON data:

# models.py
from django.contrib.postgres.fields import JSONField
from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=200)
    metadata = JSONField(default=dict)
    specifications = JSONField(default=dict)

    class Meta:
        db_table = 'products'

Query JSON fields:

# Find products with specific metadata
products = Product.objects.filter(
    metadata__color='blue'
)

# Nested queries
products = Product.objects.filter(
    specifications__dimensions__width__gte=100
)

# JSON array contains
products = Product.objects.filter(
    metadata__tags__contains=['electronics', 'sale']
)

# Get specific JSON value
from django.db.models import Q

products = Product.objects.filter(
    Q(metadata__has_key='discount') | Q(specifications__has_key='warranty')
)

Array Fields

Store arrays natively:

from django.contrib.postgres.fields import ArrayField

class Article(models.Model):
    title = models.CharField(max_length=200)
    tags = ArrayField(models.CharField(max_length=50), default=list)
    ratings = ArrayField(models.IntegerField(), default=list)

Query arrays:

# Contains any
articles = Article.objects.filter(tags__overlap=['django', 'python'])

# Contains all
articles = Article.objects.filter(tags__contains=['django', 'tutorial'])

# Array length
articles = Article.objects.filter(tags__len__gte=3)

# Index access
articles = Article.objects.filter(tags__0='django')

Full-Text Search

Configure search:

from django.contrib.postgres.search import SearchVector, SearchQuery, SearchRank

class BlogPost(models.Model):
    title = models.CharField(max_length=200)
    content = models.TextField()

    def save(self, *args, **kwargs):
        # Update search vector on save
        super().save(*args, **kwargs)

Basic search:

# Search in multiple fields
posts = BlogPost.objects.annotate(
    search=SearchVector('title', 'content'),
).filter(search='django postgresql')

Weighted search:

# Give more weight to title matches
from django.contrib.postgres.search import SearchVector

posts = BlogPost.objects.annotate(
    search=SearchVector('title', weight='A') + SearchVector('content', weight='B')
).filter(search='django')

Ranked search:

# Order by relevance
search_query = SearchQuery('django postgresql')
search_vector = SearchVector('title', weight='A') + SearchVector('content', weight='B')

posts = BlogPost.objects.annotate(
    rank=SearchRank(search_vector, search_query)
).filter(rank__gte=0.3).order_by('-rank')

Search configuration:

# Language-specific search (stemming, stop words)
from django.contrib.postgres.search import SearchVector

posts = BlogPost.objects.annotate(
    search=SearchVector('title', 'content', config='english')
).filter(search=SearchQuery('running', config='english'))
# Matches: run, running, runs

Range Fields

Store ranges efficiently:

from django.contrib.postgres.fields import IntegerRangeField, DateRangeField
from django.db import models

class Event(models.Model):
    name = models.CharField(max_length=200)
    age_range = IntegerRangeField()
    dates = DateRangeField()

# Create event
from django.db.models import Q
from psycopg2.extras import NumericRange, DateRange
from datetime import date

event = Event.objects.create(
    name='Youth Workshop',
    age_range=NumericRange(13, 18),
    dates=DateRange(date(2019, 4, 1), date(2019, 4, 5))
)

# Query ranges
# Events for 15-year-olds
events = Event.objects.filter(age_range__contains=15)

# Events in April 2019
events = Event.objects.filter(
    dates__contains=date(2019, 4, 15)
)

# Overlapping date ranges
events = Event.objects.filter(
    dates__overlap=DateRange(date(2019, 4, 1), date(2019, 4, 10))
)

Advanced Indexing

GIN index for JSON:

from django.contrib.postgres.indexes import GinIndex

class Product(models.Model):
    name = models.CharField(max_length=200)
    metadata = JSONField(default=dict)

    class Meta:
        indexes = [
            GinIndex(fields=['metadata']),
        ]

GiST index for ranges:

from django.contrib.postgres.indexes import GistIndex

class Event(models.Model):
    name = models.CharField(max_length=200)
    dates = DateRangeField()

    class Meta:
        indexes = [
            GistIndex(fields=['dates']),
        ]

Partial indexes:

from django.db.models import Index, Q

class Article(models.Model):
    title = models.CharField(max_length=200)
    published = models.BooleanField(default=False)

    class Meta:
        indexes = [
            # Index only published articles
            Index(
                fields=['title'],
                name='published_title_idx',
                condition=Q(published=True)
            ),
        ]

Aggregates and Window Functions

PostgreSQL-specific aggregates:

from django.contrib.postgres.aggregates import ArrayAgg, JSONBAgg, StringAgg

# Aggregate tags into array
from django.db.models import Count

articles = Article.objects.values('author').annotate(
    all_tags=ArrayAgg('tags', distinct=True),
    tag_count=Count('tags')
)

# String aggregation
authors = Author.objects.annotate(
    article_titles=StringAgg('articles__title', delimiter=', ')
)

Window functions:

from django.db.models import F, Window
from django.db.models.functions import RowNumber, Rank

# Add row numbers within partition
articles = Article.objects.annotate(
    row_number=Window(
        expression=RowNumber(),
        partition_by=[F('category')],
        order_by=F('published_at').desc()
    )
).filter(row_number__lte=5)  # Top 5 per category

Performance Optimization

Explain queries:

# Analyze query performance
articles = Article.objects.filter(title__icontains='django')
print(articles.explain())

# Detailed analysis
print(articles.explain(analyze=True, verbose=True))

Connection pooling:

pip install django-db-geventpool

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django_db_geventpool.backends.postgresql_psycopg2',
        'ATOMIC_REQUESTS': False,
        'CONN_MAX_AGE': 0,
        'OPTIONS': {
            'MAX_CONNS': 20,
        }
    }
}

Query optimization tips:

# 1. Use select_related for foreign keys
articles = Article.objects.select_related('author').all()

# 2. Use prefetch_related for many-to-many
articles = Article.objects.prefetch_related('tags').all()

# 3. Index frequently queried fields
class Article(models.Model):
    published_at = models.DateTimeField(db_index=True)

# 4. Use only() to fetch specific fields
articles = Article.objects.only('title', 'published_at')

# 5. Use defer() to exclude large fields
articles = Article.objects.defer('content')

Materialized Views

Create with raw SQL:

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("""
        CREATE MATERIALIZED VIEW article_stats AS
        SELECT
            author_id,
            COUNT(*) as article_count,
            AVG(view_count) as avg_views
        FROM articles
        GROUP BY author_id
    """)

# Refresh materialized view
with connection.cursor() as cursor:
    cursor.execute("REFRESH MATERIALIZED VIEW article_stats")

Conclusion

PostgreSQL's advanced features make Django applications more powerful. JSON fields, full-text search, and array fields eliminate need for external services in many cases.

Building data-intensive Django applications? Contact ZIRA Software for PostgreSQL optimization.