PostgreSQL in Production: Performance, Connection Pooling, and Operational Reliability

Writing correct SQL is the entry ticket to working with PostgreSQL, not the whole job. Once a database sits behind a real application, the questions change. How many connections can the server sustain? Why did a query that took milliseconds last month now take seconds? What happens when two requests try to sell the last unit of stock at the same moment? How would you know the disk is filling up before it fills up, and how long would it take to restore if it did?

This article covers PostgreSQL production best practices across five concerns: performance, reliability, data integrity, concurrency, and operational visibility. It starts with the architecture that shapes all of them, then works through PostgreSQL connection pooling, indexing, query optimization, transactions, vacuum, monitoring, backups, and how these apply to a Django application. Examples target PostgreSQL 16/17, PgBouncer 1.21+, and Django 5.2.

1. PostgreSQL architecture fundamentals#

Most operational decisions in PostgreSQL trace back to a handful of architectural choices. Understanding them makes the rest of this article less like a list of rules and more like a set of consequences.

Process-per-connection#

PostgreSQL uses a process model. A supervisor process, the postmaster, listens for connections and forks a dedicated backend process for each client. That backend handles every query from that client until it disconnects. The model gives strong isolation between sessions, but it means every connection costs an operating system process, its own private memory, and a slot in shared data structures. This is the root reason connection pooling matters.

Shared buffers and the WAL#

shared_buffers is PostgreSQL's own page cache, shared by all backends. Reads first check shared buffers, then fall back to the operating system, which has its own page cache. Sizing it is a balance: too small and hot data is evicted, too large and you starve the OS cache and other memory consumers.

Every change is first recorded in the write-ahead log (WAL) before the corresponding data pages are written. On commit, PostgreSQL only needs to flush the WAL to durable storage; the modified data pages can be written later. This is what makes crash recovery possible, and the WAL also feeds streaming replication and point-in-time recovery.

Checkpoints, the checkpointer and the background writer#

A checkpoint guarantees that all data pages modified before a certain WAL position are on disk, which bounds how much WAL must be replayed after a crash. The checkpointer process performs checkpoints, spreading writes over time according to checkpoint_completion_target. Checkpoints that occur too frequently (often because max_wal_size is small relative to write volume) cause I/O spikes and extra WAL, because the first change to a page after each checkpoint writes a full page image.

The background writer trickles dirty pages out of shared buffers so that backends rarely have to write pages themselves before reusing a buffer.

MVCC and autovacuum#

PostgreSQL implements concurrency with multi-version concurrency control (MVCC). An UPDATE does not overwrite a row in place; it writes a new row version and marks the old one as expired. Readers see a consistent snapshot without blocking writers. The cost is that old row versions (dead tuples) accumulate and must be cleaned up. The autovacuum launcher schedules autovacuum workers that reclaim dead tuples, update planner statistics, and freeze old transaction IDs. If autovacuum falls behind, tables bloat, queries slow down, and in the worst case the database approaches transaction ID wraparound.

2. Database connections and pooling#

Why too many connections hurt#

Each backend process consumes memory for its own catalog caches and working state. On top of that, work_mem is a per-operation limit, not a per-connection limit: a single complex query can use work_mem for each sort or hash node in its plan, and parallel workers can multiply that again. A server with hundreds of active connections running heavy queries can exhaust memory even when each individual setting looks modest.

Beyond memory, many concurrently active backends compete for a fixed number of CPU cores, so the operating system spends more time context switching. Contention also grows inside PostgreSQL: building a snapshot involves scanning the set of running transactions, and lightweight locks on shared structures see more contention as backend count rises. Recent releases have improved snapshot scalability, but the principle stands. Past the point where CPU and I/O are saturated, adding more active connections increases latency without increasing throughput.

max_connections sets the hard ceiling. Raising it is easy, but it is rarely the right answer to "too many connections" errors. The better question is why the application needs that many simultaneous server connections.

Application connection lifecycles#

Applications hold connections in different ways. Django, by default, opens a connection on the first query in a request and closes it at the end of the request (CONN_MAX_AGE = 0). With CONN_MAX_AGE set to a positive number, each worker thread or process keeps a persistent connection. Either way, the number of server connections tends to scale with the number of application workers multiplied by the number of application instances. Add Celery workers, cron jobs, and admin tools and the total can climb quickly, particularly when autoscaling adds instances.

