36 PostgreSQL Internals and Optimization Interview Questions and Answers

Short answer: An index is a separate data structure that stores the values of one or more columns in a sorted/organized form along with pointers to the physical location of rows (TID — tuple identifier), so the database can find rows without scanning the entire table.

In detail: Without an index, PostgreSQL is forced to read the whole table (Sequential Scan) and check every row. An index lets it find the needed TIDs in O(log n) (for a B-tree) and then fetch the rows themselves from the heap (the table's main storage area).

CREATE INDEX idx_users_email ON users (email);

-- Now the query searches the tree, not the whole table
SELECT * FROM users WHERE email = 'a@b.com';

Physically, a table in Postgres is stored as a heap — an unordered set of 8 KB pages. An index is a "table of contents" for this heap. A single table can have many indexes.

⚠️ Gotcha: An index speeds up reads but slows down writes (each INSERT/UPDATE/DELETE must keep the index current). Adding an index on every column "just in case" is an antipattern.

Short answer: A B-tree (more precisely, the B+tree-like Lehman & Yao structure) is a balanced tree where the values are sorted. The leaves are linked into a doubly linked list. Search, range queries, and sorting work in time logarithmic in the tree's height.

In detail: A B-tree supports the operators =, <, <=, >, >=, BETWEEN, IN, IS NULL, as well as sorting (ORDER BY) and LIKE 'prefix%'. The tree consists of:

• a root page,
• internal (branch) pages with separators,
• leaf pages with the actual values and TIDs.

The tree height is usually 3–4 levels even for hundreds of millions of rows, so a search is 3–4 page reads.

CREATE INDEX idx_orders_created ON orders (created_at); -- B-tree by default

-- Efficient: range + sorting straight from the index
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
ORDER BY created_at
LIMIT 100;

The linked leaves allow a range scan: find the start of the range and walk the leaves to the end. That same ordering gives "free" sorting.

⚠️ Gotcha: A B-tree is useless for conditions like WHERE col LIKE '%suffix' or WHERE upper(col) = '...' — the ordering of values doesn't help. And remember: an index on created_at ASC perfectly serves ORDER BY created_at DESC (Postgres can read the tree in reverse).

Short answer: A Hash index stores the hash of the value and supports only the equality operator =. It can be slightly more compact/faster than a B-tree for point lookups, but it doesn't support ranges or sorting.

In detail: Before PostgreSQL 10, hash indexes weren't written to the WAL (they weren't crash-safe and weren't replicated), so they weren't recommended. As of version 10 they are WAL-logged and production-ready.

CREATE INDEX idx_sessions_token ON sessions USING hash (token);

SELECT * FROM sessions WHERE token = '...'; -- equality only

In practice, a B-tree is almost always preferred because it's more versatile, and the hash gain is small. A hash makes sense for very long values, where comparing hashes is cheaper than comparing full keys.

⚠️ Gotcha: A Hash index doesn't serve ORDER BY, <, >, LIKE, couldn't be unique until recent versions, and isn't fully usable in composite indexes. By default, choose a B-tree.

Short answer: GIN (Generalized Inverted Index) is an inverted index of the form "value → list of rows where it appears." It's ideal for composite values: arrays, jsonb, full-text search (tsvector) — anywhere a single column holds many elements.

In detail: GIN stores each element (a jsonb key, an array element, a lexeme) as a separate entry pointing to all rows that contain it. That's why it answers "which rows contain element X" so well.

-- Arrays: containment search
CREATE INDEX idx_posts_tags ON posts USING gin (tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgres'];

-- jsonb: search by keys/values
CREATE INDEX idx_docs_data ON docs USING gin (data);
SELECT * FROM docs WHERE data @> '{"status": "active"}';

-- Full-text search
CREATE INDEX idx_articles_fts ON articles USING gin (to_tsvector('russian', body));
SELECT * FROM articles
WHERE to_tsvector('russian', body) @@ plainto_tsquery('russian', 'database');

GIN operators: @> (contains), <@, ?, ?|, ?&, @@.

⚠️ Gotcha: GIN is expensive to update (an insert touches many entries). To soften this there's fastupdate (deferred inserts via a pending list), and autovacuum is essential. For jsonb queried only with @>, use the jsonb_path_ops operator class — it's smaller and faster but supports fewer operators.

Short answer: GiST (Generalized Search Tree) is a framework for "proximity/overlap" indexes: geodata (PostGIS), ranges (range types), geometry, nearest neighbors (KNN), and partially full-text. It's a balanced tree with customizable predicates.

In detail: GiST is not an exact but a "lossy" structure — internal nodes store approximate predicates (a bounding box), which enables searching by overlap, containment, and distance.

-- Non-overlapping ranges (exclusion constraint)
CREATE TABLE bookings (
  room_id int,
  during  tsrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

-- Geo: nearest points (KNN)
CREATE INDEX idx_places_geom ON places USING gist (geom);
SELECT * FROM places ORDER BY geom <-> ST_Point(30.3, 59.9) LIMIT 5;

There's also SP-GiST for unbalanced structures (quad-tree, radix-tree) — for text prefixes and points.

⚠️ Gotcha: GiST is slower than B-tree for plain equality/range on scalars — use it only when you need overlap/distance semantics, otherwise stick with B-tree.

Short answer: BRIN (Block Range Index) stores the minimum and maximum value for a range of blocks (128 pages by default). It's tiny in size and efficient for very large tables where the data physically correlates with the column (for example, a monotonically increasing created_at).

In detail: Instead of an entry per row, BRIN stores a summary (min/max) per block group. On a query it discards blocks whose ranges don't overlap the condition and scans only the rest.

CREATE INDEX idx_events_ts_brin ON events USING brin (created_at);

SELECT * FROM events WHERE created_at BETWEEN '2026-06-01' AND '2026-06-02';

An index over billions of rows can take only kilobytes. It's ideal for append-only tables (logs, metrics, time-series), where new rows are written at the end and the value keeps growing.

⚠️ Gotcha: If the physical row order doesn't correlate with the column value (for example, after many UPDATEs/shuffling), BRIN is useless — the block ranges overlap and you end up reading everything. You can restore correlation via CLUSTER or by sorting physically during load.

Short answer: When the condition isn't SARGable (a function/transformation over the column), with LIKE '%x', with low selectivity, on small tables, on a type mismatch (implicit cast), and when the planner decides a Seq Scan is cheaper.

In detail: Common causes:

-- 1) A function over the column kills the index on that column
WHERE lower(email) = 'a@b.com'        -- the index on email is not used
-- Fix: an expression index
CREATE INDEX ON users (lower(email));

-- 2) Leading wildcard
WHERE name LIKE '%son'                 -- B-tree doesn't help
WHERE name LIKE 'john%'                -- but this does (prefix)

-- 3) Low selectivity: 60% of the table will be returned
WHERE status = 'active'                -- Seq Scan is cheaper than index + random heap fetches

