Advanced PostgreSQL: Locks, Execution Plans and Partitioning

If you already understand PostgreSQL basics, Advanced PostgreSQL can be understood through three major areas:

Locks & concurrency — how does PostgreSQL handle when multiple transactions access data simultaneously?
Execution plan — how does PostgreSQL decide to run SQL queries, and how do you know where queries are slow?
Partitioning — how to split a very large table into multiple parts for more efficient querying/management?

1. Locks — How does PostgreSQL lock data?#

Imagine two transactions:

-- Transaction A
BEGIN;

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

sql

While A hasn’t COMMIT yet, transaction B runs:

BEGIN;

UPDATE accounts
SET balance = balance + 50
WHERE id = 1;

sql

B will have to wait for A to finish.

This is a form of row-level locking.

`SELECT ... FOR UPDATE`#

A very common pattern:

BEGIN;

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

-- process logic

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

COMMIT;

sql

FOR UPDATE tells PostgreSQL:

“I’m preparing to modify this row, don’t let other transactions modify it simultaneously.”

Very useful when you have logic like:

read → check → change

text

Example:

SELECT stock
FROM products
WHERE id = 10
FOR UPDATE;

sql

Then:

UPDATE products
SET stock = stock - 1
WHERE id = 10;

sql

If you don’t lock correctly, two simultaneous requests might both see:

stock = 1

text

and both think the product is still in stock.

Lock types to know#

You don’t need to memorize all of them right away. The most important:

Row lock#

SELECT ...
FOR UPDATE;

sql

or:

SELECT ...
FOR SHARE;

sql

Use when concurrency is at the record level.

Table lock#

Example:

LOCK TABLE accounts;

sql

Lock at the table level.

Typically, application code should not casually use table locks, as they easily reduce concurrency.

Deadlock#

This is a very important part.

Transaction A:

lock row 1
↓
wait for row 2

text

Transaction B:

lock row 2
↓
wait for row 1

text

We have:

A ──locks──> Row 1
A ──waits──> Row 2

B ──locks──> Row 2
B ──waits──> Row 1

text

No one can proceed.

PostgreSQL will detect the deadlock and terminate one transaction.

How to prevent#

Ensure all transactions acquire locks in the same order.

For example, always:

lock smaller account first
→ lock larger account

text

instead of this transaction:

account 1 → account 2

text

and that transaction:

account 2 → account 1

text

2. Execution Plan — How does PostgreSQL actually run queries?#

This is an extremely important skill when optimizing PostgreSQL.

You have a query:

SELECT *
FROM users
WHERE email = 'alice@example.com';

sql

Don’t just ask:

“Is there an index?”

Ask:

How is PostgreSQL actually executing this query?

Use:

EXPLAIN
SELECT *
FROM users
WHERE email = 'alice@example.com';

sql

For example, PostgreSQL might return:

Index Scan using users_email_idx on users
  Index Cond: (email = 'alice@example.com')

text

That means PostgreSQL is using an index.

`EXPLAIN ANALYZE`#

More importantly:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'alice@example.com';

sql

EXPLAIN gives you the planned execution.

EXPLAIN ANALYZE will execute the query and measure actual performance.

Example:

Index Scan using users_email_idx on users
(cost=0.42..8.44 rows=1 width=100)
(actual time=0.035..0.037 rows=1 loops=1)

text

You should focus on:

cost
rows
actual time
loops
scan type

Sequential Scan vs Index Scan#

Suppose the table has:

10,000,000 users

text

Query:

SELECT *
FROM users
WHERE id = 123;

sql

PostgreSQL might use:

Index Scan

text

because it only needs to find one row.

But query:

SELECT *
FROM users
WHERE country = 'Vietnam';

sql

If 70% of the table is Vietnam, PostgreSQL might choose:

Seq Scan

text

even if country has an index.

This is not necessarily a problem.

A common mistake:

“Seq Scan = bad query.”

Not true.

If you need to read most of the table, sequential reading is sometimes cheaper than using an index.

Execution nodes to know#

At minimum, understand:

Seq Scan
Index Scan
Index Only Scan
Bitmap Index Scan
Nested Loop
Hash Join
Merge Join
Sort
Aggregate

text

Example:

SELECT *
FROM orders o
JOIN users u ON u.id = o.user_id;

sql

PostgreSQL might choose:

Nested Loop

text

or:

Hash Join

text

or:

Merge Join

text

Depending on data, statistics, and cost model.

Query optimization example#

Suppose:

SELECT *
FROM orders
WHERE customer_id = 100
  AND created_at >= '2026-01-01';

sql

Index:

CREATE INDEX idx_orders_customer
ON orders(customer_id);

sql

It might be better if your workload frequently queries both conditions:

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at);

sql

