PostgreSQL Performance Engineering: Indexes, Query Plans, Locks, and Write Amplification

1. The Real Problem: Performance Is a System Property

Imagine an eLearning platform with:

• 500,000 students
• 10,000+ learning objects
• 20,000 courses
• thousands of active enrollments
• millions of progress records
• frequent dashboard requests
• limited-capacity cohorts

A developer might start with a query like:

SELECT *
FROM enrollments
WHERE student_id = '7c1...'
  AND status = 'ACTIVE';

With 100 rows, almost anything works.

With 5 million rows, the database has to make an important decision:

"How should I find these rows?"

It could scan the entire table. Or it could use an index.

PostgreSQL's query planner makes that decision based on the query, available indexes, table statistics, estimated costs, and other factors. EXPLAIN allows us to see the plan PostgreSQL selected, while EXPLAIN ANALYZE actually executes the query and shows the real execution behavior.

That distinction is fundamental.

2. Start With a Realistic Database

Let's use a simplified enrollment system.

CREATE TABLE enrollments (
    id UUID PRIMARY KEY,
    student_id UUID NOT NULL,
    cohort_id UUID NOT NULL,
    status VARCHAR(20) NOT NULL,
    enrolled_at TIMESTAMP NOT NULL,
    completed_at TIMESTAMP
);

Suppose the application frequently executes:

SELECT *
FROM enrollments
WHERE student_id = $1
AND status = 'ACTIVE';

Initially, there may be no index except the primary key.

PostgreSQL could therefore perform a sequential scan:

Seq Scan on enrollments

Conceptually:

If only one row belongs to the student, reading millions of rows is wasteful.

This is where indexes become useful.

3. What an Index Actually Does

An index is an additional data structure that helps PostgreSQL locate rows without scanning the entire table.

A simplified mental model is:

Instead of examining every row, PostgreSQL can locate the relevant entries through the index.

PostgreSQL supports several index types, including B-tree, Hash, GiST, SP-GiST, GIN, and BRIN. B-tree is the default and is appropriate for many equality and range queries.

For our query:

WHERE student_id = $1

a B-tree index is a natural starting point.

CREATE INDEX idx_enrollments_student_id
ON enrollments(student_id);

But we can do better.

4. Don't Index Columns. Index Query Patterns.

This is one of the most important lessons.

A common beginner approach is:

"The table has 15 columns. Let's index the important ones."

That's the wrong mental model.

Indexes should be designed around how the application queries the data.

Suppose the application consistently asks:

SELECT *
FROM enrollments
WHERE student_id = $1
AND status = 'ACTIVE';

An index on only (student_id) may help. But a composite index can be more appropriate:

CREATE INDEX idx_enrollments_student_status
ON enrollments(student_id, status);

Now PostgreSQL has an index aligned with the query's filtering pattern.

Design indexes from workload patterns, not from the schema alone.

5. Composite Indexes and Column Order

Consider:

CREATE INDEX idx_enrollments_student_status
ON enrollments(student_id, status);

The order matters.

This index is particularly useful for queries such as:

and:

But it is not automatically equivalent to having (status, student_id) because PostgreSQL's B-tree ordering follows the index column sequence.

Therefore, when designing a composite index, consider:

• equality predicates
• range predicates
• sorting
• selectivity
• actual query patterns

Don't blindly put the column with the highest cardinality first.

6. Indexes Are Not Free

This is where many performance discussions become incomplete.

An index improves some reads. But every additional index also creates work.

Consider:

INSERT INTO enrollments (...) VALUES (...);

PostgreSQL must maintain:

More indexes mean more structures to maintain.

So the trade-off is:

PostgreSQL's documentation explicitly notes that indexes improve retrieval but also add overhead to the database system.

This becomes particularly important for write-heavy tables such as:

• progress tracking
• event logs
• analytics
• transaction records
• audit logs

7. The Query Planner: PostgreSQL Does Not Blindly Use Indexes

Suppose we create:

