Introduction to PostgreSQL: How It Works and Why It Is Used

PostgreSQL is a relational database management system.

It is used to store, organize, query, and protect application data.

A Django application, for example, may use PostgreSQL to store:

• users
• articles
• products
• orders
• payments
• comments
• permissions
• application settings

A simple application architecture might look like:

1
2
3
4
5

Browser
   ↓
Django application
   ↓
PostgreSQL

Django handles the application logic.

PostgreSQL stores the persistent data.

If the application server restarts, the database still keeps its records.

That persistence is one of the main reasons databases exist.

What Is PostgreSQL?

PostgreSQL is an open-source relational database management system, often shortened to:

1

Postgres

It organizes data primarily into tables.

A simple users table might look conceptually like:

1
2
3
4
5
6

users

id | username | email
---+----------+------------------
1  | alex     | alex@example.com
2  | sam      | sam@example.com

An articles table might contain:

1
2
3
4
5
6

articles

id | title               | author_id
---+---------------------+----------
1  | Learning Django     | 1
2  | PostgreSQL Basics   | 2

The value:

1

author_id

can reference a row in the users table.

This creates a relationship between the data.

Why Applications Need a Database

A Python variable exists only while the process is running.

For example:

1
2
3
4

users = [
    "alex",
    "sam",
]

If the application process stops, that in-memory data disappears unless it was saved somewhere else.

A database provides persistent storage.

Conceptually:

1
2
3
4
5

Application memory
    Temporary

Database
    Persistent

A database also provides much more than file storage.

It can:

• search records
• sort results
• enforce relationships
• prevent invalid data
• update many records safely
• handle concurrent users
• manage transactions
• control access
• recover from failures

PostgreSQL Is a Database Server

PostgreSQL usually runs as its own server process.

An application connects to it.

For example:

1
2
3
4
5
6
7

Django process
      ↓
Database connection
      ↓
PostgreSQL server
      ↓
Database files

The application does not normally edit PostgreSQL's data files directly.

Instead, it sends database commands.

For example:

1
2

SELECT *
FROM articles;

PostgreSQL processes the query and returns the matching rows.

Databases, Tables, Rows, and Columns

A PostgreSQL server can contain multiple databases.

A database contains objects such as:

• tables
• indexes
• views
• functions
• sequences
• schemas

A table organizes related records.

Example:

1
2
3
4
5

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

This table has three columns:

1
2
3

id
name
price

A row represents one product.

1
2
3
4

id | name       | price
---+------------+------
1  | Keyboard   | 89.99
2  | Mouse      | 39.99

PostgreSQL Uses SQL

PostgreSQL is primarily controlled through SQL.

SQL stands for:

1

Structured Query Language

It is used to:

• create tables
• retrieve data
• insert data
• update data
• delete data
• create indexes
• define constraints
• manage transactions

Retrieve data:

1
2

SELECT id, name, price
FROM products;

Insert data:

1
2
3
4
5
6
7
8

INSERT INTO products (
    name,
    price
)
VALUES (
    'Keyboard',
    89.99
);

Update data:

1
2
3

UPDATE products
SET price = 79.99
WHERE id = 1;

Delete data:

1
2

DELETE FROM products
WHERE id = 1;

These four operations correspond broadly to:

1
2
3
4

Create
Read
Update
Delete

often shortened to:

1

CRUD

How PostgreSQL Processes a Query

Suppose an application sends:

1
2
3

SELECT *
FROM products
WHERE price < 100;

A simplified internal flow is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

Application sends SQL
        ↓
PostgreSQL parses the query
        ↓
PostgreSQL validates names and syntax
        ↓
Query planner evaluates possible strategies
        ↓
Executor runs the selected plan
        ↓
Matching rows are found
        ↓
Results are returned

The query planner is an important part of PostgreSQL.

It decides how to retrieve the requested data efficiently.

Parsing

PostgreSQL first needs to understand the SQL.

For example:

1
2
3

SELECT name
FROM products
WHERE price < 100;

It checks that:

• the syntax is valid
• the table exists
• the columns exist
• the user has permission
• the data types are compatible

If the SQL is invalid:

1
2

SELCT *
FROM products;

PostgreSQL returns an error instead of running it.

The Query Planner