PgBouncer pooling modes#

PgBouncer sits between clients and PostgreSQL, accepting many client connections and multiplexing them over a smaller set of server connections. How aggressively it shares server connections depends on the pool mode.

Transaction mode gives the largest reduction in server connections for typical web workloads, because a client only occupies a server connection while a transaction is open. The trade-off is that consecutive transactions from the same client may run on different server connections, so anything that relies on session state breaks or behaves unpredictably:

• Session-level SET changes (for example SET search_path or SET statement_timeout) can leak to other clients or vanish. Use SET LOCAL inside a transaction, or configure settings per role or database instead.
• Session-level advisory locks (pg_advisory_lock) are tied to a server connection. Transaction-level advisory locks (pg_advisory_xact_lock) are compatible.
• Temporary tables live for the session, so a later transaction may not see them. They are only safe if created and dropped within one transaction (for example with ON COMMIT DROP).
• LISTEN/NOTIFY: LISTEN needs a dedicated session. Run listeners against PostgreSQL directly or through a session-mode pool.
• Prepared statements: SQL-level PREPARE/EXECUTE is not supported in transaction mode. Since PgBouncer 1.21, setting max_prepared_statements to a non-zero value lets PgBouncer track protocol-level named prepared statements (the kind drivers send via the extended query protocol) and prepare them on whichever server connection the client lands on.

A sample PgBouncer configuration#

The following PgBouncer configuration is a starting point for a single application database in transaction mode. Every number in it is an example to be adjusted, not a recommendation.

[databases]
; Clients connect to "app" on PgBouncer; PgBouncer connects to the real server.
app = host=10.0.0.10 port=5432 dbname=app
 
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
 
; Authenticate clients with SCRAM; credentials live in auth_file
; (or use auth_query to look them up in PostgreSQL).
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
 
; Return the server connection to the pool at the end of each transaction.
pool_mode = transaction
 
; Client connections PgBouncer will accept (cheap compared with server connections).
max_client_conn = 1000
 
; Server connections per (database, user) pair. Example value only.
default_pool_size = 20
 
; Extra server connections allowed when clients have waited reserve_pool_timeout seconds.
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; Hard cap on server connections to one database across all users.
max_db_connections = 40
 
; Fail queued client requests rather than letting them wait indefinitely.
query_wait_timeout = 30
 
; Close idle server connections and recycle long-lived ones.
server_idle_timeout = 600
server_lifetime = 3600
 
; Track protocol-level prepared statements in transaction mode (PgBouncer 1.21+).
max_prepared_statements = 100
 
; Some drivers send this startup parameter; ignore it instead of rejecting the client.
ignore_startup_parameters = extra_float_digits
 
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

Reasoning about pool size#

No pool size is universally correct. The useful number depends on how many CPU cores and how much I/O capacity the database has, how long transactions last, how much time queries spend waiting on disk or locks rather than computing, and how many other clients (replicas excluded) share the server.

A practical approach is to start with a pool of the same order of magnitude as the server's core count, then measure. If clients queue in PgBouncer (visible via SHOW POOLS in the admin console, as cl_waiting) while the database has idle CPU and low I/O wait, the pool may be too small. If increasing the pool increases latency but not throughput, the database is saturated and a bigger pool only adds contention. Remember that pool sizes multiply: several PgBouncer instances, each with several pools, can together exceed max_connections. Keep headroom for superuser access, replication, migrations, and maintenance jobs.

3. PostgreSQL indexing#

Indexes trade write cost and storage for faster reads. Good indexing starts from the queries the application actually runs, not from the schema.

B-tree and composite indexes#

A composite B-tree index is ordered by its first column, then the second within each value of the first, and so on. It can serve queries that filter on a leftmost prefix of its columns. An index on (customer_id, created_at) helps a query filtering by customer_id, and one filtering by customer_id with a range on created_at, and can return rows already sorted by created_at for one customer. It is much less useful for a query that filters on created_at alone. As a rule of thumb, put equality columns before range columns.

-- Supports: WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20
CREATE INDEX orders_customer_created_idx
    ON orders (customer_id, created_at DESC);

Partial and unique indexes#

A partial index covers only rows matching a predicate. If most queries touch a small, well-defined subset, the index is smaller and cheaper to maintain.

