Database · Fresher-relevant
PostgreSQL interview questions on JSONB, CTEs, window functions, and performance tuning for production workloads.
Commonly asked at: Commonly asked at product companies using Postgres as their primary DB, such as Zerodha, CRED, and Razorpay — general pattern, not company-verified.
Company names below are mentioned only to indicate the general type/level of interview these questions are common at, based on widely known industry patterns. This content is not affiliated with, endorsed by, or sourced from any confidential material of the named companies. All trademarks belong to their respective owners.
PostgreSQL vs MySQL — what's the difference?
Tip: Mention advanced data types (JSONB, arrays) — that's usually the standout feature interviewers expect you to know.
PostgreSQL is an open-source, object-relational database known for strict standards compliance and advanced data types like JSONB and arrays. Compared to MySQL, it generally offers more advanced features out of the box, like window functions and CTEs, though MySQL has historically been simpler to set up for basic use cases.
JSON vs JSONB — what's the difference?
Tip: Say "JSONB is usually the right default" — that's the practical takeaway interviewers want.
JSON stores data as an exact text copy of the input and gets re-parsed on every operation. JSONB stores it in a decomposed binary format — slightly slower to write, but much faster to query, and it supports indexing. JSONB is the recommended default unless you specifically need to preserve the exact original text.
What are CTEs, and why are they useful?
Tip: Mention recursion as a bonus point — it shows deeper knowledge beyond basic CTE usage.
A CTE, defined with WITH, creates a temporary named result set you can reference within a single query — it breaks complex queries into readable steps. CTEs also support recursion, useful for hierarchical data like org charts or category trees.
View vs materialized view — what's the difference?
Tip: Mention that materialized views need manual/periodic refresh — that's the key operational detail.
A regular view is a saved query that runs fresh every time you access it, always showing current data. A materialized view stores the actual result set on disk — faster to read, but shows stale data until you manually refresh it.
What is MVCC in PostgreSQL?
Tip: Say it avoids read locks — that's the practical benefit interviewers want highlighted.
MVCC lets multiple transactions read and write concurrently without blocking each other, by keeping multiple versions of a row so each transaction sees a consistent snapshot. This avoids needing read locks in most cases, improving concurrency.
What index types does PostgreSQL support?
Tip: Pair each index type with its use case — B-tree for general, GIN for JSONB/search, BRIN for time-series.
Beyond the default B-tree, PostgreSQL supports GIN, which is good for full-text search and JSONB, GiST for geometric or range data, and BRIN, which is efficient for very large, naturally ordered tables like time-series logs.
Primary key vs foreign key — what's the difference?
Tip: Keep this short — it's a quick definitional recall, not a deep-dive question.
A primary key uniquely identifies each row in its own table. A foreign key references another table's primary key, enforcing referential integrity — you can't insert a value that doesn't exist in the referenced table, unless it's explicitly allowed to be NULL.
What are triggers in PostgreSQL, and when would you use one?
Tip: Give the classic example — auto-updating an updated_at column — it's the most commonly cited use case.
A trigger automatically runs a function in response to an INSERT, UPDATE, or DELETE on a table. Common uses include auto-updating an updated_at timestamp, maintaining an audit log, or enforcing business rules too complex for a simple constraint.
What is a sequence in PostgreSQL?
Tip: Mention it's what powers SERIAL/IDENTITY columns under the hood — a common follow-up detail.
A sequence is a database object that generates a series of unique numbers, typically used to auto-generate primary key values. Columns declared as SERIAL or GENERATED ... AS IDENTITY are actually backed by a sequence under the hood.
⭐ Scenario: A specific query is running slower than expected in production. How would you diagnose it in PostgreSQL?
Tip: This is scenario-based — mention EXPLAIN ANALYZE specifically, it's the tool interviewers expect.
Situation: A query that used to be fast started taking noticeably longer as the table grew.
Task: I needed to identify the exact bottleneck without guessing.
Action: I ran EXPLAIN ANALYZE on the query to see the actual execution plan and where time was being spent, and found it was doing a sequential scan instead of using an existing index because of a type mismatch in the WHERE clause.
Result: Fixing the type mismatch let PostgreSQL use the index correctly, and the query time dropped back to normal.
InterviewEra generates role-specific questions using your actual projects and skills. Get scored feedback on technical depth, clarity, and structure — free to start.