-- 4) Small table — it fits in a couple of pages, Seq Scan is faster

-- 5) Type mismatch
WHERE user_id = '123'                  -- user_id is bigint, '123' is text → possible cast
-- the index may not apply; cast the literal to the column type

-- 6) Arithmetic over the column
WHERE price * 1.2 > 100                 -- not SARGable
WHERE price > 100 / 1.2                 -- SARGable

⚠️ Gotcha: Don't do SET enable_seqscan = off to "force" the index — that masks the problem. If an index is ignored on a "correct" query, the culprit is almost always stale statistics (ANALYZE), a type mismatch, or the fact that the result set really is large. Look at EXPLAIN ANALYZE first.

Short answer: A composite (multicolumn) index is built over several columns. The "leftmost prefix" rule: the index is used effectively for conditions on the leading column and a contiguous prefix of columns from left to right. A column from the middle without the leading ones — no.

In detail:

CREATE INDEX idx ON orders (customer_id, status, created_at);

-- Used well:
WHERE customer_id = 5                                  -- prefix (1 column)
WHERE customer_id = 5 AND status = 'paid'              -- prefix (2 columns)
WHERE customer_id = 5 AND status = 'paid'
      AND created_at > '2026-01-01'                    -- the whole index
WHERE customer_id = 5 ORDER BY status, created_at      -- ordering from the index

-- Used poorly / not used:
WHERE status = 'paid'                                  -- no leading customer_id
WHERE created_at > '2026-01-01'                        -- no prefix

With equality on the leading columns, a range/sort can be applied efficiently to the next one. If the leading column has a range condition (>), the columns to its right are no longer used to narrow the tree further (though they may serve as an index filter).

⚠️ Gotcha: A single composite index (a, b) doesn't replace the need for a standalone index on b. But the (a, b) index covers queries on a — so a separate index on a alone is usually redundant.

Short answer: Put equality (=) columns first, then the column used for a range/sort. Among the equalities, consider which query combinations you need (the leading column should appear most often).

In detail: The "equality first, range last" heuristic:

-- Query:
SELECT * FROM events
WHERE tenant_id = 7 AND type = 'click' AND ts > now() - interval '1 day'
ORDER BY ts;