CREATE INDEX idx_students_email ON students(email);

Then execute:

SELECT * FROM students WHERE email = 'student@example.com';

You might expect: Index Scan

But PostgreSQL may choose: Seq Scan

That doesn't necessarily mean PostgreSQL is broken. It may be making the correct decision.

Imagine the table contains only 50 rows.

An index lookup might involve:

A sequential scan might simply read the small table directly. For a tiny table, the sequential scan may be cheaper.

"PostgreSQL isn't using my index" does not automatically mean the query is slow or the planner is wrong.

The planner chooses between possible execution strategies based on estimated costs.

8. EXPLAIN: Look at What PostgreSQL Plans to Do

Use:

EXPLAIN
SELECT *
FROM enrollments
WHERE student_id = '7c1...';

You might see:

Index Scan using idx_enrollments_student_id on enrollments
  Index Cond: (student_id = '7c1...')

This tells you PostgreSQL intends to use the index.

But there is an important limitation.

EXPLAIN gives you the planned behavior. It doesn't execute the query.

For real performance investigation, use:

EXPLAIN ANALYZE
SELECT *
FROM enrollments
WHERE student_id = '7c1...';

PostgreSQL executes the query and reports actual row counts and timing alongside the planner's estimates.

9. EXPLAIN ANALYZE: Estimated vs Actual

Imagine PostgreSQL reports:

Index Scan using idx_enrollments_student_id
(cost=0.43..12.50 rows=5 width=80)
(actual time=0.031..0.044 rows=4 loops=1)

There are two different worlds here.

But imagine:

Now something is wrong with the planner's understanding of the data distribution.

That can lead to bad decisions. For example, PostgreSQL might choose a nested loop because it expects 5 rows when there are actually 500,000.

10. Statistics Matter More Than Many Developers Realize

PostgreSQL maintains statistics about table data.

ANALYZE collects statistics that the query planner uses to estimate row distributions and choose execution plans.

Run:

ANALYZE enrollments;

After major changes to the data distribution, updated statistics can help the planner make better decisions.

A query planner using stale or insufficient statistics may estimate the query incorrectly.

This is why database performance is not just about SQL. It is also about the planner's knowledge of the data.

11. EXPLAIN BUFFERS: Find the Real I/O Problem

A particularly useful command is:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM enrollments
WHERE student_id = $1;

You may see:

Buffers: shared hit=120 read=15

A buffer hit means PostgreSQL found the required page in memory.

A read indicates that PostgreSQL had to read a page from storage.

BUFFERS can therefore help identify whether a query is causing significant buffer activity and I/O. PostgreSQL's EXPLAIN documentation specifically recommends it for examining buffer hits, reads, dirtied buffers, and writes.

12. A Real Query Optimization Example

Imagine our student dashboard executes:

SELECT *
FROM enrollments
WHERE student_id = $1
AND status = 'ACTIVE'
ORDER BY enrolled_at DESC;

Initially:

We inspect:

and discover:

The table contains millions of rows.

We add:

CREATE INDEX idx_enrollments_student_status_enrolled
ON enrollments(student_id, status, enrolled_at DESC);

Then run the query again.

The planner may now choose Index Scan, and the execution time could drop substantially.

The exact numbers will depend on the hardware, dataset, cache state, query shape, and data distribution. The important engineering process is:

Not:

13. Locks: When the Query Is Fast but the Transaction Is Slow

Now we move from query execution to concurrency.

Consider a cohort with Capacity = 1 seat.

Two students attempt to enroll simultaneously.

Naive code might do:

SELECT available_seats
FROM cohorts
WHERE id = $1;

Both transactions could see: available_seats = 1

Then both attempt:

UPDATE cohorts
SET available_seats = available_seats - 1
WHERE id = $1;

Without carefully designed concurrency control, application logic can make incorrect assumptions about the state it read.

This is where transactions and row-level locking become important.

14. SELECT FOR UPDATE

PostgreSQL supports:

SELECT *
FROM cohorts
WHERE id = $1
FOR UPDATE;

