PostgreSQL
Relational database internals, query optimization, indexing, transactions, and operational concerns for backend interviews.
Relational Model & SQL Fundamentals
Relations, tuples, attributes and the SQL that operates on them — keys, NULL semantics, join types, and the logical query processing order interviewers test first.
Normalization & Schema Design
Normal forms walked through a concrete table, when denormalization pays off, modeling relationships and polymorphism, and surrogate vs natural keys including UUID v7.
PostgreSQL Data Types
Picking the right type: text vs varchar, numeric vs float for money, the all-important TIMESTAMPTZ, JSON vs JSONB, arrays, enums, network types, and custom domains.
Indexing — B-tree, Hash, GIN, GiST, BRIN
How each index type works and what it is for, composite and covering indexes, partial and expression indexes, the cost of indexing, and why the planner sometimes ignores an index.
Query Planning & EXPLAIN
Reading EXPLAIN output, the scan and join node types, how the planner chooses between them, finding slow queries with pg_stat_statements, and why Postgres has no planner hints.
Transactions, ACID, Isolation Levels
ACID, the four isolation levels and the anomalies they prevent, what Read Committed actually guarantees, SSI Serializable, MVCC and row versions, and VACUUM and ID wraparound.
Locks & Concurrency
Table- and row-level lock modes, advisory locks, deadlock detection, investigating contention with pg_locks and pg_stat_activity, and SKIP LOCKED for safe job queues.
Window Functions & Advanced SQL
Window vs aggregate functions, ranking and offset functions, frame clauses, CTE materialization (the 12+ change), recursive CTEs, LATERAL joins, DISTINCT ON, and GROUPING SETS.
JSON & JSONB
JSONB vs JSON, the access and containment operators, GIN indexing strategies, mutation functions, JSONPath, and when document columns beat normalized tables.
Full-Text Search
tsvector and tsquery, building a GIN-backed FTS index, the query-builder functions, ranking, language configurations, and when to reach for a dedicated engine instead.
Partitioning
Declarative partitioning, range/list/hash strategies, partition pruning, default partitions, live attach/detach, subpartitioning, and the foreign-key and global-index gotchas.
Replication & High Availability
Physical vs logical replication, sync vs async streaming, replication lag and slots, WAL, publications/subscriptions, failover tooling, and PgBouncer pooling modes.
Backup, Recovery, PITR
Logical vs physical backups, pg_dump/pg_dumpall/pg_basebackup, point-in-time recovery and WAL archiving, the ecosystem tools, and why DR testing matters.
Performance Tuning & Operations
The key postgresql.conf parameters and how to size them, autovacuum tuning, connection pooling, finding slow queries and unused indexes, and CLUSTER/REINDEX/VACUUM FULL locking.
Stored Procedures, Functions, Triggers
Functions vs procedures, the procedural languages, volatility categories, trigger types and timing, when triggers become a liability, return shapes, and SECURITY DEFINER.