-- Optimal: equalities on the left, range+sort on the right
CREATE INDEX ON events (tenant_id, type, ts);

Putting ts first would only give you a coarse range without a precise hit on tenant_id/type. With the equalities on the left, the tree narrows straight to the right branch, and within it ts is already sorted — giving you both the filter and the ORDER BY without a separate sort.

Selectivity matters too, but with equalities the "equality first" rule usually wins.

⚠️ Gotcha: It's not always "the most selective column first." If you search the most selective column with a range and a less selective one with equality, the leading column should be the equality one.

Short answer: A covering index contains all the columns a query needs, so the data is taken straight from the index without touching the heap — that's an Index-Only Scan. INCLUDE adds a "payload" to the index leaves that doesn't participate in sorting/uniqueness.

In detail:

-- The query reads only user_id and email
SELECT email FROM users WHERE user_id = 42;

-- Option 1: everything in the key
CREATE INDEX ON users (user_id, email);

-- Option 2: INCLUDE (email isn't needed for the search, only for the return)
CREATE INDEX ON users (user_id) INCLUDE (email);

In the plan you'll see Index Only Scan. The advantage — no random access to the heap.

⚠️ Gotcha: An Index-Only Scan is possible only if the visibility map says the page is "all-visible." On freshly updated data, Postgres still visits the heap to check visibility (Heap Fetches in the plan > 0). If Heap Fetches is large — you need a VACUUM to update the visibility map.

Short answer: A partial index indexes only the rows that satisfy a WHERE condition. It's smaller, faster, and updated less often when queries always hit a subset of the data.

In detail:

-- Index only active orders (1% of them, not all 100M)
CREATE INDEX idx_orders_active ON orders (created_at)
WHERE status = 'active';

-- Used automatically if the query is compatible with the predicate:
SELECT * FROM orders WHERE status = 'active' AND created_at > '2026-06-01';

-- A common case: uniqueness only among the non-deleted
CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL;

This is a classic for soft-delete, flags, and task queues (WHERE processed = false).

⚠️ Gotcha: The planner uses a partial index only if it can prove the query's predicate is implied by the index's predicate. WHERE status = 'active' works, but WHERE status = $1 with a parameter at planning time may not, if the value is unknown (depends on the prepared statement / generic plan).

Short answer: An index not on a column but on the result of an expression/function. It's used when the same expression appears in WHERE/ORDER BY.

In detail:

-- Case-insensitive search
CREATE INDEX idx_users_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower($1);

-- Index on a jsonb field
CREATE INDEX idx_docs_status ON docs ((data->>'status'));
SELECT * FROM docs WHERE data->>'status' = 'active';

The expression in the index and in the query must match literally (up to an equivalence the planner can recognize).

⚠️ Gotcha: The function must be IMMUTABLE. You can't index on now() or functions that depend on session settings (for example, lower() without an explicit collation in unstable cases). An expression index is slightly more expensive to maintain — the expression is computed on every write.

Short answer: A unique index guarantees that a combination of values doesn't repeat, and at the same time speeds up lookups. PRIMARY KEY and a UNIQUE constraint are implemented on top of a unique index.

In detail:

CREATE UNIQUE INDEX ON users (email);

-- equivalent to a constraint
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

By default NULLs are treated as distinct (multiple NULLs are allowed). Since PostgreSQL 15 there's UNIQUE NULLS NOT DISTINCT to forbid duplicate NULLs.

⚠️ Gotcha: Uniqueness is checked immediately on insert (unless DEFERRABLE). Bulk inserts with conflicts are better done via INSERT ... ON CONFLICT DO NOTHING/UPDATE (upsert), otherwise the whole transaction fails on the first duplicate.

Short answer: Indexes cost you: (1) slower writes — every INSERT/UPDATE/DELETE maintains all indexes; (2) disk space; (3) maintenance overhead (VACUUM, bloat, reindexing); (4) load on the planner when choosing a plan.

In detail: UPDATE is especially expensive: because of MVCC it creates a new row version and may need to insert into all indexes (except for HOT updates — Heap-Only Tuple, when the changed columns aren't part of any index and the new version fits on the same page).

-- Find unused indexes (idx_scan = 0)
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

⚠️ Gotcha: "I'll just add an index" on a hot, large table under load blocks writes. Use CREATE INDEX CONCURRENTLY (not inside a transaction, slower, but doesn't block). Duplicate and unused indexes are a common source of write degradation.

Short answer: EXPLAIN shows the estimated plan and estimates (cost, rows). EXPLAIN ANALYZE actually runs the query and shows the real time and row counts. The plan is read bottom-up / inside-out.

In detail:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer on orders
  (cost=0.43..8.45 rows=3 width=64)
  (actual time=0.018..0.022 rows=2 loops=1)
  Index Cond: (customer_id = 42)
Planning Time: 0.1 ms
Execution Time: 0.05 ms

What it means:

• cost=startup..total — arbitrary units (not milliseconds): the cost to the first row and to the last.
• rows — the planner's estimate; actual ... rows — reality.
• loops — how many times the node ran (important in nested loops: real time = actual time * loops).
• BUFFERS — how many pages were read from cache (shared hit) and from disk (read).

What to look for on a slow query:

1. A large gap between rows (estimate) and actual rows — stale statistics → ANALYZE. A wrong estimate leads to a bad plan.
2. A Seq Scan on a large table with a selective filter → a missing index.
3. A large Rows Removed by Filter → the index doesn't narrow, filtering happens in the heap.
4. A large Heap Fetches in an Index Only Scan → you need a VACUUM.
5. A Sort with external merge Disk → not enough work_mem, the sort spilled to disk.
6. A Nested Loop with a large loops and a big inner side → a bad join.

⚠️ Gotcha: EXPLAIN ANALYZE actually runs the query — for UPDATE/DELETE/INSERT it will change data! Wrap it in a transaction with ROLLBACK. And remember: cost is not time; you can compare plans by cost, but measure performance by actual time and Execution Time.

Short answer: Seq Scan reads the whole table sequentially. Index Scan walks the index and fetches each row randomly from the heap. Bitmap Heap Scan is a hybrid: it collects all the needed TIDs into a bitmap, sorts them by physical location, and reads the heap sequentially; it's good at medium selectivity.

In detail:

-- Index Scan: few rows, pinpoint access
Index Scan using idx_users_email on users
  Index Cond: (email = 'a@b.com')

-- Bitmap: medium result set, many rows but not the whole table
Bitmap Heap Scan on orders
  Recheck Cond: (status = 'paid')
  ->  Bitmap Index Scan on idx_orders_status
        Index Cond: (status = 'paid')

-- Seq Scan: a large fraction of the table or no index
Seq Scan on small_table
  Filter: (active = true)

The selection logic:

• Very few rows → Index Scan (random access is justified).
• A medium fraction → Bitmap (avoid repeated random reads of the same page, read in order).
• A large fraction / a small table → Seq Scan (sequential reads are cheaper than random).

Bitmap can combine several indexes via BitmapAnd / BitmapOr.

⚠️ Gotcha: Recheck Cond in a Bitmap Heap Scan is normal: if the bitmap became "lossy" (not enough memory for exact TIDs, it stores whole pages), Postgres rechecks the condition on the rows. Lots of lossy → raise work_mem.

Short answer: Nested Loop — for each row of the outer table, look up matches in the inner one (good when the outer is small and there's an index on the inner). Hash Join — builds a hash table from the smaller side and streams the larger one through it (good for large unsorted sets, equality). Merge Join — merges two sorted sides (good when both are already sorted by the key).

In detail:

EXPLAIN ANALYZE
SELECT * FROM orders o JOIN customers c ON c.id = o.customer_id;

• Nested Loop: cost ≈ outer_rows * cost_inner_lookup. Great when the outer side returns few rows and there's an index on the inner. Bad when both are large (quadratic).
• Hash Join: O(n + m), needs memory for the hash of the smaller side. Equi-joins only (=). If the hash doesn't fit in work_mem — "batches" spill to disk.
• Merge Join: requires sorting both sides (or indexes that provide the order). Good for very large sorted sets and for range inequalities.

Hash Join  (cost=...) 
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o
  ->  Hash
        ->  Seq Scan on customers c

⚠️ Gotcha: If you see a Nested Loop with loops=1000000 and a Seq Scan inside — that's a disaster, usually from underestimating the outer side's rows (bad statistics) or a missing index on the join key. Check ANALYZE and the index on the FK column.

Short answer: SARGable (Search ARGument able) is a condition that lets an index be used, because the indexed column stands "bare" on one side of the operator, with no functions or computations over it.

In detail:

-- NOT SARGable (function/arithmetic over the column):
WHERE date_trunc('day', created_at) = '2026-06-24'
WHERE created_at + interval '1 day' > now()
WHERE extract(year from created_at) = 2026
WHERE price * 1.2 > 120

-- SARGable (rewritten so the column stays clean):
WHERE created_at >= '2026-06-24' AND created_at < '2026-06-25'
WHERE created_at > now() - interval '1 day'
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
WHERE price > 100

If the predicate cannot be rewritten, create an index on the exact expression, for example CREATE INDEX ON users (lower(email)); a plain index on email cannot serve WHERE lower(email) = ....

⚠️ Gotcha: An implicit cast also breaks SARGability. WHERE varchar_col = 123 (a number) can trigger type coercion and ignore the index. Cast the literal to the column's type, not the other way around.

Short answer: Make conditions SARGable, don't drag along SELECT *, avoid N+1 (join instead of querying in a loop), use keyset pagination, and where needed — denormalization and materialized views.

In detail:

-- 1) SELECT * prevents an index-only scan and drags in TOAST/large fields
SELECT id, name FROM products WHERE category_id = 5;   -- only what's needed

-- 2) N+1: instead of querying in the application loop — one JOIN/IN
SELECT o.*, c.name FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.id = ANY($1);

-- 3) Aggregates / frequently read — denormalization or a materialized view
CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) d, sum(amount) FROM orders GROUP BY 1;
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