Conceptually:

This allows the application to serialize access to the specific row representing the contested resource.

PostgreSQL uses MVCC for normal concurrency and also provides explicit row- and table-level locking when applications need to manage particular points of conflict.

Don't use locks everywhere. Use them where there is an actual shared resource that must be coordinated.

15. MVCC: Why PostgreSQL Doesn't Lock Every Read

PostgreSQL uses Multiversion Concurrency Control, commonly called MVCC.

The basic idea is that PostgreSQL can maintain multiple row versions so transactions can see an appropriate snapshot of the data.

This means ordinary reads generally don't block ordinary writes in the way a simplistic lock-based mental model would suggest. PostgreSQL's documentation describes MVCC as a mechanism that reduces lock contention for multi-user workloads.

Think of it like:

This is one reason PostgreSQL can handle substantial concurrent workloads without every read waiting for every write.

But MVCC introduces another performance concern.

And this is where write amplification enters the picture.

16. What Is Write Amplification?

Suppose we have:

CREATE TABLE learning_progress (
    id UUID PRIMARY KEY,
    student_id UUID NOT NULL,
    content_id UUID NOT NULL,
    progress NUMERIC NOT NULL,
    last_position INTEGER,
    updated_at TIMESTAMP NOT NULL
);

A student watches a video. The frontend sends progress updates: 10%, 20%, 30%, ... 100%.

A naive implementation might execute ten updates:

UPDATE learning_progress SET progress = 0.10 WHERE id = $1;
UPDATE learning_progress SET progress = 0.20 WHERE id = $1;
UPDATE learning_progress SET progress = 0.30 WHERE id = $1;
...

The application thinks: "I'm just updating one row."

But PostgreSQL's MVCC model means an update creates a new row version rather than simply overwriting the existing row in place. Updates can also require new index entries, depending on which columns are indexed and whether a HOT update is possible.

This is write amplification.

The application requested a small logical change, but the database performs significantly more physical work to maintain consistency and durability.

17. Why Indexes Can Increase Write Amplification

Imagine:

CREATE INDEX idx_progress_student ON learning_progress(student_id);
CREATE INDEX idx_progress_content ON learning_progress(content_id);
CREATE INDEX idx_progress_updated ON learning_progress(updated_at);

Now suppose we execute:

UPDATE learning_progress
SET progress = 75,
    updated_at = NOW()
WHERE id = $1;

The row changed. The primary key remains the same. But updated_at is indexed.

That can affect how efficiently PostgreSQL can perform the update.

PostgreSQL has an optimization called a Heap-Only Tuple (HOT) update. HOT can avoid creating new index entries when the update does not modify indexed columns and there is enough space on the page for the new row version.

So excessive indexing can have a hidden cost:

This is why indexing every column is bad database engineering.

18. A Real-World Progress Tracking Problem

Imagine:

Even if the exact production workload is lower, the architectural lesson remains.

The wrong approach is firing every browser event directly into PostgreSQL.

Instead of persisting every tiny movement (10%, 11%, 12%...), the system can persist meaningful state transitions (e.g. 20%).

The point isn't to blindly discard updates. The point is to ask:

Does every state transition need durable storage?

Often, the answer is no.

19. Write Amplification Is Also an Application Problem

Database performance is frequently blamed on PostgreSQL when the actual problem is the application.

Consider an API PATCH /progress called every 500 milliseconds.

The database may be perfectly capable of handling a moderate number of writes. But if the application generates unnecessary writes, the database cannot magically remove that workload.

Performance engineering therefore has several layers:

Optimizing only the SQL layer can miss the actual bottleneck.

20. Another Example: E-Commerce Inventory

Consider an online store with product Stock = 1. Two customers click Buy simultaneously.

Bad approach:

Application checks if (stock > 0) purchase(). Two requests observe the same state.

A safer approach may use an atomic update:

UPDATE products
SET stock = stock - 1
WHERE id = $1
AND stock > 0
RETURNING stock;

