PostgreSQL Interview Questions for Indian Engineers 2026

These questions appear across all company types in India:

Q: What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN? INNER JOIN returns rows where the join condition matches in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right (NULLs where no match). RIGHT JOIN is the mirror. FULL OUTER JOIN returns all rows from both tables (NULLs where no match on either side).

Q: What is the difference between WHERE and HAVING? WHERE filters rows before grouping. HAVING filters groups after GROUP BY. You cannot use aggregate functions in WHERE: use HAVING for conditions on aggregates (SUM, COUNT, AVG).

Q: What is the difference between UNION and UNION ALL? UNION removes duplicates (requires a sort step). UNION ALL includes all rows including duplicates (faster). Use UNION ALL when you know there are no duplicates or do not need to remove them.

Q: Write a query to find the second-highest salary in a table: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); or using DENSERANK(): SELECT salary FROM (SELECT salary, DENSERANK() OVER (ORDER BY salary DESC) as rnk FROM employees) t WHERE rnk = 2;

Q: What is a CTE (Common Table Expression)? A named temporary result set defined with WITH. Makes complex queries more readable. CTEs can be recursive (for hierarchical data like org charts or category trees).

Q: What types of indexes does PostgreSQL support? B-tree (default, good for equality and range queries), Hash (good for equality-only), GiST (geometric, full-text search), SP-GiST (space-partitioned, phone book-style data), GIN (good for arrays, JSONB, full-text search), BRIN (block range, good for naturally ordered large tables like time-series).

Q: What is the difference between a clustered and non-clustered index in PostgreSQL? PostgreSQL does not have traditional clustered indexes. CLUSTER command reorders table data by an index once, but the ordering is not maintained on subsequent inserts. Every PostgreSQL index is a non-clustered index in traditional terms.

Q: What is EXPLAIN ANALYZE and how do you use it? EXPLAIN shows the query plan without executing. EXPLAIN ANALYZE executes the query and shows actual timings. Used to identify slow query components: sequential scans on large tables, nested loop joins on large result sets, missing index usage.

Q: What is MVCC (Multi-Version Concurrency Control)? PostgreSQL uses MVCC to allow concurrent reads and writes without blocking. Each transaction sees a snapshot of the database. Old row versions are kept until VACUUM cleans them up. This means reads never block writes and writes never block reads.

Q: What is VACUUM and AUTOVACUUM? VACUUM reclaims storage from dead row versions created by MVCC. AUTOVACUUM runs automatically in the background. Table bloat (from high update/delete rates) can slow queries: monitor with pgstatuser_tables.

Q: What are the ACID properties? Atomicity (all operations in a transaction succeed or all fail), Consistency (the database remains in a valid state after the transaction), Isolation (concurrent transactions do not interfere with each other), Durability (committed transactions survive crashes).

Q: What are PostgreSQL isolation levels? READ COMMITTED (default): each statement sees committed rows at the time of the statement. REPEATABLE READ: each transaction sees a consistent snapshot from its start. SERIALIZABLE: full serialisation, as if transactions ran one at a time.

Q: What is a deadlock and how does PostgreSQL handle it? Deadlock occurs when two transactions hold locks that the other needs. PostgreSQL's deadlock detector identifies deadlock cycles and terminates one transaction with error code 40P01. Applications should handle this error and retry the transaction.

Q: What is the difference between LOCK NOWAIT and SKIP LOCKED? LOCK NOWAIT fails immediately if the lock cannot be acquired. SKIP LOCKED skips rows that are locked, useful for job queue implementations (multiple workers can pull from a queue without blocking each other).

Q: What is the difference between JSON and JSONB in PostgreSQL? JSON stores the exact input text. JSONB stores a decomposed binary format: slower to insert but faster to query. JSONB supports indexing (GIN index). Use JSONB for any JSON data you need to query or filter on.

Q: How do you query nested JSONB fields? Use the -> operator to return JSON and ->> to return text. SELECT data->'address'->>'city' FROM users returns the city as text from a JSONB column named data.

Q: What is a partial index? An index on a subset of rows where a condition is true. Example: CREATE INDEX idxactiveusers ON users(email) WHERE active = true; This index only covers active users: smaller, faster, and only used when the WHERE clause includes active = true.

Q: What is connection pooling and why is it important for PostgreSQL? PostgreSQL creates a new process for each connection: expensive. Connection poolers (PgBouncer, pgpool-II) maintain a pool of database connections and reuse them across application requests. Critical for high-concurrency web applications.