-- Only pending orders are queried by the fulfilment worker.
CREATE INDEX orders_pending_created_idx
    ON orders (created_at)
    WHERE status = 'pending';
 
-- Unique email among active users; deleted accounts may reuse an address.
CREATE UNIQUE INDEX users_active_email_uniq
    ON users (lower(email))
    WHERE deleted_at IS NULL;

The planner only uses a partial index when it can prove the query's WHERE clause implies the index predicate, so queries must include a matching condition.

GIN and BRIN#

-- jsonb containment: WHERE attributes @> '{"colour": "red"}'
CREATE INDEX products_attributes_gin ON products USING gin (attributes jsonb_path_ops);
 
-- Array membership: WHERE tags @> ARRAY['sale']
CREATE INDEX articles_tags_gin ON articles USING gin (tags);
 
-- Full-text search over a stored tsvector column
CREATE INDEX articles_search_gin ON articles USING gin (search_vector);
 
-- Substring and similarity search: WHERE name ILIKE '%widget%'
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm ON products USING gin (name gin_trgm_ops);
 
-- Append-only event log queried by time range
CREATE INDEX events_created_brin ON events USING brin (created_at);

BRIN works because rows inserted in time order sit in roughly time-ordered blocks. If rows are frequently updated or inserted out of order, the block summaries become wide and the index stops being selective.

Selectivity and query patterns#

An index is most valuable when it is selective, meaning a condition matches a small fraction of rows. If a query will read a large share of a table, a sequential scan can be cheaper than thousands of random index lookups, and the planner will choose it. Indexing a boolean column where most rows share one value rarely helps; a partial index on the rare value usually does.

Building indexes safely#

A plain CREATE INDEX blocks writes to the table for the duration of the build. On a busy table, use CREATE INDEX CONCURRENTLY, which allows reads and writes to continue at the cost of a slower build that scans the table twice.

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);

It cannot run inside a transaction block. If it fails, it leaves an INVALID index that must be dropped (DROP INDEX CONCURRENTLY) and retried.

Maintenance cost and HOT updates#

Every index adds work to inserts, to deletes (through vacuum), and to updates. Because of MVCC, an update normally creates a new row version that must be added to every index on the table, which is write amplification. PostgreSQL avoids this with heap-only tuple (HOT) updates when no indexed column changes and the new version fits on the same page. Adding an index on a frequently updated column disables HOT for those updates. Periodically review pg_stat_user_indexes for indexes with idx_scan near zero, keeping in mind that some exist to enforce constraints.

4. Query planning and optimization#

PostgreSQL's planner estimates the cost of alternative plans using table statistics and picks the cheapest. Most query optimization work is about finding where those estimates or the available access paths fall short.

Reading EXPLAIN#

EXPLAIN shows the chosen plan and estimated costs without running the query. EXPLAIN (ANALYZE, BUFFERS) runs it and adds actual row counts, timings, and buffer usage.

Common plan nodes:

• Seq Scan reads the whole table. Fine for small tables or queries that need most rows.
• Index Scan walks an index and fetches matching rows from the table.
• Index Only Scan answers from the index alone when all needed columns are in it and the visibility map shows the pages are all-visible.
• Bitmap Index Scan / Bitmap Heap Scan collects matching row locations from one or more indexes, then reads table pages in physical order. Common for medium selectivity or when combining indexes.
• Nested Loop runs the inner side once per outer row. Excellent when the outer side is small and the inner side is indexed; poor when the outer side is unexpectedly large.
• Hash Join builds a hash table from one input and probes it with the other. Good for larger unsorted inputs; limited by work_mem.
• Merge Join joins two inputs sorted on the join key.

The following plan is illustrative, written to show the shape of the output rather than captured from a real system.

Limit  (cost=0.43..85.12 rows=20 width=64) (actual time=0.041..4.870 rows=20 loops=1)
  Buffers: shared hit=12 read=310
  ->  Nested Loop  (cost=0.43..4235.80 rows=1000 width=64) (actual time=0.040..4.861 rows=20 loops=1)
        Buffers: shared hit=12 read=310
        ->  Index Scan using orders_customer_created_idx on orders o  (cost=0.43..812.50 rows=1000 width=40) (actual time=0.025..4.700 rows=20 loops=1)
              Index Cond: (customer_id = 42)
              Filter: (status = 'shipped'::text)
              Rows Removed by Filter: 4780
        ->  Index Scan using customers_pkey on customers c  (cost=0.29..3.40 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=20)
              Index Cond: (id = o.customer_id)