Now PostgreSQL itself participates in enforcing the condition.

If the update returns a row: Purchase can continue.

If it returns zero rows: Out of stock.

Move correctness constraints as close to the data as practical.

Don't retrieve state, make a decision in application memory, and assume the state hasn't changed.

21. Locks Are Not Automatically Bad

A common misconception is: "Locks make databases slow."

The truth is more nuanced. Locks are necessary for many forms of coordination. The problem is uncontrolled lock contention.

For example, a bad transaction design:

Now Transaction B waits for five seconds. That's terrible transaction design.

Instead, keep the critical section small:

Long-running transactions can increase contention and interfere with cleanup of old row versions.

22. Finding Lock Contention

When the application feels slow, don't assume the query itself is slow. The query may be waiting.

PostgreSQL exposes information through system views such as pg_stat_activity:

SELECT
    pid,
    usename,
    state,
    wait_event_type,
    wait_event,
    query,
    query_start
FROM pg_stat_activity
WHERE state <> 'idle';

The important distinction is Query execution time versus Time waiting to execute.

A query can be computationally cheap but operationally slow because another transaction is holding a conflicting lock.

23. The N+1 Query Problem

PostgreSQL performance problems don't always appear inside one SQL statement.

const students = await studentRepository.find();

for (const student of students) {
  student.courses = await courseRepository.find({
    where: { studentId: student.id },
  });
}

Suppose there are 1,000 students. You produce 1 query for students + 1,000 queries for courses = 1,001 queries.

This is the classic N+1 query problem. The SQL queries might each be fast, but total system performance is poor.

A better approach uses joins, batching, or array queries:

SELECT *
FROM courses
WHERE student_id = ANY($1);

A fast query executed 10,000 times is still a performance problem.

24. Pagination: OFFSET Can Become Expensive

Consider:

SELECT *
FROM learning_objects
ORDER BY created_at DESC
LIMIT 50
OFFSET 500000;

The database may need to process a large number of preceding rows before returning the requested page.

For large datasets, keyset pagination can be more efficient:

SELECT *
FROM learning_objects
WHERE created_at < $last_seen_created_at
ORDER BY created_at DESC
LIMIT 50;

With an appropriate index:

the database can continue directly from the last known position without scanning half a million discarded rows.

25. Don't Optimize Before Measuring

This sounds obvious, but it is violated constantly.

Suppose someone says: "This query is slow. Add an index."

The correct response is: "Show me the query plan."

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

26. Practical Investigation Workflow

When a production query becomes slow, I use a systematic investigation process:

27. A Performance Checklist

28. The Four Performance Problems Are Connected

Indexes, query plans, locks, and write amplification are not independent topics. They influence each other.

This is why PostgreSQL performance should be approached as a system problem, not as a collection of isolated tricks.

29. What I Would Actually Optimize First

If I inherited a slow PostgreSQL-backed application, I would prioritize work in this exact order:

1. Find the expensive queries: Measure p95/p99 latency, query execution times, queries per request, and rows scanned.
2. Inspect query plans: Use EXPLAIN (ANALYZE, BUFFERS) to understand why PostgreSQL chose each plan.
3. Fix obvious query problems: Eliminate N+1 queries, remove unnecessary columns, replace bad OFFSET pagination, and optimize joins.
4. Design targeted indexes: Create composite indexes based on actual workload filtering and sorting requirements.
5. Investigate contention: Check locks, wait events, connection pool saturation, and transaction lengths via pg_stat_activity.
6. Reduce write amplification: Implement debouncing, batching, and HOT update optimizations for high-frequency progress and analytics writes.
7. Re-measure: Benchmark before and after changes. Never declare victory because SQL "looks better."

30. The Most Important Lesson

PostgreSQL optimization is not about memorizing syntax.

The actual engineering skill is learning to reason about how data moves through the system.

Once you understand these relationships, database optimization becomes much less mysterious.

31. Final Takeaways

If you remember only ten things from this article, remember these: