Production Patterns Overview

In real backend projects, PostgreSQL performance is rarely about a single slow query. It is the combination of how your application connects, how it queries, and how your schema evolves over time. This lesson covers four production patterns that directly impact end-to-end performance: connection pooling with PgBouncer, pagination strategies for large result sets, schema migration workflows using tools like sqitch or Flyway, and a systematic approach to performance tuning that starts from the query and goes down to the database configuration.

The goal is to move from "it works on my machine" to predictable behavior under real load.

Connection Pooling with PgBouncer

Every connection to PostgreSQL forks a backend process, which costs memory and CPU. A backend with hundreds of concurrent requests will exhaust the connection limit quickly. PgBouncer sits between your application and PostgreSQL, multiplexing many application connections onto a smaller pool of real database connections.

The most common production mode is transaction pooling, where a connection is assigned to a client only for the duration of a transaction:

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

In your application, you point the database host to port 6432 instead of 5432. One important caveat with transaction pooling: prepared statements, `SET` commands, and session-level features like `LISTEN/NOTIFY` do not work reliably because the underlying connection can change between transactions. Use server-side prepared statements only when running in session pooling mode.

Pagination Strategies

Offset pagination with `LIMIT ... OFFSET ...` is convenient but performs poorly on large tables because PostgreSQL still scans and discards all skipped rows. On a table with millions of rows, page 1000 is noticeably slower than page 1.

Keyset pagination (also called cursor pagination) avoids the offset entirely by using the last seen value from an indexed column:

-- Bad: offset pagination
SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

-- Good: keyset pagination
SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The second query uses a composite index on `(created_at, id)` and can jump directly to the right position. It also returns stable results even if new rows are inserted between page loads, which is essential for feeds and infinite scroll.

Schema Migrations

In production, schema changes must be repeatable, reviewable, and reversible. Tools like Flyway, Liquibase, or sqitch store migrations as versioned SQL files that run in order, with a tracking table recording which migrations have been applied.

A typical migration file looks like this:

-- V002__add_user_email_index.sql
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Three rules matter most. First, always use `CREATE INDEX CONCURRENTLY` on existing tables so you do not lock writes during index creation. Second, never mix destructive changes (dropping a column) with new features in the same migration, so rollback is possible. Third, test migrations against a copy of production data, because PostgreSQL planner statistics can reveal issues that a small dev database never shows.

End-to-End Performance Tuning

Tuning follows a layered approach. First, identify the slow query with `pg_stat_statements` or application-level logging. Second, run `EXPLAIN (ANALYZE, BUFFERS)` to see the actual plan, row counts, and buffer hits versus disk reads. Third, fix the root cause, which is usually a missing index, a wrong join order, or a query that fetches more data than needed.

Only after the query layer is clean should you touch database parameters. Common wins include increasing `shared_buffers` to around 25 percent of RAM, setting `effective_cache_size` to roughly 75 percent, and tuning `work_mem` per query if large sorts or hashes are spilling to disk. But remember: configuration cannot save a query that scans an entire table on every request.

-- Finding the top 10 slowest queries by total execution time
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Tổng kết

Production PostgreSQL performance is a system property, not a single knob. Pool connections with PgBouncer to handle concurrency without exhausting the database, replace offset pagination with keyset pagination on large tables, manage schema changes through versioned migration tools, and follow a query-first tuning workflow before touching server configuration. Together, these patterns give you predictable latency and a clear path forward when load increases.