N+1 "at the DB level" is when the ORM, instead of one query, runs 1 query for the list + N queries for the related entities. Solved by eager loading (JOIN) or batch fetching via WHERE id IN (...).

⚠️ Gotcha: Denormalization speeds up reads but creates the risk of data inconsistency and complicates writes (you need triggers/sync logic). A materialized view doesn't refresh automatically — the data is stale until a REFRESH.

Short answer: OFFSET N forces Postgres to read and discard the first N rows before returning the page you want — the cost grows linearly with N. Keyset (seek/cursor) pagination uses a condition on the last seen key and jumps straight to the right place via the index.

In detail:

-- Slow at depth: reads 100020 rows, returns 20
SELECT * FROM events ORDER BY created_at DESC, id DESC
OFFSET 100000 LIMIT 20;

-- Keyset: O(log n) jump to the right position via the index
SELECT * FROM events
WHERE (created_at, id) < ($last_created_at, $last_id)   -- cursor
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Needs an index ON events (created_at DESC, id DESC)

Keyset is stable under inserts/deletes (it doesn't "drift" like OFFSET) and doesn't degrade at large depths.

⚠️ Gotcha: Keyset can't "jump to page 500" (only forward/backward from the cursor) and requires a strict, unique sort order — that's why id is usually added to the key as a tie-breaker, otherwise rows with the same created_at would be lost or duplicated at the page boundary.

Short answer: MVCC (Multiversion Concurrency Control) keeps several versions of a row. Readers see a consistent snapshot of the data and don't block writers, and writers don't block readers. A version's visibility is determined by the system columns xmin/xmax.

In detail: Every row has hidden fields:

• xmin — the id of the transaction that created this version.
• xmax — the id of the transaction that deleted/updated it (0 if it's still alive).

SELECT xmin, xmax, * FROM accounts WHERE id = 1;

Visibility rules (simplified): a row is visible to a transaction if its xmin is already committed and visible in the snapshot, and xmax is either empty or belongs to a not-yet-committed/invisible transaction.

Why UPDATE = DELETE + INSERT: Postgres doesn't change a row in place. It sets xmax on the old version and inserts a new version with a new xmin. The old version (a dead tuple) stays until VACUUM removes it.

The snapshot determines which transactions count as "already finished." In READ COMMITTED a new snapshot is taken for each statement; in REPEATABLE READ/SERIALIZABLE — one for the whole transaction.

⚠️ Gotcha: Because of "UPDATE = new version," frequent updates produce bloat (the table swelling with dead rows) and force index updates. Also, MVCC requires fighting transaction ID wraparound: the XID counter is 32-bit, and without VACUUM (freeze) the database can halt to avoid "looping" the transaction age.

Short answer: VACUUM removes dead row versions (dead tuples), freeing space for reuse and updating the visibility map. ANALYZE collects data distribution statistics for the planner. autovacuum does both automatically in the background.

In detail:

VACUUM (VERBOSE, ANALYZE) orders;   -- cleanup + statistics
ANALYZE orders;                     -- statistics only
VACUUM FULL orders;                 -- rewrites the table, returns space to the OS

Differences:

• VACUUM (regular): marks the space from dead tuples as reusable within the table, doesn't return it to the OS, doesn't block readers/writers (takes a light lock). Updates the visibility map (needed for Index-Only Scan) and freezes old XIDs.
• VACUUM FULL: rewrites the whole table into a new file, physically compacting it and returning space to the OS, but takes an ACCESS EXCLUSIVE lock (a full table lock!).
• ANALYZE: computes statistics (histograms, n_distinct, most common values) that drive plan selection.

-- How many dead rows and when the last autovacuum ran
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

⚠️ Gotcha: You can't run VACUUM FULL in production on hot tables — it locks the entire table. For online bloat removal use pg_repack. Long-open transactions (idle in transaction) prevent VACUUM from removing dead tuples (they "might still be needed" by an old snapshot) — hence a sudden growth of bloat.

Short answer: Postgres uses row-level and table-level locks. Thanks to MVCC, reads aren't blocked. Deadlocks are detected automatically by a background detector, which aborts one of the transactions with an error.

In detail: Levels/types (simplified):

• Row-level: FOR UPDATE, FOR SHARE (SELECT ... FOR UPDATE), and also UPDATE/DELETE take a row lock.
• Table-level: ACCESS SHARE (SELECT) ... ACCESS EXCLUSIVE (DDL, VACUUM FULL).
• Advisory locks — application-level locks by key.

-- Transaction A
BEGIN; UPDATE accounts SET bal = bal - 100 WHERE id = 1; -- lock row 1
-- Transaction B
BEGIN; UPDATE accounts SET bal = bal - 50  WHERE id = 2; -- lock row 2
-- A: UPDATE ... WHERE id = 2  (waits for B)
-- B: UPDATE ... WHERE id = 1  (waits for A) → DEADLOCK

The detector finds the wait cycle and kills one transaction: ERROR: deadlock detected. The check interval is deadlock_timeout (1s by default).

-- Who is blocking whom
SELECT * FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT granted;

⚠️ Gotcha: The main deadlock prevention is to always take locks in the same order (for example, update rows in ascending id order). Use SELECT ... FOR UPDATE SKIP LOCKED for task queues so workers don't conflict. And avoid lock_timeout hangs — set timeouts.

Short answer: TOAST (The Oversized-Attribute Storage Technique) is a mechanism for storing large values (text, jsonb, bytea) that don't fit in an 8 KB page. Large fields are compressed and/or moved out into a separate TOAST table, while only a pointer remains in the main row.

In detail: If a row exceeds ~2 KB (TOAST_TUPLE_THRESHOLD), Postgres compresses and/or moves large attributes "out-of-line." Column storage strategies: PLAIN, EXTENDED (compress + move out, the default for text/jsonb), EXTERNAL (move out without compression), MAIN (compress but try to keep it inline).

ALTER TABLE docs ALTER COLUMN payload SET STORAGE EXTERNAL;

This is transparent to queries but affects performance: reading a TOAST value is an extra access.

⚠️ Gotcha: SELECT * on a table with large TOAST fields pulls in and decompresses them even when they're not needed — yet another reason to list only the columns you need. Frequent UPDATEs of large jsonb are expensive: the entire row version + TOAST changes.

Short answer: WAL (Write-Ahead Log) is a journal where changes are written BEFORE being applied to data pages. This provides durability (crash recovery) and serves as the foundation for replication and PITR (point-in-time recovery).

In detail: The write-ahead logging principle: before changing a data page, the change is recorded in WAL and flushed to disk (fsync) at COMMIT. If the server crashes, on startup it "replays" WAL and brings the data to a consistent state (crash recovery).

Advantages:

• No need to synchronously write the data pages themselves on every commit — a sequential write to WAL is enough (fast).
• WAL records are shipped to replicas → streaming replication.
• By archiving WAL, you can recover to any point in time (PITR).

SELECT pg_current_wal_lsn();           -- current position in WAL
SHOW wal_level;                        -- replica / logical

⚠️ Gotcha: Under heavy writes, WAL can grow faster than it's archived/replicated and fill up the disk. Watch pg_wal, replication lag, and max_slot_wal_keep_size. A stuck replication slot (a dead replica) holds back WAL and fills up the primary's disk.

Short answer: Physical (streaming) replication copies WAL byte-for-byte to a standby — the replica is identical to the primary, suitable for read-replicas and failover. Logical replication ships changes at the row level via publish/subscribe — selectively by table, across different versions. It can be synchronous or asynchronous.

In detail:

• Streaming (physical): the standby continuously receives WAL and applies it. Replicas are read-only (hot standby). One primary — many replicas. Used for read scaling and HA.
• Synchronous: a COMMIT on the primary waits for confirmation from the standby (no data loss if the primary fails, but higher latency).
• Asynchronous: the primary doesn't wait for the replica (faster, but the last transactions may be lost on a crash). The default.
• Logical: decodes WAL into logical changes and replicates selected tables. Flexible: different major versions, partial replication, different schemas, multi-master via extensions.

-- Logical replication
CREATE PUBLICATION pub_orders FOR TABLE orders;          -- on the source
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=primary dbname=app' PUBLICATION pub_orders;  -- on the receiver

⚠️ Gotcha: A read-replica under asynchronous replication lags (replication lag) — "I read back my own just-written record and didn't find it." This is the replication lag / read-your-writes problem: route critical reads after a write to the primary. A synchronous replica improves reliability, but if it hangs — commits on the primary stall (you need a quorum / several synchronous standbys).

Short answer: Declarative partitioning splits one logical table into several physical partitions by range, list, or hash of a key. This speeds up queries (partition pruning), simplifies removing old data (DROP a partition), and eases maintenance.

In detail:

CREATE TABLE events (id bigserial, created_at date, payload jsonb)
PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_06 PARTITION OF events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

CREATE TABLE events_2026_07 PARTITION OF events
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

Advantages:

• Partition pruning: a query with WHERE created_at >= '2026-07-01' scans only the relevant partitions.
• Removing data: DROP TABLE events_2026_06 is instant instead of a slow DELETE + VACUUM.
• Smaller per-partition indexes, better vacuum/maintenance.

Kinds: RANGE, LIST, HASH.

⚠️ Gotcha: The partition key must be part of the primary/unique key. Queries without a condition on the partition key scan all partitions (worse than a single table). Too many partitions (thousands) slow down planning. You need a process to automatically create future partitions (pg_partman or cron).

Short answer: Partitioning splits a table into parts within a single server. Sharding distributes data across multiple servers (horizontal scaling of writes and volume). Replication copies the same data to multiple servers (fault tolerance and read scaling).

In detail:

Sharding in Postgres usually requires an external solution (Citus, app-level routing, postgres_fdw). It's often combined: sharding + replication of each shard + partitioning within a shard.

⚠️ Gotcha: Sharding breaks cross-shard JOINs, transactions, and global key uniqueness — it's a serious architectural step. Don't shard "just in case": start with indexes, read replicas, partitioning, and connection pooling.

Short answer: Every connection in Postgres is a separate OS process with noticeable memory overhead, and their number is limited (max_connections). A connection pool (pgbouncer) reuses a small number of real connections across many clients, reducing overhead and protecting the database from overload.

In detail: Postgres uses a "process per connection" model, not threads. 10,000 client connections directly = 10,000 processes = a crash from memory and context switching. pgbouncer keeps, say, 50 real connections to the database and multiplexes thousands of clients onto them.

Pooling modes:

• session — a database connection is tied to a client for the entire session (safe, but less savings).
• transaction — the connection is returned to the pool after each transaction (most popular, best utilization).
• statement — after each query (aggressive).

⚠️ Gotcha: In transaction mode you cannot rely on server-side session features: prepared statements (no support), session-level SET, advisory session locks, LISTEN/NOTIFY, temporary tables — they may "leak" between different clients or simply not work. Also, don't set the pool size larger than the database can actually serve (rule of thumb: ~ (cores*2 + disks)).

Short answer: json stores text "as is" (preserving whitespace, key order, duplicates) and parses it on every access. jsonb stores a parsed binary representation: operations are faster, it supports indexing (GIN), but it doesn't preserve formatting or key order. In 99% of cases you want jsonb.

In detail:

-- GIN over the whole document: operators @>, ?, ?|, ?&
CREATE INDEX idx_docs_data ON docs USING gin (data);
SELECT * FROM docs WHERE data @> '{"status":"active"}';

-- jsonb_path_ops: smaller and faster, but only @>
CREATE INDEX idx_docs_data2 ON docs USING gin (data jsonb_path_ops);

-- B-tree on a specific path (equality/range on a single field)
CREATE INDEX idx_docs_status ON docs ((data->>'status'));
SELECT * FROM docs WHERE data->>'status' = 'active';

⚠️ Gotcha: json/jsonb tempt you to "dump everything into one column," losing relational guarantees (types, FKs, normalization). Use jsonb for genuinely semi-structured/dynamic data, not as a replacement for a proper schema. A GIN index over an entire jsonb is large and expensive to update — for searching on a single field, a B-tree on an expression is cheaper.

Short answer: PostgreSQL supports arrays of any type (int[], text[]) with containment operators and a GIN index. ENUM is an enumerated type with a fixed set of values, stored compactly and with a defined ordering.

In detail:

-- ARRAY
CREATE TABLE posts (id int, tags text[]);
INSERT INTO posts VALUES (1, ARRAY['sql','db']);
SELECT * FROM posts WHERE tags @> ARRAY['sql'];     -- contains
CREATE INDEX ON posts USING gin (tags);

-- ENUM
CREATE TYPE order_status AS ENUM ('new','paid','shipped','cancelled');
CREATE TABLE orders (id int, status order_status);
-- sorts by declared order, not alphabetically
SELECT * FROM orders ORDER BY status;

⚠️ Gotcha: ENUMs are hard to change: you can add a value (ALTER TYPE ... ADD VALUE), but removing/renaming one in the middle is painful (often it's easier to use text + CHECK or a lookup table). Arrays are convenient, but searching/joining by elements and maintaining integrity (there are no FKs on array elements) is worse than a normalized many-to-many relationship.

Short answer: In PostgreSQL, DDL statements (CREATE/ALTER/DROP) are transactional: you can run them inside BEGIN ... COMMIT and roll them back with ROLLBACK. Schema changes are atomic.

In detail:

BEGIN;
ALTER TABLE users ADD COLUMN age int;
CREATE INDEX idx_users_age ON users (age);
-- if something went wrong:
ROLLBACK;   -- the schema returns to its original state, as if nothing happened

This is a huge plus for migrations: an entire migration either applies in full or doesn't apply at all — there's no "half-broken" schema.

⚠️ Gotcha: Not everything can/should run inside a transaction. CREATE INDEX CONCURRENTLY, VACUUM, ALTER TYPE ... ADD VALUE (in older versions) do NOT work inside a transaction block. In addition, an ALTER TABLE that takes an ACCESS EXCLUSIVE lock in a long transaction blocks the table for the entire duration of the transaction — keep DDL transactions short and set a lock_timeout.

Short answer: A full scan is O(n): on a table with 100M rows, every search would read gigabytes from disk. An index gives you O(log n) and reads literally a few pages. Without indexes, any query scales linearly and kills the database under load.

In detail: Reading everything makes sense only when you actually take a large fraction of the rows (then a Seq Scan really is faster than random access), or the table is tiny. For point/selective queries the difference is milliseconds versus minutes.

⚠️ Gotcha: The reverse is also true — an index on a query returning 80% of the rows will only slow it down (random heap fetches). An index pays off on selective conditions.

Short answer: Postgres can't "jump" to the 100,000th row — it's forced to physically read and discard all 100,000 rows before the page you want. The cost is linear in OFFSET.

In detail: Use keyset pagination: the client keeps a cursor containing the last row's created_at and unique id. The next page runs WHERE (created_at, id) < (:created_at, :id) ORDER BY created_at DESC, id DESC LIMIT 20, so the database seeks into a matching composite index instead of scanning and discarding every earlier row.

⚠️ Gotcha: A deep OFFSET is also unstable: if rows were added/removed between requests, the pages "shift" — the user sees duplicates or skipped rows.

Short answer: Because of MVCC, updates and deletes leave behind dead row versions. VACUUM frees up that space for reuse, prevents bloat, updates the visibility map (for index-only scans), and protects against transaction ID wraparound.

In detail: Without VACUUM the table and indexes bloat, queries slow down, statistics go stale, and in the worst case the database goes into a protective shutdown from XID exhaustion. autovacuum usually handles it, but it requires tuning on write-heavy tables.

⚠️ Gotcha: Long transactions and idle in transaction prevent VACUUM from removing dead tuples — bloat grows even with autovacuum running.

Short answer: Find the query → take EXPLAIN (ANALYZE, BUFFERS) → look for the bottleneck (Seq Scan, row estimate mismatch, Nested Loop with large loops, on-disk sort, Heap Fetches) → fix the cause (index, ANALYZE, rewrite the query, increase work_mem) → verify the effect.

In detail: Step by step:

-- 1) Find heavy queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

-- 2) What's happening right now (locks, long-running queries)
SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY dur DESC;

-- 3) The plan of the actual query
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>;

What to look for in the plan: estimate vs actual rows (statistics), the presence of a Seq Scan on large tables, Rows Removed by Filter, the join type and loops, Sort Method: external merge Disk, Heap Fetches. Then hypothesis → fix (add/fix an index, ANALYZE, make the condition SARGable, rewrite pagination, raise work_mem) → re-measure.

⚠️ Gotcha: Don't optimize blindly and don't add indexes at random. Reproduce the problem with realistic data (on an empty table the plan will be different), use pg_stat_statements, and remember: sometimes a "slow query" is a symptom of locks or memory pressure, not a missing index.