PostgreSQL — Cheat Sheet

Tags: postgresql, sql, rdbms, databases, backend, indexing, transactions

Level: intermediate

TL;DR: Core PostgreSQL features, MVCC mechanics, indexing strategies, query optimization, and transaction isolation levels for technical interviews.

Key concepts

Object-relational database management system with strong ACID compliance and extensible type system.

MVCC (Multi-Version Concurrency Control) ensures readers do not block writers and writers do not block readers.

WAL (Write-Ahead Logging) guarantees data integrity and enables point-in-time recovery and streaming replication.

Default transaction isolation level is Read Committed; also supports Repeatable Read and Serializable.

B-Tree is the default index type; GiST, GIN, BRIN, and SP-GiST serve specialized workloads.

GIN indexes optimize full-text search, arrays, and JSONB queries via inverted key mapping.

BRIN (Block Range Index) offers ultra-lightweight indexing for physically ordered sequential data like timestamps.

JSONB stores parsed binary JSON supporting fast path extraction, containment operators, and indexing.

Autovacuum reclaims dead tuple storage, updates table statistics, and prevents transaction ID wraparound.

Connection pooling tools like PgBouncer mitigate PostgreSQL's process-per-connection architectural overhead.

Typical questions and ready answers

Q: How does PostgreSQL implement MVCC under the hood?R: PostgreSQL appends new row versions on UPDATE and marks old rows via xmin and xmax transaction IDs. Queries check row visibility against a snapshot taken at query or transaction start, avoiding read locks.

Q: What is the difference between JSON and JSONB in PostgreSQL?R: json stores exact text as input, preserving whitespace and key order but requiring re-parsing on every query. jsonb stores decomposed binary data, which introduces minor write overhead but provides significantly faster lookup and index support.

Q: When should you choose a BRIN index over a standard B-Tree index?R: I use BRIN for massive, append-only tables where data is naturally correlated with physical page storage, such as log timestamps or auto-incrementing IDs. It uses minimal disk space and memory compared to B-Tree.

Q: How do you identify and resolve a slow query in PostgreSQL?R: I run EXPLAIN (ANALYZE, BUFFERS) to compare estimated vs actual row counts and identify expensive sequential scans or disk reads. From there, I add targeted partial or composite indexes, rewrite JOINs, or adjust table statistics.

Q: Why is connection pooling crucial when scaling PostgreSQL?R: PostgreSQL forks a separate OS process per client connection, each consuming significant RAM and IPC overhead. Using an external pooler like PgBouncer in transaction mode maintains a small backend pool while serving thousands of active client connections.

Pitfalls and common mistakes

Running table-wide COUNT(*) without WHERE clauses triggers sequential scans across all pages due to MVCC visibility checks.

Adding a column with a non-null volatile default in older Postgres versions locks tables and causes table rewrites.

Neglecting dead tuple bloat by disabling or misconfiguring autovacuum leads to severe table degradation and slow index scans.

Creating foreign keys without creating indexes on referencing columns causes table-level locking during parent row deletes.

Over-indexing write-heavy tables increases write amplification and disables HOT (Heap-Only Tuple) optimization.

Using OFFSET with large values in pagination forces PostgreSQL to read and discard all skipped rows.

Snippets and commandsSQL

```-- Analyze query plan with execution timing and buffer usage

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)

SELECT u.id, u.email, o.total_amount

FROM users u

JOIN orders o ON o.user_id = u.id

WHERE u.created_at >= NOW() - INTERVAL '30 days';

-- Composite index with included columns to enable index-only scans

CREATE INDEX CONCURRENTLY idx_orders_user_created

ON orders (user_id, created_at DESC)

INCLUDE (total_amount);

-- GIN index for JSONB containment lookups

CREATE INDEX idx_users_metadata_gin ON users USING GIN (metadata jsonb_path_ops);

SELECT * FROM users WHERE metadata @> '{"role": "admin"}';

-- Detect table bloat and dead tuple ratios

SELECT relname, n_live_tup, n_dead_tup,

ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC;

-- Identify running long queries and lock blocks

SELECT pid, now() - query_start AS duration, query, state

FROM pg_stat_activity

WHERE state != 'idle' AND (now() - query_start) > INTERVAL '5 seconds';

```

To review

[ ] Inspect HOT (Heap-Only Tuple) update mechanics and fillfactor settings.

[ ] Review query plan node types: Hash Join vs Merge Join vs Nested Loop.

[ ] Configure PgBouncer pool modes: session vs transaction vs statement.

[ ] Understand transaction ID freeze and wraparound vacuum mechanisms.

[x] Basic CRUD, B-Tree index creation, and standard ACID transaction blocks.

References

PostgreSQL Official Documentation (Core Architecture, Index Types, Concurrency Control)

The Internals of PostgreSQL (Hironobu Suzuki)

Designing Data-Intensive Applications (Martin Kleppmann)