Planning Time: 0.210 ms
Execution Time: 4.910 ms

How to read it: the scan on orders uses the composite index for customer_id but then discards most rows with a Filter on status. "Rows Removed by Filter" is large compared with the rows kept, and read=310 shows most pages came from outside shared buffers. Options would include adding status to the index (or a partial index for shipped orders), depending on how common that query is. The estimated rows=1000 versus the actual numbers also hints that statistics may not capture this customer's distribution. In general, look for the node where estimated and actual row counts diverge sharply, since that is where the planner's choices start to go wrong.

Statistics and row estimates#

ANALYZE samples tables and records per-column statistics: null fraction, distinct values, most common values, and histograms. Autovacuum runs it automatically, but run it manually after bulk loads. default_statistics_target (100 by default) controls sample detail; it can be raised for individual columns with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS.

By default, the planner assumes columns are independent. When they are correlated, such as city and postal code, estimates for combined filters can be far too low. Extended statistics address this:

CREATE STATISTICS addresses_city_postcode_stats (dependencies, ndistinct)
    ON city, postcode FROM addresses;
ANALYZE addresses;

5. Transactions and concurrency#

ACID and isolation levels#

PostgreSQL transactions are atomic, consistent, isolated, and durable. MVCC gives each statement or transaction a snapshot, and the isolation level determines which snapshot.

• Read Committed (the default): each statement sees data committed before it began. Two statements in one transaction can see different data.
• Repeatable Read: the whole transaction sees one snapshot. Concurrent updates to the same row raise a serialization error that the application must retry.
• Serializable: guarantees the result matches some serial order of transactions, detecting dangerous patterns and aborting one transaction. Applications must be ready to retry.

PostgreSQL accepts READ UNCOMMITTED but treats it as Read Committed; dirty reads never occur.

Row-level locks#

SELECT ... FOR UPDATE locks the selected rows until the transaction ends, so other transactions that try to update or lock them wait. NOWAIT makes them fail immediately instead, and SKIP LOCKED skips locked rows, which is useful for job queues where several workers pick tasks from one table.

A deadlock occurs when two transactions each hold a lock the other needs. PostgreSQL detects it after deadlock_timeout and aborts one. The standard prevention is consistent lock ordering: if code always locks rows in, for example, ascending primary key order, cycles cannot form.

A hypothetical inventory race#

Consider a hypothetical shop where two customers try to buy the last two units of a product at the same time, each ordering two. A naive implementation reads, checks, then writes:

-- Unsafe: both sessions can pass the check before either writes.
BEGIN;
SELECT stock FROM products WHERE id = 7;          -- both sessions read stock = 2
-- application checks 2 >= 2, decides to proceed
UPDATE products SET stock = 2 - 2 WHERE id = 7;   -- value computed in the application
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1001, 7, 2);
COMMIT;

Under Read Committed both transactions read stock = 2, both pass the check, and two orders are accepted for units that exist only once. One fix is to lock the row before checking:

BEGIN;
SELECT stock FROM products WHERE id = 7 FOR UPDATE;  -- second session waits here
-- application checks stock >= 2
UPDATE products SET stock = stock - 2 WHERE id = 7;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1001, 7, 2);
COMMIT;

The second session blocks until the first commits, then reads the updated stock of 0 and rejects the order. When the check is simple, an atomic conditional update is shorter and holds the lock for less time:

BEGIN;
UPDATE products
   SET stock = stock - 2
 WHERE id = 7
   AND stock >= 2
RETURNING stock;
-- No row returned: insufficient stock, so ROLLBACK.
-- One row returned: reservation succeeded, continue.
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1001, 7, 2);
COMMIT;

A CHECK (stock >= 0) constraint on the table adds a final safety net.

Long-running transactions and timeouts#

A transaction that stays open, even an idle one, holds back the oldest snapshot that vacuum must respect. Dead tuples created after that point cannot be removed anywhere in the database, so bloat grows. Long transactions also hold locks that queue other work. Three settings bound the damage:

• idle_in_transaction_session_timeout terminates sessions left idle inside an open transaction.
• lock_timeout aborts a statement that waits too long for a lock.
• statement_timeout aborts a statement that runs too long.