There may be several ways to execute the same query.

Suppose a table contains one million products.

For:

1
2
3

SELECT *
FROM products
WHERE id = 500000;

PostgreSQL might:

1
2
3
4
5

Option 1
    Scan every row

Option 2
    Use an index

If an appropriate index exists, PostgreSQL will often choose the index.

The query planner estimates the cost of different approaches and selects a query plan.

Sequential Scans

A sequential scan means PostgreSQL reads rows from the table in sequence.

Conceptually:

1
2
3
4
5

Row 1
Row 2
Row 3
...
Row 1,000,000

Sequential scans are not automatically bad.

For example, if a query needs most rows in a table:

1
2

SELECT *
FROM products;

reading the table sequentially may be efficient.

Indexes

An index is an additional data structure that helps PostgreSQL locate records more quickly.

For example:

1
2

CREATE INDEX products_name_idx
ON products(name);

Now a query such as:

1
2
3

SELECT *
FROM products
WHERE name = 'Keyboard';

may use the index rather than scanning every row.

Conceptually:

1
2
3
4
5

Without index

Search table row by row
        ↓
Find matching record

With an index:

1
2
3
4
5

Index lookup
    ↓
Matching row location
    ↓
Read record

Indexes Have a Cost

Indexes improve some reads, but they are not free.

They require:

• additional disk space
• additional memory
• maintenance during writes

When a row is inserted:

1
2
3

Insert table row
        +
Update relevant indexes

When a row is changed:

1
2
3

Update table
        +
Possibly update indexes

Adding an index to every column usually makes little sense.

Indexes should support real query patterns.

Primary Keys

A primary key uniquely identifies a row.

Example:

1
2
3
4

CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);

The primary key ensures values in:

1

id

are unique.

So this is valid:

1
2
3

1
2
3

but duplicate primary keys are not allowed.

Applications commonly use the primary key to retrieve one object:

1
2
3

SELECT *
FROM articles
WHERE id = 42;

Foreign Keys

A foreign key creates a relationship between tables.

Example:

1
2
3
4
5
6

CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id BIGINT NOT NULL
        REFERENCES users(id)
);

Now:

1

articles.author_id

must reference a valid user.

This helps PostgreSQL enforce data integrity.

Without the foreign key, the database might allow:

1

author_id = 999999

even when no such user exists.

Relational Data

Relational databases are especially useful when different types of information are connected.

Example:

1
2
3
4
5

User
   ↓
Article
   ↓
Comment

Tables might look like:

1
2
3

users
articles
comments

Relationships connect them.

For example:

1
2
3
4
5
6
7
8

Article.author_id
    → User.id

Comment.article_id
    → Article.id

Comment.author_id
    → User.id

This allows complex questions such as:

1
2

Give me all comments written by Alex
on articles published this month.

Joins

SQL joins combine related rows from several tables.

Example:

1
2
3
4
5
6

SELECT
    articles.title,
    users.username
FROM articles
JOIN users
    ON articles.author_id = users.id;

The result might be:

1
2
3
4

title               | username
--------------------+---------
Learning Django     | alex
PostgreSQL Basics   | sam

This is one of the main strengths of relational databases.

Constraints

PostgreSQL can enforce rules directly in the database.

Examples include:

1
2
3
4
5

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK

NOT NULL:

1

name VARCHAR(200) NOT NULL

means:

1

name cannot be NULL

UNIQUE:

1

email VARCHAR(255) UNIQUE

means two rows cannot use the same email value.

A check constraint might be:

1

CHECK (price >= 0)

This prevents negative prices.

Why Database Constraints Matter

Applications may contain validation.

For example, Django forms may reject duplicate usernames.

But data can enter the database through:

• Django views
• scripts
• management commands
• background tasks
• database imports
• administrative tools
• another application

Database constraints provide a lower-level guarantee.

A useful rule is:

1
2
3
4
5

User experience validation
    Application layer

Critical data integrity
    Database layer

Transactions

A transaction groups several database operations into one logical unit.

Suppose an application transfers €100:

1
2
3
4
5

Account A
    -100

Account B
    +100

You do not want this:

1
2
3
4
5

Account A updated
        ↓
Application crashes
        ↓
Account B not updated

The transfer should be treated as one operation.

