PostgreSQL vs SQLite: Which Database for Django?

Choosing the right database for your Django project is a critical decision. SQLite ships with Python and is great for development, but PostgreSQL is the production standard. This guide compares both and shows you how to set up PostgreSQL with Django.

Quick Comparison

When to Use SQLite

• Local development and prototyping
• Running tests (fast in-memory option)
• Single-user applications or embedded systems
• Small websites with low traffic
• When you need zero server setup

When to Use PostgreSQL

• Production deployments (always)
• Multiple concurrent users
• Complex queries and joins
• Full-text search requirements
• JSON data (PostgreSQL has native JSONB)
• Geospatial data (PostGIS)

Setting Up PostgreSQL

Install PostgreSQL

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib

# macOS (Homebrew)
brew install postgresql@16
brew services start postgresql@16

# Check it's running
sudo systemctl status postgresql

Create Database and User

# Switch to postgres user
sudo -u postgres psql

-- Create user and database
CREATE USER myproject_user WITH PASSWORD 'secure_password_here';
CREATE DATABASE myproject_db OWNER myproject_user;

-- Grant permissions
ALTER ROLE myproject_user SET client_encoding TO 'utf8';
ALTER ROLE myproject_user SET default_transaction_isolation TO 'read committed';
ALTER ROLE myproject_user SET timezone TO 'UTC';

\q  -- Exit psql

Install Python Adapter

pip install psycopg2-binary

Configure Django

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'myproject_db',
        'USER': 'myproject_user',
        'PASSWORD': 'secure_password_here',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

# Then run migrations
# python manage.py migrate

PostgreSQL-Specific Django Features

# models.py - PostgreSQL-specific fields
from django.contrib.postgres.fields import ArrayField
from django.db import models


class Product(models.Model):
    name = models.CharField(max_length=200)

    # Array field - store list of strings
    tags = ArrayField(
        models.CharField(max_length=50),
        default=list, blank=True
    )

    # JSON field (works on all DBs in Django 4+)
    metadata = models.JSONField(default=dict, blank=True)

# Querying arrays
Product.objects.filter(tags__contains=['sale'])
Product.objects.filter(tags__overlap=['new', 'featured'])

# Full-text search
from django.contrib.postgres.search import SearchVector
Product.objects.annotate(
    search=SearchVector('name', 'description')
).filter(search='django framework')

Backups

# Backup
pg_dump -U myproject_user myproject_db > backup.sql

# Backup (compressed)
pg_dump -U myproject_user -Fc myproject_db > backup.dump

# Restore
psql -U myproject_user myproject_db < backup.sql

# Automated daily backup (crontab)
0 3 * * * pg_dump -U myuser mydb | gzip > /backups/db_$(date +\%Y\%m\%d).sql.gz

Summary

• SQLite: Perfect for development, testing, and small single-user apps
• PostgreSQL: Required for production web applications
• PostgreSQL gives you arrays, JSONB, full-text search, and true concurrency
• Always use environment variables for database credentials
• Set up automated backups from day one