Then:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 100
  AND created_at >= '2026-01-01';

sql

You want to see if the planner utilizes the new index.

3. Partitioning — Splitting large tables#

Suppose:

orders

text

has:

2 billion rows

text

and the data has:

created_at

text

You can partition by month:

orders
├── orders_2026_01
├── orders_2026_02
├── orders_2026_03
├── ...
└── orders_2026_08

text

Instead of one massive table.

Range partitioning#

Example:

CREATE TABLE orders (
    id bigint,
    created_at timestamp,
    customer_id bigint,
    amount numeric
) PARTITION BY RANGE (created_at);

sql

Create partition:

CREATE TABLE orders_2026_08
PARTITION OF orders
FOR VALUES FROM ('2026-08-01')
             TO ('2026-09-01');

sql

Next month’s partition:

CREATE TABLE orders_2026_09
PARTITION OF orders
FOR VALUES FROM ('2026-09-01')
             TO ('2026-10-01');

sql

Partition pruning#

This is why partitioning is useful.

Query:

SELECT *
FROM orders
WHERE created_at >= '2026-08-01'
  AND created_at < '2026-09-01';

sql

PostgreSQL might only read:

orders_2026_08

text

instead of:

orders_2026_01
orders_2026_02
orders_2026_03
...
orders_2026_08

text

This is called partition pruning.

But partitioning is not a “magic performance button”#

Example:

SELECT *
FROM orders
WHERE customer_id = 123;

sql

If you partition by:

created_at

text

then PostgreSQL might still have to search through multiple partitions.

Therefore, the partition key must match your workload.

An important question before partitioning:

“Which column do my largest queries typically filter by?”

4. How do these three topics relate?#

This is the important part.

Imagine an e-commerce system:

orders: 2 billion rows

text

You have:

SELECT *
FROM orders
WHERE customer_id = 123
  AND created_at >= '2026-01-01';

sql

You might need:

Partitioning#

Split:

orders
→ by created_at

text

Index#

Within each partition:

(customer_id, created_at)

sql

Execution plan#

Check:

EXPLAIN ANALYZE

sql

to see if PostgreSQL has:

Partition pruning
       ↓
Index Scan
       ↓
few rows

text

or not.

5. Locks + Execution plan also relate#

A slow query doesn’t just annoy users.

Example:

Transaction A
    ↓
UPDATE
    ↓
holds lock
    ↓
query runs 30 seconds

text

Meanwhile:

Transaction B
    ↓
UPDATE same row
    ↓
WAIT

text

An unoptimized query can cause locks to be held longer, creating:

slow query
   ↓
long transaction
   ↓
lock contention
   ↓
more waiting
   ↓
more slow requests

text

This is why when debugging production PostgreSQL, you shouldn’t just look at:

“This query takes 5 seconds.”

But also ask:

“What lock is it holding during those 5 seconds?”

6. A roadmap for learning Advanced PostgreSQL#

If your goal is senior/backend engineer, I would learn in this order:

flowchart TD
    A[PostgreSQL] --> B[Transactions]
    A --> C[Query Planning]
    A --> D[Storage]
    
    B --> E[Isolation]
    B --> F[Locks]
    B --> G[Deadlock]
    
    C --> H[EXPLAIN]
    C --> I[Indexes]
    
    D --> J[MVCC]
    D --> K[VACUUM]
    D --> L[Bloat]
    
    F --> M[Concurrency]
    I --> N[Performance]
    
    M --> O[Partitioning]
    N --> O
    O --> P[Large datasets]

After the three topics you asked about, the next topics very worth learning are:

MVCC
VACUUM / autovacuum
Index internals — B-tree, GIN, GiST, BRIN
Transaction isolation
Deadlock debugging
CTE / materialized CTE
Window functions
Query planner & statistics
Table/index bloat
Connection pooling
Replication
Read replicas
Partition maintenance

If learning in a practical way, the trio EXPLAIN ANALYZE + locks + MVCC should be prioritized first, as they help you debug most PostgreSQL production issues.

1. Locks — How does PostgreSQL lock data?#

SELECT ... FOR UPDATE#

Lock types to know#

Row lock#

Table lock#

Deadlock#

How to prevent#

2. Execution Plan — How does PostgreSQL actually run queries?#

EXPLAIN ANALYZE#

Sequential Scan vs Index Scan#

Execution nodes to know#

Query optimization example#

3. Partitioning — Splitting large tables#

Range partitioning#

Partition pruning#

But partitioning is not a “magic performance button”#

4. How do these three topics relate?#

Partitioning#

Index#

Execution plan#

5. Locks + Execution plan also relate#

6. A roadmap for learning Advanced PostgreSQL#

References#

`SELECT ... FOR UPDATE`#

`EXPLAIN ANALYZE`#