Conceptually:

1
2
3
4
5
6
7

BEGIN
   ↓
Subtract 100 from A
   ↓
Add 100 to B
   ↓
COMMIT

If something fails:

1

ROLLBACK

returns the transaction's database changes to the earlier state.

ACID Properties

Traditional relational transactions are often described through the ACID properties:

1
2
3
4

Atomicity
Consistency
Isolation
Durability

Atomicity means a transaction succeeds as a unit or is rolled back.

Consistency means database rules remain satisfied.

Isolation controls how concurrent transactions interact.

Durability means committed changes are expected to survive normal failures such as process restarts.

These properties are a major reason PostgreSQL is used for important application data.

Concurrent Users

Web applications often serve many users simultaneously.

For example:

1
2
3
4

User A places an order
User B updates a profile
User C publishes an article
User D searches products

These operations may happen at the same time.

PostgreSQL is designed to handle concurrent database activity safely.

MVCC

PostgreSQL uses a concurrency model called:

1

Multi-Version Concurrency Control

or:

1

MVCC

Instead of treating every update as simply overwriting a row in place from the perspective of all transactions, PostgreSQL maintains row versions so transactions can see an appropriate database snapshot.

Conceptually:

1
2
3
4
5
6
7
8
9

Transaction A
    sees version 1

Transaction B
    creates version 2

Transaction A
    may continue seeing version 1
    according to its isolation rules

This helps reduce unnecessary conflicts between readers and writers.

Why MVCC Matters

Without an approach such as MVCC, a reader could frequently need to wait for a writer.

With PostgreSQL's concurrency design, many reads can proceed while other transactions modify data.

This is particularly useful in web applications where many requests access the database at the same time.

Old Row Versions

MVCC means obsolete row versions can remain internally after updates and deletes.

PostgreSQL therefore needs maintenance to clean up obsolete row versions.

This is where:

1

VACUUM

becomes important.

VACUUM

PostgreSQL uses VACUUM to reclaim space associated with obsolete row versions and maintain internal database health.

In ordinary systems, this is largely handled automatically through:

1

autovacuum

Administrators usually do not manually vacuum every table after every change.

Autovacuum continuously helps maintain tables in the background.

ANALYZE

PostgreSQL's planner needs information about table contents.

For example:

1
2
3

How many rows exist?
How common is a value?
How selective is a column?

ANALYZE gathers statistics that help the query planner make better decisions.

Autovacuum also participates in maintaining planner statistics.

Storage on Disk

PostgreSQL ultimately stores persistent database information on disk.

Internally, it manages:

• table data
• indexes
• transaction information
• metadata
• write-ahead logs

Applications should not edit these internal files directly.

All normal access goes through PostgreSQL.

Memory and Caching

PostgreSQL does not read every byte directly from disk for every query.

Frequently used data can remain in memory.

Caching may occur through:

• PostgreSQL's own shared buffers
• the operating system's filesystem cache

This is one reason repeated queries may become faster after relevant data has been read recently.

Write-Ahead Logging

PostgreSQL uses write-ahead logging, commonly called:

1

WAL

Before certain database changes are considered safely committed, PostgreSQL records enough information in its write-ahead log to help recover database state after a crash.

A simplified concept is:

1
2
3
4
5
6
7

Database change
      ↓
Record change information in WAL
      ↓
Commit
      ↓
Database pages may be written later

WAL is important for:

• crash recovery
• replication
• backup strategies

Crash Recovery

Suppose the server loses power.

Some data changes may have been committed but not yet fully written into their final table pages.

PostgreSQL can use WAL during startup to recover committed database state.

This is one of the mechanisms behind transaction durability.

Replication

PostgreSQL can copy database changes to another PostgreSQL server.

A simplified architecture is:

1
2
3
4
5

Primary PostgreSQL
        ↓
WAL changes
        ↓
Replica PostgreSQL

A replica can be useful for:

• disaster recovery
• read workloads
• failover architectures
• geographical redundancy

The exact architecture depends on application requirements.

Backups

A database should not be considered safe merely because PostgreSQL is reliable.

Important systems still need backups.

Common strategies include:

• logical backups
• physical backups
• snapshots
• continuous archiving
• managed database backups

The goal is to recover from problems such as:

• accidental deletion
• application bugs
• hardware failure
• corrupted environments
• operator mistakes

Schemas

PostgreSQL supports schemas inside a database.

A schema is a namespace for database objects.

For example:

1
2

public.users
public.articles

or:

1
2

billing.invoices
analytics.events

The default schema commonly used by simple applications is:

1

public

Schemas can help organize larger databases.

PostgreSQL Data Types

PostgreSQL provides many data types.

Common examples include:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

INTEGER
BIGINT
NUMERIC
VARCHAR
TEXT
BOOLEAN
DATE
TIMESTAMP
UUID
JSONB
ARRAY

A table might contain:

1
2
3
4
5
6
7

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL
);

Choosing appropriate data types helps preserve data integrity.

Text Data

PostgreSQL supports:

1
2

VARCHAR
TEXT

For example:

1

name VARCHAR(200)

or:

1

description TEXT

Both store character data.

Length limits may be used when they represent a meaningful application rule.

Numeric Data

PostgreSQL supports several numeric types.

For money-like exact values:

1

NUMERIC(10, 2)

may be appropriate.

For example:

1

12345678.99

Using exact numeric types avoids the rounding behavior associated with approximate floating-point values.

Date and Time Data

PostgreSQL supports date and time types.

Examples include:

1
2
3
4

DATE
TIME
TIMESTAMP
TIMESTAMP WITH TIME ZONE

Applications commonly use timestamps for:

1
2
3
4

created_at
updated_at
published_at
paid_at

Time-zone handling should be designed carefully for applications operating across regions.

UUIDs

PostgreSQL has a native UUID data type.

Example:

1

id UUID PRIMARY KEY

UUIDs are commonly useful when:

• identifiers should not be sequential
• records are generated across distributed systems
• identifiers appear publicly

Django also supports UUID-backed model fields.

JSONB

PostgreSQL can store structured JSON data using:

1

JSONB

Example:

1
2
3
4

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    metadata JSONB NOT NULL
);

Possible value:

1
2
3
4

{
    "browser": "Firefox",
    "campaign": "summer"
}

PostgreSQL can query and index data inside JSONB.

This gives applications some flexibility without abandoning relational tables.

Relational Data and JSON Can Coexist

Using PostgreSQL does not mean every value must live in a traditional column.

A design might use:

1
2
3
4
5

Normal columns
    Important structured data

JSONB
    Flexible secondary metadata

For example:

1
2
3
4
5
6

orders
    id
    customer_id
    total
    status
    metadata

Core data remains relational while optional metadata can remain flexible.

Arrays

PostgreSQL also supports array columns.

For example:

1

tags TEXT[]

A row could contain:

1

{"django","postgresql","python"}

Arrays can be useful in specific situations, although a related table is often preferable when values need independent relationships or richer querying.

Full-Text Search

PostgreSQL includes full-text search functionality.

This can support searching text content using concepts such as:

• documents
• search vectors
• tokenization
• ranking

For many applications, built-in PostgreSQL search is sufficient before a dedicated search system becomes necessary.

Extensions

PostgreSQL can be extended.

Extensions can add:

• data types
• functions
• indexing methods
• geospatial support
• cryptographic functions

A famous example is:

1

PostGIS

which adds advanced geographic and spatial functionality.

This extensibility is one reason PostgreSQL is used beyond simple CRUD databases.

Views

A database view is a stored query that behaves somewhat like a virtual table.

Example:

1
2
3
4

CREATE VIEW published_articles AS
SELECT *
FROM articles
WHERE is_published = TRUE;

Applications can then query:

1
2

SELECT *
FROM published_articles;

Views can help encapsulate complex query logic or provide controlled access to data.

Materialized Views

PostgreSQL also supports materialized views.

Unlike a normal view, a materialized view stores query results.

Conceptually:

1
2
3
4
5

Complex query
    ↓
Stored result
    ↓
Fast reads

The stored result must be refreshed when updated data is required.

Materialized views can be useful for:

• reporting
• analytics
• expensive aggregations

Database Functions

PostgreSQL can execute functions inside the database.

For example, application logic can sometimes be expressed with SQL or procedural database functions.

However, not all business logic should be moved into the database.

A useful separation is often:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

Database
    Data integrity
    efficient querying
    constraints
    transactional operations

Application
    User workflows
    business orchestration
    HTTP behavior

Roles and Permissions

PostgreSQL has its own authentication and authorization system.

A database role can be granted access to specific databases or objects.

For example:

1
2
3
4
5
6
7
8

Application role
    Read and write application tables

Reporting role
    Read-only access

Administrator
    Database management privileges

Applications should generally not connect using an unnecessarily powerful database account.

Connections

An application must establish a connection before communicating with PostgreSQL.

Conceptually:

1
2
3
4
5

Django
   ↓
TCP or local socket
   ↓
PostgreSQL

Connection settings commonly include:

1
2
3
4
5

database name
username
password
host
port

PostgreSQL's default TCP port is commonly:

1

5432

Django and PostgreSQL

Django can use PostgreSQL as its database backend.

A configuration may resemble:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

DATABASES = {
    "default": {
        "ENGINE": (
            "django.db.backends.postgresql"
        ),
        "NAME": "myapp",
        "USER": "myapp_user",
        "PASSWORD": "secret",
        "HOST": "localhost",
        "PORT": "5432",
    }
}

Production credentials should normally come from secure environment configuration rather than being committed directly into source code.

Django Models Become PostgreSQL Tables

Consider:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

from django.db import models


class Article(models.Model):
    title = models.CharField(
        max_length=200,
    )

    content = models.TextField()

    published = models.BooleanField(
        default=False,
    )

Django migrations can create an equivalent PostgreSQL table.

Conceptually:

1
2
3
4
5
6
7

Django model
      ↓
Migration
      ↓
SQL
      ↓
PostgreSQL table

The developer can work mostly with Python while PostgreSQL remains the underlying database engine.

Django ORM Queries Become SQL

Django code:

1
2
3

Article.objects.filter(
    published=True,
)

is translated into SQL similar to:

1
2
3

SELECT ...
FROM articles_article
WHERE published = TRUE;

The exact generated query depends on the model and query.

The ORM does not remove the database.

It generates database queries on the application's behalf.

Why Understanding PostgreSQL Still Matters With Django

The Django ORM makes database interaction easier, but developers still benefit from understanding:

• indexes
• transactions
• constraints
• joins
• query plans
• locking
• data types
• database connections

For example:

1
2
3

Article.objects.filter(
    author__username="alex",
)

may look like ordinary Python, but the database may need to:

1
2
3
4

Join tables
Filter rows
Use indexes
Return results

Database knowledge becomes increasingly important as applications grow.

PostgreSQL and Migrations

Django migrations modify the PostgreSQL schema.

For example:

1
2

python manage.py makemigrations
python manage.py migrate

A migration might:

• create a table
• add a column
• remove a column
• add an index
• create a constraint

The model is the Python definition.

PostgreSQL holds the actual database schema.

Migrations keep them synchronized.

Connection Pooling

Opening a completely new database connection has a cost.

Larger applications may use connection pooling.

Conceptually:

1
2
3
4
5
6
7

Application request
       ↓
Connection pool
       ↓
Existing database connection
       ↓
PostgreSQL

Instead of creating and destroying a connection for every operation, connections can be reused.

A common PostgreSQL connection-pooling tool is:

1

PgBouncer

Connection pooling becomes especially important under higher concurrency.

Why PostgreSQL Is Used

PostgreSQL is popular because it combines several useful characteristics.

It provides:

• relational modeling
• strong transactional behavior
• SQL
• rich data types
• constraints
• indexes
• concurrency
• JSON support
• full-text search
• extensibility
• replication features
• mature tooling

This makes it useful for both small applications and large production systems.

Strong Data Integrity

PostgreSQL can enforce rules directly in the database.

For example:

1

email TEXT UNIQUE NOT NULL

means an application cannot silently insert:

1

NULL

or duplicate email addresses into that column.

This makes the database an active protector of application data rather than a passive file store.

Complex Queries

Relational applications often need queries such as:

1
2
3
4
5

Find all paid orders
created this month
for active customers
whose total is above €100
and include each customer's email.

PostgreSQL is designed to handle these kinds of relationships and filters efficiently.

Reliable Transactions

Applications dealing with:

• payments
• inventory
• bookings
• account balances
• subscriptions
• permissions

often need several related changes to succeed or fail together.

PostgreSQL's transactional model is particularly valuable for these workflows.

Good Django Integration

PostgreSQL is a common choice for Django applications because Django supports PostgreSQL-specific capabilities in addition to normal relational database functionality.

Developers can generally use the standard Django ORM while still benefiting from PostgreSQL features.

Rich Query Features

PostgreSQL supports advanced SQL including:

• joins
• subqueries
• common table expressions
• window functions
• aggregates
• grouping
• filtered indexes
• expression indexes

This gives applications room to grow without replacing the database immediately when query requirements become more complex.

Open Source

PostgreSQL is open-source software.

Organizations can:

• run it themselves
• inspect it
• modify it
• use managed hosting providers
• move between infrastructure providers

This reduces dependence on one proprietary database vendor.

Mature Ecosystem

PostgreSQL has existed for decades and has a large ecosystem around:

• administration
• monitoring
• backup
• replication
• cloud hosting
• application drivers
• ORMs
• migration tools

Maturity matters for software that may contain critical application data.

PostgreSQL Is Not Always the Only Choice

PostgreSQL is powerful, but it is not the only database.

Other choices include:

1
2
3
4
5
6
7

SQLite
MySQL
MariaDB
Microsoft SQL Server
Oracle
MongoDB
Redis

These systems solve overlapping but different problems.

PostgreSQL Versus SQLite

SQLite is embedded directly into an application.

Conceptually:

1
2
3

Django
   ↓
SQLite file

PostgreSQL uses a database server:

1
2
3
4
5

Django
   ↓
PostgreSQL server
   ↓
Database storage

SQLite is excellent for:

• learning
• scripts
• prototypes
• small local applications
• some low-concurrency production workloads

PostgreSQL becomes attractive when an application needs:

• more concurrency
• stronger operational tooling
• network database access
• replication
• richer PostgreSQL features
• larger production deployments

PostgreSQL Versus a Key-Value Store

Redis, for example, is commonly used for:

• caching
• short-lived data
• queues
• counters
• sessions

PostgreSQL is commonly used for durable relational application data.

A project may use both:

1
2
3
4
5

PostgreSQL
    Primary application database

Redis
    Cache / queue / temporary state

Different tools can serve different responsibilities.

Common PostgreSQL Deployment Architecture

A simple production setup might be:

1
2
3
4
5
6
7

Browser
   ↓
Reverse proxy
   ↓
Django
   ↓
PostgreSQL

A larger architecture might be:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

Users
  ↓
Load balancer
  ↓
Several Django servers
  ↓
Connection pool
  ↓
Primary PostgreSQL
  ↓
Replica

The architecture can evolve without changing the basic concept:

1
2

Applications send queries.
PostgreSQL stores and processes data.

Common Performance Problems

PostgreSQL is powerful, but poor application queries can still be slow.

Common causes include:

• missing indexes
• unnecessary indexes
• retrieving too many rows
• expensive joins
• repeated queries
• poor query patterns
• stale statistics
• long transactions
• insufficient memory or storage performance

The database cannot automatically compensate for every inefficient application design.

The N+1 Query Problem

An application may accidentally perform:

1
2
3

1 query to retrieve articles
+
1 query per article to retrieve its author

For 100 articles:

1

101 queries

In Django this can often be improved using:

1
2
3

Article.objects.select_related(
    "author"
)

The problem is visible at the Django level, but the cost occurs in database communication.

Understanding both layers helps identify the issue.

Use EXPLAIN

PostgreSQL can show how it plans to execute a query.

Example:

1
2
3
4

EXPLAIN
SELECT *
FROM products
WHERE name = 'Keyboard';

The output may indicate whether PostgreSQL plans to use:

• a sequential scan
• an index scan
• a join strategy
• sorting
• aggregation

For actual execution information:

1
2
3
4

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE name = 'Keyboard';

This is an important tool for diagnosing slow queries.

Be careful with EXPLAIN ANALYZE on statements that modify data because it actually executes the statement.

Long Transactions

Transactions should normally remain focused.

A long-running transaction can:

• retain old row versions
• increase contention
• hold locks
• interfere with cleanup
• complicate concurrency

