Database (PostgreSQL) Basic

When an endpoint is slow, the first question is not “which index should I create?”. You need to know how PostgreSQL is reading the data, which query plan it chose, and whether it is waiting on another transaction.

These three problems can show the same symptom in the application, but the fixes differ.

This page is the starting point for picking the right article. The full list is in the left-hand menu.

Which layers does a query pass through?

1. Where is the data stored? Regular tables use a heap, made of pages holding the physical versions of rows (tuples). Large values may be compressed or moved to TOAST.

   Read pages, heap, tuples and TOAST to understand why table size, row size and the amount of data read are not the same.

2. How does PostgreSQL fetch the data? The planner estimates the cost of each alternative and picks a plan: scanning the table, going through an index, combining tables with joins, and other steps. Having an index does not mean the plan will use it.

   Start with B-tree index, then learn to read EXPLAIN and EXPLAIN ANALYZE to see the actual choice.

3. What happens when many transactions modify data at once? Besides the query plan, lock waits can slow requests down. Read row-level locks to tell plain reads apart from operations that lock rows; if transactions wait on each other in a cycle, see deadlock.

These are three viewpoints, not three steps every SQL statement goes through: a plain SELECT takes no row locks, and an index-based plan may not have to read the heap for every row.

Symptom → article to read

Loading diagram…

Current situation	Read in this order
New to PostgreSQL	How data is stored → B-tree index → Reading execution plans
Index created but still Seq Scan	Reading execution plans → Why an index is not used → Statistics and ANALYZE
Designing indexes for multi-condition queries	Composite column order → Partial, expression, covering → GIN, GiST, BRIN
Slow joins or slow deep pages	Join algorithms, OFFSET and keyset
Slow requests under concurrent writes, 40P01 errors	Row-level locks → Deadlock → NOWAIT and SKIP LOCKED
Inconsistent reads, suspected anomaly	ACID → Transaction anomalies → Isolation levels
Table bloat, VACUUM falling behind, wraparound warnings	MVCC → VACUUM, autovacuum and bloat → Transaction ID wraparound
Huge table, old data deleted periodically	Partitioning
A migration hangs the API, the pool drains waiting on locks	Table-level locks → Lock queue → Lock timeouts and monitoring
Two users edit the same record, cron runs twice across pods	Optimistic vs pessimistic, Advisory lock
too many clients, many pods exhaust connections	Connection pooling and PgBouncer
Reading right after a write returns old data	Replication and read replicas
23505 errors when two requests insert the same key	Upsert with ON CONFLICT
Reports on the wrong day, totals off by a few units	Misused data types
Choosing a primary key: bigint or UUID	UUID or bigint keys
SaaS customers must never see each other’s rows	Row-level security and multi-tenancy

Partitioning is strongest for retention (DETACH/DROP partition) and per-partition maintenance; it is not faster than a good index by default. Compare plans and operational cost before choosing it.

Learning path

1. Foundations: storage layout, B-tree, EXPLAIN.
2. Query tuning: index not used, statistics, composite index, join, pagination.
3. Concurrency and transactions: ACID, MVCC, isolation levels, row-level locks, table-level locks, lock queue, optimistic vs pessimistic, advisory lock, deadlock, lock timeouts and monitoring.
4. Operations: VACUUM and bloat, transaction ID wraparound, partitioning, connection pooling, replication, upsert, data types, UUID or bigint.

Read a plan in a few minutes

Run all of the SQL below in one psql session on PostgreSQL 17. The temp table only exists in that session and does not touch application tables:

CREATE TEMP TABLE demo_orders AS
SELECT id, id % 100 AS customer_id
FROM generate_series(1, 10000) AS g(id);

CREATE INDEX ON demo_orders (customer_id);
ANALYZE demo_orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM demo_orders WHERE customer_id = 42;

Look at the scan node: did PostgreSQL choose Seq Scan, Index Scan or Bitmap Heap Scan? Compare the estimated rows with actual rows, then read Buffers to see which pages were accessed.

The plan can differ between machines or after the data changes; the goal is to read what PostgreSQL did, not to force it to use the index. The EXPLAIN article explains each field in the output and how to investigate when estimates diverge from reality.