roadmap
- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
Prepare for PostgreSQL interviews as a developer, from writing SQL to how MVCC, indexes and the planner shape your queries. Covers schema design, JSONB and extensions, transactions, security and scaling.
1252 of 1252 questions in your plan: 0 known, 0 in progress, 0 due, 0 skipped, 1252 new. 0 cut, outside your plan.
A developer is seldom interviewed on PostgreSQL alone. The questions come scattered through backend and data engineering loops: in a live SQL exercise, in the technical conversation about the service you last built, and in system design once the data needs a home. The questions start from things developers run into. Why does a table keep growing when the application deletes as many rows as it inserts? Why does a filter on `lower(email)` read the whole table although `email` is indexed? Why did a migration that only added an index block every write, and what would `CREATE INDEX CONCURRENTLY` have changed? Levels separate by how far an answer goes beneath the SQL. An early-career answer fixes the query: add the index, rewrite the predicate. A stronger one explains the engine: an update writes a new row version, a session left idle in transaction holds back vacuum, and a row estimate in `EXPLAIN (ANALYZE, BUFFERS)` that is far off points to stale statistics before it points to a missing index. From senior level up the question tends to arrive as an incident, such as an `ALTER TABLE` that waited for its lock and queued every later query behind it, and the answer names the view or log line that confirmed the cause.
SQL comes first: [the language](/topics/lang-sql), written to run on any engine, and straight after it [what Postgres adds](/topics/db-postgresql-sql-dialect), from `DISTINCT ON` and `ON CONFLICT` upserts to `RETURNING`. [psql](/topics/db-psql) sits beside them as the server's own terminal client, where `\copy` reads a file on your machine while `COPY` reads one on the server, and a script carries on past a failed statement unless `ON_ERROR_STOP` is set. From here on, a general subject and its Postgres form usually share a module, so a rule and the place Postgres bends it are read together. Data modeling and the code that lives in the database follow, because a schema has to exist before anything can be said about how a server stores or changes it. [Schema design](/topics/db-relational-schema-design) and the [constraints](/topics/db-relational-constraints) that make the database refuse bad rows meet Postgres's [column types](/topics/db-postgresql-sql-datatypes), and [views, procedures and triggers](/topics/db-relational-views-server-logic) meet [PL/pgSQL](/topics/db-postgresql-server-programming), where logic moves onto the server. Reading and changing data under load come next. [Indexes and access paths](/topics/db-relational-indexing) and [query processing](/topics/db-relational-query-processing) explain why a planner makes the choices it does, and [Postgres's own planning](/topics/db-postgresql-indexing-query-planning) shows why it sometimes passes over an index that exists. [Transactions and concurrency control](/topics/db-relational-transactions) name the anomalies each isolation level admits, which places Postgres's repeatable read exactly: a snapshot that prevents phantoms but still allows write skew. [MVCC and its locks](/topics/db-postgresql-mvcc-transactions) show how that snapshot is built, and [vacuum](/topics/db-postgresql-vacuum) closes the module because MVCC leaves dead row versions behind. [JSONB and the other multi-model features](/topics/db-postgresql-jsonb-extensions) wait until here because keeping documents in Postgres is an argument about indexes and write costs, and [extensions](/topics/db-postgresql-extensibility) are how the server keeps growing past its core. [Access control](/topics/db-relational-security-access) then runs from roles to row-level security, with [SQL injection](/topics/found-appsec-sql-injection) beside it as the attack that arrives through the application's own account. The last stretch turns from the query to the running server. [Engine anatomy](/topics/db-postgresql-architecture) and [tuning](/topics/db-postgresql-performance-tuning) show what each connection costs, which is why [PgBouncer](/topics/db-pgbouncer) sits with them, and why session state does not survive its transaction mode. [Partitioning](/topics/db-relational-partitioning-scaling) and [replication](/topics/db-relational-replication-ha-replication) change what earlier modules promised: a unique constraint on a partitioned table must include the partition key, and an asynchronous replica can return a row the primary has already changed. [The write-ahead log](/topics/db-postgresql-replication-wal) that feeds those replicas also makes [point-in-time recovery](/topics/db-postgresql-backup) possible, so backups and upgrades close the plan.
For a backend round a few weeks away, the modules that pair a general subject with its Postgres form repay drilling: with importance at must know, answer each slow-query or blocked-write question once in general terms and once in Postgres's own. Seniors can move quickly through the first two modules and spend the time on vacuum, the write-ahead log and replication, where the follow-up asks which statistics view would have shown the problem first. With more time, by topics is the study order to use, and a local server the place to use it: two `psql` sessions side by side reproduce much of the transactions module, one holding a lock or a snapshot while the other tries to write. If you learned databases on MySQL, read [heap storage against a clustered index](/topics/db-relational-indexing-clustered-vs-heap) early: InnoDB keeps rows inside the primary key's B+tree, Postgres writes new row versions into a heap, and habits formed on the first give wrong answers about the second. ORMs, and the N+1 selects they generate, sit outside this plan: the code that issues the queries is the subject of the application roadmaps, such as [Java Backend Developer](/roadmaps/java-developer). If the job is running Postgres rather than writing against it, owning failover, upgrades and backups, the [PostgreSQL DBA roadmap](/roadmaps/postgresql-dba) is built for that role.
A developer is seldom interviewed on PostgreSQL alone. The questions come scattered through backend and data engineering loops: in a live SQL exercise, in the technical conversation about the service you last built, and in system design once the data needs a home. The questions start from things developers run into. Why does a table keep growing when the application deletes as many rows as it inserts? Why does a filter on lower(email) read the whole table although email is indexed? Why did a migration that only added an index block every write, and what would CREATE INDEX CONCURRENTLY have changed?
Levels separate by how far an answer goes beneath the SQL. An early-career answer fixes the query: add the index, rewrite the predicate. A stronger one explains the engine: an update writes a new row version, a session left idle in transaction holds back vacuum, and a row estimate in EXPLAIN (ANALYZE, BUFFERS) that is far off points to stale statistics before it points to a missing index. From senior level up the question tends to arrive as an incident, such as an ALTER TABLE that waited for its lock and queued every later query behind it, and the answer names the view or log line that confirmed the cause.
SQL comes first: the language, written to run on any engine, and straight after it what Postgres adds, from DISTINCT ON and ON CONFLICT upserts to RETURNING. psql sits beside them as the server's own terminal client, where \copy reads a file on your machine while COPY reads one on the server, and a script carries on past a failed statement unless ON_ERROR_STOP is set. From here on, a general subject and its Postgres form usually share a module, so a rule and the place Postgres bends it are read together.
Data modeling and the code that lives in the database follow, because a schema has to exist before anything can be said about how a server stores or changes it. Schema design and the constraints that make the database refuse bad rows meet Postgres's column types, and views, procedures and triggers meet PL/pgSQL, where logic moves onto the server.
Reading and changing data under load come next. Indexes and access paths and query processing explain why a planner makes the choices it does, and Postgres's own planning shows why it sometimes passes over an index that exists. Transactions and concurrency control name the anomalies each isolation level admits, which places Postgres's repeatable read exactly: a snapshot that prevents phantoms but still allows write skew. MVCC and its locks show how that snapshot is built, and vacuum closes the module because MVCC leaves dead row versions behind.
JSONB and the other multi-model features wait until here because keeping documents in Postgres is an argument about indexes and write costs, and extensions are how the server keeps growing past its core. Access control then runs from roles to row-level security, with SQL injection beside it as the attack that arrives through the application's own account.
The last stretch turns from the query to the running server. Engine anatomy and tuning show what each connection costs, which is why PgBouncer sits with them, and why session state does not survive its transaction mode. Partitioning and replication change what earlier modules promised: a unique constraint on a partitioned table must include the partition key, and an asynchronous replica can return a row the primary has already changed. The write-ahead log that feeds those replicas also makes point-in-time recovery possible, so backups and upgrades close the plan.
For a backend round a few weeks away, the modules that pair a general subject with its Postgres form repay drilling: with importance at must know, answer each slow-query or blocked-write question once in general terms and once in Postgres's own. Seniors can move quickly through the first two modules and spend the time on vacuum, the write-ahead log and replication, where the follow-up asks which statistics view would have shown the problem first.
With more time, by topics is the study order to use, and a local server the place to use it: two psql sessions side by side reproduce much of the transactions module, one holding a lock or a snapshot while the other tries to write. If you learned databases on MySQL, read heap storage against a clustered index early: InnoDB keeps rows inside the primary key's B+tree, Postgres writes new row versions into a heap, and habits formed on the first give wrong answers about the second.
ORMs, and the N+1 selects they generate, sit outside this plan: the code that issues the queries is the subject of the application roadmaps, such as Java Backend Developer. If the job is running Postgres rather than writing against it, owning failover, upgrades and backups, the PostgreSQL DBA roadmap is built for that role.