PostgreSQL vs SQLite: Which Database for Django?

Django works with several databases, but two come up constantly for newcomers: SQLite, the zero-configuration default that ships with Python, and PostgreSQL, the powerful open-source database that production Django apps overwhelmingly run on. Knowing when each is the right choice — and why the answer is almost always "SQLite for development, PostgreSQL for production" — saves you from both over-engineering a side project and under-provisioning a real application.

Why the choice matters

Your database is the foundation your application's data sits on, and switching later, while possible, is friction you would rather avoid. The good news is that Django's ORM abstracts most database differences, so your models and queries look the same regardless of the backend — which is exactly why SQLite is so convenient for getting started and why moving to PostgreSQL for production is usually smooth. Understanding the strengths and limits of each lets you start quickly without painting yourself into a corner, and choose deliberately rather than by accident when it is time to deploy something real.

What SQLite is

SQLite is a serverless, file-based database: the entire database is a single file on disk, and there is no separate database server process to run. It comes built into Python, so a new Django project uses it with zero configuration — you can start building immediately. This simplicity is its defining feature and its great strength for development, prototyping, and learning. There is nothing to install, configure, or manage; the database just works as a file, which removes a whole layer of setup that would otherwise stand between a beginner and a running application.

Step by step: a new Django project on SQLite

The easiest way to understand "zero configuration" is to watch it happen. These commands create a project called myproject inside a virtual environment — a private folder of Python packages, so this project's Django version cannot clash with anything else. On Windows, activate with .venv\Scripts\activate instead.

mkdir myproject && cd myproject
python3 -m venv .venv
source .venv/bin/activate
python -m pip install "Django>=5.2,<6.0"
django-admin startproject myproject .
python manage.py migrate
python manage.py runserver

Notice what you did not do: install a database, create a user, or type a password. The migrate command created db.sqlite3 next to manage.py and built the tables Django's built-in apps need inside it. The configuration responsible is in myproject/settings.py:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "db.sqlite3",
    }
}

ENGINE tells Django which backend to load, and for SQLite NAME is simply the file path. There is no host, port or password because there is no server: Django opens the file directly. Two habits follow from this design: add db.sqlite3 to .gitignore (it is data, not code), and when you want a clean slate early on, delete the file and run migrate again.

SQLite's strengths

SQLite shines wherever simplicity matters most. It is perfect for local development, where its zero setup lets you focus on building rather than configuring infrastructure. It is excellent for learning Django, for prototypes, for small single-user tools, and for running tests quickly. Because the whole database is one file, it is trivial to copy, reset, or share. For a great many development scenarios and small applications, SQLite is not a compromise but exactly the right tool — lightweight, reliable, and friction-free, doing precisely what is needed without any operational overhead.

SQLite's limitations

SQLite's simplicity comes from being file-based and serverless, and that is also the source of its limits. It handles concurrent writes poorly — because it locks the whole database file for a write, multiple users writing at once contend, which does not scale to a busy multi-user web application. It lacks some advanced features and types that PostgreSQL offers, and it is not designed for the concurrent, high-traffic access pattern of a production web service. These are not flaws so much as the natural boundary of its design: SQLite is built for simplicity and single-writer scenarios, not for serving many simultaneous users writing data.

What "database is locked" looks like

When two connections try to write at once, the second waits. If it waits longer than the timeout (five seconds by default), Django raises django.db.utils.OperationalError: database is locked. You will rarely see it on a laptop; it typically appears when a background job writes in a long transaction while web requests also write, or when a burst of traffic submits forms at the same time.

Tuning SQLite if you stay on it

A small internal tool or personal blog can reasonably stay on SQLite in production. Django 5.1 and later let you tune it through OPTIONS, which removes most of the pain for light workloads:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "db.sqlite3",
        "OPTIONS": {
            "transaction_mode": "IMMEDIATE",  # take the write lock up front
            "timeout": 20,                    # wait up to 20 s for the lock
            "init_command": "PRAGMA journal_mode=WAL;PRAGMA synchronous=NORMAL;",
        },
    }
}

WAL (write-ahead logging) lets readers keep reading while one writer writes, and IMMEDIATE makes writers queue in an orderly way instead of failing halfway through a transaction. These settings raise the ceiling but do not remove it — there is still only one writer at a time — and they assume a local disk. Never put a SQLite file on a network share used by several machines; if you need more than one application server, that alone is a reason to move to PostgreSQL.

Quirks that surprise people later

SQLite is also more relaxed than PostgreSQL in ways that do not hurt on day one but bite when you switch:

• String lengths are not enforced. A CharField(max_length=100) column will happily store 500 characters if a script or the shell bypasses form validation. PostgreSQL rejects such values.
• contains and startswith ignore case for ASCII letters. On SQLite, filter(title__contains="django") also matches "Django"; on PostgreSQL it does not, and you need icontains.
• Altering tables is limited. For many schema changes Django quietly rebuilds the whole table, which is slow for large tables.

What PostgreSQL is

PostgreSQL is a full-featured, open-source relational database that runs as a separate server process designed for concurrent, multi-user access. It is robust, standards-compliant, extensible, and trusted for serious production workloads across the industry. Unlike SQLite, it requires installation and configuration and runs as a service your application connects to over a connection, which is more setup but unlocks far greater capability. PostgreSQL is the database that the overwhelming majority of production Django applications use, and Django's PostgreSQL support is first-class, exposing many of its advanced capabilities directly through the ORM.

Setting up PostgreSQL step by step

Because PostgreSQL is a separate server program, setup has four stages on any operating system: install the server, create a database and user, install a Python driver, and point Django at the database.

Stage 1: install and start the server

Django 5.2 requires PostgreSQL 14 or newer; the examples use 17.

• Debian or Ubuntu: sudo apt install postgresql — the server starts automatically, owned by a system user called postgres.
• macOS with Homebrew: brew install postgresql@17, then brew services start postgresql@17.
• Windows: the official installer sets up a service and the psql client, and asks for a postgres superuser password — write it down.
• Docker: run the server in an isolated container, as below.

docker run --name myproject-db \
    -e POSTGRES_USER=myproject_user \
    -e POSTGRES_PASSWORD=change-me-locally \
    -e POSTGRES_DB=myproject \
    -p 127.0.0.1:5432:5432 \
    -v myproject-pgdata:/var/lib/postgresql/data \
    -d postgres:17

The POSTGRES_* variables create the user and database for you, so Docker users can skip Stage 2. The port is published only on 127.0.0.1, and the named volume keeps your data when the container is removed.

Stage 2: create a database and a dedicated user

Your app should not use the all-powerful postgres superuser. Create a normal role that owns only your project's database. Open a SQL prompt (sudo -u postgres psql on Linux, psql elsewhere) and type:

CREATE ROLE myproject_user WITH LOGIN PASSWORD 'choose-a-strong-password';
CREATE DATABASE myproject OWNER myproject_user;
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';

Making the role the database OWNER matters since PostgreSQL 15, which no longer lets ordinary users create tables in the public schema of a database they do not own. The ALTER ROLE lines set the defaults Django expects, which saves setup queries on every new connection. Type \q to leave psql.

Stage 3: install the Python driver

Django talks to PostgreSQL through a driver. New projects should use psycopg 3 (Django 5.2 still supports the older psycopg2). With your virtual environment active:

python -m pip install "psycopg[binary]>=3.2,<4.0"

The [binary] extra ships a pre-compiled build, so no C compiler is needed. Add the same line to requirements.txt so your server installs it too.

Stage 4: point Django at PostgreSQL

Passwords do not belong in settings.py, which is committed to version control, so read connection details from environment variables. This version keeps SQLite as the default and switches only when you ask, which is convenient while learning:

import os

if os.environ.get("DB_ENGINE") == "postgresql":
    DATABASES = {
        "default": {
            "ENGINE": "django.db.backends.postgresql",
            "NAME": os.environ.get("DB_NAME", "myproject"),
            "USER": os.environ.get("DB_USER", "myproject_user"),
            "PASSWORD": os.environ["DB_PASSWORD"],
            "HOST": os.environ.get("DB_HOST", "127.0.0.1"),
            "PORT": os.environ.get("DB_PORT", "5432"),
        }
    }
else:
    DATABASES = {
        "default": {
            "ENGINE": "django.db.backends.sqlite3",
            "NAME": BASE_DIR / "db.sqlite3",
        }
    }

DB_PASSWORD deliberately has no default: if you select PostgreSQL but forget it, Django fails at once with a KeyError naming the variable. Set the variables (in PowerShell, $env:DB_ENGINE = "postgresql") and create the schema:

export DB_ENGINE=postgresql
export DB_PASSWORD='choose-a-strong-password'
python manage.py migrate
python manage.py shell -c "from django.db import connection; print(connection.vendor)"

The last line should print postgresql. If it prints sqlite, the variable is not set in the shell you ran it from. The most common errors at this stage:

PostgreSQL's strengths

PostgreSQL handles exactly what SQLite cannot: many users reading and writing concurrently, which is the defining requirement of a production web application. It offers advanced data types and features — rich JSON support, full-text search, powerful indexing, robust constraints — that let you do more in the database. It is built for reliability, data integrity, and scale, with a track record of running demanding applications. For any application that will serve real users in production, these strengths — concurrency, advanced features, and proven robustness — are precisely what you need, and they are why PostgreSQL is the standard choice for serious Django deployments.

The standard recommendation

For most developers, the answer is clear and worth stating plainly: use SQLite for local development and PostgreSQL for production. SQLite gets you building instantly with no setup, and because Django's ORM abstracts the differences, your code works the same on both. When you deploy to production, you switch to PostgreSQL for its concurrency and robustness. This pairing gives you the best of both — frictionless development and a production-grade database where it counts — and it is the path the vast majority of Django projects follow. Starting on SQLite is not a mistake to correct but a sensible default to begin with.

Keeping development and production aligned

There is one nuance worth knowing: while Django's ORM smooths over most differences, the two databases are not identical, and subtle behaviors can differ. For this reason, teams working on serious applications sometimes choose to develop against PostgreSQL too, so that development matches production exactly and database-specific issues surface early rather than at deployment. This is a reasonable choice as a project matures, especially when you start using PostgreSQL-specific features. The tradeoff is a little more local setup in exchange for closer dev-production parity, and which way you lean depends on how much you rely on features unique to PostgreSQL.

Differences you can actually observe

These are the differences that most often make a test pass on one database and fail on the other:

Two habits protect you against most of these on either backend: add an explicit order_by() (or Meta.ordering) whenever order matters, and choose the case-sensitivity you mean — icontains for user-facing search.

Switching from SQLite to PostgreSQL

Because Django abstracts the database, switching is mostly a configuration change — pointing your settings at a PostgreSQL database instead of the SQLite file — plus migrating your data if you have any worth keeping. For a fresh production deployment you simply configure PostgreSQL and run your migrations against it to create the schema. The ORM-level code you wrote does not change. This smooth path is exactly why starting on SQLite carries little risk: the move to PostgreSQL when you are ready is a deployment detail, not a rewrite, as long as you have not relied on behavior specific to SQLite.

Worked example: moving an existing project from SQLite to PostgreSQL

Sooner or later many beginners have a SQLite project that already holds real content — posts, user accounts, orders — and want to keep it. This walk-through moves a project called myproject with a blog app, using only Django's own tools: export the data to database-neutral JSON with dumpdata, build an empty schema in PostgreSQL with migrate, then import the JSON with loaddata.

Step 1: bring SQLite up to date and stop writes

The export assumes your models match the tables, so apply pending migrations first with python manage.py migrate and check python manage.py showmigrations shows no unticked [ ] entries. For a live site, also put it into maintenance mode now: anything written to SQLite after the export will not reach PostgreSQL.

Step 2: back up the SQLite file

sqlite3 db.sqlite3 ".backup db-before-migration.sqlite3"

SQLite's .backup command produces a consistent copy even if something is still using the file (a plain copy is fine once the app is stopped). Keep it until the new database has run happily for a while; it is your undo button.

Step 3: export the data to JSON

python manage.py dumpdata \
    --natural-foreign --natural-primary \
    --exclude contenttypes --exclude auth.permission \
    --exclude sessions \
    --indent 2 --output data.json

The flags matter:

• Excluding contenttypes and auth.permission: migrate recreates these rows automatically on the new database, often with different ids, so importing them too would collide.
• --natural-foreign and --natural-primary: instead of "permission 27", Django writes references by name, such as ["change_post", "blog", "post"], so user and group permissions reconnect correctly whatever the new ids are.
• Excluding sessions: sessions are temporary; users simply log in again.

Step 4: create the PostgreSQL schema

Create the role and database as in the setup section, set DB_ENGINE=postgresql and DB_PASSWORD, and run python manage.py migrate against the empty database. Do not run createsuperuser: your existing admin accounts are in data.json, and a new one could clash with a username you are about to import.

Step 5: import the data

python manage.py loaddata data.json

Success looks like Installed 1843 object(s) from 1 fixture(s). The import runs in one transaction, so a failure leaves nothing half-loaded and you can fix the problem and retry. loaddata also resets PostgreSQL's id sequences afterwards, so the next new post gets an id above the highest imported one.

Step 6: verify that every row arrived

"No errors" is not the same as "all the data is there". Temporarily add the backup as a second database alias called legacy:

# settings.py (temporary)
DATABASES["legacy"] = {
    "ENGINE": "django.db.backends.sqlite3",
    "NAME": BASE_DIR / "db-before-migration.sqlite3",
}

Then save this as verify_migration.py and run python manage.py shell < verify_migration.py:

from django.apps import apps

SKIP = {"contenttypes.contenttype", "auth.permission", "sessions.session"}

for model in apps.get_models():
    label = model._meta.label_lower
    if label in SKIP or model._meta.proxy or not model._meta.managed:
        continue
    old = model._base_manager.using("legacy").count()
    new = model._base_manager.using("default").count()
    status = "ok" if old == new else "MISMATCH"
    print(f"{status:8} {label:40} sqlite={old} postgres={new}")

It counts rows per model in both databases. _base_manager is used instead of objects so custom managers that hide rows (for example, unpublished posts) do not distort the counts. Every line should say ok. Then log in to the admin with an existing account (proving passwords and permissions came across), open a few records with relations, and create one new object to confirm the sequences work. Remove the legacy alias afterwards, and keep data.json private — it contains user data, including password hashes.

When the data migration fails

PostgreSQL refuses data that SQLite quietly accepted, so problems surface during the import rather than months later. The common ones:

"value too long for type character varying"

A CharField holds more than its max_length. The error does not name the row, so find it on the SQLite side:

from django.db.models.functions import Length
from blog.models import Post

limit = Post._meta.get_field("title").max_length
print(list(
    Post.objects.using("legacy")
    .annotate(n=Length("title")).filter(n__gt=limit)
    .values_list("pk", "n")
))

Either shorten those values in SQLite and export again, or raise max_length, create and apply the migration, and redo the export — depending on whether the long values are legitimate.

Foreign key and duplicate key errors

violates foreign key constraint means a row points at one that no longer exists, such as a comment whose post was deleted by hand or by raw SQL. Find orphans with a query like Comment.objects.using("legacy").exclude(post_id__in=Post.objects.using("legacy").values("pk")), then delete or reattach them. A duplicate key on content types or permissions means the dump missed the Step 3 flags. A duplicate on one of your own models usually means a data migration seeded rows during migrate; exclude that model with another --exclude app_label.ModelName or delete the seeded rows before loading.

Large databases and changed results

For hundreds of thousands of rows, dump and load one app at a time (auth first, then apps that reference users) and rehearse on a copy. If the import succeeds but a search suddenly finds less, check for the case-sensitivity and ordering differences in the table above.

How Django's ORM smooths the difference

The reason you can comfortably develop on one database and deploy on another is Django's ORM, which sits between your code and the database and generates the appropriate SQL for whichever backend you configure. Your models, queries, and migrations are written once and work across supported databases, so switching is largely a configuration change rather than a rewrite. This abstraction is what makes the "SQLite for development, PostgreSQL for production" pattern practical, because the code you write does not care which database is underneath. Understanding that the ORM is doing this translation explains why the switch is smooth and why your application logic stays the same across both.

Why concurrency is the deciding factor

The single most important practical difference is how the two handle many users at once. SQLite locks the whole database file during a write, so concurrent writes queue up — fine for one developer or a single-user tool, but a bottleneck for a web application serving many simultaneous users. PostgreSQL is built for exactly that concurrent access, handling many readers and writers at once without the file-level locking. This is the core reason production web apps use PostgreSQL: a real web service has concurrent users writing data, and that is precisely the workload SQLite is not designed for and PostgreSQL is.

Features that unlock more in PostgreSQL

Beyond concurrency, PostgreSQL offers capabilities that let you do more within the database. Rich JSON support lets you store and query flexible structured data; full-text search lets you build search features without a separate engine; advanced indexing keeps queries fast at scale; and strong constraint support lets the database enforce data integrity rules. Django exposes many of these through the ORM. As an application grows and its data needs become more sophisticated, these features become genuinely useful, and they are part of why PostgreSQL is not just "SQLite that handles concurrency" but a more capable database that production applications grow into.

What these features look like in code

Here is a small model using several of them. For the PostgreSQL-specific pieces, add "django.contrib.postgres" to INSTALLED_APPS.

from django.contrib.postgres.fields import ArrayField
from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.search import SearchQuery, SearchVector
from django.db import models


class Article(models.Model):
    title = models.CharField(max_length=200)
    body = models.TextField()
    tags = ArrayField(models.CharField(max_length=40), default=list, blank=True)
    metadata = models.JSONField(default=dict, blank=True)

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