These can be set per role or per database, which suits applications behind transaction-mode poolers better than session-level SET.

ALTER ROLE app_web SET statement_timeout = '15s';
ALTER ROLE app_web SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE app_migrations SET lock_timeout = '5s';

6. Vacuum and table maintenance#

VACUUM marks space held by dead tuples as reusable, updates the visibility map (which enables index-only scans), and freezes old row versions. It does not usually return space to the operating system; it makes space reusable within the table. Bloat is the accumulated free and dead space that makes tables and indexes larger than their live data requires, increasing I/O for every scan.

Autovacuum triggers a vacuum on a table when dead tuples exceed autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor times the table's row count, and an analyze on a similar rule. The default scale factor of 0.2 means a large table may accumulate a great many dead tuples before it is vacuumed. Large, frequently updated tables often benefit from per-table settings:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_scale_factor = 0.01
);

If autovacuum consistently falls behind, consider adjusting cost limits (autovacuum_vacuum_cost_limit), worker count, and maintenance_work_mem, and look for long transactions that prevent cleanup.

Transaction ID wraparound#

PostgreSQL transaction IDs are 32-bit and compared modulo wraparound. Rows must be frozen (marked visible to all transactions) before they become older than about two billion transactions. Autovacuum forces anti-wraparound vacuums as tables approach autovacuum_freeze_max_age. If freezing cannot keep up, PostgreSQL first warns and eventually refuses new transaction IDs to protect data, which is effectively an outage until a vacuum completes.

VACUUM FULL rewrites the table into a new file and does return space to the operating system, but it takes an ACCESS EXCLUSIVE lock that blocks all reads and writes for its duration and needs extra disk space for the copy. Treat it as an exceptional operation for planned maintenance windows, not routine maintenance.

7. PostgreSQL monitoring#

PostgreSQL monitoring should answer two questions: is the database healthy now, and is something trending toward trouble? Useful indicators include:

• Active connections and utilisation relative to max_connections, including idle-in-transaction sessions.
• Lock waits and blocked sessions.
• Long-running transactions and queries.
• Query latency and the queries that consume the most total time.
• Cache hit ratio. This only measures hits in shared buffers; a "miss" may still be served from the OS page cache. Watch for changes rather than chasing a target number.
• Replication lag on standbys.
• WAL generation rate, which drives disk usage, archiving, and replication bandwidth.
• Disk capacity for data, WAL, and archive locations.
• Autovacuum activity, dead tuple counts, and the age of the oldest unfrozen transaction ID.

pg_stat_activity#

pg_stat_activity shows one row per backend with its state, current query, and timing. The following queries are read-only.

-- Connections by state, and utilisation of max_connections
SELECT state, count(*) AS connections
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY connections DESC;
 
SELECT count(*) AS total,
       current_setting('max_connections')::int AS max_connections,
       round(100.0 * count(*) / current_setting('max_connections')::int, 1) AS pct_used
FROM pg_stat_activity
WHERE backend_type = 'client backend';

-- Oldest open transactions, including idle-in-transaction sessions
SELECT pid,
       usename,
       application_name,
       state,
       wait_event_type,
       wait_event,
       now() - xact_start AS xact_age,
       now() - query_start AS query_age,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

-- Who is blocked, and by whom
SELECT blocked.pid AS blocked_pid,
       left(blocked.query, 80) AS blocked_query,
       blocking.pid AS blocking_pid,
       left(blocking.query, 80) AS blocking_query,
       now() - blocked.query_start AS waiting_for
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid;
 
-- Ungranted lock requests by lock type and mode
SELECT locktype, mode, count(*) AS waiting
FROM pg_locks
WHERE NOT granted
GROUP BY locktype, mode;

pg_stat_statements#

pg_stat_statements aggregates execution statistics per normalised query. It must be added to shared_preload_libraries (which requires a restart) and then created in the database with CREATE EXTENSION pg_stat_statements;. Since PostgreSQL 13 the timing columns are total_exec_time and mean_exec_time.

-- Queries consuming the most total execution time
SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Sorting by total time finds what costs the server most overall; sorting by mean time finds individually slow queries. Both views are useful.

Tables, replication, and capacity#

-- Dead tuples and recent autovacuum activity
SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze,
       autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;
 
-- Cache hit ratio per database (shared buffers only)
SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database
WHERE datname IS NOT NULL;
 
