Cracking PostgreSQL Interview Questions

Summary

The guide prepares candidates for PostgreSQL interviews by concentrating on transactional SQL rather than mixing it with analytical topics such as window functions, ranking, and aggregates. It covers relational modeling with foreign keys and join tables, PostgreSQL’s richer types, schemas, indexing strategies, and EXPLAIN ANALYZE for comparing estimated and actual query costs. It also addresses views, automatically updatable views and their limitations, database permissions, roles, PUBLIC grants, row-level security policies, and business logic implemented with PL/pgSQL functions and triggers. The guide recommends thinking in sets with SQL operations such as JOIN and UNION, using common table expressions for readable or recursive queries, and practicing these topics to build a strong foundation for PostgreSQL interview answers.

Takeaways

1. 1

   A many-to-many relationship can be modeled with a join table; the guide uses StudentCourses as an example and recommends understanding normalization and foreign keys.

2. 2

   EXPLAIN alone provides estimated plan costs, while EXPLAIN (ANALYZE) provides estimated and actual costs, allowing query plans to be compared before and after an index is added.

3. 3

   A view that selects from a single base relation can be automatically updatable; views involving multiple base relations require an INSTEAD OF trigger to support updates.