Article.objects.filter(metadata__source="newsletter")  # SQLite and PostgreSQL
Article.objects.filter(tags__contains=["django"])      # PostgreSQL only
Article.objects.annotate(
    search=SearchVector("title", "body")
).filter(search=SearchQuery("database migration"))    # PostgreSQL only

JSONField works on every backend Django supports, so it does not tie you to PostgreSQL. ArrayField, GinIndex and the search classes do: the migration that creates an ArrayField fails outright on SQLite. The moment you add one is exactly when the earlier advice about developing on PostgreSQL stops being optional.

Databases in testing

One place SQLite's speed is genuinely valuable even for PostgreSQL-based projects is testing, where its fast, in-memory operation can make a test suite run quickly. However, testing against a different database than production risks missing database-specific issues, so many teams test against PostgreSQL to match production exactly, accepting slightly slower tests for higher confidence. The tradeoff is speed versus fidelity, and the right choice depends on how much you rely on PostgreSQL-specific behavior. Being aware of this consideration helps you decide deliberately how your tests should be configured, rather than defaulting to whatever is fastest and being surprised by a production-only bug.

Running your tests against PostgreSQL

python manage.py test never touches your real data: it creates a separate test database, runs migrations there, and destroys it afterwards. On SQLite that database lives in memory; on PostgreSQL Django creates a real one named test_myproject, so the development role needs permission to create databases (never grant this to the production role):

ALTER ROLE myproject_user CREATEDB;

Then python manage.py test --keepdb reuses the test database between runs instead of replaying every migration, and --parallel auto spreads test classes across CPU cores. A common compromise is quick SQLite runs while coding and a full PostgreSQL run in continuous integration — and when the two disagree, trust PostgreSQL, because production will behave like it.

Making the decision simply

For most situations the decision is genuinely simple, and it is worth not overthinking it. If you are learning, prototyping, or building something small and single-user, SQLite is perfect and you need nothing more. If you are building a web application that will serve real users in production, use PostgreSQL there. Develop on SQLite for its zero-setup convenience and deploy on PostgreSQL for its production strengths, or develop on PostgreSQL too if you want exact parity. The framework supports all of these smoothly, so the choice is low-risk — start with what gets you building and move to PostgreSQL when you deploy something real.

Beyond these two

While SQLite and PostgreSQL are the two that matter most for getting started, Django supports other databases too, such as MySQL and MariaDB, which are also used in production. The reason PostgreSQL is so often recommended in the Django community is its strong feature set, excellent Django support, and reliability, but the broader point is that Django's database abstraction gives you choices. For most developers the SQLite-for-development, PostgreSQL-for-production path is the well-trodden default, but knowing that other production databases exist and are supported rounds out the picture, and reinforces that Django's ORM is designed to work across databases so your application is not tightly bound to any single one.

Two production habits to start with

Once real users depend on PostgreSQL, two small habits prevent most beginner surprises. First, reuse connections: by default Django opens a new connection for every request. CONN_MAX_AGE keeps connections open between requests, and CONN_HEALTH_CHECKS checks a reused connection is still alive, so a database restart does not cause a burst of errors:

DATABASES["default"]["CONN_MAX_AGE"] = 60
DATABASES["default"]["CONN_HEALTH_CHECKS"] = True

Second, back up — and prove the backup restores. pg_dump takes a consistent snapshot while the site keeps running:

pg_dump --format=custom --file=/srv/app/backups/myproject.dump myproject
createdb myproject_restore_test
pg_restore --dbname=myproject_restore_test --no-owner /srv/app/backups/myproject.dump

Schedule the dump, copy it to another machine, and test a restore now and then. Finally, keep port 5432 closed to the internet: leave PostgreSQL listening on localhost when it shares a server with Django, or allow only the application server's private address (such as 10.0.0.5) in both the firewall and pg_hba.conf.

Summary

SQLite and PostgreSQL serve different roles in a Django project, and the standard, sensible choice is SQLite for development and PostgreSQL for production. SQLite is a zero-configuration, file-based database built into Python — perfect for development, learning, prototypes, and small tools, but limited by poor write concurrency that makes it unsuitable for busy multi-user production. PostgreSQL is a full-featured server-based database built for concurrent access, advanced features, and reliability at scale, which is why production Django runs on it. Django's ORM abstracts most differences, so you can develop quickly on SQLite and deploy to PostgreSQL with mainly a configuration change. As a project matures and uses PostgreSQL-specific features, developing against PostgreSQL too keeps dev and production aligned. Start simple, deploy robust — that is the path almost every Django application should follow.