Avoid opening a transaction and then doing unrelated slow work such as waiting on an external API unless the workflow truly requires it.

Locks

PostgreSQL uses locks to protect data and coordinate concurrent operations.

For example, updating a row may cause another transaction trying to modify the same row to wait.

Locks are necessary for correctness.

Problems arise when:

• transactions stay open too long
• operations acquire resources in inconsistent order
• too many requests contend for the same rows

Deadlocks

A deadlock can happen when two transactions wait for each other.

Conceptually:

1
2
3
4
5
6
7

Transaction A
    locks row 1
    waits for row 2

Transaction B
    locks row 2
    waits for row 1

Neither can proceed.

PostgreSQL detects deadlocks and aborts one transaction so the system can continue.

Applications may need to retry failed operations in appropriate cases.

Connection Limits

PostgreSQL can only handle a configured number of simultaneous connections.

Creating too many application workers without considering database connections can exhaust that limit.

For example:

1
2
3
4
5

10 servers
×
20 workers
×
several connections

can become a large number quickly.

Connection management is an important production concern.

Monitoring

Production databases should be monitored.

Useful areas include:

• CPU
• memory
• disk usage
• connection count
• slow queries
• locks
• replication health
• transaction duration
• table growth
• backup status

A database should not be treated as an invisible dependency.

Security

Database security should include:

• strong authentication
• restricted network access
• limited database privileges
• encrypted connections where appropriate
• protected backups
• secret management
• regular updates

The application database account should usually have only the permissions the application needs.

Do Not Expose PostgreSQL Directly to Users

A typical web user should never connect directly to the application database.

The architecture should be:

1
2
3
4
5

User
   ↓
Application
   ↓
PostgreSQL

not:

1
2
3

User
   ↓
PostgreSQL

The application enforces:

• authentication
• permissions
• business rules
• validation

before database operations occur.

SQL Injection

Applications should not construct SQL by combining untrusted strings.

Avoid patterns like:

1
2
3
4

query = (
    "SELECT * FROM users "
    f"WHERE username = '{username}'"
)

An attacker may manipulate the SQL.

Use parameterized queries or an ORM.

For example, Django:

1
2
3

User.objects.filter(
    username=username,
)

handles parameters safely rather than treating user input as raw SQL syntax.

PostgreSQL and Data Ownership

PostgreSQL protects database objects with roles and permissions, but application-level ownership rules usually remain the application's responsibility.

For example:

1
2

User 1 may edit Article 12.
User 2 may not.

Django might enforce this with:

1
2
3
4

Article.objects.get(
    pk=12,
    author=request.user,
)

The database enforces structural integrity.

The application enforces domain-specific authorization.

A Simple Example From Django to PostgreSQL

Django model:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

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

    price = models.DecimalField(
        max_digits=10,
        decimal_places=2,
    )

    active = models.BooleanField(
        default=True,
    )

Django query:

1
2
3
4

products = Product.objects.filter(
    active=True,
    price__lt=100,
)

Conceptually PostgreSQL receives a query similar to:

1
2
3
4
5
6
7
8
9

SELECT
    id,
    name,
    price,
    active
FROM products
WHERE
    active = TRUE
    AND price < 100;

PostgreSQL then:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

Parses SQL
   ↓
Builds a query plan
   ↓
Uses table/index data
   ↓
Finds matching rows
   ↓
Returns results
   ↓
Django creates model objects

The full application flow becomes:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

Browser
   ↓
Django view
   ↓
Django ORM
   ↓
PostgreSQL
   ↓
Rows
   ↓
Django models
   ↓
Template / API response
   ↓
Browser

A Useful Mental Model

For beginners, think of PostgreSQL as several things at once.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

Persistent storage
    Keeps data after the application stops

Relational engine
    Connects related tables

Query engine
    Finds and combines data

Transaction manager
    Protects groups of changes

Constraint system
    Enforces data rules

Concurrency manager
    Coordinates many users

Database server
    Accepts connections from applications

It is much more than a file containing rows.

When PostgreSQL Is a Good Choice

PostgreSQL is especially suitable when an application needs:

• reliable persistent data
• relationships between records
• transactional updates
• many concurrent users
• advanced querying
• strong data constraints
• indexing
• JSON alongside relational data
• mature production tooling
• room to grow