-- Replication lag as seen from the primary
SELECT application_name, client_addr, state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
 
-- Transaction ID age per database (watch against autovacuum_freeze_max_age)
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;
 
-- Database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

For WAL generation, sample pg_current_wal_lsn() at intervals and compute the difference with pg_wal_lsn_diff(), or read wal_bytes from pg_stat_wal (PostgreSQL 14 and later). In practice these queries are best collected by an exporter into a time-series system with alerting, rather than run by hand during an incident.

8. Backup and recovery#

Two families of backup serve different goals.

Logical backups with pg_dump export a database as SQL or as an archive. The custom format (-Fc) is compressed and lets pg_restore restore selectively and in parallel. Logical dumps are portable across major versions and useful for migrations and partial restores, but restoring a large database is slow because indexes must be rebuilt, and a dump only captures one moment.

pg_dump --format=custom --file=app-$(date +%F).dump --dbname=app
pg_restore --jobs=4 --dbname=app_restore_test app-2026-01-15.dump

Physical backups copy the data directory. pg_basebackup takes a consistent base backup of a running cluster. Combined with continuous WAL archiving (archive_mode and archive_command or archive_library), you can replay WAL from the base backup up to a chosen moment. That is point-in-time recovery (PITR), which lets you recover to just before an accidental DELETE, for example. Dedicated tools such as pgBackRest and others manage base backups, WAL archiving, retention, and verification, and are worth evaluating rather than scripting everything yourself.

Two targets frame the design. The recovery point objective (RPO) is how much data loss is acceptable, which drives how continuously WAL is archived. The recovery time objective (RTO) is how long restoration may take, which drives backup frequency, restore tooling, and whether standbys are needed for fast failover. Replicas help availability but are not backups: a destructive statement replicates immediately.

9. PostgreSQL and Django#

Connection settings#

Django's database settings map directly onto the connection lifecycle discussed earlier. CONN_MAX_AGE controls persistent connections, and CONN_HEALTH_CHECKS makes Django check a reused connection before the first query of a request, discarding it if the server has closed it.

import os
 
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": os.environ["POSTGRES_DB"],
        "USER": os.environ["POSTGRES_USER"],
        "PASSWORD": os.environ["POSTGRES_PASSWORD"],
        "HOST": os.environ.get("POSTGRES_HOST", "pgbouncer"),
        "PORT": os.environ.get("POSTGRES_PORT", "6432"),
        # Keep connections to PgBouncer open between requests.
        "CONN_MAX_AGE": 60,
        "CONN_HEALTH_CHECKS": True,
        # Required with PgBouncer in transaction mode: server-side cursors
        # (used by QuerySet.iterator()) cannot span transactions.
        "DISABLE_SERVER_SIDE_CURSORS": True,
        "OPTIONS": {
            "application_name": "web",
        },
    }
}

When an external pooler is not available, Django 5.1 and later can use psycopg 3's connection pool by setting "pool": True (or a dict of pool options) in OPTIONS. This needs the psycopg[pool] package, and Django's documentation notes it is not compatible with persistent connections, so leave CONN_MAX_AGE at 0 when using it. An in-process pool still holds connections per process, so it does not replace PgBouncer when many processes or hosts share one database.

Transaction boundaries#

ATOMIC_REQUESTS = True wraps every view in a transaction. It is simple and makes partial writes less likely, but every request holds a transaction open for its whole duration, including template rendering and calls to external services. That lengthens lock holding and works against transaction pooling. Explicit transaction.atomic() blocks around the code that actually needs atomicity are usually a better default.

from django.db import transaction
from django.db.models import F
 
from .models import Order, OrderItem, Product
 
 
class OutOfStock(Exception):
    pass
 
 
def reserve_with_lock(order: Order, product_id: int, quantity: int) -> None:
    with transaction.atomic():
        # SELECT ... FOR UPDATE: concurrent reservations for this product wait here.
        product = Product.objects.select_for_update().get(pk=product_id)
        if product.stock < quantity:
            raise OutOfStock(product_id)
        product.stock = F("stock") - quantity
        product.save(update_fields=["stock"])
        OrderItem.objects.create(order=order, product_id=product_id, quantity=quantity)
 
 
def reserve_atomic(order: Order, product_id: int, quantity: int) -> None:
    with transaction.atomic():
        # UPDATE ... WHERE stock >= quantity: returns the number of rows changed.
        updated = Product.objects.filter(pk=product_id, stock__gte=quantity).update(
            stock=F("stock") - quantity
        )
        if updated == 0:
            raise OutOfStock(product_id)
        OrderItem.objects.create(order=order, product_id=product_id, quantity=quantity)

select_for_update() must be evaluated inside a transaction; Django raises an error otherwise. It also accepts nowait=True and skip_locked=True. Keep slow work such as sending emails or calling payment providers outside the atomic block, and use transaction.on_commit() to trigger it after a successful commit.

QuerySet optimisation#

Most Django database performance problems come from issuing too many queries or fetching too much data. Use select_related() for foreign keys and prefetch_related() for reverse and many-to-many relations to avoid N+1 queries, only() or values() when large columns are not needed, exists() and count() instead of loading rows, and bulk_create() or update() for batches. Declare indexes in Meta.indexes so they match the queries the ORM produces, and confirm with QuerySet.explain().

Migrations on large tables#

Schema changes take locks, and some take strong ones. Since PostgreSQL 11, adding a column with a constant (non-volatile) default does not rewrite the table, so it is fast. Changing a column type, or adding a default that must be computed per row, may rewrite the table under an ACCESS EXCLUSIVE lock. Even a fast ALTER TABLE must wait for that lock, and while it waits it blocks queries queued behind it, so setting lock_timeout for the migration role is a sensible precaution.

For indexes on large tables, django.contrib.postgres.operations provides AddIndexConcurrently, which must run in a non-atomic migration:

from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models
 
 
class Migration(migrations.Migration):
    atomic = False
 
    dependencies = [
        ("orders", "0011_order_status"),
    ]
 
    operations = [
        AddIndexConcurrently(
            "order",
            models.Index(fields=["status", "created_at"], name="order_status_created_idx"),
        ),
    ]

Run migrations through a direct connection or a session-mode pool rather than a transaction-mode pool, because migration tooling sometimes relies on session behaviour.

10. Production checklist#

• Know your connection budget: application processes, workers, poolers, and admin access, all below max_connections with headroom.
• Use PgBouncer (or another pooler) for high-concurrency workloads; audit code for session-state assumptions before using transaction mode.
• Set statement_timeout, idle_in_transaction_session_timeout, and lock_timeout per role.
• Index for real query patterns; build indexes concurrently; review unused indexes.
• Enable pg_stat_statements and review top queries regularly.
• Use EXPLAIN (ANALYZE, BUFFERS) safely, inside a rolled-back transaction for writes.
• Keep autovacuum on; tune large, busy tables individually.
• Alert on transaction ID age, replication lag, disk usage, blocked sessions, and long transactions.
• Use explicit, short transactions; lock rows in a consistent order; prefer atomic conditional updates where they fit.
• Run physical backups with WAL archiving if you need point-in-time recovery; keep logical dumps for portability.
• Test restores on a schedule and record the time taken.
• Review migrations for locking behaviour before they reach production.

Conclusion#

Running PostgreSQL in production is less about memorising settings than understanding consequences. The process model explains why PostgreSQL connection pooling matters, MVCC explains why vacuum and short transactions matter, and the planner's reliance on statistics explains most surprising query plans. PostgreSQL indexing, query optimization, and PostgreSQL monitoring all follow the same loop: understand the workload, measure where time and resources go, change one thing, and measure again. Reliability rests on the parts that are easiest to postpone: timeouts that contain failures, alerts that fire before limits are hit, and backups that have actually been restored.

Further reading#

• PostgreSQL: Resource consumption settings
• PostgreSQL: Concurrency control (MVCC)
• PostgreSQL: Explicit locking
• PostgreSQL: Indexes
• PostgreSQL: Using EXPLAIN
• PostgreSQL: Statistics used by the planner
• PostgreSQL: Routine vacuuming
• PostgreSQL: The cumulative statistics system
• PostgreSQL: pg_stat_statements
• PostgreSQL: Backup and restore
• PostgreSQL: Continuous archiving and point-in-time recovery
• PgBouncer: Configuration
• PgBouncer: Features and SQL feature map for pooling modes
• Django: PostgreSQL notes and connection management
• Django: Database transactions
• Django: Database access optimization
• Django: PostgreSQL specific database migration operations