These requirements describe a large percentage of traditional web applications.

Common Beginner Misunderstandings

PostgreSQL Is Not the Django ORM

Django ORM code:

1

Article.objects.all()

is Python.

PostgreSQL is the database that ultimately processes SQL and stores the data.

The ORM is an abstraction between the application and database.

PostgreSQL Does Not Store Django Model Objects

PostgreSQL stores rows and typed column values.

Django converts those rows into Python model instances.

An Index Does Not Automatically Make Everything Faster

Indexes help specific query patterns but make writes and storage more expensive.

Transactions Are Not Only for Financial Applications

Transactions are useful whenever several related database changes must remain consistent.

JSONB Does Not Make PostgreSQL a Pure Document Database

PostgreSQL can store JSON, but its core strengths still include relational data, constraints, SQL, and transactions.

A Database Backup Is Still Necessary

Replication and durability do not protect against every kind of data loss.

For example, an accidental valid:

1

DELETE

can also be replicated.

Backups solve a different problem.

More Database Connections Are Not Always Better

Too many connections can reduce performance and exhaust resources.

A Fast Development Query May Be Slow in Production

A query against 50 rows may behave very differently against 50 million rows.

Production-scale data changes performance characteristics.

Recommended Learning Order

A practical PostgreSQL learning path is:

1. Databases, tables, rows, and columns
2. Basic SQL
3. Primary keys
4. Foreign keys
5. Constraints
6. Joins
7. Indexes
8. Transactions
9. Query plans
10. MVCC
11. VACUUM and ANALYZE
12. Backups
13. Replication
14. Connection management
15. Production monitoring

For Django developers, learn these concepts alongside:

1
2
3
4
5
6
7

Django models
Django migrations
Django QuerySets
transaction.atomic()
select_related()
prefetch_related()
database constraints

Mini Reference

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

PostgreSQL
    Relational database management system

Database
    Container for database objects

Table
    Structured collection of rows

Row
    One record

Column
    One typed attribute

SQL
    Language used to query and modify data

Primary key
    Unique row identifier

Foreign key
    Relationship to another table

Constraint
    Database-enforced rule

Index
    Structure that can accelerate queries

Transaction
    Group of database operations

COMMIT
    Make a transaction permanent

ROLLBACK
    Undo transaction changes

MVCC
    PostgreSQL concurrency mechanism

VACUUM
    Cleans obsolete row versions

ANALYZE
    Updates query-planner statistics

WAL
    Write-ahead log used for durability
    and replication

EXPLAIN
    Shows PostgreSQL's query plan

Basic query:

1
2
3

SELECT *
FROM articles
WHERE is_published = TRUE;

Basic insert:

1
2
3
4
5
6
7
8

INSERT INTO articles (
    title,
    is_published
)
VALUES (
    'PostgreSQL Basics',
    TRUE
);

Basic transaction:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

Basic Django query:

1
2
3

Article.objects.filter(
    is_published=True,
)

Conceptually:

1
2
3
4
5
6
7
8
9

Django ORM
   ↓
SQL
   ↓
PostgreSQL
   ↓
Rows
   ↓
Django model instances

PostgreSQL is a relational database system designed to store application data reliably and make that data easy to query, relate, validate, and update.

Its basic operation can be understood as:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

Application sends SQL
        ↓
PostgreSQL parses it
        ↓
Query planner chooses a strategy
        ↓
Database engine reads or changes data
        ↓
Transaction and concurrency rules
protect correctness
        ↓
Result is returned

PostgreSQL is widely used because it combines:

• strong relational modeling
• transactions
• data integrity
• concurrency
• indexes
• powerful SQL
• JSON support
• mature production features
• extensibility
• open-source availability

For a Django developer, PostgreSQL is especially important because the ORM may hide much of the SQL syntax, but it does not remove the database itself.

Understanding what happens beneath:

1

Article.objects.filter(...)

helps explain why indexes matter, why some queries are slow, why transactions matter, and why database constraints should be treated as part of application design.

The simplest way to think about PostgreSQL is not merely as somewhere an application puts data.

It is the system responsible for keeping that data organized, searchable, consistent, and durable while many parts of an application may be reading and changing it at the same time.